Spatial relation functions

A spatial relation function tests the relations between geometry values g1 and g2 based on precise object shapes. The return values 1 and 0 indicate true and false, respectively, while a distance function returns the distance value.

OceanBase Database supports the following spatial relation functions: ST_Intersects(), ST_Contains(), ST_Distance(), ST_Distance_Sphere(), ST_Within(), ST_Crosses(), ST_Overlaps(), _ST_Touches(), and ST_Equals().

ST_Intersects

ST_Intersects() returns 1 or 0 to indicate whether g1 intersects g2 in space. The syntax is as follows:

ST_Intersects(g1, g2)

ST_Contains

ST_Contains() returns 1 or 0 to indicate whether g1 completely contains g2. The syntax is as follows:

ST_Contains(g1, g2)

ST_Distance

ST_Distance() returns the distance between g1 and g2, in the length unit of the spatial reference system (SRS) of the geometry argument. The syntax is as follows:

ST_Distance(g1, g2 [, unit])

The ST_Distance() function supports an optional unit argument that specifies the linear unit of the distance value to return. If the unit argument is specified, the value is returned in the specified unit.

For any argument invalid in geometry, the function may return any number or an error. If a NaN or negative number is generated as the intermediate or final result, the ER_GIS_INVALID_DATA error is returned. Here is an example:

obclient [test]> SET @geo1 = ST_GeomFromText('POINT(1 1)', 4230);
Query OK, 0 rows affected

obclient [test]> SET @geo2 = ST_GeomFromText('POINT(3 3)', 4230);
Query OK, 0 rows affected

obclient [test]> SELECT ST_Distance(@geo1, @geo2);
+---------------------------+
| ST_Distance(@geo1, @geo2) |
+---------------------------+
|         313709.8158791322 |
+---------------------------+
1 row in set

obclient [test]> SELECT ST_Distance(@geo1, @geo2, 'metre');
+------------------------------------+
| ST_Distance(@geo1, @geo2, 'metre') |
+------------------------------------+
|                  313709.8158791322 |
+------------------------------------+
1 row in set

obclient [test]> SELECT ST_Distance(@geo1, @geo2, 'foot');
+-----------------------------------+
| ST_Distance(@geo1, @geo2, 'foot') |
+-----------------------------------+
|                1029231.6793934782 |
+-----------------------------------+
1 row in set

ST_Distance_Sphere

ST_Distance_Sphere() returns the minimum spherical distance between two Point or MultiPoint values on a sphere, in meters. The syntax is as follows:

ST_Distance_Sphere(g1, g2 [, radius])

The optional radius argument should be in the unit of meters. If the radius argument exists but is set to a negative number, the ER_NONPOSITIVE_RADIUS error occurs. If the distance exceeds the range of a double-precision number, the ER_STD_OVERFLOW_ERROR error is returned.

If both geometry arguments are valid Point or MultiPoint values in an SRS, the return value is the shortest distance between the two geometry objects on the sphere with a radius of radius. If the radius argument is omitted, the average radius is used by default, which is (2a+b)/3, where a is the semi-major axis of the SRS and b is the semi-minor axis.

ST_Distance_Sphere() supports combinations of Point objects, or Point and MultiPoint objects, in any order. If at least one of the geometry objects is neither a Point object nor a MultiPoint object and its SRID is 0, the ER_NOT_IMPLEMENTED_FOR_CARTESIAN_SRS error is returned. If at least one of the geometry objects is neither a Point object nor a MultiPoint object and its SRID indicates a geographic SRS, the ER_NOT_IMPLEMENTED_FOR_GEOGRAPHIC_SRS error is returned. If any geometry object references a projected SRS, the ER_NOT_IMPLEMENTED_FOR_PROJECTED_SRS error is returned.

Here is an example:

obclient [test]> SET @pt1 = ST_GeomFromText('POINT(0 0)');
Query OK, 0 rows affected

obclient [test]> SET @pt2 = ST_GeomFromText('POINT(180 0)');
Query OK, 0 rows affected

obclient [test]> SELECT ST_Distance_Sphere(@pt1, @pt2);
+--------------------------------+
| ST_Distance_Sphere(@pt1, @pt2) |
+--------------------------------+
|             20015042.813723423 |
+--------------------------------+
1 row in set

ST_IsValid

If the argument of the ST_IsValid() function is geometrically valid, 1 is returned. If the argument is geometrically invalid, 0 is returned. Geometric validity is defined by the Open Geospatial Consortium (OGC) Abstract Specification. The syntax is as follows:

ST_IsValid(g)

The only valid empty geometry object is an empty collection of geometry objects. In this case, the ST_IsValid() function returns 1. GIS EMPTY values, such as POINT EMPTY, are not supported.

obclient [test]> SET @ls_test1 = ST_GeomFromText('LINESTRING(0 0,-0.00 0,0.0 0)');
Query OK, 0 rows affected

obclient [test]> SET @ls_test2 = ST_GeomFromText('LINESTRING(0 0, 1 1)');
Query OK, 0 rows affected

obclient [test]> SELECT ST_IsValid(@ls_test1);
+------------------+
| ST_IsValid(@ls1) |
+------------------+
|                0 |
+------------------+
1 row in set

obclient [test]> SELECT ST_IsValid(@ls_test2);
+------------------+
| ST_IsValid(@ls2) |
+------------------+
|                1 |
+------------------+
1 row in set

ST_Within

ST_Within() returns 1 or 0 to indicate whether g1 is spatially within g2. It is opposite to ST_Contains(). The syntax is as follows:

ST_Within(g1, g2)

ST_Crosses

ST_Crosses tests whether geometry objects g1 and g2 spatially cross. The function returns 1 if g1 crosses g2. Otherwise, it returns 0. The ST_Crosses() function supports spatial indexes.

Both ST_Crosses(g1, g2) and ST_Intersects(g1, g2) are spatial relation functions that test the spatial relations between two geometry objects, but their definitions and uses are slightly different.

• ST_Intersects(g1, g2) tests whether two geometry objects intersect each other spatially, which means that the objects share at least one point (including bounding and interior points).

  ST_Intersects(g1, g2) returns 1 as long as the two objects have an overlap, no matter if it is a point, line, or surface.

• ST_Crosses(g1, g2) tests whether two geometry objects cross, that is, whether the interior of one geometry object partially passes through the interior of the other. Specifically, if two lines cross each other at more than two points in their interiors, ST_Crosses(g1, g2) returns 1. If a line passes through the interior of a surface, ST_Crosses(g1, g2) returns 1.

Different from ST_Intersects(g1, g2), ST_Crosses(g1, g2) does not consider full overlaps or boundary touches.

ST_Intersects(g1, g2) tests whether two geometry objects have any kind of intersection, while ST_Crosses(g1, g2) specifically tests whether one geometry object passes through the other. For example, you may use ST_Crosses(g1, g2) to determine whether a road crosses a piece of land, and use ST_Intersects(g1, g2) to find all the routes that cover or touch this piece of land.

The syntax is as follows:

ST_Crosses(g1,g2)

Here is an example:

obclient > select st_crosses(st_geomfromtext('LINESTRING(1 1, 11 11)'), st_geomfromtext('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))'));

In this example, ST_Crosses() tests whether a LineString object, a straight line from point (1,1) to point (11,11), crosses a Polygon object, a square with vertices of (0,0), (0,10), (10,10), and (10,0). The return result is 1, indicating that the straight line crosses the polygon.

The return result is as follows:

+------------------------------------------------------------------------------------------------------------------+
| st_crosses(st_geomfromtext('LINESTRING(1 1, 11 11)'), st_geomfromtext('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))')) |
+------------------------------------------------------------------------------------------------------------------+
|                                                                                                                1 |
+------------------------------------------------------------------------------------------------------------------+
1 row in set

ST_Overlaps

ST_Overlaps(g1,g2) tests whether two geometry objects g1 and g2 spatially overlap. The containment relation is excluded. The function returns 1 if g1 and g2 overlap, and returns 0 if they do not overlap or one completely contains the other. The ST_Overlaps(g1,g2) function supports spatial indexes.

If two lines share a segment but do not contain each other, ST_Overlaps(g1,g2) returns 1. If two surfaces (such as polygons) partially overlap but do not coincide with each other, ST_Overlaps(g1,g2) returns 1.

The syntax is as follows:

ST_Overlaps(g1,g2)

Here is an example:

obclient > SELECT ST_OVERLAPS(ST_GEOMFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,0 0,0 0),(2 2,2 4,4 4,4 2,2 2))'), ST_GEOMFROMTEXT('POLYGON((0 0,0 5,5 5,5 0,0 0))'));

In this example, ST_Overlaps() tests whether two polygons overlap. The first polygon POLYGON((0 0,0 10,10 10,10 0,0 0,0 0),(2 2,2 4,4 4,4 2,2 2)) is a 10 ✖️ 10 square with a 2 ✖️ 2 hole (a small square inside). The second polygon POLYGON((0 0,0 5,5 5,5 0,0 0)) is a 5 ✖️ 5 square located in the lower-left corner of the first polygon. The second polygon overlaps the interior of the first polygon but is not fully contained in the first polygon. Therefore, ST_Overlaps() returns 1.

The return result is as follows:

+-------------------------------------------------------------------------------------------------------------------------------------------------+
| ST_OVERLAPS(ST_GEOMFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,0 0,0 0),(2 2,2 4,4 4,4 2,2 2))'), ST_GEOMFROMTEXT('POLYGON((0 0,0 5,5 5,5 0,0 0))')) |
+-------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                                                                               1 |
+-------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

_ST_Touches

_ST_Touches(geometry A, geometry B) tests whether two geometry objects A and B share at least one point on the boundary while their interiors do not intersect. The _ST_Touches(geometry A, geometry B) function supports spatial indexes.

Take note that if both input geometry objects are Point objects, _ST_Touches(geometry A, geometry B) returns 0. This is because points do not have boundaries, and two points therefore cannot be considered “touching”. They are either identical (that is, at the same position) or completely independent.

The syntax is as follows:

_ST_Touches(geometry A, geometry B)

Here is an example:

obclient > SELECT _ST_Touches(st_geomfromtext('LINESTRING(0 0, 1 1, 0 2)'), st_geomfromtext('POINT(0 2)'));

In this example, _ST_Touches() tests whether a LineString object touches a Point object in space. Because the Point object (0 2) is at the end of the LineString object (0 0, 1 1, 0 2), the function returns 1, indicating that the two objects spatially touch.

The return result is as follows:

+------------------------------------------------------------------------------------------+
| _ST_Touches(st_geomfromtext('LINESTRING(0 0, 1 1, 0 2)'), st_geomfromtext('POINT(0 2)')) |
+------------------------------------------------------------------------------------------+
|                                                                                        1 |
+------------------------------------------------------------------------------------------+
1 row in set

ST_Equals

ST_Equals(geometry A, geometry B) tests whether two geometry objects A and B are spatially equal. That is, the function tests whether the two objects contain the same set of points that are at the same relative positions, but the function does not require the same sequence of points.

In other words, if two geometry objects occupy the same spatial area in the same shape and size, ST_Equals returns 1. If two geometry objects differ in any way, even slightly in size or shape, ST_Equals returns 0.

The ST_Equals(geometry A, geometry B) function supports spatial indexes.

The syntax is as follows:

ST_Equals(geometry A, geometry B);

Here is an example:

obclient > SELECT ST_Equals(ST_GeomFromText('LINESTRING(0 0, 10 10)'), ST_GeomFromText('LINESTRING(0 0, 5 5, 10 10)'));

In this example, ST_Equals() tests whether two LineString objects are spatially equal. The first LineString object is a simple straight line from (0 0) to (10 10). The second LineString object is also from (0 0) to (10 10), but passes through an intermediate point (5 5). Although the second LineString object contains an extra vertex, it covers the same set of points in space as the first LineString object. Therefore, ST_Equals() determines that the two LineString objects are spatially equal and returns the result 1.

The return result is as follows:

+------------------------------------------------------------------------------------------------------+
| ST_Equals(ST_GeomFromText('LINESTRING(0 0, 10 10)'), ST_GeomFromText('LINESTRING(0 0, 5 5, 10 10)')) |
+------------------------------------------------------------------------------------------------------+
|                                                                                                    1 |
+------------------------------------------------------------------------------------------------------+
1 row in set

Considerations for parameters

The return value for a geometry argument in a spatial relation function is non-NULL, except in the following cases:

• If any argument is NULL or any geometry argument is an empty geometry object, the return value is NULL.

• If any geometry argument is in an incorrect syntax format, the ER_GIS_INVALID_DATA error is returned.

• If any geometry argument belongs to an undefined SRS, the ER_SRS_NOT_FOUND error is returned.

• For a function that has multiple geometry arguments in different SRSs, the ER_GIS_DIFFERENT_SRIDS error is returned.

• If any geometry argument is invalid, the result may be True or False, or an error may be returned.

• For geographic SRS geometry arguments, if the longitude or latitude of any argument exceeds the valid range in degrees or in other units used by the SRS, an error occurs.

  • If the longitude value is not within the range of (-180, 180], the ER_GEOMETRY_PARAM_LONGITUDE_OUT_OF_RANGE error is returned.

  • If the latitude value is not within the range of [−90, 90], the ER_GEOMETRY_PARAM_LATITUDE_OUT_OF_RANGE error is returned.

• The coordinate precision in spatial calculation must be within the range of (-9.223e18, 9.223e18). Otherwise, an error is returned.

  obclient [test]> SELECT ST_CONTAINS(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 1e+19,0 0,0 0,0 1e+19)))'), ST_GEOMFROMTEXT('POLYGON((0 0,0 0,0 0,0 0))'));
  ERROR 1690 (22003): coordinate value is out of range in 'st_contains'