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

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)'));

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

-- 查找距離 (15, 15) 點(diǎn) 10 公里內(nèi)的所有位置  
-- 假設(shè)地球半徑為 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;

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

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))'));  
-- 創(chuàng)建一個(gè)包含點(diǎn)的表  
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)'));

-- 假設(shè)我們想要檢查'Point 1'和'Point 2'是否分別位于'Polygon A'和'Polygon B'內(nèi)部  
-- 并計(jì)算它們與各自多邊形中心點(diǎn)的距離  
-- 首先，我們需要計(jì)算每個(gè)多邊形的中心點(diǎn)  
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))'));  
-- 然后，我們可以使用這些中心點(diǎn)與點(diǎn)表中的點(diǎn)進(jìn)行比較和距離計(jì)算  
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')  
    );