Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Learn about geometry relations in MariaDB Server. This section details SQL functions for determining spatial relationships between geometric objects, such as ST_Intersects and ST_Contains.
Synonym for ST_DISJOINT. Checks if two geometries are disjoint, meaning they have no points in common.
Disjoint(g1,g2)Returns 1 or 0 to indicate whether g1 is spatially disjoint from (does not intersect) g2.
DISJOINT() tests the opposite relationship to .
DISJOINT() is based on the original MySQL implementation and uses object bounding rectangles, while uses object shapes.
This page is licensed: GPLv2, originally from
0g1g2This tests the opposite relationship to Within().
This page is licensed: GPLv2, originally from fill_help_tables.sql
Contains(g1,g2)Synonym for ST_CROSSES. Checks if two geometries cross, meaning they share some interior points but not all, and the intersection has a lower dimension.
Crosses(g1,g2)Returns 1 if g1 spatially crosses g2. Returns NULL if g1 is a or a , or if g2 is a or a . Otherwise, returns 0.
The term spatially crosses denotes a spatial relation between two given geometries that has the following properties:
The two geometries intersect.
Their intersection results in a geometry that has a dimension that is one less than the maximum dimension of the two given geometries.
Their intersection is not equal to either of the two given geometries.
CROSSES() is based on the original MySQL implementation, and uses object bounding rectangles, while uses object shapes.
This page is licensed: GPLv2, originally from
Calculates the spherical distance between two points or geometries. Uses a spherical earth model to determine the minimum distance in meters.
ST_DISTANCE_SPHERE(g1,g2,[r])Returns the spherical distance in meters between two geometries (point or multipoint) on a sphere. The optional radius r is in meters, must be positive, and defaults to the Earth's radius (6370986 meters) if not specified. If either of the two geometries are not valid, NULL is returned.
This page is licensed: CC BY-SA / Gnu FDL
g1g2ST_CONTAINS() uses object shapes, while CONTAINS(), based on the original MySQL implementation, uses object bounding rectangles.
ST_CONTAINS tests the opposite relationship to ST_WITHIN().
This page is licensed: CC BY-SA / Gnu FDL
ST_CONTAINS(g1,g2)0g1g2This tests the opposite relationship as ST_CONTAINS().
ST_WITHIN() uses object shapes, while WITHIN(), based on the original MySQL implementation, uses object bounding rectangles.
This page is licensed: CC BY-SA / Gnu FDL
ST_WITHIN(g1,g2)g1g2WITHIN() is based on the original MySQL implementation, and uses object bounding rectangles, while ST_WITHIN() uses object shapes.
This page is licensed: GPLv2, originally from fill_help_tables.sql
Within(g1,g2)SET @zenica = ST_GeomFromText('POINT(17.907743 44.203438)');
SET @sarajevo = ST_GeomFromText('POINT(18.413076 43.856258)');
SELECT ST_Distance_Sphere(@zenica, @sarajevo);
+----------------------------------------+
| ST_Distance_Sphere(@zenica, @sarajevo) |
+----------------------------------------+
| 55878.59337591705 |
+----------------------------------------+
SELECT ST_Distance_Sphere(@zenica, @sarajevo, 6370986);
+-------------------------------------------------+
| ST_Distance_Sphere(@zenica, @sarajevo, 6370986) |
+-------------------------------------------------+
| 55878.59337591705 |
+-------------------------------------------------+
SELECT ST_Distance_Sphere(@zenica, @sarajevo, 200);
+---------------------------------------------+
| ST_Distance_Sphere(@zenica, @sarajevo, 200) |
+---------------------------------------------+
| 1.754158410516584 |
+---------------------------------------------+SET @g1 = ST_GEOMFROMTEXT('POLYGON((175 150, 20 40, 50 60, 125 100, 175 150))');
SET @g2 = ST_GEOMFROMTEXT('POINT(174 149)');
SELECT ST_CONTAINS(@g1,@g2);
+----------------------+
| ST_CONTAINS(@g1,@g2) |
+----------------------+
| 1 |
+----------------------+
SET @g2 = ST_GEOMFROMTEXT('POINT(175 151)');
SELECT ST_CONTAINS(@g1,@g2);
+----------------------+
| ST_CONTAINS(@g1,@g2) |
+----------------------+
| 0 |
+----------------------+SET @g1 = ST_GEOMFROMTEXT('POINT(174 149)');
SET @g2 = ST_GEOMFROMTEXT('POLYGON((175 150, 20 40, 50 60, 125 100, 175 150))');
SELECT ST_WITHIN(@g1,@g2);
+--------------------+
| ST_WITHIN(@g1,@g2) |
+--------------------+
| 1 |
+--------------------+
SET @g1 = ST_GEOMFROMTEXT('POINT(176 151)');
SELECT ST_WITHIN(@g1,@g2);
+--------------------+
| ST_WITHIN(@g1,@g2) |
+--------------------+
| 0 |
+--------------------+SET @g1 = GEOMFROMTEXT('POINT(174 149)');
SET @g2 = GEOMFROMTEXT('POINT(176 151)');
SET @g3 = GEOMFROMTEXT('POLYGON((175 150, 20 40, 50 60, 125 100, 175 150))');
SELECT within(@g1,@g3);
+-----------------+
| within(@g1,@g3) |
+-----------------+
| 1 |
+-----------------+
SELECT within(@g2,@g3);
+-----------------+
| within(@g2,@g3) |
+-----------------+
| 0 |
+-----------------+This page is licensed: CC BY-SA / Gnu FDL
ST_DISTANCE(g1,g2)SELECT ST_Distance(POINT(1,2),POINT(2,2));
+------------------------------------+
| ST_Distance(POINT(1,2),POINT(2,2)) |
+------------------------------------+
| 1 |
+------------------------------------+Checks if two geometries are disjoint. Returns 1 if the geometries share no points, 0 otherwise.
ST_DISJOINT(g1,g2)Returns 1 or 0 to indicate whether geometry g1 is spatially disjoint from (does not intersect with) geometry g2.
ST_DISJOINT() uses object shapes, while , based on the original MySQL implementation, uses object bounding rectangles.
ST_DISJOINT() tests the opposite relationship to .
This page is licensed: CC BY-SA / Gnu FDL
SET @g1 = ST_GEOMFROMTEXT('POINT(0 0)');
SET @g2 = ST_GEOMFROMTEXT('LINESTRING(2 0, 0 2)');
SELECT ST_DISJOINT(@g1,@g2);
+----------------------+
| ST_DISJOINT(@g1,@g2) |
+----------------------+
| 1 |
+----------------------+
SET @g2 = ST_GEOMFROMTEXT('LINESTRING(0 0, 0 2)');
SELECT ST_DISJOINT(@g1,@g2);
+----------------------+
| ST_DISJOINT(@g1,@g2) |
+----------------------+
| 0 |
+----------------------+Checks if two geometries cross. Returns 1 if they intersect but one does not cover the other, and the intersection dimension is less than the maximum dimension.
ST_CROSSES(g1,g2)Returns 1 if geometry g1 spatially crosses geometry g2. Returns NULL if g1 is a or a , or if g2 is a or a . Otherwise, returns 0.
The term spatially crosses denotes a spatial relation between two given geometries that has the following properties:
The two geometries intersect.
Their intersection results in a geometry that has a dimension that is one less than the maximum dimension of the two given geometries.
Their intersection is not equal to either of the two given geometries.
ST_CROSSES() uses object shapes, while , based on the original MySQL implementation, uses object bounding rectangles.
This page is licensed: CC BY-SA / Gnu FDL
SET @g1 = ST_GEOMFROMTEXT('LINESTRING(174 149, 176 151)');
SET @g2 = ST_GEOMFROMTEXT('POLYGON((175 150, 20 40, 50 60, 125 100, 175 150))');
SELECT ST_CROSSES(@g1,@g2);
+---------------------+
| ST_CROSSES(@g1,@g2) |
+---------------------+
| 1 |
+---------------------+
SET @g1 = ST_GEOMFROMTEXT('LINESTRING(176 149, 176 151)');
SELECT ST_CROSSES(@g1,@g2);
+---------------------+
| ST_CROSSES(@g1,@g2) |
+---------------------+
| 0 |
+---------------------+Synonym for ST_INTERSECTS. Checks if two geometries intersect, meaning they share at least one common point.
INTERSECTS(g1,g2)Returns 1 or 0 to indicate whether geometry g1 spatially intersects geometry g2.
INTERSECTS() is based on the original MySQL implementation and uses object bounding rectangles, while uses object shapes.
INTERSECTS() tests the opposite relationship to .
This page is licensed: GPLv2, originally from
Returns the length of a linear geometry. Calculates the total length of a LineString or MultiLineString in its associated spatial reference units.
ST_LENGTH(ls)Returns as a double-precision number the length of the LineString value ls in its associated spatial reference.
This page is licensed: CC BY-SA / Gnu FDL
SET @ls = 'LineString(1 1,2 2,3 3)';
SELECT ST_LENGTH(ST_GeomFromText(@ls));
+---------------------------------+
| ST_LENGTH(ST_GeomFromText(@ls)) |
+---------------------------------+
| 2.82842712474619 |
+---------------------------------+Returns a geometry representing the difference between two geometries. The result contains points from the first geometry that are not in the second.
SET @g1 = POINT(10,10), @g2 = POINT(20,20);
SELECT ST_AsText(ST_Difference(@g1, @g2));
+------------------------------------+
| ST_AsText(ST_Difference(@g1, @g2)) |
+------------------------------------+
| POINT(10 10) |
+------------------------------------+0g1g2EQUALS() is based on the original MySQL implementation and uses object bounding rectangles, while ST_EQUALS() uses object shapes.
MBREQUALS is a synonym for Equals.
This page is licensed: GPLv2, originally from fill_help_tables.sql
MBREQUALS(g1,g2)Synonym for ST_OVERLAPS. Checks if two geometries overlap, sharing some but not all points, and having the same dimension.
OVERLAPS(g1,g2)Returns 1 or 0 to indicate whether g1 spatially overlaps g2.
The term spatially overlaps is used if two geometries of equal dimensions intersect and their intersection results in a geometry of the same dimension but not equal to either of the given geometries.
OVERLAPS() is based on the original MySQL implementation and uses object bounding rectangles, while uses object shapes.
This page is licensed: GPLv2, originally from
Checks if two geometries overlap. Returns 1 if they share space but neither completely contains the other, and they have the same dimension.
ST_OVERLAPS(g1,g2)Returns 1 or 0 to indicate whether geometry g1 spatially overlaps geometry g2.
The term spatially overlaps is used if two geometries of equal dimensions intersect and their intersection results in a geometry of the same dimension but not equal to either of the given geometries.
ST_OVERLAPS() uses object shapes, while , based on the original MySQL implementation, uses object bounding rectangles.
This page is licensed: CC BY-SA / Gnu FDL
Synonym for ST_TOUCHES. Checks if two geometries touch at their boundaries without their interiors intersecting.
Touches(g1,g2)Returns 1 or 0 to indicate whether g1 spatially touches g2. Two geometries spatially touch if the interiors of the geometries do not intersect, but the boundary of one of the geometries intersects either the boundary or the interior of the other.
TOUCHES() is based on the original MySQL implementation and uses object bounding rectangles, while uses object shapes.
This page is licensed: GPLv2, originally from
Returns a geometry representing the difference between two geometries. The result contains points from the first geometry that are not in the second.
SET @g1 = ST_GEOMFROMTEXT('LINESTRING(174 149, 176 151)');
SET @g2 = ST_GEOMFROMTEXT('LINESTRING(176 151, 174 149)');
SELECT ST_EQUALS(@g1,@g2);
+--------------------+
| ST_EQUALS(@g1,@g2) |
+--------------------+
| 1 |
+--------------------+SET @g1 = ST_GEOMFROMTEXT('POINT(0 2)');
SET @g1 = ST_GEOMFROMTEXT('POINT(2 0)');
SELECT ST_EQUALS(@g1,@g2);
+--------------------+
| ST_EQUALS(@g1,@g2) |
+--------------------+
| 0 |
+--------------------+Checks if two geometries touch. Returns 1 if they intersect only at their boundaries and not their interiors.
ST_TOUCHES(g1,g2)Returns 1 or 0 to indicate whether geometry g1 spatially touches geometry g2. Two geometries spatially touch if the interiors of the geometries do not intersect, but the boundary of one of the geometries intersects either the boundary or the interior of the other.
ST_TOUCHES() uses object shapes, while , based on the original MySQL implementation, uses object bounding rectangles.
This page is licensed: CC BY-SA / Gnu FDL
Checks if two geometries intersect. Returns 1 if the geometries share any portion of space (interior or boundary), 0 otherwise.
ST_INTERSECTS(g1,g2)Returns 1 or 0 to indicate whether geometry g1 spatially intersects geometry g2.
ST_INTERSECTS() uses object shapes, while , based on the original MySQL implementation, uses object bounding rectangles.
ST_INTERSECTS() tests the opposite relationship to .
This page is licensed: CC BY-SA / Gnu FDL
SET @g1 = ST_GEOMFROMTEXT('POINT(2 0)');
SET @g2 = ST_GEOMFROMTEXT('LINESTRING(2 0, 0 2)');
SELECT ST_TOUCHES(@g1,@g2);
+---------------------+
| ST_TOUCHES(@g1,@g2) |
+---------------------+
| 1 |
+---------------------+
SET @g1 = ST_GEOMFROMTEXT('POINT(2 1)');
SELECT ST_TOUCHES(@g1,@g2);
+---------------------+
| ST_TOUCHES(@g1,@g2) |
+---------------------+
| 0 |
+---------------------+SET @g1 = ST_GEOMFROMTEXT('POINT(0 0)');
SET @g2 = ST_GEOMFROMTEXT('LINESTRING(0 0, 0 2)');
SELECT ST_INTERSECTS(@g1,@g2);
+------------------------+
| ST_INTERSECTS(@g1,@g2) |
+------------------------+
| 1 |
+------------------------+SET @g2 = ST_GEOMFROMTEXT('LINESTRING(2 0, 0 2)');
SELECT ST_INTERSECTS(@g1,@g2);
+------------------------+
| ST_INTERSECTS(@g1,@g2) |
+------------------------+
| 0 |
+------------------------+