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)

Integer (Boolean)

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