基于MySQL Spatial的矢量空間數(shù)據(jù)存儲(chǔ)與查詢

黃 鵬

(長(zhǎng)江工程職業(yè)技術(shù)學(xué)院， 武漢 430212)

定位技術(shù)與地理位置服務(wù)的廣泛應(yīng)用，使得空間地理信息在智慧城市建設(shè)中占據(jù)重要位置。與傳統(tǒng)數(shù)據(jù)相比，空間數(shù)據(jù)具有數(shù)據(jù)量大、非結(jié)構(gòu)化、分類編碼、空間位置相關(guān)、更新速度快等特點(diǎn)。目前空間數(shù)據(jù)庫(kù)有兩種主流技術(shù)模式：一種是采用空間數(shù)據(jù)引擎+關(guān)系型數(shù)據(jù)庫(kù)的中間件技術(shù)，如ESRI公司的ArcSDE。另一種是關(guān)系數(shù)據(jù)庫(kù)+空間功能擴(kuò)展的對(duì)象關(guān)系型數(shù)據(jù)庫(kù)融合模式，由數(shù)據(jù)庫(kù)廠商專門實(shí)現(xiàn)，如Oracle Spatial，PostGIS，Mysql Spatial等[1]。

1 對(duì)象關(guān)系型空間數(shù)據(jù)庫(kù)

對(duì)象關(guān)系型數(shù)據(jù)庫(kù)采用關(guān)系模型組織屬性數(shù)據(jù)，按對(duì)象存取空間數(shù)據(jù)，實(shí)現(xiàn)了在數(shù)據(jù)庫(kù)內(nèi)核中進(jìn)行空間操作和處理，其優(yōu)點(diǎn)是擴(kuò)展SQL比較方便，較易實(shí)現(xiàn)數(shù)據(jù)共享與互操作[2]。目前市場(chǎng)上流行的三種對(duì)象關(guān)系型空間數(shù)據(jù)庫(kù)分別是Oracle Spatial，PostGIS和Mysql Spatial。

Oracle spatial 是Oracle數(shù)據(jù)庫(kù)為實(shí)現(xiàn)快速、高效地存取分析空間數(shù)據(jù)而推出的專用組件。Oracl Spatial定義了一種可自定義的空間對(duì)象類型SDO_Geometry，在該類型中，可以進(jìn)一步定義數(shù)據(jù)的幾何體類型、空間參考坐標(biāo)系、數(shù)據(jù)結(jié)構(gòu)、坐標(biāo)值信息等。Oracl Spatial還提供了四叉樹、參照樹等空間索引方案，支持直接在SQL語(yǔ)句中調(diào)用完整的空間操作函數(shù)，極大地提高了空間數(shù)據(jù)的查詢效率。Oracle Spatial實(shí)現(xiàn)了非結(jié)構(gòu)化、有嵌套關(guān)系的空間數(shù)據(jù)與屬性數(shù)據(jù)的統(tǒng)一存儲(chǔ)和管理，為空間數(shù)據(jù)的管理和利用帶來(lái)方便。

PostGIS是在開源數(shù)據(jù)庫(kù)Postgre SQL基礎(chǔ)上研發(fā)出來(lái)的空間功能擴(kuò)展插件，它彌補(bǔ)了Postgre SQL在空間數(shù)據(jù)管理上的缺陷和不足。PostGIS支持幾乎所有的空間數(shù)據(jù)類型，如點(diǎn)、線、多邊形、復(fù)合點(diǎn)、復(fù)合線、復(fù)合多邊形，也支持所有的數(shù)據(jù)存儲(chǔ)和構(gòu)造方法。PostGIS提供了Union(求并)、Difference(求差)、Distance(求距離)等空間分析函數(shù)來(lái)操作空間數(shù)據(jù)，也提供二元謂詞來(lái)監(jiān)測(cè)空間對(duì)象之間的空間關(guān)系[3]。此外，PostGIS還提供了空間坐標(biāo)系轉(zhuǎn)換、空間聚集函數(shù)、柵格數(shù)據(jù)切片緩存等功能，拓展了空間數(shù)據(jù)管理能力，在目前的開源GIS建設(shè)方案中占據(jù)重要地位。

MySQL是最初由瑞典MySQL AB公司所研發(fā)的中小型數(shù)據(jù)庫(kù)管理系統(tǒng)，據(jù)數(shù)據(jù)庫(kù)權(quán)威網(wǎng)站DB-Engines統(tǒng)計(jì)，2020-2022三年間，MySQL牢牢占據(jù)數(shù)據(jù)庫(kù)排行榜第二名的位置。MySQL從4.0版本開始加入了Spatial模塊，簡(jiǎn)稱為MySQL Spatial。該模塊遵循了美國(guó)OGC協(xié)會(huì)提出的開放式地理數(shù)據(jù)互操作規(guī)范，支持空間幾何數(shù)據(jù)類型，并實(shí)現(xiàn)了相關(guān)的空間操作函數(shù)和空間索引方法。隨著8.0版本的推出，MySQL Spatial在空間操作功能方面逐步完善，引入了地理參照坐標(biāo)系，豐富了空間分析函數(shù)，又因其開源免費(fèi)、知名度高等特點(diǎn)，已成為空間數(shù)據(jù)管理的優(yōu)選方案之一。

2 MySQL Spatial空間擴(kuò)展

2.1 空間數(shù)據(jù)類型及存儲(chǔ)格式

Mysql Spatial實(shí)現(xiàn)了OpenGIS規(guī)范的一個(gè)子集，實(shí)現(xiàn)了有限的空間幾何數(shù)據(jù)類型[4]，如Geometry(幾何基類)、Point(點(diǎn))、LineString(線)、Polygon(面)等基礎(chǔ)數(shù)據(jù)類型，和MulitPoint(復(fù)合點(diǎn))、MultiLineString(復(fù)合線)、MultiPolygon(復(fù)合面)、GeometryCollection(幾何集合)等復(fù)雜數(shù)據(jù)類型。Mysql Spatial已支持的幾何數(shù)據(jù)模型如圖1所示。

圖1 MySQL Spatial支持的幾何數(shù)據(jù)模型

Mysql Spatial采用三種數(shù)據(jù)格式WKT、WKB、內(nèi)部存儲(chǔ)格式來(lái)描述空間數(shù)據(jù)的幾何結(jié)構(gòu)。WKT(Well-Known Text format)是一種文本標(biāo)記語(yǔ)言，用于表示地圖上的矢量幾何對(duì)象、空間參照系以及空間參照系之間的轉(zhuǎn)換。如將點(diǎn)對(duì)象表示為POINT(x y)，線對(duì)象表示為L(zhǎng)INESTRING(x1 y1,x2 y2,…,xn yn)，而面對(duì)象則采用內(nèi)環(huán)坐標(biāo)加外環(huán)坐標(biāo)的形式來(lái)表示，如POLYGON((x01 y01,x02 y02,…,x0n y0n), (x11 y11,x12 y12,…,x1n y1n))。

WKB(Well-Known Binary Format)格式以二進(jìn)制流的形式交互幾何數(shù)據(jù)，將幾何信息存儲(chǔ)在BLOB值中。如一個(gè)點(diǎn)狀對(duì)象表示為21個(gè)字節(jié)的二進(jìn)制數(shù)，其中1個(gè)字節(jié)代表字節(jié)序、4個(gè)字節(jié)代表幾何類型、16個(gè)字節(jié)代表雙精度x坐標(biāo)、y坐標(biāo)。線狀和面狀對(duì)象則保留前部“字節(jié)序和幾何類型”的位置，剩余的字節(jié)由子對(duì)象數(shù)目和子對(duì)象的坐標(biāo)數(shù)組共同決定。

內(nèi)部存儲(chǔ)格式是Mysql實(shí)際存儲(chǔ)幾何對(duì)象的數(shù)據(jù)格式，它與WKB格式不完全相同，而是在二進(jìn)制數(shù)的頭部再加4個(gè)字節(jié)表示空間參考系的ID(SRID)值，如WGS84坐標(biāo)系的SRID為4326。這樣一個(gè)點(diǎn)狀對(duì)象在Mysql中的實(shí)際存儲(chǔ)長(zhǎng)度是25個(gè)字節(jié)。

2.2 空間索引

為了提高空間數(shù)據(jù)的搜索效率，MySQL Spatial支持為空間數(shù)據(jù)添加R-Trees(R樹)空間索引[5]。R樹索引將空間對(duì)象按范圍進(jìn)行劃分，分為葉節(jié)點(diǎn)和非葉節(jié)點(diǎn)。其中葉節(jié)點(diǎn)不可再分，由空間對(duì)象的最小外接矩形(MBR)構(gòu)成。非葉節(jié)點(diǎn)是可以再分的節(jié)點(diǎn)，也稱為父節(jié)點(diǎn)，它由所有子節(jié)點(diǎn)的公共外接矩形MBR構(gòu)成，并存儲(chǔ)有指向各子節(jié)點(diǎn)的指針。自8.0版本以后，MySQL Spatial支持為MyISAM和INNODB存儲(chǔ)引擎的數(shù)據(jù)表添加空間索引，添加索引的關(guān)鍵字為SPATIAL INDEX，且添加索引的字段不能為空。

2.3 空間處理函數(shù)

MySQL Spatial也提供了大量的空間處理函數(shù)來(lái)操作空間數(shù)據(jù)。這些函數(shù)可以分為以下5類：幾何對(duì)象構(gòu)造函數(shù)、格式轉(zhuǎn)換函數(shù)、幾何屬性訪問函數(shù)、空間關(guān)系函數(shù)和空間運(yùn)算函數(shù)，如表1所示。在MySQL 8.0版本以前，空間函數(shù)的操作都是在幾何平面(笛卡爾坐標(biāo)系)下進(jìn)行，沒有考慮幾何對(duì)象的空間參照關(guān)系。而在MySQL 8.0版本中，空間函數(shù)已經(jīng)支持同一坐標(biāo)系下的幾何空間運(yùn)算，極大地?cái)U(kuò)展了MySQL的空間數(shù)據(jù)處理能力。

表1 Mysql Spatial空間函數(shù)表

3 矢量空間數(shù)據(jù)存儲(chǔ)與查詢

為了驗(yàn)證MySQL Spatial在空間數(shù)據(jù)存儲(chǔ)管理上的有效性，選取湖北省行政區(qū)劃數(shù)據(jù)(面類型)與綠地公園數(shù)據(jù)(點(diǎn)類型)為樣本，通過(guò)Shapelib開源庫(kù)實(shí)現(xiàn)Shapefile矢量格式數(shù)據(jù)的讀取，再調(diào)用Mysql Connector Net驅(qū)動(dòng)程序的API接口，將其轉(zhuǎn)存到Mysql數(shù)據(jù)庫(kù)中。

3.1 Shapefile矢量數(shù)據(jù)讀取

Shapelib是采用c語(yǔ)言編寫的輕量級(jí)地圖工具，在.net環(huán)境下，可以通過(guò)DLLImport方法將其引入到c#語(yǔ)言中使用(注意指針與IntPtr、數(shù)組等參數(shù)的轉(zhuǎn)換關(guān)系)，也可以使用SharpShape版本來(lái)讀取數(shù)據(jù)。Shapelib讀取Shapefile數(shù)據(jù)的具體過(guò)程如下：(1)通過(guò)ShapeOpen()函數(shù)獲取文件句柄，調(diào)用SHPGetInfo()和SHPReadObject()函數(shù)讀取要素的幾何類型和坐標(biāo)數(shù)據(jù)；(2)通過(guò)DBFOpen()打開屬性數(shù)據(jù)，再通過(guò)字段處理函數(shù)讀取字段類型及屬性值；(3)讀取.proj投影文件信息，根據(jù)空間參考規(guī)范，確定數(shù)據(jù)的SRID值。由于Mysql目前僅支持同一空間參考系下的函數(shù)運(yùn)算，因此統(tǒng)一定義數(shù)據(jù)坐標(biāo)系為WGS84，其SRID值為4326。Shapefile文件的讀取及存儲(chǔ)過(guò)程如圖2所示。

圖2 Shapefile矢量文件讀取及存儲(chǔ)過(guò)程

3.2 創(chuàng)建空間數(shù)據(jù)庫(kù)與數(shù)據(jù)表

在數(shù)據(jù)庫(kù)中，通過(guò)SQL語(yǔ)句“Create database myGIS; use myGIS;”創(chuàng)建并打開空間數(shù)據(jù)庫(kù)。由于Shapelib讀取獲得的字段類型與Mysql數(shù)據(jù)庫(kù)中的字段類型不完全一致，因此在創(chuàng)建數(shù)據(jù)表之前，需要確立兩者之間的轉(zhuǎn)換關(guān)系。幾何數(shù)據(jù)類型直接轉(zhuǎn)換為Mysql支持的幾何類型，而字符串類型和數(shù)字類型在轉(zhuǎn)換時(shí)還要保留數(shù)據(jù)的長(zhǎng)度和精度信息。如表2所示。

表2 Shapelib讀取字段類型與MySQL字段類型對(duì)比

根據(jù)空間數(shù)據(jù)的特性，幾何坐標(biāo)信息采用Point、

Linestring、Polygon幾何字段類型保存，屬性信息采用Mysql基礎(chǔ)數(shù)據(jù)類型保存。通過(guò)SQL語(yǔ)句“Create Table Name()”創(chuàng)建空間數(shù)據(jù)表，其建表過(guò)程如下：加載Mysql-connector-net驅(qū)動(dòng)程序，根據(jù)之前讀取到的幾何字段類型和屬性字段類型，確定空間數(shù)據(jù)表的各個(gè)字段類型，創(chuàng)建對(duì)應(yīng)的數(shù)據(jù)表。由于Mysql Spatial的空間函數(shù)已支持同一坐標(biāo)系的空間運(yùn)算，因此在建立幾何字段的同時(shí)，需附加空間參考約束‘SRID 4326’。建立行政區(qū)劃數(shù)據(jù)表的Sql語(yǔ)句如下所示：

CRETTETABLEhubeiRegion(FIDintprimarykeyauto_increment, -- 主鍵

SHAPEpolygonnotnullSRID4326,-- 幾何字段

NAMEvarchar(30), -- 區(qū)劃名稱

KINDvarchar(4), -- 分類碼

ADMINCODEvarchar(6), -- 行政編碼

PROVINCENAvarchar(50)); -- 所屬省份名稱

3.3 插入空間數(shù)據(jù)

數(shù)據(jù)表建好之后，將Shapefile中的坐標(biāo)數(shù)據(jù)轉(zhuǎn)為標(biāo)準(zhǔn)SQL語(yǔ)句“Insert into values”插入表中。(1)將空間坐標(biāo)數(shù)據(jù)逐一轉(zhuǎn)為WKT文本格式，緯度在前，經(jīng)度在后；對(duì)于線、面對(duì)象，需要逐一獲取其子對(duì)象Part的坐標(biāo)串?dāng)?shù)據(jù)，再附加其空間參考ID，以保證與數(shù)據(jù)表的結(jié)構(gòu)相一致；(2)利用Mysql Spatial提供的ST_GeomFromText()函數(shù)逐一構(gòu)造幾何參數(shù)；(3)依據(jù)shapelib讀取得到的屬性數(shù)據(jù)，補(bǔ)齊其余的屬性字段參數(shù)；(4)調(diào)用API接口的ExecuteNonQuery()方法，執(zhí)行SQL語(yǔ)句向數(shù)據(jù)表插入一條記錄。插入空間數(shù)據(jù)后的行政區(qū)劃表如圖3所示。

圖3 MySQL空間數(shù)據(jù)存儲(chǔ)

3.4 空間查詢與索引

空間數(shù)據(jù)轉(zhuǎn)入數(shù)據(jù)庫(kù)后，還需要建立空間索引，

為后續(xù)的空間查詢做好鋪墊?？臻g索引的方法默認(rèn)為R-Tree索引。其創(chuàng)建的方法是在幾何字段上建立R樹索引約束，關(guān)鍵字為Spatial Index。創(chuàng)建索引之前，還要確?？臻g索引字段不為空(非空約束)，且數(shù)據(jù)表的存儲(chǔ)引擎為InnoDB，以支持地理空間參考下的空間查詢功能。建立空間索引的SQL語(yǔ)句如下所示。

ALTERTABLEhubeiRegionADDSPATIALINDEX(shape);

(1)以查詢“與武漢市接壤的市級(jí)行政區(qū)劃”為例，實(shí)現(xiàn)行政區(qū)劃圖層內(nèi)部的空間鄰接關(guān)系查詢。由于ST_Touches()函數(shù)在8.0.27版本中不能良好支持地理空間坐標(biāo)系下的關(guān)系運(yùn)算，因此可利用空間拓?fù)潢P(guān)系的映射不變特性，將其置于平面坐標(biāo)系(SRID:0)來(lái)間接判斷要素間的空間鄰接關(guān)系。SQL查詢語(yǔ)句如下，查詢結(jié)果如圖4(a)所示。

圖4 MySQL空間查詢結(jié)果

SET@g= (SELECTST_SRID(shape,0)fromhubeiregionWHERENAME=“武漢市”);

SELECTNAME,ST_AsText(shape)FROMhubeiregionWHEREST_Touches(@g,ST_SRID(shape,0));

(2)以查詢“武漢市行政區(qū)劃內(nèi)的綠地公園”為例，實(shí)現(xiàn)行政區(qū)劃圖層與綠地圖層的空間包含關(guān)系查詢。Mysql Spatial提供ST_Contains()函數(shù)來(lái)判斷實(shí)體要素間的空間包含關(guān)系。SQL查詢語(yǔ)句如下，查詢結(jié)果圖4(b)所示。

SET@g= (SELECTshapefromhubeiregionWHERENAME=“武漢市”);

SELECTNAMEFROMgreenlandWHEREST_Contains(@g,shape)limit14;

3.5 查詢結(jié)果分析

將上述樣本數(shù)據(jù)導(dǎo)入PostGIS 14中，與Mysql Spatial的查詢結(jié)果作對(duì)比分析。評(píng)測(cè)指標(biāo)采用服務(wù)器執(zhí)行SQL語(yǔ)句的實(shí)際查詢用時(shí)，排除查詢緩存和客戶端渲染技術(shù)對(duì)結(jié)果的影響，對(duì)比結(jié)果如表3所示。(1)數(shù)據(jù)導(dǎo)入方式：PostGIS提供了工具命令shp2pgsql及圖形化界面來(lái)導(dǎo)入Shapefile數(shù)據(jù)，導(dǎo)入后的坐標(biāo)數(shù)據(jù)被定義為Geometry類型；而Mysql Spatial不提供shapefile文件導(dǎo)入功能，本文中使用shapelib第三方庫(kù)實(shí)現(xiàn)；(2)空間關(guān)系判斷：以“市內(nèi)綠地”(點(diǎn)-面包含)及“行政區(qū)劃接壤”(面-面相鄰)的關(guān)系判斷為例，在不考慮空間參照系時(shí)，PostGIS中的查詢時(shí)間分別為67ms、116ms，Mysql Spatial中的查詢時(shí)間分別為88ms，48ms，后者總耗時(shí)少于前者。而在代入地理參照系后，前者的查詢時(shí)間幾乎沒有變化，而后者的“包含查詢”用時(shí)增加到508ms，且無(wú)法直接判斷鄰接關(guān)系，提示Mysql在空間關(guān)系的判斷上還存在不足；(3)距離量測(cè)與邊界查詢：兩者均提供了ST_Distance()函數(shù)進(jìn)行地面距離的測(cè)算，在PostGIS中需要將Geometry幾何對(duì)象轉(zhuǎn)為Geography地理對(duì)象，才能獲得大地距離。以“白玉公園”到“新城廣場(chǎng)”中心點(diǎn)的距離為例，在PostGIS中的測(cè)量值為13 802.145m，耗時(shí)71ms，在Mysql中的測(cè)量值為13 802.158m，耗時(shí)66ms。另外，在讀取面要素的幾何邊界(武漢市行政邊界)時(shí)，兩者分別用時(shí)75ms、31ms，說(shuō)明在不考慮空間關(guān)系運(yùn)算時(shí)，Mysql的計(jì)算速度略優(yōu)于PostGIS；(4)空間索引：PostGIS綜合使用了R樹與GIST索引兩種方式，而Mysql采用的是R樹索引，這也是導(dǎo)致兩者查詢效率產(chǎn)生差異的因素之一。

表3 Mysql Spatial與PostGIS查詢結(jié)果對(duì)比

4 結(jié) 語(yǔ)

隨著8.0版本的推出，Mysql Spatial進(jìn)一步豐富了其空間處理函數(shù)，引入了地理坐標(biāo)系和投影坐標(biāo)系，并支持同一參考坐標(biāo)系下的空間運(yùn)算。其空間運(yùn)算性能雖然相比PostGIS還有差距，但已能實(shí)現(xiàn)基于地理實(shí)體的空間關(guān)系判斷與距離測(cè)量，讀取幾何數(shù)據(jù)較快，具備了在中小型GIS系統(tǒng)中存儲(chǔ)和管理矢量數(shù)據(jù)的能力。研究中也發(fā)現(xiàn)，Mysql Spatial 8.0在地理坐標(biāo)系下的鄰接關(guān)系判斷不夠完善，其空間分析函數(shù)不如專業(yè)的GIS應(yīng)用軟件豐富。因此，Mysql Spatial未來(lái)可能會(huì)繼續(xù)專注于空間數(shù)據(jù)存儲(chǔ)、管理與查詢功能的完善，而將專業(yè)的地理分析功能交由商業(yè)GIS軟件來(lái)實(shí)現(xiàn)。