Spatial relationship functions
Spatial relationship functions test the relationship between two geometric values g1 and g2 using the exact shape of the objects. The return values 1 and 0 indicate true and false, respectively, except for the distance function, which returns the distance value.
The spatial relationship functions supported by seekdb are 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) of the geometry parameters. The syntax is as follows:
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 of 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.
Here is an example:
SET @geo1 = ST_GeomFromText('POINT(1 1)', 4230);
Query OK, 0 rows affected (0.001 sec)
SET @geo2 = ST_GeomFromText('POINT(3 3)', 4230);
Query OK, 0 rows affected (0.001 sec)
SELECT ST_Distance(@geo1, @geo2);
+---------------------------+
| ST_Distance(@geo1, @geo2) |
+---------------------------+
| 313709.8158791322 |
+---------------------------+
1 row in set (0.001 sec)
SELECT ST_Distance(@geo1, @geo2, 'metre');
+------------------------------------+
| ST_Distance(@geo1, @geo2, 'metre') |
+------------------------------------+
| 313709.8158791322 |
+------------------------------------+
1 row in set (0.001 sec)
SELECT ST_Distance(@geo1, @geo2, 'foot');
+-----------------------------------+
| ST_Distance(@geo1, @geo2, 'foot') |
+-----------------------------------+
| 1029231.6793934782 |
+-----------------------------------+
1 row in set (0.001 sec)
ST_Distance_Sphere
The ST_Distance_Sphere() function returns the minimum spherical distance in meters between Point or MultiPoint parameters on a sphere. The syntax is as follows:
ST_Distance_Sphere(g1, g2 [, radius])
The optional radius parameter should be specified 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 the radius parameter is 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 geometry parameter combinations of 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 object 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.
Here is an example:
SET @pt1 = ST_GeomFromText('POINT(0 0)');
Query OK, 0 rows affected (0.001 sec)
SET @pt2 = ST_GeomFromText('POINT(180 0)');
Query OK, 0 rows affected (0.001 sec)
SELECT ST_Distance_Sphere(@pt1, @pt2);
+--------------------------------+
| ST_Distance_Sphere(@pt1, @pt2) |
+--------------------------------+
| 20015042.813723423 |
+--------------------------------+
1 row in set (0.001 sec)
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. The syntax is as follows:
ST_IsValid(g)
The only valid empty geometry object is represented by an empty geometry object collection value. In this case, ST_IsValid() returns 1, and GIS EMPTY values, such as POINT EMPTY, are not supported.
SET @ls_test1 = ST_GeomFromText('LINESTRING(0 0,-0.00 0,0.0 0)');
Query OK, 0 rows affected (0.001 sec)
SET @ls_test2 = ST_GeomFromText('LINESTRING(0 0, 1 1)');
Query OK, 0 rows affected (0.001 sec)
SELECT ST_IsValid(@ls_test1);
+------------------+
| ST_IsValid(@ls1) |
+------------------+
| 0 |
+------------------+
1 row in set (0.001 sec)
SELECT ST_IsValid(@ls_test2);
+------------------+
| ST_IsValid(@ls2) |
+------------------+
| 1 |
+------------------+
1 row in set (0.001 sec)
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(). The syntax is as follows:
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 otherwise. 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 different definitions and purposes.
-
The
ST_Intersects(g1, g2)function determines whether two geometry objects intersect in space, meaning they share at least one common point (including boundaries and interiors).As long as the two objects have any overlapping parts, whether points, lines, or areas,
ST_Intersects(g1, g2)returns 1. -
The
ST_Crosses(g1, g2)function determines whether two geometry objects "cross" each other, which has a specific definition in geometry: one object must partially pass through the interior of the other.
Specifically, for two line geometry objects, if they intersect at more than two points and intersect internally, ST_Crosses(g1, g2) returns 1. For a line and a surface geometry object, if the line passes through the interior of the surface, 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.
ST_Intersects(g1, g2) detects any type of intersection between two geometry objects, while ST_Crosses(g1, g2) specifically detects whether one object passes through 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).
The syntax is as follows:
ST_Crosses(g1,g2)
Here is an example:
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 defined by 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 (0.001 sec)
ST_Overlaps
The ST_Overlaps(g1,g2) function returns 1 or 0 to determine whether two geometry objects g1 and g2 partially overlap in space 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 is completely contained within the other, the ST_Overlaps(g1,g2) function returns 1.
For two polygon geometry objects, if they partially overlap and each has a part that does not overlap with the other, the ST_Overlaps(g1,g2) function returns 1.
Syntax:
ST_Overlaps(g1,g2)
Example:
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 small 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 (0.001 sec)
_ST_Touches
The _ST_Touches(geometry A, geometry B) function determines whether the boundaries of two geometry objects A and B share at least one common point 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 to "touch"; they are either identical (exactly in the same position) or completely independent.
Syntax:
_ST_Touches(geometry A, geometry B)
Example:
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 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 result 1 indicates that the two objects are touching.
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 (0.001 sec)
ST_Equals
The ST_Equals(geometry A, geometry B) function determines whether two geometry objects A and B are spatially equal, meaning they contain the same set of points and the relative positions of each point are the same in both geometries. The function does not require the points to be in the same order.
In other words, if two geometry objects occupy the exact same spatial area and have the same shape and size, the ST_Equals function returns 1. If the two geometry objects are different in any way, even if only 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:
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 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 has an extra vertex, since both lines cover the same set of points in space, the ST_Equals function determines that the two lines are 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 (0.001 sec)
Considerations
The return value of a geometry parameter in a spatial relationship function is not NULL in the following cases:
-
If any parameter is
NULLor any geometry parameter is an empty geometry, the return value isNULL. -
If the syntax of any geometry parameter is invalid, an error
ER_GIS_INVALID_DATAis returned. -
If any geometry parameter belongs to an undefined spatial reference system (SRS), an error
ER_SRS_NOT_FOUNDis returned. -
For functions that take multiple geometry parameters, if these parameters are not in the same SRS, an error
ER_GIS_DIFFERENT_SRIDSis 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 unit 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_RANGEis returned. -
If the latitude value is not in the range [−90, 90], an error
ER_GEOMETRY_PARAM_LATITUDE_OUT_OF_RANGEis returned.
-
-
The precision of coordinates in spatial calculations cannot exceed (-9.223e18, 9.223e18). Otherwise, an error occurs:
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'