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