MySQL空間函數及記錄經緯度并進行計算詳解
一、空間數據類型
在 MySQL 空間數據類型中，GEOMETRY 是所有空間類型的“基類”，其余（POINT、LINESTRING 等）都是 GEOMETRY 的具體子類，用于存儲不同形態(tài)的地理/幾何信息（如單個點、線、區(qū)域、多個點集合等）。這些類型的核心作用是結構化存儲空間數據，配合空間函數（如距離計算、范圍判斷）和空間索引，實現高效的地理/幾何運算。
1、GEOMETRY：核心基類
作用：
所有空間數據類型的父類型（抽象基類），本身不直接存儲具體的空間形態(tài)，而是用于：
1.兼容所有子類（如 POINT、POLYGON 都可賦值給 GEOMETRY 類型字段）；
2.統(tǒng)一處理混合類型的空間數據（如查詢時返回任意類型的空間結果）。
關鍵特點：
不能直接用 INSERT 插入數據（無具體形態(tài)），需通過子類的 WKT/函數生成后賦值；
支持所有空間函數（如 ST_SRID()、ST_AsText()），可接收任意子類的空間對象。
適用場景：
需存儲“不確定形態(tài)”的空間數據（如同時存儲點、線、面）；
通用化的空間數據接口（如函數參數、存儲過程返回值）。
-- 定義 GEOMETRY 類型字段（兼容所有空間類型）
CREATE TABLE spatial_data (
id INT PRIMARY KEY AUTO_INCREMENT,
geom GEOMETRY NOT NULL SRID 4326 COMMENT '兼容任意空間類型'
);
-- 插入 POINT 類型數據（自動兼容 GEOMETRY 字段）
INSERT INTO spatial_data (geom) VALUES (ST_MakePoint(116.4, 39.9));
-- 插入 POLYGON 類型數據（同樣兼容）
INSERT INTO spatial_data (geom) VALUES (
ST_GeomFromText('POLYGON((116.3 39.8, 116.5 39.8, 116.5 40.0, 116.3 40.0, 116.3 39.8))', 4326)
);
2、POINT：單個點（重要）
作用：
存儲二維平面/地球表面的單個坐標點（核心用于經緯度、具體位置）。
關鍵特點：
由一對坐標 (X, Y) 組成（地理場景：X=經度，Y=緯度）；
必須指定 SRID（如 4326 對應 WGS84 經緯度）；
是經緯度存儲的最優(yōu)類型（之前對話重點講過）。
實際場景：
存儲 POI 坐標（如商場、景點、地址）；
設備定位點（如手機GPS、車輛定位）。
-- WKT 格式：POINT(X Y)（經度在前，緯度在后 貌似我的版本正好是反過來的？很奇怪）
ST_GeomFromText('POINT(116.4042 39.9153)', 4326)
-- 簡化函數（MySQL 8.0.12+）：ST_MakePoint(X, Y)（自動繼承字段 SRID）
ST_MakePoint(116.4042, 39.9153)
3、LINESTRING：線串（折線）
作用：
存儲由多個有序點連接而成的連續(xù)線段（可理解為“折線”，非曲線）。
關鍵特點：
由 2 個及以上 POINT 組成（點的順序決定線的走向）；
點之間是直線連接，可自交（但通常用于無自交的連續(xù)線）；
坐標單位與 SRID 一致（4326 為度，3857 為米）。
實際場景：
道路、鐵路、航線（如公交路線、飛機航線）；
河流、管道、邊界線（如省界的一段）；
軌跡（如跑步路線、車輛行駛軌跡）。
-- WKT 格式：LINESTRING(點1 點2 點3 ...)（點之間用空格分隔）
-- 示例：北京到天津的航線（簡化為3個途經點）
ST_GeomFromText('LINESTRING(116.4042 39.9153, 116.8 39.95, 117.2 39.13)', 4326)
4、POLYGON：多邊形（閉合區(qū)域）
作用：
存儲由閉合線串圍成的區(qū)域（可包含“孔洞”），用于表示“面狀”地理范圍。
關鍵特點：
核心是“閉合線串”：外邊界的首尾點必須完全相同（否則 MySQL 會自動補全，但不推薦）；
支持“孔洞”：可包含多個線串，第一個是外邊界，后續(xù)是內邊界（孔洞，如區(qū)域內的湖泊）；
點的順序：外邊界按“順時針”或“逆時針”排列，內邊界（孔洞）順序相反（避免交叉）。
實際場景：
行政區(qū)域（如城市、區(qū)縣、省份范圍）；
地塊、園區(qū)（如工業(yè)園區(qū)、校園范圍）；
地理圍欄（如“電子圍欄內的設備報警”）。
-- 1. 簡單多邊形（無孔洞）：北京市東城區(qū)范圍（簡化）
ST_GeomFromText('POLYGON((116.35 39.88, 116.45 39.88, 116.45 39.95, 116.35 39.95, 116.35 39.88))', 4326)
-- 2. 帶孔洞的多邊形（區(qū)域內有湖泊）
ST_GeomFromText('POLYGON(
(116.35 39.88, 116.45 39.88, 116.45 39.95, 116.35 39.95, 116.35 39.88), -- 外邊界
(116.38 39.90, 116.42 39.90, 116.42 39.93, 116.38 39.93, 116.38 39.90) -- 內邊界（孔洞）
)', 4326)
5、復合類型（MULTI 開頭）：多個同類型幾何對象
用于存儲多個結構相同的空間對象（如多個點、多條線、多個多邊形），且這些對象之間相互獨立、不重疊（邏輯上是“集合”）。
6、MULTIPOINT：多點集合
作用：
存儲多個獨立的 POINT（無關聯(lián)，不連接）。
關鍵特點：
多個點之間無順序要求，互不連接；
適合存儲“一組離散的點”（無需每條記錄存一個 POINT）。
實際場景：
多個POI集合（如同一品牌的所有門店坐標）；
傳感器部署點、監(jiān)測點（如多個空氣質量監(jiān)測站）。
-- 示例：某城市的多個公交站點坐標
ST_GeomFromText('MULTIPOINT((116.40 39.91), (116.41 39.92), (116.42 39.90), (116.39 39.93))', 4326)
7、MULTILINESTRING：多線集合
作用：
存儲多條獨立的 LINESTRING（互不連接，無順序要求）。
關鍵特點：
每條線串都是獨立的（如多條不相交的道路）；
避免重復創(chuàng)建多條 LINESTRING 記錄，簡化數據管理。
實際場景：
多條道路、水系（如一個區(qū)域內的所有河流）；
多條航線、軌跡（如某航空公司的多條航線）。
-- 示例：某城市的3條主要道路
ST_GeomFromText('MULTILINESTRING(
(116.35 39.88, 116.45 39.88), -- 道路1（東西向）
(116.40 39.88, 116.40 39.95), -- 道路2（南北向）
(116.38 39.90, 116.42 39.93) -- 道路3（斜向）
)', 4326)
8、MULTIPOLYGON：多面集合
作用：
存儲多個獨立的 POLYGON（互不重疊、不相交）。
關鍵特點：
每個多邊形都是獨立的（如多個城市、多個地塊）；
適合存儲“一組區(qū)域”（如省份包含的所有區(qū)縣）。
實際場景：
行政區(qū)域集合（如省份、國家包含的所有下級區(qū)域）；
多個地塊、園區(qū)（如一個開發(fā)區(qū)內的多個工廠地塊）。
-- 示例：京津冀地區(qū)的3個主要城市范圍（簡化）
ST_GeomFromText('MULTIPOLYGON(
-- 北京
((116.3 39.8, 116.6 39.8, 116.6 40.1, 116.3 40.1, 116.3 39.8)),
-- 天津
((117.1 39.0, 117.4 39.0, 117.4 39.3, 117.1 39.3, 117.1 39.0)),
-- 石家莊
((114.2 38.0, 114.6 38.0, 114.6 38.4, 114.2 38.4, 114.2 38.0))
)', 4326)
9、混合集合：GEOMETRYCOLLECTION
作用：
存儲多種不同類型的空間對象（如 POINT + LINESTRING + POLYGON），是最靈活的空間類型（但使用頻率較低）。
關鍵特點：
可包含任意空間類型（單個或復合類型），如“一個點 + 一條線 + 一個多邊形”；
無類型限制，但可讀性和查詢效率較低（不推薦頻繁使用）；
需確保所有對象的 SRID 一致（否則空間函數會報錯）。
實際場景：
復雜地理數據集（如一個區(qū)域內的所有地理要素：點、線、面）；
臨時數據存儲（如批量導入的混合類型空間數據）。
-- 示例：某區(qū)域的“景點（點）+ 游覽路線（線）+ 景區(qū)范圍（面）”
ST_GeomFromText('GEOMETRYCOLLECTION(
POINT(116.4042 39.9153), -- 天安門（點）
LINESTRING(116.40 39.91, 116.41 39.92), -- 游覽路線（線）
POLYGON((116.39 39.90, 116.42 39.90, 116.42 39.93, 116.39 39.93, 116.39 39.90)) -- 景區(qū)范圍（面）
)', 4326)
二、使用
1、POINT相關
（1）建表與索引
CREATE TABLE `poi` ( `id` INT PRIMARY KEY AUTO_INCREMENT COMMENT '主鍵ID', `name` VARCHAR(100) NOT NULL COMMENT '地點名稱', `location` POINT SRID 4326 NOT NULL COMMENT '經緯度（POINT類型，WGS84坐標系）', `address` VARCHAR(255) COMMENT '詳細地址', -- 創(chuàng)建空間索引（加速空間查詢） SPATIAL INDEX `idx_spatial_location` (`location`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='POI地點表';
（2）ST_PointFromText()：從 WKT 構造點（推薦，顯式指定 SRID）
-- 語法：ST_PointFromText('POINT(緯度 經度)', SRID)
INSERT INTO poi (name, location, address)
VALUES (
'天安門',
ST_PointFromText('POINT(39.914885 116.403874)', 4326), -- 經度116.403874，緯度39.914885
'北京市東城區(qū)東長安街'
), (
'上海外灘',
ST_PointFromText('POINT(31.235924 121.490171)', 4326), -- 經度121.490171，緯度31.235924
'上海市黃浦區(qū)中山東一路'
);
（3）ST_GeomFromText()：通用幾何對象創(chuàng)建函數
INSERT INTO poi (name, location, address)
VALUES (
'廣州塔',
ST_GeomFromText('POINT(23.113500 113.330430)', 4326),
'廣州市海珠區(qū)閱江西路222號'
);
（4）ST_SRID()：通過對象創(chuàng)建
INSERT INTO poi (name, location, address) VALUES ( '深圳市民中心', ST_SRID(Point(114.057865, 22.543096), 4326), -- Point(經度, 緯度) '深圳市福田區(qū)福中三路' );
（5）ST_X、ST_Y查詢x、y坐標（不適用經緯度）
mysql> SELECT ST_X(Point(56.7, 53.34)); +--------------------------+ | ST_X(Point(56.7, 53.34)) | +--------------------------+ | 56.7 | +--------------------------+
mysql> SELECT ST_Y(Point(56.7, 53.34)); +--------------------------+ | ST_Y(Point(56.7, 53.34)) | +--------------------------+ | 53.34 | +--------------------------+
（6）ST_AsText(), ST_AsWKT()：轉換為字符串、WKT格式
mysql> SET @g = 'LineString(1 1,2 2,3 3)'; mysql> SELECT ST_AsText(ST_GeomFromText(@g)); +--------------------------------+ | ST_AsText(ST_GeomFromText(@g)) | +--------------------------------+ | LINESTRING(1 1,2 2,3 3) | +--------------------------------+
（7）ST_Longitude()、ST_Latitude()：查詢經緯度
SELECT id, name, ST_Longitude(location) AS longitude, -- 提取經度 ST_Latitude(location) AS latitude, -- 提取緯度 address FROM poi;
（8）ST_Distance()：點的平面距離（不適用經緯度）
mysql> SET @g1 = ST_GeomFromText('POINT(1 1)');
mysql> SET @g2 = ST_GeomFromText('POINT(2 2)');
mysql> SELECT ST_Distance(@g1, @g2);
+-----------------------+
| ST_Distance(@g1, @g2) |
+-----------------------+
| 1.4142135623730951 |
+-----------------------+
mysql> SET @g1 = ST_GeomFromText('POINT(1 1)', 4326);
mysql> SET @g2 = ST_GeomFromText('POINT(2 2)', 4326);
mysql> SELECT ST_Distance(@g1, @g2);
+-----------------------+
| ST_Distance(@g1, @g2) |
+-----------------------+
| 156874.3859490455 |
+-----------------------+
mysql> SELECT ST_Distance(@g1, @g2, 'metre');
+--------------------------------+
| ST_Distance(@g1, @g2, 'metre') |
+--------------------------------+
| 156874.3859490455 |
+--------------------------------+
mysql> SELECT ST_Distance(@g1, @g2, 'foot');
+-------------------------------+
| ST_Distance(@g1, @g2, 'foot') |
+-------------------------------+
| 514679.7439273146 |
+-------------------------------+
（9）ST_Distance_Sphere()：基于球體（地球）計算兩點直線距離
SELECT ST_Distance_Sphere ( ST_PointFromText ( 'POINT(39.914885 116.403874)', 4326 ), ST_PointFromText ( 'POINT(31.235924 121.490171)', 4326 ) ) AS distance_m;
2、實戰(zhàn)
（1）查詢范圍內的點
-- 步驟1：構造目標點（天安門經緯度）
-- 步驟2：計算每個POI到目標點的距離（米）
-- 步驟3：篩選距離≤5000米的結果，按距離升序排序
SELECT
id,
name,
address,
ST_Longitude(location) AS longitude,
ST_Latitude(location) AS latitude,
-- 計算距離（米），保留2位小數
ROUND(ST_Distance_Sphere(
location,
ST_PointFromText('POINT(39.914883 116.403873)', 4326) -- 目標點：天安門
), 2) AS distance_m
FROM poi
WHERE
-- 先通過經緯度范圍粗篩（減少計算量，優(yōu)化性能）
ST_Longitude(location) BETWEEN 116.403872 - 0.05 AND 116.403874 + 0.05 -- 經度范圍（≈5.5公里）
AND ST_Latitude(location) BETWEEN 39.914883 - 0.05 AND 39.914885 + 0.05 -- 緯度范圍（≈5.5公里）
-- 再精確篩選5公里內
AND ST_Distance_Sphere(
location,
ST_PointFromText('POINT(39.914885 116.403874)', 4326)
) < 5000
ORDER BY distance_m ASC; -- 按距離從近到遠排序
參考資料
https://dev.mysql.com/doc/refman/8.0/en/spatial-types.html
https://dev.mysql.com/doc/refman/8.0/en/spatial-function-reference.html
到此這篇關于MySQL空間函數及記錄經緯度并進行計算的文章就介紹到這了,更多相關MySQL空間函數記錄經緯度內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
DML是一類用于查詢和操作數據的 SQL 語句,DDL是一類用于定義和管理數據庫結構及對象的 SQL 語句,本文為大家整理了一些DML與DDL的常用語句,希望對大家有所幫助2023-12-12
這篇文章主要為大家詳細介紹了Linux環(huán)境Mysql5.7.12安裝教程，感興趣的小伙伴們可以參考一下2016-06-06
今天教大家怎么下載安裝MySQL msi版本,文中有非常詳細的圖文解說,對不會下載安裝mysql的小伙伴們很有幫助,需要的朋友可以參考下2021-05-05
這篇文章主要介紹了MySQL?配置主從復制實踐分享，通過檢測通信展開詳情，具有一定的參考價值，需要的小伙伴可以參考一下2022-07-07
mysql數據庫中l(wèi)imit子句可以被用于強制select語句返回指定的記錄數,這篇文章主要介紹了mysql中l(wèi)imit用法詳解及注意事項的相關資料,文中通過代碼介紹的非常詳細,需要的朋友可以參考下2026-01-01
這篇文章主要介紹了PHP使用mysqli擴展連接MySQL數據庫,需要的朋友可以參考下2014-08-08
MySQL 5.6 & 5.7最優(yōu)配置文件模板(my.ini)
這篇文章主要介紹了MySQL 5.6 & 5.7最優(yōu)配置文件模板(my.ini),需要的朋友可以參考下2016-07-07
在分布式數據庫和高并發(fā)應用場景下,數據一致性 和 數據延遲 是兩個不可忽視的問題,MySQL 作為廣泛使用的關系型數據庫,提供了多種機制來保障數據一致性,本文將深入探討 MySQL 如何保障數據一致性 以及 應對數據延遲的策略,需要的朋友可以參考下2025-03-03
這篇文章主要給大家介紹了關于MySQL中表的幾種連接方式，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2020-11-11
本文主要介紹了MySQL索引失效的幾種情況小結，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2023-03-03