# ST_Difference

Release 9.3
Note: This topic was updated for 9.3.1.

## Definition

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

## Syntax

Oracle

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

PostgreSQL

``st_difference (g1 st_geometry, g2 st_geometry)``

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.

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``````