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_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
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)g1g2NULLg1g2 is a 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 ST_CROSSES() uses object shapes.
This page is licensed: GPLv2, originally from fill_help_tables.sql
0g1g2OVERLAPS() is based on the original MySQL implementation and uses object bounding rectangles, while ST_OVERLAPS() uses object shapes.
This page is licensed: GPLv2, originally from fill_help_tables.sql
OVERLAPS(g1,g2)0g1g2This tests the opposite relationship to Within().
This page is licensed: GPLv2, originally from fill_help_tables.sql
Contains(g1,g2)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)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 |
+----------------------+Crosses(g1,g2)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
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
This page is licensed: CC BY-SA / Gnu FDL
ST_DIFFERENCE(g1,g2)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) |
+------------------------------------+0g1g2ST_INTERSECTS() uses object shapes, while INTERSECTS(), based on the original MySQL implementation, uses object bounding rectangles.
ST_INTERSECTS() tests the opposite relationship to ST_DISJOINT().
This page is licensed: CC BY-SA / Gnu FDL
ST_INTERSECTS(g1,g2)g1g2ST_DISJOINT() uses object shapes, while DISJOINT(), based on the original MySQL implementation, uses object bounding rectangles.
ST_DISJOINT() tests the opposite relationship to ST_INTERSECTS().
This page is licensed: CC BY-SA / Gnu FDL
ST_DISJOINT(g1,g2)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 |
+------------------------+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
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
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
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
Synonym for ST_WITHIN. Checks if the first geometry is completely enclosed by the second geometry.
Within(g1,g2)Returns 1 or 0 to indicate whether g1 is spatially within g2. This tests the opposite relationship as .
WITHIN() 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 geometry A is within geometry B. Returns 1 if A is completely inside B, 0 otherwise. This is the inverse of ST_CONTAINS.
ST_WITHIN(g1,g2)Returns 1 or 0 to indicate whether geometry g1 is spatially within geometry g2.
This tests the opposite relationship as .
ST_WITHIN() uses object shapes, while , based on the original MySQL implementation, uses object bounding rectangles.
This page is licensed: CC BY-SA / Gnu FDL
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 @ls = 'LineString(1 1,2 2,3 3)';
SELECT ST_LENGTH(ST_GeomFromText(@ls));
+---------------------------------+
| ST_LENGTH(ST_GeomFromText(@ls)) |
+---------------------------------+
| 2.82842712474619 |
+---------------------------------+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(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 |
+--------------------+0g1g2ST_EQUALS() uses object shapes, while EQUALS(), based on the original MySQL implementation, uses object bounding rectangles.
This page is licensed: CC BY-SA / Gnu FDL
ST_EQUALS(g1,g2)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('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 |
+---------------------+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 |
+-----------------+SET @g1 = ST_GEOMFROMTEXT('POINT(0 2)');
SET @g1 = ST_GEOMFROMTEXT('POINT(2 0)');
SELECT ST_EQUALS(@g1,@g2);
+--------------------+
| ST_EQUALS(@g1,@g2) |
+--------------------+
| 0 |
+--------------------+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
Calculates the shortest distance between two geometries. Returns the minimum Euclidean distance between any two points in the geometries.
SELECT ST_Distance(POINT(1,2),POINT(2,2));
+------------------------------------+
| ST_Distance(POINT(1,2),POINT(2,2)) |
+------------------------------------+
| 1 |
+------------------------------------+