ST_Equals compares two geometries and returns 1 (Oracle) or t (PostgreSQL) if the geometries are identical; otherwise, it returns 0 (Oracle) or f (PostgreSQL).
Oracle
sde.st_equals (g1 sde.st_geometry, g2 sde.st_geometry)
PostgreSQL
st_equals (g1 st_geometry, g2 st_geometry)
The city GIS technician suspects that some of the data in the bldgs table was somehow duplicated. To alleviate his concern, he queries the table to determine if any of the footprint multipolygons are equal.
The bldgs table was created and populated with the following statements. The bldg_id column uniquely identifies the buildings, and the footprint stores the building's geometry.
Oracle
CREATE TABLE bldgs (bldg_id integer unique,
footprint sde.st_geometry);
INSERT INTO bldgs (bldg_id, footprint) VALUES (
1,
sde.st_polygon ('polygon ((0 0, 0 10, 10 10, 10 0, 0 0))', 0)
);
INSERT INTO bldgs (bldg_id, footprint) VALUES (
2,
sde.st_polygon ('polygon ((20 0, 20 10, 30 10, 30 0, 20 0))', 0)
);
INSERT INTO bldgs (bldg_id, footprint) VALUES (
3,
sde.st_polygon ('polygon ((40 0, 40 10, 50 10, 50 0, 40 0))', 0)
);
INSERT INTO bldgs (bldg_id, footprint) VALUES (
4,
sde.st_polygon ('polygon ((0 0, 0 10, 10 10, 10 0, 0 0))', 0)
);
PostgreSQL
CREATE TABLE bldgs (bldg_id integer unique,
footprint st_geometry);
INSERT INTO bldgs (bldg_id, footprint) VALUES (
1,
st_polygon ('polygon ((0 0, 0 10, 10 10, 10 0, 0 0))', 0)
);
INSERT INTO bldgs (bldg_id, footprint) VALUES (
2,
st_polygon ('polygon ((20 0, 20 10, 30 10, 30 0, 20 0))', 0)
);
INSERT INTO bldgs (bldg_id, footprint) VALUES (
3,
st_polygon ('polygon ((40 0, 40 10, 50 10, 50 0, 40 0))', 0)
);
INSERT INTO bldgs (bldg_id, footprint) VALUES (
4,
st_polygon ('polygon ((0 0, 0 10, 10 10, 10 0, 0 0))', 0)
);
The bldgs table is spatially joined to itself by the equal predicate, which returns 1 whenever it finds two multipolygons that are equal. The b1.bldg_id<>b2.bldg_id condition eliminates the comparison of a geometry to itself.
Oracle
SELECT UNIQUE (b1.bldg_id), b2.bldg_id
FROM bldgs b1, bldgs b2
WHERE sde.st_equals (b1.footprint, b2.footprint) = 1
AND b1.bldg_id <> b2.bldg_id;
BLDG_ID BLDG_ID
4 1
1 4
PostgreSQL
SELECT DISTINCT (b1.bldg_id), b2.bldg_id
FROM bldgs b1, bldgs b2
WHERE st_equals (b1.footprint, b2.footprint) = 't'
AND b1.bldg_id <> b2.bldg_id;
BLDG_ID BLDG_ID
1 4
4 1