ArcGIS Server Banner

ST_Difference

ST_Difference

Release 9.3 E-mail This TopicPrintable VersionGive Us feedback

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)

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.

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

See Also

  • An overview of SQL functions used with ST_Geometry types
  • Spatial operations