-- 點類型（Point）
POINT( longitude latitude )
-- 線類型（LineString）
LINESTRING( point1, point2, point3, ... )
-- 多邊形類型（Polygon）
POLYGON( outer_ring, [inner_ring1, inner_ring2, ...] )
-- 多點類型（MultiPoint）
MULTIPOINT( point1, point2, ... )
-- 多線類型（MultiLineString）
MULTILINESTRING( linestring1, linestring2, ... )
-- 多多邊形類型（MultiPolygon）
MULTIPOLYGON( polygon1, polygon2, ... )
-- 幾何集合類型（GeometryCollection）
GEOMETRYCOLLECTION( geometry1, geometry2, ... )

-- 使用WGS84坐標系（SRID 4326）
POINT( longitude latitude ) SRID 4326
-- 使用Web墨卡托投影（SRID 3857）
POINT( x y ) SRID 3857

CREATE TABLE spatial_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    -- 點類型字段
    location POINT SRID 4326 NOT NULL,
    -- 多邊形類型字段
    area POLYGON SRID 4326,
    -- 線類型字段
    route LINESTRING SRID 4326,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    -- 創(chuàng)建空間索引
    SPATIAL INDEX idx_location (location),
    SPATIAL INDEX idx_area (area)
) ENGINE=InnoDB;

-- 創(chuàng)建空間索引
CREATE SPATIAL INDEX idx_geom ON spatial_data (location);
-- 查看索引信息
SHOW INDEX FROM spatial_data;

-- 使用WKT（Well-Known Text）格式插入點數據
INSERT INTO spatial_data (name, location) VALUES
('北京天安門', ST_GeomFromText('POINT(116.3974 39.9093)', 4326)),
('上海外灘', ST_GeomFromText('POINT(121.4903 31.2228)', 4326));
-- 插入多邊形數據（矩形區(qū)域）
INSERT INTO spatial_data (name, area) VALUES
('中關村科技園', ST_GeomFromText('POLYGON((116.300 39.980, 116.320 39.980, 116.320 39.960, 116.300 39.960, 116.300 39.980))', 4326));
-- 使用ST_Point函數插入點數據
INSERT INTO spatial_data (name, location) VALUES
('廣州塔', ST_Point(113.3233, 23.0994, 4326));

-- 查詢所有空間數據（以WKT格式顯示）
SELECT id, name, ST_AsText(location) as location_wkt 
FROM spatial_data;
-- 計算兩點之間的距離（單位：米）
SELECT 
    a.name as point1,
    b.name as point2,
    ST_Distance_Sphere(a.location, b.location) as distance_meters
FROM spatial_data a, spatial_data b
WHERE a.id = 1 AND b.id = 2;
-- 查詢特定范圍內的點（距離某點10公里內）
SET @center_point = ST_GeomFromText('POINT(116.3974 39.9093)', 4326);
SELECT name, ST_AsText(location) as location,
       ST_Distance_Sphere(location, @center_point) as distance
FROM spatial_data
WHERE ST_Distance_Sphere(location, @center_point) <= 10000
ORDER BY distance;

-- 判斷點是否在多邊形內
SELECT name, ST_AsText(location) as location,
       ST_Within(location, area) as within_area
FROM spatial_data
WHERE area IS NOT NULL;
-- 判斷兩個幾何對象是否相交
SELECT a.name as geom1, b.name as geom2,
       ST_Intersects(a.area, b.location) as intersects
FROM spatial_data a, spatial_data b
WHERE a.area IS NOT NULL AND b.location IS NOT NULL;
-- 計算凸包（Convex Hull）
SELECT name, ST_AsText(ST_ConvexHull(area)) as convex_hull
FROM spatial_data
WHERE area IS NOT NULL;

-- 緩沖區(qū)分析（Buffer Analysis）
SELECT name, ST_AsText(ST_Buffer(location, 0.01)) as buffer_zone
FROM spatial_data
WHERE location IS NOT NULL;
-- 計算幾何對象的面積
SELECT name, ST_Area(area) as area_sq_degrees
FROM spatial_data
WHERE area IS NOT NULL;
-- 轉換為不同坐標系
SELECT name, 
       ST_AsText(location) as wgs84,
       ST_AsText(ST_Transform(location, 3857)) as web_mercator
FROM spatial_data;

-- 創(chuàng)建商家表
CREATE TABLE businesses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    location POINT SRID 4326 NOT NULL,
    SPATIAL INDEX idx_location (location)
);
-- 插入測試數據
INSERT INTO businesses (name, category, location) VALUES
('星巴克咖啡', '餐飲', ST_Point(116.3974, 39.9093, 4326)),
('麥當勞', '餐飲', ST_Point(116.4000, 39.9100, 4326)),
('家樂福超市', '零售', ST_Point(116.3950, 39.9080, 4326));
-- 搜索用戶當前位置1公里內的商家
SET @user_location = ST_Point(116.3980, 39.9090, 4326);
SELECT name, category, 
       ROUND(ST_Distance_Sphere(location, @user_location), 2) as distance_meters
FROM businesses
WHERE ST_Distance_Sphere(location, @user_location) <= 1000
ORDER BY distance_meters;

-- 創(chuàng)建地理圍欄表
CREATE TABLE geo_fences (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    fence POLYGON SRID 4326 NOT NULL,
    SPATIAL INDEX idx_fence (fence)
);
-- 判斷設備是否進入特定區(qū)域
SET @device_location = ST_Point(116.3974, 39.9093, 4326);
SELECT name, 
       ST_Within(@device_location, fence) as inside_fence
FROM geo_fences
WHERE ST_Within(@device_location, fence) = 1;

-- 確保所有空間列都有索引
EXPLAIN SELECT * FROM spatial_data 
WHERE ST_Within(location, @search_area);
-- 使用MBR（Minimum Bounding Rectangle）函數優(yōu)化查詢
SELECT * FROM spatial_data 
WHERE MBRWithin(location, ST_Envelope(@search_area))
AND ST_Within(location, @search_area);

-- 按地理區(qū)域分區(qū)
CREATE TABLE spatial_data_partitioned (
    id INT AUTO_INCREMENT,
    location POINT SRID 4326,
    region VARCHAR(20),
    PRIMARY KEY (id, region)
) PARTITION BY LIST COLUMNS(region) (
    PARTITION p_north VALUES IN ('north'),
    PARTITION p_south VALUES IN ('south'),
    PARTITION p_east VALUES IN ('east'),
    PARTITION p_west VALUES IN ('west')
);

-- 驗證幾何對象的有效性
SELECT name, ST_IsValid(area) as is_valid,
       ST_IsValidReason(area) as validation_reason
FROM spatial_data
WHERE area IS NOT NULL;
-- 修復無效的幾何對象
UPDATE spatial_data 
SET area = ST_MakeValid(area)
WHERE NOT ST_IsValid(area);