CITY_ADCODE
city_adcode(longitude, latitude)
Function description: This function processes data based on the longitude.
Longitude
and Latitudelatitude
queries the city ADCode.If
longitude
andlatitude
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 Latitudelatitude
value to query the ADCode of the corresponding province.If
longitude
andlatitude
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:
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
ST_asText(GEOMETRY)
Function description: This function converts a Geometry into a WKT string.
Type of the returned value: VARCHAR.
Example code:
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.
Type of the returned value: GEOMETRY
Example code:
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.
Type of the returned value: GEOMETRY
Example code:
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.
Type of the returned value: GEOMETRY
Example code:
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.
Type of the returned value: DOUBLE
Example code:
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.
Type of the returned value: 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.
Type of the returned value: GEOMETRY
ST_Centroid
ST_Centroid(GEOMETRY)
Function description: This function obtains the geometric center of a geometry.
Type of the returned value: GEOMETRY
Example code:
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.
Type of the returned value: BIGINT
Example code:
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.
Type of the returned value: BIGINT
Example code:
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.
Type of the returned value: BOOLEAN
Example code:
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.
Type of the returned value: BOOLEAN
Example code:
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.
Type of the returned value: DOUBLE
Example code:
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.
Type of the returned value: DOUBLE
Example code:
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.
Type of the returned value: DOUBLE
Example code:
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.
Type of the returned value: DOUBLE
Example code:
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.
Type of the returned value: DOUBLE
Example code:
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.
Type of the returned value: BIGINT
Example code:
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.
Type of the returned value: BIGINT
Example code:
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.
Type of the returned value: BOOLEAN
Example code:
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.
Type of the returned value: GEOMETRY
Example code:
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.
Type of the returned value: GEOMETRY
Example code:
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.
Type of the returned value: DOUBLE
Example code:
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.
Type of the returned value: DOUBLE
Example code:
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.
Type of the returned value: GEOMETRY
Example code:
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.
Type of the returned value: GEOMETRY
Example code:
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.
Type of the returned value: GEOMETRY
Example code:
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.
Type of the returned value: DOUBLE
Example code:
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_externorring
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:
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.
Type of the returned value: GEOMETRY
Example code:
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.
Type of the returned value: GEOMETRY
Example code:
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.
Type of the returned value: BOOLEAN
Example code:
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.
Type of the returned value: BOOLEAN
Example code:
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.
Type of the returned value: BOOLEAN
Example code:
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.
Type of the returned value: BOOLEAN
Example code:
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.
Type of the returned value: BOOLEAN
Example code:
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.
Type of the returned value: BOOLEAN
Example code:
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.
Type of the returned value: BOOLEAN
Example code:
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.
Type of the returned value: BOOLEAN
Example code:
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.
Type of the returned value: BOOLEAN
Example code:
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 returned value: VARBINARY.
ST_GeometryFromWKBHexString
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:
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.
Type of the returned value: GEOMETRY
Example code:
select ST_ASTEXT(ST_pointFromWKBHexString('000000000140000000000000004010000000000000'));
+-------------+
| _col0 |
+-------------+
| POINT (2 4) |
+-------------+
St_linefrowkbhexstring
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
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
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
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
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
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:
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.
Type of the returned value: GEOMETRY
Example code:
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.
Type of the returned value: GEOMETRY
ST_polyFromWKB
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
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
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
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
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
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
ST_asGeoJson(GEOMETRY) VARCHAR
Function description: This function converts a geometry into a GeoJson string.
Type of the returned value: GEOMETRY
Example code:
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)
Function description: This function converts a Geometry into an ESRI Geometry Object Json string.
Type of the returned value: VARCHAR.
Example code:
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_geometryfromscript
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:
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 |
+-----------------+------+