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.
When used in SQLite, there is a second option when using ST_Relate in SQLite: you can compare two geometries to return a string representing the DE-9IM pattern matrix that defines the geometries' relationship to one another.
Syntax
Oracle and PostgreSQL
sde.st_relate (geometry1 sde.st_geometry, geometry2 sde.st_geometry, patternMatrix string)
SQLite
Option 1
st_relate (geometry1 st_geometry, geometry2 st_geometry, patternMatrix string)
Option 2
patternMatrix string st_relate (geometry1 st_geometry, geometry2 st_geometry)
Return type
Boolean
Option 2 for SQLite returns a string.
Examples
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 these examples, 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;
Returns the following:
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;
Returns the following:
g1=g2 g1=g3 g2=g3 t f f
SQLite
This first example shows the first ST_Relate option, which compares two geometries based on a DE-9IM pattern matrix to return 1 if the geometries meet the requirements defined in the matrix or 0 if the geometries do not.
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;
Returns the following:
g1=g2 g1=g3 g2=g3 1 0 0
This example compares two geometries and returns the DE-9IM pattern matrix.
SELECT st_relate (g1,g3)
FROM relate_test;
Returns the following:
st_relate (g1,g3) FF0FFF0F2