Spatial relation functions

A spatial relation function tests the relations between geometric 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(), and ST_Within().

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 completely contains g2. Syntax:

ST_Contains(g1, g2)

ST_Distance

The ST_Distance() function returns the distance between g1 and g2, in the length unit of the spatial reference system (SRS) of the geometric argument. Syntax:

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. 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 between two Point or MultiPoint values on a sphere, in meters. Syntax:

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 geometric arguments are valid Point or MultiPoint values in an SRS, the return value is the shortest distance between the two geometric 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 geometric objects is neither a Point nor a MultiPoint and its SRID is 0, the ER_NOT_IMPLEMENTED_FOR_CARTESIAN_SRS error is returned. If at least one of the geometric objects is neither a Point nor a MultiPoint and its SRID indicates a geographic SRS, the ER_NOT_IMPLEMENTED_FOR_GEOGRAPHIC_SRS error is returned. If any geometric object references a projected SRS, the ER_NOT_IMPLEMENTED_FOR_PROJECTED_SRS error 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

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

ST_IsValid(g)

The only valid empty geometric object is an empty collection of geometric 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

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

ST_Within(g1, g2)

Notes

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

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

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

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

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

• If any geometric 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.223 e18). 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'