User's Guide and Reference

ST_GeometryN

ST_GeometryN takes a collection and an integer index and returns the nth geometry object in the collection.

Syntax
db2gse.ST GeometryN(g db2gse.ST_GeomCollection, n Integer)

Return type

db2gse.ST_Geometry

Examples

The city engineer needs to know if the building footprints are all inside the first polygon of the lot's multipolygon.

The BUILDING_ID column uniquely identifies each row of the BUILDINGFOOTPRINTS table. The LOT_ID column identifies the building's lot. The FOOTPRINT column stores the building's geometry.

CREATE TABLE BUILDINGFOOTPRINTS ( building_id integer,
                                  lot_id      integer,
                                  footprint   db2gse.ST_MultiPolygon);
 
CREATE TABLE LOTS ( lot_id   integer,
                    lot   db2gse.ST_MultiPolygon);

The query lists the BUILDINGFOOTPRINTS building_id and lot_id for all building footprints that are all within the first lot polygon. The ST_GeometryN function returns a first lot polygon in the multipolygon array.

SELECT bf.building_id,bf.lot_id
FROM BUILDINGFOOTPRINTS bf,LOTS
WHERE db2gse.ST_Within(footprint, db2gse.ST_GeometryN (lot,1)) = 1
      AND bf.lot_id = LOTS.lot_id;