MySQL空間函式實現位置打卡

古道發表於2020-08-15
MySql函式

專案需求是跟使用者當前位置判斷是否在給定的地理位置範圍內，符合位置限制才可以打卡，其中的位置範圍是一個或多個不規則的多邊形。如下圖，判斷使用者是在清華還是北大。
地理位置

圖形獲取區域座標

因為專案前端使用微信小程式的wx.getLocation獲取地理位置，為了座標的一致性，後臺選取區域範圍採用了騰訊地圖的地理位置服務，在應用工具->繪製幾何圖形裡，提供了點、線、多邊形和圓形可以方便的選取看這裡。
在官方提供的示例上稍加改動即可獲取選定的位置座標。
提取位置

儲存位置

取到座標位置後，接著就是怎麼儲存？
開放地理空間聯盟(OGC)是一個由 250多家公司，機構和大學組成的國際聯盟，參與開發公開可用的空間解決方案，這些解決方案可用於管理空間資料的各種應用程式。OGC釋出了地理資訊的 OpenGIS®Implementation 標準，該規範可從 OGC 網站http://www.opengeospatial.org/standards/sfs獲得。為了遵循 OGC 規範，MySQL 將空間 extensions 實現為具有 Geometry Types 環境的 SQL 的子集，提供生成、儲存、分析空間的功能。總之，MySQL可以滿足我們的需求。
MySQL提供單個的儲存型別 POINT、LINESTRING、POLYGON 對應幾何圖形點、線、多邊形，GEOMETRY 可以儲存三種中的任何一種。同時擁有儲存多種型別的能力， MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION依次對應單個圖形的複數。
回到專案中，我們用到的是 POLYGON ，
建表語句 如下：

CREATE TABLE `polygon` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `polygon` polygon NOT NULL,
  PRIMARY KEY (`id`),
  SPATIAL KEY `d` (`polygon`)
) DEFAULT CHARSET=utf8;

插入資料
MySQL 支援將Well-Known 文字(WKT)格式和Well-Known 二進位制(WKB)格式兩種格式轉換為object型別儲存起來，我們使用更易於理解的WKT格式。對WKB感興趣的可以看這裡。
插入語句如下：

INSERT INTO `polygon` VALUES ('1', '清華大學', GeomFromText('POLYGON((
40.01169924229143 116.31565081888039,39.99304082299905 116.31616541796757,39.99343506780591 116.33297565023167,40.00237067000859 116.33743550702275,40.01340715321479 116.33057418815224,40.01169924229143 116.31565081888039))'));

INSERT INTO `polygon` VALUES ('2', '北京大學', GeomFromText('POLYGON((39.99711457525893 116.30450117461078,39.98673259872773 116.30535884106575,39.98673259872773 116.31702308311287,39.99963848242885 116.31598375134854,39.99711457525893 116.30450117461078))'));

需要注意的是騰訊地圖返回的多邊形的點不是閉合的，而polygon函式需要為了確定多邊形是否閉合要求第一個點和最後一個點是一樣的。如果不是閉合的polygon返回的結果將是NULL，插入語句就會執行失敗。
如果幾何滿足諸如此(非窮舉)列表中的條件，則它在語法上是 well-formed：

  • 線串至少有兩個點
  • 多邊形至少有一個環
  • 多邊形環關閉(第一個和最後一個點相同)
  • 多邊形環至少有 4 個點(最小多邊形是一個三角形，第一個和最後一個點相同)
  • 集合不為空(除了GeometryCollection)

查詢判斷

SELECT * FROM polygon WHERE
	MBRWithin (ST_GeomFromText('POINT(39.991333490218544 116.30964748487895)'), polygon);
# 在北京大學

SELECT * FROM polygon WHERE
	MBRWithin (ST_GeomFromText('POINT(39.988967560246685 116.3286905102832)'), polygon);
# 不在北大

細心的同學可能發現了這裡的查詢語句裡用的是函式，在以往的SQL裡如果存在查詢欄位上使用函式必然導致索引失效、全表掃描，但是在空間資料上不會，先看 EXPLAIN 語句和結果：
索引結果

可見MySQL空間型別的資料同樣可以建立索引，使用的關鍵詞是 SPATIAL
用法如下：

CREATE TABLE geom (g GEOMETRY NOT NULL);
CREATE SPATIAL INDEX g ON geom (g);

常用的空間計算函式

1、判斷兩點之間的距離
ST_Distance(g1，g2)，返回g1和g2之間的距離。如果任一引數是NULL或空幾何，則 return value 為NULL。
2、圖形1是否完全包含圖形2
ST_Contains(g1，g2)，返回 1 或 0 以指示g1是否完全包含g2。還可以用ST_Within(g2,g1)達到相同的效果。
3、不相交
ST_Disjoint(g1，g2)，返回 1 或 0 以指示g1是否在空間上與(不相交)g2不相交。
4、關於圖形相交的情況比較複雜，包含重疊、外相交等情況，具體可以看這裡

總結

本文通過一個地理位置打卡的需求，使用 MySQL 自帶的 Polygon 資料型別實現了空間資料的儲存，用ST_Contains(g1，g2) 函式代入了後臺預置的地理區域和前端獲取到的使用者地理位置可以得出使用者是否在打卡範圍內。其中還涉及到了 MySQL 在使用函式作為查詢欄位的情況下依然可以使用索引，最後延伸了一些其他的空間處理函式。

相關文章