MySQL中地理位置資料擴充套件geometry的使用心得

MySQL · 發表 2018-09-18 20:49:28

摘要：最近學習了些MySQL geometry資料儲存和計算，在這裡記錄下。 1. 環境 geometry推薦在5.6版本以上使用，儘管大部分功能在5.5已經可用，除了距離計算函式st_distance等新增函式。 2. Geometry主要相關類 2.1 Geometry Ge...

最近學習了些SQL/">MySQL geometry資料儲存和計算，在這裡記錄下。

1. 環境

geometry推薦在5.6版本以上使用，儘管大部分功能在5.5已經可用，除了距離計算函式st_distance等新增函式。

2. Geometry主要相關類

2.1 Geometry

Geometry是所有此擴充套件中型別得基類，其他型別如Point,LineString,Polygon都是Geometry的子類。Geometry有一些屬性，這些屬性是所有其他幾何類的共有屬性：

type: 型別（Point, LineString,...）

SRID: 該值確定了用於描述定義幾何物件的座標空間的空間座標系統，參考連結：https://www.cnblogs.com/Joetao/articles/2086846.html

coordinates: 座標值

interior, boundary, exterior: interior是幾何物件所展空間的部分，boundary是幾何物件的邊界，exterior是幾何物件未佔有的空間。

MBR: 能夠覆蓋幾何物件的最小矩形，可以想象成信封，它由幾何物件中最大最小的座標值組合而成：

( (MINX MINY , MAXX MINY , MAXX MAXY , MINX MAXY , MINX MINY ) )

simple/nonsimple: 幾何物件是否簡單

closed/not closed: 幾何物件是否封閉

dimension: 維度數（Point: 0, LineString: 1, Polygon: 2）

2.2 Point

顧名思義就是點，有一個座標值，沒有長度、面積、邊界。

2.3 LineString

顧名思義就是線，由一系列點連線而成。

如果線從頭至尾沒有交叉，那就是簡單的（simple）

如果起點和終點重疊，那就是封閉的（closed）

2.4 Polygon

多邊形。可以是一個實心平面形，即沒有內部邊界，也可以有空洞，類似鈕釦。

2.5 MultiPoint, MultiLineString, MultiPolygon, GeometryCollection

這4種類型都是集合類，是多個Point、LineString或Polygon組合在一起而成。

3. 幾何物件在MySQL中的資料格式

在MySQL中有3種表達幾何物件的格式：

-->WKT（文字格式）

-->WKB（二進位制格式）

-->MySQL內部儲存格式

其中WKT格式簡單易讀，在這裡著重介紹：

3.1 WKT

3.1.1 Point

POINT(121.213342 31.234532)

經度(longitude)在前，維度(latitude)在後，用空格分隔

3.1.2 LineString

LINESTRING(121.342423 31.542423,121.345664 31.246790,121.453178 31.456862)

點與點之間用逗號分隔；一個點中的經緯度用空格分隔，與POINT格式一致

3.1.3 Polygon

POLYGON((121.342423 31.542423,121.345664 31.246790,121.453178 31.456862),(121.563633 31.566652,121.233565 31.234565,121.568756 31.454367))

由一個表示外部邊界的LineString和0個或多個表示內部邊界的LineString組成，最簡單的就是隻有一個外邊界的情況：POLYGON((0 0,10,0 10 10, 0 10))

3.1.4 集合類格式

MULTIPOINT(0 0, 20 20, 60 60)

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))

4. 幾何物件建立函式

MySQL表中的幾何物件有它自己的內部格式，我們需要將幾何物件從方便輸入的WKT格式轉換為其內部格式，才能進行進一步的儲存，計算等。

這裡主要講解使用WKT格式的函式，對於集合類物件的建立函式由於較少使用也不再列舉

GeomFromText(wkt): 建立一個任何型別的幾何物件Geometry

PointFromText(wkt): 建立一個Point物件

LineStringFromText(wkt): 建立一個LineString物件

PolygonFromText(wkt): 建立一個Polygon物件

5. 建立支援空間幾何物件的表

5.1 建立表

以下是我建立的一個樣例：

CREATE TABLE `t_geo_test` (

`ID` int(11) NOT NULL AUTO_INCREMENT,

`NAME` varchar(64) NOT NULL,

`SHAPE` geometry NOT NULL,

PRIMARY KEY (`ID`)

) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

這裡的欄位SHAPE就是儲存幾何物件的，型別為geometry，可以支援point,linestring,polygon等任意幾何物件。

引擎需要使用MyISAM。

表結構：

5.2 插入資料

5.2.1 插入點資料

INSERT INTO `t_geo_test` (ID,NAME,SHAPE) VALUES (1, 'P1', geomFromText('POINT(121.474103 31.232862)'));

5.2.2 插入線資料

INSERT INTO `t_geo_test` (ID,NAME,SHAPE) VALUES (2, 'L1', geomFromText('LINESTRING(121.474103 31.232862,121.472462 31.231339,121.471984 31.232821)'));

5.2.3 插入多邊形資料

INSERT INTO `t_geo_test` (ID,NAME,SHAPE) VALUES (5, 'POLYGON_1', geomfromtext('POLYGON((121.474243 31.234504, 121.471775 31.233348, 121.470724 31.23155, 121.471603 31.230229, 121.472655 31.230357, 121.475777 31.232045, 121.474243 31.234504))'));

5.3 獲取資料

AsText(): 此函式能將幾何物件的內部儲存格式轉換為WKT格式

6. 常用函式

6.1 獲取幾何物件屬性的函式

6.1.1 Geometry

-->Dimension(g)

返回物件g的維數

-->Envelope(g)

返回物件g的最小邊界矩形（MBR）。結型別為Polygon值。

-->GeometryType(g)

以字串形式返回幾何型別的名稱，如POINT,LINESTRING

-->IsClosed(g)

返回物件g是否封閉

-->IsSimple(g)

返回物件g是否簡單

6.1.2 Point

-->X(p)

以雙精度數值返回點p的X座標值(經度)。

-->Y(p)

以雙精度數值返回點p的Y座標值(緯度)。

6.1.3 LineString

-->EndPoint(line)

返回物件line的最後一個點Point

-->StartPoint(line)

返回物件line的第一個點Point

-->PointN(line, N)

返回物件line中第N個點，N從1開始

6.1.4 Polygon

-->ExteriorRing(poly)

返回物件poly的外環，型別為LineString

-->InteriorRingN(poly, N)

返回物件poly的第N個內環，N從1開始

-->NumInteriorRings(poly)

返回物件poly的neihuan個數

6.2 從現成幾何物件建立新的物件

6.2.1 st_union(g1, g2)

將g1和g2合併為一個集合類物件

SET @g1 = geomFromText('POLYGON((121.474243 31.234504,121.471775 31.233348,121.470724 31.23155,121.471603 31.230229,121.472655 31.230357,121.475777 31.232045,121.474243 31.234504))');

SET @g2 = geomFromText('POLYGON((121.474243 31.234804,121.471775 31.233948,121.471724 31.23155,121.471903 31.230229,121.472655 31.230157,121.475777 31.231045,121.474243 31.234804))');

SELECT st_union(@g1, @g2);

結果：

POLYGON((121.472655 31.230157, 121.471903 31.230229, 121.471898134093 31.2302649098516, 121.471603 31.230229, 121.470724 31.23155, 121.471761757556 31.2333253454665, 121.471775 31.233948, 121.474243 31.234804, 121.474597 31.2339365384615, 121.475777 31.232045, 121.475442678789 31.2318642395248, 121.475777 31.231045, 121.472655 31.230157))

6.2.2 st_difference(g1, g2)

返回幾何物件，該物件表示了幾何值g1與g2的點集合差異

SET @g1 = geomFromText('POLYGON((121.474243 31.234504,121.471775 31.233348,121.470724 31.23155,121.471603 31.230229,121.472655 31.230357,121.475777 31.232045,121.474243 31.234504))');

SET @g2 = geomFromText('POLYGON((121.474243 31.234804,121.471775 31.233948,121.471724 31.23155,121.471903 31.230229,121.472655 31.230157,121.475777 31.231045,121.474243 31.234804))');

SELECT st_difference(@g1,@g2);

結果：

MULTIPOLYGON(((121.471603 31.230229, 121.470724 31.23155, 121.471761757556 31.2333253454665, 121.471724 31.23155, 121.471898134093 31.2302649098516, 121.471603 31.230229)), ((121.475442678789 31.2318642395248, 121.474597 31.2339365384615, 121.475777 31.232045, 121.475442678789 31.2318642395248)))

6.2.3 st_intersection(g1,g2)

返回幾何物件，該物件表示了幾何值g1與g2的點集合交集

SET @g1 = geomFromText('POLYGON((121.474243 31.234504,121.471775 31.233348,121.470724 31.23155,121.471603 31.230229,121.472655 31.230357,121.475777 31.232045,121.474243 31.234504))');

SET @g2 = geomFromText('POLYGON((121.474243 31.234804,121.471775 31.233948,121.471724 31.23155,121.471903 31.230229,121.472655 31.230157,121.475777 31.231045,121.474243 31.234804))');

SELECT st_intersection(@g1,@g2);

結果：

POLYGON((121.471898134093 31.2302649098516, 121.471724 31.23155, 121.471761757556 31.2333253454665, 121.471775 31.233348, 121.474243 31.234504, 121.474597 31.2339365384615, 121.475442678789 31.2318642395248, 121.472655 31.230357, 121.471898134093 31.2302649098516))

6.3 幾何物件之間空間關係的函式

6.3.1 st_contains(g1, g2)

返回1: g1完全包含g2；返回0: g1未包含g2

6.3.2 st_crosses(g1, g2), st_intersects(g1, g2)

返回1: g1與g2相交；返回0:g1與g2未相交

6.3.3 st_disjoint(g1, g2)

是st_crosses的反函式

6.3.4 st_within(g1, g2)

g1在g2內則返回1，否則返回0

7. 空間索引

對錶中的geometry型別的欄位進行索引可以優化搜尋，MySQL中通過對Geometry物件的MBR建立索引

建立：

CREATE SPATIAL INDEX i_shape ON `t_geo_test`(SHAPE);

刪除：

DROP INDEX i_shape ON `t_geo_test`;

8.一些注意事項

8.1 目前MySQL中支援的空間座標系統沒有gcj02,bd09等國內座標系，預設使用WGS84地球座標系，所以在建立幾何物件時輸入的座標值儘量使用WGS84座標，以避免誤差。

8.2 MySQL中的計算距離，長度，面積等絕對數值的空間計算函式（area(), GLength(), st_distance()）存在一定的誤差，儘量不要使用。

Linux公社的RSS地址： ofollow,noindex" target="_blank">https://www.linuxidc.com/rssFeed.aspx

本文永久更新連結地址： https://www.linuxidc.com/Linux/2018-09/154191.htm

MySQL中地理位置資料擴充套件geometry的使用心得

您可能也會喜歡…