Definition
ST_Equals compares two geometries and returns 1 (Oracle and SQLite) or t (PostgreSQL) if the geometries are identical; otherwise, it returns 0 (Oracle and SQLite) or f (PostgreSQL).
Syntax
Oracle and PostgreSQL
sde.st_equals (geometry1 sde.st_geometry, geometry2 sde.st_geometry)
SQLite
st_equals (geometry1 geometryblob, geometry2 geometryblob)
Return type
Boolean
Example
The GIS technician suspects that some of the data in the studies table was duplicated. To alleviate his concern, he queries the table to determine whether any of the shape multipolygons are equal.
The studies table was created and populated with the following statements. The id column uniquely identifies the study areas, and the shape field stores the area's geometry.
Next, the studies table is spatially joined to itself by the equal predicate, which returns 1 (Oracle and SQLite) or t (PostgreSQL) whenever it finds two multipolygons that are equal. The s1.id<>s2.id condition eliminates the comparison of a geometry to itself.
Oracle
CREATE TABLE studies (
id integer unique,
shape sde.st_geometry
);
INSERT INTO studies (id, shape) VALUES (
1,
sde.st_polygon ('polygon ((0 0, 0 10, 10 10, 10 0, 0 0))', 4326)
);
INSERT INTO studies (id, shape) VALUES (
2,
sde.st_polygon ('polygon ((20 0, 20 10, 30 10, 30 0, 20 0))', 4326)
);
INSERT INTO studies (id, shape) VALUES (
3,
sde.st_polygon ('polygon ((40 0, 40 10, 50 10, 50 0, 40 0))', 4326)
);
INSERT INTO studies (id, shape) VALUES (
4,
sde.st_polygon ('polygon ((0 0, 0 10, 10 10, 10 0, 0 0))', 4326)
);
SELECT UNIQUE (s1.id), s2.id
FROM STUDIES s1, STUDIES s2
WHERE sde.st_equals (s1.shape, s2.shape) = 1
AND s1.id <> s2.id;
ID ID
4 1
1 4
PostgreSQL
CREATE TABLE studies (
id serial,
shape st_geometry
);
INSERT INTO studies (shape) VALUES (
st_polygon ('polygon ((0 0, 0 10, 10 10, 10 0, 0 0))', 4326)
);
INSERT INTO studies (shape) VALUES (
st_polygon ('polygon ((20 0, 20 10, 30 10, 30 0, 20 0))', 4326)
);
INSERT INTO studies (shape) VALUES (
st_polygon ('polygon ((40 0, 40 10, 50 10, 50 0, 40 0))', 4326)
);
INSERT INTO studies (shape) VALUES (
st_polygon ('polygon ((0 0, 0 10, 10 10, 10 0, 0 0))', 4326)
);
SELECT DISTINCT (s1.id), s2.id
FROM studies s1, studies s2
WHERE st_equals (s1.shape, s2.shape) = 't'
AND s1.id <> s2.id;
id id
1 4
4 1
SQLite
CREATE TABLE studies (
id integer primary key autoincrement not null
);
SELECT AddGeometryColumn (
NULL,
'studies',
'shape',
4326,
'polygon',
'xy',
'null'
);
INSERT INTO studies (shape) VALUES (
st_polygon ('polygon ((0 0, 0 10, 10 10, 10 0, 0 0))', 4326)
);
INSERT INTO studies (shape) VALUES (
st_polygon ('polygon ((20 0, 20 10, 30 10, 30 0, 20 0))', 4326)
);
INSERT INTO studies (shape) VALUES (
st_polygon ('polygon ((40 0, 40 10, 50 10, 50 0, 40 0))', 4326)
);
INSERT INTO studies (shape) VALUES (
st_polygon ('polygon ((0 0, 0 10, 10 10, 10 0, 0 0))', 4326)
);
SELECT DISTINCT (s1.id), s2.id
FROM studies s1, studies s2
WHERE st_equals (s1.shape, s2.shape) = 1
AND s1.id <> s2.id;
id id
1 4
4 1