ST_Distance takes two geometries and returns the closest distance separating them.
Syntax
db2gse.ST_Distance(g1 db2gse.ST_Geometry, g2 db2gse.ST_Geometry)
Return type
Double
Examples
The city engineer needs a list of all buildings that are within one foot of any lot line.
The BUILDING_ID column of the BUILDINGFOOTPRINTS table uniquely identifies each building. The LOT_ID column identifies the lot each building belongs to. The footprint multipolygon stores the geometry of each building's footprint.
CREATE TABLE BUILDINGFOOTPRINTS ( building_id integer,
lot_id integer,
footprint db2gse.ST_MultiPolygon);
The LOTS table stores the lot ID that uniquely identifies each lot, and the lot multipolygon that contains the lot line geometry.
CREATE TABLE LOTS ( lot_id integer,
lot db2gse.ST_MultiPolygon);
The query returns a list of building IDs that are within one foot of their lot lines. The ST_Distance function performs a spatial join between the footprints and the boundary of the lot multipolygons. However, the equijoin between the bf.lot_id and LOTS.lot_id ensures that only the multipolygons belonging to the same lot are compared by the ST_Distance function.
SELECT bf.building_id
FROM BUILDINGFOOTPRINTS bf, LOTS
WHERE bf.lot_id = LOTS.lot_id AND
db2gse.ST_Distance(bf.footprint, db2gse.ST_Boundary(LOTS.lot)) <= 1.0;