Search

# 地理空间函数

``city_adcode(longitude, latitude)``
• 函数说明：根据经度`longitude`和纬度`latitude`值查询所属城市ADCode。

如果`longitude``latitude`任一值非法或者为NULL，返回结果为空字符串。

• 返回值类型：STRING。

• 示例：

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

``````province_adcode(longitude, latitude)
``````
• 函数说明：根据经度`longitude`和纬度`latitude`值查询所属省份的ADCode。

如果`longitude``latitude`任一值非法或者为NULL，返回结果为空字符串。

• 返回值类型：STRING。

• 示例：

``````        select province_adcode(118.0, 28.0);
+---------------------------------+
|             360000              |
``````

## 简介

• WKT

• WKB

• GeoJson

• ESRI Geometry Object Json

• ESRI Shape

DLA采用4326坐标系标准，EPSG 4326使用经纬度坐标，属于地理坐标系。GPS采用的就是这个坐标系。

## 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))`

## 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

## 函数列表

Name

Description

ST_asText

ST_LineFromText

ST_Point

ST_Polygon

ST_Area

ST_GeometryFromText

ST_Buffer

ST_Centroid

ST_CoordDim

ST_Dimension

ST_IsClosed

ST_IsEmpty

ST_Length

ST_XMax

ST_XMin

ST_YMax

ST_YMin

ST_NumInteriorRing

ST_NumPoints

ST_IsRing

ST_StartPoint

ST_EndPoint

ST_X

ST_Y

ST_Boundary

ST_Envelope

ST_Difference

ST_Distance

ST_ExteriorRing

ST_Intersection

ST_SymDifference

ST_Contains

ST_Crosses

ST_Disjoint

ST_Equals

ST_Intersects

ST_Overlaps

ST_Relate

ST_Touches

ST_Within

ST_asBinary

ST_GeometryFromWKBHexString

ST_pointFromWKBHexString

ST_lineFromWKBHexString

ST_polyFromWKBHexString

ST_MPointFromWKBHexString

ST_MLineFromWKBHexString

ST_MPolyFromWKBHexString

ST_GeometryFromWKB

ST_pointFromWKB

ST_lineFromWKB

ST_polyFromWKB

ST_MPointFromWKB

ST_MLineFromWKB

ST_MPolyFromWKB

ST_GeometryFromGeoJson

ST_GeometryFromJson

ST_asGeoJson

ST_asJson

ST_GeometryFromEsriShape

## 函数定义与示例

### ST_asText

``ST_asText(GEOMETRY)  ``
• 函数说明：将Geometry数据转成WKT格式的字符串数据。

• 返回值类型：VARCHAR。

• 示例：

``````        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)  ``
• 函数说明：输入WKT格式的Line字符串数据，生成Line的Geometry数据。

• 返回值类型：GEOMETRY。

• 示例：

``````        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)  ``
• 函数说明：从坐标系的坐标值（X, Y），生成对应的Point的Geometry数据。

• 返回值类型：GEOMETRY。

• 示例：

``````        select ST_asText(ST_Point(30.2741500000,120.1551500000));

+----------------------------+
| _col0                      |
+----------------------------+
| POINT (30.27415 120.15515) |
+----------------------------+``````

### ST_Polygon

``ST_Polygon(VARCHAR)  ``
• 函数说明：输入WKT格式的Polygon字符串数据，生成Polygon的Geometry数据。

• 返回值类型：GEOMETRY。

• 示例：

``````        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)  ``
• 函数说明：返回面或多面的面积。对于点、线，返回0.0。对于GeometryCollection，返回所有单个面积的和。

• 返回值类型：DOUBLE。

• 示例：

``````        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)
``````
• 函数说明：输入WKT格式的字符串数据，生成Geometry数据。

• 返回值类型：GEOMETRY。

### ST_Buffer

``````ST_Buffer(GEOMETRY, DOUBLE)
``````
• 函数说明：获取几何对象和距离，然后返回表示围绕源对象的缓冲区的几何对象。

• 返回值类型：GEOMETRY。

### ST_Centroid

``````ST_Centroid(GEOMETRY)
``````
• 函数说明：获取几何对象的中心点。

• 返回值类型：GEOMETRY。

• 示例：

``````        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)
``````
• 函数说明：返回几何对象的坐标值维度。

• 返回值类型：BIGINT。

• 示例：

``````        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)
``````
• 函数说明：用于返回几何对象的维度。在这种情况下，维度是指长度和宽度。例如，点既没有长度也没有宽度，所以其维度为 0；而线只有长度却没有宽度，因此其维度为 1。

• 返回值类型：BIGINT。

• 示例：

``````        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)
``````
• 函数说明：判断Line或者MultiLine是否闭合。

• 返回值类型：BOOLEAN。

• 示例：

``````        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)
``````
• 函数说明：判断几何对象是否为空。

• 返回值类型：BOOLEAN。

• 示例：

``````        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)
``````
• 函数说明：计算Line或者MultiLine的长度。

• 返回值类型：DOUBLE。

• 示例：

``````        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)
``````
• 函数说明：返回几何对象在坐标系中的最大X坐标值。

• 返回值类型：DOUBLE。

• 示例：

``````        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)
``````
• 函数说明：返回几何对象在坐标系中的最小X坐标值。

• 返回值类型：DOUBLE。

• 示例：

``````        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)
``````
• 函数说明：返回几何对象在坐标系中的最大Y坐标值。

• 返回值类型：DOUBLE。

• 示例：

``````        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)
``````
• 函数说明：返回几何对象在坐标系中的最小Y坐标值。

• 返回值类型：DOUBLE。

• 示例：

``````        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)
``````
• 函数说明：以Polygon作为输入参数，并返回其内部环数。

• 返回值类型：BIGINT。

• 示例：

``````        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)
``````
• 函数说明：用于返回几何对象中的点（折点）数。

• 返回值类型：BIGINT。

• 示例：

``````        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)
``````
• 函数说明：以Line作为输入参数，判断是否是环（如Line是闭合的）。

• 返回值类型：BOOLEAN。

• 示例：

``````        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)
``````
• 函数说明：用于返回Line的第一个点。

• 返回值类型：GEOMETRY。

• 示例：

``````        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)
``````
• 函数说明：用于返回Line的最后一个点。

• 返回值类型：GEOMETRY。

• 示例：

``````        select ST_asText(ST_EndPoint(ST_GeometryFromText('LINESTRING (0 0, 1 1, 1 2)')));

+-------------+
| _col0       |
+-------------+
| POINT (1 2) |
+-------------+
``````

### ST_X

``ST_X(GEOMETRY)  ``
• 函数说明：返回Point的X坐标。

• 返回值类型：DOUBLE。

• 示例：

``````        select ST_X(ST_GeometryFromText('POINT (0.5 1)'));

+-------+
| _col0 |
+-------+
|   0.5 |
+-------+
``````

### ST_Y

``````ST_Y(GEOMETRY)
``````
• 函数说明：返回Point的Y坐标。

• 返回值类型：DOUBLE。

• 示例：

``````        select ST_Y(ST_GeometryFromText('POINT (0.5 1)'));

+-------+
| _col0 |
+-------+
|   1.0 |
+-------+
``````

### ST_Boundary

``````ST_Boundary(GEOMETRY)
``````
• 函数说明：输入一个几何对象，然后以几何对象形式返回其组合边界。

• 返回值类型：GEOMETRY。

• 示例：

``````        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)
``````
• 函数说明：以多边形的形式返回几何对象的最小边界框。

• 返回值类型：GEOMETRY。

• 示例：

``````        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)
``````
• 函数说明：输入两个几何对象，然后返回表示两个源对象之差的几何对象。

• 返回值类型：GEOMETRY。

• 示例：

``````        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)
``````
• 函数说明：用于返回两个几何对象之间的距离。这一距离是两个几何对象的最近折点之间的距离。

• 返回值类型：DOUBLE。

• 示例：

``````        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)
``````
• 函数说明：以Line形式返回面的外部环。

• 返回值类型：GEOMETRY。

• 示例：

``````        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)
``````
• 函数说明：以两个几何对象作为输入参数，然后以二维几何对象的形式返回交集。

• 返回值类型：GEOMETRY。

• 示例：

``````        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)
``````
• 函数说明：返回表示两个几何对象间的点集对称差异的几何值对象。

• 返回值类型：GEOMETRY。

• 示例：

``````        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)) |
+---------------------------+
``````

下图的阴影部分显示了对称差异的结果。对称差异为包括两个表面的多表面图形：其中一个表面包含位于正方形之内、圆形之外的所有点，另外一个表面包含位于圆形之内、正方形之外的所有点。

### ST_Contains

``````ST_Contains(GEOMETRY, GEOMETRY)
``````
• 函数说明：输入两个几何对象，判断第一个对象是否完全包含第二个对象。

• 返回值类型：BOOLEAN。

• 示例：

``````        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)
``````
• 函数说明：以两个几何对象作为输入，如果这两个对象的交集生成的几何对象的维度小于两个源对象中的最大维度，则返回 1。交集对象所包含的点必须在两个源几何的内部，并且不等于其中任何一个源对象。否则，返回 0。

• 返回值类型：BOOLEAN。

• 示例：

``````        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)
``````
• 函数说明：输入两个几何对象，判断两个几何对象的交集是否为空集。

• 返回值类型：BOOLEAN。

• 示例：

``````        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)
``````
• 函数说明：判断两个几何对象是否完全相同。

• 返回值类型：BOOLEAN。

• 示例：

``````        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)
``````
• 函数说明：判断两个几何对象的交集是否不生成空集。

• 返回值类型：BOOLEAN。

• 示例：

``````        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)
``````
• 函数说明：判断两个几何对象的交集生成的几何对象是否维度相同但不等于任一源对象。

• 返回值类型：BOOLEAN。

• 示例：

``````        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)
``````
• 函数说明：比较两个几何对象，判断是否满足DE-9IM模式矩阵字符串指定的条件。

• 返回值类型：BOOLEAN。

• 示例：

``````        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)
``````
• 函数说明：判断两个几何对象的公共点是否都不与两个几何对象的内部相交。

• 返回值类型：BOOLEAN。

• 示例：

``````        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)
``````
• 函数说明：判断第一个几何对象是否完全位于第二个几何对象的范围内。

• 返回值类型：BOOLEAN。

• 示例：

``````        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)
``````
• 函数说明：输入一个几何对象，然后返回其可识别的二进制WKB数据。

• 返回值类型：VARBINARY。

### ST_GeometryFromWKBHexString

``````ST_GeometryFromWKBHexString(VARCHAR)
``````
• 函数说明：输入WKB的HEX字符串数据，返回对应的几何对象。

• 返回值类型：GEOMETRY。

• 示例：

``````        select ST_ASTEXT(ST_GeometryFromWKBHexString('000000000140000000000000004010000000000000'));

+-------------+
| _col0       |
+-------------+
| POINT (2 4) |
+-------------+
``````

### ST_pointFromWKBHexString

``````ST_pointFromWKBHexString(VARCHAR)
``````
• 函数说明：输入Point的WKB的HEX字符串数据，返回对应的Point几何对象。

• 返回值类型：GEOMETRY。

• 示例：

``````        select ST_ASTEXT(ST_pointFromWKBHexString('000000000140000000000000004010000000000000'));

+-------------+
| _col0       |
+-------------+
| POINT (2 4) |
+-------------+
``````

### ST_lineFromWKBHexString

``````ST_lineFromWKBHexString(VARCHAR)
``````
• 函数说明：输入Line的WKB的HEX字符串数据，返回对应的Line几何对象。

• 返回值类型：GEOMETRY。

### ST_polyFromWKBHexString

``````ST_polyFromWKBHexString(VARCHAR)
``````
• 函数说明：输入Polygon的WKB的HEX字符串数据，返回对应的Polygon几何对象。

• 返回值类型：GEOMETRY。

### ST_MPointFromWKBHexString

``````ST_MPointFromWKBHexString(VARCHAR)
``````
• 函数说明：输入MultiPoint的WKB的HEX字符串数据，返回对应的MultiPoint几何对象。

• 返回值类型：GEOMETRY。

### ST_MLineFromWKBHexString

``````ST_MLineFromWKBHexString(VARCHAR)
``````
• 函数说明：输入MultiLine的WKB的HEX字符串数据，返回对应的MultiLine几何对象。

• 返回值类型：GEOMETRY。

### ST_MPolyFromWKBHexString

``````ST_MPolyFromWKBHexString(VARCHAR)
``````
• 函数说明：输入MultiPolygon的WKB的HEX字符串数据，返回对应的MultiPolygon几何对象。

• 返回值类型：GEOMETRY。

### ST_GeometryFromWKB

``````ST_GeometryFromWKB(VARBINARY)
``````
• 函数说明：输入WKB数据，返回对应的几何对象。

• 返回值类型：GEOMETRY。

• 示例：

``````        select ST_ASTEXT(ST_GeometryFromWKB(from_hex('000000000140000000000000004010000000000000')));

+-------------+
| _col0       |
+-------------+
| POINT (2 4) |
+-------------+
``````

### ST_pointFromWKB

``````ST_pointFromWKB(VARBINARY)
``````
• 函数说明：输入Point的WKB数据，返回对应的Point几何对象。

• 返回值类型：GEOMETRY。

• 示例：

``````        select ST_ASTEXT(ST_pointFromWKB(from_hex('000000000140000000000000004010000000000000')));

+-------------+
| _col0       |
+-------------+
| POINT (2 4) |
+-------------+
``````

### ST_lineFromWKB

``````ST_lineFromWKB(VARBINARY)
``````
• 函数说明：输入Line的WKB数据，返回对应的Line几何对象。

• 返回值类型：GEOMETRY。

### ST_polyFromWKB

``````ST_polyFromWKB(VARBINARY)
``````
• 函数说明：输入Polygon的WKB数据，返回对应的Polygon几何对象。

• 返回值类型：GEOMETRY。

### ST_MPointFromWKB

``````ST_MPointFromWKB(VARBINARY)
``````
• 函数说明：输入MultiPoint的WKB数据，返回对应的MultiPoint几何对象。

• 返回值类型：GEOMETRY。

### ST_MLineFromWKB

``````ST_MLineFromWKB(VARBINARY)
``````
• 函数说明：输入MultiLine的WKB数据，返回对应的MultiLine几何对象。

• 返回值类型：GEOMETRY。

### ST_MPolyFromWKB

``````ST_MPolyFromWKB(VARBINARY)
``````
• 函数说明：输入MultiPolygon的WKB数据，返回对应的MultiPolygon几何对象。

• 返回值类型：GEOMETRY。

### ST_GeometryFromGeoJson

``````ST_GeometryFromGeoJson(VARCHAR)
``````
• 函数说明：输入GeoJson的字符串数据，返回对应的几何对象。

• 返回值类型：GEOMETRY。

### ST_GeometryFromJson

``````ST_GeometryFromJson(VARCHAR)
``````
• 函数说明：输入ESRI Geometry Object Json的字符串数据，返回对应的几何对象。

• 返回值类型：GEOMETRY。

### ST_asGeoJson

``````ST_asGeoJson(GEOMETRY)  VARCHAR
``````
• 函数说明：把几何对象转成GeoJson格式输出。

• 返回值类型：GEOMETRY。

• 示例：

``````        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)
``````
• 函数说明：把几何对象转成ESRI Geometry Object Json格式输出。

• 返回值类型：VARCHAR。

• 示例：

``````        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)
``````
• 函数说明：输入ESRI Shape的二进制数据，返回对应的几何对象。

• 返回值类型：GEOMETRY。

• 示例：

``````        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);
``````
• 函数说明：对于给定的经纬度坐标以及最大的hash长度，生成对应的GeoHash字符串。

• 返回值类型：varchar。

• 示例：

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

### ST_LatFromGeoHash

``````ST_LatFromGeoHash(varchar geoHashString);
``````
• 函数说明：从给定的GeoHash字符串中提取纬度值。

• 返回值类型：double。

• 示例：

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

### ST_LongFromGeoHash

``````ST_LongFromGeoHash(varchar geoHashString);
``````
• 函数说明：从给定的GeoHash字符串中提取经度值。

• 返回值类型：double。

• 示例：

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

### ST_GeoHash_Neighbors

``````ST_GeoHash_Neighbors(double longitude, double latitude, long maxCharLength);
``````
• 函数说明：对于给定的经纬度坐标以及最大的hash长度，计算与它相邻的8个方位的GeoHash字符串。

• 返回值类型：varchar。

• 示例：

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