All Products
Search

This Product

    Geospatial analysis functions

    Document Center
    Product Details

    Geospatial analysis functions

    Last Updated: Aug 06, 2019

    0. Introduction

    To meet customer requirements on geospatial data analysis in the cloud, Alibaba Cloud Data Lake Analytics (DLA) supports processing geospatial data in various formats and is compliant with the <span data-type="color" style="color:rgb(34, 34, 34)"><span data-type="background" style="background-color:rgb(255, 255, 255)">Open Geospatial Consortium’s(OGC) OpenGIS standards. The supported common data formats include:</span></span>

    • WKT
    • WKB
    • GeoJSON
    • ESRI Geometry Object JSON
    • ESRI Shape

    DLA uses the 4326 coordinate system standard. <span data-type="color" style="color:rgb(36, 41, 46)"><span data-type="background" style="background-color:rgb(255, 255, 255)">EPSG 4326 uses longitude and latitude coordinates and belongs to the geographical coordinate system. GPS uses this coordinate system. </span></span>

    1. WKT data

    For more information, visit http://www.opengeospatial.org/standards/wkt-crs.

    The following WKT strings are supported:

    • POINT (0 0)
    • LINESTRING (0 0, 1 1, 1 2)
    • POLYGON ((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1))
    • MULTIPOINT (0 0, 1 2)
    • MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))
    • MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))
    • GEOMETRYCOLLECTION (POINT (2 3), LINESTRING (2 3, 3 4))

    2. WKB data

    For more information, visit https://en.wikipedia.org/wiki/Well-known_text#Well-known_binary

    Mapping between WKT data and WKB data:

    WKT WKB
    POINT (0 0) 010100000000000000000000000000000000000000
    LINESTRING (0 0, 1 1, 1 2) 01020000000300000000000000000000000000000000000000000000000000F03F000000000000F03F000000000000F03F0000000000000040
    POLYGON ((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)) 01030000000200000005000000000000000000000000000000000000000000000000001040000000000000000000000000000010400000000000001040000000000000000000000000000010400000000000000000000000000000000005000000000000000000F03F000000000000F03F000000000000F03F0000000000000040000000000000004000000000000000400000000000000040000000000000F03F000000000000F03F000000000000F03F
    MULTIPOINT (0 0, 1 2) 0104000000020000000101000000000000000000000000000000000000000101000000000000000000F03F0000000000000040
    MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4)) 01050000000200000001020000000300000000000000000000000000000000000000000000000000F03F000000000000F03F000000000000F03F0000000000000040010200000003000000000000000000004000000000000008400000000000000840000000000000004000000000000014400000000000001040
    MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1))) 01060000000200000001030000000200000005000000000000000000000000000000000000000000000000001040000000000000000000000000000010400000000000001040000000000000000000000000000010400000000000000000000000000000000005000000000000000000F03F000000000000F03F000000000000F03F0000000000000040000000000000004000000000000000400000000000000040000000000000F03F000000000000F03F000000000000F03F01030000000100000005000000000000000000F0BF000000000000F0BF00000000000000C0000000000000F0BF00000000000000C000000000000000C0000000000000F0BF00000000000000C0000000000000F0BF000000000000F0BF
    GEOMETRYCOLLECTION (POINT (2 3), LINESTRING (2 3, 3 4)) 0107000000020000000101000000000000000000004000000000000008400102000000020000000000000000000040000000000000084000000000000008400000000000001040

    3. GeoJSON data

    For information about GeoJSON, visit http://geojson.org/.

    4. ESRI Geometry Object JSON data

    For information about this format, visit http://resources.esri.com/help/9.3/arcgisserver/apis/REST/geometry.html.

    5. ESRI Shape binary format

    For information about this format, visit http://www.esri.com/LIBRARY/WHITEPAPERS/PDFS/SHAPEFILE.PDF.

    6. Function list

    Name Description
    ST_asText Converts a geometry into a WKT string.
    ST_LineFromText Generates a line geometry from a line-specific WKT string.
    ST_Point Generates a point geometry from the coordinate values (x and y) of a coordinate system.
    ST_Polygon Generates a polygon geometry from a polygon-specific string.
    ST_Area Returns the area of the geometry if it is a polygon or multipolygon. The returned value for dots and lines is 0.0. The returned value for GeometryCollection is the area sum of all geometries in the collection.
    ST_GeometryFromText Generates a geometry from a WKT string.
    ST_Buffer Returns a geometry that represents all points whose distance from the entered geometry is less than or equal to a specified distance.
    ST_Centroid Returns the geometric center of a geometry.
    ST_CoordDim Returns the dimensions of coordinate values for a geometry.
    ST_Dimension Returns the dimensions of a geometry. In this case, dimension refers to the length and width. For example, a point has no length or width, so its dimension is 0, while a line has only length but no width, so its dimension is 1.
    ST_IsClosed Determines whether a line or multiline is closed.
    ST_IsEmpty Determines whether a geometry is empty.
    ST_Length Calculates the length of a line or multiline.
    ST_XMax Returns the maximum x coordinate value of a geometry in the coordinate system.
    ST_XMin Returns the minimum x coordinate value of a geometry in the coordinate system.
    ST_YMax Returns the maximum y coordinate value of a geometry in the coordinate system.
    ST_YMin Returns the minimum y coordinate value of a geometry in the coordinate system.
    ST_NumInteriorRing Returns the number of internal loops for a polygon geometry.
    ST_NumPoints Returns the number of points (inflection points) in a geometry.
    ST_IsRing Determines whether a line geometry is a ring, for example, whether it is closed.
    ST_StartPoint Returns the first point of a line.
    ST_EndPoint Returns the last point of a line.
    ST_X Returns the x coordinate value of a point.
    ST_Y Returns the y coordinate value of a point.
    ST_Boundary Returns the closure of the combinatorial boundary of the entered geometry, as a geometry.
    ST_Envelope Returns the minimum bounding box for the supplied geometry, as a polygon.
    ST_Difference Returns a geometry that represents that part of geometry A that does not intersect with geometry B.
    ST_Distance Returns the distance between two geometries. This distance is the distance between the closest inflection points of the two geometries.
    ST_ExteriorRing Returns a line string that represents the exterior ring of a polygon geometry.
    ST_Intersection Returns a 2D geometry that represents the intersection of two input geometries.
    ST_SymDifference Returns a geometry that represents the symmetric difference in a point set between two input geometries.
    ST_Contains Determines whether geometry A is completely inside geometry B.
    ST_Crosses Returns 1 if the dimension of the geometry representing the intersection of two geometries is smaller than the maximum dimension of the two source geometries. In addition, the intersection “spatially crosses,” that is, the geometries have some, but not all interior points in common. The intersection must not equal either of the source geometries. Otherwise, 0 is returned.
    ST_Disjoint Determines whether the intersection of two geometries is an empty set.
    ST_Equals Determines whether two geometries are the same.
    ST_Intersects Determines whether the intersection of two geometries does not generate an empty set.
    ST_Overlaps Determines whether the two input geometries “spatially overlap.” This means that they intersect, but one does not completely contain another.
    ST_Relate Determines whether the two input geometries meet the conditions specified by the “DE-9IM” pattern matrix string.
    ST_Touches Determines whether the only points in common between the two geometries intersect the interior of the two geometries.
    ST_Within Determines whether geometry A is completely inside geometry B.
    ST_asBinary Returns the WKB representation of the geometry.
    ST_GeometryFromWKBHexString Returns the geometry that corresponds to the entered WKB HexString.
    ST_pointFromWKBHexString Returns the point geometry that corresponds to the entered point-specific WKB HexString.
    ST_lineFromWKBHexString Returns the line geometry that corresponds to the entered line-specific WKB HexString.
    ST_polyFromWKBHexString Returns the polygon geometry that corresponds to the entered polygon-specific WKB HexString.
    ST_MPointFromWKBHexString Returns the multipoint geometry that corresponds to the entered multipoint-specific WKB HexString.
    ST_MLineFromWKBHexString Returns the multiline geometry that corresponds to the entered multiline-specific WKB HexString.
    ST_MPolyFromWKBHexString Returns the multipolygon geometry that corresponds to the entered multipolygon-specific WKB HexString.
    ST_GeometryFromWKB Returns the geometry that corresponds to the entered WKB data.
    ST_pointFromWKB Returns the point geometry that corresponds to the entered point-specific WKB data.
    ST_lineFromWKB Returns the line geometry that corresponds to the entered line-specific WKB data.
    ST_polyFromWKB Returns the polygon geometry that corresponds to the entered polygon-specific WKB data.
    ST_MPointFromWKB Returns the multipoint geometry that corresponds to the entered multipoint-specific WKB data.
    ST_MLineFromWKB Returns the multiline geometry that corresponds to the entered multiline-specific WKB data.
    ST_MPolyFromWKB Returns the multipolygon geometry that corresponds to the entered multipolygon-specific WKB data.
    ST_GeometryFromGeoJson Returns the geometry that corresponds to the entered GeoJSON string.
    ST_GeometryFromJson Returns the geometry that corresponds to the entered ESRI Geometry Object JSON string.
    ST_asGeoJson Converts a geometry into a GeoJSON string.
    ST_asJson Converts a geometry into an ESRI Geometry Object JSON string.
    ST_GeometryFromEsriShape Returns the geometry that corresponds to the entered ESRI Shape binary data.

    7. Function definitions and examples

    • ST_asText
    1. ST_asText(GEOMETRY) -> VARCHAR

    Converts a geometry into a WKT string.

    Example:

    1. select ST_asText(ST_LineFromText('LINESTRING (0 0, 1 1, 1 2)'));
    3. +----------------------------+
    4. | _col0                      |
    5. +----------------------------+
    6. | LINESTRING (0 0, 1 1, 1 2) |
    7. +----------------------------+
    • ST_LineFromText
    1. ST_LineFromText(VARCHAR) -> GEOMETRY

    Generates a line geometry from a line-specific WKT string.

    Example:

    1. select ST_asText(ST_LineFromText('LINESTRING (0 0, 1 1, 1 2)'));
    3. +----------------------------+
    4. | _col0                      |
    5. +----------------------------+
    6. | LINESTRING (0 0, 1 1, 1 2) |
    7. +----------------------------+
    • ST_Point
    1. ST_Point(DOUBLE, DOUBLE) -> GEOMETRY

    Generates a point geometry from the coordinate values (x and y) of a coordinate system.

    Example:

    1. select ST_asText(ST_Point(30.2741500000,120.1551500000));
    3. +----------------------------+
    4. | _col0                      |
    5. +----------------------------+
    6. | POINT (30.27415 120.15515) |
    7. +----------------------------+
    • ST_Polygon
    1. ST_Polygon(VARCHAR) -> GEOMETRY

    Generates a polygon geometry from a polygon-specific string.

    Example:

    1. select ST_asText(ST_Polygon('POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))'));
    3. +----------------------------------------------------------------+
    4. | _col0                                                          |
    5. +----------------------------------------------------------------+
    6. | POLYGON ((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 1 2, 2 2, 2 1, 1 1)) |
    7. +----------------------------------------------------------------+
    • ST_Area
    1. ST_Area(Geometry) -> DOUBLE

    Returns the area of the geometry if it is a polygon or multipolygon. The returned value for dots and lines is 0.0. The returned value for GeometryCollection is the area sum of all geometries in the collection.

    Example:

    1. select ST_Area(ST_Polygon('POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))'));
    3. +-------+
    4. | _col0 |
    5. +-------+
    6. |  15.0 |
    7. +-------+
    9. select ST_Area(ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'));
    11. +-------+
    12. | _col0 |
    13. +-------+
    14. |  16.0 |
    15. +-------+
    • ST_GeometryFromText
    1. ST_GeometryFromText(VARCHAR) -> GEOMETRY

    Generates a geometry from a WKT string.

    • ST_Buffer
    1. ST_Buffer(GEOMETRY, DOUBLE) -> GEOMETRY

    <span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)">Returns a geometry that represents all points whose distance from the entered geometry is less than or equal to a specified distance. </span></span>

    • ST_Centroid
    1. ST_Centroid(GEOMETRY) -> GEOMETRY

    Returns the geometric center of a geometry.

    Example:

    1. select ST_asText(ST_Centroid(ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))')));
    3. +-----------------------+
    4. | _col0                 |
    5. +-----------------------+
    6. | POINT (1.8125 1.8125) |
    7. +-----------------------+
    9. select ST_asText(ST_Centroid(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2)')));
    11. +---------------+
    12. | _col0         |
    13. +---------------+
    14. | POINT (0.5 1) |
    15. +---------------+
    17. select ST_asText(ST_Centroid(ST_GeometryFromText('POINT (0.5 1)')));
    19. +---------------+
    20. | _col0         |
    21. +---------------+
    22. | POINT (0.5 1) |
    23. +---------------+
    25. select ST_asText(ST_Centroid(ST_GeometryFromText('POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))')));
    27. +---------------------------------------------+
    28. | _col0                                       |
    29. +---------------------------------------------+
    30. | POINT (2.033333333333333 2.033333333333333) |
    31. +---------------------------------------------+
    • ST_CoordDim
    1. ST_CoordDim(GEOMETRY) -> BIGINT

    Returns the dimensions of coordinate values for a geometry.

    Example:

    1. select ST_CoordDim(ST_GeometryFromText('POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))'));
    3. +-------+
    4. | _col0 |
    5. +-------+
    6. |     2 |
    7. +-------+
    9. select ST_CoordDim(ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'));
    11. +-------+
    12. | _col0 |
    13. +-------+
    14. |     2 |
    15. +-------+
    17. select st_coorddim(st_geometryfromtext('point Z (60.567222 -140.404 5959)'));
    19. +-------+
    20. | _col0 |
    21. +-------+
    22. |     3 |
    23. +-------+
    25. select st_coorddim(st_geometryfromtext('point M (60.567222 -140.404 5250)'));
    27. +-------+
    28. | _col0 |
    29. +-------+
    30. |     3 |
    31. +-------+
    33. select st_coorddim(st_geometryfromtext('point ZM (60.567222 -140.404 5959 5250)'));
    35. +-------+
    36. | _col0 |
    37. +-------+
    38. |     4 |
    39. +-------+
    • ST_Dimension
    1. ST_Dimension(GEOMETRY) -> BIGINT

    <span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)">Returns the dimension of a geometry. In this case, dimension refers to the length and width. For example, a point has no length or width, so its dimension is 0, while a line has only length but no width, so its dimension is 1. </span></span>

    Example:

    1. select ST_Dimension(ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'));
    3. +-------+
    4. | _col0 |
    5. +-------+
    6. |     2 |
    7. +-------+
    • ST_IsClosed
    1. ST_IsClosed(GEOMETRY) -> BOOLEAN

    Determines whether a line or multiline is closed.

    Example:

    1. select ST_IsClosed(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2)'));
    3. +-------+
    4. | _col0 |
    5. +-------+
    6. |     0 |
    7. +-------+
    9. select ST_IsClosed(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2, 0 0)'));
    11. +-------+
    12. | _col0 |
    13. +-------+
    14. |     1 |
    15. +-------+
    17. select ST_IsClosed(ST_GeometryFromText('MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))'));
    19. +-------+
    20. | _col0 |
    21. +-------+
    22. |     0 |
    23. +-------+
    • ST_IsEmpty
    1. ST_IsEmpty(GEOMETRY) -> BOOLEAN

    Determines whether a geometry is empty.

    Example:

    1. select ST_IsEmpty(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2, 0 0)'));
    3. +-------+
    4. | _col0 |
    5. +-------+
    6. |     0 |
    7. +-------+
    9. select ST_IsEmpty(null);
    11. +-------+
    12. | _col0 |
    13. +-------+
    14. |  NULL |
    15. +-------+
    17. SELECT ST_IsEmpty(ST_GeometryFromText('GEOMETRYCOLLECTION EMPTY'));
    19. +-------+
    20. | _col0 |
    21. +-------+
    22. |     1 |
    23. +-------+
    25. SELECT ST_IsEmpty(ST_GeometryFromText('POLYGON EMPTY'));
    27. +-------+
    28. | _col0 |
    29. +-------+
    30. |     1 |
    31. +-------+
    • ST_Length
    1. ST_Length(GEOMETRY) -> DOUBLE

    Calculates the length of a line or multiline.

    Example:

    1. SELECT ST_Length(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2, 0 0)'));
    3. +-------------------+
    4. | _col0             |
    5. +-------------------+
    6. | 4.650281539872885 |
    7. +-------------------+
    9. SELECT ST_Length(ST_GeometryFromText('MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))'));
    11. +-------------------+
    12. | _col0             |
    13. +-------------------+
    14. | 6.656854249492381 |
    15. +-------------------+
    • ST_XMax
    1. ST_XMax(GEOMETRY) -> DOUBLE

    Returns the maximum x coordinate value of a geometry in the coordinate system.

    Example:

    1. SELECT ST_XMax(ST_GeometryFromText('MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))'));
    3. +-------+
    4. | _col0 |
    5. +-------+
    6. |   5.0 |
    7. +-------+
    9. SELECT ST_XMax(ST_GeometryFromText('POINT (0.5 1)'));
    11. +-------+
    12. | _col0 |
    13. +-------+
    14. |   0.5 |
    15. +-------+
    • ST_XMin
    1. ST_XMin(GEOMETRY) -> DOUBLE

    Returns the minimum x coordinate value of a geometry in the coordinate system.

    Example:

    1. SELECT ST_XMin(ST_GeometryFromText('MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))'));
    3. +-------+
    4. | _col0 |
    5. +-------+
    6. |   0.0 |
    7. +-------+
    9. SELECT ST_XMin(ST_GeometryFromText('POINT (0.5 1)'));
    11. +-------+
    12. | _col0 |
    13. +-------+
    14. |   0.5 |
    15. +-------+
    • ST_YMax
    1. ST_YMax(GEOMETRY) -> DOUBLE

    Returns the maximum y coordinate value of a geometry in the coordinate system.

    Example:

    1. SELECT ST_YMax(ST_GeometryFromText('MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))'));
    3. +-------+
    4. | _col0 |
    5. +-------+
    6. |   4.0 |
    7. +-------+
    9. SELECT ST_YMax(ST_GeometryFromText('POINT (0.5 1)'));
    11. +-------+
    12. | _col0 |
    13. +-------+
    14. |   1.0 |
    15. +-------+
    • ST_YMin
    1. ST_YMin(GEOMETRY) -> DOUBLE

    Returns the minimum y coordinate value of a geometry in the coordinate system.

    Example:

    1. SELECT ST_YMin(ST_GeometryFromText('MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))'));
    3. +-------+
    4. | _col0 |
    5. +-------+
    6. |   0.0 |
    7. +-------+
    9. SELECT ST_YMin(ST_GeometryFromText('POINT (0.5 1)'));
    11. +-------+
    12. | _col0 |
    13. +-------+
    14. |   1.0 |
    15. +-------+
    • ST_NumInteriorRing
    1. ST_NumInteriorRing(GEOMETRY) -> BIGINT

    Returns the number of internal loops for a polygon geometry.

    Example:

    1. SELECT ST_NumInteriorRing(ST_GeometryFromText('POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))'));
    3. +-------+
    4. | _col0 |
    5. +-------+
    6. |     1 |
    7. +-------+
    • ST_NumPoints
    1. ST_NumPoints(GEOMETRY) -> BIGINT

    <span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)">Returns the number of points (inflection points) in a geometry. </span></span>

    Example:

    1. SELECT ST_NumPoints(ST_GeometryFromText('POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))'));
    3. +-------+
    4. | _col0 |
    5. +-------+
    6. |     8 |
    7. +-------+
    9. SELECT ST_NumPoints(ST_GeometryFromText('POLYGON EMPTY'));
    11. +-------+
    12. | _col0 |
    13. +-------+
    14. |     0 |
    15. +-------+
    17. SELECT ST_NumPoints(ST_GeometryFromText('POINT (0.5 1)'));
    19. +-------+
    20. | _col0 |
    21. +-------+
    22. |     1 |
    23. +-------+
    • ST_IsRing
    1. ST_IsRing(GEOMETRY) -> BOOLEAN

    <span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)">Determines whether a line geometry is a ring, for example, whether it is closed. </span></span>

    Example:

    1. select ST_IsRing(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2)'));
    3. +-------+
    4. | _col0 |
    5. +-------+
    6. |     0 |
    7. +-------+
    9. select ST_IsRing(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2, 0 0)'));
    11. +-------+
    12. | _col0 |
    13. +-------+
    14. |     1 |
    15. +-------+
    • ST_StartPoint
    1. ST_StartPoint(GEOMETRY) -> GEOMETRY

    <span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)">Returns the first point of a line. </span></span>

    Example:

    1. select ST_asText(ST_StartPoint(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2, 0 0)')));
    3. +-------------+
    4. | _col0       |
    5. +-------------+
    6. | POINT (0 0) |
    7. +-------------+
    • ST_EndPoint
    1. ST_EndPoint(GEOMETRY) -> GEOMETRY

    <span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)">Returns the last point of a line. </span></span>

    Example:

    1. select ST_asText(ST_EndPoint(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2)')));
    3. +-------------+
    4. | _col0       |
    5. +-------------+
    6. | POINT (1 2) |
    7. +-------------+
    • ST_X
    1. ST_X(GEOMETRY) -> DOUBLE

    Returns the x coordinate value of a point.

    Example:

    1. select ST_X(ST_GeometryFromText('POINT (0.5 1)'));
    3. +-------+
    4. | _col0 |
    5. +-------+
    6. |   0.5 |
    7. +-------+
    • ST_Y
    1. ST_Y(GEOMETRY) -> DOUBLE

    Returns the y coordinate value of a point.

    Example:

    1. select ST_Y(ST_GeometryFromText('POINT (0.5 1)'));
    3. +-------+
    4. | _col0 |
    5. +-------+
    6. |   1.0 |
    7. +-------+
    • ST_Boundary
    1. ST_Boundary(GEOMETRY) -> GEOMETRY

    Returns the closure of the combinatorial boundary of the entered geometry, as a geometry.

    Example:

    1. select ST_asText(ST_Boundary(ST_GeometryFromText('POINT (0.5 1)')));
    3. +-------------+
    4. | _col0       |
    5. +-------------+
    6. | POINT EMPTY |
    7. +-------------+
    9. select ST_asText(ST_Boundary(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2)')));
    11. +---------------------------+
    12. | _col0                     |
    13. +---------------------------+
    14. | MULTIPOINT ((0 0), (1 2)) |
    15. +---------------------------+
    17. select ST_asText(ST_Boundary(ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))')));
    19. +-------------------------------------------------------------------------------------------------------------+
    20. | _col0                                                                                                       |
    21. +-------------------------------------------------------------------------------------------------------------+
    22. | MULTILINESTRING ((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 1 2, 2 2, 2 1, 1 1), (-1 -1, -2 -1, -2 -2, -1 -2, -1 -1)) |
    23. +-------------------------------------------------------------------------------------------------------------+
    • ST_Envelope
    1. ST_Envelope(GEOMETRY) -> GEOMETRY

    <span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)">Returns the minimum bounding box for the supplied geometry, as a polygon. </span></span>

    Example:

    1. select ST_asText(ST_Envelope(ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))')));
    3. +-------------------------------------------+
    4. | _col0                                     |
    5. +-------------------------------------------+
    6. | POLYGON ((-2 -2, 4 -2, 4 4, -2 4, -2 -2)) |
    7. +-------------------------------------------+
    • ST_Difference
    1. ST_Difference(GEOMETRY, GEOMETRY) -> GEOMETRY

    Returns a geometry that represents that part of geometry A that does not intersect with geometry B.

    Example:

    1. select ST_asText(ST_Difference(
    2.   ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
    3.   ST_GeometryFromText('MULTILINESTRING ((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 1 2, 2 2, 2 1, 1 1), (-1 -1, -2 -1, -2 -2, -1 -2, -1 -1))')));
    5. +--------------------------------------------------------------------------------------------------------------+
    6. | _col0                                                                                                        |
    7. +--------------------------------------------------------------------------------------------------------------+
    8. | MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 1 2, 2 2, 2 1, 1 1)), ((-1 -1, -2 -1, -2 -2, -1 -2, -1 -1))) |
    9. +--------------------------------------------------------------------------------------------------------------+
    11. select ST_asText(ST_Difference(
    12.   ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
    13.   ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))')));
    15. +--------------------+
    16. | _col0              |
    17. +--------------------+
    18. | MULTIPOLYGON EMPTY |
    19. +--------------------+
    • ST_Distance
    1. ST_Distance(GEOMETRY, GEOMETRY) -> DOUBLE

    Returns the distance between two geometries. This distance is the distance between the closest inflection points of the two geometries.

    Example:

    1. select ST_Distance(
    2.   ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
    3.   ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'));
    5. +-------+
    6. | _col0 |
    7. +-------+
    8. |   0.0 |
    9. +-------+
    11. select ST_Distance(
    12.   ST_GeometryFromText('POINT(0 0)'),
    13.   ST_GeometryFromText('POINT(1 1)'));
    15. +--------------------+
    16. | _col0              |
    17. +--------------------+
    18. | 1.4142135623730951 |
    19. +--------------------+
    • ST_ExteriorRing
    1. ST_ExteriorRing(GEOMETRY) -> GEOMETRY

    <span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)">Returns a line string that represents the exterior ring of a polygon geometry. </span></span>

    Example:

    1. select ST_asText(ST_ExteriorRing(ST_GeometryFromText('POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))')));
    3. +--------------------------------------+
    4. | _col0                                |
    5. +--------------------------------------+
    6. | LINESTRING (0 0, 4 0, 4 4, 0 4, 0 0) |
    7. +--------------------------------------+
    • ST_Intersection
    1. ST_Intersection(GEOMETRY, GEOMETRY) -> GEOMETRY

    <span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)">Returns a 2D geometry that represents the intersection of two input geometries. </span></span>

    Example:

    1. select ST_asText(ST_Intersection(
    2.   ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
    3.   ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))')));
    5. +--------------------------------------------------------------------------------------------------------------+
    6. | _col0                                                                                                        |
    7. +--------------------------------------------------------------------------------------------------------------+
    8. | MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 1 2, 2 2, 2 1, 1 1)), ((-1 -1, -2 -1, -2 -2, -1 -2, -1 -1))) |
    9. +--------------------------------------------------------------------------------------------------------------+
    11. select ST_asText(ST_Intersection(
    12.   ST_GeometryFromText('POINT(0 0)'),
    13.   ST_GeometryFromText('POINT(1 1)')));
    15. +--------------------+
    16. | _col0              |
    17. +--------------------+
    18. | MULTIPOLYGON EMPTY |
    19. +--------------------+
    • ST_SymDifference
    1. ST_SymDifference(GEOMETRY, GEOMETRY) -> GEOMETRY

    <span data-type="color" style="color:rgb(0, 0, 0)">Returns a geometry that represents the symmetric difference in a point set between two input geometries. </span>

    Example:

    1. select ST_asText(ST_SymDifference(
    2.   ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
    3.   ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))')));
    5. +--------------------+
    6. | _col0              |
    7. +--------------------+
    8. | MULTIPOLYGON EMPTY |
    9. +--------------------+
    11. select ST_asText(ST_SymDifference(
    12.   ST_GeometryFromText('POINT(0 0)'),
    13.   ST_GeometryFromText('POINT(1 1)')));
    15. +---------------------------+
    16. | _col0                     |
    17. +---------------------------+
    18. | MULTIPOINT ((0 0), (1 1)) |
    19. +---------------------------+

    <span data-type="color" style="color:rgb(0, 0, 0)">The shadow section in the following figure shows the result of symmetric difference. Symmetric difference is a multi-surface image that includes two surfaces. One surface contains all the points that are located inside the square and outside the circle. The other surface contains all the points that are located inside the circle and outside the square. </span>

    • ST_Contains
    1. ST_Contains(GEOMETRY, GEOMETRY) -> BOOLEAN

    Determines <span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)"> whether geometry A is completely inside geometry B. </span></span>

    Example:

    1. select ST_Contains(
    2.   ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
    3.   ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'));
    5. +-------+
    6. | _col0 |
    7. +-------+
    8. |     1 |
    9. +-------+
    11. select ST_Contains(
    12.   ST_GeometryFromText('POINT(0 0)'),
    13.   ST_GeometryFromText('POINT(1 1)'));
    15. +-------+
    16. | _col0 |
    17. +-------+
    18. |     0 |
    19. +-------+
    • ST_Crosses
    1. ST_Crosses(GEOMETRY, GEOMETRY) -> BOOLEAN

    <span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)">Returns 1 if the dimension of the geometry representing the intersection of two geometries is smaller than the maximum dimension of the two source geometries. In addition, the intersection “spatially crosses,” that is, the geometries have some, but not all interior points in common. The intersection must not equal either of the source geometries. Otherwise, 0 is returned. </span></span>

    Example:

    1. select ST_Crosses(
    2.   ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
    3.   ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'));
    5. +-------+
    6. | _col0 |
    7. +-------+
    8. |     0 |
    9. +-------+
    11. select ST_Crosses(
    12.   ST_GeometryFromText('LINESTRING(0 0, 2 2)'),
    13.   ST_GeometryFromText('LINESTRING(0 2, 2 0)'));
    15. +-------+
    16. | _col0 |
    17. +-------+
    18. |     1 |
    19. +-------+
    • ST_Disjoint
    1. ST_Disjoint(GEOMETRY, GEOMETRY) -> BOOLEAN

    <span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)">Determines whether the intersection of two geometries is an empty set. </span></span>

    Example:

    1. select ST_Disjoint(
    2.   ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
    3.   ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'));
    5. +-------+
    6. | _col0 |
    7. +-------+
    8. |     0 |
    9. +-------+
    11. select ST_Disjoint(
    12.   ST_GeometryFromText('LINESTRING(0 0, 2 2)'),
    13.   ST_GeometryFromText('LINESTRING(0 2, 2 4)'));
    15. +-------+
    16. | _col0 |
    17. +-------+
    18. |     1 |
    19. +-------+
    • ST_Equals
    1. ST_Equals(GEOMETRY, GEOMETRY) -> BOOLEAN

    Determines <span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)">whether two geometries are the same. </span></span>

    Example:

    1. select ST_Equals(
    2.   ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
    3.   ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'));
    5. +-------+
    6. | _col0 |
    7. +-------+
    8. |     1 |
    9. +-------+
    11. select ST_Equals(
    12.   ST_GeometryFromText('LINESTRING(0 0, 2 2)'),
    13.   ST_GeometryFromText('LINESTRING(0 2, 2 4)'));
    15. +-------+
    16. | _col0 |
    17. +-------+
    18. |     0 |
    19. +-------+
    • ST_Intersects
    1. ST_Intersects(GEOMETRY, GEOMETRY) -> BOOLEAN

    Determines <span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)">whether the intersection of two geometries does not generate an empty set. </span></span>

    Example:

    1. select ST_Intersects(
    2.   ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
    3.   ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'));
    5. +-------+
    6. | _col0 |
    7. +-------+
    8. |     1 |
    9. +-------+
    11. select ST_Intersects(
    12.   ST_GeometryFromText('LINESTRING(0 0, 2 2)'),
    13.   ST_GeometryFromText('LINESTRING(0 2, 2 4)'));
    15. +-------+
    16. | _col0 |
    17. +-------+
    18. |     0 |
    19. +-------+
    • ST_Overlaps
    1. ST_Overlaps(GEOMETRY, GEOMETRY) -> BOOLEAN

    Determines <span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)">whether the two input geometries “spatially overlap.” This means that they intersect, but one does not completely contain another </span></span>

    Example:

    1. select ST_Overlaps(
    2.   ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
    3.   ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'));
    5. +-------+
    6. | _col0 |
    7. +-------+
    8. |     0 |
    9. +-------+
    11. select ST_Overlaps(
    12.   ST_GeometryFromText('LINESTRING(1 1, 2 2)'),
    13.   ST_GeometryFromText('LINESTRING(0 0, 1.5 1.5)'));
    15. +-------+
    16. | _col0 |
    17. +-------+
    18. |     1 |
    19. +-------+
    • ST_Relate
    1. ST_Relate(GEOMETRY, GEOMETRY, VARCHAR) -> BOOLEAN

    <span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)">Determines whether the two input geometries meet the conditions specified by the “DE-9IM” (</span></span>https://en.wikipedia.org/wiki/DE-9IM<span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)">)pattern matrix string. </span></span>

    Example:

    1. select ST_Relate(
    2.   ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
    3.   ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
    4.   '102101FF2');
    6. +-------+
    7. | _col0 |
    8. +-------+
    9. |     0 |
    10. +-------+
    12. select ST_Relate(
    13.   ST_GeometryFromText('LINESTRING(1 1, 2 2)'),
    14.   ST_GeometryFromText('LINESTRING(0 0, 1.5 1.5)'),
    15.   '1*1***1**');
    17. +-------+
    18. | _col0 |
    19. +-------+
    20. |     1 |
    21. +-------+
    • ST_Touches
    1. ST_Touches(GEOMETRY, GEOMETRY) -> BOOLEAN

    Determines <span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)">whether the only points in common between two geometries intersect the interior of the two geometries. </span></span>

    Example:

    1. select ST_Touches(
    2.   ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
    3.   ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'));
    5. +-------+
    6. | _col0 |
    7. +-------+
    8. |     0 |
    9. +-------+
    11. select ST_Touches(
    12.   ST_GeometryFromText('LINESTRING(1 1, 2 2)'),
    13.   ST_GeometryFromText('LINESTRING(0 0, 1 1)'));
    15. +-------+
    16. | _col0 |
    17. +-------+
    18. |     1 |
    19. +-------+
    • ST_Within
    1. ST_Within(GEOMETRY, GEOMETRY) -> BOOLEAN

    Determines <span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)">whether geometry A is completely inside geometry B. </span></span>

    Example:

    1. select ST_Within(
    2.   ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
    3.   ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'));
    5. +-------+
    6. | _col0 |
    7. +-------+
    8. |     1 |
    9. +-------+
    11. select ST_Within(
    12.   ST_GeometryFromText('LINESTRING(1 1, 2 2)'),
    13.   ST_GeometryFromText('LINESTRING(0 0, 1 1)'));
    15. +-------+
    16. | _col0 |
    17. +-------+
    18. |     0 |
    19. +-------+
    • ST_asBinary
    1. ST_asBinary(GEOMETRY) -> VARBINARY

    Returns <span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)">the WKB representation of the geometry. </span></span>

    • ST_GeometryFromWKBHexString
    1. ST_GeometryFromWKBHexString(VARCHAR) -> GEOMETRY

    Returns the geometry that corresponds to the entered WKB HexString.

    Example:

    1. select ST_ASTEXT(ST_GeometryFromWKBHexString('000000000140000000000000004010000000000000'));
    3. +-------------+
    4. | _col0       |
    5. +-------------+
    6. | POINT (2 4) |
    7. +-------------+
    • ST_pointFromWKBHexString
    1. ST_pointFromWKBHexString(VARCHAR) -> GEOMETRY

    Returns the point geometry that corresponds to the entered point-specific WKB HexString.

    Example:

    1. select ST_ASTEXT(ST_pointFromWKBHexString('000000000140000000000000004010000000000000'));
    3. +-------------+
    4. | _col0       |
    5. +-------------+
    6. | POINT (2 4) |
    7. +-------------+
    • ST_lineFromWKBHexString
    1. ST_lineFromWKBHexString(VARCHAR) -> GEOMETRY

    Returns the line geometry that corresponds to the entered line-specific WKB HexString.

    • ST_polyFromWKBHexString
    1. ST_polyFromWKBHexString(VARCHAR) -> GEOMETRY

    Returns the polygon geometry that corresponds to the entered polygon-specific WKB HexString.

    • ST_MPointFromWKBHexString
    1. ST_MPointFromWKBHexString(VARCHAR) -> GEOMETRY

    Returns the multipoint geometry that corresponds to the entered multipoint-specific WKB HexString.

    Example:

    1. SELECT ST_asText(ST_MPointFromWKBHexString('0104000000020000000101000000000000000000000000000000000000000101000000000000000000F03F0000000000000040'));
    3. +---------------------------+
    4. | _col0                     |
    5. +---------------------------+
    6. | MULTIPOINT ((0 0), (1 2)) |
    7. +---------------------------+
    • ST_MLineFromWKBHexString
    1. ST_MLineFromWKBHexString(VARCHAR) -> GEOMETRY

    Returns the multiline geometry that corresponds to the entered multiline-specific WKB HexString.

    • ST_MPolyFromWKBHexString
    1. ST_MPolyFromWKBHexString(VARCHAR) -> GEOMETRY

    Returns the multipolygon geometry that corresponds to the entered multipolygon-specific WKB HexString.

    • ST_GeometryFromWKB
    1. ST_GeometryFromWKB(VARBINARY) -> GEOMETRY

    Returns the geometry that corresponds to the entered WKB data.

    Example:

    1. select ST_ASTEXT(ST_GeometryFromWKB(from_hex('000000000140000000000000004010000000000000')));
    3. +-------------+
    4. | _col0       |
    5. +-------------+
    6. | POINT (2 4) |
    7. +-------------+
    • ST_pointFromWKB
    1. ST_pointFromWKB(VARBINARY) -> GEOMETRY

    Returns the point geometry that corresponds to the entered point-specific WKB data.

    Example:

    1. select ST_ASTEXT(ST_pointFromWKB(from_hex('000000000140000000000000004010000000000000')));
    3. +-------------+
    4. | _col0       |
    5. +-------------+
    6. | POINT (2 4) |
    7. +-------------+
    • ST_lineFromWKB
    1. ST_lineFromWKB(VARBINARY) -> GEOMETRY

    Returns the line geometry that corresponds to the entered line-specific WKB data.

    • ST_polyFromWKB
    1. ST_polyFromWKB(VARBINARY) -> GEOMETRY

    Returns the polygon geometry that corresponds to the entered polygon-specific WKB data.

    • ST_MPointFromWKB
    1. ST_MPointFromWKB(VARBINARY) -> GEOMETRY

    Returns the multipoint geometry that corresponds to the entered multipoint-specific WKB data.

    Example:

    1. SELECT ST_asText(ST_MPointFromWKB(from_hex('0104000000020000000101000000000000000000000000000000000000000101000000000000000000F03F0000000000000040')));
    3. +---------------------------+
    4. | _col0                     |
    5. +---------------------------+
    6. | MULTIPOINT ((0 0), (1 2)) |
    7. +---------------------------+
    • ST_MLineFromWKB
    1. ST_MLineFromWKB(VARBINARY) -> GEOMETRY

    Returns the multiline geometry that corresponds to the entered multiline-specific WKB data.

    • ST_MPolyFromWKB
    1. ST_MPolyFromWKB(VARBINARY) -> GEOMETRY

    Returns the multipolygon geometry that corresponds to the entered multipolygon-specific WKB data.

    • ST_GeometryFromGeoJson
    1. ST_GeometryFromGeoJson(VARCHAR) -> GEOMETRY

    Returns the geometry that corresponds to the entered GeoJSON string.

    • ST_GeometryFromJson
    1. ST_GeometryFromJson(VARCHAR) -> GEOMETRY

    Returns the geometry that corresponds to the entered ESRI Geometry Object JSON string.

    • ST_asGeoJson
    1. ST_asGeoJson(GEOMETRY) -> VARCHAR

    Converts a geometry into a GeoJSON string.

    Example:

    1. SELECT ST_asGeoJson(ST_GeometryFromText('MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))'));
    3. +-----------------------------------------------------------------------------------------------+
    4. | _col0                                                                                         |
    5. +-----------------------------------------------------------------------------------------------+
    6. | {"type":"MultiLineString","coordinates":[[[0,0],[1,1],[1,2]],[[2,3],[3,2],[5,4]]],"crs":null} |
    7. +-----------------------------------------------------------------------------------------------+
    • ST_asJson
    1. ST_asJson(GEOMETRY) -> VARCHAR

    Converts a geometry into an ESRI Geometry Object JSON string.

    Example:

    1. SELECT ST_asJson(ST_GeometryFromText('MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))'));
    3. +-----------------------------------------------------+
    4. | _col0                                               |
    5. +-----------------------------------------------------+
    6. | {"paths":[[[0,0],[1,1],[1,2]],[[2,3],[3,2],[5,4]]]} |
    7. +-----------------------------------------------------+
    • ST_GeometryFromEsriShape
    1. ST_GeometryFromEsriShape(VARBINARY) -> GEOMETRY

    Returns the geometry that corresponds to the entered ESRI Shape binary data.

    Example:

    1. SELECT california_counties.name,
    2.         COUNT(*) cnt
    3. FROM california_counties
    4. CROSS JOIN earthquakes
    5. WHERE ST_CONTAINS (ST_GeometryFromesrishape(california_counties.boundaryshape), ST_POINT(earthquakes.longitude, earthquakes.latitude))
    6. GROUP BY  california_counties.name
    7. ORDER BY  cnt DESC, california_counties.name;
    9. +-----------------+------+
    10. | name            | cnt  |
    11. +-----------------+------+
    12. | San Benito      |    8 |
    13. | San Bernardino  |    7 |
    14. | Riverside       |    6 |
    15. | Inyo            |    5 |
    16. | Imperial        |    3 |
    17. | San Diego       |    2 |
    18. | Kern            |    1 |
    19. | Kings           |    1 |
    20. | Monterey        |    1 |
    21. | San Luis Obispo |    1 |
    22. | Santa Clara     |    1 |
    23. | Ventura         |    1 |
    24. +-----------------+------+
    Free Trial Free Trial