ST_Difference

Definition

ST_Difference takes two ST_Geometry objects and returns an ST_Geometry object that is the difference of the source objects.

Syntax

sde.st_difference (g1 sde.st_geometry, g2 sde.st_geometry)

Return type

ST_Geometry

Example

The city engineer needs to know the total area of the city's lot area not covered by buildings. In fact, she wants the sum of the lot area after the building area has been removed.

CREATE TABLE footprints (building_id integer,
footprint sde.st_geometry);

CREATE TABLE lots (lot_id integer,
lot sde.st_geometry);

INSERT INTO footprints (building_id, footprint) VALUES (
1, 
sde.st_polygon ('polygon ((0 0, 0 10, 10 10, 10 0, 0 0))', 0)
);

INSERT INTO footprints (building_id, footprint) VALUES (
2,
sde.st_polygon ('polygon ((20 0, 20 10, 30 10, 30 0, 20 0))', 0)
);

INSERT INTO footprints (building_id, footprint) VALUES (
3,
sde.st_polygon ('polygon ((40 0, 40 10, 50 10, 50 0, 40 0))', 0)
);

INSERT INTO lots (lot_id, lot) VALUES (
1,
sde.st_polygon ('polygon ((-1 -1, -1 11, 11 11, 11 -1, -1 -1))', 0)
);

INSERT INTO lots (lot_id, lot) VALUES (
2,
sde.st_polygon ('polygon ((19 -1, 19 11, 29 9, 31 -1, 19 -1))', 0)
);

INSERT INTO lots (lot_id, lot) VALUES (
3,
sde.st_polygon ('polygon ((39 -1, 39 11, 51 11, 51 -1, 39 -1))', 0)
);

The city engineer equally joins the footprints and lots table on the lot_id and takes the sum of the area of the difference of the lots minus the footprints.

Oracle

SELECT SUM (sde.st_area (sde.st_difference (lot, footprint)))
FROM FOOTPRINTS bf, LOTS
WHERE bf.building_id = lots.lot_id;

SUM(ST_AREA(ST_DIFFERENCE(LOT,FOOTPRINT)))

114

PostgreSQL

SELECT SUM (sde.st_area (sde.st_difference (lot, footprint)))
FROM footprints bf, lots
WHERE bf.building_id = lots.lot_id;

sum

114