All Products
Search
Document Center

Geospatial functions

Last Updated: Aug 04, 2021

CITY_ADCODE

city_adcode(longitude, latitude)
  • Description: returns the administrative division code (ADCode) of a city based on its longitude and latitude.

    If the value of longitude or latitude is invalid or null, an empty string is returned.

  • Data type of the return value: STRING.

  • Example:

            select city_adcode(118.0, 28.0);
            +----------------------------+
            |           361100           |
    

PROVINCE_ADCODE

province_adcode(longitude, latitude)
  • Description: returns the ADCode of a province based on its longitude and latitude.

    If the value of longitude or latitude is invalid or null, an empty string is returned.

  • Data type of the return value: STRING.

  • Example:

            select province_adcode(118.0, 28.0);
            +---------------------------------+
            |             360000              |
    

Overview

To allow some customers to analyze geospatial data in the cloud, Alibaba Cloud Data Lake Analytics (DLA) provides functions to process geospatial data in various formats in compliance with the Open Geospatial Consortium (OGC) OpenGIS standards. The following common data formats are supported:

  • WKT

  • WKB

  • GeoJson

  • ESRI Geometry Object Json

  • ESRI Shape

DLA uses the European Petroleum Survey Group (EPSG) 4326 coordinate reference system. This system is a geographic coordinate system that uses longitudes and latitudes to indicate locations. GPS also uses this coordinate reference system.

WKT data

For more information, see WKT.

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))

WKB data

The following table describes the mappings 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

GeoJSON data

For more information, see GeoJSON.

ESRI Geometry Object JSON data

For more information, see Geometry Objects.

ESRI Shapefile binary data

For more information, see ESRI Shapefile Technical Description.

Functions

Name

Description

ST_asText

Converts a geometry object into a WKT string.

ST_LineFromText

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

ST_Point

Generates a point object from coordinate values (x, y).

ST_Polygon

Generates a polygon object from a polygon-specific WKT string.

ST_Area

Returns the area of a polygon or multipolygon. The return value for points and lines is 0.0. The return value for GeometryCollection is the sum of all areas in the collection.

ST_GeometryFromText

Generates a geometry object from a WKT string.

ST_Buffer

Returns a geometry object that represents all points whose distance from the entered geometry is less than or equal to a specified distance.

ST_Centroid

Returns the centroid of a geometry object.

ST_CoordDim

Returns the dimensions of coordinate values for a geometry object.

ST_Dimension

Returns the dimensions of a geometry object. In this case, the dimensions refer to the length and width. For example, if a point has no length or width, its dimension is 0. If a line has only length but no width, its dimension is 1.

ST_IsClosed

Determines whether a line or multiline is closed.

ST_IsEmpty

Determines whether a geometry object is empty.

ST_Length

Calculates the length of a line or multiline.

ST_XMax

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

ST_XMin

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

ST_YMax

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

ST_YMin

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

ST_NumInteriorRing

Returns the number of interior rings in a polygon.

ST_NumPoints

Returns the number of points (inflection points) in a geometry object.

ST_IsRing

Determines whether a line object 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 a geometry object for the closure of the combinatorial boundary of the entered geometry.

ST_Envelope

Returns a polygon object for the minimum bounding box.

ST_Difference

Returns a geometry object that represents the non-intersecting part of two geometry objects.

ST_Distance

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

ST_ExteriorRing

Returns a line object that represents the exterior ring of a polygon.

ST_Intersection

Returns a 2D geometry object that represents the intersection of two geometry objects.

ST_SymDifference

Returns a geometry object that represents the symmetric difference in a point set between two geometry objects.

ST_Contains

Determines whether a geometry object is entirely inside another geometry object.

ST_Crosses

Returns 1 if the dimension of the geometry object that represents the intersection of two geometry objects is smaller than the maximum dimension of the two source geometry objects. In addition, the intersection spatially crosses. The geometry objects have some, but not all interior points in common. The intersection must not equal either of the source geometry objects. Otherwise, 0 is returned.

ST_Disjoint

Determines whether the intersection of two geometry objects is an empty set.

ST_Equals

Determines whether two geometry objects are the same.

ST_Intersects

Determines whether the intersection of two geometry objects does not generate an empty set.

ST_Overlaps

Determines whether two geometry objects spatially overlap. This means that they intersect, but one does not completely contain another.

ST_Relate

Determines whether two geometry objects meet the conditions specified by the DE-9IM matrix string.

ST_Touches

Determines whether the only points in common between two geometry objects intersect the interior of the two geometry objects.

ST_Within

Determines whether a geometry object is completely inside another geometry object.

ST_asBinary

Returns the WKB representation of a geometry object.

ST_GeometryFromWKBHexString

Returns a geometry object that corresponds to the entered WKB HEX string.

ST_pointFromWKBHexString

Returns a geometry point object that corresponds to the entered point-specific WKB HEX string.

ST_lineFromWKBHexString

Returns a line object that corresponds to the entered line-specific WKB HEX string.

ST_polyFromWKBHexString

Returns a polygon object that corresponds to the entered polygon-specific WKB HEX string.

ST_MPointFromWKBHexString

Returns a multipoint object that corresponds to the entered multipoint-specific WKB HEX string.

ST_MLineFromWKBHexString

Returns a multiline object that corresponds to the entered multiline-specific WKB HEX string.

ST_MPolyFromWKBHexString

Returns a multipolygon object that corresponds to the entered multipolygon-specific WKB HEX string.

ST_GeometryFromWKB

Returns a geometry object that corresponds to the entered WKB data.

ST_pointFromWKB

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

ST_lineFromWKB

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

ST_polyFromWKB

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

ST_MPointFromWKB

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

ST_MLineFromWKB

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

ST_MPolyFromWKB

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

ST_GeometryFromGeoJson

Returns a geometry object that corresponds to the entered GeoJSON string.

ST_GeometryFromJson

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

ST_asGeoJson

Converts a geometry object into a GeoJSON string.

ST_asJson

Converts a geometry object into an ESRI Geometry Object JSON string.

ST_GeometryFromEsriShape

Returns a geometry object that corresponds to the entered ESRI Shapefile binary data.

Function definitions and examples

ST_asText

ST_asText(GEOMETRY)
  • Description: converts a geometry object into a WKT string.

  • Data type of the return value: VARCHAR.

  • Example:

            select ST_asText(ST_LineFromText('LINESTRING (0 0, 1 1, 1 2)'));
    
            +----------------------------+
            | _col0                      |
            +----------------------------+
            | LINESTRING (0 0, 1 1, 1 2) |
            +----------------------------+

ST_LineFromText

ST_LineFromText(VARCHAR)
  • Description: generates a line object from a line-specific WKT string.

  • Data type of the return value: GEOMETRY.

  • Example:

            select ST_asText(ST_LineFromText('LINESTRING (0 0, 1 1, 1 2)'));
    
            +----------------------------+
            | _col0                      |
            +----------------------------+
            | LINESTRING (0 0, 1 1, 1 2) |
            +----------------------------+

ST_Point

ST_Point(DOUBLE, DOUBLE)
  • Description: generates a point object from coordinate values (x, y).

  • Data type of the return value: GEOMETRY.

  • Example:

            select ST_asText(ST_Point(30.2741500000,120.1551500000));
    
            +----------------------------+
            | _col0                      |
            +----------------------------+
            | POINT (30.27415 120.15515) |
            +----------------------------+

ST_Polygon

ST_Polygon(VARCHAR)
  • Description: generates a polygon object from a polygon-specific WKT string.

  • Data type of the return value: GEOMETRY.

  • Example:

            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))'));
    
            +----------------------------------------------------------------+
            | _col0                                                          |
            +----------------------------------------------------------------+
            | POLYGON ((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 1 2, 2 2, 2 1, 1 1)) |
            +----------------------------------------------------------------+

ST_Area

ST_Area(Geometry)
  • Description: returns the area of a polygon or multipolygon. The return value for points and lines is 0.0. The return value for GeometryCollection is the sum of all areas in the collection.

  • Data type of the return value: DOUBLE.

  • Example:

            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))'));
    
            +-------+
            | _col0 |
            +-------+
            |  15.0 |
            +-------+
    
            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)))'));
    
            +-------+
            | _col0 |
            +-------+
            |  16.0 |
            +-------+
    

ST_GeometryFromText

ST_GeometryFromText(VARCHAR)
  • Description: generates a geometry object from a WKT string.

  • Data type of the return value: GEOMETRY.

ST_Buffer

ST_Buffer(GEOMETRY, DOUBLE)
  • Description: returns a geometry object that represents all points whose distance from the entered geometry is less than or equal to a specified distance.

  • Data type of the return value: GEOMETRY.

ST_Centroid

ST_Centroid(GEOMETRY)
  • Description: returns the centroid of a geometry object.

  • Data type of the return value: GEOMETRY.

  • Example:

            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)))')));
    
            +-----------------------+
            | _col0                 |
            +-----------------------+
            | POINT (1.8125 1.8125) |
            +-----------------------+
    
            select ST_asText(ST_Centroid(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2)')));
    
            +---------------------------------------------+
            |            _col0                            |
            +---------------------------------------------+
            | POINT (0.7071067811865476 0.914213562373095)|
            +---------------------------------------------+
    
            select ST_asText(ST_Centroid(ST_GeometryFromText('POINT (0.5 1)')));
    
            +---------------+
            | _col0         |
            +---------------+
            | POINT (0.5 1) |
            +---------------+
    
            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))')));
    
            +---------------------------------------------+
            | _col0                                       |
            +---------------------------------------------+
            | POINT (2.033333333333333 2.033333333333333) |
            +---------------------------------------------+
    

ST_CoordDim

ST_CoordDim(GEOMETRY)
  • Description: returns the dimensions of coordinate values for a geometry object.

  • Data type of the return value: BIGINT.

  • Example:

            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))'));
    
            +-------+
            | _col0 |
            +-------+
            |     2 |
            +-------+
    
            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)))'));
    
            +-------+
            | _col0 |
            +-------+
            |     2 |
            +-------+
    

ST_Dimension

ST_Dimension(GEOMETRY)
  • Description: returns the dimensions of a geometry object. In this case, the dimensions refer to the length and width. For example, if a point has no length or width, its dimension is 0. If a line has only length but no width, its dimension is 1.

  • Data type of the return value: BIGINT.

  • Example:

            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)))'));
    
            +-------+
            | _col0 |
            +-------+
            |     2 |
            +-------+
    

ST_IsClosed

ST_IsClosed(GEOMETRY)
  • Description: determines whether a line or multiline is closed.

  • Data type of the return value: BOOLEAN.

  • Example:

            select ST_IsClosed(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2)'));
    
            +-------+
            | _col0 |
            +-------+
            |     0 |
            +-------+
    
            select ST_IsClosed(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2, 0 0)'));
    
            +-------+
            | _col0 |
            +-------+
            |     1 |
            +-------+
    
            select ST_IsClosed(ST_GeometryFromText('MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))'));
    
            +-------+
            | _col0 |
            +-------+
            |     0 |
            +-------+
    

ST_IsEmpty

ST_IsEmpty(GEOMETRY)
  • Description: determines whether a geometry object is empty.

  • Data type of the return value: BOOLEAN.

  • Example:

            select ST_IsEmpty(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2, 0 0)'));
    
            +-------+
            | _col0 |
            +-------+
            |     0 |
            +-------+
    
            select ST_IsEmpty(null);
    
            +-------+
            | _col0 |
            +-------+
            |  NULL |
            +-------+
    
            SELECT ST_IsEmpty(ST_GeometryFromText('GEOMETRYCOLLECTION EMPTY'));
    
            +-------+
            | _col0 |
            +-------+
            |     1 |
            +-------+
    
            SELECT ST_IsEmpty(ST_GeometryFromText('POLYGON EMPTY'));
    
            +-------+
            | _col0 |
            +-------+
            |     1 |
            +-------+
    

ST_Length

ST_Length(GEOMETRY)
  • Description: calculates the length of a line or multiline.

  • Data type of the return value: DOUBLE.

  • Example:

            SELECT ST_Length(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2, 0 0)'));
    
            +-------------------+
            | _col0             |
            +-------------------+
            | 4.650281539872885 |
            +-------------------+
    
            SELECT ST_Length(ST_GeometryFromText('MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))'));
    
            +-------------------+
            | _col0             |
            +-------------------+
            | 6.656854249492381 |
            +-------------------+
    

ST_XMax

ST_XMax(GEOMETRY)
  • Description: returns the maximum x-coordinate value of a geometry object in the coordinate reference system.

  • Data type of the return value: DOUBLE.

  • Example:

            SELECT ST_XMax(ST_GeometryFromText('MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))'));
    
            +-------+
            | _col0 |
            +-------+
            |   5.0 |
            +-------+
    
            SELECT ST_XMax(ST_GeometryFromText('POINT (0.5 1)'));
    
            +-------+
            | _col0 |
            +-------+
            |   0.5 |
            +-------+
    

ST_XMin

ST_XMin(GEOMETRY)
  • Description: returns the minimum x-coordinate value of a geometry object in the coordinate reference system.

  • Data type of the return value: DOUBLE.

  • Example:

            SELECT ST_XMin(ST_GeometryFromText('MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))'));
    
            +-------+
            | _col0 |
            +-------+
            |   0.0 |
            +-------+
    
            SELECT ST_XMin(ST_GeometryFromText('POINT (0.5 1)'));
    
            +-------+
            | _col0 |
            +-------+
            |   0.5 |
            +-------+
    

ST_YMax

ST_YMax(GEOMETRY)
  • Description: returns the maximum y-coordinate value of a geometry object in the coordinate reference system.

  • Data type of the return value: DOUBLE.

  • Example:

            SELECT ST_YMax(ST_GeometryFromText('MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))'));
    
            +-------+
            | _col0 |
            +-------+
            |   4.0 |
            +-------+
    
            SELECT ST_YMax(ST_GeometryFromText('POINT (0.5 1)'));
    
            +-------+
            | _col0 |
            +-------+
            |   1.0 |
            +-------+
    

ST_YMin

ST_YMin(GEOMETRY)
  • Description: returns the minimum y-coordinate value of a geometry object in the coordinate reference system.

  • Data type of the return value: DOUBLE.

  • Example:

            SELECT ST_YMin(ST_GeometryFromText('MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))'));
    
            +-------+
            | _col0 |
            +-------+
            |   0.0 |
            +-------+
    
            SELECT ST_YMin(ST_GeometryFromText('POINT (0.5 1)'));
    
            +-------+
            | _col0 |
            +-------+
            |   1.0 |
            +-------+
    

ST_NumInteriorRing

ST_NumInteriorRing(GEOMETRY)
  • Description: returns the number of interior rings in a polygon.

  • Data type of the return value: BIGINT.

  • Example:

            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))'));
    
            +-------+
            | _col0 |
            +-------+
            |     1 |
            +-------+
    

ST_NumPoints

ST_NumPoints(GEOMETRY)
  • Description: returns the number of points (inflection points) in a geometry object.

  • Data type of the return value: BIGINT.

  • Example:

            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))'));
    
            +-------+
            | _col0 |
            +-------+
            |     8 |
            +-------+
    
            SELECT ST_NumPoints(ST_GeometryFromText('POLYGON EMPTY'));
    
            +-------+
            | _col0 |
            +-------+
            |     0 |
            +-------+
    
            SELECT ST_NumPoints(ST_GeometryFromText('POINT (0.5 1)'));
    
            +-------+
            | _col0 |
            +-------+
            |     1 |
            +-------+
    

ST_IsRing

ST_IsRing(GEOMETRY)
  • Description: determines whether a geometry line object is a ring, for example, whether it is closed.

  • Data type of the return value: BOOLEAN.

  • Example:

            select ST_IsRing(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2)'));
    
            +-------+
            | _col0 |
            +-------+
            |     0 |
            +-------+
    
            select ST_IsRing(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2, 0 0)'));
    
            +-------+
            | _col0 |
            +-------+
            |     1 |
            +-------+
    

ST_StartPoint

ST_StartPoint(GEOMETRY)
  • Description: returns the first point of a line.

  • Data type of the return value: GEOMETRY.

  • Example:

            select ST_asText(ST_StartPoint(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2, 0 0)')));
    
            +-------------+
            | _col0       |
            +-------------+
            | POINT (0 0) |
            +-------------+
    

ST_EndPoint

ST_EndPoint(GEOMETRY)
  • Description: returns the last point of a line.

  • Data type of the return value: GEOMETRY.

  • Example:

            select ST_asText(ST_EndPoint(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2)')));
    
            +-------------+
            | _col0       |
            +-------------+
            | POINT (1 2) |
            +-------------+
    

ST_X

ST_X(GEOMETRY)
  • Description: returns the x-coordinate value of a point.

  • Data type of the return value: DOUBLE.

  • Example:

            select ST_X(ST_GeometryFromText('POINT (0.5 1)'));
    
            +-------+
            | _col0 |
            +-------+
            |   0.5 |
            +-------+
    

ST_Y

ST_Y(GEOMETRY)
  • Description: returns the y-coordinate value of a point.

  • Data type of the return value: DOUBLE.

  • Example:

            select ST_Y(ST_GeometryFromText('POINT (0.5 1)'));
    
            +-------+
            | _col0 |
            +-------+
            |   1.0 |
            +-------+
    

ST_Boundary

ST_Boundary(GEOMETRY)
  • Description: returns a geometry object for the closure of the combinatorial boundary of the entered geometry.

  • Data type of the return value: GEOMETRY.

  • Example:

            select ST_asText(ST_Boundary(ST_GeometryFromText('POINT (0.5 1)')));
    
            +-------------------+
            |       _col0       |
            +-------------------+
            | MULTIPOINT  EMPTY |
            +-------------------+
    
            select ST_asText(ST_Boundary(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2)')));
    
            +---------------------------+
            | _col0                     |
            +---------------------------+
            | MULTIPOINT ((0 0), (1 2)) |
            +---------------------------+
    
            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)))')));
    
            +-------------------------------------------------------------------------------------------------------------+
            | _col0                                                                                                       |
            +-------------------------------------------------------------------------------------------------------------+
            | 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)) |
            +-------------------------------------------------------------------------------------------------------------+
    

ST_Envelope

ST_Envelope(GEOMETRY)
  • Description: returns a polygon object for the minimum bounding box.

  • Data type of the return value: GEOMETRY.

  • Example:

            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)))')));
    
            +-------------------------------------------+
            | _col0                                     |
            +-------------------------------------------+
            | POLYGON ((-2 -2, 4 -2, 4 4, -2 4, -2 -2)) |
            +-------------------------------------------+
    

ST_Difference

ST_Difference(GEOMETRY, GEOMETRY)
  • Description: returns a geometry object that represents the non-intersecting part of two geometry objects.

  • Data type of the return value: GEOMETRY.

  • Example:

            select ST_asText(ST_Difference(
    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)))'),
    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))')));
    
            +--------------------------------------------------------------------------------------------------------------+
            | _col0                                                                                                        |
            +--------------------------------------------------------------------------------------------------------------+
            | 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))) |
            +--------------------------------------------------------------------------------------------------------------+
    
            select ST_asText(ST_Difference(
    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)))'),
    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)))')));
    
            +--------------------+
            | _col0              |
            +--------------------+
            | MULTIPOLYGON EMPTY |
            +--------------------+
    

ST_Distance

ST_Distance(GEOMETRY, GEOMETRY)
  • Description: returns the distance between two geometry objects. This distance is between the closest inflection points of the two geometry objects.

  • Data type of the return value: DOUBLE.

  • Example:

            select ST_Distance(
    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)))'),
    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)))'));
    
            +-------+
            | _col0 |
            +-------+
            |   0.0 |
            +-------+
    
            select ST_Distance(
    ST_GeometryFromText('POINT(0 0)'),
    ST_GeometryFromText('POINT(1 1)'));
    
            +--------------------+
            | _col0              |
            +--------------------+
            | 1.4142135623730951 |
            +--------------------+
    

ST_ExteriorRing

ST_ExteriorRing(GEOMETRY)
  • Description: returns a line object that represents the exterior ring of a polygon.

  • Data type of the return value: GEOMETRY.

  • Example:

            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))')));
    
            +--------------------------------------+
            | _col0                                |
            +--------------------------------------+
            | LINESTRING (0 0, 4 0, 4 4, 0 4, 0 0) |
            +--------------------------------------+
    

ST_Intersection

ST_Intersection(GEOMETRY, GEOMETRY)
  • Description: returns a 2D geometry object that represents the intersection of two geometry objects.

  • Data type of the return value: GEOMETRY.

  • Example:

            select ST_asText(ST_Intersection(
    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)))'),
    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)))')));
    
            +--------------------------------------------------------------------------------------------------------------+
            | _col0                                                                                                        |
            +--------------------------------------------------------------------------------------------------------------+
            | 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))) |
            +--------------------------------------------------------------------------------------------------------------+
    
            select ST_asText(ST_Intersection(
    ST_GeometryFromText('POINT(0 0)'),
    ST_GeometryFromText('POINT(1 1)')));
    
            +--------------------+
            | _col0              |
            +--------------------+
            | MULTIPOLYGON EMPTY |
            +--------------------+
    

ST_SymDifference

ST_SymDifference(GEOMETRY, GEOMETRY)
  • Description: returns a geometry object that represents the symmetric difference in a point set between two geometry objects.

  • Data type of the return value: GEOMETRY.

  • Example:

            select ST_asText(ST_SymDifference(
    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)))'),
    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)))')));
    
            +--------------------+
            | _col0              |
            +--------------------+
            | MULTIPOLYGON EMPTY |
            +--------------------+
    
            select ST_asText(ST_SymDifference(
    ST_GeometryFromText('POINT(0 0)'),
    ST_GeometryFromText('POINT(1 1)')));
    
            +---------------------------+
            | _col0                     |
            +---------------------------+
            | MULTIPOINT ((0 0), (1 1)) |
            +---------------------------+
    

    The shaded part of the following figure shows the result of symmetric differences. The 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.

    image.png | left | 223x166

ST_Contains

ST_Contains(GEOMETRY, GEOMETRY)
  • Description: determines whether a geometry object is entirely inside another geometry object.

  • Data type of the return value: BOOLEAN.

  • Example:

            select ST_Contains(
    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)))'),
    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)))'));
    
            +-------+
            | _col0 |
            +-------+
            |     1 |
            +-------+
    
            select ST_Contains(
    ST_GeometryFromText('POINT(0 0)'),
    ST_GeometryFromText('POINT(1 1)'));
    
            +-------+
            | _col0 |
            +-------+
            |     0 |
            +-------+
    

ST_Crosses

ST_Crosses(GEOMETRY, GEOMETRY)
  • Description: returns 1 if the dimension of the geometry object that represents the intersection of two geometry objects is smaller than the maximum dimension of the two source geometry objects. In addition, the intersection spatially crosses. The geometry objects have some, but not all interior points in common. The intersection must not equal either of the source geometry objects. Otherwise, 0 is returned.

  • Data type of the return value: BOOLEAN.

  • Example:

            select ST_Crosses(
    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)))'),
    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)))'));
    
            +-------+
            | _col0 |
            +-------+
            |     0 |
            +-------+
    
            select ST_Crosses(
    ST_GeometryFromText('LINESTRING(0 0, 2 2)'),
    ST_GeometryFromText('LINESTRING(0 2, 2 0)'));
    
            +-------+
            | _col0 |
            +-------+
            |     1 |
            +-------+
    

ST_Disjoint

ST_Disjoint(GEOMETRY, GEOMETRY)
  • Description: determines whether the intersection of two geometry objects is an empty set.

  • Data type of the return value: BOOLEAN.

  • Example:

            select ST_Disjoint(
    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)))'),
    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)))'));
    
            +-------+
            | _col0 |
            +-------+
            |     0 |
            +-------+
    
            select ST_Disjoint(
    ST_GeometryFromText('LINESTRING(0 0, 2 2)'),
    ST_GeometryFromText('LINESTRING(0 2, 2 4)'));
    
            +-------+
            | _col0 |
            +-------+
            |     1 |
            +-------+
    

ST_Equals

ST_Equals(GEOMETRY, GEOMETRY)
  • Description: determines whether two geometry objects are the same.

  • Data type of the return value: BOOLEAN.

  • Example:

            select ST_Equals(
    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)))'),
    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)))'));
    
            +-------+
            | _col0 |
            +-------+
            |     1 |
            +-------+
    
            select ST_Equals(
    ST_GeometryFromText('LINESTRING(0 0, 2 2)'),
    ST_GeometryFromText('LINESTRING(0 2, 2 4)'));
    
            +-------+
            | _col0 |
            +-------+
            |     0 |
            +-------+
    

ST_Intersects

ST_Intersects(GEOMETRY, GEOMETRY)
  • Description: determines whether the intersection of two geometry objects does not generate an empty set.

  • Data type of the return value: BOOLEAN.

  • Example:

            select ST_Intersects(
    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)))'),
    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)))'));
    
            +-------+
            | _col0 |
            +-------+
            |     1 |
            +-------+
    
            select ST_Intersects(
    ST_GeometryFromText('LINESTRING(0 0, 2 2)'),
    ST_GeometryFromText('LINESTRING(0 2, 2 4)'));
    
            +-------+
            | _col0 |
            +-------+
            |     0 |
            +-------+
    

ST_Overlaps

ST_Overlaps(GEOMETRY, GEOMETRY)
  • Description: determines whether two geometry objects spatially overlap. This means that they intersect, but one does not completely contain another.

  • Data type of the return value: BOOLEAN.

  • Example:

            select ST_Overlaps(
    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)))'),
    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)))'));
    
            +-------+
            | _col0 |
            +-------+
            |     0 |
            +-------+
    
            select ST_Overlaps(
    ST_GeometryFromText('LINESTRING(1 1, 2 2)'),
    ST_GeometryFromText('LINESTRING(0 0, 1.5 1.5)'));
    
            +-------+
            | _col0 |
            +-------+
            |     1 |
            +-------+
    

ST_Relate

ST_Relate(GEOMETRY, GEOMETRY, VARCHAR)
  • Description: determines whether two geometry objects meet the conditions specified by the DE-9IM matrix string.

  • Data type of the return value: BOOLEAN.

  • Example:

            select ST_Relate(
    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)))'),
    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)))'),
    '102101FF2');
    
            +-------+
            | _col0 |
            +-------+
            |     0 |
            +-------+
    
            select ST_Relate(
    ST_GeometryFromText('LINESTRING(1 1, 2 2)'),
    ST_GeometryFromText('LINESTRING(0 0, 1.5 1.5)'),
    '1*1***1**');
    
            +-------+
            | _col0 |
            +-------+
            |     1 |
            +-------+
    

ST_Touches

ST_Touches(GEOMETRY, GEOMETRY)
  • Description: determines whether the only points in common between two geometry objects intersect the interior of the two geometry objects.

  • Data type of the return value: BOOLEAN.

  • Example:

            select ST_Touches(
    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)))'),
    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)))'));
    
            +-------+
            | _col0 |
            +-------+
            |     0 |
            +-------+
    
            select ST_Touches(
    ST_GeometryFromText('LINESTRING(1 1, 2 2)'),
    ST_GeometryFromText('LINESTRING(0 0, 1 1)'));
    
            +-------+
            | _col0 |
            +-------+
            |     1 |
            +-------+
    

ST_Within

ST_Within(GEOMETRY, GEOMETRY)
  • Description: determines whether a geometry object is completely inside another geometry object.

  • Data type of the return value: BOOLEAN.

  • Example:

            select ST_Within(
    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)))'),
    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)))'));
            +-------+
            | _col0 |
            +-------+
            |     1 |
            +-------+
    
            select ST_Within(
    ST_GeometryFromText('LINESTRING(1 1, 2 2)'),
    ST_GeometryFromText('LINESTRING(0 0, 1 1)'));
    
            +-------+
            | _col0 |
            +-------+
            |     0 |
            +-------+
    

ST_asBinary

ST_asBinary(GEOMETRY)
  • Description: returns the WKB representation of a geometry object.

  • Data type of the return value: VARBINARY.

ST_GeometryFromWKBHexString

ST_GeometryFromWKBHexString(VARCHAR)
  • Description: returns the geometry object that corresponds to the entered WKB HEX string.

  • Data type of the return value: GEOMETRY.

  • Example:

            select ST_ASTEXT(ST_GeometryFromWKBHexString('000000000140000000000000004010000000000000'));
    
            +-------------+
            | _col0       |
            +-------------+
            | POINT (2 4) |
            +-------------+
    

ST_pointFromWKBHexString

ST_pointFromWKBHexString(VARCHAR)
  • Description: returns the geometry point object that corresponds to the entered point-specific WKB HEX string.

  • Data type of the return value: GEOMETRY.

  • Example:

            select ST_ASTEXT(ST_pointFromWKBHexString('000000000140000000000000004010000000000000'));
    
            +-------------+
            | _col0       |
            +-------------+
            | POINT (2 4) |
            +-------------+
    

ST_lineFromWKBHexString

ST_lineFromWKBHexString(VARCHAR)
  • Description: returns a line object that corresponds to the entered line-specific WKB HEX string.

  • Data type of the return value: GEOMETRY.

ST_polyFromWKBHexString

ST_polyFromWKBHexString(VARCHAR)
  • Description: returns a polygon object that corresponds to the entered polygon-specific WKB HEX string.

  • Data type of the return value: GEOMETRY.

ST_MPointFromWKBHexString

ST_MPointFromWKBHexString(VARCHAR)
  • Description: returns a multipoint object that corresponds to the entered multipoint-specific WKB HEX string.

  • Data type of the return value: GEOMETRY.

ST_MLineFromWKBHexString

ST_MLineFromWKBHexString(VARCHAR)
  • Description: returns a multiline object that corresponds to the entered multiline-specific WKB HEX string.

  • Data type of the return value: GEOMETRY.

ST_MPolyFromWKBHexString

ST_MPolyFromWKBHexString(VARCHAR)
  • Description: returns a multipolygon object that corresponds to the entered multipolygon-specific WKB HEX string.

  • Data type of the return value: GEOMETRY.

ST_GeometryFromWKB

ST_GeometryFromWKB(VARBINARY)
  • Description: returns a geometry object that corresponds to the entered multipoint-specific WKB data.

  • Data type of the return value: GEOMETRY.

  • Example:

            select ST_ASTEXT(ST_GeometryFromWKB(from_hex('000000000140000000000000004010000000000000')));
    
            +-------------+
            | _col0       |
            +-------------+
            | POINT (2 4) |
            +-------------+
    

ST_pointFromWKB

ST_pointFromWKB(VARBINARY)
  • Description: returns a geometry point object that corresponds to the entered point-specific WKB data.

  • Data type of the return value: GEOMETRY.

  • Example:

            select ST_ASTEXT(ST_pointFromWKB(from_hex('000000000140000000000000004010000000000000')));
    
            +-------------+
            | _col0       |
            +-------------+
            | POINT (2 4) |
            +-------------+
    

ST_lineFromWKB

ST_lineFromWKB(VARBINARY)
  • Description: returns a line object that corresponds to the entered line-specific WKB data.

  • Data type of the return value: GEOMETRY.

ST_polyFromWKB

ST_polyFromWKB(VARBINARY)
  • Description: returns a polygon object that corresponds to the entered polygon-specific WKB data.

  • Data type of the return value: GEOMETRY.

ST_MPointFromWKB

ST_MPointFromWKB(VARBINARY)
  • Description: returns a multipoint object that corresponds to the entered multipoint-specific WKB data.

  • Data type of the return value: GEOMETRY.

ST_MLineFromWKB

ST_MLineFromWKB(VARBINARY)
  • Description: returns a multiline object that corresponds to the entered multiline-specific WKB data.

  • Data type of the return value: GEOMETRY.

ST_MPolyFromWKB

ST_MPolyFromWKB(VARBINARY)
  • Description: returns a multipolygon object that corresponds to the entered multipolygon-specific WKB data.

  • Data type of the return value: GEOMETRY.

ST_GeometryFromGeoJson

ST_GeometryFromGeoJson(VARCHAR)
  • Description: returns a geometry object that corresponds to the entered GeoJSON string.

  • Data type of the return value: GEOMETRY.

ST_GeometryFromJson

ST_GeometryFromJson(VARCHAR)
  • Description: returns a geometry object that corresponds to the entered ESRI Geometry Object JSON string.

  • Data type of the return value: GEOMETRY.

ST_asGeoJson

ST_asGeoJson(GEOMETRY)  VARCHAR
  • Description: converts a geometry object into a GeoJSON string.

  • Data type of the return value: GEOMETRY.

  • Example:

            SELECT ST_asGeoJson(ST_GeometryFromText('MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))'));
    
            +-----------------------------------------------------------------------------------------------+
            | _col0                                                                                         |
            +-----------------------------------------------------------------------------------------------+
            | {"type":"MultiLineString","coordinates":[[[0,0],[1,1],[1,2]],[[2,3],[3,2],[5,4]]],"crs":null} |
            +-----------------------------------------------------------------------------------------------+
    

ST_asJson

ST_asJson(GEOMETRY)
  • Description: converts a geometry object into an ESRI Geometry Object JSON string.

  • Data type of the return value: VARCHAR.

  • Example:

            SELECT ST_asJson(ST_GeometryFromText('MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))'));
    
            +-----------------------------------------------------+
            | _col0                                               |
            +-----------------------------------------------------+
            | {"paths":[[[0,0],[1,1],[1,2]],[[2,3],[3,2],[5,4]]]} |
            +-----------------------------------------------------+
    

ST_GeometryFromEsriShape

ST_GeometryFromEsriShape(VARBINARY)
  • Description: returns a geometry object that corresponds to the entered ESRI Shapefile binary data.

  • Data type of the return value: GEOMETRY.

  • Example:

            SELECT california_counties.name,
                            COUNT(*) cnt
            FROM california_counties
            CROSS JOIN earthquakes
            WHERE ST_CONTAINS (ST_GeometryFromesrishape(california_counties.boundaryshape), ST_POINT(earthquakes.longitude, earthquakes.latitude))
            GROUP BY  california_counties.name
            ORDER BY  cnt DESC, california_counties.name;
    
            +-----------------+------+
            | name            | cnt  |
            +-----------------+------+
            | San Benito      |    8 |
            | San Bernardino  |    7 |
            | Riverside       |    6 |
            | Inyo            |    5 |
            | Imperial        |    3 |
            | San Diego       |    2 |
            | Kern            |    1 |
            | Kings           |    1 |
            | Monterey        |    1 |
            | San Luis Obispo |    1 |
            | Santa Clara     |    1 |
            | Ventura         |    1 |
            +-----------------+------+
    

ST_GeoHash

ST_GeoHash(double longitude, double latitude, long maxCharLength);
  • Description: generates a GeoHash string based on the given latitude and longitude as well as the maximum hash size.

  • Data type of the return value: VARCHAR.

  • Example:

mysql> select st_geohash(108, 20, 10);
+-------------------------+
| st_geohash(108, 20, 10) |
+-------------------------+
| w7scc8ghqq              |
+-------------------------+

ST_LatFromGeoHash

ST_LatFromGeoHash(varchar geoHashString);
  • Description: extracts the latitude value from the given GeoHash string.

  • Data type of the return value: DOUBLE.

  • Example:

mysql> select st_latfromgeohash('w7scc8ghqq');
+---------------------------------+
| st_latfromgeohash('w7scc8ghqq') |
+---------------------------------+
|               20.00000149011612 |
+---------------------------------+

ST_LongFromGeoHash

ST_LongFromGeoHash(varchar geoHashString);
  • Description: extracts the longitude value from the given GeoHash string.

  • Data type of the return value: DOUBLE.

  • Example:

mysql> select st_longfromgeohash('w7scc8ghqq');
+----------------------------------+
| st_longfromgeohash('w7scc8ghqq') |
+----------------------------------+
|                107.9999989271164 |
+----------------------------------+

ST_GeoHash_Neighbors

ST_GeoHash_Neighbors(double longitude, double latitude, long maxCharLength);
  • Description: calculates the eight GeoHash neighbors based on the given latitude and longitude as well as the maximum hash size.

  • Data type of the return value: VARCHAR.

  • Example:

mysql> select st_geohash_neighbors(108, 20, 10);
+--------------------------------------------------------------------------+
|               st_geohash_neighbors(108, 20, 10)                           |
+--------------------------------------------------------------------------+
| [wtw37qv, wtw37qy, wtw37qw, wtw37qq, wtw37qm, wtw37qk, wtw37qs, wtw37qu] |
+--------------------------------------------------------------------------+