Accessor functions can be used to obtain values of the BIGINT, DOUBLE, or GEOMERTY type from different strings.

  • ST_XMax: returns the maximum X coordinate of g1.
  • ST_YMax: returns the maximum Y coordinate of g1.
  • ST_XMin: returns the minimum X coordinate of g1.
  • ST_YMin: returns the minimum Y coordinate of g1.
  • ST_Distance: returns the linear distance between g1 and g2.
  • ST_Distance_Sphere: returns the spherical distance between g1 and g2. You can specify the sphere radius. The default radius is 6,370,986 meters.
  • ST_Area: returns the Euclidean area of g1 in 2D space.
  • ST_Centroid: returns the geometric center of g1.
  • ST_ConvexHull: returns the convex hull of g1. The convex hull is the smallest convex geometry that encloses all geometries in g1.
  • ST_CoordDim: returns the dimension of coordinate components of g1.
  • ST_Dimension: returns the inherent dimension of g1. The inherent dimension must be less than or equal to the coordinate dimension.
  • ST_IsClosed: returns true if the input geometry is a linestring or multilinestring and the start and end points of the linestring or multilinestring coincide.
  • ST_IsEmpty: returns true if g1 is an empty geometry collection, polygon, or point.
  • ST_IsValid: returns true if g1 is valid.
  • geometry_invalid_reason: returns the reason why g1 is invalid.
  • ST_Length: returns the Euclidean length of a linestring or multilinestring. If the input geometry is of the GEOGRAPHY type, the great-circle distance is returned.
  • line_locate_point: returns a float that represents the location of the closest point on the specified linestring to the specified point as a fraction of total line length.
  • line_interpolate_point: returns a point along a line at a fractional distance from the start of the line.
  • line_interpolate_points: returns all points along a line at a fractional distance from the start of the line.
  • ST_NumInteriorRing: returns the number of interior rings in a polygon geometry.
  • ST_InteriorRings: returns a geometry array of all interior rings found within the specified geometry, or an empty array if the polygon has no interior rings. If the specified geometry is empty, null is returned. If the specified geometry is not a polygon, an error is returned.
  • ST_NumGeometries: returns the number of geometries within the collection. If the geometry is a collection of geometries, such as a GEOMETRYCOLLECTION or MULTI* object, the number of geometries is returned. 1 is returned for each geometry. 0 is returned for empty geometries. An empty geometry in a GEOMETRYCOLLECTION object counts as one geometry.
  • ST_GeometryN: returns the geometry element at a specified integer index. Indexes start from 1. If the specified geometry is a collection of geometries, such as a GEOMETRYCOLLECTION or MULTI* object, the geometry at the specified index is returned. If the specified index is less than 1 or greater than the total number of elements in the collection, null is returned.
  • ST_PointN: returns the vertex of the specified linestring at the specified integer index. Indexes start from 1. If the specified index is less than 1 or greater than the total number of elements in the collection, null is returned.
  • ST_Geometries: returns an array of geometries within the specified collection. If the specified geometry is not a multi-geometry, a one-element array is returned. If the specified geometry is empty, null is returned.
  • ST_InteriorRingN: returns the interior ring element at the specified index. Indexes start from 1. If the specified index is less than 1 or greater than the total number of interior rings in the specified geometry, null is returned. If the specified geometry is not a polygon, an error is returned.
  • ST_NumPoints: returns the number of points within the geometry.
  • ST_IsRing: returns true if and only if the line type is closed and simple.
  • ST_StartPoint: returns the first point of a linestring geometry.
  • simplify_geometry: uses the Ramer-Douglas-Peucker algorithm to return a simplified version of the specified geometry in the geometry data type. This function ensures that created derived geometries (in particular, polygons) are invalid.
  • ST_EndPoint: returns the last point of a line geometry data type in a point geometry data type.
  • ST_Points: returns an array of points from the specified linestring geometry object.
  • ST_X: returns the X coordinate of a point.
  • ST_Y: returns the Y coordinate of a point.
  • ST_GeometryType: returns the type of the geometry as a varchar.

ST_XMax

ST_XMax(g1)
  • Description: This function returns the maximum X coordinate of g1.
  • Return value type: DOUBLE.
  • Example:
    SELECT ST_XMax(ST_GeomFromText('POINT (1.5 2.5)'));
    +--------------------------------------------+
    | ST_XMax(ST_GeomFromText('POINT (1.5 2.5)')) |
    +--------------------------------------------+
    |                         1.5                 |  

ST_YMax

ST_YMax(g1)
  • Description: This function returns the maximum Y coordinate of g1.
  • Return value type: DOUBLE.
  • Example:
    SELECT ST_YMax(ST_GeomFromText('POINT (1.5 2.5)'));
    +--------------------------------------------+| ST_YMax(ST_GeomFromText('POINT (1.5 2.5)')) |
    +--------------------------------------------+
    |                         2.5                 |

ST_XMin

ST_XMin(g1)
  • Description: This function returns the minimum X coordinate of g1.
  • Return value 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                                   |

ST_YMin

ST_YMin(g1)
  • Description: This function returns the minimum Y coordinate of g1.
  • Return value 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)')) |
    +-------------------------------------------------------------+
    |                        2                                    |

ST_Distance

ST_Distance(g1, g2)
  • Description: This function returns the linear distance between g1 and g2.
  • Return value 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 |           

ST_Distance_Sphere

ST_Distance_Sphere(g1, g2 [, radius])
  • Description: This function returns the spherical distance between g1 and g2. You can specify the sphere radius. The default radius is 6,370,986 meters.
  • Return value type: DOUBLE.
  • Example:
    SELECT ST_Distance_Sphere(point(1,1), point(2,2));
    +--------------------------------------------+
    | ST_Distance_Sphere(point(1,1), point(2,2)) |
    +--------------------------------------------+
    |                         157225.08654191086 |

ST_Area

ST_Area(g1)
  • Description: This function returns the Euclidean area of g1 in 2D space.
  • Return value 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)
  • Description: This function returns the geometric center of g1.
  • Return value type: GEOMETRY. If you execute the SELECT statement to query results of this function, the returned results are corrupted.
  • 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)
  • Description: This function returns the convex hull of g1. The convex hull is the smallest convex geometry that encloses all geometries in g1.
  • Return value type: GEOMETRY. If you execute the SELECT statement to query results of this function, the returned results are corrupted.
  • 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)
  • Description: This function returns the dimension of coordinate components of g1.
  • Return value 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                                                                  |

ST_Dimension

ST_Dimension(g1)
  • Description: This function returns the inherent dimension of g1. The inherent dimension must be less than or equal to the coordinate dimension.
  • Return value 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                                                                  |

ST_IsClosed

ST_IsClosed(g1)
  • Description: This function returns true if the input geometry is a linestring or multilinestring and the start and end points of the linestring or multilinestring.
  • Return value type: BOOLEAN.
  • 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)'))           |
    +-------------------------------------------------------------------------------+   |            true                                                               |

ST_IsEmpty

ST_IsEmpty(g1)
  • Description: This function returns true if g1 is an empty geometry collection, polygon, or point.
  • Return value type: BOOLEAN.
  • Example:
    SELECT ST_IsEmpty(ST_GeometryFromText('POINT (1.5 2.5)'));
    +-------------------------------------------------------------+
    | ST_IsEmpty(ST_GeometryFromText('POINT (1.5 2.5)'))          |
    +-------------------------------------------------------------+   
    |          false                                              |

ST_IsValid

ST_IsValid(g1)
  • Description: This function returns true if g1 is valid.
  • Return value type: BOOLEAN.
  • Example:
    SELECT ST_IsValid(ST_GeometryFromText('MULTIPOINT (1 2, 3 4)');
    +-------------------------------------------------------------+
    | ST_IsValid(ST_GeometryFromText('MULTIPOINT (1 2, 3 4)')     |
    +-------------------------------------------------------------+   
    |          true                                               | 

geometry_invalid_reason

ST_IsValid(g1)
  • Description: This function returns the reason why g1 is invalid.
  • Return value 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)                                                                          |

ST_Length

ST_IsValid(g1)
  • Description: This function returns the Euclidean length of a linestring or multilinestring. If the input geometry is of the GEOGRAPHY type, the great-circle distance is returned.
  • Return value type: DOUBLE.
  • Example:
    SELECT ST_Length(ST_GeometryFromText('LINESTRING EMPTY'));
    +------------------------------------------------------+
    | ST_Length(ST_GeometryFromText('LINESTRING EMPTY'))   |
    +------------------------------------------------------+   
    |          0.0                                         | 

line_locate_point

line_locate_point(g1, g2)
  • Description: This function returns a float that represents the location of the closest point on the specified linestring to the specified point as a fraction of total line length.
  • Return value 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                                         | 

line_interpolate_point

line_interpolate_point(g1, d)
  • Description: This function returns a point along a line at a fractional distance from the start of the line.
  • Return value type: GEOMETRY. If you execute the SELECT statement to query results of this function, the returned results are corrupted.
  • Example:
    SELECT line_interpolate_point(ST_GeometryFromText('LINESTRING EMPTY'), 0.5);
    +-------------------------------------------------------------------------------------+
    | line_interpolate_point(ST_GeometryFromText('LINESTRING EMPTY'), 0.5)               |
    +-------------------------------------------------------------------------------------+   
    |                                      null                                           | 

line_interpolate_points

line_interpolate_points(g1, d)
  • Description: This function returns all points along a line at a fractional distance from the start of the line.
  • Return value type: GEOMETRY. If you execute the SELECT statement to query results of this function, the returned results are corrupted.
  • 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)                                                |

ST_NumInteriorRing

ST_NumInteriorRing(g1)
  • Description: This function returns the number of interior rings in a polygon geometry.
  • Return value 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                                           | 

ST_InteriorRings

ST_InteriorRings(g1)
  • Description: This function returns a geometry array of all interior rings found within the specified geometry, or an empty array if the polygon has no interior rings. If the specified geometry is empty, null is returned. If the specified geometry is not a polygon, an error is returned.
  • Return value type: Array[GEOMETRY[GEOMETRY].
  • Example:
    SELECT ST_InteriorRings(ST_GeometryFromText('POLYGON EMPTY'));
    +----------------------------------------------------------------------+
    | ST_InteriorRings(ST_GeometryFromText('POLYGON EMPTY'))               |
    +----------------------------------------------------------------------+   
    |                                    null                              | 

ST_NumGeometries

ST_NumGeometries(g1)
  • Description: This function returns the number of geometries within the collection. If the geometry is a collection of geometries, such as a GEOMETRYCOLLECTION or MULTI* object, the number of geometries is returned. 1 is returned for each geometry. 0 is returned for empty geometries. An empty geometry in a GEOMETRYCOLLECTION object counts as one geometry.
  • Return value type: INT.
  • Example:
    SELECT ST_NumGeometries(ST_GeometryFromText('POINT (1 2)'));
    +----------------------------------------------------------------------+
    | ST_NumGeometries(ST_GeometryFromText('POINT (1 2)'))                 |
    +----------------------------------------------------------------------+   
    |                                    1                                 | 

ST_GeometryN

ST_GeometryN(g, i)
  • Description: This function returns the geometry element at a specified integer index. Indexes start from 1. If the specified geometry is a collection of geometries, such as a GEOMETRYCOLLECTION or MULTI* object, the geometry at the specified index is returned. If the specified index is less than 1 or greater than the total number of elements in the collection, null is returned.
  • Return value type: GEOMETRY. If you execute the SELECT statement to query results of this function, the returned results are corrupted.
  • 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)                                                    |  

ST_PointN

ST_PointN(g, i)
  • Description: This function returns the vertex of the specified linestring at the specified integer index. Indexes start from 1. If the specified index is less than 1 or greater than the total number of elements in the collection, null is returned.
  • Return value type: GEOMETRY. If you execute the SELECT statement to query results of this function, the returned results are corrupted.
  • 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)                                                          |  

ST_Geometries

ST_Geometries(g)
  • Description: This function returns an array of geometries within the specified collection. If the specified geometry is not a multi-geometry, a one-element array is returned. If the specified geometry is empty, null is returned.
  • Return value type: GEOMETRY. If you execute the SELECT statement to query results of this function, the returned results are corrupted.
  • 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)                                                          |  

ST_InteriorRingN

ST_InteriorRingN(g, d)
  • Description: This function returns the interior ring element at the specified index. Indexes start from 1. If the specified index is less than 1 or greater than the total number of interior rings in the specified geometry, null is returned. If the specified geometry is not a polygon, an error is returned.
  • Return value type: GEOMETRY. If you execute the SELECT statement to query results of this function, the returned results are corrupted.
  • 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)                                                                        |  

ST_NumPoints

 ST_NumPoints(g)
  • Description: This function returns the number of points within the geometry.
  • Return value type: BIGINT.
  • Example:
    SELECT  ST_NumPoints(ST_GeometryFromText('POINT (1 2)'))                  +
    *-------------------------------------------------------------------------+
    |     ST_NumPoints(ST_GeometryFromText('POINT (1 2)'))                    |
    +-------------------------------------------------------------------------+   
    |                 1                                                       |  

ST_IsRing

ST_IsRing(g)
  • Description: This function returns true if and only if the line type is closed and simple.
  • Return value type: BOOLEAN.
  • Example:
    SELECT  ST_IsRing(ST_GeometryFromText('LINESTRING (8 4, 4 8)'))           +
    *-------------------------------------------------------------------------+
    |     ST_IsRing(ST_GeometryFromText('LINESTRING (8 4, 4 8)'))             |
    +-------------------------------------------------------------------------+   
    |                 false                                                   |  

ST_StartPoint

ST_StartPoint(g)
  • Description: This function returns the first point of a linestring geometry.
  • Return value type: GEOMETRY. If you execute the SELECT statement to query results of this function, the returned results are corrupted.
  • 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 (5 6)                                                         | 

simplify_geometry

simplify_geometry(g, d)
  • Description: This function uses the Ramer-Douglas-Peucker algorithm to return a simplified version of the specified geometry in the geometry data type. This function ensures that created derived geometries (in particular, polygons) are invalid.
  • Return value type: GEOMETRY. If you execute the SELECT statement to query results of this function, the returned results are corrupted.
  • 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)
  • Description: This function returns the last point of a line geometry data type in a point geometry data type.
  • Return value type: GEOMETRY. If you execute the SELECT statement to query results of this function, the returned results are corrupted.
  • 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)                                                      | 

ST_Points

ST_Points(g)
  • Description: This function returns an array of points from the specified linestring geometry object.
  • Return value type: GEOMETRY. If you execute the SELECT statement to query results of this function, the returned results are corrupted.
  • 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))       |
    +-----------------------------------------------------------------------------------+   
    |                  0 0                                                              | 

ST_X

ST_X(g)
  • Description: This function returns the X coordinate of a point.
  • Return value type: DOUBLE.
  • Example:
    SELECT ST_X(ST_GeometryFromText('POINT (1 2)'))  +
    *------------------------------------------------+
    |ST_X(ST_GeometryFromText('POINT (1 2)'))        |
    +------------------------------------------------+   
    |                1.0                             | 

ST_Y

ST_Y(g)
  • Description: This function returns the Y coordinate of a point.
  • Return value type: DOUBLE.
  • Example:
    SELECT ST_Y(ST_GeometryFromText('POINT (1 2)'))  +
    *------------------------------------------------+
    |ST_Y(ST_GeometryFromText('POINT (1 2)'))        |
    +------------------------------------------------+   
    |                2.0                             | 

ST_GeometryType

ST_Within(g1)
  • Description: This function returns the type of the geometry as a varchar.
  • Return value type: VARCHAR.
  • Example:
    SELECT ST_GeometryType(ST_Point(1, 4))  +
    *---------------------------------------+
    |ST_GeometryType(ST_Point(1, 4))        |
    +---------------------------------------+   
    |                 false                 |