All pages
Powered by GitBook
Couldn't generate the PDF for 180 pages, generation stopped at 100.
Extend with 50 more pages.
1 of 100

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Loading...

Geometry Statements

Learn about geometry constructors in MariaDB Server. This section details SQL functions for creating spatial data types like points, lines, and polygons, enabling geospatial data management.

Miscellaneous GIS functions

Explore miscellaneous GIS functions in MariaDB Server. This section details various SQL functions that support geographic information system operations and spatial data analysis.

Geometry Relations

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.

ST_POINTONSURFACE

Returns a Point guaranteed to lie on the surface of the given geometry. This standard function finds a representative point strictly within the geometry's area.

Syntax

ST_PointOnSurface(g)
PointOnSurface(g)

Description

Given a geometry, returns a POINT guaranteed to intersect a surface. However, see MDEV-7514.

ST_PointOnSurface() and PointOnSurface() are synonyms.

This page is licensed: CC BY-SA / Gnu FDL

DIMENSION

Synonym for ST_DIMENSION. Returns the inherent dimension of a geometry object (0 for Point, 1 for LineString, 2 for Polygon).

A synonym for ST_DIMENSION.

This page is licensed: CC BY-SA / Gnu FDL

ENVELOPE

Synonym for ST_ENVELOPE. Returns the Minimum Bounding Rectangle (MBR) for the given geometry as a Polygon.

A synonym for ST_ENVELOPE.

This page is licensed: CC BY-SA / Gnu FDL

SRID

Synonym for ST_SRID. Returns the Spatial Reference Identifier (SRID) integer associated with the geometry.

A synonym for ST_SRID.

This page is licensed: CC BY-SA / Gnu FDL

GEOMETRYCOLLECTION

Constructs a GeometryCollection value from a list of WKB arguments. This function creates a collection containing multiple geometry objects.

Syntax

Description

Constructs a GeometryCollection. If any argument is not a well-formed WKB representation of a geometry, the return value is NULL

PointOnSurface

Returns a Point which is guaranteed to lie on the surface of the given geometry. This is useful for labeling or finding a representative point inside a polygon.

A synonym for .

This page is licensed: CC BY-SA / Gnu FDL

NumGeometries

Synonym for ST_NUMGEOMETRIES. Returns the number of geometries contained in a GeometryCollection.

A synonym for .

This page is licensed: CC BY-SA / Gnu FDL

IsRing

Synonym for ST_ISRING. Checks if a LineString is a ring, meaning it is both closed and simple.

A synonym for .

This page is licensed: CC BY-SA / Gnu FDL

ST_DISTANCE

Calculates the shortest distance between two geometries. Returns the minimum Euclidean distance between any two points in the geometries.

Syntax

Description

Returns the distance between two geometries, or null if not given valid inputs.

ST_RELATE

Tests if two geometries are spatially related according to a given DE-9IM intersection matrix pattern.

Syntax

Description

Returns true if Geometry g1 is spatially related to Geometry

CONVEXHULL

Returns a geometry representing the convex hull of the given geometry value. It computes the smallest convex polygon that contains the entire geometry.

A synonym for .

This page is licensed: CC BY-SA / Gnu FDL

ST_DIFFERENCE

Returns a geometry representing the difference between two geometries. The result contains points from the first geometry that are not in the second.

Syntax

Description

Returns a geometry representing the point set difference of the given geometry values.

ST_SYMDIFFERENCE

Returns a geometry representing the point set symmetric difference of two geometries. It outputs the spatial area present in either geometry but not in both.

Syntax

Description

Returns a geometry that represents the portions of geometry

ST_IsSimple

Checks if the geometry is simple (no self-intersections or anomalous points). Returns 1 if simple, 0 otherwise.

Syntax

Description

Returns true if the given Geometry has no anomalous geometric points, false if it does, or NULL if given a NULL value.

ST_NUMGEOMETRIES

Returns the number of geometries in a GeometryCollection. Returns NULL if the argument is not a GeometryCollection.

Syntax

Description

Returns the number of geometries in the GeometryCollection gc

ST_ENVELOPE

Returns the Minimum Bounding Rectangle (MBR) for the geometry value. The result is returned as a Polygon defined by the corner points.

Syntax

Description

Returns the Minimum Bounding Rectangle (MBR) for the geometry value g

ENDPOINT

Synonym for ST_ENDPOINT. Returns the last point of a LineString geometry.

A synonym for .

This page is licensed: CC BY-SA / Gnu FDL

MBR Definition

Understand Minimum Bounding Rectangles. An MBR is the smallest rectangle that completely encloses a geometry, defined by its minimum and maximum X and Y coordinates.

Description

The MBR (Minimum Bounding Rectangle), or Envelope is the bounding geometry, formed by the minimum and maximum (X,Y) coordinates:

Examples

X

Synonym for ST_X. Returns the X-coordinate value of a Point geometry as a double-precision number.

A synonym for .

This page is licensed: CC BY-SA / Gnu FDL

MBR (Minimum Bounding Rectangle)

Learn about Minimum Bounding Rectangles (MBR) in MariaDB Server. This section details how to calculate and use MBRs for spatial indexing and efficient querying of geometric data.

Polygon Properties

Learn about POLYGON properties in MariaDB Server. This section details SQL functions for retrieving attributes of polygonal spatial objects, such as area, perimeter, and the number of rings.

Point Properties

Learn about POINT properties in MariaDB Server. This section details SQL functions for retrieving attributes of point spatial objects, such as their X and Y coordinates.

.

Examples

This page is licensed: GPLv2, originally from fill_help_tables.sql

GeometryCollection(g1,g2,...)
WKB
CREATE TABLE gis_geometrycollection  (g GEOMETRYCOLLECTION);
SHOW FIELDS FROM gis_geometrycollection;
INSERT INTO gis_geometrycollection VALUES
    (GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')),
    (GeometryFromWKB(AsWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9)))))),
    (GeomFromText('GeometryCollection()')),
    (GeomFromText('GeometryCollection EMPTY'));
Example

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 |
+------------------------------------+
g2
by testing for intersections between the interior, boundary and exterior of the two geometries as specified by the values in intersection matrix pattern
i
.

This page is licensed: CC BY-SA / Gnu FDL

ST_Relate(g1, g2, i)
Example

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)                       |
+------------------------------------+
g1
and geometry
g2
that don't intersect.

Examples

This page is licensed: CC BY-SA / Gnu FDL

ST_SYMDIFFERENCE(g1,g2)
SET @g1 = ST_GEOMFROMTEXT('LINESTRING(10 20, 10 40)');

SET @g2 = ST_GEOMFROMTEXT('LINESTRING(10 15, 10 25)');

SELECT ASTEXT(ST_SYMDIFFERENCE(@g1,@g2));
+----------------------------------------------+
| ASTEXT(ST_SYMDIFFERENCE(@g1,@g2))            |
+----------------------------------------------+
| MULTILINESTRING((10 15,10 20),(10 25,10 40)) |
+----------------------------------------------+

SET @g2 = ST_GeomFromText('LINESTRING(10 20, 10 41)');

SELECT ASTEXT(ST_SYMDIFFERENCE(@g1,@g2));
+-----------------------------------+
| ASTEXT(ST_SYMDIFFERENCE(@g1,@g2)) |
+-----------------------------------+
| LINESTRING(10 40,10 41)           |
+-----------------------------------+
ST_IsSimple() and IsSimple() are synonyms.

Examples

A POINT is always simple.

This page is licensed: CC BY-SA / Gnu FDL

ST_IsSimple(g)
IsSimple(g)
SET @g = 'Point(1 2)';

SELECT ST_ISSIMPLE(GEOMFROMTEXT(@g));
+-------------------------------+
| ST_ISSIMPLE(GEOMFROMTEXT(@g)) |
+-------------------------------+
|                             1 |
+-------------------------------+
.

ST_NumGeometries() and NumGeometries() are synonyms.

Example

This page is licensed: CC BY-SA / Gnu FDL

ST_NumGeometries(gc)
NumGeometries(gc)
SET @gc = 'GeometryCollection(Point(1 1),LineString(2 2, 3 3))';

SELECT NUMGEOMETRIES(GeomFromText(@gc));
+----------------------------------+
| NUMGEOMETRIES(GeomFromText(@gc)) |
+----------------------------------+
|                                2 |
+----------------------------------+
. The result is returned as a Polygon value.

The polygon is defined by the corner points of the bounding box:

ST_ENVELOPE() and ENVELOPE() are synonyms.

Examples

This page is licensed: GPLv2, originally from fill_help_tables.sql

ST_ENVELOPE(g)
ENVELOPE(g)
POLYGON((MINX MINY, MAXX MINY, MAXX MAXY, MINX MAXY, MINX MINY))
This page is licensed: GPLv2, originally from fill_help_tables.sql
((MINX MINY, MAXX MINY, MAXX MAXY, MINX MAXY, MINX MINY))
ST_PointOnSurface
ST_NumGeometries
ST_IsRing
ST_CONVEXHULL
ST_ENDPOINT
ST_X

LINESTRING

Constructs a LineString value from a number of Point values. It creates a linear geometry connecting the specified coordinate points.

Syntax

LineString(pt1,pt2,...)

Description

WKB LineString from WKB Point coordinate data. Constructs a WKB LineString value from a number of WKB Point arguments. If any argument is not a WKB Point, the return value isNULL. If the number of arguments is less than two, the return value is NULL.

Examples

Linestring_Example:

This page is licensed: GPLv2, originally from

MULTIPOLYGON

Constructs a MultiPolygon value from a list of WKB Polygon arguments. This function creates a geometry collection containing multiple polygons.

Syntax

MultiPolygon(poly1,poly2,...)

Description

Constructs a WKB MultiPolygon value from a set of WKB Polygon arguments. If any argument is not a WKB Polygon, the return value is NULL.

Example

MultiPolygon_Example:

This page is licensed: GPLv2, originally from

ST_BUFFER

Returns a geometry representing all points within a given distance of the geometry. This standard-compliant function creates a buffer zone around the object.

Syntax

ST_BUFFER(g1,r)
BUFFER(g1,r)

Description

Returns a geometry that represents all points whose distance from geometry g1 is less than or equal to distance, or radius, r.

Uses for this function could include creating for example a new geometry representing a buffer zone around an island.

BUFFER() is a synonym.

Examples

Determining whether a point is within a buffer zone:

This page is licensed: CC BY-SA / Gnu FDL

POINT

Constructs a Point value using its coordinates. This basic constructor creates a point geometry from given X and Y (and optionally Z/M) values.

Syntax

Point(x,y)

Description

Constructs a WKB Point using the given coordinates.

Examples

Point_Example:

This page is licensed: GPLv2, originally from

ST_DIMENSION

Returns the inherent dimension of the geometry value. Returns 0 for points, 1 for linear geometries, and 2 for surface geometries.

Syntax

ST_Dimension(g)
Dimension(g)

Description

Returns the inherent dimension of the geometry value g. The result can be

Dimension
Definition

ST_Dimension() and Dimension() are synonyms.

Examples

This page is licensed: GPLv2, originally from

ST_UNION

Returns a geometry representing the point set union of two geometries. This function merges multiple spatial objects into a single geometry.

Syntax

ST_UNION(g1,g2)

Description

Returns a geometry that is the union of the geometry g1 and geometry g2.

Examples

This page is licensed: CC BY-SA / Gnu FDL

BOUNDARY

Synonym for ST_BOUNDARY. Returns a geometry representing the closure of the combinatorial boundary of the geometry value.

A synonym for ST_BOUNDARY.

This page is licensed: CC BY-SA / Gnu FDL

IsClosed

Synonym for ST_ISCLOSED. Checks if a LineString's start and end points are the same (closed).

A synonym for ST_IsClosed.

This page is licensed: CC BY-SA / Gnu FDL

POLYGON

Constructs a Polygon value from a list of WKB LineString arguments. It defines a filled shape using an outer ring and optional inner rings (holes).

Syntax

Polygon(ls1,ls2,...)

Description

Constructs a WKB Polygon value from a number of WKB LineString arguments. If any argument does not represent the WKB of a LinearRing (that is, not a closed and simple LineString) the return value is NULL.

Note that according to the OpenGIS standard, a POLYGON should have exactly one ExteriorRing and all other rings should lie within that ExteriorRing and thus be the InteriorRings. Practically, however, some systems, including MariaDB's, permit polygons to have several 'ExteriorRings'. In the case of there being multiple, non-overlapping exterior rings will return 1.

Examples

Non-overlapping 'polygon':

Polygon_Example:

This page is licensed: GPLv2, originally from

ST_OVERLAPS

Checks if two geometries overlap. Returns 1 if they share space but neither completely contains the other, and they have the same dimension.

Syntax

ST_OVERLAPS(g1,g2)

Description

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

ST_GEOMETRYN

Returns the N-th geometry in a GeometryCollection. Geometries are numbered beginning with 1.

Syntax

ST_GeometryN(gc,N)
GeometryN(gc,N)

Description

Returns the N-th geometry in the GeometryCollection gc. Geometries are numbered beginning with 1.

ST_GeometryN() and GeometryN() are synonyms.

Example

This page is licensed: CC BY-SA / Gnu FDL

ST_EQUALS

Returns a geometry representing the difference between two geometries. The result contains points from the first geometry that are not in the second.

Syntax

ST_EQUALS(g1,g2)

Description

Returns 1 or 0 to indicate whether geometry g1 is spatially equal to geometry g2.

ST_EQUALS() uses object shapes, while , based on the original MySQL implementation, uses object bounding rectangles.

Examples

This page is licensed: CC BY-SA / Gnu FDL

ST_TOUCHES

Checks if two geometries touch. Returns 1 if they intersect only at their boundaries and not their interiors.

Syntax

ST_TOUCHES(g1,g2)

Description

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.

Examples

This page is licensed: CC BY-SA / Gnu FDL

NumPoints

Synonym for ST_NUMPOINTS. Returns the number of points in a LineString geometry.

A synonym for ST_NumPoints.

This page is licensed: CC BY-SA / Gnu FDL

MBRDisjoint

Check if two MBRs are disjoint. Returns 1 if the Minimum Bounding Rectangles of the two geometries do not intersect or touch at all.

Syntax

MBRDisjoint(g1,g2)

Description

Returns 1 or 0 to indicate whether the Minimum Bounding Rectangles of the two geometries g1 and g2 are disjoint. Two geometries are disjoint if they do not intersect, that is touch or overlap.

Examples

This page is licensed: GPLv2, originally from

ST_LatFromGeoHash

Decode a Geohash to retrieve the latitude. This function returns the latitude coordinate (Y-axis) from a given Geohash string.

ST_LatFromGeoHash is available from MariaDB 12.0.

Syntax

ST_LatFromGeoHash(geohash)

Description

Decodes a given geohash string and returns the latitude in the interval [90, -90].

If the argument is NULL, the return value is NULL. If the argument is invalid, an ER_INCORRECT_TYPE error is thrown.

The function can be used to generate geohashes.

Examples

See Also

This page is licensed: CC BY-SA / Gnu FDL

ST_LongFromGeoHash

Decode a Geohash to retrieve the longitude. This function returns the longitude coordinate (X-axis) from a given Geohash string.

ST_LongFromGeoHash is available from MariaDB 12.0.

Syntax

Description

Decodes a given geohash string and returns the longitude in the interval [180, -180].

If the argument is NULL, the return value is NULL. If the argument is invalid, an ER_INCORRECT_TYPE error is thrown.

The function can be used to generate geohashes.

Examples

See Also

This page is licensed: CC BY-SA / Gnu FDL

ST_SRID

Returns the Spatial Reference Identifier (SRID) for the geometry. This integer represents the coordinate system used.

Syntax

ST_SRID(g)
SRID(g)

Description

Returns an integer indicating the Spatial Reference System ID for the geometry value g.

In MariaDB, the SRID value is just an integer associated with the geometry value. All calculations are done assuming Euclidean (planar) geometry.

ST_SRID() and SRID() are synonyms.

Examples

This page is licensed: GPLv2, originally from

ST_CONVEXHULL

Returns a geometry representing the convex hull of the given geometry. This standard function computes the smallest convex polygon enclosing the geometry.

Syntax

ST_ConvexHull(g)
ConvexHull(g)

Description

Given a geometry, returns a geometry that is the minimum convex geometry enclosing all geometries within the set. Returns NULL if the geometry value is NULL or an empty value.

ST_ConvexHull() and ConvexHull() are synonyms.

Examples

The ConvexHull of a single point is simply the single point:

This page is licensed: CC BY-SA / Gnu FDL

ST_GEOMETRYTYPE

Returns the name of the geometry type (e.g., 'POINT', 'LINESTRING', 'POLYGON') for the given geometry instance.

Syntax

ST_GeometryType(g)
GeometryType(g)

Description

Returns as a string the name of the geometry type of which the geometry instance g is a member. The name corresponds to one of the instantiable Geometry subclasses.

ST_GeometryType() and GeometryType() are synonyms.

Examples

This page is licensed: GPLv2, originally from

BUFFER

Returns a geometry representing all points within a given distance of the geometry value. This function calculates a buffer zone around the spatial object.

A synonym for ST_BUFFER.

This page is licensed: CC BY-SA / Gnu FDL

ST_IsValid

Check if a geometry is valid. This function returns 1 if the geometry complies with OGC specifications (e.g., no self-intersections), 0 otherwise.

ST_IsValid is available from MariaDB 12.0.

Syntax

Description

Given a geometry input, returns 1 if the argument is geometrically valid according to the OGC specifications, 0 if the argument is not geometrically valid.

Unlike , requires valid GIS data, or ERROR 3037 (22023): Invalid GIS data provided to function st_isvalid is returned.

Examples

A requires both x and y co-ordinates:

See Also

This page is licensed: CC BY-SA / Gnu FDL

MBREqual

Check if two MBRs are identical. Returns 1 if the Minimum Bounding Rectangles of both geometries share the exact same coordinates.

Syntax

MBREqual(g1,g2)
MBREquals(g1,g2)

Description

Returns 1 or 0 to indicate whether the Minimum Bounding Rectangles of the two geometries g1 and g2 are the same.

MBREquals is a synonym.

Examples

This page is licensed: GPLv2, originally from

CONTAINS

Synonym for ST_CONTAINS. Checks if the first geometry completely contains the second geometry, with no points of the second geometry outside the first.

Syntax

Contains(g1,g2)

Description

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

ST_INTERSECTS

Checks if two geometries intersect. Returns 1 if the geometries share any portion of space (interior or boundary), 0 otherwise.

Syntax

ST_INTERSECTS(g1,g2)

Description

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 .

Examples

This page is licensed: CC BY-SA / Gnu FDL

ST_ISEMPTY

Checks if a geometry is empty. Returns 1 if the geometry contains no points, and 0 otherwise.

Syntax

ST_IsEmpty(g)
IsEmpty(g)

Description

IsEmpty is a function defined by the OpenGIS specification, but is not fully implemented by MariaDB or MySQL.

Since MariaDB and MySQL do not support GIS EMPTY values such as POINT EMPTY, as implemented it simply returns 1 if the geometry value g is invalid, 0 if it is valid, and NULL if the argument is NULL.

ST_IsEmpty() and IsEmpty() are synonyms.

This page is licensed: GPLv2, originally from

ST_DISTANCE_SPHERE

Calculates the spherical distance between two points or geometries. Uses a spherical earth model to determine the minimum distance in meters.

Syntax

ST_DISTANCE_SPHERE(g1,g2,[r])

Description

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.

Example

This page is licensed: CC BY-SA / Gnu FDL

ST_POINTN

Returns the N-th Point in a LineString. This function retrieves a specific point from the sequence based on its 1-based index.

Syntax

ST_PointN(ls,N)
PointN(ls,N)

Description

Returns the N-th Point in the LineString value ls. Points are numbered beginning with 1.

ST_PointN() and PointN() are synonyms.

Examples

This page is licensed: GPLv2, originally from

ST_STARTPOINT

Returns the start Point of a LineString. This function retrieves the initial coordinate in the linear geometry sequence.

Syntax

ST_StartPoint(ls)
StartPoint(ls)

Description

Returns the Point that is the start point of the LineString value ls.

ST_StartPoint() and StartPoint() are synonyms.

Examples

This page is licensed: GPLv2, originally from

ST_ENDPOINT

Returns the end Point of a LineString. This function retrieves the final coordinate in the linear geometry sequence.

Syntax

ST_EndPoint(ls)
EndPoint(ls)

Description

Returns the Point that is the endpoint of the LineString value ls.

ST_EndPoint() and EndPoint() are synonyms.

Examples

This page is licensed: GPLv2, originally from

GeometryN

Synonym for ST_GEOMETRYN. Returns the N-th geometry from a GeometryCollection, numbered starting from 1.

A synonym for ST_GeometryN.

This page is licensed: CC BY-SA / Gnu FDL

ST_NUMPOINTS

Returns the count of Points in a LineString. This function calculates the total number of vertices defining the line.

Syntax

ST_NumPoints(ls)
NumPoints(ls)

Description

Returns the number of Point objects in the LineString value ls.

ST_NumPoints() and NumPoints() are synonyms.

Examples

This page is licensed: GPLv2, originally from

ST_IsRing

Checks if the LineString is a ring. Returns 1 if the LineString is both closed (start equals end) and simple (no self-intersection).

Syntax

ST_IsRing(g)
IsRing(g)

Description

Returns true if a given LINESTRING is a ring, that is, both ST_IsClosed and ST_IsSimple. A simple curve does not pass through the same point more than once. However, see .

St_IsRing() and IsRing() are synonyms.

This page is licensed: CC BY-SA / Gnu FDL

ST_Y

Returns the Y-coordinate of a Point geometry. This function extracts the vertical coordinate value as a double-precision number.

Syntax

ST_Y(p)
Y(p)

Description

Returns the Y-coordinate value for the point p as a double-precision number.

ST_Y() and Y() are synonyms.

Examples

This page is licensed: GPLv2, originally from

EQUALS

Synonym for ST_EQUALS. Checks if two geometries are spatially equal, meaning they contain exactly the same point set.

Syntax

MBREQUALS(g1,g2)

Description

Returns 1 or 0 to indicate whether g1 is spatially equal to g2.

EQUALS() is based on the original MySQL implementation and uses object bounding rectangles, while uses object shapes.

MBREQUALS is a synonym for Equals.

This page is licensed: GPLv2, originally from

PointN

Synonym for ST_POINTN. Returns the N-th point in a LineString geometry, where N is a 1-based index.

A synonym for ST_PointN.

This page is licensed: CC BY-SA / Gnu FDL

STARTPOINT

Synonym for ST_STARTPOINT. Returns the first point of a LineString geometry.

A synonym for ST_STARTPOINT.

This page is licensed: CC BY-SA / Gnu FDL

MULTILINESTRING

Constructs a MultiLineString value from a list of WKB LineString arguments. This function creates a collection of multiple line strings.

Syntax

Description

Constructs a WKB MultiLineString value using arguments. If any argument is not a WKB LineString, the return value is

MULTIPOINT

Constructs a MultiPoint value from a list of WKB Point arguments. It creates a geometry collection consisting of multiple individual points.

Syntax

Description

Constructs a MultiPoint value using WKB arguments. If any argument is not a WKB Point, the return value is

ST_LENGTH

Returns the length of a linear geometry. Calculates the total length of a LineString or MultiLineString in its associated spatial reference units.

Syntax

Description

Returns as a double-precision number the length of the value

MBROverlaps

Check if two MBRs overlap. Returns 1 if the MBRs intersect but neither completely contains the other, and they have the same dimension.

Syntax

Description

Returns 1 or 0 to indicate whether the of the two geometries g1

ST_GeomFromGeoJSON

Parses a GeoJSON string and returns a geometry. This function converts a standard GeoJSON representation into a MariaDB geometry value.

Syntax

Description

Given a GeoJSON input g, returns a geometry object. The option specifies what to do if

OVERLAPS

Synonym for ST_OVERLAPS. Checks if two geometries overlap, sharing some but not all points, and having the same dimension.

Syntax

Description

Returns 1 or

INTERSECTS

Synonym for ST_INTERSECTS. Checks if two geometries intersect, meaning they share at least one common point.

Syntax

Description

Returns 1 or

ST_GeoHash

Generate a Geohash string from a point or coordinates. This function encodes spatial locations into short, alphanumeric strings for efficient indexing and proximity searches.

ST_GeoHash is available from .

Syntax

ST_Validate

Validate and optionally return a geometry. This function checks if a geometry is valid according to OGC rules; it returns the geometry if valid, or NULL if not.

ST_Validate is available from .

Syntax

GeometryType

Synonym for ST_GEOMETRYTYPE. Returns the name of the geometry type (e.g., 'POINT') of the geometry instance.

A synonym for .

This page is licensed: CC BY-SA / Gnu FDL

MBRTouches

Check if two MBRs touch. Returns 1 if the MBRs intersect only at their boundaries and do not share any interior points.

Syntax

Description

Returns 1 or 0 to indicate whether the of the two geometries g1

WITHIN

Synonym for ST_WITHIN. Checks if the first geometry is completely enclosed by the second geometry.

Syntax

Description

Returns 1 or 0

Y

Synonym for ST_Y. Returns the Y-coordinate value of a Point geometry as a double-precision number.

A synonym for .

This page is licensed: CC BY-SA / Gnu FDL

IsSimple

Synonym for ST_ISSIMPLE. Checks if a geometry is simple, meaning it has no anomalous geometric points like self-intersections.

A synonym for .

This page is licensed: CC BY-SA / Gnu FDL

ST_ISCLOSED

Returns 1 if the LineString is closed (start and end points are the same), 0 if not, and NULL if the argument is NULL.

Syntax

Description

Returns 1 if a given start and end points are the same, or 0 if they are not the same.

GLENGTH

Synonym for ST_LENGTH. Calculates the length of a LineString or MultiLineString in its associated spatial reference units.

Syntax

Description

Returns as a double-precision number the length of the value

ST_Simplify

Simplify a geometry using the Douglas-Peucker algorithm. This function reduces the number of vertices in a geometry while preserving its general shape, useful for rendering maps.

ST_Simplify is available from .

Syntax

ST_X

Returns the X-coordinate of a Point geometry. This function extracts the horizontal coordinate value as a double-precision number.

Syntax

Description

Returns the X-coordinate value for the point p

MBRCoveredBy

Check if one MBR is covered by another. Returns 1 if the MBR of the first geometry is entirely contained within the MBR of the second geometry.

MBRCoveredBy is available from .

Syntax

IsEmpty

Synonym for ST_ISEMPTY. Checks if a geometry is empty (contains no point sets). Returns 1 if empty, 0 otherwise.

A synonym for .

This page is licensed: CC BY-SA / Gnu FDL

CREATE TABLE geomcoll_example (
  g GEOMETRYCOLLECTION
);
INSERT INTO geomcoll_example VALUES
  (ST_GeomCollFromText(
    'GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 1, 0 2),
    POINT(1 0))')),
  (GeometryCollection(
    Point(0, 0),
    LineString(Point(0, 1), Point(0, 2))));
SELECT ST_AsWKT(g) FROM geomcoll_example;

+---------------------------------------------------------------+
| ST_AsWKT(g)                                                   |
+---------------------------------------------------------------+
| GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 1,0 2),POINT(1 0)) |
| GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(0 1,0 2))            |
+---------------------------------------------------------------+
SELECT AsText(ST_ENVELOPE(GeomFromText('LineString(1 1,4 4)')));
+----------------------------------------------------------+
| AsText(ST_ENVELOPE(GeomFromText('LineString(1 1,4 4)'))) |
+----------------------------------------------------------+
| POLYGON((1 1,4 1,4 4,1 4,1 1))                           |
+----------------------------------------------------------+
ST_LongFromGeoHash(geohash)
ST_IsValid(g)

Geometry Properties

Learn about geometry properties. This section details SQL functions for retrieving attributes of spatial objects, such as area, length, and bounding box, essential for geospatial analysis.

NULL
.

Example

MultiLineString_Example:

This page is licensed: GPLv2, originally from fill_help_tables.sql

MultiLineString(ls1,ls2,...)
WKB
LineString
CREATE TABLE gis_multi_line (g MULTILINESTRING);
INSERT INTO gis_multi_line VALUES
 (MultiLineStringFromText('MULTILINESTRING((10 48,10 21,10 0),(16 0,16 23,16 48))')),
 (MLineFromText('MULTILINESTRING((10 48,10 21,10 0))')),
 (MLineFromWKB(AsWKB(MultiLineString(LineString(Point(1, 2), 
    Point(3, 5)), LineString(Point(2, 5),Point(5, 8),Point(21, 7))))));
NULL
.

Examples

MultiPoint_Example:

This page is licensed: GPLv2, originally from fill_help_tables.sql

MultiPoint(pt1,pt2,...)
WKB
Point
SET @g = ST_GEOMFROMTEXT('MultiPoint( 1 1, 2 2, 5 3, 7 2, 9 3, 8 4, 6 6, 6 9, 4 9, 1 5 )');

CREATE TABLE gis_multi_point (g MULTIPOINT);
INSERT INTO gis_multi_point VALUES
    (MultiPointFromText('MULTIPOINT(0 0,10 10,10 20,20 20)')),
    (MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)')),
    (MPointFromWKB(AsWKB(MultiPoint(Point(3, 6), Point(4, 10)))));
ls
in its associated spatial reference.

Examples

This page is licensed: CC BY-SA / Gnu FDL

ST_LENGTH(ls)
LineString
SET @ls = 'LineString(1 1,2 2,3 3)';

SELECT ST_LENGTH(ST_GeomFromText(@ls));
+---------------------------------+
| ST_LENGTH(ST_GeomFromText(@ls)) |
+---------------------------------+
|                2.82842712474619 |
+---------------------------------+
and
g2
overlap. The term spatially overlaps is used if two geometries intersect and their intersection results in a geometry of the same dimension but not equal to either of the given geometries.

Examples

This page is licensed: GPLv2, originally from fill_help_tables.sql

MBROverlaps(g1,g2)
Minimum Bounding Rectangles
g
contains geometries with coordinate dimensions higher than 2.
Option
Description

1

Return an error (the default)

2 - 4

The document is accepted, but the coordinates for higher coordinate dimensions are stripped off.

Examples

See Also

  • ST_AsGeoJSON

This page is licensed: CC BY-SA / Gnu FDL

ST_GeomFromGeoJSON(g[, option])
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 ST_OVERLAPS() uses object shapes.

This page is licensed: GPLv2, originally from fill_help_tables.sql

OVERLAPS(g1,g2)
0
to indicate whether geometry
g1
spatially intersects geometry
g2
.

INTERSECTS() is based on the original MySQL implementation and uses object bounding rectangles, while ST_INTERSECTS() uses object shapes.

INTERSECTS() tests the opposite relationship to DISJOINT().

This page is licensed: GPLv2, originally from fill_help_tables.sql

INTERSECTS(g1,g2)
Description

Returns the geohash corresponding to the input values, or NULL if any argument is NULL. Geohashes encode latitude and longitude coordinates into a text string made up only of numeric and lowercase latin letter characters.

The longitude parameter is a numeric value in the interval [180, -180]. latitude is a numeric value in the interval [90, -90].

In the case of point, the x coordinate is treated as the latitude and the y coordinate is treated as the latitude. The same constraints apply.

The max_length parameter is the upper limit on the resulting string size and cannot exceed 100.

The ST_LatFromGeoHash function decodes a given geohash and returns the latitude.

Examples

See Also

  • ST_LatFromGeoHash

  • Geometry Constructors

  • ST_GeomFromText

This page is licensed: CC BY-SA / Gnu FDL

MariaDB 12.0
Description

The function checks that a given geometry is compliant with the Well-Known Binary (WKB) format and Spatial Reference System Identifier (SRID) syntax, and is geometrically valid.

It returns the geometry if it's valid, or NULL if not.

The function is useful to filter out invalid geometry data.

Examples

A POINT requires both x and y co-ordinates:

See Also

  • Geometry Constructors

  • ST_AsText

This page is licensed: CC BY-SA / Gnu FDL

MariaDB 12.0
and
g2
touch. 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.

Examples

This page is licensed: GPLv2, originally from fill_help_tables.sql

MBRTouches(g1,g2)
Minimum Bounding Rectangles
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 ST_WITHIN() uses object shapes.

Examples

This page is licensed: GPLv2, originally from fill_help_tables.sql

Within(g1,g2)
Contains()
ST_IsClosed() and IsClosed() are synonyms.

Examples

This page is licensed: CC BY-SA / Gnu FDL

ST_IsClosed(g)
IsClosed(g)
LINESTRING's
SET @ls = 'LineString(0 0, 0 4, 4 4, 0 0)';
SELECT ST_ISCLOSED(GEOMFROMTEXT(@ls));
+--------------------------------+
| ST_ISCLOSED(GEOMFROMTEXT(@ls)) |
+--------------------------------+
|                              1 |
+--------------------------------+

SET @ls = 'LineString(0 0, 0 4, 4 4, 0 1)';
SELECT ST_ISCLOSED(GEOMFROMTEXT(@ls));
+--------------------------------+
| ST_ISCLOSED(GEOMFROMTEXT(@ls)) |
+--------------------------------+
|                              0 |
+--------------------------------+
ls
in its associated spatial reference.

Examples

See Also

  • ST_LENGTH() is the OpenGIS equivalent.

This page is licensed: GPLv2, originally from fill_help_tables.sql

GLength(ls)
LineString
Description

Takes as input a geometry (g) and a double (max_distance). It applies the Ramer–Douglas–Peucker algorithm on g and returns the resulting geometry.

The goal of the Douglas-Peucker algorithm is to provide generalized simplifications by returning a geometry that is similar to g but uses only a subset of points. To perform the simplification, all the vertices that are shorter than max_distance are removed.

The algorithm may produce self-intersections and therefore result in invalid geometries. ST_IsValid can be used to test validity of the result.

If the max_distance is not positive or is NULL, an ER_WRONG_ARGUMENT will occur.

Examples

See Also

  • Geometry Constructors

  • ST_AsText

  • ST_GeomFromText

This page is licensed: CC BY-SA / Gnu FDL

MariaDB 12.0
as a double-precision number.

ST_X() and X() are synonyms.

Examples

This page is licensed: GPLv2, originally from fill_help_tables.sql

ST_X(p)
X(p)
SET @pt = 'Point(56.7 53.34)';

SELECT X(GeomFromText(@pt));
+----------------------+
| X(GeomFromText(@pt)) |
+----------------------+
|                 56.7 |
+----------------------+
Description

Returns 1 if the minimum bounding rectangle of g1 is covered by the minimum bounding rectangle of g2, otherwise 0.

Returns NULL If any argument is NULL, or an argument is an empty geometry.

Examples

See Also

This page is licensed: CC BY-SA / Gnu FDL

MBRCoveredBy(g1, g2)
MariaDB 12.0
SET @ls = 'LineString(1 1,2 2,3 3)';

SELECT AsText(EndPoint(GeomFromText(@ls)));
+-------------------------------------+
| AsText(EndPoint(GeomFromText(@ls))) |
+-------------------------------------+
| POINT(3 3)                          |
+-------------------------------------+

CREATE TABLE gis_line  (g LINESTRING);
INSERT INTO gis_line VALUES
    (LineFromText('LINESTRING(0 0,0 10,10 0)')),
    (LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)')),
    (LineStringFromWKB(AsWKB(LineString(Point(10, 10), Point(40, 10)))));
Point
fill_help_tables.sql
CREATE TABLE gis_multi_polygon  (g MULTIPOLYGON);
INSERT INTO gis_multi_polygon VALUES
    (MultiPolygonFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),
     ((59 18,67 18,67 13,59 13,59 18)))')),
    (MPolyFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),
        ((59 18,67 18,67 13,59 13,59 18)))')),
    (MPolyFromWKB(AsWKB(MultiPolygon(Polygon(LineString(
       Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3)))))));
fill_help_tables.sql
SET @g1 = ST_GEOMFROMTEXT('POLYGON((10 10, 10 20, 20 20, 20 10, 10 10))');

SET @g2 = ST_GEOMFROMTEXT('POINT(8 8)');

SELECT ST_WITHIN(@g2,ST_BUFFER(@g1,5));
+---------------------------------+
| ST_WITHIN(@g2,ST_BUFFER(@g1,5)) |
+---------------------------------+
|                               1 |
+---------------------------------+

SELECT ST_WITHIN(@g2,ST_BUFFER(@g1,1));
+---------------------------------+
| ST_WITHIN(@g2,ST_BUFFER(@g1,1)) |
+---------------------------------+
|                               0 |
+---------------------------------+
SET @g = ST_GEOMFROMTEXT('Point(1 1)');

CREATE TABLE gis_point  (g POINT);
INSERT INTO gis_point VALUES
    (PointFromText('POINT(10 10)')),
    (PointFromText('POINT(20 10)')),
    (PointFromText('POINT(20 20)')),
    (PointFromWKB(AsWKB(PointFromText('POINT(10 20)'))));
CREATE TABLE point_example (
  p POINT
);
fill_help_tables.sql

-1

empty geometry

0

geometry with no length or area

1

geometry with no area but nonzero length

2

geometry with nonzero area

fill_help_tables.sql
SET @g1 = GEOMFROMTEXT('POINT (0 2)');

SET @g2 = GEOMFROMTEXT('POINT (2 0)');

SELECT ASTEXT(ST_UNION(@g1,@g2));
+---------------------------+
| ASTEXT(ST_UNION(@g1,@g2)) |
+---------------------------+
| MULTIPOINT(2 0,0 2)       |
+---------------------------+
SET @g1 = GEOMFROMTEXT('POLYGON((0 0,0 3,3 3,3 0,0 0))');

SET @g2 = GEOMFROMTEXT('POLYGON((2 2,4 2,4 4,2 4,2 2))');

SELECT ASTEXT(ST_UNION(@g1,@g2));
+------------------------------------------------+
| ASTEXT(ST_UNION(@g1,@g2))                      |
+------------------------------------------------+
| POLYGON((0 0,0 3,2 3,2 4,4 4,4 2,3 2,3 0,0 0)) |
+------------------------------------------------+
SET @g = ST_GEOMFROMTEXT('POLYGON((1 1,1 5,4 9,6 9,9 3,7 2,1 1))');

CREATE TABLE gis_polygon   (g POLYGON);
INSERT INTO gis_polygon VALUES
    (PolygonFromText('POLYGON((10 10,20 10,20 20,10 20,10 10))')),
    (PolyFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))')),
    (PolyFromWKB(AsWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0))))));
ST_NUMINTERIORRINGS()
fill_help_tables.sql
SET @gc = 'GeometryCollection(Point(1 1),LineString(12 14, 9 11))';

SELECT AsText(GeometryN(GeomFromText(@gc),1));
+----------------------------------------+
| AsText(GeometryN(GeomFromText(@gc),1)) |
+----------------------------------------+
| POINT(1 1)                             |
+----------------------------------------+
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 |
+--------------------+
EQUALS()
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 |
+---------------------+
TOUCHES()
SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))');
SET @g2 = GeomFromText('Polygon((4 4,4 7,7 7,7 4,4 4))');
SELECTmbrdisjoint(@g1,@g2);
+----------------------+
| mbrdisjoint(@g1,@g2) |
+----------------------+
|                    1 |
+----------------------+

SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))');
SET @g2 = GeomFromText('Polygon((3 3,3 6,6 6,6 3,3 3))');
SELECT mbrdisjoint(@g1,@g2);
+----------------------+
| mbrdisjoint(@g1,@g2) |
+----------------------+
|                    0 |
+----------------------+
fill_help_tables.sql
ST_GeoHash
ST_GeoHash
ST_GeoHash
ST_GeoHash
ST_LatFromGeoHash
SELECT SRID(GeomFromText('LineString(1 1,2 2)',101));
+-----------------------------------------------+
| SRID(GeomFromText('LineString(1 1,2 2)',101)) |
+-----------------------------------------------+
|                                           101 |
+-----------------------------------------------+
fill_help_tables.sql
SET @g = ST_GEOMFROMTEXT('Point(0 0)');

SELECT ST_ASTEXT(ST_CONVEXHULL(@g));
+------------------------------+
| ST_ASTEXT(ST_CONVEXHULL(@g)) |
+------------------------------+
| POINT(0 0)                   |
+------------------------------+
SET @g = ST_GEOMFROMTEXT('MultiPoint(0 0, 1 2, 2 3)');

SELECT ST_ASTEXT(ST_CONVEXHULL(@g));
+------------------------------+
| ST_ASTEXT(ST_CONVEXHULL(@g)) |
+------------------------------+
| POLYGON((0 0,1 2,2 3,0 0))   |
+------------------------------+
SELECT GeometryType(GeomFromText('POINT(1 1)'));
+------------------------------------------+
| GeometryType(GeomFromText('POINT(1 1)')) |
+------------------------------------------+
| POINT                                    |
+------------------------------------------+
fill_help_tables.sql
ST_Validate
POINT
Geometry Constructors
ST_AsText
SET @g1=GEOMFROMTEXT('LINESTRING(0 0, 1 2)');
SET @g2=GEOMFROMTEXT('POLYGON((0 0, 0 2, 1 2, 1 0, 0 0))');
SELECT MbrEqual(@g1,@g2);
+-------------------+
| MbrEqual(@g1,@g2) |
+-------------------+
|                 1 |
+-------------------+

SET @g1=GEOMFROMTEXT('LINESTRING(0 0, 1 3)');
SET @g2=GEOMFROMTEXT('POLYGON((0 0, 0 2, 1 4, 1 0, 0 0))');
SELECT MbrEqual(@g1,@g2);
+-------------------+
| MbrEqual(@g1,@g2) |
+-------------------+
|                 0 |
+-------------------+
fill_help_tables.sql
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 |
+------------------------+
INTERSECTS()
ST_DISJOINT()
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 AsText(PointN(GeomFromText(@ls),2));
+-------------------------------------+
| AsText(PointN(GeomFromText(@ls),2)) |
+-------------------------------------+
| POINT(2 2)                          |
+-------------------------------------+
fill_help_tables.sql
SET @ls = 'LineString(1 1,2 2,3 3)';

SELECT AsText(StartPoint(GeomFromText(@ls)));
+---------------------------------------+
| AsText(StartPoint(GeomFromText(@ls))) |
+---------------------------------------+
| POINT(1 1)                            |
+---------------------------------------+
fill_help_tables.sql
SET @ls = 'LineString(1 1,2 2,3 3)';

SELECT AsText(EndPoint(GeomFromText(@ls)));
+-------------------------------------+
| AsText(EndPoint(GeomFromText(@ls))) |
+-------------------------------------+
| POINT(3 3)                          |
+-------------------------------------+
fill_help_tables.sql
SET @ls = 'LineString(1 1,2 2,3 3)';

SELECT NumPoints(GeomFromText(@ls));
+------------------------------+
| NumPoints(GeomFromText(@ls)) |
+------------------------------+
|                            3 |
+------------------------------+
fill_help_tables.sql
SET @pt = 'Point(56.7 53.34)';

SELECT Y(GeomFromText(@pt));
+----------------------+
| Y(GeomFromText(@pt)) |
+----------------------+
|                53.34 |
+----------------------+
fill_help_tables.sql
OVERLAPS()
ST_CONTAINS()
Within()
fill_help_tables.sql
fill_help_tables.sql
MDEV-7510
ST_EQUALS()
fill_help_tables.sql
ST_GeometryType
ST_Y
ST_IsSImple
ST_IsEmpty

MBRContains

Check if one MBR contains another. Returns 1 if the Minimum Bounding Rectangle of the first geometry completely encloses the MBR of the second geometry.

Syntax

MBRContains(g1,g2)

Description

Returns 1 or 0 to indicate whether the Minimum Bounding Rectangle of g1 contains the Minimum Bounding Rectangle of g2. This tests the opposite relationship as MBRWithin().

Examples

See Also

This page is licensed: GPLv2, originally from

ST_WITHIN

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.

Syntax

ST_WITHIN(g1,g2)

Description

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.

Examples

This page is licensed: CC BY-SA / Gnu FDL

ST_CONTAINS

Checks if geometry A contains geometry B. Returns 1 if B is completely inside A, 0 otherwise.

Syntax

ST_CONTAINS(g1,g2)

Description

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 .

Examples

This page is licensed: CC BY-SA / Gnu FDL

MBRIntersects

Check if two MBRs intersect. Returns 1 if the Minimum Bounding Rectangles of the geometries share any portion of space, including boundaries.

Syntax

MBRIntersects(g1,g2)

Description

Returns 1 or 0 to indicate whether the Minimum Bounding Rectangles of the two geometries g1 and g2 intersect.

Examples

This page is licensed: GPLv2, originally from

ST_Collect

Aggregate multiple geometries into a collection. This function creates a MultiPoint, MultiLineString, MultiPolygon, or GeometryCollection from a set of geometry arguments.

ST_Collect is available from .

Syntax

MBRWithin

Check if one MBR is within another. Returns 1 if the MBR of the first geometry is completely enclosed by the MBR of the second geometry.

Syntax

Description

Returns 1 or 0 to indicate whether the of g1 is within the Minimum Bounding Rectangle of g2. This tests the opposite relationship as

CREATE TABLE mlstr_example (
  m MULTILINESTRING
);
INSERT INTO mlstr_example VALUES
  (ST_MultiLineStringFromText(
    'MULTILINESTRING((0 40, 0 20, 6 30, 12 20, 12 40),
    (15 40, 15 20, 25 20, 30 25, 30 35, 25 40, 15 40))')),
  (ST_MLineFromText('MULTILINESTRING((0 0, 1 1, 2 2))')),
  (MultiLineString(
    LineString(Point(0, 40), Point(0, 20)),
    LineString(Point(6, 30), Point(12, 20), Point(12, 40))));
INSERT INTO mlstr_example VALUES
  (MultiLineStringFromText(
    'MULTILINESTRING((0 40, 0 20, 6 30, 12 20, 12 40),
    (15 40, 15 20, 25 20, 30 25, 30 35, 25 40, 15 40))')),
  (MLineFromText('MULTILINESTRING((0 0, 1 1, 2 2))')),
  (MultiLineString(
    LineString(Point(0, 40), Point(0, 20)),
    LineString(Point(6, 30), Point(12, 20), Point(12, 40))));
SELECT ST_AsWKT(m) FROM mlstr_example;
+-------------------------------------------------------------------------------------------+
| ST_AsWKT(m)                                                                               |
+-------------------------------------------------------------------------------------------+
| MULTILINESTRING((0 40,0 20,6 30,12 20,12 40),(15 40,15 20,25 20,30 25,30 35,25 40,15 40)) |
| MULTILINESTRING((0 0,1 1,2 2))                                                            |
| MULTILINESTRING((0 40,0 20),(6 30,12 20,12 40))                                           |
+-------------------------------------------------------------------------------------------+
CREATE TABLE multipoint_example (
  m MULTIPOINT
);
INSERT INTO multipoint_example VALUES
  (ST_MultiPointFromText('MULTIPOINT(0 0, 1 0, 1 1, 0 1)')),
  (ST_MPointFromText('MULTIPOINT(1 1, 2 2, 3 3, 4 4)')),
  (MultiPoint(Point(0, 0), Point(1, 1)));
INSERT INTO multipoint_example VALUES
  (MultiPointFromText('MULTIPOINT(0 0, 1 0, 1 1, 0 1)')),
  (MPointFromText('MULTIPOINT(1 1, 2 2, 3 3, 4 4)')),
  (MultiPoint(Point(0, 0), Point(1, 1)));
SELECT ST_AsWKT(m) FROM multipoint_example;
+-----------------------------+
| ST_AsWKT(m)                 |
+-----------------------------+
| MULTIPOINT(0 0,1 0,1 1,0 1) |
| MULTIPOINT(1 1,2 2,3 3,4 4) |
| MULTIPOINT(0 0,1 1)         |
+-----------------------------+
SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))');
SET @g2 = GeomFromText('Polygon((4 4,4 7,7 7,7 4,4 4))');
SELECT mbroverlaps(@g1,@g2);
+----------------------+
| mbroverlaps(@g1,@g2) |
+----------------------+
|                    0 |
+----------------------+

SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))');
SET @g2 = GeomFromText('Polygon((3 3,3 6,6 6,6 3,3 3))');
SELECT mbroverlaps(@g1,@g2);
+----------------------+
| mbroverlaps(@g1,@g2) |
+----------------------+
|                    0 |
+----------------------+

SET @g1 = GeomFromText('Polygon((0 0,0 4,4 4,4 0,0 0))');
SET @g2 = GeomFromText('Polygon((3 3,3 6,6 6,6 3,3 3))');
SELECT mbroverlaps(@g1,@g2);
+----------------------+
| mbroverlaps(@g1,@g2) |
+----------------------+
|                    1 |
+----------------------+
SET @j = '{ "type": "Point", "coordinates": [5.3, 15.0]}';

SELECT ST_AsText(ST_GeomFromGeoJSON(@j));
+-----------------------------------+
| ST_AsText(ST_GeomFromGeoJSON(@j)) |
+-----------------------------------+
| POINT(5.3 15)                     |
+-----------------------------------+
ST_GeoHash(longitude, latitude, max_length)
ST_GeoHash(point, max_length)
SELECT ST_GeoHash(ST_GeomFromText('POINT(1 1)'),15), ST_GeoHash(0,30,15);
+----------------------------------------------+---------------------+
| ST_GeoHash(ST_GeomFromText('POINT(1 1)'),15) | ST_GeoHash(0,30,15) |
+----------------------------------------------+---------------------+
| s00twy01mtw037m                              | sj248j248j248j2     |
+----------------------------------------------+---------------------+
ST_Validate(g)
SELECT ST_ASTEXT(ST_VALIDATE(ST_GeomFromText('POINT(1 0)')));          
+-------------------------------------------------------+
| ST_ASTEXT(ST_VALIDATE(ST_GeomFromText('POINT(1 0)'))) |
+-------------------------------------------------------+
| POINT(1 0)                                            |
+-------------------------------------------------------+

SELECT ST_ASTEXT(ST_VALIDATE(ST_GeomFromText('POINT(1)')));  
+-----------------------------------------------------+
| ST_ASTEXT(ST_VALIDATE(ST_GeomFromText('POINT(1)'))) |
+-----------------------------------------------------+
| NULL                                                |
+-----------------------------------------------------+
SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))');
SET @g2 = GeomFromText('Polygon((4 4,4 7,7 7,7 4,4 4))');
SELECT mbrtouches(@g1,@g2);
+---------------------+
| mbrtouches(@g1,@g2) |
+---------------------+
|                   0 |
+---------------------+

SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))');
SET @g2 = GeomFromText('Polygon((3 3,3 6,6 6,6 3,3 3))');
SELECT mbrtouches(@g1,@g2);
+---------------------+
| mbrtouches(@g1,@g2) |
+---------------------+
|                   1 |
+---------------------+

SET @g1 = GeomFromText('Polygon((0 0,0 4,4 4,4 0,0 0))');
SET @g2 = GeomFromText('Polygon((3 3,3 6,6 6,6 3,3 3))');
SELECT mbrtouches(@g1,@g2);
+---------------------+
| mbrtouches(@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 @ls = 'LineString(1 1,2 2,3 3)';

SELECT GLength(GeomFromText(@ls));
+----------------------------+
| GLength(GeomFromText(@ls)) |
+----------------------------+
|           2.82842712474619 |
+----------------------------+
ST_Simplify(g, max_distance)
SELECT ST_AsText(ST_Simplify(ST_GeomFromText('LINESTRING(0 0,0 2,2 2,2 4,4 4,4 6,6 6)'), 0.5));
+-----------------------------------------------------------------------------------------+
| ST_AsText(ST_Simplify(ST_GeomFromText('LINESTRING(0 0,0 2,2 2,2 4,4 4,4 6,6 6)'), 0.5)) |
+-----------------------------------------------------------------------------------------+
| LINESTRING(0 0,0 2,2 2,2 4,4 4,4 6,6 6)                                                 |
+-----------------------------------------------------------------------------------------+

SELECT ST_AsText(ST_Simplify(ST_GeomFromText('LINESTRING(0 0,0 2,2 2,2 4,4 4,4 6,6 6)'), 1));  
+---------------------------------------------------------------------------------------+
| ST_AsText(ST_Simplify(ST_GeomFromText('LINESTRING(0 0,0 2,2 2,2 4,4 4,4 6,6 6)'), 1)) |
+---------------------------------------------------------------------------------------+
| LINESTRING(0 0,0 2,2 2,2 4,6 6)                                                       |
+---------------------------------------------------------------------------------------+
SET @g1a = ST_GeomFromText('Point(5 6)');
SET @g1b = ST_GeomFromText('Point(5 11)');
SET @g2 = ST_GeomFromText('Polygon((0 0,0 10,10 10,10 0,0 0))');

SELECT MBRCoveredby(@g1a,@g2), MBRCoveredby(@g1b,@g2);
+------------------------+------------------------+
| MBRCoveredby(@g1a,@g2) | MBRCoveredby(@g1b,@g2) |
+------------------------+------------------------+
|                      1 |                      0 |
+------------------------+------------------------+
CREATE TABLE linestring_example (
  g LINESTRING
);
INSERT INTO linestring_example VALUES
  (ST_LineFromText('LINESTRING(0 0, 1 1, 2 2)')),
  (ST_LineStringFromText('LINESTRING(10 10, 20 10, 20 20, 10 20, 10 10)')),
  (LineString(Point(10, 10), Point(40, 10)));
SELECT ST_AsWKT(g) FROM linestring_example;
<</code>>

<<sql>>
+-------------------------------------------+
| ST_AsWKT(g)                               |
+-------------------------------------------+
| LINESTRING(0 0,1 1,2 2)                   |
| LINESTRING(10 10,20 10,20 20,10 20,10 10) |
| LINESTRING(10 10,40 10)                   |
+-------------------------------------------+
CREATE TABLE multipolygon_example (
  m MULTIPOLYGON
);
INSERT INTO multipolygon_example VALUES
  (ST_MultiPolygonFromText(
    'MULTIPOLYGON(((0 40, 0 20, 6 30, 12 20, 12 40, 0 40),
    (15 40, 15 20, 25 20, 30 25, 30 35, 25 40, 15 40)))')),
  (ST_MPolyFromText(
    'MULTIPOLYGON(((-5 45, 35 45, 35 15, -5 15, -5 45),
    (0 40, 0 20, 6 30, 12 20, 12 40, 0 40),
    (15 40, 15 20, 25 20, 30 25, 30 35, 25 40, 15 40)))')),
  (MultiPolygon(Polygon(LineString(Point(0, 0), Point(0, 1),
     Point(1, 1), Point(1, 0), Point(0, 0)))));
SELECT ST_AsWKT(m) FROM multipolygon_example;
+-------------------------------------------------------------------------------------------------------------------------------+
| ST_AsWKT(m)                                                                                                                   |
+-------------------------------------------------------------------------------------------------------------------------------+
| MULTIPOLYGON(((0 40,0 20,6 30,12 20,12 40,0 40),(15 40,15 20,25 20,30 25,30 35,25 40,15 40)))                                 |
| MULTIPOLYGON(((-5 45,35 45,35 15,-5 15,-5 45),(0 40,0 20,6 30,12 20,12 40,0 40),(15 40,15 20,25 20,30 25,30 35,25 40,15 40))) |
| MULTIPOLYGON(((0 0,0 1,1 1,1 0,0 0)))                                                                                         |
+-------------------------------------------------------------------------------------------------------------------------------+
INSERT INTO point_example VALUES
  (ST_PointFromText('POINT(1 1)')),
  (ST_PointFromText('POINT(2 2)')),
  (Point(3, 3)),
  (Point(4, 4));
SELECT ST_AsWKT(p) FROM point_example;
+-------------+
| ST_AsWKT(p) |
+-------------+
| POINT(1 1)  |
| POINT(2 2)  |
| POINT(3 3)  |
| POINT(4 4)  |
+-------------+
SELECT Dimension(GeomFromText('LineString(1 1,2 2)'));
+------------------------------------------------+
| Dimension(GeomFromText('LineString(1 1,2 2)')) |
+------------------------------------------------+
|                                              1 |
+------------------------------------------------+
SELECT ST_NumInteriorRings(ST_PolyFromText('POLYGON((0 0,10 0,10 10,0 10,0 0),
  (-1 -1,-5 -1,-5 -5,-1 -5,-1 -1))')) AS NumInteriorRings;
+------------------+
| NumInteriorRings |
+------------------+
|                1 |
+------------------+
CREATE TABLE polygon_example (
  p POLYGON
);
INSERT INTO polygon_example VALUES
  (ST_PolygonFromText('POLYGON((0 40, 0 20, 6 30, 12 20, 12 40, 0 40))')),
  (ST_PolyFromText('POLYGON((15 40, 15 20, 25 20, 30 25, 30 35, 25 40, 15 40))')),
  (Polygon(LineString(Point(0, 0), Point(0, 1), Point(1, 1),
  Point(1, 0), Point(0, 0))));
SELECT ST_AsWKT(p) FROM polygon_example;
+------------------------------------------------------+
| ST_AsWKT(p)                                          |
+------------------------------------------------------+
| POLYGON((0 40,0 20,6 30,12 20,12 40,0 40))           |
| POLYGON((15 40,15 20,25 20,30 25,30 35,25 40,15 40)) |
| POLYGON((0 0,0 1,1 1,1 0,0 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 |
+--------------------+
SELECT ST_LatFromGeoHash('zzzzzzzzz'), ST_LatFromGeoHash('xvrfxvrfxvrfxvr');
+--------------------------------+--------------------------------------+
| ST_LatFromGeoHash('zzzzzzzzz') | ST_LatFromGeoHash('xvrfxvrfxvrfxvr') |
+--------------------------------+--------------------------------------+
|                             90 |                                   30 |
+--------------------------------+--------------------------------------+
SELECT ST_LongFromGeoHash('zzzzzzzzz'), ST_LongFromGeoHash('sj248j248j248j2');           
+---------------------------------+---------------------------------------+
| ST_LongFromGeoHash('zzzzzzzzz') | ST_LongFromGeoHash('sj248j248j248j2') |
+---------------------------------+---------------------------------------+
|                             180 |                                     0 |
+---------------------------------+---------------------------------------+
SET @g = ST_GEOMFROMTEXT('MultiPoint( 1 1, 2 2, 5 3, 7 2, 9 3, 8 4, 6 6, 6 9, 4 9, 1 5 )');

SELECT ST_ASTEXT(ST_CONVEXHULL(@g));
+----------------------------------------+
| ST_ASTEXT(ST_CONVEXHULL(@g))           |
+----------------------------------------+
| POLYGON((1 1,1 5,4 9,6 9,9 3,7 2,1 1)) |
+----------------------------------------+
SELECT ST_IsValid(ST_GeomFromText('LINESTRING (0 0, 1 1)'));
+------------------------------------------------------+
| ST_IsValid(ST_GeomFromText('LINESTRING (0 0, 1 1)')) |
+------------------------------------------------------+
|                                                    1 |
+------------------------------------------------------+

SELECT ST_IsValid(ST_GeomFromText('LINESTRING (0 0, 0 0)'));
+------------------------------------------------------+
| ST_IsValid(ST_GeomFromText('LINESTRING (0 0, 0 0)')) |
+------------------------------------------------------+
|                                                    0 |
+------------------------------------------------------+
SELECT ST_IsValid(ST_GeomFromText('POINT (0)'));  
ERROR 3037 (22023): Invalid GIS data provided to function st_isvalid.
SET @g2 = ST_GEOMFROMTEXT('LINESTRING(2 0, 0 2)');

SELECT ST_INTERSECTS(@g1,@g2);
+------------------------+
| ST_INTERSECTS(@g1,@g2) |
+------------------------+
|                      0 |
+------------------------+

LineString Properties

Learn about LINESTRING properties in MariaDB Server. This section details SQL functions for retrieving attributes of linear spatial objects, such as length, number of points, and start/end points.

MBRWithin
fill_help_tables.sql
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 |
+--------------------+
ST_CONTAINS()
WITHIN()
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 |
+----------------------+
CONTAINS()
ST_WITHIN()
SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))');
SET @g2 = GeomFromText('Polygon((3 3,3 6,6 6,6 3,3 3))');
SELECT mbrintersects(@g1,@g2);
+------------------------+
| mbrintersects(@g1,@g2) |
+------------------------+
|                      1 |
+------------------------+

SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))');
SET @g2 = GeomFromText('Polygon((4 4,4 7,7 7,7 4,4 4))');
SELECT mbrintersects(@g1,@g2);
+------------------------+
| mbrintersects(@g1,@g2) |
+------------------------+
|                      0 |
+------------------------+
fill_help_tables.sql
Description

ST_Collect is an aggregate function that can also be used as a window function.

Given multiple geometries, returns the aggregation of the distinct geometry arguments. This function also supports the DISTINCT option. If DISTINCT is used, it returns the aggregation of the distinct geometry arguments.

The resulting value type is chosen using the following policy:

  • If all arguments are Point values, the result is a MultiPoint value.

  • If all arguments are LineString values, the result is a MultiLineString value.

  • If all arguments are Polygon values, the result is a MultiPolygon value.

  • Otherwise, the result is a GeometryCollection value.

If there are multiple geometry arguments and those arguments are in the same spatial reference system (SRS), the return value is in that SRS. If those arguments are not in the same SRS, an ER_GIS_DIFFERENT_SRIDS_AGGREGATION error occurs.

Examples

Multiple Point geometries aggregated into a MultiPoint geometry:

See Also

  • Geometry Constructors

  • ST_AsText

This page is licensed: CC BY-SA / Gnu FDL

MariaDB 12.0
.

Examples

This page is licensed: GPLv2, originally from fill_help_tables.sql

MBRWithin(g1,g2)
Minimum Bounding Rectangle
MBRContains()
SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))');

SET @g2 = GeomFromText('Point(1 1)');

SELECT MBRContains(@g1,@g2), MBRContains(@g2,@g1);
+----------------------+----------------------+
| MBRContains(@g1,@g2) | MBRContains(@g2,@g1) |
+----------------------+----------------------+
|                    1 |                    0 |
+----------------------+----------------------+
ST_Collect(g)
CREATE OR REPLACE TABLE t1 ( running_number INTEGER NOT NULL
  AUTO_INCREMENT, grouping_condition INTEGER, location GEOMETRY , PRIMARY KEY (
  running_number));

INSERT INTO t1 ( grouping_condition, location ) VALUES
  ( 0,ST_GEOMFROMTEXT('POINT(0 0)',4326)),
  ( 1,ST_GEOMFROMTEXT('POINT(0 0)',4326)),
  ( 0,ST_GEOMFROMTEXT('POINT(1 0)',4326)),
  ( 1,ST_GEOMFROMTEXT('POINT(2 0)',4326)),
  ( 0,ST_GEOMFROMTEXT('POINT(3 0)',4326));

SELECT ST_EQUALS( (SELECT ST_COLLECT( location ) AS t FROM t1), 
  ST_GEOMFROMTEXT('MULTIPOINT(0 0,0 0,1 0,2 0,3 0) ',4326)) AS equals;
+--------+
| equals |
+--------+
|      1 |
+--------+
SET @g1 = GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))');
SET @g2 = GeomFromText('Polygon((0 0,0 5,5 5,5 0,0 0))');
SELECT MBRWithin(@g1,@g2), MBRWithin(@g2,@g1);
+--------------------+--------------------+
| MBRWithin(@g1,@g2) | MBRWithin(@g2,@g1) |
+--------------------+--------------------+
|                  1 |                  0 |
+--------------------+--------------------+

CROSSES

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.

Syntax

Crosses(g1,g2)

Description

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

ST_AsGeoJSON

Generates a GeoJSON object from a given geometry. This function converts internal geometry data into the standard JSON-based format for web mapping.

Syntax

ST_AsGeoJSON(g[, max_decimals[, options]])

Description

Returns the given geometry g as a GeoJSON element. The optional max_decimals limits the maximum number of decimals displayed.

The optional options flag can be set to 1 to add a bounding box to the output.

Examples

See Also

This page is licensed: CC BY-SA / Gnu FDL

ST_PointFromGeoHash

Create a Point geometry from a Geohash. This function decodes a Geohash string into a Point object representing the location's center.

ST_PointFromGeoHash is available from .

Syntax

TOUCHES

Synonym for ST_TOUCHES. Checks if two geometries touch at their boundaries without their interiors intersecting.

Syntax

Description

Returns 1 or

Polygon
MultiPolygon
Point
MultiPoint
ST_CROSSES()
fill_help_tables.sql
SELECT ST_AsGeoJSON(ST_GeomFromText('POINT(5.3 7.2)'));
+-------------------------------------------------+
| ST_AsGeoJSON(ST_GeomFromText('POINT(5.3 7.2)')) |
+-------------------------------------------------+
| {"type": "Point", "coordinates": [5.3, 7.2]}    |
+-------------------------------------------------+
ST_GeomFromGeoJSON
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 ST_TOUCHES() uses object shapes.

This page is licensed: GPLv2, originally from fill_help_tables.sql

Touches(g1,g2)
Description

Takes a given geohash string and returns a point where the x is the longitude and the y is the latitude.

The latitude is returned as a numeric value in the interval [180, -180]. The longitude is returned as a numeric value in the interval [90, -90]. If the argument is NULL, the return value is NULL. If the argument is invalid, an ER_GIS_INVALID_DATA is thrown.

Examples

See Also

  • ST_GeoHash

This page is licensed: CC BY-SA / Gnu FDL

MariaDB 12.0
ST_PointFromGeoHash(geohash, srid)
SELECT ST_ASTEXT(ST_POINTFROMGEOHASH("s00twy01mtw037m",0));
+-----------------------------------------------------+
| ST_ASTEXT(ST_POINTFROMGEOHASH("s00twy01mtw037m",0)) |
+-----------------------------------------------------+
| POINT(1 1)                                          |
+-----------------------------------------------------+

SELECT ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(180,90,20),0));
+---------------------------------------------------------+
| ST_ASTEXT(ST_POINTFROMGEOHASH(ST_GEOHASH(180,90,20),0)) |
+---------------------------------------------------------+
| POINT(180 90)                                           |
+---------------------------------------------------------+

ST_INTERSECTION

Returns a geometry representing the point set intersection of two geometries. It outputs the spatial region shared by both input objects.

Syntax

ST_INTERSECTION(g1,g2)

Description

Returns a geometry that is the intersection, or shared portion, of geometry g1 and geometry g2.

Examples

This page is licensed: CC BY-SA / Gnu FDL

ST_BOUNDARY

Returns the combinatorial boundary of a geometry. For a Polygon, this is the line string defining the boundary.

Syntax

ST_BOUNDARY(g)
BOUNDARY(g)

Description

Returns a geometry that is the closure of the combinatorial boundary of the geometry value g.

BOUNDARY() is a synonym.

Examples

This page is licensed: GPLv2, originally from

ST_DISJOINT

Checks if two geometries are disjoint. Returns 1 if the geometries share no points, 0 otherwise.

Syntax

Description

Returns 1 or 0

DISJOINT

Synonym for ST_DISJOINT. Checks if two geometries are disjoint, meaning they have no points in common.

Syntax

Description

Returns 1 or

SET @g1 = ST_GEOMFROMTEXT('POINT(2 1)');

SET @g2 = ST_GEOMFROMTEXT('LINESTRING(2 1, 0 2)');

SELECT ASTEXT(ST_INTERSECTION(@g1,@g2));
+----------------------------------+
| ASTEXT(ST_INTERSECTION(@g1,@g2)) |
+----------------------------------+
| POINT(2 1)                       |
+----------------------------------+
SELECT ST_AsText(ST_Boundary(ST_GeomFromText('LINESTRING(3 3,0 0, -3 3)')));
+----------------------------------------------------------------------+
| ST_AsText(ST_Boundary(ST_GeomFromText('LINESTRING(3 3,0 0, -3 3)'))) |
+----------------------------------------------------------------------+
| MULTIPOINT(3 3,-3 3)                                                 |
+----------------------------------------------------------------------+

SELECT ST_AsText(ST_Boundary(ST_GeomFromText('POLYGON((3 3,0 0, -3 3, 3 3))')));
+--------------------------------------------------------------------------+
| ST_AsText(ST_Boundary(ST_GeomFromText('POLYGON((3 3,0 0, -3 3, 3 3))'))) |
+--------------------------------------------------------------------------+
| LINESTRING(3 3,0 0,-3 3,3 3)                                             |
+--------------------------------------------------------------------------+
fill_help_tables.sql
0
to indicate whether
g1
is spatially disjoint from (does not intersect)
g2
.

DISJOINT() 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)
to indicate whether geometry
g1
is spatially disjoint from (does not intersect with) geometry
g2
.

ST_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().

Examples

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(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 |
+----------------------+

ST_CROSSES

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.

Syntax

Description

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 CROSSES(), based on the original MySQL implementation, uses object bounding rectangles.

Examples

This page is licensed: CC BY-SA / Gnu FDL

Polygon
MultiPolygon
Point
MultiPoint
ST_CROSSES(g1,g2)
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 |
+---------------------+

Geometry Constructors

Learn about geometry constructors in MariaDB Server. This section details SQL functions for creating spatial data types like points, lines, and polygons, enabling geospatial data management.