Spatial relationship functions test the relationship between two geometric values g1 and g2, using precise object shapes. Return values of 1 and 0 indicate true and false, respectively, except that distance functions return distance values.
OceanBase AI Database currently supports the following spatial relationship functions: ST_Intersects(), ST_Contains(), ST_Distance(), ST_Distance_Sphere(), ST_Within(), ST_Crosses(), ST_Overlaps(), _ST_Touches(), and ST_Equals().
ST_Intersects
The ST_Intersects() function returns 1 or 0 to indicate whether g1 intersects g2 in space. The syntax is as follows:
ST_Intersects(g1, g2)
ST_Contains
The ST_Contains() function returns 1 or 0 to indicate whether g1 completely contains g2. The syntax is as follows:
ST_Contains(g1, g2)
ST_Distance
The ST_Distance() function returns the distance between g1 and g2, measured in the length units of the spatial reference system (SRS) with geometric parameters. The syntax is as follows:
ST_Distance(g1, g2 [, unit])
The ST_Distance() function supports an optional unit parameter, which specifies the linear unit for the returned distance value. If the unit parameter is specified, the distance is measured in the units of the optional unit parameter.
If any parameter is geometrically invalid, the result may return any number or an error may occur. If NaN or a negative number is generated in the intermediate or final result, the ER_GIS_INVALID_DATA error is reported. An example is as follows:
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
The ST_Distance_Sphere() function returns the minimum spherical distance between a Point or MultiPoint parameter on a sphere, in meters. The syntax is as follows:
ST_Distance_Sphere(g1, g2 [, radius])
The optional radius parameter must be in meters. If the radius parameter is present but not a positive number, the error ER_NONPOSITIVE_RADIUS is returned. If the distance exceeds the range of double-precision numbers, the error ER_STD_OVERFLOW_ERROR is returned.
If both geometric parameters are valid Point or MultiPoint values in the spatial reference system (SRS), the return value is the shortest distance between the two geometric objects on a sphere with a radius of radius. If omitted, the default radius equals the mean radius, defined as (2a+b)/3, where a is the semi-major axis and b is the semi-minor axis of the SRS.
The ST_Distance_Sphere() function supports the following combinations of geometric parameters: Point and Point, or Point and MultiPoint (in any order). An error ER_NOT_IMPLEMENTED_FOR_CARTESIAN_SRS is returned if at least one geometry is neither a Point nor a MultiPoint and its SRID is 0. An error ER_NOT_IMPLEMENTED_FOR_GEOGRAPHIC_SRS is returned if at least one geometry is neither a Point nor a MultiPoint and its SRID refers to a geographic SRS. An error ER_NOT_IMPLEMENTED_FOR_ProjectED_SRS is returned if any geometry references a projected SRS.
An example is as follows:
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 geometry specified as a parameter to the ST_IsValid() function is valid, the function returns 1. If the geometry is invalid, the function returns 0. Geometry validity is defined by the OGC standard. The syntax is as follows:
ST_IsValid(g)
The only valid NULL geometric object is represented as a set of NULL geometric objects. In this case, ST_IsValid() returns 1. The EMPTY value in the spatial data specification, such as POINT EMPTY, is 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 has the opposite relationship to ST_Contains(). The syntax is as follows:
ST_Within(g1, g2)
ST_Crosses
The ST_Crosses function returns 1 or 0 to indicate whether g1 intersects g2 in space. If g1 and g2 intersect, it returns 1; if they do not intersect, it returns 0. The ST_Crosses function supports spatial indexes.
Both ST_Crosses(g1, g2) and ST_Intersects(g1, g2) are spatial relationship functions used to determine the spatial relationship between two geometric objects. However, their definitions and applications differ slightly.
The
ST_Intersects(g1, g2)function determines whether two geometric objects intersect in space, meaning they share at least one common point (including edges and interiors).As long as two objects have any overlapping parts, whether they are points, lines, or surfaces,
ST_Intersects(g1, g2)returns 1.The
ST_Crosses(g1, g2)function determines whether two geometric objects "cross," which has a specific definition in geometry: one object must partially pass through the interior of the other.
Specifically, for two linear geometric objects, ST_Crosses(g1, g2) returns 1 if they intersect at more than two points and internally. For a line and a plane geometric object, ST_Crosses(g1, g2) returns 1 if the line passes through the interior of the plane.
Unlike ST_Intersects(g1, g2), ST_Crosses(g1, g2) does not consider full overlap or edge-touching between the two objects.
ST_Intersects(g1, g2) checks whether two geometric objects have any type of intersection, while ST_Crosses(g1, g2) specifically checks whether one object crosses another. For example, if you want to know whether a road crosses a piece of land, use ST_Crosses(g1, g2). If you need to find all roads that cover or touch the land, use ST_Intersects(g1, g2).
The syntax is as follows:
ST_Crosses(g1,g2)
An example is as follows:
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 determines whether a linear geometric object (in this case, a line from point (1,1) to point (11,11)) intersects a polygonal geometric object (a square formed by points (0,0), (0,10), (10,10), (10,0), and (0,0)). The function returns 1, indicating that the line intersects 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
The ST_Overlaps(g1,g2) function returns 1 or 0 to indicate whether two geometric objects g1 and g2 partially overlap in space without completely containing each other. If g1 and g2 overlap, the function returns 1; if they do not overlap or one completely contains the other, it returns 0. The ST_Overlaps(g1,g2) function supports spatial indexes.
For two linear geometric objects, if they share a segment but neither line completely contains the other, ST_Overlaps(g1,g2) returns 1. For planar geometric objects (such as polygons), if two polygons overlap at least partially, and each polygon has a portion that does not overlap with the other, ST_Overlaps(g1,g2) returns 1.
The syntax is as follows:
ST_Overlaps(g1,g2)
An example is as follows:
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, the ST_Overlaps function determines 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 (the small square inside the space), while 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. Since the second polygon overlaps with the interior area of the first polygon but is not completely contained within it, the ST_Overlaps function 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
The _ST_Touches(geometry A, geometry B) function determines whether the boundaries of two geometric objects A and B have at least one common point and do not intersect internally. This function supports spatial indexes.
Note that if both geometric objects are of the Point type, the _ST_Touches(geometry A, geometry B) function returns 0. This is because a point geometry object has no boundary, so two points cannot be considered "in contact"; they are either identical (i.e., have exactly the same location) or completely independent.
The syntax is as follows:
_ST_Touches(geometry A, geometry B)
An example is as follows:
obclient > SELECT _ST_Touches(st_geomfromtext('LINESTRING(0 0, 1 1, 0 2)'), st_geomfromtext('POINT(0 2)'));
In this example, the _ST_Touches function is used to determine whether a linear geometry object (LineString) and a point geometry object (Point) are spatially touching. Since the point (0, 2) is the endpoint of the line (0, 0, 1, 1, 0, 2), the return value of 1 indicates that the two objects are touching.
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
The ST_Equals(geometry A, geometry B) function determines whether two geometric objects A and B are spatially equal. That is, these two geometric objects contain the same set of points, and the relative position of each point is the same in both objects. However, this function does not require the order of the points to be identical.
In other words, if two geometric objects occupy exactly the same spatial range and have identical shape and size, ST_Equals returns 1. If the two geometric objects are different in any way—even by a slight difference in size or shape—the ST_Equals function returns 0.
The ST_Equals(geometry A, geometry B) function supports spatial indexes.
The syntax is as follows:
ST_Equals(geometry A, geometry B);
An example is as follows:
obclient > SELECT ST_Equals(ST_GeomFromText('LINESTRING(0 0, 10 10)'), ST_GeomFromText('LINESTRING(0 0, 5 5, 10 10)'));
In this example, the ST_Equals function is used to determine whether two lines are spatially equal. The first line is a simple straight line from (0, 0) to (10, 10). The second line also goes from (0, 0) to (10, 10), but passes through an intermediate point (5, 5). Although the second line contains an additional vertex, because both lines cover the same set of points in space, ST_Equals determines that these two lines are spatially equivalent 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
The return value of a geometric parameter in a spatial relationship function is never NULL, except in the following cases:
If any parameter is
NULLor any geometric parameter is an empty geometry, the return value isNULL.If the syntax of any geometric parameter is incorrect, the
ER_GIS_INVALID_DATAerror is returned.If any geometric parameter belongs to an undefined Spatial Reference System (SRS), the error
ER_SRS_NOT_FOUNDis reported.For functions that use multiple geometric parameters, if these parameters are not from the same SRS, an error
ER_GIS_DIFFERENT_SRIDSis reported.If any geometric parameter is invalid, the result may be True or False, or an error may occur.
For the geometric parameters of a geographic SRS, an error occurs if the longitude or latitude of any parameter exceeds its range (in degrees; if the SRS uses other units, the corresponding values in those units are used). The error message is as follows:
If the longitude value is not within the range (-180, 180], the error
ER_GEOMETRY_PARAM_LONGITUDE_OUT_OF_RANGEis returned.If the latitude value is not within the range [−90, 90], the error
ER_GEOMETRY_PARAM_LATITUDE_OUT_OF_RANGEis returned.
The coordinate precision for spatial computation cannot exceed (-9.223e18, 9.223e18). Otherwise, an error will be reported:
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'