Spatial Relationships
spatial relationship functions use arguments to test for different types of spatial relationships.
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 not necessary 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 that is not included in a cluster.
Syntax
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
Output: 1
Returns TRUE if the first geographic argument, geo1, contains the second geographic argument, geo2.
geo1 contains geo2 only if all of this 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
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
Output: true
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
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 that represents 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.
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.
Output: FALSE
Returns TRUE if no POINT in geo2 is outside of geo1.
Returns NULL if either geo1 or geo2 is NULL.
Syntax
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
Output: TRUE
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
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
Output: TRUE
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
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
Output: TRUE
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
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
Output: TRUE
Returns TRUE if the geographies are within a specified distance in meters.
Syntax
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
Output: FALSE
Returns TRUE if both geographies are spatially equal.
Syntax
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
Output: True
Returns TRUE if the specified geographies intersect, including boundaries.
This function is the opposite of the ST_DISJOINT function.
Syntax
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
Output: FALSE
Alias for ST_ISPOLYGONCCW.
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
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
Output: TRUE
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
Argument | Data Type | Description |
|---|---|---|
poly | POLYGON | A POLYGON object to evaluate to determine whether its point values align in a clockwise direction. |
Example
Output: FALSE
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
Argument | Data Type | Description |
|---|---|---|
poly | POLYGON | A POLYGON object to evaluate to determine whether its point values align in a counter-clockwise direction. |
Example
Output: TRUE
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
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
Output: TRUE
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
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
Output: TRUE
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 by more than one POINT with the same coordinates.
POLYGON values are valid if they are composed by one external ring (shell) and zero or more internal rings. A ring is a LINESTRING where only the first and last POINT intersect to 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
Argument | Data Type | Description |
|---|---|---|
geo | POINT, LINESTRING, or POLYGON | A geospatial object to evaluate to determine if it is a well-formed geography. |
Example
Output: TRUE
Returns TRUE if both geographic arguments are of the same dimension and they intersect each other, but neither contains the other. Returns NULL if either geo1 or geo2 is NULL.
Syntax
Argument | Data Type | Description |
|---|---|---|
geo1 | POINT, LINESTRING, or POLYGON | A geospatial object for evaluation if it is shaped similarly and intersects geo2. |
geo2 | POINT, LINESTRING, or POLYGON | A geospatial object for evaluation if it is shaped similarly and intersects geo1. |
Example
Output: TRUE
Returns TRUE if the geographic object is inside a circle that is centered at the specified point coordinates and with the specified radius.
Syntax
Argument | Data Type | Description |
|---|---|---|
geo | POINT, LINESTRING, or POLYGON | A geospatial object for evaluation 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
Output: TRUE
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
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 against. Must contain exactly 9 characters, corresponding to the component (interior, boundary, or exterior) intersections considered by DE-9IM. Supported characters are:
|
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 don't intersect at all, but there is evidence of intersection at the 0-, 1-, and 2-dimensional spaces.
Output: 0FFFFF102
Determine whether the intersection between the point (0.0, 0.0) and 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.
Output: TRUE
Returns TRUE if the only POINT values in common between the two geographic arguments lie in the union of their boundaries.
Syntax
Argument | Data Type | Description |
|---|---|---|
geo1 | POINT, LINESTRING, or POLYGON | A geospatial object for evaluation if it touches geo2. |
geo2 | POINT, LINESTRING, or POLYGON | A geospatial object for evaluation if it touches geo1. |
Example
Output: TRUE
Alias for ST_CONTAINS.