-- WKT 方式
SELECT ST_GeomFromText('POINT(116.4074 39.9042)');

-- ST_MakePoint (推荐，性能更好)
SELECT ST_SetSRID(ST_MakePoint(116.4074, 39.9042), 4326);

-- 三维点 (含高程)
SELECT ST_SetSRID(ST_MakePoint(116.4074, 39.9042, 50.0), 4326);

-- 从坐标值反向提取
SELECT
    ST_X(geom) AS longitude,
    ST_Y(geom) AS latitude,
    ST_Z(geom) AS elevation
FROM (SELECT ST_GeomFromText('POINT Z(116.4074 39.9042 50.0)') AS geom) t;

-- 创建门店表
CREATE TABLE stores (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    address TEXT,
    store_type VARCHAR(20) CHECK (store_type IN ('旗舰', '标准', '社区')),
    area_sqm NUMERIC(10,2),
    open_date DATE,
    geom GEOMETRY(Point, 4326) NOT NULL
);

-- 插入门店数据
INSERT INTO stores (name, address, store_type, area_sqm, open_date, geom) VALUES
('旗舰店·国贸', '北京市朝阳区建国门外大街1号', '旗舰', 5000, '2020-01-15',
 ST_SetSRID(ST_MakePoint(116.4612, 39.9087), 4326)),
('标准店·西单', '北京市西城区西单北大街176号', '标准', 2000, '2021-03-20',
 ST_SetSRID(ST_MakePoint(116.3745, 39.9125), 4326)),
('社区店·回龙观', '北京市昌平区回龙观西大街9号', '社区', 800, '2023-06-01',
 ST_SetSRID(ST_MakePoint(116.3376, 40.0691), 4326)),
('标准店·望京', '北京市朝阳区望京街10号', '标准', 1500, '2022-08-10',
 ST_SetSRID(ST_MakePoint(116.4817, 39.9968), 4326));

-- 创建空间索引
CREATE INDEX idx_stores_geom ON stores USING GIST (geom);

-- 基本线
SELECT ST_GeomFromText('LINESTRING(116.38 39.90, 116.40 39.91, 116.42 39.90)');

-- 三维线
SELECT ST_GeomFromText('LINESTRING Z(0 0 10, 1 1 20, 2 0 15)');

-- 闭合线（首尾相同）
SELECT ST_GeomFromText('LINESTRING(0 0, 1 0, 1 1, 0 1, 0 0)');

-- 创建道路表
CREATE TABLE roads (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    road_class VARCHAR(20) CHECK (road_class IN ('高速', '国道', '省道', '县道', '城市道路')),
    oneway BOOLEAN DEFAULT FALSE,
    speed_limit INTEGER,  -- 限速 km/h
    lanes INTEGER,        -- 车道数
    geom GEOMETRY(LineString, 4326) NOT NULL
);

-- 插入道路数据
INSERT INTO roads (name, road_class, speed_limit, lanes, geom) VALUES
('长安街', '城市道路', 60, 6,
 ST_GeomFromText('LINESTRING(116.3470 39.9042, 116.3745 39.9042, 116.4074 39.9042, 116.4612 39.9042)', 4326)),
('二环路', '城市道路', 80, 4,
 ST_GeomFromText('LINESTRING(116.3576 39.9340, 116.3745 39.9530, 116.4200 39.9530, 116.4450 39.9340, 116.4450 39.8800, 116.4200 39.8600, 116.3745 39.8600, 116.3576 39.8800, 116.3576 39.9340)', 4326));

CREATE INDEX idx_roads_geom ON roads USING GIST (geom);

-- 线的长度（度）
SELECT name, ST_Length(geom) AS length_degrees
FROM roads;

-- 线的真实长度（米，使用 Geography）
SELECT name, ST_Length(geom::geography) AS length_meters
FROM roads;

-- 线的起点和终点
SELECT name,
       ST_AsText(ST_StartPoint(geom)) AS start_point,
       ST_AsText(ST_EndPoint(geom)) AS end_point
FROM roads;

-- 线上的点数
SELECT name, ST_NumPoints(geom) AS point_count
FROM roads;

-- 线的边界框
SELECT name, ST_AsText(ST_Envelope(geom)) AS bbox
FROM roads;

-- 线是否闭合
SELECT name, ST_IsClosed(geom) AS is_closed,
       ST_IsRing(geom) AS is_ring
FROM roads;

-- 基本面（外环）
SELECT ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))');

-- 带孔的面
SELECT ST_GeomFromText('POLYGON(
    (0 0, 20 0, 20 20, 0 20, 0 0),
    (5 5, 15 5, 15 15, 5 15, 5 5)
)');

-- 三维面
SELECT ST_GeomFromText('POLYGON Z((0 0 10, 10 0 10, 10 10 10, 0 10 10, 0 0 10))');

-- 创建行政区划表
CREATE TABLE districts (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent VARCHAR(100),          -- 上级区划
    level VARCHAR(20) CHECK (level IN ('省', '市', '区县', '乡镇')),
    area_km2 NUMERIC(12,4),       -- 面积（平方公里）
    population BIGINT,
    geom GEOMETRY(MultiPolygon, 4326) NOT NULL
);

-- 面的常用计算
SELECT
    name,
    ST_Area(geom::geography) / 1000000 AS area_km2,    -- 面积（km²）
    ST_Perimeter(geom::geography) / 1000 AS perimeter_km, -- 周长（km）
    ST_NPoints(geom) AS point_count,                    -- 坐标点数
    ST_NumInteriorRings(geom::geometry) AS hole_count   -- 内环（孔）数量
FROM districts;

-- 创建多点
SELECT ST_GeomFromText('MULTIPOINT((0 0), (1 1), (2 2))');

-- 从多个点聚合创建
SELECT ST_Collect(geom) AS multi_point
FROM stores
WHERE store_type = '社区';

-- 或使用 ST_Union（自动去重）
SELECT ST_Union(geom) FROM stores;

-- 提取单个点
SELECT ST_GeometryN(multi_point, 1) AS first_point
FROM (SELECT ST_GeomFromText('MULTIPOINT((0 0), (1 1), (2 2))') AS multi_point) t;

-- 集合中的点数
SELECT ST_NumGeometries(ST_GeomFromText('MULTIPOINT((0 0), (1 1), (2 2))'));
-- 输出: 3

-- 创建多线
SELECT ST_GeomFromText('MULTILINESTRING(
    (0 0, 1 1, 2 2),
    (3 3, 4 4, 5 5)
)');

-- 业务场景：公交线路（一条线路可能有多段）
CREATE TABLE bus_routes (
    id SERIAL PRIMARY KEY,
    route_number VARCHAR(20),
    direction VARCHAR(10),  -- 上行/下行
    geom GEOMETRY(MultiLineString, 4326)
);

-- 合并多条道路为一条连续线路
SELECT ST_LineMerge(ST_Collect(geom)) AS merged_line
FROM roads
WHERE road_class = '城市道路';

-- 创建多面（例如：群岛）
SELECT ST_GeomFromText('MULTIPOLYGON(
    ((0 0, 1 0, 1 1, 0 1, 0 0)),
    ((3 3, 4 3, 4 4, 3 4, 3 3))
)');

-- 业务场景：行政区划（如崇明区包含多个岛屿）
CREATE TABLE admin_regions (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    geom GEOMETRY(MultiPolygon, 4326)
);

-- 合并相邻区域
SELECT ST_Union(geom) AS merged_region
FROM admin_regions
WHERE name LIKE '浦东%';

-- 包含点、线、面的混合集合
SELECT ST_GeomFromText('GEOMETRYCOLLECTION(
    POINT(0 0),
    LINESTRING(1 1, 2 2, 3 3),
    POLYGON((4 4, 8 4, 8 8, 4 8, 4 4))
)');

-- 检查集合中的元素
SELECT ST_NumGeometries(
    ST_GeomFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(1 1, 2 2))')
);
-- 输出: 2

-- 圆弧线
SELECT ST_GeomFromText('CIRCULARSTRING(0 0, 1 1, 2 0)');

-- 复合曲线（直线段 + 圆弧段）
SELECT ST_GeomFromText('COMPOUNDCURVE(
    (0 0, 0 1),
    CIRCULARSTRING(0 1, 1 2, 2 1),
    (2 1, 3 1)
)');

-- 曲线多边形
SELECT ST_GeomFromText('CURVEPOLYGON(
    CIRCULARSTRING(0 0, 4 0, 4 4, 0 4, 0 0)
)');

-- 曲线转直线（用于不支持曲线的 API）
SELECT ST_LineToCurve(ST_CurveToLine(
    ST_GeomFromText('CIRCULARSTRING(0 0, 1 1, 2 0)')
));

-- ST_MakePoint 比 ST_GeomFromText 快约 5-10 倍
-- 大批量数据创建时优先使用 ST_MakePoint

-- 测试：创建 10 万点
-- 方式 1: ST_GeomFromText（慢）
INSERT INTO points (geom)
SELECT ST_SetSRID(ST_GeomFromText('POINT(' || x || ' ' || y || ')'), 4326)
FROM generate_series(1, 1000) AS x, generate_series(1, 100) AS y;

-- 方式 2: ST_MakePoint（快，推荐）
INSERT INTO points (geom)
SELECT ST_SetSRID(ST_MakePoint(x::double precision, y::double precision), 4326)
FROM generate_series(1, 1000) AS x, generate_series(1, 100) AS y;

-- 缓冲区
SELECT ST_Buffer(ST_GeomFromText('POINT(0 0)'), 100);

-- 凸包
SELECT ST_ConvexHull(ST_GeomFromText('MULTIPOINT((0 0), (1 2), (3 1), (2 0))'));

-- 质心
SELECT ST_Centroid(ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'));
-- 输出: POINT(5 5)

-- 最小旋转外接矩形
SELECT ST_OrientedEnvelope(ST_GeomFromText('MULTIPOINT((0 0), (1 2), (3 1))'));

-- 并集
SELECT ST_Union(geom) FROM admin_regions WHERE parent = '北京市';

-- 差集
SELECT ST_Difference(geom_a, geom_b) FROM overlay_pairs;

-- 交集
SELECT ST_Intersection(geom_a, geom_b) FROM overlay_pairs;

-- 对称差
SELECT ST_SymDifference(geom_a, geom_b) FROM overlay_pairs;

-- 常见无效几何：自相交的多边形
SELECT ST_IsValid(
    ST_GeomFromText('POLYGON((0 0, 2 2, 2 0, 0 2, 0 0))')
);
-- 输出: FALSE (自相交)

-- 查看无效原因
SELECT ST_IsValidReason(
    ST_GeomFromText('POLYGON((0 0, 2 2, 2 0, 0 2, 0 0))')
);
-- 输出: Self-intersection

-- 修复无效几何
SELECT ST_MakeValid(
    ST_GeomFromText('POLYGON((0 0, 2 2, 2 0, 0 2, 0 0))')
);

-- WKT 标准：X Y (经度 纬度)
-- 部分数据源使用 纬度 经度，导入前需确认

-- 正确：北京坐标 (经度 116.4, 纬度 39.9)
SELECT ST_SetSRID(ST_MakePoint(116.4074, 39.9042), 4326);

-- 错误：纬度和经度搞反了
SELECT ST_SetSRID(ST_MakePoint(39.9042, 116.4074), 4326);
-- 这会创建一个在南极附近的点！