全部产品
云市场

地理空间函数

更新时间:2019-08-01 10:19:52

0. 简介

为满足部分客户在云上做Geometry数据的分析需求,阿里云Data Lake Analytics(以下简称:DLA)支持多种格式的地理空间数据处理函数,符合<span data-type="color" style="color:rgb(34, 34, 34)"><span data-type="background" style="background-color:rgb(255, 255, 255)">Open Geospatial Consortium’s (OGC) OpenGIS规范,支持的常用数据格式包括:</span></span>

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

DLA采用4326坐标系标准,<span data-type="color" style="color:rgb(36, 41, 46)"><span data-type="background" style="background-color:rgb(255, 255, 255)">EPSG 4326使用经纬度坐标,属于地理坐标系。GPS采用的就是这个坐标系。</span></span>

1. WKT数据

详细描述:https://en.wikipedia.org/wiki/Well-known_texthttp://www.opengeospatial.org/standards/wkt-crs

支持类似如下的WKT相关字符串。

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

2. WKB数据

上述WKT数据和WKB的对应示例:

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

3. GeoJson数据

GeoJson的详细描述:http://geojson.org/

4. ESRI Geometry Object Json数据

规范说明:http://resources.esri.com/help/9.3/arcgisserver/apis/REST/geometry.html

5. ESRI Shape二进制数据

规范说明:http://www.esri.com/LIBRARY/WHITEPAPERS/PDFS/SHAPEFILE.PDF

6. 函数列表

Name Description
ST_asText 将Geometry数据转成WKT格式的字符串数据。
ST_LineFromText 输入WKT格式的Line字符串数据,生成Line的Geometry数据。
ST_Point 从坐标系的坐标值(X, Y),生成对应的Point的Geometry数据。
ST_Polygon 输入WKT格式的Polygon字符串数据,生成Polygon的Geometry数据。
ST_Area 返回面或多面的面积。对于点、线,返回0.0。对于GeometryCollection,返回所有单个面积的和。
ST_GeometryFromText 输入WKT格式的字符串数据,生成Geometry数据。
ST_Buffer 获取几何对象和距离,然后返回表示围绕源对象的缓冲区的几何对象。
ST_Centroid 获取几何对象的中心点。
ST_CoordDim 返回几何对象的坐标值维度。
ST_Dimension 用于返回几何对象的维度。在这种情况下,维度是指长度和宽度。例如,点既没有长度也没有宽度,所以其维度为 0;而线只有长度却没有宽度,因此其维度为 1。
ST_IsClosed 判断Line或者MultiLine是否闭合。
ST_IsEmpty 判断几何对象是否为空。
ST_Length 计算Line或者MultiLine的长度。
ST_XMax 返回几何对象在坐标系中的最大X坐标值。
ST_XMin 返回几何对象在坐标系中的最小X坐标值。
ST_YMax 返回几何对象在坐标系中的最大Y坐标值。
ST_YMin 返回几何对象在坐标系中的最小Y坐标值。
ST_NumInteriorRing 以Polygon作为输入参数,并返回其内部环数。
ST_NumPoints 用于返回几何对象中的点(折点)数。
ST_IsRing 以Line作为输入参数,判断是否是环(如Line是闭合的)。
ST_StartPoint 用于返回Line的第一个点。
ST_EndPoint 用于返回Line的最后一个点。
ST_X 返回Point的X坐标。
ST_Y 返回Point的Y坐标。
ST_Boundary 输入一个几何对象,然后以几何对象形式返回其组合边界。
ST_Envelope 以多边形的形式返回几何对象的最小边界框。
ST_Difference 输入两个几何对象,然后返回表示两个源对象之差的几何对象。
ST_Distance 用于返回两个几何对象之间的距离。这一距离是两个几何对象的最近折点之间的距离。
ST_ExteriorRing 以Line形式返回面的外部环。
ST_Intersection 以两个几何对象作为输入参数,然后以二维几何对象的形式返回交集。
ST_SymDifference 返回表示两个几何对象间的点集对称差异的几何值对象。
ST_Contains 输入两个几何对象,判断第一个对象是否完全包含第二个对象。
ST_Crosses 以两个几何对象作为输入,如果这两个对象的交集生成的几何对象的维度小于两个源对象中的最大维度,则返回 1。交集对象所包含的点必须在两个源几何的内部,并且不等于其中任何一个源对象。否则,返回 0。
ST_Disjoint 输入两个几何对象,判断两个几何对象的交集是否为空集。
ST_Equals 判断两个几何对象是否完全相同。
ST_Intersects 判断两个几何对象的交集是否不生成空集。
ST_Overlaps 判断两个几何对象的交集生成的几何对象是否维度相同但不等于任一源对象。
ST_Relate 比较两个几何对象,判断是否满足“DE-9IM”模式矩阵字符串指定的条件。
ST_Touches 判断两个几何对象的公共点是否都不与两个几何对象的内部相交。
ST_Within 判断第一个几何对象是否完全位于第二个几何对象的范围内。
ST_asBinary 输入一个几何对象,然后返回其可识别的二进制WKB数据。
ST_GeometryFromWKBHexString 输入WKB的HEX字符串数据,返回对应的几何对象。
ST_pointFromWKBHexString 输入Point的WKB的HEX字符串数据,返回对应的Point几何对象。
ST_lineFromWKBHexString 输入Line的WKB的HEX字符串数据,返回对应的Line几何对象。
ST_polyFromWKBHexString 输入Polygon的WKB的HEX字符串数据,返回对应的Polygon几何对象。
ST_MPointFromWKBHexString 输入MultiPoint的WKB的HEX字符串数据,返回对应的MultiPoint几何对象。
ST_MLineFromWKBHexString 输入MultiLine的WKB的HEX字符串数据,返回对应的MultiLine几何对象。
ST_MPolyFromWKBHexString 输入MultiPolygon的WKB的HEX字符串数据,返回对应的MultiPolygon几何对象。
ST_GeometryFromWKB 输入WKB数据,返回对应的几何对象。
ST_pointFromWKB 输入Point的WKB数据,返回对应的Point几何对象。
ST_lineFromWKB 输入Line的WKB数据,返回对应的Line几何对象。
ST_polyFromWKB 输入Polygon的WKB数据,返回对应的Polygon几何对象。
ST_MPointFromWKB 输入MultiPoint的WKB数据,返回对应的MultiPoint几何对象。
ST_MLineFromWKB 输入MultiLine的WKB数据,返回对应的MultiLine几何对象。
ST_MPolyFromWKB 输入MultiPolygon的WKB数据,返回对应的MultiPolygon几何对象。
ST_GeometryFromGeoJson 输入GeoJson的字符串数据,返回对应的几何对象。
ST_GeometryFromJson 输入ESRI Geometry Object Json的字符串数据,返回对应的几何对象。
ST_asGeoJson 把几何对象转成GeoJson格式输出。
ST_asJson 把几何对象转成ESRI Geometry Object Json格式输出。
ST_GeometryFromEsriShape 输入ESRI Shape的二进制数据,返回对应的几何对象。

7. 函数定义与示例

  • ST_asText
    1. ST_asText(GEOMETRY) -> VARCHAR
    将Geometry数据转成WKT格式的字符串数据。

示例:

  1. select ST_asText(ST_LineFromText('LINESTRING (0 0, 1 1, 1 2)'));
  2. +----------------------------+
  3. | _col0 |
  4. +----------------------------+
  5. | LINESTRING (0 0, 1 1, 1 2) |
  6. +----------------------------+
  • ST_LineFromText
    1. ST_LineFromText(VARCHAR) -> GEOMETRY
    输入WKT格式的Line字符串数据,生成Line的Geometry数据。

示例:

  1. select ST_asText(ST_LineFromText('LINESTRING (0 0, 1 1, 1 2)'));
  2. +----------------------------+
  3. | _col0 |
  4. +----------------------------+
  5. | LINESTRING (0 0, 1 1, 1 2) |
  6. +----------------------------+
  • ST_Point
    1. ST_Point(DOUBLE, DOUBLE) -> GEOMETRY
    从坐标系的坐标值(X, Y),生成对应的Point的Geometry数据。

示例:

  1. select ST_asText(ST_Point(30.2741500000,120.1551500000));
  2. +----------------------------+
  3. | _col0 |
  4. +----------------------------+
  5. | POINT (30.27415 120.15515) |
  6. +----------------------------+
  • ST_Polygon
    1. ST_Polygon(VARCHAR) -> GEOMETRY
    输入WKT格式的Polygon字符串数据,生成Polygon的Geometry数据。

示例:

  1. select ST_asText(ST_Polygon('POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))'));
  2. +----------------------------------------------------------------+
  3. | _col0 |
  4. +----------------------------------------------------------------+
  5. | POLYGON ((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 1 2, 2 2, 2 1, 1 1)) |
  6. +----------------------------------------------------------------+
  • ST_Area
    1. ST_Area(Geometry) -> DOUBLE
    返回面或多面的面积。对于点、线,返回0.0。对于GeometryCollection,返回所有单个面积的和。

示例:

  1. select ST_Area(ST_Polygon('POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))'));
  2. +-------+
  3. | _col0 |
  4. +-------+
  5. | 15.0 |
  6. +-------+
  7. select ST_Area(ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'));
  8. +-------+
  9. | _col0 |
  10. +-------+
  11. | 16.0 |
  12. +-------+
  • ST_GeometryFromText

    1. ST_GeometryFromText(VARCHAR) -> GEOMETRY

    输入WKT格式的字符串数据,生成Geometry数据。

  • ST_Buffer

    1. ST_Buffer(GEOMETRY, DOUBLE) -> GEOMETRY

    <span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)">获取几何对象和距离,然后返回表示围绕源对象的缓冲区的几何对象。</span></span>

  • ST_Centroid

    1. ST_Centroid(GEOMETRY) -> GEOMETRY

    获取几何对象的中心点。

示例:

  1. select ST_asText(ST_Centroid(ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))')));
  2. +-----------------------+
  3. | _col0 |
  4. +-----------------------+
  5. | POINT (1.8125 1.8125) |
  6. +-----------------------+
  7. select ST_asText(ST_Centroid(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2)')));
  8. +---------------+
  9. | _col0 |
  10. +---------------+
  11. | POINT (0.5 1) |
  12. +---------------+
  13. select ST_asText(ST_Centroid(ST_GeometryFromText('POINT (0.5 1)')));
  14. +---------------+
  15. | _col0 |
  16. +---------------+
  17. | POINT (0.5 1) |
  18. +---------------+
  19. select ST_asText(ST_Centroid(ST_GeometryFromText('POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))')));
  20. +---------------------------------------------+
  21. | _col0 |
  22. +---------------------------------------------+
  23. | POINT (2.033333333333333 2.033333333333333) |
  24. +---------------------------------------------+
  • ST_CoordDim
    1. ST_CoordDim(GEOMETRY) -> BIGINT
    返回几何对象的坐标值维度。

示例:

  1. select ST_CoordDim(ST_GeometryFromText('POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))'));
  2. +-------+
  3. | _col0 |
  4. +-------+
  5. | 2 |
  6. +-------+
  7. select ST_CoordDim(ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'));
  8. +-------+
  9. | _col0 |
  10. +-------+
  11. | 2 |
  12. +-------+
  13. select st_coorddim(st_geometryfromtext('point Z (60.567222 -140.404 5959)'));
  14. +-------+
  15. | _col0 |
  16. +-------+
  17. | 3 |
  18. +-------+
  19. select st_coorddim(st_geometryfromtext('point M (60.567222 -140.404 5250)'));
  20. +-------+
  21. | _col0 |
  22. +-------+
  23. | 3 |
  24. +-------+
  25. select st_coorddim(st_geometryfromtext('point ZM (60.567222 -140.404 5959 5250)'));
  26. +-------+
  27. | _col0 |
  28. +-------+
  29. | 4 |
  30. +-------+
  • ST_Dimension
    1. ST_Dimension(GEOMETRY) -> BIGINT
    <span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)">用于返回几何对象的维度。在这种情况下,维度是指长度和宽度。例如,点既没有长度也没有宽度,所以其维度为 0;而线只有长度却没有宽度,因此其维度为 1。</span></span>

示例:

  1. select ST_Dimension(ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'));
  2. +-------+
  3. | _col0 |
  4. +-------+
  5. | 2 |
  6. +-------+
  • ST_IsClosed
    1. ST_IsClosed(GEOMETRY) -> BOOLEAN
    判断Line或者MultiLine是否闭合。

示例:

  1. select ST_IsClosed(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2)'));
  2. +-------+
  3. | _col0 |
  4. +-------+
  5. | 0 |
  6. +-------+
  7. select ST_IsClosed(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2, 0 0)'));
  8. +-------+
  9. | _col0 |
  10. +-------+
  11. | 1 |
  12. +-------+
  13. select ST_IsClosed(ST_GeometryFromText('MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))'));
  14. +-------+
  15. | _col0 |
  16. +-------+
  17. | 0 |
  18. +-------+
  • ST_IsEmpty
    1. ST_IsEmpty(GEOMETRY) -> BOOLEAN
    判断几何对象是否为空。

示例:

  1. select ST_IsEmpty(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2, 0 0)'));
  2. +-------+
  3. | _col0 |
  4. +-------+
  5. | 0 |
  6. +-------+
  7. select ST_IsEmpty(null);
  8. +-------+
  9. | _col0 |
  10. +-------+
  11. | NULL |
  12. +-------+
  13. SELECT ST_IsEmpty(ST_GeometryFromText('GEOMETRYCOLLECTION EMPTY'));
  14. +-------+
  15. | _col0 |
  16. +-------+
  17. | 1 |
  18. +-------+
  19. SELECT ST_IsEmpty(ST_GeometryFromText('POLYGON EMPTY'));
  20. +-------+
  21. | _col0 |
  22. +-------+
  23. | 1 |
  24. +-------+
  • ST_Length
    1. ST_Length(GEOMETRY) -> DOUBLE
    计算Line或者MultiLine的长度。

示例:

  1. SELECT ST_Length(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2, 0 0)'));
  2. +-------------------+
  3. | _col0 |
  4. +-------------------+
  5. | 4.650281539872885 |
  6. +-------------------+
  7. SELECT ST_Length(ST_GeometryFromText('MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))'));
  8. +-------------------+
  9. | _col0 |
  10. +-------------------+
  11. | 6.656854249492381 |
  12. +-------------------+
  • ST_XMax
    1. ST_XMax(GEOMETRY) -> DOUBLE
    返回几何对象在坐标系中的最大X坐标值。

示例:

  1. SELECT ST_XMax(ST_GeometryFromText('MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))'));
  2. +-------+
  3. | _col0 |
  4. +-------+
  5. | 5.0 |
  6. +-------+
  7. SELECT ST_XMax(ST_GeometryFromText('POINT (0.5 1)'));
  8. +-------+
  9. | _col0 |
  10. +-------+
  11. | 0.5 |
  12. +-------+
  • ST_XMin
    1. ST_XMin(GEOMETRY) -> DOUBLE
    返回几何对象在坐标系中的最小X坐标值。

示例:

  1. SELECT ST_XMin(ST_GeometryFromText('MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))'));
  2. +-------+
  3. | _col0 |
  4. +-------+
  5. | 0.0 |
  6. +-------+
  7. SELECT ST_XMin(ST_GeometryFromText('POINT (0.5 1)'));
  8. +-------+
  9. | _col0 |
  10. +-------+
  11. | 0.5 |
  12. +-------+
  • ST_YMax
    1. ST_YMax(GEOMETRY) -> DOUBLE
    返回几何对象在坐标系中的最大Y坐标值。

示例:

  1. SELECT ST_YMax(ST_GeometryFromText('MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))'));
  2. +-------+
  3. | _col0 |
  4. +-------+
  5. | 4.0 |
  6. +-------+
  7. SELECT ST_YMax(ST_GeometryFromText('POINT (0.5 1)'));
  8. +-------+
  9. | _col0 |
  10. +-------+
  11. | 1.0 |
  12. +-------+
  • ST_YMin
    1. ST_YMin(GEOMETRY) -> DOUBLE
    返回几何对象在坐标系中的最小Y坐标值。

示例:

  1. SELECT ST_YMin(ST_GeometryFromText('MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))'));
  2. +-------+
  3. | _col0 |
  4. +-------+
  5. | 0.0 |
  6. +-------+
  7. SELECT ST_YMin(ST_GeometryFromText('POINT (0.5 1)'));
  8. +-------+
  9. | _col0 |
  10. +-------+
  11. | 1.0 |
  12. +-------+
  • ST_NumInteriorRing
    1. ST_NumInteriorRing(GEOMETRY) -> BIGINT
    以Polygon作为输入参数,并返回其内部环数。

示例:

  1. SELECT ST_NumInteriorRing(ST_GeometryFromText('POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))'));
  2. +-------+
  3. | _col0 |
  4. +-------+
  5. | 1 |
  6. +-------+
  • ST_NumPoints
    1. ST_NumPoints(GEOMETRY) -> BIGINT
    <span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)">用于返回几何对象中的点(折点)数。</span></span>

示例:

  1. SELECT ST_NumPoints(ST_GeometryFromText('POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))'));
  2. +-------+
  3. | _col0 |
  4. +-------+
  5. | 8 |
  6. +-------+
  7. SELECT ST_NumPoints(ST_GeometryFromText('POLYGON EMPTY'));
  8. +-------+
  9. | _col0 |
  10. +-------+
  11. | 0 |
  12. +-------+
  13. SELECT ST_NumPoints(ST_GeometryFromText('POINT (0.5 1)'));
  14. +-------+
  15. | _col0 |
  16. +-------+
  17. | 1 |
  18. +-------+
  • ST_IsRing
    1. ST_IsRing(GEOMETRY) -> BOOLEAN
    <span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)">以Line作为输入参数,判断是否是环(如Line是闭合的)。</span></span>

示例:

  1. select ST_IsRing(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2)'));
  2. +-------+
  3. | _col0 |
  4. +-------+
  5. | 0 |
  6. +-------+
  7. select ST_IsRing(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2, 0 0)'));
  8. +-------+
  9. | _col0 |
  10. +-------+
  11. | 1 |
  12. +-------+
  • ST_StartPoint
    1. ST_StartPoint(GEOMETRY) -> GEOMETRY
    <span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)">用于返回Line的第一个点。</span></span>

示例:

  1. select ST_asText(ST_StartPoint(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2, 0 0)')));
  2. +-------------+
  3. | _col0 |
  4. +-------------+
  5. | POINT (0 0) |
  6. +-------------+
  • ST_EndPoint
    1. ST_EndPoint(GEOMETRY) -> GEOMETRY
    <span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)">用于返回Line的最后一个点。</span></span>

示例:

  1. select ST_asText(ST_EndPoint(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2)')));
  2. +-------------+
  3. | _col0 |
  4. +-------------+
  5. | POINT (1 2) |
  6. +-------------+
  • ST_X
    1. ST_X(GEOMETRY) -> DOUBLE
    返回Point的X坐标。

示例:

  1. select ST_X(ST_GeometryFromText('POINT (0.5 1)'));
  2. +-------+
  3. | _col0 |
  4. +-------+
  5. | 0.5 |
  6. +-------+
  • ST_Y
    1. ST_Y(GEOMETRY) -> DOUBLE
    返回Point的Y坐标。

示例:

  1. select ST_Y(ST_GeometryFromText('POINT (0.5 1)'));
  2. +-------+
  3. | _col0 |
  4. +-------+
  5. | 1.0 |
  6. +-------+
  • ST_Boundary
    1. ST_Boundary(GEOMETRY) -> GEOMETRY
    输入一个几何对象,然后以几何对象形式返回其组合边界。

示例:

  1. select ST_asText(ST_Boundary(ST_GeometryFromText('POINT (0.5 1)')));
  2. +-------------+
  3. | _col0 |
  4. +-------------+
  5. | POINT EMPTY |
  6. +-------------+
  7. select ST_asText(ST_Boundary(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2)')));
  8. +---------------------------+
  9. | _col0 |
  10. +---------------------------+
  11. | MULTIPOINT ((0 0), (1 2)) |
  12. +---------------------------+
  13. select ST_asText(ST_Boundary(ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))')));
  14. +-------------------------------------------------------------------------------------------------------------+
  15. | _col0 |
  16. +-------------------------------------------------------------------------------------------------------------+
  17. | MULTILINESTRING ((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 1 2, 2 2, 2 1, 1 1), (-1 -1, -2 -1, -2 -2, -1 -2, -1 -1)) |
  18. +-------------------------------------------------------------------------------------------------------------+
  • ST_Envelope
    1. ST_Envelope(GEOMETRY) -> GEOMETRY
    <span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)">以多边形的形式返回几何对象的最小边界框。</span></span>

示例:

  1. select ST_asText(ST_Envelope(ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))')));
  2. +-------------------------------------------+
  3. | _col0 |
  4. +-------------------------------------------+
  5. | POLYGON ((-2 -2, 4 -2, 4 4, -2 4, -2 -2)) |
  6. +-------------------------------------------+
  • ST_Difference
    1. ST_Difference(GEOMETRY, GEOMETRY) -> GEOMETRY
    输入两个几何对象,然后返回表示两个源对象之差的几何对象。

示例:

  1. select ST_asText(ST_Difference(
  2. ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
  3. ST_GeometryFromText('MULTILINESTRING ((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 1 2, 2 2, 2 1, 1 1), (-1 -1, -2 -1, -2 -2, -1 -2, -1 -1))')));
  4. +--------------------------------------------------------------------------------------------------------------+
  5. | _col0 |
  6. +--------------------------------------------------------------------------------------------------------------+
  7. | MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 1 2, 2 2, 2 1, 1 1)), ((-1 -1, -2 -1, -2 -2, -1 -2, -1 -1))) |
  8. +--------------------------------------------------------------------------------------------------------------+
  9. select ST_asText(ST_Difference(
  10. ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
  11. ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))')));
  12. +--------------------+
  13. | _col0 |
  14. +--------------------+
  15. | MULTIPOLYGON EMPTY |
  16. +--------------------+
  • ST_Distance
    1. ST_Distance(GEOMETRY, GEOMETRY) -> DOUBLE
    用于返回两个几何对象之间的距离。这一距离是两个几何对象的最近折点之间的距离。

示例:

  1. select ST_Distance(
  2. ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
  3. ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'));
  4. +-------+
  5. | _col0 |
  6. +-------+
  7. | 0.0 |
  8. +-------+
  9. select ST_Distance(
  10. ST_GeometryFromText('POINT(0 0)'),
  11. ST_GeometryFromText('POINT(1 1)'));
  12. +--------------------+
  13. | _col0 |
  14. +--------------------+
  15. | 1.4142135623730951 |
  16. +--------------------+
  • ST_ExteriorRing
    1. ST_ExteriorRing(GEOMETRY) -> GEOMETRY
    <span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)">以Line形式返回面的外部环。</span></span>

示例:

  1. select ST_asText(ST_ExteriorRing(ST_GeometryFromText('POLYGON((0 0,4 0,4 4,0 4,0 0),(1 1, 2 1, 2 2, 1 2,1 1))')));
  2. +--------------------------------------+
  3. | _col0 |
  4. +--------------------------------------+
  5. | LINESTRING (0 0, 4 0, 4 4, 0 4, 0 0) |
  6. +--------------------------------------+
  • ST_Intersection
    1. ST_Intersection(GEOMETRY, GEOMETRY) -> GEOMETRY
    <span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)">以两个几何对象作为输入参数,然后以二维几何对象的形式返回交集。</span></span>

示例:

  1. select ST_asText(ST_Intersection(
  2. ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
  3. ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))')));
  4. +--------------------------------------------------------------------------------------------------------------+
  5. | _col0 |
  6. +--------------------------------------------------------------------------------------------------------------+
  7. | MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 1 2, 2 2, 2 1, 1 1)), ((-1 -1, -2 -1, -2 -2, -1 -2, -1 -1))) |
  8. +--------------------------------------------------------------------------------------------------------------+
  9. select ST_asText(ST_Intersection(
  10. ST_GeometryFromText('POINT(0 0)'),
  11. ST_GeometryFromText('POINT(1 1)')));
  12. +--------------------+
  13. | _col0 |
  14. +--------------------+
  15. | MULTIPOLYGON EMPTY |
  16. +--------------------+
  • ST_SymDifference
    1. ST_SymDifference(GEOMETRY, GEOMETRY) -> GEOMETRY
    <span data-type="color" style="color:rgb(0, 0, 0)">返回表示两个几何对象间的点集对称差异的几何值对象。</span>

示例:

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

<span data-type="color" style="color:rgb(0, 0, 0)">下图的阴影部分显示了对称差异的结果。对称差异为包括两个表面的多表面图形:其中一个表面包含位于正方形之内、圆形之外的所有点,另外一个表面包含位于圆形之内、正方形之外的所有点。</span>

image.png | left | 223x166

  • ST_Contains
    1. ST_Contains(GEOMETRY, GEOMETRY) -> BOOLEAN
    输入<span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)">两个几何对象,判断第一个对象是否完全包含第二个对象。</span></span>

示例:

  1. select ST_Contains(
  2. ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
  3. ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'));
  4. +-------+
  5. | _col0 |
  6. +-------+
  7. | 1 |
  8. +-------+
  9. select ST_Contains(
  10. ST_GeometryFromText('POINT(0 0)'),
  11. ST_GeometryFromText('POINT(1 1)'));
  12. +-------+
  13. | _col0 |
  14. +-------+
  15. | 0 |
  16. +-------+
  • ST_Crosses
    1. ST_Crosses(GEOMETRY, GEOMETRY) -> BOOLEAN
    <span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)">以两个几何对象作为输入,如果这两个对象的交集生成的几何对象的维度小于两个源对象中的最大维度,则返回 1。交集对象所包含的点必须在两个源几何的内部,并且不等于其中任何一个源对象。否则,返回 0。</span></span>

示例:

  1. select ST_Crosses(
  2. ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
  3. ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'));
  4. +-------+
  5. | _col0 |
  6. +-------+
  7. | 0 |
  8. +-------+
  9. select ST_Crosses(
  10. ST_GeometryFromText('LINESTRING(0 0, 2 2)'),
  11. ST_GeometryFromText('LINESTRING(0 2, 2 0)'));
  12. +-------+
  13. | _col0 |
  14. +-------+
  15. | 1 |
  16. +-------+
  • ST_Disjoint
    1. ST_Disjoint(GEOMETRY, GEOMETRY) -> BOOLEAN
    输入<span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)">两个几何对象,判断两个几何对象的交集是否为空集。</span></span>

示例:

  1. select ST_Disjoint(
  2. ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
  3. ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'));
  4. +-------+
  5. | _col0 |
  6. +-------+
  7. | 0 |
  8. +-------+
  9. select ST_Disjoint(
  10. ST_GeometryFromText('LINESTRING(0 0, 2 2)'),
  11. ST_GeometryFromText('LINESTRING(0 2, 2 4)'));
  12. +-------+
  13. | _col0 |
  14. +-------+
  15. | 1 |
  16. +-------+
  • ST_Equals
    1. ST_Equals(GEOMETRY, GEOMETRY) -> BOOLEAN
    判断<span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)">两个几何对象是否完全相同。</span></span>

示例:

  1. select ST_Equals(
  2. ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
  3. ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'));
  4. +-------+
  5. | _col0 |
  6. +-------+
  7. | 1 |
  8. +-------+
  9. select ST_Equals(
  10. ST_GeometryFromText('LINESTRING(0 0, 2 2)'),
  11. ST_GeometryFromText('LINESTRING(0 2, 2 4)'));
  12. +-------+
  13. | _col0 |
  14. +-------+
  15. | 0 |
  16. +-------+
  • ST_Intersects
    1. ST_Intersects(GEOMETRY, GEOMETRY) -> BOOLEAN
    判断<span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)">两个几何对象的交集是否不生成空集。</span></span>

示例:

  1. select ST_Intersects(
  2. ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
  3. ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'));
  4. +-------+
  5. | _col0 |
  6. +-------+
  7. | 1 |
  8. +-------+
  9. select ST_Intersects(
  10. ST_GeometryFromText('LINESTRING(0 0, 2 2)'),
  11. ST_GeometryFromText('LINESTRING(0 2, 2 4)'));
  12. +-------+
  13. | _col0 |
  14. +-------+
  15. | 0 |
  16. +-------+
  • ST_Overlaps
    1. ST_Overlaps(GEOMETRY, GEOMETRY) -> BOOLEAN
    判断<span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)">两个几何对象的交集生成的几何对象是否维度相同但不等于任一源对象。</span></span>

示例:

  1. select ST_Overlaps(
  2. ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
  3. ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'));
  4. +-------+
  5. | _col0 |
  6. +-------+
  7. | 0 |
  8. +-------+
  9. select ST_Overlaps(
  10. ST_GeometryFromText('LINESTRING(1 1, 2 2)'),
  11. ST_GeometryFromText('LINESTRING(0 0, 1.5 1.5)'));
  12. +-------+
  13. | _col0 |
  14. +-------+
  15. | 1 |
  16. +-------+
  • ST_Relate
    1. ST_Relate(GEOMETRY, GEOMETRY, VARCHAR) -> BOOLEAN
    <span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)">比较两个几何对象,判断是否满足“DE-9IM”模式(</span></span>https://en.wikipedia.org/wiki/DE-9IM<span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)">)矩阵字符串指定的条件。</span></span>

示例:

  1. select ST_Relate(
  2. ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
  3. ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
  4. '102101FF2');
  5. +-------+
  6. | _col0 |
  7. +-------+
  8. | 0 |
  9. +-------+
  10. select ST_Relate(
  11. ST_GeometryFromText('LINESTRING(1 1, 2 2)'),
  12. ST_GeometryFromText('LINESTRING(0 0, 1.5 1.5)'),
  13. '1*1***1**');
  14. +-------+
  15. | _col0 |
  16. +-------+
  17. | 1 |
  18. +-------+
  • ST_Touches
    1. ST_Touches(GEOMETRY, GEOMETRY) -> BOOLEAN
    判断<span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)">两个几何对象的公共点是否都不与两个几何对象的内部相交。</span></span>

示例:

  1. select ST_Touches(
  2. ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
  3. ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'));
  4. +-------+
  5. | _col0 |
  6. +-------+
  7. | 0 |
  8. +-------+
  9. select ST_Touches(
  10. ST_GeometryFromText('LINESTRING(1 1, 2 2)'),
  11. ST_GeometryFromText('LINESTRING(0 0, 1 1)'));
  12. +-------+
  13. | _col0 |
  14. +-------+
  15. | 1 |
  16. +-------+
  • ST_Within
    1. ST_Within(GEOMETRY, GEOMETRY) -> BOOLEAN
    判断<span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)">第一个几何对象是否完全位于第二个几何对象的范围内。</span></span>

示例:

  1. select ST_Within(
  2. ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'),
  3. ST_GeometryFromText('MULTIPOLYGON (((0 0, 4 0, 4 4, 0 4, 0 0), (1 1, 2 1, 2 2, 1 2, 1 1)), ((-1 -1, -1 -2, -2 -2, -2 -1, -1 -1)))'));
  4. +-------+
  5. | _col0 |
  6. +-------+
  7. | 1 |
  8. +-------+
  9. select ST_Within(
  10. ST_GeometryFromText('LINESTRING(1 1, 2 2)'),
  11. ST_GeometryFromText('LINESTRING(0 0, 1 1)'));
  12. +-------+
  13. | _col0 |
  14. +-------+
  15. | 0 |
  16. +-------+
  • ST_asBinary

    1. ST_asBinary(GEOMETRY) -> VARBINARY

    输入<span data-type="color" style="color:rgb(77, 77, 77)"><span data-type="background" style="background-color:rgb(254, 254, 254)">一个几何对象,然后返回其可识别的二进制WKB数据。</span></span>

  • ST_GeometryFromWKBHexString

    1. ST_GeometryFromWKBHexString(VARCHAR) -> GEOMETRY

    输入WKB的HEX字符串数据,返回对应的几何对象。

示例:

  1. select ST_ASTEXT(ST_GeometryFromWKBHexString('000000000140000000000000004010000000000000'));
  2. +-------------+
  3. | _col0 |
  4. +-------------+
  5. | POINT (2 4) |
  6. +-------------+
  • ST_pointFromWKBHexString
    1. ST_pointFromWKBHexString(VARCHAR) -> GEOMETRY
    输入Point的WKB的HEX字符串数据,返回对应的Point几何对象。

示例:

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

    1. ST_lineFromWKBHexString(VARCHAR) -> GEOMETRY

    输入Line的WKB的HEX字符串数据,返回对应的Line几何对象。

  • ST_polyFromWKBHexString

    1. ST_polyFromWKBHexString(VARCHAR) -> GEOMETRY

    输入Polygon的WKB的HEX字符串数据,返回对应的Polygon几何对象。

  • ST_MPointFromWKBHexString

    1. ST_MPointFromWKBHexString(VARCHAR) -> GEOMETRY

    输入MultiPoint的WKB的HEX字符串数据,返回对应的MultiPoint几何对象。

示例:

  1. SELECT ST_asText(ST_MPointFromWKBHexString('0104000000020000000101000000000000000000000000000000000000000101000000000000000000F03F0000000000000040'));
  2. +---------------------------+
  3. | _col0 |
  4. +---------------------------+
  5. | MULTIPOINT ((0 0), (1 2)) |
  6. +---------------------------+
  • ST_MLineFromWKBHexString

    1. ST_MLineFromWKBHexString(VARCHAR) -> GEOMETRY

    输入MultiLine的WKB的HEX字符串数据,返回对应的MultiLine几何对象。

  • ST_MPolyFromWKBHexString

    1. ST_MPolyFromWKBHexString(VARCHAR) -> GEOMETRY

    输入MultiPolygon的WKB的HEX字符串数据,返回对应的MultiPolygon几何对象。

  • ST_GeometryFromWKB

    1. ST_GeometryFromWKB(VARBINARY) -> GEOMETRY

    输入WKB数据,返回对应的几何对象。

示例:

  1. select ST_ASTEXT(ST_GeometryFromWKB(from_hex('000000000140000000000000004010000000000000')));
  2. +-------------+
  3. | _col0 |
  4. +-------------+
  5. | POINT (2 4) |
  6. +-------------+
  • ST_pointFromWKB
    1. ST_pointFromWKB(VARBINARY) -> GEOMETRY
    输入Point的WKB数据,返回对应的Point几何对象。

示例:

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

    1. ST_lineFromWKB(VARBINARY) -> GEOMETRY

    输入Line的WKB数据,返回对应的Line几何对象。

  • ST_polyFromWKB

    1. ST_polyFromWKB(VARBINARY) -> GEOMETRY

    输入Polygon的WKB数据,返回对应的Polygon几何对象。

  • ST_MPointFromWKB

    1. ST_MPointFromWKB(VARBINARY) -> GEOMETRY

    输入MultiPoint的WKB数据,返回对应的MultiPoint几何对象。

示例:

  1. SELECT ST_asText(ST_MPointFromWKB(from_hex('0104000000020000000101000000000000000000000000000000000000000101000000000000000000F03F0000000000000040')));
  2. +---------------------------+
  3. | _col0 |
  4. +---------------------------+
  5. | MULTIPOINT ((0 0), (1 2)) |
  6. +---------------------------+
  • ST_MLineFromWKB

    1. ST_MLineFromWKB(VARBINARY) -> GEOMETRY

    输入MultiLine的WKB数据,返回对应的MultiLine几何对象。

  • ST_MPolyFromWKB

    1. ST_MPolyFromWKB(VARBINARY) -> GEOMETRY

    输入MultiPolygon的WKB数据,返回对应的MultiPolygon几何对象。

  • ST_GeometryFromGeoJson

    1. ST_GeometryFromGeoJson(VARCHAR) -> GEOMETRY

    输入GeoJson的字符串数据,返回对应的几何对象。

  • ST_GeometryFromJson

    1. ST_GeometryFromJson(VARCHAR) -> GEOMETRY

    输入ESRI Geometry Object Json的字符串数据,返回对应的几何对象。

  • ST_asGeoJson

    1. ST_asGeoJson(GEOMETRY) -> VARCHAR

    把几何对象转成GeoJson格式输出。

示例:

  1. SELECT ST_asGeoJson(ST_GeometryFromText('MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))'));
  2. +-----------------------------------------------------------------------------------------------+
  3. | _col0 |
  4. +-----------------------------------------------------------------------------------------------+
  5. | {"type":"MultiLineString","coordinates":[[[0,0],[1,1],[1,2]],[[2,3],[3,2],[5,4]]],"crs":null} |
  6. +-----------------------------------------------------------------------------------------------+
  • ST_asJson
    1. ST_asJson(GEOMETRY) -> VARCHAR
    把几何对象转成ESRI Geometry Object Json格式输出。

示例:

  1. SELECT ST_asJson(ST_GeometryFromText('MULTILINESTRING ((0 0, 1 1, 1 2), (2 3, 3 2, 5 4))'));
  2. +-----------------------------------------------------+
  3. | _col0 |
  4. +-----------------------------------------------------+
  5. | {"paths":[[[0,0],[1,1],[1,2]],[[2,3],[3,2],[5,4]]]} |
  6. +-----------------------------------------------------+
  • ST_GeometryFromEsriShape
    1. ST_GeometryFromEsriShape(VARBINARY) -> GEOMETRY
    输入ESRI Shape的二进制数据,返回对应的几何对象。

示例:

  1. SELECT california_counties.name,
  2. COUNT(*) cnt
  3. FROM california_counties
  4. CROSS JOIN earthquakes
  5. WHERE ST_CONTAINS (ST_GeometryFromesrishape(california_counties.boundaryshape), ST_POINT(earthquakes.longitude, earthquakes.latitude))
  6. GROUP BY california_counties.name
  7. ORDER BY cnt DESC, california_counties.name;
  8. +-----------------+------+
  9. | name | cnt |
  10. +-----------------+------+
  11. | San Benito | 8 |
  12. | San Bernardino | 7 |
  13. | Riverside | 6 |
  14. | Inyo | 5 |
  15. | Imperial | 3 |
  16. | San Diego | 2 |
  17. | Kern | 1 |
  18. | Kings | 1 |
  19. | Monterey | 1 |
  20. | San Luis Obispo | 1 |
  21. | Santa Clara | 1 |
  22. | Ventura | 1 |
  23. +-----------------+------+