MySQL的GIS、GEO入门笔记

探索和学习MySQL中GIS相关功能和特性

探索流程:

这里记录了学习和了解MySQL中GIS特性相关内容的过程。

  1. 配置了一台用于测试的MySQL Server 5.7
  2. 在浏览官网的过程中了解到一个GUI工具:MySQL Workbench,很好玩
  3. 开始了解GIS相关内容,但是百度不到很详细的介绍和相关内容
  4. 访问MySQL官方论坛,找到了很多很好的内容,但是都TM是英语。
  5. 了解到需要用到测试数据,都推荐OpenStreetMap,遂前往了解。OSM官网可以下载部分地图数据或者打包下载中国数据,数据格式不同。中国数据.PBF使用上较繁琐,区域数据.OSM可是通过记事本打开(其实可以当作XML文件内容即可)。
  6. 了解到地图数据大多采用一些像PostgreSQL、PostGIS、MongoDB等或Redis之类的NoSQL存储地理位置信息和检索,此类数据库多有GIS相关的处理函数和方法。
  7. 了解到地理位置信息的数据结构多采用OSM的通用数据结构。但是也可以变通。
  8. 在MySQL官方论坛找到一个例子,了解到可以用过一个数据导入脚本将OSM的数据直接导入MySQL数据库,并且此脚本会自动根据经度和纬度生成geometry列,并能对此列添加spatial index。但是这玩意儿需要Perl语言,WTF。
  9. 下载Perl并安装,这玩意儿分为官网版和所谓社区版,官网版就是全部的开源和商业功能的安装包,社区版又叫草莓,是完全的开源功能的安装包,抱着试试看的态度,直接下载草莓传送门。安装成功后,CMD输入perl -v有输出版本信息表示安装成功。
  10. 下载网友自行修改的OSM导入脚本传送门,用RAR解压,将OSM目录放置到Perl的安装目录下的perl\site\lib\下即可。
  11. 在修改了导入脚本osmdb.pm后终于成功导入了osm数据。

开始正式学习:

MySQL官方论坛中GIS的举例

测试数据已经导入成功,下面开始对GIS相关函数和GEOHASH进行了解和体验;

geometry(几何类型)

mysql中geometry类型的简单使用
MySQL空间数据类型

经纬度信息存储在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的坐标系表示,组成部分如下:

  1. 4bytes整形,表示SRID(空间引用标识符,坐标系WGS84,GCJ02等)
  2. 1byte整形(同WKB),表示字节顺序
  3. 4bytes整形(同WKB),表示数据类型(点、线、面等)
  4. 8bytes双精度(同WKB),表示X坐标
  5. 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_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 Wiki百科

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

效果说明
JS抽稀算法
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_ContainsST_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

思考与讨论

  1. 关于社区地理位置的存储,是否可改用Polygon()存储,并建立一列生成列,通过函数ST_Centroid(ST_ExteriorRing(polygon))的方式,把区域的外接圆的中点当作区域的中点,或直接把区域的几何中点作为中点
  2. 查找附近的小区,可使用哪几种方法?

测试用例和导入脚本可自行下载(比较老了,可能官网早已经更新)

链接: https://pan.baidu.com/s/1cW-kv6DIgtYMw5I3bNFzKA 提取码: jagn

©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 211,376评论 6 491
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 90,126评论 2 385
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 156,966评论 0 347
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 56,432评论 1 283
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 65,519评论 6 385
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 49,792评论 1 290
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 38,933评论 3 406
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 37,701评论 0 266
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 44,143评论 1 303
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 36,488评论 2 327
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,626评论 1 340
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 34,292评论 4 329
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 39,896评论 3 313
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,742评论 0 21
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,977评论 1 265
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 46,324评论 2 360
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 43,494评论 2 348