DocsICP FilingConsole
官方文档
首页

Accessor functions

更新时间：
复制 MD 格式
我的收藏

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

FunctionReturnsDescription
ST_XMaxDOUBLEMaximum X coordinate of a geometry
ST_YMaxDOUBLEMaximum Y coordinate of a geometry
ST_XMinDOUBLEMinimum X coordinate of a geometry
ST_YMinDOUBLEMinimum Y coordinate of a geometry
ST_DistanceDOUBLELinear distance between two geometries
ST_Distance_SphereDOUBLESpherical distance between two geometries
ST_AreaDOUBLEEuclidean area of a geometry in 2D space
ST_CentroidGEOMETRYGeometric center of a geometry
ST_ConvexHullGEOMETRYSmallest convex geometry enclosing all input geometries
ST_CoordDimTINYINTDimension of coordinate components
ST_DimensionTINYINTInherent dimension of a geometry
ST_IsClosedINTWhether a linestring or multilinestring is closed
ST_IsEmptyINTWhether a geometry is empty
ST_IsValidINTWhether a geometry is valid
Geometry_invalid_reasonVARCHARReason a geometry is invalid
ST_LengthDOUBLELength of a linestring, or great-circle distance for a geography
Line_locate_pointDOUBLEPosition of the closest point on a linestring to a given point, as a fraction of line length
Line_interpolate_pointGEOMETRYPoint at a fractional distance along a line
Line_interpolate_pointsGEOMETRYAll points at a fractional distance along a line
ST_NumInteriorRingBIGINTNumber of interior rings in a polygon
ST_InteriorRingsArray[GEOMETRY]All interior rings in a polygon
ST_NumGeometriesINTNumber of geometries in a collection
ST_GeometryNGEOMETRYGeometry element at a given index
ST_PointNGEOMETRYVertex of a linestring at a given index
ST_GeometriesGEOMETRYArray of geometries in a collection
ST_InteriorRingNGEOMETRYInterior ring at a given index
ST_NumPointsBIGINTNumber of points in a geometry
ST_IsRingINTWhether a linestring is closed and simple
ST_StartPointGEOMETRYFirst point of a line geometry
Simplify_geometryGEOMETRYSimplified version of a geometry using the Ramer-Douglas-Peucker algorithm
ST_EndPointGEOMETRYLast point of a line geometry
ST_PointsGEOMETRYArray of points from a linestring
ST_XDOUBLEX coordinate of a point
ST_YDOUBLEY coordinate of a point
ST_GeometryTypeVARCHARType 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              |
Previous：Spatial relationship functionsNext：Spatial constructors