ST_Difference takes two geometry objects and returns a geometry object that is the difference of the source objects.
Oracle
sde.st_difference (g1 sde.st_geometry, g2 sde.st_geometry)
PostgreSQL
st_difference (g1 st_geometry, g2 st_geometry)
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.
Oracle
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)
);
PostgreSQL
CREATE TABLE footprints (building_id integer,
footprint st_geometry);
CREATE TABLE lots (lot_id integer,
lot st_geometry);
INSERT INTO footprints (building_id, footprint) VALUES (
1,
st_polygon ('polygon ((0 0, 0 10, 10 10, 10 0, 0 0))', 0)
);
INSERT INTO footprints (building_id, footprint) VALUES (
2,
st_polygon ('polygon ((20 0, 20 10, 30 10, 30 0, 20 0))', 0)
);
INSERT INTO footprints (building_id, footprint) VALUES (
3,
st_polygon ('polygon ((40 0, 40 10, 50 10, 50 0, 40 0))', 0)
);
INSERT INTO lots (lot_id, lot) VALUES (
1,
st_polygon ('polygon ((-1 -1, -1 11, 11 11, 11 -1, -1 -1))', 0)
);
INSERT INTO lots (lot_id, lot) VALUES (
2,
st_polygon ('polygon ((19 -1, 19 11, 29 9, 31 -1, 19 -1))', 0)
);
INSERT INTO lots (lot_id, lot) VALUES (
3,
st_polygon ('polygon ((39 -1, 39 11, 51 11, 51 -1, 39 -1))', 0)
);
The city engineer equijoins 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 (st_area (st_difference (lot, footprint)))
FROM footprints bf, lots
WHERE bf.building_id = lots.lot_id;
sum
114