Spatial relationship functions

Spatial relationship functions test the relationship between two geometric values g1 and g2 using the exact shapes of the objects. The return values 1 and 0 indicate true and false, respectively, except for the distance functions, which return the distance value.

OceanBase Database 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. Syntax:

ST_Intersects(g1, g2)

ST_Contains

The ST_Contains() function returns 1 or 0 to indicate whether g1 fully contains g2. Syntax:

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) of the geometry parameters. Syntax:

ST_Distance(g1, g2 [, unit])

The ST_Distance() function supports an optional unit parameter that specifies the linear unit for the returned distance value. If the unit parameter is specified, the distance is measured in the units specified by the optional unit parameter.

If any parameter is geometrically invalid, the result may return any number or an error. If the intermediate or final result produces NaN or a negative number, an error ER_GIS_INVALID_DATA is returned. 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

The ST_Distance_Sphere() function returns the minimum spherical distance in meters between Point or MultiPoint parameters on a sphere. Syntax:

ST_Distance_Sphere(g1, g2 [, radius])

The optional radius parameter should be in meters. If the radius parameter is provided but is not a positive number, an error ER_NONPOSITIVE_RADIUS is returned. If the distance exceeds the range of double-precision numbers, an error ER_STD_OVERFLOW_ERROR is returned.

If both geometry parameters are valid Point or MultiPoint values in a geographic spatial reference system (SRS), the returned value is the shortest distance between the two geometry objects on a sphere with radius radius. If omitted, the default radius is the mean radius, defined as (2a + b)/3, where a is the semi-major axis of the SRS and b is the semi-minor axis.

The ST_Distance_Sphere() function supports the following combinations of geometry parameters: Point and Point, or Point and MultiPoint (in any order). If at least one geometry object is not a Point or MultiPoint and its SRID is 0, an error ER_NOT_IMPLEMENTED_FOR_CARTESIAN_SRS is returned. If at least one geometry is not a Point or MultiPoint and its SRID refers to a geographic SRS, an error ER_NOT_IMPLEMENTED_FOR_GEOGRAPHIC_SRS is returned. If any geometry references a projected SRS, an error ER_NOT_IMPLEMENTED_FOR_PROJECTED_SRS is returned.

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

The ST_IsValid() function returns 1 if the geometry parameter is valid, and 0 if the geometry parameter is invalid. Geometry validity is defined by the Open Geospatial Consortium (OGC) specification. Syntax:

ST_IsValid(g)

The only valid empty geometry object is represented by an empty geometry collection value. In this case, ST_IsValid() returns 1, and the EMPTY value, such as POINT EMPTY, is not supported in the spatial data specification.

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

The ST_Within() function returns 1 or 0 to indicate whether g1 is within g2 in space. It has the opposite relationship to ST_Contains(). Syntax:

ST_Within(g1, g2)

ST_Crosses

The ST_Crosses function returns 1 or 0 to indicate whether g1 crosses g2 in space. It returns 1 if g1 and g2 cross, and 0 if they do not. 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 geometry objects, but they have slightly different definitions and purposes.

• The ST_Intersects(g1, g2) function determines whether two geometry objects intersect, meaning they share at least one common point (including boundaries and interiors).

  As long as the two objects have any overlapping parts, whether they are points, lines, or areas, ST_Intersects(g1, g2) returns 1.

• The ST_Crosses(g1, g2) function determines whether two geometry objects "cross" each other, with a specific definition in geometry: one object must partially pass through the interior of another object.

Specifically, for two line geometry objects, if they intersect at two or more points and cross internally, ST_Crosses(g1, g2) returns 1. For a line and an area geometry object, if the line passes through the interior of the area, ST_Crosses(g1, g2) returns 1.

Unlike ST_Intersects(g1, g2), ST_Crosses(g1, g2) does not consider cases of complete overlap or touching boundaries.

While ST_Intersects(g1, g2) detects any type of intersection between two geometry objects, ST_Crosses(g1, g2) specifically detects 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 routes that cover or touch the land, use ST_Intersects(g1, g2).

Syntax:

ST_Crosses(g1,g2)

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 determines whether a linear geometry object (in this case, a line from point (1,1) to point (11,11)) crosses a polygon geometry object (here, a square formed by the points (0,0), (0,10), (10,10), (10,0), and (0,0)). The result is 1, indicating that the line crosses the polygon.

The 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 geometry objects g1 and g2 partially overlap but are not completely contained within each other. It returns 1 if g1 and g2 overlap, and 0 if they do not overlap or if one is completely contained within the other. The ST_Overlaps(g1,g2) function supports spatial indexes.

For two line geometry objects, if they share a segment but neither line is completely contained within the other, the ST_Overlaps(g1,g2) function returns 1. For two polygon geometry objects, if they at least partially overlap and each polygon has a portion that does not overlap with the other, the ST_Overlaps(g1,g2) function returns 1.

Syntax:

ST_Overlaps(g1,g2)

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, 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 (a smaller square) inside it. 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 of the first polygon but is not completely contained within it, the ST_Overlaps function returns 1.

The 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 two geometry objects A and B share at least one common point on their boundaries and do not intersect internally. The _ST_Touches(geometry A, geometry B) function supports spatial indexes.

Note that if both geometry objects are of the Point type, the _ST_Touches(geometry A, geometry B) function returns 0. This is because point geometry objects do not have boundaries, so two points cannot be considered as "touching." They are either identical (exactly at the same location) or completely independent.

Syntax:

_ST_Touches(geometry A, geometry B)

Example:

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 determines whether a line string geometry object (LineString) and a point geometry object (Point) touch in space. Since the point (0 2) is the endpoint of the line (0 0, 1 1, 0 2), the result 1 indicates that the two objects touch.

The 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 geometry objects A and B are spatially equal. This means that the two geometry objects contain the same set of points, and the relative positions of each point are the same in both geometry objects. However, the order of the points does not need to be exactly the same.

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

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

Syntax:

ST_Equals(geometry A, geometry B);

Example:

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 determines 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 it passes through an intermediate point (5 5). Although the second line has an extra vertex, the two lines cover the same set of points in space, so the ST_Equals function considers them spatially equal and returns 1.

The 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 geometry parameter in a spatial relationship function is not NULL in the following cases:

• If any parameter is NULL or if any geometry parameter is an empty geometry, the return value is NULL.

• If the syntax of any geometry parameter is incorrect, an error ER_GIS_INVALID_DATA is returned.

• If any geometry parameter belongs to an undefined spatial reference system (SRS), an error ER_SRS_NOT_FOUND is returned.

• For functions with multiple geometry parameters, if these parameters are not in the same SRS, an error ER_GIS_DIFFERENT_SRIDS is returned.

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

• For geographic SRS geometry parameters, if the longitude or latitude of any parameter is out of range (in degrees, or in the corresponding units if the SRS uses other units), an error occurs:

  • If the longitude value is not in the range [-180, 180], an error ER_GEOMETRY_PARAM_LONGITUDE_OUT_OF_RANGE is returned.

  • If the latitude value is not in the range [-90, 90], an error ER_GEOMETRY_PARAM_LATITUDE_OUT_OF_RANGE is returned.

• The precision of coordinates in spatial calculations cannot exceed (-9.223e18, 9.223e18). Otherwise, an error occurs:

  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'