MySQL 空间地理教程2

最新推荐文章于 2024-04-27 08:43:32 发布

Full Stack Developme

最新推荐文章于 2024-04-27 08:43:32 发布

阅读量17

点赞数

分类专栏： GIS 文章标签： mysql 数据库

原文链接：https://www.jianshu.com/p/65114a9ecb0d

版权

GIS 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

geometry（几何类型）

经纬度信息存储在geometry格式的字段中，该字段必须非空。

MySQL8.0前按照longitude-latitude的顺序存储位置
MySQL8.0前按照longitude-latitude的顺序存储位置
MySQL8.0前按照longitude-latitude的顺序存储位置

插入数据时候可使用如下语句：

使用geomfromtext()内置函数可将字符串的位置点转换为geometry格式存入到数据库中，例如：
insert into XXTABLE values (XX,XX,XX, st_geomfromtext('point(108.949871515 34.25416521),XX,XX)

建立geometry的空间索引，便于之后的查询：
创建按geom列的索引，索引类型选择spacial即可；
创建虚拟列geomhash，精度为8位，表达式为st_geohash('GEOM列名',8)；
创建虚拟列geomhash的索引。

Geometry数据组成：

MySQL存储geometry信息的方式采用了25bytes，相比WKB的21bytes，多了4bytes的坐标系表示，组成部分如下：

4bytes整形，表示SRID（空间引用标识符，坐标系WGS84，GCJ02等）
1byte整形（同WKB），表示字节顺序
4bytes整形（同WKB），表示数据类型（点、线、面等）
8bytes双精度（同WKB），表示X坐标
8bytes双精度（同WKB），表示Y坐标

ST_GeomFromText

WTF字符串格式说明
select ST_GeomFromText(WTF格式字符串);
WKT(Well-known text)是一种文本标记语言,用于表示矢量几何对象、空间参照系统及空间参照系统之间的转换。通过WTF字符串生成geometry的方法：

点：POINT(x y)
线：LINESTRING(x1 y1, x2 y2, x3 y3...)
多边形：POLYGON((0 0, 10 0, 10 10,0 10,0 0),(5 5,7 5,7 7,5 7,5 5))
多点集：MULTIPOINT(0 0, 20 20, 60 60)或MULTIPOINT((0 0),(5 5),(5 0))
多线集：MULTILINESTRING((10 10, 20 20), (15 15, 30 15))
多多边形集：MULTIPOLYGON(((0 0,10 0,10 10,0 10,0 0)),((5 5,7 5,7 7,5 7, 5 5)))
例如两点一线组成的几何集：GEOMETRYCOLLECTION(POINT(10 10), POINT(30 30), LINESTRING(15 15, 20 20))

MySQL中Geometry的格式约定：

A geometry is syntactically well-formed if it satisfies conditions such as those in this (nonexhaustive) list:

Linestrings have at least two points
Polygons have at least one ring
Polygon rings are closed (first and last points the same)
Polygon rings have at least 4 points (minimum polygon is a triangle with first and last points the same)

Collections are not empty (except GeometryCollection)

其他ST_XXXXFromText函数
`ST_PointFromText('POINT(X Y)');`
`ST_LineStringFromText('LINESTRING(0 0,1 1,2 2)');`
`ST_PolygonFromText('POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7,5 5))');`
`ST_GeomCollFromText();`

其他ST_XXXXFromWkb函数

MySQL独有的创建Geometry函数
`Point(x,y)`
`LineString((x1,y1),(x2,y2)...)`
`Polygon(LineString(),LineString()....)`

查询和转换Geometry：
`ST_AsText()`
`ST_AsBinary()`
`ST_AsWKT()`

通用属性函数：
`ST_Dimension(geom)`：返回geom的维度（-1，0，1，2）
`ST_Envelope(geom)`：返回geom的最小外接矩形（MBR）
`ST_GeometryType(geom)`：返回geom的类型
`ST_IsEmpty(geom)`：该函数并不能真实的判空，当geom为任何有效的几何值时返回0，无效的几何值返回1；
`ST_IsSimple(geom)`：当geom无任何异常几何点返回1（如自相交和自切线等），否则返回0
`ST_SRID(geom)`：返回geom的坐标系ID

Point属性函数：
`ST_X(Point)`：获取Point的X值
`ST_Y(Point)`：获取Point的Y值

LineString属性和MultiLineString属性函数
`ST_StartPoint(linestrng)`：线的起点
`ST_EndPoint(linestring)`：返回线的最后一个点
`ST_IsClosed(linestring或multilinestring)`：线是否闭合（若为线，则判断起点与终点是否一致；若为线组，则判断组内每个元素是否符合闭合线）
`ST_Length(linestring)`：返回线的长度，若入参为线集，则返回集合内所有长度的和
`ST_NumPoiints(linestring)`：返回点的数量；
`ST_PointN(linestring,N)`：返回第N个点（从1开始）

Polygon属性和MultiPolygon属性函数：
具体不在一一列举，主要有计算多边形面积、中心点、最小外接圆，最大内接圆等函数，列举几个可能会用到的：

`ST_Area(Poly|mPoly)`：返回双精度的面积或面积的和
'ST_Centroid(Poly|mPoly)'：返回数学上的中心点
`ST_ExteriorRing(Poly)`：返回外接圆

Spatial Operator Functions
不再列举，主要有：ST_Buffer（不懂干啥用），ST_ConvexHull(geom)凸包，ST_Dfference(g1,g2)比较差异，ST_Intersecton(g1,g2)交叉点，ST_SymDifference(g1,g2)对称差分，ST_Union(g1,g2)连接、合并等。

Spatial Relatiion Functions

检查geometry Objects之间的空间关系的方法。

通过Object自己的形状进行关系检查：
计算两个Object之间的空间关系的函数，有两个间距离、相交、不相交，包含、相等、相切、重叠、接触、在内等等空间关系。下面列举几个可能会常用的方法：
`ST_Contains(g1,g2)`：g1是否完全包含g2
`ST_Within(g1,g2)`：g1是否包含于g2中
`ST_Distance(g1,g2)`：返回g1和g2之间的距离，已坐标单位计算的
`ST_Equals(g1,g2)`：返回g1和g2是否相等

通过最小外接矩形MBR进行关系检查：
`MBRContains(g1,g2)`：g1的mbr是否包含g2的mbr
`MBRWithin(g1,g2)`：g1的mbr是否在g2的mbr内
`MBRCoveredBy(g1,g2)`：g1的mbr是否被g2的mbr覆盖
`MBRCovers(g1,g2)`：g1的mbr是否覆盖g2的mbr
`MBRDisjoint(g1,g2)`：g1的mbr,g2的mbr是否不相交
`MBRIntersects(g1,g2)`：g1mbr，g2mbr是否相交
`MBREqual(g1,g2)`：g1的mbr,g2的mbr的外接是否相等
`MBREquals(g1,g2)`：g1的mbr,g2的mbr的外接是否相等
`MBROverlaps(g1,g2)`：g1mbr、g2mbr
其他函数请参看原文

GeoHash

geohash字段是把坐标系分成很多小方格，然后将经纬度转化成字符串。GeoHash是把经纬度转成字符串，建表的时候让它转成8位字符，当两个点离得越近时，它生成的geohash字符串前面相同的位数越多，所以在这里先用left()截取前6位字符，前6位相同的误差在±600米左右，然后模糊查询，查出大概符合条件的数据，最后再精确比较，下面是geohash官方文档对geohash长度和距离误差的说明：

length	lat bits	lng bits	lat err	lng err	km err
1	2	3	±23	±23	±2500
2	5	5	±2.8	±5.6	±630
3	7	8	±0.70	±0.70	±78
4	10	10	±0.087	±0.18	±20
5	12	13	±0.022	±0.022	±2.4
6	15	15	±0.0027	±0.0055	±0.61
7	17	18	±0.00068	±0.00068	±0.076
8	20	20	±0.000085	±0.00017	±0.019

注意：用geohash 查询会有边界问题，所以查询出来的结果可能不准确，可以用程序(例如java代码)先查出当前点周围8个范围的geohash值，然后再匹配这9个范围的所有数据，这样就解决了geohash 的边界问题。

st_geohash：

MySQL中自带函数st_geohash(longtude,latitude,max_length)或st_geohash(point, max_length)即可生成某一点的geohash值。

其中max_length的最大值为100，经纬度也不能超出范围，否则报错。
设定max_length后产生的hash值也可能会小于此值，即不超过这个长度。

st_LatFromGeoHash，st_LongFromGeoHash

返回一个geohash字符串中的latitude或longitude

st_PointFromGeoHash

返回一个geohash解析出的point数据

geojson

geojson是一个通用的描述位置信息的json格式。具体请参见

st_AsGeoJSON

通过geometry生成一个GeoJSON Object，select st_asgeojson(geometry,max_length,options);

max_length默认不指定，如果指定则返回小数点后的指定长度
其中options参见文档，具体可以是多个选项相加，默认为0

st_GeomFromGeoJSON

通过GeoJSON生成GeoMetry对象。
ST_GeomFromGeoJSON(jsonstring, [options [, srid]])

具体使用方法参见官方文档

Spatial Convenience Functions
MySQL中提供的方便空间运算的函数们

ST_Distance_Sphere

select ST_Distance_Spher(geomPoint1,geomPoint2 [, radius]);
此方法用于计算两点或多个点之间的地球上的距离（是地球球面距离而不是直线距离），返回单位为米，

默认的radius半径为6370986米，即地球的半径，也可指定其他半径，单位是米
入参可为(Point,Point)，(Point, MultiPoint)，(MultiPoint,Point)

ST_IsValid

select ST_IsValid(ST_GeomFromText('LINESTRING(0 0,1 1)'))
判断入参是否是符合地理位置描述的格式。返回1（符合）或者0（不符）；

例如：
返回0：
select st_isvalid(st_geomfromtext('linestring(0 0, -0.00 0, 0.0 0)')
返回1：
select st_isvalid(st_geomfromtext('linestring(0 0,1 1)')

ST_MakeEnvelope

select st_astext(st_makeenvelope(pt1, pt2));
返回两点构成的包络。（此计算是基于笛卡尔坐标系而非球面）

如果P1和P2相同，则返沪的是P1
如果P1和P2是垂直或者水平线，则返回结果是线段（P1,P2)
否则，返回结果是P1和P2作为对角线的多边形包络

例如：
SELECT ST_AsText ( st_makeenvelope ( st_geomfromtext ( 'point(0 0)' ), st_geomfromtext ( 'point(1 1)' ) ) );
返回结果：
POLYGON((0 0,1 0,1 1,0 1,0 0))

ST_Simplify
`select st_simplify(geometry, max_distance);`
用道格拉斯-普克算法（抽稀函数）简化geometry，并返回与原格式相同格式的结果。

入参geometry是一个geometry对象（一般为点集合，线）
max_distance为简化步长

例如，以下点集拟合为直线，步长0.5：
SELECT st_simplify ( st_geomfromtext ( 'LINESTRING(0 0,0 1,1 1,1 2,2 2,2 3,3 3)' ), 0.5 )
返回结果：
LINESTRING(0 0, 0 1, 1 1, 2 3, 3 3)
再如，步长1.0：
SELECT st_simplify ( st_geomfromtext ( 'LINESTRING(0 0,0 1,1 1,1 2,2 2,2 3,3 3)' ), 1.0 )
返回结果：
LINESTRING(0 0, 3 3)

ST_Validate

SELECT ST_Validate(geometry);
验证geometry是符合正确的地理位置信息格式。例如Point(0 0)是合格的；Linestring(0 0)是非法的；Linestring(0 0, 1 1)是合格的

应用与实践

了解了上述MySQL中关于集合对象的功能，下面来实践一下

计算两点之间的距离：

SELECT floor( 
    st_distance_sphere (
     ( SELECT geom FROM nodestest WHERE id = '151024809' ), 
     ( SELECT geom FROM nodestest WHERE id = '151027929' )
    )
);

查找距离某点【POINT(118.9515 34.4271)】500米范围内的点（ST_Distance_Sphere精确查询）

查找耗时约2.582秒。

SELECT 
    id,
    ST_ASTEXT(geom) point,
    FLOOR(ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(118.9515 34.4271)'),
                    geom)) distance
FROM
    nodestest
WHERE
    ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(118.9515 34.4271)'),
            geom) < 500
ORDER BY distance;

HASH查找距离某点【POINT(118.9515 34.4271)】500米范围内的点（GeoHash模糊查找后再筛选）

由上面geohash长度-精度对应表可知，前6位表示±610米左右的误差，这里先查询前六位范围之后再用上述方法精确筛选一次即可：

查找耗时约0.016秒

SELECT 
    t.id,
    ST_ASTEXT(t.geom) point,
    FLOOR(ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(118.9515 34.4271)'),
                    t.geom)) distance
FROM
    (SELECT 
        id, geom, ST_ASTEXT(geom) point
    FROM
        nodestest
    WHERE
        geomhash LIKE (CONCAT(LEFT(ST_GEOHASH(118.9515, 34.4271, 8), 6), '%'))) T
WHERE
    ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(118.9515 34.4271)'),
            t.geom) < 500
ORDER BY distance;

可将上述查询方法封装为MySQL函数方便和简化程序调用.

通过几何关系函数查找距离某点【POINT(118.9515 34.4271)】500米范围内的点

该方法是运用了内置的几何关系运算函数ST_Contains和ST_MakeEnvelop来实现的，0.5对应大概500米左右的范围，具体如下；

耗时0.016s

SELECT 
    id,
    ST_ASTEXT(geom),
    FLOOR(ST_DISTANCE_SPHERE(POINT(118.9515, 34.4271), geom)) AS distance
FROM
    nodestest
WHERE
    ST_CONTAINS(ST_MAKEENVELOPE(POINT((118.9515 + (0.5 / 111)),
                        (34.4271 + (0.5 / 111))),
                    POINT((118.9515 - (0.5 / 111)),
                        (34.4271 - (0.5 / 111)))),
            geom)
ORDER BY distance