analytics/informix/extend/spatial.8.22.FC2/examples/sql/st_numgeometries.sql

-- The city engineer needs to know the number of distinct buildings
-- associated with each building footprint.
-- The building footprints are stored in the buildingfootprints table
-- that was created with the following CREATE TABLE statement.

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

INSERT INTO buildingfootprints VALUES(
   506, 1010,
   ST_MPolyFromText('multipolygon (((7.0 45.0,15.0 45.0,15.0 51.0,18.0 51.0,18.0 54.0,8.0 54.0,8.0 51.0,7.0 51.0,7.0 45.0)))',1000)
);

INSERT INTO buildingfootprints VALUES(
   543, 2930,
   ST_MPolyFromText('multipolygon (((26.0 55.0,38.0 55.0,38.0 48.0,34.0 48.0,34.0 50.0,26.0 50.0,26.0 55.0)))',1000)
);

INSERT INTO buildingfootprints VALUES(
   1208, 203,
   ST_MPolyFromText('multipolygon (((8.0 39.0,12.0 39.0,12.0 33.0,17.0 33.0,17.0 22.0,8.0 22.0,8.0 39.0)))',1000)
);

INSERT INTO buildingfootprints VALUES(
   178, 5192,
   ST_MPolyFromText('multipolygon (((26.0 33.0,38.0 33.0,38.0 24.0,33.0 24.0,33.0 27.0,26.0 27.0,26.0 33.0)))',1000)
);

-- The query lists the building_id that uniquely identifies each building
-- and the number of buildings in each footprint with the ST_NumGeometries
-- function.

SELECT building_id, ST_NumGeometries(footprint) no_of_buildings
   FROM buildingfootprints;