Geospatial function

CITY_ADCODE

city_adcode(longitude, latitude)

• Function description: This function processes data based on the longitude. Longitude and Latitude queries the city ADCode.

  If Longitude and latitude if any value is illegal or NULL, an empty string is returned.

• Type of the returned value: STRING.

• Example code:

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

PROVINCE_ADCODE

province_adcode(longitude, latitude) 

• Function description: This function processes data based on the longitude. Longitude and Latitude value to query the ADCode of the corresponding province.

  If longitude and latitude if any value is illegal or NULL, an empty string is returned.

• Type of the returned value: STRING.

• Example code:

   select province_adcode(118.0, 28.0); +---------------------------------+ | 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:

Function list

Function definitions and examples

ST_asText

ST_asText(GEOMETRY) 

• Function description: This function converts a Geometry into a WKT string.

• Return value type: 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) 

• Function description: This function generates a Line Geometry from a Line-specific WKT string.

• Return value type: 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) 

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

• Return value type: GEOMETRY.

• Example:

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

ST_Polygon

ST_Polygon(VARCHAR) 

• Function description: This function generates a Polygon-specific Geometry from a Polygon-specific string.

• Return value type: 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: 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.

• Return value type: 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) 

• Function description: This function generates a Geometry from A WKT string.

• Return value type: GEOMETRY.

ST_Buffer

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.

• Return value type: GEOMETRY.

ST_Centroid

ST_Centroid(GEOMETRY) 

• Function description: This function obtains the geometric center of a geometry.

• Return value type: 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) 

• Function description: This function returns the dimensions of coordinate values for a geometry.

• Return value type: 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) 

• 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.

• Return value type: 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) 

• Function description: This function determines whether a Line or MultiLine is closed.

• Return value type: 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) 

• Function description: This function checks whether a geometry is empty.

• Return value type: 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: This function calculates the length of a Line or MultiLine.

• Return value type: 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) 

• Function description: This function returns the maximum X coordinate value of a geometry in the coordinate system.

• Return value type: 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) 

• Function description: This function returns the minimum X coordinate value of a geometry in the coordinate system.

• Return value type: 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) 

• Function description: This function returns the maximum Y coordinate value of a geometry in the coordinate system.

• Return value type: 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) 

• Function description: This function returns the minimum Y coordinate value of a geometry in the coordinate system.

• Return value type: 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) 

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

• Return value type: 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) 

• Function description: This function returns the number of points in a geometry.

• Return value type: 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) 

• 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.

• Return value type: 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: This function returns the first vertex of a Line.

• Return value type: 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: This function returns the last vertex of a Line.

• Return value type: 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: This function returns the X coordinate value of a Point.

• Return value type: DOUBLE.

• Example:

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

ST_Y

ST_Y(GEOMETRY) 

• Function description: This function returns the Y coordinate value of a Point.

• Return value type: DOUBLE.

• Example:

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

ST_Boundary

ST_Boundary(GEOMETRY) 

• Function description: This function enters a geometry and returns the combinatorial boundary of the entered geometry as a geometry object.

• Return value type: 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: This function returns the minimum bounding box of a polygon.

• Return value type: 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) 

• Function description: This function enters two geometries and returns a geometry that represents the difference between the two geometries.

• Return value type: 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: This function returns the distance between two geometries. This distance is the distance between the closest inflection points of the two geometries.

• Return value type: 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) 

• Function description: This function returns the outer ring of a face in Line format.

• Return value type: 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) 

• Function description: This function returns a two-dimensional intersection of two input geometries.

• Return value type: 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: This function returns a geometry that represents the symmetric difference in a point set between two input geometries.

• Return value type: 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. 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.

  

ST_Contains

ST_Contains(GEOMETRY, GEOMETRY) 

• Function description: This function enters two geometry objects to determine whether the first geometry object contains the second geometry object.

• Return value type: 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) 

• 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.

• Return value type: 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: This function determines whether the intersection of two geometries is an empty set.

• Return value type: 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: This function checks whether two geometries are the same.

• Return value type: 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) 

• Function description: This function determines whether the intersection of two geometries does not generate an empty set.

• Return value type: 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) 

• 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.

• Return value type: 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: This function compares two geometric objects and determines whether the two geometric objects meet the conditions specified by the DE-9IM mode matrix string.

• Return value type: 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: This function is used to determine whether the common points of two geometries intersect the interior of the two geometries.

• Return value type: 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: This function determines whether the first geometry is completely within the range of the second geometry.

• Return value type: 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) 

• Function description: returns the WKB value that the geometry object can recognize.

• Type of the return value: VARBINARY.

ST_GeometryFromWKBHexString

ST_GeometryFromWKBHexString(VARCHAR) 

• Function description: This function returns the geometry that corresponds to the entered WKB hexString.

• Return value type: GEOMETRY.

• Example:

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

ST_pointFromWKBHexString

ST_pointFromWKBHexString(VARCHAR) 

• Function description: This function returns the Point geometry that corresponds to the entered Point-specific WKB hexString.

• Return value type: GEOMETRY.

• Example:

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

ST_lineFromWKBHexString

ST_lineFromWKBHexString(VARCHAR) 

• Function description: This function returns the Line geometry that corresponds to the entered Line-specific WKB hexString.

• Return value type: GEOMETRY.

ST_polyFromWKBHexString

ST_polyFromWKBHexString(VARCHAR) 

• Function description: This function returns the Polygon geometry that corresponds to the entered Polygon-specific WKB hexString.

• Return value type: GEOMETRY.

ST_MPointFromWKBHexString

ST_MPointFromWKBHexString(VARCHAR) 

• Function description: This function returns the MultiPoint geometry that corresponds to the entered MultiPoint-specific WKB hexString.

• Return value type: GEOMETRY.

ST_MLineFromWKBHexString

ST_MLineFromWKBHexString(VARCHAR) 

• Function description: This function returns the MultiLine geometry that corresponds to the entered MultiLine-specific WKB hexString.

• Return value type: GEOMETRY.

ST_MPolyFromWKBHexString

ST_MPolyFromWKBHexString(VARCHAR) 

• Description: This function returns the MultiPolygon geometry that corresponds to the entered MultiPolygon-specific WKB hexString.

• Return value type: GEOMETRY.

ST_GeometryFromWKB

ST_GeometryFromWKB(VARBINARY) 

• Function description: This function returns the geometry that corresponds to the entered WKB data.

• Return value type: GEOMETRY.

• Example:

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

ST_pointFromWKB

ST_pointFromWKB(VARBINARY) 

• Function description: This function returns the Point geometry that corresponds to the entered Point-specific WKB data.

• Return value type: GEOMETRY.

• Example:

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

ST_lineFromWKB

ST_lineFromWKB(VARBINARY) 

• Function description: This function returns the Line geometry that corresponds to the entered Line-specific WKB data.

• Return value type: GEOMETRY.

ST_polyFromWKB

ST_polyFromWKB(VARBINARY) 

• Function description: This function returns the Polygon geometry that corresponds to the entered Polygon-specific WKB data.

• Return value type: GEOMETRY.

ST_MPointFromWKB

ST_MPointFromWKB(VARBINARY) 

• Function description: This function returns the MultiPoint geometry that corresponds to the entered MultiPoint-specific WKB data.

• Return value type: GEOMETRY.

ST_MLineFromWKB

ST_MLineFromWKB(VARBINARY) 

• Function description: This function returns the MultiLine geometry that corresponds to the entered MultiLine-specific WKB data.

• Return value type: GEOMETRY.

ST_MPolyFromWKB

ST_MPolyFromWKB(VARBINARY) 

• Function description: This function returns the MultiPolygon geometry that corresponds to the entered MultiPolygon-specific WKB data.

• Return value type: GEOMETRY.

ST_GeometryFromGeoJson

ST_GeometryFromGeoJson(VARCHAR) 

• Function description: This function returns the geometry that corresponds to the entered GeoJson string.

• Return value type: GEOMETRY.

ST_GeometryFromJson

ST_GeometryFromJson(VARCHAR) 

• Description: This function returns the Geometry corresponding to an ESRI Geometry Object Json string.

• Return value type: GEOMETRY.

ST_asGeoJson

ST_asGeoJson(GEOMETRY) VARCHAR 

• Function description: This function converts a geometry into a GeoJson string.

• Return value type: 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) 

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

• Return value type: 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) 

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

• Return value type: 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); 

• Function description: for the given longitude and latitude coordinates and the maximum hash length, the corresponding GeoHash string is generated.

• Return value type: varchar.

• Examples:

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

ST_LatFromGeoHash

ST_LatFromGeoHash(varchar geoHashString); 

• Function description: extracts the latitude value from the given GeoHash string.

• Return value type: double.

• Examples:

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

ST_LongFromGeoHash

ST_LongFromGeoHash(varchar geoHashString); 

• Function description: extracts the longitude value from the given GeoHash string.

• Return value type: double.

• Examples:

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

ST_GeoHash_Neighbors

ST_GeoHash_Neighbors(double longitude, double latitude, long maxCharLength); 

• Function description: For a given latitude and longitude coordinates and the maximum hash length, calculate the GeoHash string of 8 orientations adjacent to it.

• Return value type: varchar.

• Examples:

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