The ST_Relate() function

The ST_Relate() function compares two geometries and returns 1 (TRUE) if the geometries meet the conditions specified by the DE-9IM pattern matrix string; otherwise, 0 (FALSE) is returned.

Syntax

ST_Relate(g1 ST_Geometry, g2 ST_Geometry, patternMatrix lvarchar)

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 will tell you if any two geometries are equal.

In this example, an equals pattern matrix, shown below, is read left to right, and top to bottom into the string ("T*F**FFF*"):
Table 1. Pattern matrix for equals

The table is a matrix. The top row and first column identify the geometry.

Interior (b) Boundary (b) Exterior (b)
Interior (a) T * F
Boundary (a) * * F
Exterior (a) F F *
Next, the table relate_test is created with the following CREATE TABLE statement:
CREATE TABLE relate_test (g1 ST_Geometry,
                          g2 ST_Geometry,
                          g3 ST_Geometry);
The following INSERT statements insert a sample subclass into the relate_test table:
INSERT INTO relate_test VALUES(
   ST_PointFromText('point (10.02 20.01)',1000),
   ST_PointFromText('point (10.02 20.01)',1000),
   ST_PointFromText('point (30.01 20.01)',1000)
);
The following SELECT statement and the corresponding result set lists the subclass name stored in the geotype column with the dimension of that geotype:
SELECT ST_Relate(g1,g2,"T*F**FFF*") equals,
       ST_Relate(g1,g3,"T*F**FFF*") not_equals
   FROM relate_test;

equals not_equals 

     t          f