DuckDB 地理空间分析实战：从坐标到商业洞察

-- 加载空间扩展（首次需要下载）
LOAD spatial;
INSTALL spatial;

-- 验证安装
SELECT version();

LOAD spatial;

-- 创建门店表
CREATE TABLE my_stores AS
SELECT * FROM (VALUES
    ('北京三里屯店',   116.4510, 39.9320),
    ('上海南京路店',   121.4730, 31.2300),
    ('广州天河路店',   113.3250, 23.1300),
    ('深圳华强北路',   114.0650, 22.5400),
    ('成都春熙路店',   104.0800, 30.6600),
    ('杭州西湖路店',   120.1500, 30.2800),
    ('武汉江汉路店',   114.2800, 30.5800),
    ('重庆解放碑店',   106.5800, 29.5600)
) AS t(name, longitude, latitude);

-- 创建竞品门店表
CREATE TABLE competitor_stores AS
SELECT * FROM (VALUES
    ('星巴克三里屯',   116.4530, 39.9340),
    ('瑞幸三里屯',     116.4490, 39.9310),
    ('星巴克南京路',   121.4750, 31.2320),
    ('瑞幸南京路',     121.4710, 31.2280),
    ('Manner天河路',   113.3270, 23.1320),
    ('瑞幸华强北',     114.0670, 22.5420)
) AS t(name, longitude, latitude);

-- 将经纬度转换为 WKT 几何对象
ALTER TABLE my_stores ADD COLUMN geom GEOMETRY;
UPDATE my_stores SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);

ALTER TABLE competitor_stores ADD COLUMN geom GEOMETRY;
UPDATE competitor_stores SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);

-- 计算每对门店之间的距离（单位：米）
SELECT 
    m.name AS my_store,
    c.name AS competitor,
    ROUND(
        ST_Distance(
            ST_Transform(m.geom, 3857),  -- 转为 Web Mercator（米制坐标系）
            ST_Transform(c.geom, 3857)
        )::INTEGER
    ) AS distance_meters,
    CASE 
        WHEN ST_Distance(
            ST_Transform(m.geom, 3857),
            ST_Transform(c.geom, 3857)
        ) < 500 THEN '⚠️ 直接竞争'
        WHEN ST_Distance(
            ST_Transform(m.geom, 3857),
            ST_Transform(c.geom, 3857)
        ) < 1000 THEN '📊 潜在竞争'
        ELSE '✅ 安全距离'
    END AS competition_level
FROM my_stores m
CROSS JOIN competitor_stores c
WHERE ST_DWithin(
    ST_Transform(m.geom, 3857),
    ST_Transform(c.geom, 3857),
    1000  -- 1000米范围内
)
ORDER BY distance_meters;

-- 为每家门店创建 500 米服务半径缓冲区
CREATE TABLE store_buffers AS
SELECT 
    name,
    ST_Buffer(ST_Transform(geom, 3857), 500) AS buffer_geom
FROM my_stores;

-- 查询每个缓冲区内有多少家竞品
SELECT 
    s.name AS store,
    COUNT(c.name) AS nearby_competitors
FROM store_buffers s
LEFT JOIN competitor_stores c 
    ON ST_Contains(s.buffer_geom, ST_Transform(c.geom, 3857))
GROUP BY s.name
ORDER BY nearby_competitors DESC;

-- 给定一个新订单位置，找到最近的 3 家门店
WITH order_location AS (
    SELECT ST_SetSRID(ST_MakePoint(116.4550, 39.9350), 4326)::GEOMETRY AS geom
)
SELECT 
    m.name,
    ROUND(ST_Distance(
        ST_Transform(m.geom, 3857),
        ST_Transform(o.geom, 3857)
    )) AS distance_m
FROM my_stores m, order_location o
ORDER BY distance_m
LIMIT 3;

-- 加载城市商圈 GeoJSON
CREATE TABLE districts AS
SELECT 
    feature->>'name' AS district_name,
    feature->>'population' AS population,
    st_geomfromjson(feature->'geometry') AS geom
FROM read_geojson('https://example.com/china-districts.geojson');

-- 统计每个商圈内有多少门店
SELECT 
    d.district_name,
    d.population,
    COUNT(m.name) AS store_count,
    ROUND(d.population::DOUBLE / NULLIF(COUNT(m.name), 0), 0) AS per_store_population
FROM districts d
LEFT JOIN my_stores m ON ST_Contains(d.geom, m.geom)
GROUP BY d.district_name, d.population
ORDER BY per_store_population;