ST_Equals

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