All Products
Search
Document Center

Geospatial analysis functions

Last Updated: Jun 08, 2020

CITY_ADCODE

  1. city_adcode(longitude, latitude)
  • Function description: This function processes data based on the longitude. Longitudeand Latitude latitudequeries the city ADCode.

    If longitudeand latitudeif any value is illegal or NULL, an empty string is returned.

  • Type of the returned value: STRING.

  • Example code:

    1. select city_adcode(118.0, 28.0);
    2. +----------------------------+
    3. | 361100 |

PROVINCE_ADCODE

  1. province_adcode(longitude, latitude)
  • Function description: This function processes data based on the longitude. Longitudeand Latitude latitudevalue to query the ADCode of the corresponding province.

    If longitudeand latitudeif any value is illegal or NULL, an empty string is returned.

  • Type of the returned value: STRING.

  • Example code:

    1. select province_adcode(118.0, 28.0);
    2. +---------------------------------+
    3. | 360000 |

Description

To meet the analysis requirements of some customers, Alibaba Cloud Data Lake Analytics (DLA) supports Geospatial Data processing functions in multiple formats and complies with the Open Geospatial Consortium ‘s’ (OGC) requirement. According to OpenGIS specifications. Common data formats supported by OpenGIS specifications include:

  • WKT
  • WKB
  • GeoJson
  • ESRI Geometry Object Json
  • ESRI Shape

DLA adopts the 4326-coordinate system standard. EPSG 4326 adopts longitude and latitude coordinates, which belongs to the geographic coordinate system. GPS uses this coordinate 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

Mapping between WKT data and WKB data:

WKT WKB
POINT (0 0) 010100000000000000000000000000000000000000
LINESTRING (0 0, 1 1, 1 2) Cost
POLYGON ((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)) Cost
MULTIPOINT (0 0, 1 2) Cost
MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4)) Cost
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))) Cost
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 Shape binary data

For more information, see ESRT .

Function list

Name Error message
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 a face or Multiface. For point, line, returns 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 specified 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 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 inner rings, with a Polygon as the input parameter.
ST_NumPoints Returns the number of points (inflection points) in a geometry.
ST_IsRing Determines whether a Line 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 combinatorial boundary of the entered geometry, as a geometry.
ST_Envelope Returns the minimum bounding box of the geometry as a polygon.
ST_Difference Returns a geometry that represents the difference between two geometries.
ST_Distance Returns the distance between two geometries. This distance is the distance between the closest inflection points of the two geometries.
St_externorring Returns the outer ring of a face in the form of a Line.
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 the first geometry contains the second geometry.
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 a geometry having the same dimension and not the same as any source object.
ST_Relate Determines whether two geometries meet the conditions specified by the DE-9IM matrix string.
ST_Touches Determines whether the common points of the two geometries do not 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_linefrowkbhexstring 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_mlinefromkbhexstring 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 object.
ST_asJson Converts a Geometry into an ESRI Geometry Object Json string.
St_geometryfromscript Returns the geometry that corresponds to the entered ESRI Shape binary data.

Function definitions and examples

ST_asText

  1. ST_asText(GEOMETRY)
  • Function description: This function converts a Geometry into a WKT string.

  • Type of the returned value: VARCHAR.

  • Example code:

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

ST_LineFromText

  1. ST_LineFromText(VARCHAR)
  • Function description: This function generates a Line Geometry from a Line-specific WKT string.

  • Type of the returned value: GEOMETRY

  • Example code:

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

ST_Point

  1. ST_Point(DOUBLE, DOUBLE)
  • Function description: generates a Point Geometry from the coordinate values (X and Y) of a coordinate system.

  • Type of the returned value: GEOMETRY

  • Example code:

    1. select ST_asText(ST_Point(30.2741500000,120.1551500000));
    2. +----------------------------+
    3. | _col0 |
    4. +----------------------------+
    5. | POINT (30.27415 120.15515) |
    6. +----------------------------+

ST_Polygon

  1. ST_Polygon(VARCHAR)
  • Function description: This function generates a Polygon-specific Geometry from a Polygon-specific string.

  • Type of the returned value: GEOMETRY

  • Example code:

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

ST_Area

  1. ST_Area(Geometry)
  • Description: This function returns the area of one or more faces. For point, line, returns 0.0. The returned value for GeometryCollection is the area sum of all geometries in the collection.

  • Type of the returned value: DOUBLE

  • Example code:

    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))'));
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 15.0 |
    6. +-------+
    7. 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)))'));
    8. +-------+
    9. | _col0 |
    10. +-------+
    11. | 16.0 |
    12. +-------+

ST_GeometryFromText

  1. ST_GeometryFromText(VARCHAR)
  • Function description: This function generates a Geometry from A WKT string.

  • Type of the returned value: GEOMETRY

ST_Buffer

  1. ST_Buffer(GEOMETRY, DOUBLE)
  • Function description: This function obtains the geometric object and distance, and then returns the geometry that represents the buffer that surrounds the source object.

  • Type of the returned value: GEOMETRY

ST_Centroid

  1. ST_Centroid(GEOMETRY)
  • Function description: This function obtains the geometric center of a geometry.

  • Type of the returned value: GEOMETRY

  • Example code:

    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)))')));
    2. +-----------------------+
    3. | _col0 |
    4. +-----------------------+
    5. | POINT (1.8125 1.8125) |
    6. +-----------------------+
    7. select ST_asText(ST_Centroid(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2)')));
    8. +---------------------------------------------+
    9. | _col0 |
    10. +---------------------------------------------+
    11. | POINT (0.7071067811865476 0.914213562373095)|
    12. +---------------------------------------------+
    13. select ST_asText(ST_Centroid(ST_GeometryFromText('POINT (0.5 1)')));
    14. +---------------+
    15. | _col0 |
    16. +---------------+
    17. | POINT (0.5 1) |
    18. +---------------+
    19. 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))')));
    20. +---------------------------------------------+
    21. | _col0 |
    22. +---------------------------------------------+
    23. | POINT (2.033333333333333 2.033333333333333) |
    24. +---------------------------------------------+

ST_CoordDim

  1. ST_CoordDim(GEOMETRY)
  • Function description: This function returns the dimensions of coordinate values for a geometry.

  • Type of the returned value: BIGINT

  • Example code:

    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))'));
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 2 |
    6. +-------+
    7. 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)))'));
    8. +-------+
    9. | _col0 |
    10. +-------+
    11. | 2 |
    12. +-------+

ST_Dimension

  1. ST_Dimension(GEOMETRY)
  • Function description: This function returns the dimensions of a geometry object. In this case, dimension refers to 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.

  • Type of the returned value: BIGINT

  • Example code:

    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)))'));
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 2 |
    6. +-------+

ST_IsClosed

  1. ST_IsClosed(GEOMETRY)
  • Function description: This function determines whether a Line or MultiLine is closed.

  • Type of the returned value: BOOLEAN

  • Example code:

    1. select ST_IsClosed(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2)'));
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 0 |
    6. +-------+
    7. select ST_IsClosed(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2, 0 0)'));
    8. +-------+
    9. | _col0 |
    10. +-------+
    11. | 1 |
    12. +-------+
    13. select ST_IsClosed(ST_GeometryFromText('MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))'));
    14. +-------+
    15. | _col0 |
    16. +-------+
    17. | 0 |
    18. +-------+

ST_IsEmpty

  1. ST_IsEmpty(GEOMETRY)
  • Function description: This function checks whether a geometry is empty.

  • Type of the returned value: BOOLEAN

  • Example code:

    1. select ST_IsEmpty(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2, 0 0)'));
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 0 |
    6. +-------+
    7. select ST_IsEmpty(null);
    8. +-------+
    9. | _col0 |
    10. +-------+
    11. | NULL |
    12. +-------+
    13. SELECT ST_IsEmpty(ST_GeometryFromText('GEOMETRYCOLLECTION EMPTY'));
    14. +-------+
    15. | _col0 |
    16. +-------+
    17. | 1 |
    18. +-------+
    19. SELECT ST_IsEmpty(ST_GeometryFromText('POLYGON EMPTY'));
    20. +-------+
    21. | _col0 |
    22. +-------+
    23. | 1 |
    24. +-------+

ST_Length

  1. ST_Length(GEOMETRY)
  • Description: This function calculates the length of a Line or MultiLine.

  • Type of the returned value: DOUBLE

  • Example code:

    1. SELECT ST_Length(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2, 0 0)'));
    2. +-------------------+
    3. | _col0 |
    4. +-------------------+
    5. | 4.650281539872885 |
    6. +-------------------+
    7. SELECT ST_Length(ST_GeometryFromText('MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))'));
    8. +-------------------+
    9. | _col0 |
    10. +-------------------+
    11. | 6.656854249492381 |
    12. +-------------------+

ST_XMax

  1. ST_XMax(GEOMETRY)
  • Function description: This function returns the maximum X coordinate value of a geometry in the coordinate system.

  • Type of the returned value: DOUBLE

  • Example code:

    1. SELECT ST_XMax(ST_GeometryFromText('MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))'));
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 5.0 |
    6. +-------+
    7. SELECT ST_XMax(ST_GeometryFromText('POINT (0.5 1)'));
    8. +-------+
    9. | _col0 |
    10. +-------+
    11. | 0.5 |
    12. +-------+

ST_XMin

  1. ST_XMin(GEOMETRY)
  • Function description: This function returns the minimum X coordinate value of a geometry in the coordinate system.

  • Type of the returned value: DOUBLE

  • Example code:

    1. SELECT ST_XMin(ST_GeometryFromText('MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))'));
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 0.0 |
    6. +-------+
    7. SELECT ST_XMin(ST_GeometryFromText('POINT (0.5 1)'));
    8. +-------+
    9. | _col0 |
    10. +-------+
    11. | 0.5 |
    12. +-------+

ST_YMax

  1. ST_YMax(GEOMETRY)
  • Function description: This function returns the maximum Y coordinate value of a geometry in the coordinate system.

  • Type of the returned value: DOUBLE

  • Example code:

    1. SELECT ST_YMax(ST_GeometryFromText('MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))'));
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 4.0 |
    6. +-------+
    7. SELECT ST_YMax(ST_GeometryFromText('POINT (0.5 1)'));
    8. +-------+
    9. | _col0 |
    10. +-------+
    11. | 1.0 |
    12. +-------+

ST_YMin

  1. ST_YMin(GEOMETRY)
  • Function description: This function returns the minimum Y coordinate value of a geometry in the coordinate system.

  • Type of the returned value: DOUBLE

  • Example code:

    1. SELECT ST_YMin(ST_GeometryFromText('MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))'));
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 0.0 |
    6. +-------+
    7. SELECT ST_YMin(ST_GeometryFromText('POINT (0.5 1)'));
    8. +-------+
    9. | _col0 |
    10. +-------+
    11. | 1.0 |
    12. +-------+

ST_NumInteriorRing

  1. ST_NumInteriorRing(GEOMETRY)
  • Function description: This function uses a Polygon as the input parameter and returns the number of rings within the Polygon.

  • Type of the returned value: BIGINT

  • Example code:

    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))'));
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 1 |
    6. +-------+

ST_NumPoints

  1. ST_NumPoints(GEOMETRY)
  • Function description: This function returns the number of points in a geometry.

  • Type of the returned value: BIGINT

  • Example code:

    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))'));
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 8 |
    6. +-------+
    7. SELECT ST_NumPoints(ST_GeometryFromText('POLYGON EMPTY'));
    8. +-------+
    9. | _col0 |
    10. +-------+
    11. | 0 |
    12. +-------+
    13. SELECT ST_NumPoints(ST_GeometryFromText('POINT (0.5 1)'));
    14. +-------+
    15. | _col0 |
    16. +-------+
    17. | 1 |
    18. +-------+

ST_IsRing

  1. ST_IsRing(GEOMETRY)
  • Function description: This function uses a Line as the input parameter to determine whether a Line is a ring, for example, whether the Line is closed.

  • Type of the returned value: BOOLEAN

  • Example code:

    1. select ST_IsRing(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2)'));
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 0 |
    6. +-------+
    7. select ST_IsRing(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2, 0 0)'));
    8. +-------+
    9. | _col0 |
    10. +-------+
    11. | 1 |
    12. +-------+

ST_StartPoint

  1. ST_StartPoint(GEOMETRY)
  • Description: This function returns the first vertex of a Line.

  • Type of the returned value: GEOMETRY

  • Example code:

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

ST_EndPoint

  1. ST_EndPoint(GEOMETRY)
  • Description: This function returns the last vertex of a Line.

  • Type of the returned value: GEOMETRY

  • Example code:

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

ST_X

  1. ST_X(GEOMETRY)
  • Description: This function returns the X coordinate value of a Point.

  • Type of the returned value: DOUBLE

  • Example code:

    1. select ST_X(ST_GeometryFromText('POINT (0.5 1)'));
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 0.5 |
    6. +-------+

ST_Y

  1. ST_Y(GEOMETRY)
  • Function description: This function returns the Y coordinate value of a Point.

  • Type of the returned value: DOUBLE

  • Example code:

    1. select ST_Y(ST_GeometryFromText('POINT (0.5 1)'));
    2. +-------+
    3. | _col0 |
    4. +-------+
    5. | 1.0 |
    6. +-------+

ST_Boundary

  1. ST_Boundary(GEOMETRY)
  • Function description: This function enters a geometry and returns the combinatorial boundary of the entered geometry as a geometry object.

  • Type of the returned value: GEOMETRY

  • Example code:

    1. select ST_asText(ST_Boundary(ST_GeometryFromText('POINT (0.5 1)')));
    2. +-------------------+
    3. | _col0 |
    4. +-------------------+
    5. | MULTIPOINT EMPTY |
    6. +-------------------+
    7. select ST_asText(ST_Boundary(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2)')));
    8. +---------------------------+
    9. | _col0 |
    10. +---------------------------+
    11. | MULTIPOINT ((0 0), (1 2)) |
    12. +---------------------------+
    13. 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)))')));
    14. +-------------------------------------------------------------------------------------------------------------+
    15. | _col0 |
    16. +-------------------------------------------------------------------------------------------------------------+
    17. | 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)) |
    18. +-------------------------------------------------------------------------------------------------------------+

ST_Envelope

  1. ST_Envelope(GEOMETRY)
  • Description: This function returns the minimum bounding box of a polygon.

  • Type of the returned value: GEOMETRY

  • Example code:

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

ST_Difference

  1. ST_Difference(GEOMETRY, GEOMETRY)
  • Function description: This function enters two geometries and returns a geometry that represents the difference between the two geometries.

  • Type of the returned value: GEOMETRY

  • Example code:

    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))')));
    4. +--------------------------------------------------------------------------------------------------------------+
    5. | _col0 |
    6. +--------------------------------------------------------------------------------------------------------------+
    7. | 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))) |
    8. +--------------------------------------------------------------------------------------------------------------+
    9. select ST_asText(ST_Difference(
    10. 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. 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)))')));
    12. +--------------------+
    13. | _col0 |
    14. +--------------------+
    15. | MULTIPOLYGON EMPTY |
    16. +--------------------+

ST_Distance

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

  • Type of the returned value: DOUBLE

  • Example code:

    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)))'));
    4. +-------+
    5. | _col0 |
    6. +-------+
    7. | 0.0 |
    8. +-------+
    9. select ST_Distance(
    10. ST_GeometryFromText('POINT(0 0)'),
    11. ST_GeometryFromText('POINT(1 1)'));
    12. +--------------------+
    13. | _col0 |
    14. +--------------------+
    15. | 1.4142135623730951 |
    16. +--------------------+

St_externorring

  1. ST_ExteriorRing(GEOMETRY)
  • Function description: This function returns the outer ring of a face in Line format.

  • Type of the returned value: GEOMETRY

  • Example code:

    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))')));
    2. +--------------------------------------+
    3. | _col0 |
    4. +--------------------------------------+
    5. | LINESTRING (0 0, 4 0, 4 4, 0 4, 0 0) |
    6. +--------------------------------------+

ST_Intersection

  1. ST_Intersection(GEOMETRY, GEOMETRY)
  • Function description: This function returns a two-dimensional intersection of two input geometries.

  • Type of the returned value: GEOMETRY

  • Example code:

    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)))')));
    4. +--------------------------------------------------------------------------------------------------------------+
    5. | _col0 |
    6. +--------------------------------------------------------------------------------------------------------------+
    7. | 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))) |
    8. +--------------------------------------------------------------------------------------------------------------+
    9. select ST_asText(ST_Intersection(
    10. ST_GeometryFromText('POINT(0 0)'),
    11. ST_GeometryFromText('POINT(1 1)')));
    12. +--------------------+
    13. | _col0 |
    14. +--------------------+
    15. | MULTIPOLYGON EMPTY |
    16. +--------------------+

ST_SymDifference

  1. ST_SymDifference(GEOMETRY, GEOMETRY)
  • Description: This function returns a geometry that represents the symmetric difference in a point set between two input geometries.

  • Type of the returned value: GEOMETRY

  • Example code:

    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)))')));
    4. +--------------------+
    5. | _col0 |
    6. +--------------------+
    7. | MULTIPOLYGON EMPTY |
    8. +--------------------+
    9. select ST_asText(ST_SymDifference(
    10. ST_GeometryFromText('POINT(0 0)'),
    11. ST_GeometryFromText('POINT(1 1)')));
    12. +---------------------------+
    13. | _col0 |
    14. +---------------------------+
    15. | MULTIPOINT ((0 0), (1 1)) |
    16. +---------------------------+

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

  1. ST_Contains(GEOMETRY, GEOMETRY)
  • Function description: This function enters two geometry objects to determine whether the first geometry object contains the second geometry object.

  • Type of the returned value: BOOLEAN

  • Example code:

    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)))'));
    4. +-------+
    5. | _col0 |
    6. +-------+
    7. | 1 |
    8. +-------+
    9. select ST_Contains(
    10. ST_GeometryFromText('POINT(0 0)'),
    11. ST_GeometryFromText('POINT(1 1)'));
    12. +-------+
    13. | _col0 |
    14. +-------+
    15. | 0 |
    16. +-------+

ST_Crosses

  1. ST_Crosses(GEOMETRY, GEOMETRY)
  • Function description: If the dimension of the geometry representing the intersection of two geometries is smaller than the maximum dimension of the two source geometries, the function returns 1. 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.

  • Type of the returned value: BOOLEAN

  • Example code:

    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)))'));
    4. +-------+
    5. | _col0 |
    6. +-------+
    7. | 0 |
    8. +-------+
    9. select ST_Crosses(
    10. ST_GeometryFromText('LINESTRING(0 0, 2 2)'),
    11. ST_GeometryFromText('LINESTRING(0 2, 2 0)'));
    12. +-------+
    13. | _col0 |
    14. +-------+
    15. | 1 |
    16. +-------+

ST_Disjoint

  1. ST_Disjoint(GEOMETRY, GEOMETRY)
  • Description: This function determines whether the intersection of two geometries is an empty set.

  • Type of the returned value: BOOLEAN

  • Example code:

    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)))'));
    4. +-------+
    5. | _col0 |
    6. +-------+
    7. | 0 |
    8. +-------+
    9. select ST_Disjoint(
    10. ST_GeometryFromText('LINESTRING(0 0, 2 2)'),
    11. ST_GeometryFromText('LINESTRING(0 2, 2 4)'));
    12. +-------+
    13. | _col0 |
    14. +-------+
    15. | 1 |
    16. +-------+

ST_Equals

  1. ST_Equals(GEOMETRY, GEOMETRY)
  • Description: This function checks whether two geometries are the same.

  • Type of the returned value: BOOLEAN

  • Example code:

    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)))'));
    4. +-------+
    5. | _col0 |
    6. +-------+
    7. | 1 |
    8. +-------+
    9. select ST_Equals(
    10. ST_GeometryFromText('LINESTRING(0 0, 2 2)'),
    11. ST_GeometryFromText('LINESTRING(0 2, 2 4)'));
    12. +-------+
    13. | _col0 |
    14. +-------+
    15. | 0 |
    16. +-------+

ST_Intersects

  1. ST_Intersects(GEOMETRY, GEOMETRY)
  • Function description: This function determines whether the intersection of two geometries does not generate an empty set.

  • Type of the returned value: BOOLEAN

  • Example code:

    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)))'));
    4. +-------+
    5. | _col0 |
    6. +-------+
    7. | 1 |
    8. +-------+
    9. select ST_Intersects(
    10. ST_GeometryFromText('LINESTRING(0 0, 2 2)'),
    11. ST_GeometryFromText('LINESTRING(0 2, 2 4)'));
    12. +-------+
    13. | _col0 |
    14. +-------+
    15. | 0 |
    16. +-------+

ST_Overlaps

  1. ST_Overlaps(GEOMETRY, GEOMETRY)
  • Function description: This function determines whether geometries generated from the intersection of two geometries have the same dimension but are not equal to any source objects.

  • Type of the returned value: BOOLEAN

  • Example code:

    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)))'));
    4. +-------+
    5. | _col0 |
    6. +-------+
    7. | 0 |
    8. +-------+
    9. select ST_Overlaps(
    10. ST_GeometryFromText('LINESTRING(1 1, 2 2)'),
    11. ST_GeometryFromText('LINESTRING(0 0, 1.5 1.5)'));
    12. +-------+
    13. | _col0 |
    14. +-------+
    15. | 1 |
    16. +-------+

ST_Relate

  1. ST_Relate(GEOMETRY, GEOMETRY, VARCHAR)
  • Description: This function compares two geometric objects and determines whether the two geometric objects meet the conditions specified by the DE-9IM mode matrix string.

  • Type of the returned value: BOOLEAN

  • Example code:

    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');
    5. +-------+
    6. | _col0 |
    7. +-------+
    8. | 0 |
    9. +-------+
    10. select ST_Relate(
    11. ST_GeometryFromText('LINESTRING(1 1, 2 2)'),
    12. ST_GeometryFromText('LINESTRING(0 0, 1.5 1.5)'),
    13. '1*1***1**');
    14. +-------+
    15. | _col0 |
    16. +-------+
    17. | 1 |
    18. +-------+

ST_Touches

  1. ST_Touches(GEOMETRY, GEOMETRY)
  • Description: This function is used to determine whether the common points of two geometries intersect the interior of the two geometries.

  • Type of the returned value: BOOLEAN

  • Example code:

    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)))'));
    4. +-------+
    5. | _col0 |
    6. +-------+
    7. | 0 |
    8. +-------+
    9. select ST_Touches(
    10. ST_GeometryFromText('LINESTRING(1 1, 2 2)'),
    11. ST_GeometryFromText('LINESTRING(0 0, 1 1)'));
    12. +-------+
    13. | _col0 |
    14. +-------+
    15. | 1 |
    16. +-------+

ST_Within

  1. ST_Within(GEOMETRY, GEOMETRY)
  • Description: This function determines whether the first geometry is completely within the range of the second geometry.

  • Type of the returned value: BOOLEAN

  • Example code:

    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)))'));
    4. +-------+
    5. | _col0 |
    6. +-------+
    7. | 1 |
    8. +-------+
    9. select ST_Within(
    10. ST_GeometryFromText('LINESTRING(1 1, 2 2)'),
    11. ST_GeometryFromText('LINESTRING(0 0, 1 1)'));
    12. +-------+
    13. | _col0 |
    14. +-------+
    15. | 0 |
    16. +-------+

ST_asBinary

  1. ST_asBinary(GEOMETRY)
  • Function description: returns the WKB value that the geometry object can recognize.

  • Type of the returned value: VARBINARY.

ST_GeometryFromWKBHexString

  1. ST_GeometryFromWKBHexString(VARCHAR)
  • Function description: This function returns the geometry that corresponds to the entered WKB hexString.

  • Type of the returned value: GEOMETRY

  • Example code:

    1. select ST_ASTEXT(ST_GeometryFromWKBHexString('000000000140000000000000004010000000000000'));
    2. +-------------+
    3. | _col0 |
    4. +-------------+
    5. | POINT (2 4) |
    6. +-------------+

ST_pointFromWKBHexString

  1. ST_pointFromWKBHexString(VARCHAR)
  • Function description: This function returns the Point geometry that corresponds to the entered Point-specific WKB hexString.

  • Type of the returned value: GEOMETRY

  • Example code:

    1. select ST_ASTEXT(ST_pointFromWKBHexString('000000000140000000000000004010000000000000'));
    2. +-------------+
    3. | _col0 |
    4. +-------------+
    5. | POINT (2 4) |
    6. +-------------+

St_linefrowkbhexstring

  1. ST_lineFromWKBHexString(VARCHAR)
  • Function description: This function returns the Line geometry that corresponds to the entered Line-specific WKB hexString.

  • Type of the returned value: GEOMETRY

ST_polyFromWKBHexString

  1. ST_polyFromWKBHexString(VARCHAR)
  • Function description: This function returns the Polygon geometry that corresponds to the entered Polygon-specific WKB hexString.

  • Type of the returned value: GEOMETRY

ST_MPointFromWKBHexString

  1. ST_MPointFromWKBHexString(VARCHAR)
  • Function description: This function returns the MultiPoint geometry that corresponds to the entered MultiPoint-specific WKB hexString.

  • Type of the returned value: GEOMETRY

St_mlinefromkbhexstring

  1. ST_MLineFromWKBHexString(VARCHAR)
  • Function description: This function returns the MultiLine geometry that corresponds to the entered MultiLine-specific WKB hexString.

  • Type of the returned value: GEOMETRY

ST_MPolyFromWKBHexString

  1. ST_MPolyFromWKBHexString(VARCHAR)
  • Description: This function returns the MultiPolygon geometry that corresponds to the entered MultiPolygon-specific WKB hexString.

  • Type of the returned value: GEOMETRY

ST_GeometryFromWKB

  1. ST_GeometryFromWKB(VARBINARY)
  • Function description: This function returns the geometry that corresponds to the entered WKB data.

  • Type of the returned value: GEOMETRY

  • Example code:

    1. select ST_ASTEXT(ST_GeometryFromWKB(from_hex('000000000140000000000000004010000000000000')));
    2. +-------------+
    3. | _col0 |
    4. +-------------+
    5. | POINT (2 4) |
    6. +-------------+

ST_pointFromWKB

  1. ST_pointFromWKB(VARBINARY)
  • Function description: This function returns the Point geometry that corresponds to the entered Point-specific WKB data.

  • Type of the returned value: GEOMETRY

  • Example code:

    1. select ST_ASTEXT(ST_pointFromWKB(from_hex('000000000140000000000000004010000000000000')));
    2. +-------------+
    3. | _col0 |
    4. +-------------+
    5. | POINT (2 4) |
    6. +-------------+

ST_lineFromWKB

  1. ST_lineFromWKB(VARBINARY)
  • Function description: This function returns the Line geometry that corresponds to the entered Line-specific WKB data.

  • Type of the returned value: GEOMETRY

ST_polyFromWKB

  1. ST_polyFromWKB(VARBINARY)
  • Function description: This function returns the Polygon geometry that corresponds to the entered Polygon-specific WKB data.

  • Type of the returned value: GEOMETRY

ST_MPointFromWKB

  1. ST_MPointFromWKB(VARBINARY)
  • Function description: This function returns the MultiPoint geometry that corresponds to the entered MultiPoint-specific WKB data.

  • Type of the returned value: GEOMETRY

ST_MLineFromWKB

  1. ST_MLineFromWKB(VARBINARY)
  • Function description: This function returns the MultiLine geometry that corresponds to the entered MultiLine-specific WKB data.

  • Type of the returned value: GEOMETRY

ST_MPolyFromWKB

  1. ST_MPolyFromWKB(VARBINARY)
  • Function description: This function returns the MultiPolygon geometry that corresponds to the entered MultiPolygon-specific WKB data.

  • Type of the returned value: GEOMETRY

ST_GeometryFromGeoJson

  1. ST_GeometryFromGeoJson(VARCHAR)
  • Function description: This function returns the geometry that corresponds to the entered GeoJson string.

  • Type of the returned value: GEOMETRY

ST_GeometryFromJson

  1. ST_GeometryFromJson(VARCHAR)
  • Description: This function returns the Geometry corresponding to an ESRI Geometry Object Json string.

  • Type of the returned value: GEOMETRY

ST_asGeoJson

  1. ST_asGeoJson(GEOMETRY) VARCHAR
  • Function description: This function converts a geometry into a GeoJson string.

  • Type of the returned value: GEOMETRY

  • Example code:

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

ST_asJson

  1. ST_asJson(GEOMETRY)
  • Function description: This function converts a Geometry into an ESRI Geometry Object Json string.

  • Type of the returned value: VARCHAR.

  • Example code:

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

St_geometryfromscript

  1. ST_GeometryFromEsriShape(VARBINARY)
  • Function description: This function returns the geometry that corresponds to the entered ESRI Shape binary data.

  • Type of the returned value: GEOMETRY

  • Example code:

    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;
    8. +-----------------+------+
    9. | name | cnt |
    10. +-----------------+------+
    11. | San Benito | 8 |
    12. | San Bernardino | 7 |
    13. | Riverside | 6 |
    14. | Inyo | 5 |
    15. | Imperial | 3 |
    16. | San Diego | 2 |
    17. | Kern | 1 |
    18. | Kings | 1 |
    19. | Monterey | 1 |
    20. | San Luis Obispo | 1 |
    21. | Santa Clara | 1 |
    22. | Ventura | 1 |
    23. +-----------------+------+