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(), and ST_Within().

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)

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'