Spatial Relationships - Ocient Documentation

spatial relationship functions use arguments to test for different types of spatial relationships.

ST_CLUSTERDBSCAN

Returns the cluster number for each input geography based on a two-dimensional implementation of the density-based spatial clustering of applications with noise (DBSCAN) algorithm. This function is a window function because the resulting value for an individual row depends on other rows and not just itself. No frame specification is allowed. This function supports sort orders, but it is unnecessary unless deterministic resolution of tie-breaking in cluster determination is required. This tie-breaking happens only in situations where a point on the boundary of a cluster is equally valid for multiple clusters. In these cases, the result depends on the sort order of the data. For this reason, you might want to sort the data to force the deterministic behavior by using an ORDER BY in the OVER clause. The function returns NULL for any row not included in a cluster. Syntax

SQL

ST_CLUSTERDBSCAN(geo, eps, min_neighbors)

Argument	Data Type	Description
`geo`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object to be grouped into a cluster based on the DBSCAN algorithm.
`eps`	`DOUBLE`	A numeric argument that controls the separation between objects before they are considered a new cluster. The `eps` argument is in meters.
`min_neighbors`	`BIGINT`	The minimum number of objects within the appropriate distance of each other that need to exist before it is considered a cluster.

Example

SQL

SELECT ST_CLUSTERDBSCAN(ST_POINT(1.5, 1.5), 1.0, 1) OVER();

Output: 1

ST_CONTAINS

Returns TRUE if the first geographic argument, geo1, contains the second geographic argument, geo2. geo1 contains geo2 only if all of these criteria are met:

No POINT values of geo2 are located at the exterior of geo1.
At least one POINT in the interior of geo2 lies in the interior of geo1.

Geographies do not contain their boundaries, therefore, all POINT values in a LINESTRING are interior, except for the endpoints, which are regarded as exterior boundaries. If either geo1 or geo2 is NULL, then the function returns NULL. Syntax

SQL

SELECT ST_CONTAINS(geo1, geo2)

Argument	Data Type	Description
`geo1`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object to be compared to `geo2`. The `POINT` values in `geo1` are evaluated to determine if they are contained within `geo2`.
`geo2`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object to be compared to `geo1`. The `POINT` values in `geo2` are evaluated to determine if they are contained within `geo1`.

Example

SQL

SELECT ST_CONTAINS(
    ST_LINEFROMTEXT('LINESTRING(0 0, 1 1, 2 2)'),
    ST_POINT(1, 1));

Output: true

ST_CONTAINSPROPERLY

This function is similar to ST_CONTAINS, but it has stricter criteria. Returns true if geo2 lies entirely in the interior of geo1 and does not intersect or touch the boundary or exterior points. Returns NULL if either geo1 or geo2 is NULL. Syntax

SQL

ST_CONTAINSPROPERLY(geo1, geo2)

Argument	Data Type	Description
`geo1`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object to be compared to `geo2`. The `POINT` values in `geo1` are evaluated to determine if they are contained within `geo2`.
`geo2`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object to be compared to `geo1`. The `POINT` values in `geo2` are evaluated to determine if they are contained within `geo1`.

Examples In this example, the first geographic object is a POLYGON representing a simple square (0 0, 1 0, 1 1, 0 1). The second geographic object is a small LINESTRING that spans from the point (0.25 0.25) to the point (0.75 0.75). Because the line is located entirely within the polygon, never touching its boundaries, the function evaluates to true.

SQL

SELECT ST_CONTAINSPROPERLY(
        ST_MAKEPOLYGON(
            ST_MAKELINE(
                ARRAY [ST_POINT(0, 0), ST_POINT(1, 0), ST_POINT(1, 1), ST_POINT(0, 1), ST_POINT(0, 0)])),
        ST_MAKELINE(ST_POINT(0.25, 0.25), ST_POINT(0.75, 0.75)));

Output: TRUE The second geographic object is slightly altered in this example, where the line spans from (0 0) to (1 1). Both of these endpoints touch the boundaries of the polygon, so the function now evaluates to false.

SQL

SELECT ST_CONTAINSPROPERLY(
        ST_MAKEPOLYGON(
            ST_MAKELINE(
                ARRAY [ST_POINT(0, 0), ST_POINT(1, 0), ST_POINT(1, 1), ST_POINT(0, 1), ST_POINT(0, 0)])),
        ST_MAKELINE(ST_POINT(0, 0), ST_POINT(1, 1)));

Output: FALSE

ST_COVERS

Returns TRUE if no POINT in geo2 is outside of geo1. Returns NULL if either geo1 or geo2 is NULL. Syntax

SQL

ST_COVERS(geo1, geo2)

Argument	Data Type	Description
`geo1`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object to be compared to `geo2`. The `POINT` values in `geo2` are evaluated to determine if they are contained within `geo2`.
`geo2`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object to be compared to `geo1`. The `POINT` values in `geo2` are evaluated to determine if they are contained within `geo1`.

Example

SQL

SELECT ST_COVERS(
        ST_MAKEPOLYGON(
            ST_MAKELINE(
                ARRAY [ST_POINT(0, 0), ST_POINT(1, 0), ST_POINT(1, 1), ST_POINT(0, 1), ST_POINT(0, 0)])),
        ST_MAKELINE(ST_POINT(0, 0), ST_POINT(1, 1)));

Output: TRUE

ST_COVEREDBY

This function operates similarly to ST_COVERS, except the arguments are interchanged. Returns TRUE if no POINT in geo1 is outside of geo2. Returns NULL if either geo1 or geo2 is NULL. Syntax

SQL

ST_COVEREDBY(geo1, geo2)

Argument	Data Type	Description
`geo1`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object to be compared to `geo2`. The `POINT` values in `geo1` are evaluated to determine if they are contained within `geo2`.
`geo2`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object to be compared to `geo1` The `POINT` values in `geo1` are evaluated to determine if they are contained within `geo2`.

Example

SQL

SELECT ST_COVEREDBY(
    ST_POINT(1,1),
    ST_POLYGON('POLYGON((1 1, 2 1, 2 2, 1 1))'));

Output: TRUE

ST_CROSSES

Returns TRUE if two geospatial objects meet this criteria:

The intersection of the geospatial interiors is not empty.
The intersection is not equal to geo1 or geo2.
Neither geospatial object is a single POINT.

Returns NULL if either argument is NULL. Syntax

SQL

ST_CROSSES(geo1, geo2)

Argument	Data Type	Description
`geo1`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object to be compared to `geo2` to evaluate whether they cross.
`geo2`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object to be compared to `geo1` to evaluate whether they cross.

Example

SQL

SELECT ST_CROSSES(
    ST_LINESTRING(ST_POINT(0,0), ST_POINT(2,2)),
    ST_LINESTRING(ST_POINT(1,0), ST_POINT(1,5)));

Output: TRUE

ST_DISJOINT

Returns true if the specified geographies have no intersection, including boundaries. Both geographic arguments can be different types. This function is the opposite of the ST_INTERSECTS function. Syntax

SQL

ST_DISJOINT(geo1, geo2)

Argument	Data Type	Description
`geo1`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object to be compared to `geo2` to evaluate whether they intersect.
`geo2`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object to be compared to `geo1` to evaluate whether they intersect.

Example

SQL

SELECT ST_DISJOINT(
    ST_POLYGON(ST_LINESTRING('LINESTRING(2 3, 2 4, 2 4, 2 3)')),
    ST_POLYGON(ST_LINESTRING('LINESTRING(1 2, 1 4, 1 5, 1 2)')));

Output: TRUE

ST_DWITHIN

Returns TRUE if the geographies are within a specified distance in meters. Syntax

SQL

ST_DWITHIN(geo1, geo2, distance_meters)

Argument	Data Type	Description
`geo1`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object to be compared to `geo2` to evaluate whether they are within the specified distance `distance_meters`.
`geo2`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object to be compared to `geo1` to evaluate whether they are within the specified distance `distance_meters`.
`distance_meters`	`DOUBLE`	A numeric value that represents the number of meters to evaluate the distance between `geo1` and `geo2`.

Example

SQL

SELECT ST_DWITHIN(
    ST_POINT('POINT(1 1)'),
    ST_POINT('POINT(0 0)'),
    100);

Output: FALSE

ST_EQUALS

Returns TRUE if both geographies are spatially equal. Syntax

SQL

ST_EQUALS(geo1, geo2)

Argument	Data Type	Description
`geo1`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object to be compared to `geo2` to evaluate whether they are equal.
`geo2`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object to be compared to `geo1` to evaluate whether they are equal.

Example

SQL

SELECT ST_EQUALS(
    ST_POLYGON(ST_LINESTRING('LINESTRING(10 10, 0 0, 0 0, 10 10)')),
    ST_POLYGON(ST_LINESTRING('LINESTRING(10 10, 0 0, 0 0, 10 10)')));

Output: True

ST_INTERSECTS

Returns TRUE if the specified geographies intersect, including boundaries. This function is the opposite of the ST_DISJOINT function. Syntax

SQL

ST_INTERSECTS(geo1, geo2)

Argument	Data Type	Description
`geo1`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object to be compared to `geo2` to evaluate whether they intersect.
`geo2`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object to be compared to `geo1` to evaluate whether they intersect.

Example

SQL

SELECT ST_INTERSECTS(
    ST_POLYGON(ST_LINESTRING('LINESTRING(2 3, 2 4, 2 4, 2 3)')),
    ST_POLYGON(ST_LINESTRING('LINESTRING(1 2, 1 4, 1 5, 1 2)')));

Output: FALSE

ST_ISCCW

Alias for ST_ISPOLYGONCCW.

ST_ISCLOSED

Returns TRUE if an input LINESTRING has starting and ending points that are equal. If you specify a POLYGON or POINT value, the function returns true if the specified geography is not empty. Syntax

SQL

ST_ISCLOSED(line)

Argument	Data Type	Description
`line`	`POINT`, `LINESTRING`, or `POLYGON`	If you specify a `LINESTRING` object, the function evaluates it to determine whether its starting and ending points are equal. If you specify a `POLYGON` or `POINT` value, the function returns `true` if the specified geography is not empty.

Example

SQL

SELECT ST_ISCLOSED(
    ST_LINESTRING('LINESTRING(1 2, 1 3, 1 2)'));

Output: TRUE

ST_ISPOLYGONCW

Alias for BOOLNOT(ST_ISCCW(poly)). Returns TRUE if an input POLYGON has an exterior that is clockwise. If the POLYGON argument is empty, this function returns TRUE. If the argument is NULL, this function returns NULL. Syntax

SQL

ST_ISPOLYGONCW(poly)

Argument	Data Type	Description
`poly`	`POLYGON`	A `POLYGON` object to evaluate to determine whether its point values align in a clockwise direction.

Example

SQL

SELECT ST_ISPOLYGONCW(
    ST_POLYGON('POLYGON((0 0,1 0,1 1,0 1,0 0))'));

Output: FALSE

ST_ISPOLYGONCCW

Alias for ST_ISCCW. Returns TRUE if an input POLYGON has an exterior that is counter-clockwise. If the POLYGON argument is empty, this function returns TRUE. If the argument is NULL, this function returns NULL. Syntax

SQL

ST_ISPOLYGONCCW(poly)

Argument	Data Type	Description
`poly`	`POLYGON`	A `POLYGON` object to evaluate to determine whether its point values align in a counter-clockwise direction.

Example

SQL

SELECT ST_ISPOLYGONCCW(
    ST_POLYGON('POLYGON((0 0,1 0,1 1,0 1,0 0))'));

Output: TRUE

ST_ISRING

Returns TRUE if the specified LINESTRING is closed and does not intersect itself. This function is equivalent to evaluating both ST_ISCLOSED and ST_ISSIMPLE. If you specify a value other than LINESTRING, the function throws an error. Syntax

SQL

ST_ISRING(geo)

Argument	Data Type	Description
`line`	`LINESTRING`	A `LINESTRING` object to evaluate to determine whether its starting and ending points are equal and whether it has no intersections.

Example

SQL

SELECT ST_ISRING(
    ST_LINESTRING('LINESTRING(1 2, 2 4, 1 3, 1 2)'));

Output: TRUE

ST_ISSIMPLE

For LINESTRING values, this function returns FALSE if any line segments intersect anywhere besides the endpoints. For POLYGON values, the function returns FALSE if either the exterior ring or any interior hole is not simple. For POINT values, the function always returns TRUE. Syntax

SQL

ST_ISSIMPLE(geo)

Argument	Data Type	Description
`geo`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object to evaluate to determine if any points intersect. For `LINESTRING` values, this function returns `FALSE` if any line segments intersect anywhere besides the endpoints. For `POLYGON` values, the function returns `FALSE` if either the exterior ring or any interior hole is not simple. For `POINT` values, the function always returns `TRUE`.

Example

SQL

SELECT ST_ISSIMPLE(
    ST_POLYGON('POLYGON((1 2, 3 4, 3 5, 1 2))'));

Output: TRUE

ST_ISVALID

Returns TRUE if the specified geospatial value is a well-formed and valid geography according to the OGC standards. LINESTRING values are not valid if they are composed of more than one POINT with the same coordinates. POLYGON values are valid if they are composed of one external ring (shell) and zero or more internal rings. A ring is a LINESTRING where only the first and last POINT intersect each other. All internal rings need to be contained inside the shell, and none of the rings should intersect with each other in more than one POINT. Syntax

SQL

ST_ISVALID(geo1)

Argument	Data Type	Description
`geo`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object to evaluate to determine if it is a well-formed geography.

Example

SQL

SELECT ST_ISVALID(
    ST_LINESTRING('LINESTRING(1 2, 2 4)'));

Output: TRUE

ST_OVERLAPS

Returns TRUE if both geographic arguments are of the same dimension and intersect, but neither contains the other. Returns NULL if either geo1 or geo2 is NULL. Syntax

SQL

ST_OVERLAPS(geo1, geo2)

Argument	Data Type	Description
`geo1`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object for evaluation to determine if it is shaped similarly and intersects `geo2`.
`geo2`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object for evaluation to determine if it is shaped similarly and intersects `geo1`.

Example

SQL

SELECT ST_OVERLAPS(
    ST_LINESTRING(ST_POINT(0,0), ST_POINT(2,2)),
    ST_LINESTRING(ST_POINT(1,1), ST_POINT(3,3)));

Output: TRUE

ST_POINTINSIDECIRCLE

Returns TRUE if the geographic object is inside a circle centered at the specified point coordinates and with the specified radius. Syntax

SQL

ST_POINTINSIDECIRCLE(geo, center_x, center_y, circle_radius)

Argument	Data Type	Description
`geo`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object for evaluation to determine if it is contained within a circle of the specified dimensions.
`center_x`	`DOUBLE`	The x coordinate of the center of the circle to evaluate.
`center_y`	`DOUBLE`	The y coordinate of the center of the circle to evaluate.
`circle_radius`	`DOUBLE`	The radius in meters of the circle to evaluate.

Example

SQL

SELECT ST_POINTINSIDECIRCLE(
    ST_POINT(1, 3), 1, 2, 3000000);

Output: TRUE

ST_RELATE

Returns the DE-9IM intersection string that represents the nature of the intersection with the specified geographies. If you specify an intersection pattern as the third argument, this function returns TRUE if the intersection of the geographies satisfies the pattern and FALSE otherwise. Syntax

SQL

ST_RELATE(geo1, geo2, [, intersectionPattern])

Parameter	Data Type	Description
`geo1`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object to relate with `geo2`.
`geo2`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object to relate with `geo1`.
`intersectionPattern`	`STRING`	Optional. Intersection pattern to query the nature of the intersection of `geo1` and `geo2`. Must contain exactly 9 characters, corresponding to the component (interior, boundary, or exterior) intersections considered by DE-9IM. Supported characters are: * `T` — Components intersect. * `F` — Components do not intersect. * `0` — Component intersection is zero-dimensional. * `1` — Component intersection is 1-dimensional. * `2` — Component intersection is 2-dimensional. * `*` — Specify no restriction on the component intersection.

Examples Determine the nature of the intersection between the point (0.0, 0.0) and the line from -1 0 to 1 0. This function returns the DE-9IM string 0FFFFF102, which means that the boundaries do not intersect at all, but there is evidence of an intersection at the zero, one, and two-dimensional spaces.

SQL

SELECT ST_RELATE(ST_POINT(0.0, 0.0),
    ST_LINESTRING('LINESTRING(-1 0, 1 0)'));

*Output: *0FFFFF102 Determine whether the intersection between the point (0.0, 0.0) and the line from -1 0 to 1 0 follows the DE-9IM pattern string TF*FFF102. This function returns TRUE, which means the nature of the intersection between these two geographies conforms to this pattern.

SQL

SELECT ST_RELATE(ST_POINT(0.0, 0.0),
    ST_LINESTRING('LINESTRING(-1 0, 1 0)'), 'TF*FFF102');

*Output: *TRUE

ST_TOUCHES

Returns TRUE if the only POINT values in common between the two geographic arguments lie in the union of their boundaries. Syntax

SQL

ST_TOUCHES(geo1, geo2)

Argument	Data Type	Description
`geo1`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object for evaluation to determine if it touches `geo2`.
`geo2`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object for evaluation to determine if it touches `geo1`.

Example

SQL

SELECT ST_TOUCHES(
    ST_LINESTRING('LINESTRING(1.0 3.0, 0.0 3.0)'),
    ST_LINESTRING('LINESTRING(1.0 3.0, 2.0 3.0)'));

Output: TRUE

ST_WITHIN

Returns TRUE if the geography specified by the first argument is fully inside the geography specified by the second argument. ST_WITHIN(geo1, geo2) is equivalent to ST_CONTAINS(geo2, geo1). For details, see ST_CONTAINS. Syntax

SQL

ST_WITHIN(geo1, geo2)

Argument	Data Type	Description
`geo1`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object to be compared to `geo2` to evaluate whether it is fully inside this second geography.
`geo2`	`POINT`, `LINESTRING`, or `POLYGON`	A geospatial object to be compared to `geo1` to evaluate whether it contains the first geography.

Example

SQL

SELECT ST_WITHIN(
    ST_POINT(1, 1),
    ST_LINEFROMTEXT('LINESTRING(0 0, 1 1, 2 2)')
);

Output: true Geospatial Data Types Spatial Measurement Spatial Operators

OcientAIQ Unified Data Platform

Documentation Index

​ST_CLUSTERDBSCAN

​ST_CONTAINS

​ST_CONTAINSPROPERLY

​ST_COVERS

​ST_COVEREDBY

​ST_CROSSES

​ST_DISJOINT

​ST_DWITHIN

​ST_EQUALS

​ST_INTERSECTS

​ST_ISCCW

​ST_ISCLOSED

​ST_ISPOLYGONCW

​ST_ISPOLYGONCCW

​ST_ISRING

​ST_ISSIMPLE

​ST_ISVALID

​ST_OVERLAPS

​ST_POINTINSIDECIRCLE

​ST_RELATE

​ST_TOUCHES

​ST_WITHIN

​Related Links

ST_CLUSTERDBSCAN

ST_CONTAINS

ST_CONTAINSPROPERLY

ST_COVERS

ST_COVEREDBY

ST_CROSSES

ST_DISJOINT

ST_DWITHIN

ST_EQUALS

ST_INTERSECTS

ST_ISCCW

ST_ISCLOSED

ST_ISPOLYGONCW

ST_ISPOLYGONCCW

ST_ISRING

ST_ISSIMPLE

ST_ISVALID

ST_OVERLAPS

ST_POINTINSIDECIRCLE

ST_RELATE

ST_TOUCHES

ST_WITHIN

Related Links