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_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
Synonym for ST_CONTAINS. Checks if the first geometry completely contains the second geometry, with no points of the second geometry outside the first.
Contains(g1,g2)Returns 1 or 0 to indicate whether a geometry g1 completely contains geometry g2. CONTAINS() is based on the original MySQL implementation and uses object bounding rectangles, while uses object shapes.
This tests the opposite relationship to .
This page is licensed: GPLv2, originally from
0g1g2DISJOINT() tests the opposite relationship to INTERSECTS().
DISJOINT() is based on the original MySQL implementation and uses object bounding rectangles, while ST_DISJOINT() uses object shapes.
This page is licensed: GPLv2, originally from fill_help_tables.sql
Disjoint(g1,g2)Returns a geometry representing the difference between two geometries. The result contains points from the first geometry that are not in the second.
Calculates the shortest distance between two geometries. Returns the minimum Euclidean distance between any two points in the geometries.
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
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 |
+--------------------+0g1g2ST_TOUCHES() uses object shapes, while TOUCHES(), based on the original MySQL implementation, uses object bounding rectangles.
This page is licensed: CC BY-SA / Gnu FDL
ST_TOUCHES(g1,g2)0g1g2TOUCHES() is based on the original MySQL implementation and uses object bounding rectangles, while ST_TOUCHES() uses object shapes.
This page is licensed: GPLv2, originally from fill_help_tables.sql
Touches(g1,g2)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)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 = POINT(10,10), @g2 = POINT(20,20);
SELECT ST_AsText(ST_Difference(@g1, @g2));
+------------------------------------+
| ST_AsText(ST_Difference(@g1, @g2)) |
+------------------------------------+
| POINT(10 10) |
+------------------------------------+SELECT ST_Distance(POINT(1,2),POINT(2,2));
+------------------------------------+
| ST_Distance(POINT(1,2),POINT(2,2)) |
+------------------------------------+
| 1 |
+------------------------------------+Checks if geometry A contains geometry B. Returns 1 if B is completely inside A, 0 otherwise.
ST_CONTAINS(g1,g2)Returns 1 or 0 to indicate whether a geometry g1 completely contains geometry g2.
ST_CONTAINS() uses object shapes, while , based on the original MySQL implementation, uses object bounding rectangles.
ST_CONTAINS tests the opposite relationship to .
This page is licensed: CC BY-SA / Gnu FDL
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
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
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
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 @g1 = ST_GEOMFROMTEXT('POINT(0 2)');
SET @g1 = ST_GEOMFROMTEXT('POINT(2 0)');
SELECT ST_EQUALS(@g1,@g2);
+--------------------+
| ST_EQUALS(@g1,@g2) |
+--------------------+
| 0 |
+--------------------+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('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 @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 |
+---------------------------------+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)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 @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 |
+----------------------+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 |
+-----------------+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(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 |
+------------------------+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 |
+---------------------+