mysql儲存地理資訊的方法

TechSynapse發表於2024-05-27
MySql

MySQL 儲存地理資訊通常使用 GEOMETRY 資料型別或其子型別（如 POINT, LINESTRING, POLYGON 等）。為了支援這些資料型別，MySQL 提供了 SPATIAL 索引，這允許我們執行高效的地理空間查詢。

1. 建立支援地理資訊的表

首先，我們需要一個包含 GEOMETRY 或其子型別列的表。以下是一個示例，展示如何建立一個包含 POINT 型別的表：

CREATE TABLE locations (  
    id INT AUTO_INCREMENT PRIMARY KEY,  
    name VARCHAR(255) NOT NULL,  
    position POINT NOT NULL,  
    SPATIAL INDEX(position)  -- 為位置列建立空間索引  
) ENGINE=InnoDB;

2. 插入地理資訊資料

我們可以使用 GeomFromText() 或 PointFromText() 函式插入地理資料。以下是如何插入一個點的示例：

INSERT INTO locations (name, position)  
VALUES ('Location A', GeomFromText('POINT(10 20)'));  
-- 或者使用 PointFromText  
INSERT INTO locations (name, position)  
VALUES ('Location B', PointFromText('POINT(30 40)'));

3. 查詢地理資訊資料

我們可以使用 MBRContains(), Distance_Sphere(), ST_Distance_Sphere() 等函式來查詢地理資料。以下是一些示例：

3.1查詢指定矩形區域內的位置

-- 查詢位置在 (0, 0) 到 (20, 20) 矩形區域內的所有位置  
SELECT * FROM locations  
WHERE MBRContains(  
    GeomFromText('POLYGON((0 0, 20 0, 20 20, 0 20, 0 0))'),  
    position  
);

3.2查詢距離特定點一定距離內的位置

注意：這裡使用了 Distance_Sphere() 函式，它基於地球是完美球體的假設。對於更精確的計算，我們可以使用 ST_Distance_Sphere() 並指定地球半徑。

-- 查詢距離 (15, 15) 點 10 公里內的所有位置  
-- 假設地球半徑為 6371 公里（平均半徑）  
SELECT *, (6371 * acos(cos(radians(15))   
  * cos(radians(X(position)))   
  * cos(radians(Y(position)) - radians(15))   
  + sin(radians(15))   
  * sin(radians(X(position))))) AS distance_km   
FROM locations   
HAVING distance_km < 10;

3.3使用 ST_Distance_Sphere() 查詢距離

這是一個更精確的距離計算示例，它使用 ST_Distance_Sphere() 函式並指定地球的平均半徑。

-- 查詢距離 (15, 15) 點 10 公里內的所有位置  
SELECT *, ST_Distance_Sphere(point(15, 15), position, 6371) AS distance_km   
FROM locations   
HAVING distance_km < 10;

注意：上述查詢中的距離計算是基於 Haversine 公式的簡化版本，它假設地球是一個完美的球體。在實際應用中，我們可能需要使用更復雜的演算法來考慮地球的不規則形狀。

此外，我們還可以使用 MySQL 的其他地理空間函式和運算子來執行更復雜的地理空間查詢和操作。

4.查詢地理資訊進階示例

我們可以探討一個更復雜的示例，該示例涉及POLYGON地理資料型別，並使用ST_Contains函式來檢查一個點是否位於多邊形內部。同時，我們也會使用ST_Distance_Sphere函式來計算點與多邊形中心點的距離。

4.1建立表並插入資料

首先，我們建立一個包含POLYGON列的表，並插入一些多邊形資料。

CREATE TABLE polygons (  
    id INT AUTO_INCREMENT PRIMARY KEY,  
    name VARCHAR(255) NOT NULL,  
    shape POLYGON NOT NULL,  
    SPATIAL INDEX(shape)  
) ENGINE=InnoDB;  
  
INSERT INTO polygons (name, shape)  
VALUES ('Polygon A', GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'));  
INSERT INTO polygons (name, shape)  
VALUES ('Polygon B', GeomFromText('POLYGON((20 20, 30 20, 30 30, 20 30, 20 20))'));  
  
-- 建立一個包含點的表  
CREATE TABLE points (  
    id INT AUTO_INCREMENT PRIMARY KEY,  
    name VARCHAR(255) NOT NULL,  
    position POINT NOT NULL,  
    SPATIAL INDEX(position)  
) ENGINE=InnoDB;  
  
INSERT INTO points (name, position)  
VALUES ('Point 1', GeomFromText('POINT(5 5)'));  
INSERT INTO points (name, position)  
VALUES ('Point 2', GeomFromText('POINT(25 25)'));

4.2查詢點是否在多邊形內部，並計算距離

現在，我們可以編寫一個查詢來檢查點是否位於多邊形內部，並計算這些點與多邊形中心點的距離。

-- 假設我們想要檢查'Point 1'和'Point 2'是否分別位於'Polygon A'和'Polygon B'內部  
-- 並計算它們與各自多邊形中心點的距離  
  
-- 首先，我們需要計算每個多邊形的中心點  
SET @polygonA_center = ST_Centroid(GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'));  
SET @polygonB_center = ST_Centroid(GeomFromText('POLYGON((20 20, 30 20, 30 30, 20 30, 20 20))'));  
  
-- 然後，我們可以使用這些中心點與點表中的點進行比較和距離計算  
SELECT   
    p.name AS point_name,  
    p.position,  
    CASE   
        WHEN ST_Contains(pg.shape, p.position) THEN 'Inside'  
        ELSE 'Outside'  
    END AS location_status,  
    ST_Distance_Sphere(p.position, CASE pg.name WHEN 'Polygon A' THEN @polygonA_center ELSE @polygonB_center END, 6371) AS distance_km  
FROM   
    points p  
JOIN   
    polygons pg ON (  
        (p.name = 'Point 1' AND pg.name = 'Polygon A') OR  
        (p.name = 'Point 2' AND pg.name = 'Polygon B')  
    );

這個查詢首先計算了兩個多邊形的中心點，並使用JOIN語句將點表與多邊形表連線起來。它使用ST_Contains函式來檢查點是否位於多邊形內部，並使用ST_Distance_Sphere函式來計算點與對應多邊形中心點的距離（以公里為單位）。注意，我們使用了CASE語句來根據點的名稱選擇正確的多邊形中心點進行計算。

這個查詢將返回每個點的名稱、位置、是否在多邊形內部的狀態以及與對應多邊形中心點的距離。

相關文章