-- DuckDB 1.4 及之前
INSTALL spatial;
LOAD spatial;
SELECT ST_Point(116.4, 39.9) AS beijing;
-- 必须装扩展，否则报错

-- DuckDB 1.5.0
SELECT ST_Point(116.4, 39.9) AS beijing;
-- ↳ 直接返回 POINT (116.4 39.9)，0 配置

-- 创建空间表，GEOMETRY 是原生类型
CREATE TABLE stores (
    id INTEGER,
    name VARCHAR,
    location GEOMETRY,  -- 原生列类型！
    opening_date DATE
);

-- 插入空间数据
INSERT INTO stores VALUES
    (1, '西湖银泰店', ST_GeomFromText('POINT(120.1671 30.2550)'), '2024-01-15'),
    (2, '龙湖天街店', ST_GeomFromText('POINT(120.2072 30.2919)'), '2024-03-20'),
    (3, '城西银泰店', ST_GeomFromText('POINT(120.0901 30.3020)'), '2024-06-01');

-- 直接查：不需要任何扩展加载
SELECT name, ST_AsText(location) AS wkt
FROM stores;

-- 点
SELECT ST_Point(116.4, 39.9);           -- POINT (116.4 39.9)
SELECT ST_MakePoint(116.4, 39.9);       -- 同上

-- 线
SELECT ST_GeomFromText('LINESTRING(0 0, 1 1, 2 0)');

-- 多边形
SELECT ST_GeomFromText('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))');

-- 从 GeoJSON 构造
SELECT ST_GeomFromGeoJSON('{"type":"Point","coordinates":[116.4,39.9]}');

-- 两个几何是否相交
SELECT ST_Intersects(
    ST_Point(116.4, 39.9),
    ST_Buffer(ST_Point(116.4, 39.9), 0.1)
);
-- ↳ true

-- 是否在指定距离内
SELECT ST_DWithin(
    ST_Point(116.4, 39.9),
    ST_Point(116.5, 39.9),
    10000  -- 10公里
);
-- ↳ true (大约 11 公里，所以 false)

-- 是否包含
SELECT ST_Contains(
    ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'),
    ST_Point(5, 5)
);
-- ↳ true

-- 距离计算（单位取决于坐标系）
SELECT ST_Distance(
    ST_Point(120.1671, 30.2550),  -- 西湖银泰
    ST_Point(120.2072, 30.2919)   -- 龙湖天街
);
-- ↳ 约 0.052 度（约 5.8 公里）

-- 面积计算
SELECT ST_Area(
    ST_GeomFromText('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))')
);

-- 缓冲区（画一个圆）
SELECT ST_AsText(ST_Buffer(ST_Point(0, 0), 2.0));

-- WKT 输出
SELECT ST_AsText(ST_Point(116.4, 39.9));
-- ↳ POINT (116.4 39.9)

-- GeoJSON 输出
SELECT ST_AsGeoJSON(ST_Point(116.4, 39.9));
-- ↳ {"type":"Point","coordinates":[116.4,39.9]}

-- WKB 二进制输出
SELECT ST_AsWKB(ST_Point(116.4, 39.9));

-- 创建门店表
CREATE TABLE stores AS SELECT * FROM (
    VALUES
        (1, '西湖银泰店', ST_Point(120.1671, 30.2550)),
        (2, '龙湖天街店', ST_Point(120.2072, 30.2919)),
        (3, '城西银泰店', ST_Point(120.0901, 30.3020)),
        (4, '下沙宝龙店', ST_Point(120.3412, 30.3136)),
        (5, '滨江天街店', ST_Point(120.1993, 30.2038)),
        (6, '远洋乐堤港店', ST_Point(120.1530, 30.2770)),
        (7, '西溪印象城店', ST_Point(120.0469, 30.2693)),
        (8, '萧山万象汇店', ST_Point(120.2654, 30.1762))
) AS t(id, name, location);

-- 创建大学表（使用 WKT）
CREATE TABLE universities AS SELECT * FROM (
    VALUES
        ('浙江大学(紫金港)', ST_GeomFromText('POINT(120.0822 30.3003)')),
        ('浙江大学(玉泉)', ST_GeomFromText('POINT(120.1219 30.2682)')),
        ('浙江大学(西溪)', ST_GeomFromText('POINT(120.1505 30.2728)')),
        ('浙江工业大学', ST_GeomFromText('POINT(120.1577 30.2938)')),
        ('杭州电子科技大学', ST_GeomFromText('POINT(120.3416 30.3137)')),
        ('浙江理工大学', ST_GeomFromText('POINT(120.3465 30.3119)')),
        ('浙江工商大学', ST_GeomFromText('POINT(120.3498 30.3155)')),
        ('中国美术学院(象山)', ST_GeomFromText('POINT(120.0598 30.1761)')),
        ('浙江科技大学', ST_GeomFromText('POINT(120.0507 30.2319)'))
) AS t(name, location);

-- 分析：每个门店方圆3公里内有哪些大学？
-- 使用 ST_DWithin（第三个参数是距离，单位度，3公里≈0.027度）
SELECT
    s.name AS store_name,
    u.name AS university_name,
    ST_Distance(s.location, u.location) AS dist_degree
FROM stores s
CROSS JOIN universities u
WHERE ST_DWithin(s.location, u.location, 0.027)
ORDER BY s.name, dist_degree;

-- 统计：找出有超过2所大学覆盖的门店
SELECT
    s.name AS store_name,
    COUNT(u.name) AS nearby_universities
FROM stores s
LEFT JOIN universities u
    ON ST_DWithin(s.location, u.location, 0.027)
GROUP BY s.name
HAVING COUNT(u.name) >= 2
ORDER BY nearby_universities DESC;

import smallpond

# 初始化分布式 session
sp = smallpond.init()

# 读分布在多台机器上的 Parquet 文件（含 GEOMETRY 列）
df = sp.read_parquet("nationwide_stores/*.parquet")

# 分布式空间 JOIN
df = sp.partial_sql("""
    SELECT s.store_id, s.region,
           COUNT(u.id) AS competitor_count
    FROM {0} s
    JOIN competitors u
      ON ST_DWithin(s.location, u.location, 0.01)
    GROUP BY s.store_id, s.region
""", df)

df.write_parquet("output/")

-- 选址分析核心查询（示意）
SELECT
    candidate.address,
    COUNT(DISTINCT competitor.id) AS nearby_competitors,
    COUNT(DISTINCT residential.id) AS nearby_communities,
    AVG(rent.per_sqm) AS avg_rent
FROM candidate_sites candidate
LEFT JOIN competitors competitor
    ON ST_DWithin(candidate.location, competitor.location, 0.005)  -- ~500米
LEFT JOIN residential_areas residential
    ON ST_DWithin(candidate.location, residential.location, 0.01)  -- ~1公里
LEFT JOIN rent_prices rent
    ON ST_DWithin(candidate.location, rent.location, 0.01)
GROUP BY candidate.address
ORDER BY nearby_competitors ASC, nearby_communities DESC
LIMIT 10;