Blackbing Playground

淺談 MySql geom

發表於 |

##MySQL的Geom實在很不成才
MySQL的Geom實在很不成才，OpenGIS規範了這麼多function，就連這麼基本的Distance他都沒實做，跟postgress相比，真的是差了很多，然而系統都是在MySQL上，要移轉資料庫工程實在浩大，還是屈就於MySQL的淫威之下來解決問題。廢話不多說，來看一下如何創建geom欄位。

###建立、修改geom
這裡用到最重要的function是 GeomFromText, 以下例子都是用最常用到的POINT來做說明

1
UPDATE `poi` SET geom = GeomFromText('POINT(X Y)')

查詢落在bounding box裏頭的地標
這裡用到最重要的function是intersects，用來判斷兩個geom是否相集合，我們透過計算將bounding box的轉成polygon來做查詢

1
2
3
4
5
6
7
8
9
10
SELECT id, label, ASTEXT( geom )
FROM poi
WHERE INTERSECTS( geom, GEOMFROMTEXT( 'POLYGON((
121.561531961596 25.0360992913184,
121.56149683801432 25.041444032861694,
121.56846249577495 25.04122795598331,
121.56833620876884 25.036005306093482 ,
121.561531961596 25.0360992913184
))' ) )
LIMIT 0 , 10

###查詢落在bounding box裏的地標, 並計算距離
這裡用到兩點距離公式直接計算距離

1
2
3
4
5
6
7
8
9
10
11
SELECT id, label, ASTEXT( geom ) ,
SQRT( POW( ABS( X( geom ) - 25.037346023922883 ) , 2 ) + POW( ABS( Y( geom ) - 121.55710515578352 ) , 2 ) ) AS Dist
FROM poi
WHERE INTERSECTS( geom, GEOMFROMTEXT( 'POLYGON((
121.561531961596 25.0360992913184,
121.56149683801432 25.041444032861694,
121.56846249577495 25.04122795598331,
121.56833620876884 25.036005306093482 ,
121.561531961596 25.0360992913184
))' ) )
LIMIT 0 , 10

但是單位是經緯度，求出經緯度兩點的點距離人類無法理解，經緯度的座標轉換公式有點繁雜，可以參考經緯度轉換TWD97，在大部分的需求，距離只是一個大概的數字即可。

###神秘的1/111111
在中低緯度的地區（例如台灣），可以用這個參數來做很rough的經緯度/ 公尺轉換，例如：我要計算距離經度121.5 距離1000公尺的經度：121.5 + (1/111111 * 1000) = 121.509

因此，我們將上面的SQL做點小修改，乘上一個111111的參數，即可知道距離，當然也可以做排序囉。

1
2
3
4
5
6
7
8
9
10
11
SELECT id, label, ASTEXT( geom ) ,
ROUND(SQRT( POW( ABS( X( geom ) - 121.5644248807452 ) , 2 ) + POW( ABS( Y( geom ) - 25.033943775454475 ) , 2 ) )*111111) AS DIST
FROM poi
WHERE INTERSECTS( geom, GEOMFROMTEXT( 'POLYGON((
121.5509248807452 25.020443775454474,
121.5509248807452 25.047443775454475,
121.57792488074519 25.047443775454475,
121.57792488074519 25.020443775454474,
121.5509248807452 25.020443775454474
))' ) )
ORDER BY DIST

###參考資料：