User's Guide and Reference

ST_Difference

ST_Difference takes two geometry objects and returns a geometry object that is the difference of the source objects.

Syntax

db2gse.ST_Difference(g1 db2gse.ST_Geometry, g2 db2gse.ST_Geometry)

Return type

db2gse.ST_Geometry

Examples

The city engineer needs to know the total area of the city's lot area not covered by a building. That is, the city engineer wants the sum of the lot area after the building area has been removed.

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

The city engineer equijoins the BUILDINGFOOTPRINTS and LOTS table on the lot_id. The engineer then takes the sum of the area of the difference of the lots, minus the building footprints.

SELECT SUM(db2gse.ST_Area(db2gse.ST_Difference(lot,footprint)))
FROM BUILDINGFOOTPRINTS bf, LOTS
WHERE bf.lot_id = LOTS.lot_id;