ST_Relate

Definition

ST_Relate compares two geometries and returns 1 (Oracle and SQLite) or t (PostgreSQL) if the geometries meet the conditions specified by the DE-9IM pattern matrix string; otherwise, 0 (Oracle and SQLite) or f (PostgreSQL) is returned.

Syntax

Oracle and PostgreSQL

sde.st_relate (geometry1 sde.st_geometry, geometry2 sde.st_geometry, patternMatrix string)

SQLite

st_relate (geometry1 geometryblob, geometry2 geometryblob, patternMatrix string)

Return type

Boolean

Example

A DE-9IM pattern matrix is a device for comparing geometries. There are several types of such matrices. For example, the equals pattern matrix (T*F**FFF*) will tell you if any two geometries are equal.

In this example, a table, relate_test, is created with three spatial columns, and point features are inserted into each one. The ST_Relate function is used in the SELECT statement to test if the points are equal. Note that if you know you want to determine if geometries are equal, it is recommended that you use the ST_Equals function instead.

Oracle

CREATE TABLE relate_test (
 g1 sde.st_geometry,
 g2 sde.st_geometry,
 g3 sde.st_geometry
);

INSERT INTO relate_test (g1, g2, g3) VALUES (
 sde.st_geometry ('point (10.02 20.01)', 4326),
 sde.st_geometry ('point (10.02 20.01)', 4326),
 sde.st_geometry ('point (30.01 20.01)', 4326)
);

SELECT sde.st_relate (g1, g2, 'T*F**FFF*') g1_equals_g2, 
 sde.st_relate (g1, g3, 'T*F**FFF*') g1_equals_g3, 
 sde.st_relate (g2, g3, 'T*F**FFF*') g2_equals_g3 
 FROM RELATE_TEST;
 
g1_equals_g2     g1_equals_g3      g2_equals_g3

1                0                 0

PostgreSQL

CREATE TABLE relate_test (
 g1 sde.st_geometry,
 g2 sde.st_geometry,
 g3 sde.st_geometry
);

INSERT INTO relate_test (g1, g2, g3) VALUES (
 sde.st_geometry ('point (10.02 20.01)', 4326),
 sde.st_geometry ('point (10.02 20.01)', 4326),
 sde.st_geometry ('point (30.01 20.01)', 4326)
);

SELECT st_relate (g1, g2, 'T*F**FFF*') AS "g1=g2", 
 st_relate (g1, g3, 'T*F**FFF*') AS "g1=g3",
 st_relate (g2, g3, 'T*F**FFF*') AS "g2=g3" 
 FROM relate_test;
 
g1=g2     g1=g3     g2=g3

t          f         f

SQLite

CREATE TABLE relate_test (id integer primary key autoincrement not null);

SELECT AddGeometryColumn(
 NULL,
 'relate_test',
 'g1',
 4326,
 'point',
 'xy',
 'null'
);

CREATE TABLE relate_test2 (id integer primary key autoincrement not null);

SELECT AddGeometryColumn(
 NULL,
 'relate_test2',
 'g2',
 4326,
 'point',
 'xy',
 'null'
);

CREATE TABLE relate_test3 (id integer primary key autoincrement not null);

SELECT AddGeometryColumn(
 NULL,
 'relate_test3',
 'g3',
 4326,
 'point',
 'xy',
 'null'
);

INSERT INTO relate_test (g1) VALUES (
 st_geometry ('point (10.02 20.01)', 4326)
);

INSERT INTO relate_test2 (g2) VALUES (
 st_geometry ('point (10.02 20.01)', 4326)
);

INSERT INTO relate_test3 (g3) VALUES (
 st_geometry ('point (30.01 20.01)', 4326)
);

SELECT st_relate (relate_test.g1, relate_test2.g2, 'T*F**FFF*') AS "g1=g2", 
 st_relate (relate_test.g1, relate_test3.g3, 'T*F**FFF*') AS "g1=g3",
 st_relate (relate_test2.g2, relate_test3.g3, 'T*F**FFF*') AS "g2=g3" 
 FROM relate_test, relate_test2, relate_test3;
 
g1=g2     g1=g3     g2=g3

1          0         0