探索和學(xué)習(xí)MySQL中GIS相關(guān)功能和特性

探索流程：

這里記錄了學(xué)習(xí)和了解MySQL中GIS特性相關(guān)內(nèi)容的過(guò)程。

1. 配置了一臺(tái)用于測(cè)試的MySQL Server 5.7
2. 在瀏覽官網(wǎng)的過(guò)程中了解到一個(gè)GUI工具：MySQL Workbench，很好玩
3. 開(kāi)始了解GIS相關(guān)內(nèi)容，但是百度不到很詳細(xì)的介紹和相關(guān)內(nèi)容
4. 訪問(wèn)MySQL官方論壇，找到了很多很好的內(nèi)容，但是都TM是英語(yǔ)。
5. 了解到需要用到測(cè)試數(shù)據(jù)，都推薦OpenStreetMap，遂前往了解。OSM官網(wǎng)可以下載部分地圖數(shù)據(jù)或者打包下載中國(guó)數(shù)據(jù)，數(shù)據(jù)格式不同。中國(guó)數(shù)據(jù).PBF使用上較繁瑣，區(qū)域數(shù)據(jù).OSM可是通過(guò)記事本打開(kāi)（其實(shí)可以當(dāng)作XML文件內(nèi)容即可）。
6. 了解到地圖數(shù)據(jù)大多采用一些像PostgreSQL、PostGIS、MongoDB等或Redis之類的NoSQL存儲(chǔ)地理位置信息和檢索，此類數(shù)據(jù)庫(kù)多有GIS相關(guān)的處理函數(shù)和方法。
7. 了解到地理位置信息的數(shù)據(jù)結(jié)構(gòu)多采用OSM的通用數(shù)據(jù)結(jié)構(gòu)。但是也可以變通。
8. 在MySQL官方論壇找到一個(gè)例子，了解到可以用過(guò)一個(gè)數(shù)據(jù)導(dǎo)入腳本將OSM的數(shù)據(jù)直接導(dǎo)入MySQL數(shù)據(jù)庫(kù)，并且此腳本會(huì)自動(dòng)根據(jù)經(jīng)度和緯度生成geometry列，并能對(duì)此列添加spatial index。但是這玩意兒需要Perl語(yǔ)言，WTF。
9. 下載Perl并安裝，這玩意兒分為官網(wǎng)版和所謂社區(qū)版，官網(wǎng)版就是全部的開(kāi)源和商業(yè)功能的安裝包，社區(qū)版又叫草莓，是完全的開(kāi)源功能的安裝包，抱著試試看的態(tài)度，直接下載草莓傳送門。安裝成功后，CMD輸入perl -v有輸出版本信息表示安裝成功。
10. 下載網(wǎng)友自行修改的OSM導(dǎo)入腳本傳送門，用RAR解壓，將OSM目錄放置到Perl的安裝目錄下的perl\site\lib\下即可。
11. 在修改了導(dǎo)入腳本osmdb.pm后終于成功導(dǎo)入了osm數(shù)據(jù)。

開(kāi)始正式學(xué)習(xí)：

MySQL官方論壇中GIS的舉例

測(cè)試數(shù)據(jù)已經(jīng)導(dǎo)入成功，下面開(kāi)始對(duì)GIS相關(guān)函數(shù)和GEOHASH進(jìn)行了解和體驗(yàn)；

geometry（幾何類型）

mysql中g(shù)eometry類型的簡(jiǎn)單使用
MySQL空間數(shù)據(jù)類型

經(jīng)緯度信息存儲(chǔ)在geometry格式的字段中，該字段必須非空。

MySQL8.0前按照l(shuí)ongitude-latitude的順序存儲(chǔ)位置
MySQL8.0前按照l(shuí)ongitude-latitude的順序存儲(chǔ)位置
MySQL8.0前按照l(shuí)ongitude-latitude的順序存儲(chǔ)位置

插入數(shù)據(jù)時(shí)候可使用如下語(yǔ)句：

使用geomfromtext()內(nèi)置函數(shù)可將字符串的位置點(diǎn)轉(zhuǎn)換為geometry格式存入到數(shù)據(jù)庫(kù)中，例如：
insert into XXTABLE values (XX,XX,XX, st_geomfromtext('point(108.949871515 34.25416521),XX,XX)

• 建立geometry的空間索引，便于之后的查詢：
• 創(chuàng)建按geom列的索引，索引類型選擇spacial即可；
• 創(chuàng)建虛擬列g(shù)eomhash，精度為8位，表達(dá)式為st_geohash('GEOM列名',8)；
• 創(chuàng)建虛擬列g(shù)eomhash的索引。

Geometry數(shù)據(jù)組成：

MySQL存儲(chǔ)geometry信息的方式采用了25bytes，相比WKB的21bytes，多了4bytes的坐標(biāo)系表示，組成部分如下：

1. 4bytes整形，表示SRID（空間引用標(biāo)識(shí)符，坐標(biāo)系WGS84，GCJ02等）
2. 1byte整形（同WKB），表示字節(jié)順序
3. 4bytes整形（同WKB），表示數(shù)據(jù)類型（點(diǎn)、線、面等）
4. 8bytes雙精度（同WKB），表示X坐標(biāo)
5. 8bytes雙精度（同WKB），表示Y坐標(biāo)

ST_GeomFromText

WTF字符串格式說(shuō)明
select ST_GeomFromText(WTF格式字符串);


WKT(Well-known text)是一種文本標(biāo)記語(yǔ)言,用于表示矢量幾何對(duì)象、空間參照系統(tǒng)及空間參照系統(tǒng)之間的轉(zhuǎn)換。通過(guò)WTF字符串生成geometry的方法：

點(diǎn)：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))
多點(diǎn)集：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)))
例如兩點(diǎn)一線組成的幾何集：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函數(shù)

更多內(nèi)容參見(jiàn)
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函數(shù)

更多內(nèi)容參見(jiàn)

MySQL獨(dú)有的創(chuàng)建Geometry函數(shù)

參見(jiàn)
Point(x,y)
LineString((x1,y1),(x2,y2)...)
Polygon(LineString(),LineString()....)

查詢和轉(zhuǎn)換Geometry：

參見(jiàn)
ST_AsText()
ST_AsBinary()
ST_AsWKT()

通用屬性函數(shù)：

參見(jiàn)
ST_Dimension(geom)：返回geom的維度（-1，0，1，2）
ST_Envelope(geom)：返回geom的最小外接矩形（MBR）
ST_GeometryType(geom)：返回geom的類型
ST_IsEmpty(geom)：該函數(shù)并不能真實(shí)的判空，當(dāng)geom為任何有效的幾何值時(shí)返回0，無(wú)效的幾何值返回1；
ST_IsSimple(geom)：當(dāng)geom無(wú)任何異常幾何點(diǎn)返回1（如自相交和自切線等），否則返回0
ST_SRID(geom)：返回geom的坐標(biāo)系ID

Point屬性函數(shù)：

參見(jiàn)
ST_X(Point)：獲取Point的X值
ST_Y(Point)：獲取Point的Y值

LineString屬性和MultiLineString屬性函數(shù)

參見(jiàn)
ST_StartPoint(linestrng)： 線的起點(diǎn)
ST_EndPoint(linestring)：返回線的最后一個(gè)點(diǎn)
ST_IsClosed(linestring或multilinestring)：線是否閉合（若為線，則判斷起點(diǎn)與終點(diǎn)是否一致；若為線組，則判斷組內(nèi)每個(gè)元素是否符合閉合線）
ST_Length(linestring)：返回線的長(zhǎng)度，若入?yún)榫€集，則返回集合內(nèi)所有長(zhǎng)度的和
ST_NumPoiints(linestring)：返回點(diǎn)的數(shù)量；
ST_PointN(linestring,N)：返回第N個(gè)點(diǎn)（從1開(kāi)始）

Polygon屬性和MultiPolygon屬性函數(shù)：

參見(jiàn)
具體不在一一列舉，主要有計(jì)算多邊形面積、中心點(diǎn)、最小外接圓，最大內(nèi)接圓等函數(shù)，列舉幾個(gè)可能會(huì)用到的：


ST_Area(Poly|mPoly)：返回雙精度的面積或面積的和
'ST_Centroid(Poly|mPoly)'：返回?cái)?shù)學(xué)上的中心點(diǎn)
ST_ExteriorRing(Poly)：返回外接圓

Spatial Operator Functions

參見(jiàn)
ST_Buffer說(shuō)明
不再列舉，主要有：ST_Buffer（不懂干啥用），ST_ConvexHull(geom)凸包，ST_Dfference(g1,g2)比較差異，ST_Intersecton(g1,g2)交叉點(diǎn)，ST_SymDifference(g1,g2)對(duì)稱差分，ST_Union(g1,g2)連接、合并等。

Spatial Relatiion Functions

檢查geometry Objects之間的空間關(guān)系的方法。

通過(guò)Object自己的形狀進(jìn)行關(guān)系檢查：

參見(jiàn)
計(jì)算兩個(gè)Object之間的空間關(guān)系的函數(shù)，有兩個(gè)間距離、相交、不相交，包含、相等、相切、重疊、接觸、在內(nèi)等等空間關(guān)系。下面列舉幾個(gè)可能會(huì)常用的方法：
ST_Contains(g1,g2)：g1是否完全包含g2
ST_Within(g1,g2)：g1是否包含于g2中
ST_Distance(g1,g2)：返回g1和g2之間的距離，已坐標(biāo)單位計(jì)算的
ST_Equals(g1,g2)：返回g1和g2是否相等

通過(guò)最小外接矩形MBR進(jìn)行關(guān)系檢查：

參見(jiàn)
MBRContains(g1,g2)：g1的mbr是否包含g2的mbr
MBRWithin(g1,g2)：g1的mbr是否在g2的mbr內(nèi)
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
其他函數(shù)請(qǐng)參看原文

GeoHash

GeoHash介紹
GeoHash Wiki百科

geohash字段是把坐標(biāo)系分成很多小方格，然后將經(jīng)緯度轉(zhuǎn)化成字符串。GeoHash是把經(jīng)緯度轉(zhuǎn)成字符串，建表的時(shí)候讓它轉(zhuǎn)成8位字符，當(dāng)兩個(gè)點(diǎn)離得越近時(shí)，它生成的geohash字符串前面相同的位數(shù)越多，所以在這里先用left()截取前6位字符，前6位相同的誤差在±600米左右，然后模糊查詢，查出大概符合條件的數(shù)據(jù)，最后再精確比較，下面是geohash官方文檔對(duì)geohash長(zhǎng)度和距離誤差的說(shuō)明：

注意：用geohash 查詢會(huì)有邊界問(wèn)題，所以查詢出來(lái)的結(jié)果可能不準(zhǔn)確，可以用程序(例如java代碼)先查出當(dāng)前點(diǎn)周圍8個(gè)范圍的geohash值，然后再匹配這9個(gè)范圍的所有數(shù)據(jù)，這樣就解決了geohash 的邊界問(wèn)題。

st_geohash：

MySQL中自帶函數(shù)st_geohash(longtude,latitude,max_length)或st_geohash(point, max_length)即可生成某一點(diǎn)的geohash值。

• 其中max_length的最大值為100，經(jīng)緯度也不能超出范圍，否則報(bào)錯(cuò)。
• 設(shè)定max_length后產(chǎn)生的hash值也可能會(huì)小于此值，即不超過(guò)這個(gè)長(zhǎng)度。

st_LatFromGeoHash，st_LongFromGeoHash

返回一個(gè)geohash字符串中的latitude或longitude

st_PointFromGeoHash

返回一個(gè)geohash解析出的point數(shù)據(jù)

geojson

官方文檔

geojson是一個(gè)通用的描述位置信息的json格式。具體請(qǐng)參見(jiàn)

st_AsGeoJSON

通過(guò)geometry生成一個(gè)GeoJSON Object，select st_asgeojson(geometry,max_length,options);

• max_length默認(rèn)不指定，如果指定則返回小數(shù)點(diǎn)后的指定長(zhǎng)度
• 其中options參見(jiàn)文檔，具體可以是多個(gè)選項(xiàng)相加，默認(rèn)為0

st_GeomFromGeoJSON

通過(guò)GeoJSON生成GeoMetry對(duì)象。
ST_GeomFromGeoJSON(jsonstring, [options [, srid]])


具體使用方法參見(jiàn)官方文檔

Spatial Convenience Functions

官方文檔
MySQL中提供的方便空間運(yùn)算的函數(shù)們

ST_Distance_Sphere

select ST_Distance_Spher(geomPoint1,geomPoint2 [, radius]);
此方法用于計(jì)算兩點(diǎn)或多個(gè)點(diǎn)之間的地球上的距離（是地球球面距離而不是直線距離），返回單位為米，

• 默認(rèn)的radius半徑為6370986米，即地球的半徑，也可指定其他半徑，單位是米
• 入?yún)⒖蔀?Point,Point)，(Point, MultiPoint)，(MultiPoint,Point)

ST_IsValid

select ST_IsValid(ST_GeomFromText('LINESTRING(0 0,1 1)'))
判斷入?yún)⑹欠袷欠系乩砦恢妹枋龅母袷健７祷?（符合）或者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));
返回兩點(diǎn)構(gòu)成的包絡(luò)。（此計(jì)算是基于笛卡爾坐標(biāo)系而非球面）

• 如果P1和P2相同，則返滬的是P1
• 如果P1和P2是垂直或者水平線，則返回結(jié)果是線段（P1,P2)
• 否則，返回結(jié)果是P1和P2作為對(duì)角線的多邊形包絡(luò)

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

ST_Simplify

效果說(shuō)明
JS抽稀算法
select st_simplify(geometry, max_distance);
用道格拉斯-普克算法（抽稀函數(shù)）簡(jiǎn)化geometry，并返回與原格式相同格式的結(jié)果。

• 入?yún)eometry是一個(gè)geometry對(duì)象（一般為點(diǎn)集合，線）
• max_distance為簡(jiǎn)化步長(zhǎng)

例如，以下點(diǎn)集擬合為直線，步長(zhǎng)0.5：
SELECT st_simplify ( st_geomfromtext ( 'LINESTRING(0 0,0 1,1 1,1 2,2 2,2 3,3 3)' ), 0.5 )
返回結(jié)果：
LINESTRING(0 0, 0 1, 1 1, 2 3, 3 3)
再如，步長(zhǎng)1.0：
SELECT st_simplify ( st_geomfromtext ( 'LINESTRING(0 0,0 1,1 1,1 2,2 2,2 3,3 3)' ), 1.0 )
返回結(jié)果：
LINESTRING(0 0, 3 3)

ST_Validate

SELECT ST_Validate(geometry);
驗(yàn)證geometry是符合正確的地理位置信息格式。例如Point(0 0)是合格的；Linestring(0 0)是非法的；Linestring(0 0, 1 1)是合格的

應(yīng)用與實(shí)踐

了解了上述MySQL中關(guān)于集合對(duì)象的功能，下面來(lái)實(shí)踐一下

計(jì)算兩點(diǎn)之間的距離：

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

查找距離某點(diǎn)【POINT(118.9515 34.4271)】500米范圍內(nèi)的點(diǎn)（ST_Distance_Sphere精確查詢）

查找耗時(shí)約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查找距離某點(diǎn)【POINT(118.9515 34.4271)】500米范圍內(nèi)的點(diǎn)（GeoHash模糊查找后再篩選）

由上面geohash長(zhǎng)度-精度對(duì)應(yīng)表可知，前6位表示±610米左右的誤差，這里先查詢前六位范圍之后再用上述方法精確篩選一次即可：

查找耗時(shí)約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函數(shù)方便和簡(jiǎn)化程序調(diào)用.

通過(guò)幾何關(guān)系函數(shù)查找距離某點(diǎn)【POINT(118.9515 34.4271)】500米范圍內(nèi)的點(diǎn)

該方法是運(yùn)用了內(nèi)置的幾何關(guān)系運(yùn)算函數(shù)ST_Contains和ST_MakeEnvelop來(lái)實(shí)現(xiàn)的，0.5對(duì)應(yīng)大概500米左右的范圍，具體如下；

耗時(shí)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. 關(guān)于社區(qū)地理位置的存儲(chǔ)，是否可改用Polygon()存儲(chǔ)，并建立一列生成列，通過(guò)函數(shù)ST_Centroid(ST_ExteriorRing(polygon))的方式，把區(qū)域的外接圓的中點(diǎn)當(dāng)作區(qū)域的中點(diǎn)，或直接把區(qū)域的幾何中點(diǎn)作為中點(diǎn)
2. 查找附近的小區(qū)，可使用哪幾種方法？

測(cè)試用例和導(dǎo)入腳本可自行下載（比較老了，可能官網(wǎng)早已經(jīng)更新）

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