-- 查看两个几何的 DE-9IM 矩阵
SELECT ST_Relate(
    ST_GeomFromText('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))'),
    ST_GeomFromText('POLYGON((1 1, 3 1, 3 3, 1 3, 1 1))')
);
-- 输出: T*T***T**

-- 使用特定模式匹配
SELECT ST_Relate(
    ST_GeomFromText('LINESTRING(0 0, 2 2)'),
    ST_GeomFromText('LINESTRING(0 2, 2 0)'),
    'T*F**FFF*'  -- 测试是否相等
);
-- 输出: FALSE

-- 判断两条路是否相交
SELECT ST_Intersects(
    ST_GeomFromText('LINESTRING(0 0, 10 10)'),
    ST_GeomFromText('LINESTRING(0 10, 10 0)')
);
-- 输出: TRUE

-- 判断点是否在区域内
SELECT ST_Intersects(
    ST_GeomFromText('POINT(5 5)'),
    ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))')
);
-- 输出: TRUE

-- 查询某个配送区域内的所有门店
SELECT s.name, s.address
FROM stores s
JOIN delivery_zones dz ON ST_Intersects(s.geom, dz.geom)
WHERE dz.zone_name = '朝阳区配送区';

-- 查询与某条地铁线 500 米缓冲区相交的门店
SELECT s.name, s.address
FROM stores s
WHERE ST_Intersects(
    s.geom,
    ST_Buffer(
        (SELECT geom FROM metro_lines WHERE line_name = '1号线')::geography,
        500
    )::geometry
);

-- 判断某个点是否在某个区域内
SELECT ST_Contains(
    ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'),
    ST_GeomFromText('POINT(5 5)')
);
-- 输出: TRUE

-- 边界上的点不算包含
SELECT ST_Contains(
    ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'),
    ST_GeomFromText('POINT(0 0)')
);
-- 输出: FALSE (边界上的点不属于"内部")

-- ST_ContainsProperly: 严格在内部（不接触边界）
SELECT ST_ContainsProperly(
    ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'),
    ST_GeomFromText('POINT(0 0)')
);
-- 输出: FALSE

-- 查找每个 POI 所属的行政区
SELECT p.name AS poi_name, d.name AS district_name
FROM pois p
JOIN districts d ON ST_Contains(d.geom, p.geom);

-- 查找包含在某个多边形内的所有设施
SELECT f.name, f.facility_type
FROM facilities f
WHERE ST_Within(f.geom, ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[...]}'));

-- Geometry 距离（坐标单位，这里是度）
SELECT ST_Distance(
    ST_GeomFromText('POINT(116.4074 39.9042)', 4326),
    ST_GeomFromText('POINT(121.4737 31.2304)', 4326)
);
-- 输出: ~10.77 (度)
-- 这个数字没有直观意义！

-- Geography 距离（米，真实球面距离）
SELECT ST_Distance(
    ST_SetSRID(ST_MakePoint(116.4074, 39.9042), 4326)::geography,
    ST_SetSRID(ST_MakePoint(121.4737, 31.2304), 4326)::geography
) / 1000 AS distance_km;
-- 输出: ~1068 (公里)
-- 这才是真实的直线距离！

-- ❌ 错误方式：先计算所有距离再过滤（全表扫描）
SELECT name, ST_Distance(geom::geography, target::geography) AS dist
FROM stores
WHERE ST_Distance(geom::geography, target::geography) < 3000;
-- 这会计算所有记录的距离，非常慢！

-- ✅ 正确方式：使用 ST_DWithin（利用空间索引）
SELECT name, ST_Distance(geom::geography, target::geography) AS dist
FROM stores
WHERE ST_DWithin(geom::geography, target::geography, 3000);
-- ST_DWithin 会利用空间索引先过滤，再精确计算

-- 查找距离目标点最近的 5 个门店
SELECT name, address,
       ST_Distance(
           geom::geography,
           ST_SetSRID(ST_MakePoint(116.4074, 39.9042), 4326)::geography
       ) AS distance_m
FROM stores
ORDER BY geom <-> ST_SetSRID(ST_MakePoint(116.4074, 39.9042), 4326)
LIMIT 5;

-- 查找某点 3 公里范围内的门店
SELECT name, address,
       ROUND(ST_Distance(
           geom::geography,
           ST_SetSRID(ST_MakePoint(116.4074, 39.9042), 4326)::geography
       )) AS distance_m
FROM stores
WHERE ST_DWithin(
    geom::geography,
    ST_SetSRID(ST_MakePoint(116.4074, 39.9042), 4326)::geography,
    3000  -- 3000 米
)
ORDER BY distance_m;

-- 场景：用户进入任何门店 500 米范围内时触发推送
CREATE OR REPLACE FUNCTION check_geofence(
    user_lng DOUBLE PRECISION,
    user_lat DOUBLE PRECISION
) RETURNS TABLE(store_name TEXT, distance DOUBLE PRECISION) AS $$
BEGIN
    RETURN QUERY
    SELECT s.name::TEXT,
           ST_Distance(
               s.geom::geography,
               ST_SetSRID(ST_MakePoint(user_lng, user_lat), 4326)::geography
           )
    FROM stores s
    WHERE ST_DWithin(
        s.geom::geography,
        ST_SetSRID(ST_MakePoint(user_lng, user_lat), 4326)::geography,
        500
    );
END;
$$ LANGUAGE plpgsql;

-- 调用
SELECT * FROM check_geofence(116.4074, 39.9042);

-- 两个多边形是否边界相接（不重叠）
SELECT ST_Touches(
    ST_GeomFromText('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))'),
    ST_GeomFromText('POLYGON((2 0, 4 0, 4 2, 2 2, 2 0))')
);
-- 输出: TRUE (共享边)

-- 业务场景：查找相邻地块
SELECT a.id AS plot_a, b.id AS plot_b
FROM land_plots a, land_plots b
WHERE a.id < b.id
  AND ST_Touches(a.geom, b.geom);

-- 线穿过面
SELECT ST_Crosses(
    ST_GeomFromText('LINESTRING(0 5, 10 5)'),
    ST_GeomFromText('POLYGON((3 3, 7 3, 7 7, 3 7, 3 3))')
);
-- 输出: TRUE

-- 业务场景：管线穿越行政区
SELECT p.pipeline_name, d.district_name
FROM pipelines p
JOIN districts d ON ST_Crosses(p.geom, d.geom);

-- 两个同维度几何部分重叠
SELECT ST_Overlaps(
    ST_GeomFromText('POLYGON((0 0, 4 0, 4 4, 0 4, 0 0))'),
    ST_GeomFromText('POLYGON((2 2, 6 2, 6 6, 2 6, 2 2))')
);
-- 输出: TRUE

-- 业务场景：检测规划地块重叠
SELECT a.plot_id AS plot_a, b.plot_id AS plot_b,
       ST_Area(ST_Intersection(a.geom, b.geom)) AS overlap_area
FROM planned_plots a, planned_plots b
WHERE a.plot_id < b.plot_id
  AND ST_Overlaps(a.geom, b.geom);

-- ST_Equals：几何相等（不考虑坐标顺序）
SELECT ST_Equals(
    ST_GeomFromText('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))'),
    ST_GeomFromText('POLYGON((1 1, 1 -1, -1 -1, -1 1, 1 1))')  -- 旋转后的同一个面
);
-- 输出: TRUE (如果表示同一区域)

-- ST_OrderingEquals：坐标完全相同
SELECT ST_OrderingEquals(
    ST_GeomFromText('LINESTRING(0 0, 1 1)'),
    ST_GeomFromText('LINESTRING(1 1, 0 0)')
);
-- 输出: FALSE (方向相反)

-- 为每个居民区找到最近的医院
WITH hospitals AS (
    SELECT id, name, geom
    FROM facilities
    WHERE facility_type = '医院'
),
residential AS (
    SELECT id, name, geom
    FROM communities
)
SELECT r.name AS community,
       h.name AS nearest_hospital,
       ROUND(ST_Distance(r.geom::geography, h.geom::geography)) AS distance_m
FROM residential r
CROSS JOIN LATERAL (
    SELECT h.name, h.geom
    FROM hospitals h
    ORDER BY r.geom <-> h.geom
    LIMIT 1
) h;

-- 计算所有门店的 3 公里配送覆盖区域
SELECT ST_Union(
    ST_Buffer(geom::geography, 3000)::geometry
) AS coverage_area
FROM stores;

-- 计算覆盖面积（平方公里）
SELECT ST_Area(
    ST_Union(ST_Buffer(geom::geography, 3000)::geometry)::geography
) / 1000000 AS coverage_km2
FROM stores;

-- 计算两个图层的交集
SELECT
    a.id AS region_id,
    b.id AS zone_id,
    ST_Intersection(a.geom, b.geom) AS intersection_geom,
    ST_Area(ST_Intersection(a.geom, b.geom)::geography) / 1000000 AS area_km2
FROM regions a
JOIN zones b ON ST_Intersects(a.geom, b.geom)
WHERE NOT ST_IsEmpty(ST_Intersection(a.geom, b.geom));

-- 优化技巧：两步过滤法
-- 步骤 1: 用边界框快速过滤（利用索引）
-- 步骤 2: 用精确几何函数验证
SELECT name
FROM stores
WHERE geom && ST_Buffer(target_geom, 0.01)  -- 粗过滤（边界框）
  AND ST_DWithin(geom::geography, target_geom::geography, 1000);  -- 精确过滤