Geometry Accessor Functions Explained - AnalyticDB for MySQL

Accessor functions extract BIGINT, DOUBLE, or GEOMETRY values from geometry objects. Use them to query coordinates, dimensions, lengths, validity, and structural properties of spatial data.

Function overview

Function	Returns	Description
ST_XMax	DOUBLE	Maximum X coordinate of a geometry
ST_YMax	DOUBLE	Maximum Y coordinate of a geometry
ST_XMin	DOUBLE	Minimum X coordinate of a geometry
ST_YMin	DOUBLE	Minimum Y coordinate of a geometry
ST_Distance	DOUBLE	Linear distance between two geometries
ST_Distance_Sphere	DOUBLE	Spherical distance between two geometries
ST_Area	DOUBLE	Euclidean area of a geometry in 2D space
ST_Centroid	GEOMETRY	Geometric center of a geometry
ST_ConvexHull	GEOMETRY	Smallest convex geometry enclosing all input geometries
ST_CoordDim	TINYINT	Dimension of coordinate components
ST_Dimension	TINYINT	Inherent dimension of a geometry
ST_IsClosed	INT	Whether a linestring or multilinestring is closed
ST_IsEmpty	INT	Whether a geometry is empty
ST_IsValid	INT	Whether a geometry is valid
Geometry_invalid_reason	VARCHAR	Reason a geometry is invalid
ST_Length	DOUBLE	Length of a linestring, or great-circle distance for a geography
Line_locate_point	DOUBLE	Position of the closest point on a linestring to a given point, as a fraction of line length
Line_interpolate_point	GEOMETRY	Point at a fractional distance along a line
Line_interpolate_points	GEOMETRY	All points at a fractional distance along a line
ST_NumInteriorRing	BIGINT	Number of interior rings in a polygon
ST_InteriorRings	Array[GEOMETRY]	All interior rings in a polygon
ST_NumGeometries	INT	Number of geometries in a collection
ST_GeometryN	GEOMETRY	Geometry element at a given index
ST_PointN	GEOMETRY	Vertex of a linestring at a given index
ST_Geometries	GEOMETRY	Array of geometries in a collection
ST_InteriorRingN	GEOMETRY	Interior ring at a given index
ST_NumPoints	BIGINT	Number of points in a geometry
ST_IsRing	INT	Whether a linestring is closed and simple
ST_StartPoint	GEOMETRY	First point of a line geometry
Simplify_geometry	GEOMETRY	Simplified version of a geometry using the Ramer-Douglas-Peucker algorithm
ST_EndPoint	GEOMETRY	Last point of a line geometry
ST_Points	GEOMETRY	Array of points from a linestring
ST_X	DOUBLE	X coordinate of a point
ST_Y	DOUBLE	Y coordinate of a point
ST_GeometryType	VARCHAR	Type of a geometry

Functions that return GEOMETRY produce binary output when queried directly with SELECT. Wrap the result in ST_AsText() to get readable text. For functions that return arrays of geometries, use the transform function with a Lambda expression to convert each element.

ST_XMax

ST_XMax(g1)

Returns the maximum X coordinate of g1.

Return type: DOUBLE

Example:

SELECT ST_XMax(ST_GeomFromText('POINT (1.5 2.5)'));

+----------------------------------------------+
| ST_XMax(ST_GeomFromText('POINT (1.5 2.5)'))  |
+----------------------------------------------+
|                         1.5                  |

Related functions: ST_XMin, ST_YMax, ST_YMin

ST_YMax

ST_YMax(g1)

Returns the maximum Y coordinate of g1.

Return type: DOUBLE

Example:

SELECT ST_YMax(ST_GeomFromText('POINT (1.5 2.5)'));

+----------------------------------------------+
| ST_YMax(ST_GeomFromText('POINT (1.5 2.5)'))  |
+----------------------------------------------+
|                         2.5                  |

Related functions: ST_YMin, ST_XMax, ST_XMin

ST_XMin

ST_XMin(g1)

Returns the minimum X coordinate of g1.

Return type: DOUBLE

Example:

SELECT ST_XMin(ST_GeomFromText('MULTIPOINT (1 2, 2 4, 3 6, 4 8)'));

+-------------------------------------------------------------+
| ST_XMin(ST_GeomFromText('MULTIPOINT (1 2, 2 4, 3 6, 4 8)')) |
+-------------------------------------------------------------+
|                         1.0                                 |

Related functions: ST_XMax, ST_YMin, ST_YMax

ST_YMin

ST_YMin(g1)

Returns the minimum Y coordinate of g1.

Return type: DOUBLE

Example:

SELECT ST_YMin(ST_GeomFromText('MULTIPOINT (1 2, 2 4, 3 6, 4 8)'));

+-------------------------------------------------------------+
| ST_YMin(ST_GeomFromText('MULTIPOINT (1 2, 2 4, 3 6, 4 8)')) |
+-------------------------------------------------------------+
|                        2.0                                  |

Related functions: ST_YMax, ST_XMin, ST_XMax

ST_Distance

ST_Distance(g1, g2)

Returns the linear distance between g1 and g2.

Return type: DOUBLE

Example:

SELECT ST_Distance(ST_Point(1,1), ST_Point(2,2));

+-------------------------------------------+
| ST_Distance(ST_Point(1,1), ST_Point(2,2)) |
+-------------------------------------------+
|                        1.4142135623730951 |

Related functions: ST_Distance_Sphere, ST_Length

ST_Distance_Sphere

ST_Distance_Sphere(g1, g2 [, radius])

Returns the spherical distance between g1 and g2. The optional radius parameter sets the sphere radius in meters. The default radius is 6,370,986 meters.

Return type: DOUBLE

Example:

SELECT ST_Distance_Sphere(point(1,1), point(2,2));

+--------------------------------------------+
| ST_Distance_Sphere(point(1,1), point(2,2)) |
+--------------------------------------------+
|                         157225.08654191086 |

Related functions: ST_Distance, ST_Length

ST_Area

ST_Area(g1)

Returns the Euclidean area of g1 in 2D space.

Return type: DOUBLE

Example:

SELECT ST_Area(ST_GeometryFromText('POLYGON ((2 2, 2 6, 6 6, 6 2))'));

+------------------------------------------------------------------------+
| ST_Area(ST_GeometryFromText('POLYGON ((2 2, 2 6, 6 6, 6 2))'))         |
+------------------------------------------------------------------------+
|                        16.0                                            |

ST_Centroid

ST_Centroid(g1)

Returns the geometric center of g1.

Return type: GEOMETRY. Use ST_AsText() to convert the result to readable text.

Example:

SELECT ST_AsText(ST_Centroid(ST_GeometryFromText('POINT (3 5)')));

+-------------------------------------------------------------------+
| ST_AsText(ST_Centroid(ST_GeometryFromText('POINT (3 5)')))        |
+-------------------------------------------------------------------+
|                       POINT (3 5)                                 |

ST_ConvexHull

ST_ConvexHull(g1)

Returns the convex hull of g1. The convex hull is the smallest convex geometry that encloses all geometries in g1.

Return type: GEOMETRY. Use ST_AsText() to convert the result to readable text.

Example:

SELECT ST_AsText(ST_ConvexHull(ST_GeometryFromText('LINESTRING (20 20, 30 30)')));

+--------------------------------------------------------------------------------+
| ST_AsText(ST_ConvexHull(ST_GeometryFromText('LINESTRING (20 20, 30 30)')))     |
+--------------------------------------------------------------------------------+
|               POLYGON ((1 1, 5 1, 6 6, 1 1))                                   |

ST_CoordDim

ST_CoordDim(g1)

Returns the dimension of coordinate components of g1.

Return type: TINYINT

Example:

SELECT ST_CoordDim(ST_GeometryFromText('POLYGON ((1 1, 1 4, 4 4, 4 1))'));

+--------------------------------------------------------------------------------+
| ST_CoordDim(ST_GeometryFromText('POLYGON ((1 1, 1 4, 4 4, 4 1))'))             |
+--------------------------------------------------------------------------------+
|             2                                                                  |

Related functions: ST_Dimension

ST_Dimension

ST_Dimension(g1)

Returns the inherent dimension of g1. The inherent dimension must be less than or equal to the coordinate dimension.

Return type: TINYINT

Example:

SELECT ST_Dimension(ST_GeometryFromText('POLYGON ((1 1, 1 4, 4 4, 4 1))'));

+--------------------------------------------------------------------------------+
| ST_Dimension(ST_GeometryFromText('POLYGON ((1 1, 1 4, 4 4, 4 1))'))            |
+--------------------------------------------------------------------------------+
|             2                                                                  |

Related functions: ST_CoordDim

ST_IsClosed

ST_IsClosed(g1)

Returns whether the start and end points of a linestring or multilinestring coincide. Returns 1 (true) if closed, 0 (false) otherwise.

Return type: INT

Example:

SELECT ST_IsClosed(ST_GeometryFromText('LINESTRING (1 1, 2 2, 1 3, 1 1)'));

+-------------------------------------------------------------------------------+
| ST_IsClosed(ST_GeometryFromText('LINESTRING (1 1, 2 2, 1 3, 1 1)'))           |
+-------------------------------------------------------------------------------+
|               1                                                               |

Related functions: ST_IsRing, ST_IsValid

ST_IsEmpty

ST_IsEmpty(g1)

Returns whether g1 is an empty geometry collection, polygon, or point.

Return type: INT

Usage notes:

Returns 0 when the geometry is empty (0 = true).
Returns 1 when the geometry is not empty (1 = false).

This is the opposite of typical boolean conventions. A return value of 0 means the geometry is empty.

Example:

SELECT ST_IsEmpty(ST_GeometryFromText('POINT (1.5 2.5)'));

+-------------------------------------------------------------+
| ST_IsEmpty(ST_GeometryFromText('POINT (1.5 2.5)'))          |
+-------------------------------------------------------------+
|              0                                              |

The result is 0 because POINT (1.5 2.5) is not an empty geometry.

Related functions: ST_IsValid, ST_IsClosed

ST_IsValid

ST_IsValid(g1)

Returns whether g1 is a valid geometry. Returns 1 (true) if valid.

Return type: INT

Example:

SELECT ST_IsValid(ST_GeometryFromText('LINESTRING (1 1, 2 2, 1 3, 1 1)'));

+------------------------------------------------------------------------+
| ST_IsValid(ST_GeometryFromText('LINESTRING (1 1, 2 2, 1 3, 1 1)'))     |
+------------------------------------------------------------------------+
|                      1                                                 |

Related functions: Geometry_invalid_reason, ST_IsClosed

Geometry_invalid_reason

Geometry_invalid_reason(g1)

Returns the reason why g1 is invalid. Use this alongside ST_IsValid to diagnose invalid geometries.

Return type: VARCHAR

Example:

SELECT Geometry_invalid_reason(ST_GeometryFromText('POLYGON ((0 0, 1 1, 0 1, 1 0, 0 0))'));

+----------------------------------------------------------------------------------------+
| Geometry_invalid_reason(ST_GeometryFromText('POLYGON ((0 0, 1 1, 0 1, 1 0, 0 0))'))    |
+----------------------------------------------------------------------------------------+
|     Intersecting or overlapping segments at or near (1.0 0.0) and (1.0 1.0)            |

Related functions: ST_IsValid

ST_Length

ST_Length(g1)

Returns the Euclidean length for a linestring or multilinestring, or the great-circle distance for a geography.

Return type: DOUBLE

Example:

SELECT ST_Length(ST_GeometryFromText('LINESTRING (1 1, 1 2)'));

+----------------------------------------------------------+
| ST_Length(ST_GeometryFromText('LINESTRING (1 1, 1 2)'))  |
+----------------------------------------------------------+
|          1.0                                             |

Related functions: ST_Distance, ST_Distance_Sphere

Line_locate_point

Line_locate_point(g1, g2)

Returns the position of the closest point on linestring g1 to point g2, expressed as a fraction of the total line length.

Return type: DOUBLE

Example:

SELECT Line_locate_point(ST_GeometryFromText('LINESTRING (0 0, 0 1)'), ST_Point(0, 0.2));

+-------------------------------------------------------------------------------------+
| Line_locate_point(ST_GeometryFromText('LINESTRING (0 0, 0 1)'), ST_Point(0, 0.2))   |
+-------------------------------------------------------------------------------------+
|                                         0.2                                         |

Related functions: Line_interpolate_point, Line_interpolate_points

Line_interpolate_point

Line_interpolate_point(g1, d)

Returns the point at fractional distance d from the start of linestring g1.

Return type: GEOMETRY. Use ST_AsText() to convert the result to readable text.

Example:

SELECT ST_AsText(Line_interpolate_point(ST_GeometryFromText('LINESTRING (0 0, 2 2)'), 0.5));

+----------------------------------------------------------------------------------------+
| ST_AsText(Line_interpolate_point(ST_GeometryFromText('LINESTRING (0 0, 2 2)'), 0.5))   |
+----------------------------------------------------------------------------------------+
|                                        POINT (1 1)                                     |

Related functions: Line_interpolate_points, Line_locate_point

Line_interpolate_points

Line_interpolate_points(g1, d)

Returns all points at fractional distance d from the start of linestring g1. The function returns a GEOMETRY array. Use the transform function with a Lambda expression to convert each element to text.

Return type: GEOMETRY

Example:

SELECT transform(Line_interpolate_points(ST_GeometryFromText('LINESTRING (0 0, 1 1, 10 10)'), 0.4), x -> ST_AsText(x));

+-----------------------------------------------------------------------------------------------------------------+
| transform(Line_interpolate_points(ST_GeometryFromText('LINESTRING (0 0, 1 1, 10 10)'), 0.4), x -> ST_AsText(x)) |
+-----------------------------------------------------------------------------------------------------------------+
|      ["POINT (4.000000000000001 4.000000000000001)","POINT (8 8)"]                                               |

Related functions: Line_interpolate_point, Line_locate_point

ST_NumInteriorRing

ST_NumInteriorRing(g1)

Returns the number of interior rings in polygon g1.

Return type: BIGINT

Example:

SELECT ST_NumInteriorRing(ST_GeometryFromText('POLYGON ((0 0, 0 5, 5 5, 5 0, 0 0))'));

+-------------------------------------------------------------------------------------+
| ST_NumInteriorRing(ST_GeometryFromText('POLYGON ((0 0, 0 5, 5 5, 5 0, 0 0))'))      |
+-------------------------------------------------------------------------------------+
|                                      0                                              |

Related functions: ST_InteriorRings, ST_InteriorRingN

ST_InteriorRings

ST_InteriorRings(g1)

Returns a geometry array of all interior rings in g1.

Return type: Array[GEOMETRY]

Usage notes:

Returns an empty array if the polygon has no interior rings.
Returns null if g1 is an empty geometry.
Returns an error if g1 is not a polygon.

Example:

SELECT ST_InteriorRings(ST_GeometryFromText('POLYGON EMPTY'));

+----------------------------------------------------------------------+
| ST_InteriorRings(ST_GeometryFromText('POLYGON EMPTY'))               |
+----------------------------------------------------------------------+
|                                    null                              |

Related functions: ST_NumInteriorRing, ST_InteriorRingN

ST_NumGeometries

ST_NumGeometries(g1)

Returns the number of geometries in collection g1.

Return type: INT

Usage notes:

Returns 1 for single geometry types (e.g., POINT, LINESTRING).
Returns 0 for empty geometries.
For GEOMETRYCOLLECTION, an empty geometry inside the collection still counts as one geometry.

Example:

SELECT ST_NumGeometries(ST_GeometryFromText('POINT (1 2)'));

+----------------------------------------------------------------------+
| ST_NumGeometries(ST_GeometryFromText('POINT (1 2)'))                 |
+----------------------------------------------------------------------+
|                                    1                                 |

Related functions: ST_GeometryN, ST_Geometries

ST_GeometryN

ST_GeometryN(g, i)

Returns the geometry element at index i in collection g. Indexes start from 1.

Return type: GEOMETRY. Use ST_AsText() to convert the result to readable text.

Usage notes:

If g is a GEOMETRYCOLLECTION or MULTI* object, returns the geometry at index i.
Returns null if i is less than 1 or greater than the total number of elements.

Example:

SELECT ST_ASText(ST_GeometryN(ST_GeometryFromText('POINT (1 2)'), 1));

+---------------------------------------------------------------------------------+
|   ST_ASText(ST_GeometryN(ST_GeometryFromText('POINT (1 2)'), 1))                |
+---------------------------------------------------------------------------------+
|                  POINT (1 2)                                                    |

Related functions: ST_NumGeometries, ST_Geometries, ST_PointN

ST_PointN

ST_PointN(g, i)

Returns the vertex at index i in linestring g. Indexes start from 1.

Return type: GEOMETRY. Use ST_AsText() to convert the result to readable text.

Usage notes:

Returns null if i is less than 1 or greater than the total number of points in the linestring.

Example:

SELECT ST_ASText(ST_PointN(ST_GeometryFromText('LINESTRING(1 2, 3 4, 5 6, 7 8)'), 3));

+---------------------------------------------------------------------------------------+
|   ST_ASText(ST_PointN(ST_GeometryFromText('LINESTRING(1 2, 3 4, 5 6, 7 8)'), 3))      |
+---------------------------------------------------------------------------------------+
|                  POINT (5 6)                                                          |

Related functions: ST_StartPoint, ST_EndPoint, ST_NumPoints

ST_Geometries

ST_Geometries(g)

Returns an array of all geometries in collection g. Use the transform function with a Lambda expression to convert each element to text.

Return type: GEOMETRY

Usage notes:

Returns a one-element array if g is not a multi-geometry.
Returns null if g is empty.

Example:

SELECT transform(ST_Geometries(ST_GeometryFromText('POINT (1 5)')), x -> ST_ASText(x));

+---------------------------------------------------------------------------------------+
|   transform(ST_Geometries(ST_GeometryFromText('POINT (1 5)')), x -> ST_ASText(x))     |
+---------------------------------------------------------------------------------------+
|                  POINT (1 5)                                                          |

Related functions: ST_NumGeometries, ST_GeometryN

ST_InteriorRingN

ST_InteriorRingN(g, d)

Returns the interior ring at index d in polygon g. Indexes start from 1.

Return type: GEOMETRY. Use ST_AsText() to convert the result to readable text.

Usage notes:

Returns null if d is less than 1 or greater than the total number of interior rings.
Returns an error if g is not a polygon.

Example:

SELECT ST_ASText(ST_InteriorRingN(ST_GeometryFromText('POLYGON ((0 0, 0 3, 3 3, 3 0, 0 0), (1 1, 1 2, 2 2, 2 1, 1 1))'), 1));

+-----------------------------------------------------------------------------------------------------------------------------+
|    ST_ASText(ST_InteriorRingN(ST_GeometryFromText('POLYGON ((0 0, 0 3, 3 3, 3 0, 0 0), (1 1, 1 2, 2 2, 2 1, 1 1))'), 1))    |
+-----------------------------------------------------------------------------------------------------------------------------+
|                 LINESTRING (1 1, 1 2, 2 2, 2 1, 1 1)                                                                        |

Related functions: ST_NumInteriorRing, ST_InteriorRings

ST_NumPoints

ST_NumPoints(g)

Returns the number of points in geometry g.

Return type: BIGINT

Example:

SELECT ST_NumPoints(ST_GeometryFromText('POINT (1 2)'));

+-------------------------------------------------------------------------+
|     ST_NumPoints(ST_GeometryFromText('POINT (1 2)'))                    |
+-------------------------------------------------------------------------+
|                 1                                                       |

Related functions: ST_PointN, ST_Points

ST_IsRing

ST_IsRing(g)

Returns 1 (true) if the linestring is both closed and simple, 0 (false) otherwise. Both conditions must be met.

Return type: INT

Example:

SELECT ST_IsRing(ST_GeometryFromText('LINESTRING (1 1, 1 3, 2 3, 2 1, 1 1)'));

+---------------------------------------------------------------------------------+
|     ST_IsRing(ST_GeometryFromText('LINESTRING (1 1, 1 3, 2 3, 2 1, 1 1)'))      |
+---------------------------------------------------------------------------------+
|                 1                                                               |

Related functions: ST_IsClosed, ST_StartPoint, ST_EndPoint

ST_StartPoint

ST_StartPoint(g)

Returns the first point of line geometry g.

Return type: GEOMETRY. Use ST_AsText() to convert the result to readable text.

Example:

SELECT ST_AsText(ST_StartPoint(ST_GeometryFromText('LINESTRING (8 4, 4 8, 5 6)')));

+-------------------------------------------------------------------------------------+
|     ST_AsText(ST_StartPoint(ST_GeometryFromText('LINESTRING (8 4, 4 8, 5 6)')))     |
+-------------------------------------------------------------------------------------+
|                 POINT (8 4)                                                         |

Related functions: ST_EndPoint, ST_PointN

Simplify_geometry

Simplify_geometry(g, d)

Returns a simplified version of geometry g using the Ramer-Douglas-Peucker algorithm. The parameter d is the distance tolerance. This function guarantees that derived geometries, especially polygons, are valid.

Return type: GEOMETRY. Use ST_AsText() to convert the result to readable text.

Example:

SELECT ST_AsText(Simplify_geometry(ST_GeometryFromText('POLYGON ((1 0, 2 1, 3 1, 3 1, 4 1, 1 0))'), 1.5));

+-----------------------------------------------------------------------------------------------------------+
|     ST_AsText(Simplify_geometry(ST_GeometryFromText('POLYGON ((1 0, 2 1, 3 1, 3 1, 4 1, 1 0))'), 1.5))    |
+-----------------------------------------------------------------------------------------------------------+
|                  POLYGON ((1 0, 4 1, 2 1, 1 0))                                                           |

ST_EndPoint

ST_EndPoint(g)

Returns the last point of line geometry g.

Return type: GEOMETRY. Use ST_AsText() to convert the result to readable text.

Example:

SELECT ST_AsText(ST_EndPoint(ST_GeometryFromText('LINESTRING (8 4, 4 8, 5 6)')));

+-----------------------------------------------------------------------------------+
| ST_AsText(ST_EndPoint(ST_GeometryFromText('LINESTRING (8 4, 4 8, 5 6)')))         |
+-----------------------------------------------------------------------------------+
|                  POINT (5 6)                                                      |

Related functions: ST_StartPoint, ST_PointN

ST_Points

ST_Points(g)

Returns an array of all points from linestring geometry g. Use the transform function with a Lambda expression to convert each element to text.

Return type: GEOMETRY

Example:

SELECT transform(ST_Points(ST_GeometryFromText('POINT (0 0)')), x -> ST_AsText(x));

+-----------------------------------------------------------------------------------+
| transform(ST_Points(ST_GeometryFromText('POINT (0 0)')), x -> ST_AsText(x))       |
+-----------------------------------------------------------------------------------+
|                  ["POINT (0 0)"]                                                  |

Related functions: ST_NumPoints, ST_PointN

ST_X

ST_X(g)

Returns the X coordinate of point g.

Return type: DOUBLE

Example:

SELECT ST_X(ST_GeometryFromText('POINT (1 2)'));

+------------------------------------------------+
|ST_X(ST_GeometryFromText('POINT (1 2)'))        |
+------------------------------------------------+
|                1.0                             |

Related functions: ST_Y, ST_XMax, ST_XMin

ST_Y

ST_Y(g)

Returns the Y coordinate of point g.

Return type: DOUBLE

Example:

SELECT ST_Y(ST_GeometryFromText('POINT (1 2)'));

+------------------------------------------------+
|ST_Y(ST_GeometryFromText('POINT (1 2)'))        |
+------------------------------------------------+
|                2.0                             |

Related functions: ST_X, ST_YMax, ST_YMin

ST_GeometryType

ST_GeometryType(g1)

Returns the type of geometry g1 as a string.

Return type: VARCHAR

Example:

SELECT ST_GeometryType(ST_Point(1, 4))

+---------------------------------------+
|ST_GeometryType(ST_Point(1, 4))        |
+---------------------------------------+
|                 ST_Point              |