CITY_ADCODE
city_adcode(longitude, latitude)
Description: returns the administrative division code (ADCode) of a city based on its
longitude
andlatitude
.If the value of
longitude
orlatitude
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
andlatitude
.If the value of
longitude
orlatitude
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.
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] |
+--------------------------------------------------------------------------+