The ST_GeometryN() function

The ST_GeometryN() function takes an ST_GeomCollection (ST_MultiPoint, ST_MultiLineString, or ST_MultiPolygon) and an INTEGER index and returns the nth ST_Geometry object in the collection.

Syntax

ST_GeometryN(mpt1 ST_MultiPoint, index integer)
ST_GeometryN(mln1 ST_MultiLineString, index integer)
ST_GeometryN(mpl1 ST_MultiPolygon, index integer)

Return type

ST_Geometry

Example

The city engineer wants to know which building footprints are all inside the first polygon of the lots ST_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 geometries:

CREATE TABLE buildingfootprints (building_id  integer,
                                 lot_id       integer,
                                 footprint    ST_MultiPolygon);

CREATE TABLE lots (lot_id  integer,
                   lot     ST_MultiPolygon);

The query lists the buildingfootprints table values of 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 element in the ST_MultiPolygon:

SELECT bf.building_id,bf.lot_id
   FROM buildingfootprints bf,lots
   WHERE ST_Within(footprint,ST_GeometryN(lot,1))
   AND bf.lot_id = lots.lot_id;