MySQL中地理位置數據擴展geometry的使用心得

来源:https://www.cnblogs.com/hargen/archive/2018/09/18/9671087.html
-Advertisement-
Play Games

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


最近學習了些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()）存在一定的誤差，儘量不要使用。


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • device eth0 does not seem to be present..VMware虛擬機網路問題
    【問題來源】 因為虛擬機過大，所以直接在本地磁碟直接複製，啟動的時候，換好IP重新啟動網卡報錯。 device eth0 does not seem to be present.. ifconfig查看發現沒有網卡存在 【解決方法】 第一步： 將網卡eth0改為eth1 第二步: 查看eth1的ma ...
  • shell定時統計Nginx下access.log的PV併發送給API保存到資料庫
    shell定時統計Nginx下access.log的PV併發送給API保存到資料庫 ...
  • 小規模集群架構規劃
    "配置無人值守批量安裝系統（Cobbler）" "搭建PPTP VPN/ NTP/Firewalld內部共用上網 " "搭建跳板機服務jumpserver" "MySQL資料庫環境搭建" "MySQL資料庫主從搭建" "Memcahced/Redis 實現Session會話保持" "Rsync全網備 ...
  • Linux忘記root密碼，密碼找回，圖片展示
    忘記root密碼 CentOS 7參考地址如下：https://www.baidu.com/s?wd=CentOS7+%E6%89%BE%E5%9B%9Eroot%E5%AF%86%E7%A0%81&ie=UTF-8 CentOS 6版本如下： 在下麵這個界面按任意鍵，這個時候儘快按下任意鍵，只有5 ...
  • SQL Server基礎之表級觸發器
    觸發器分為兩種，一種與數據表綁定，響應數據表指定動作（insert、delete或update），此處稱為表級；一種與資料庫本身綁定，響應數據定義語句（主要是CREATE、ALTER 和 DROP 開頭的語句），此處稱為庫級。本篇以下所說觸發器皆指表級觸發器。 觸發器是一段有特定語法，實現一定功能的 ...
  • orcl資料庫查詢重覆數據及刪除重覆數據方法
    工作中，發現資料庫表中有許多重覆的數據，而這個時候老闆需要統計表中有多少條數據時（不包含重覆數據），只想說一句MMP，庫中好幾十萬數據，腫麽辦，無奈只能自己在網上找語句，最終成功解救，下麵是我一個實驗，很好理解。 假設有一張人員信息表cs（姓名，證件號，地址），將表中三個欄位數據都重覆的數據篩選出來 ...
  • 使用Dapper處理多個結果集和多重映射的教程
    在本文中，我們將介紹如何使用DAPPER從單個資料庫調用中讀取資料庫中的多個結果集。我們將看看我們可能希望這樣做的場景，以及如何使用它的Query和QueryMultiple方法更簡潔地實現這一點。 當我們談論以數據為中心的應用程式時，可能會出現一些場景，在這些場景中我們可能希望從資料庫中檢索多重結 ...
  • 集群時間同步
    1、查看本機是否安裝了ntpdate服務，安裝 時間同步軟體 ​ yum install -y ntpdate yum install -y ntp 2、同步時間 ​ ntpdate time.nist.gov // 同步時間 ​ 如果同步不了 ​ ntpdate time.nuri.net // ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類