MySQL 空间数据存储及函数

MySQL提供了数据类型geometry用来存储坐标信息，MySQL为空间数据存储及处理提供了专用的类型geometry

前言：

不久前开发了一个地图相关的后端项目，需要提供一些点线面相关的存储、查询、分析相关的操作，于是对MySQL空间函数进行充分调研并应用在项目中;MySQL为空间数据存储及处理提供了专用的类型geometry(支持所有的空间结构)，还有有细分类型Point, LineString, Polygon,MultiPoint,MultiLineString,MultiPolygon等等，我们了解了空间函数，在涉及到经纬度存储，路线存储方面的业务就能够使用此类型进行存储，使用相关空间函数进行分析业务实现，以下所有数据库操作基于MySQL5.7.20。

1.什么是MySQL空间数据

• MySQL提供了数据类型geometry用来存储坐标信息，geometry类型支持以下三种数据存储
• 

  还有多点MULTIPOINT（多点）、MULTILINESTRING（多线）、MULTIPOLYGON（多面）、GEOMETRYCOLLECTION（集合，可放入点线面）等类型

 

2.什么是geojson

GeoJSON是一种对各种地理数据结构进行编码的格式。GeoJSON对象可以表示几何、特征或者特征集合。GeoJSON支持下面几何类型：点、线、面、多点、多线、多面和几何集合。GeoJSON里的特征包含一个几何对象和其他属性，特征集合表示一系列特征。一个完整的GeoJSON数据结构总是一个（JSON术语里的）对象。在GeoJSON里，对象由名/值对--也称作成员的集合组成。对每个成员来说，名字总是字符串。成员的值要么是字符串、数字、对象、数组，要么是下面文本常量中的一个："true","false"和"null"。数组是由值是上面所说的元素组成

 

3.格式化空间数据类型（geometry相互转换geojson）

数据库存储的空间数据通过可视化工具展示的明文结构为上面示例中所见，结构并不易于客户端解析，所以MySQL提供了几个空间函数用来解析及格式化空间数据，geojson是gis空间数据展示的标准格式，前端地图框架及后端空间分析相关框架都会支持geojson格式

 

示例：

准备示例数据

 

函数应用示例

1.查询绿藤气象监测点信息将geometry处理成geojson格式

执行sql:

select id,point_name,ST_ASGEOJSON(point_geom) as geojson from meteorological_point where id = 1

查询结果:

 

2.新增一个点位信息，客户端提交的点位geometry字符串需要使用ST_GEOMFROMTEXT函数处理才能插入，否则会报错

客户端提交点位信息

{
    "point_name":"新帅集团监测点",
    "geotext":"POINT(117.420671499 40.194914201)"}
}

插入sql:

insert into meteorological_point(point_name, point_geom) values("新帅集团监测点", ST_GEOMFROMTEXT("POINT(117.420671499 40.194914201)"))

 

二、空间分析

常用的空间函数

 

二、数据格式

    Point
        字段类型: point
        样例: Point(1 1)
    MultiPoint
        字段类型: multipoint
        样例: MULTIPOINT (1 1,2 2,3 3)
            里面是多个point
    LineString
        字段类型: linestring
        样例: LINESTRING(1 1,2 2,3 3)
    MultiLineString
        字段类型: multilinestring
        样例: MULTILINESTRING ((1 1,2 2,3 3),(2 2,2 3,2 4))
    Polygon
        字段类型: polygon
        样例:
            单面 POLYGON((1 1,1 2,2 2,2 1,1 1))
            镂空面 POLYGON((1 1,,,),(2 2,,,),(3 3,,,))
                会从第一个面中，去除后面的面
                使用ST_Area方法计算面积时，只会算出几个面的面积，用第一个面减，如果后面的面有超过了第一个面范围的部分，会将超出的数值一并减掉。单纯的面积减面积。
        注意:首末点要相同，用于连接成一个闭合的面
    MultiPolygon
        字段类型: multipolygon
        样例: MULTIPOLYGON(((1 1,1 2,2 2,2 1,1 1)),((2 2,2 3,3 2,2 2)))
            里面是多个polygon

 

数据写入

插入时使用ST_GeomFromText，也可使用GeomFromText

 

INSERT INTO t_customers ( lon_lat_point )
VALUES
    ( GeomFromText ( 'POINT(123 123)' ) )

 

 

空间查询函数

• 包含相关

MBRContains(A,B) –> A包含B
MBRWithin(A,B) –> A在B中
注意:包含关系中，所要验证的集合必须全部包含在指定的集合中。如果只有部分在其中，应该使用相交

• 覆盖相关

MBRCoveredBy(A,B) –> A被B覆盖
MBRCovers(A,B) –> A覆盖B

• 相交相关

MBRDisjoint(A,B) –> A、B不相交
MBRIntersects(A,B) –> A、B相交

• 接触

MBRTouches(A,B) –> A、B接触，接触的概念类似于相切

• 重叠

MBROverlaps(A,B) –> A、B重叠

• 相同

MBREquals(A,B) –> A、B相同

 

空间数据相关方法

• 点独有

开始、结束点
ST_StartPoint(A)
ST_EndPoint(A)
获取x或y
ST_X(A)
ST_Y(A)

• 凸包

ST_ConvexHull(A) –> 多点A的凸包面

• 返回矩形

ST_MakeEnvelope(A,B) –> A、B为对角点

• 线独有

线是否闭合
ST_IsClosed(A)
线中点数量
ST_NumPoints
线中第n个点
ST_PointN(A,n)
线长度
ST_Length(A)
生成矩形
ST_Envelope(A) –> A只有两个点，且不是水平或竖直线

• 面积

ST_Area(A)
面的内外边界
ST_ExteriorRing(A) –> 获取A面外环边界，返回值为LineString
ST_InteriorRingN(A,num) –> 获取A面中第num个内环边界，返回值为LineString。num从1开始。
ST_NumInteriorRings(A) –> 获取A面内环数量(5.7.8后添加ST_NumInteriorRing，效果一样)
部分geo对象可用

• 集合

交集
ST_Intersection(A,B)
异或
ST_SymDifference(A,B) –> A、B中独有的
并集
ST_Union(A,B)
质心
ST_Centroid(A)
距离
ST_Distance(A,B) –> A和B距离
ST_Distance_Sphere(A,B) –> A和B的球面距离
不同
ST_Difference(A,B) –> 返回A中有B中没有的
抽稀
ST_Simplify(A,mix_distance) –> 将A抽稀，简化A中两点距离小于max的值(用起来有点迷。。待研究)

• 缓冲区

ST_Buffer(A,length) –> 通过A几何体，生成他周边范围为length距离的面

5.7.7后可以添加策略影响缓冲区的计算,设置的语句是ST_Buffer_Strategy()

• point策略

point_circle –> 点的缓冲区是一个圆(默认)
point_square –> 点的缓冲区是一个正方形，length是点到其中一边的距离

• join策略

join_round –> 连接处缓冲区边界为圆弧(默认)
join_miter –> 连接处缓冲区边界为尖角

• end策略

end_round –> 在结束处缓冲区为圆弧(默认)
end_flat –> 在结束处缓冲区为平坦的直线

• 举例生成缓冲区

ST_Buffer(point, 5, ST_Buffer_Strategy('point_square'))

ST_Buffer(line, 5, ST_Buffer_Strategy('join_miter', 10), ST_Buffer_Strategy('end_flat'))

• 相交

ST_Intersects(A,B) –> A和B是否相交
ST_Crosses(A,B) –> A和B是否相交(相交部分不等于A或B)
ST_Disjoint(A,B) –> A和B是否不相交

• 重叠

ST_Overlaps(A,B)

• 接触

ST_Touches(A,B)

• 包含

ST_Contains(A,B) –> A是否包含B
ST_Within(A,B) –> A是否在B中

• 验证数据是否合法

ST_IsValid(A)
ST_Validate(A)

• geo对象返回格式

ST_AsText(字段名) –> 以文本形式返回
ST_AsBinary(字段名) –> 以二进制形式返回
包含、覆盖、重叠三个方法不清楚具体的区别

注意:每个方法前的MBR、ST可要可不要，在mysql5.7.6之后，不带MBR、ST的方法开始弃用

• 查询样例:

- 查询在指定面中的点
sql
select * from test where MBRContains(ST_GeomFromText('Polygon((0 0,0 5,5 5,5 0,0 0))'),point)

- 查询所有point点的x坐标
sql
SELECT ST_X(point) FROM test

 

 包含、覆盖、重叠三个方法不清楚具体的区别

注意:每个方法前的MBR、ST可要可不要，在mysql5.7.6之后，不带MBR、ST的方法开始弃用

查询样例:
- 查询在指定面中的点
sql
select * from test where MBRContains(ST_GeomFromText('Polygon((0 0,0 5,5 5,5 0,0 0))'),point)

- 查询所有point点的x坐标
sql
SELECT ST_X(point) FROM test

 -查询面和面的交集

SELECT * from map_store_goods where ST_INTERSECTS(point_area_geometry, ST_GeomFromText('MULTIPOLYGON(((109.52244558383 19.420388720372,109.52244558383 19.456999736824,109.56993558747 19.456999736824,109.56993558747 19.420388720372,109.52244558383 19.420388720372)))'))