-- 三维点
SELECT ST_GeomFromText('POINT Z(116.4074 39.9042 44.0)', 4326);

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

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

-- 使用 ST_MakePoint 创建三维点
SELECT ST_SetSRID(ST_MakePoint(116.4074, 39.9042, 44.0), 4326);

-- 检查维度
SELECT ST_NDims(ST_GeomFromText('POINT Z(1 2 3)'));
-- 输出: 3

SELECT ST_CoordDim(ST_GeomFromText('POINT Z(1 2 3)'));
-- 输出: 3

-- 提取 Z 坐标
SELECT ST_Z(ST_GeomFromText('POINT Z(116.4 39.9 50.0)'));
-- 输出: 50.0

-- 为 2D 几何添加 Z 坐标
SELECT ST_Force3D(ST_GeomFromText('POINT(116.4 39.9)'));
-- 输出: POINT Z(116.4 39.9 0)

-- 指定默认 Z 值
SELECT ST_Force3D(ST_GeomFromText('POINT(116.4 39.9)'), 100);
-- 输出: POINT Z(116.4 39.9 100)

-- 将 3D 几何降为 2D
SELECT ST_Force2D(ST_GeomFromText('POINT Z(116.4 39.9 50.0)'));
-- 输出: POINT(116.4 39.9)

-- 修改 Z 值
SELECT ST_SetZ(ST_GeomFromText('POINT Z(116.4 39.9 50.0)'), 200);
-- 输出: POINT Z(116.4 39.9 200)

-- 从 DEM 栅格数据中提取高程并更新建筑的 Z 值
UPDATE buildings b
SET geom_3d = ST_Force3DZ(b.geom, ST_Value(d.rast, 1, ST_Centroid(b.geom)))
FROM dem d
WHERE ST_Intersects(d.rast, ST_Centroid(b.geom));

-- 3D 距离（考虑高程）
SELECT ST_3DDistance(
    ST_GeomFromText('POINT Z(0 0 0)'),
    ST_GeomFromText('POINT Z(3 4 12)')
);
-- 输出: 13 (3² + 4² + 12² = 169, √169 = 13)

-- 对比 2D 距离（忽略高程）
SELECT ST_Distance(
    ST_GeomFromText('POINT Z(0 0 0)'),
    ST_GeomFromText('POINT Z(3 4 12)')
);
-- 输出: 5 (仅计算 XY 距离)

-- 3D 范围查询
SELECT name, height
FROM buildings
WHERE ST_3DDWithin(
    geom_3d,
    ST_SetSRID(ST_MakePoint(116.4074, 39.9042, 100.0), 4326),
    500  -- 3D 距离 500 米
);

-- 对比 2D 范围查询
SELECT name, height
FROM buildings
WHERE ST_DWithin(
    geom::geography,
    ST_SetSRID(ST_MakePoint(116.4074, 39.9042), 4326)::geography,
    500
);

-- 3D 相交判断
SELECT ST_3DIntersects(
    ST_GeomFromText('POLYGON Z((0 0 0, 10 0 0, 10 10 0, 0 10 0, 0 0 0))'),
    ST_GeomFromText('POINT Z(5 5 5)')
);
-- 输出: FALSE (面在 Z=0 平面，点在 Z=5)

SELECT ST_3DIntersects(
    ST_GeomFromText('POLYGON Z((0 0 0, 10 0 0, 10 10 0, 0 10 0, 0 0 0))'),
    ST_GeomFromText('POINT Z(5 5 0)')
);
-- 输出: TRUE (点在面上)

-- 3D 包含判断
SELECT ST_3DIntersects(
    ST_GeomFromText('POLYHEDRALSURFACE Z(
        ((0 0 0, 0 1 0, 1 1 0, 1 0 0, 0 0 0)),
        ((0 0 1, 1 0 1, 1 1 1, 0 1 1, 0 0 1)),
        ((0 0 0, 1 0 0, 1 0 1, 0 0 1, 0 0 0)),
        ((1 0 0, 1 1 0, 1 1 1, 1 0 1, 1 0 0)),
        ((1 1 0, 0 1 0, 0 1 1, 1 1 1, 1 1 0)),
        ((0 1 0, 0 0 0, 0 0 1, 0 1 1, 0 1 0))
    )'),
    ST_GeomFromText('POINT Z(0.5 0.5 0.5)')
);
-- 输出: TRUE (点在立方体内部)

-- 注意: ST_Buffer 在 3D 几何上会忽略 Z 值
-- 如需真正的 3D 缓冲区，需要使用 ST_Extrude（需 SFCGAL）

-- 2D 缓冲区（Z 值保持不变）
SELECT ST_AsText(ST_Buffer(ST_GeomFromText('POINT Z(0 0 10)'), 5));
-- 输出: 2D 多边形，Z=10

-- 3D 体积缓冲区（需要 SFCGAL）
CREATE EXTENSION IF NOT EXISTS postgis_sfcgal;

SELECT ST_AsText(ST_Extrude(
    ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'),
    0, 0, 20  -- 向上挤出 20 米
));

-- 3D 凸包
SELECT ST_AsText(ST_3DConvexHull(
    ST_GeomFromText('MULTIPOINT Z(
        (0 0 0), (10 0 0), (10 10 0), (0 10 0),
        (5 5 10)
    )')
));
-- 输出: 金字塔形状的三维凸包

-- 3D 线的长度（考虑高程变化）
SELECT ST_3DLength(
    ST_GeomFromText('LINESTRING Z(0 0 0, 3 4 12)')
);
-- 输出: 13 (三维斜距)

-- 对比 2D 长度
SELECT ST_Length(
    ST_GeomFromText('LINESTRING Z(0 0 0, 3 4 12)')
);
-- 输出: 5 (仅 XY 平面距离)

-- 3D 面积
SELECT ST_3DArea(
    ST_GeomFromText('POLYGON Z((0 0 0, 10 0 0, 10 10 10, 0 10 10, 0 0 0))')
);
-- 输出: 倾斜面的真实面积

-- 坡度计算（基于 3D 三角形）
CREATE OR REPLACE FUNCTION calc_slope_3d(
    p1 GEOMETRY, p2 GEOMETRY, p3 GEOMETRY
) RETURNS DOUBLE PRECISION AS $$
DECLARE
    v1 GEOMETRY;
    v2 GEOMETRY;
    normal_z DOUBLE PRECISION;
    normal_mag DOUBLE PRECISION;
BEGIN
    -- 计算两个向量
    v1 := ST_MakePoint(ST_X(p2)-ST_X(p1), ST_Y(p2)-ST_Y(p1), ST_Z(p2)-ST_Z(p1));
    v2 := ST_MakePoint(ST_X(p3)-ST_X(p1), ST_Y(p3)-ST_Y(p1), ST_Z(p3)-ST_Z(p1));

    -- 叉积的 Z 分量
    normal_z := ST_X(v1) * ST_Y(v2) - ST_Y(v1) * ST_X(v2);

    -- 叉积的模
    normal_mag := ST_Distance(
        ST_MakePoint(0, 0, 0),
        ST_MakePoint(
            ST_Y(v1) * ST_Z(v2) - ST_Z(v1) * ST_Y(v2),
            ST_Z(v1) * ST_X(v2) - ST_X(v1) * ST_Z(v2),
            ST_X(v1) * ST_Y(v2) - ST_Y(v1) * ST_X(v2)
        )
    );

    -- 坡度 = atan(|normal_z| / normal_xy_component)
    RETURN degrees(atan2(normal_z, normal_mag));
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 建筑物表（含高度信息）
CREATE TABLE buildings_3d (
    id SERIAL PRIMARY KEY,
    name TEXT,
    building_type VARCHAR(50),
    height_m NUMERIC(8,2),
    floors INTEGER,
    geom_2d GEOMETRY(Polygon, 4326),      -- 底面几何
    geom_3d GEOMETRY(PolygonZ, 4326)      -- 三维几何（含高程）
);

-- 插入建筑物数据
INSERT INTO buildings_3d (name, building_type, height_m, floors, geom_2d, geom_3d) VALUES
('国贸大厦', '商业', 330, 81,
 ST_GeomFromText('POLYGON((116.461 39.908, 116.462 39.908, 116.462 39.909, 116.461 39.909, 116.461 39.908))', 4326),
 ST_GeomFromText('POLYGON Z((116.461 39.908 44, 116.462 39.908 44, 116.462 39.909 44, 116.461 39.909 44, 116.461 39.908 44))', 4326)
);

-- 查询指定高程范围内的建筑
SELECT name, height_m
FROM buildings_3d
WHERE ST_3DDWithin(
    geom_3d,
    ST_SetSRID(ST_MakePoint(116.4074, 39.9042, 200), 4326),
    1000
);

-- 地下管网（含埋深）
CREATE TABLE underground_pipes (
    id SERIAL PRIMARY KEY,
    pipe_type VARCHAR(30),  -- 供水、排水、燃气等
    diameter_mm INTEGER,
    depth_start_m NUMERIC(6,2),  -- 起点埋深
    depth_end_m NUMERIC(6,2),    -- 终点埋深
    geom_3d GEOMETRY(LineStringZ, 4326)
);

-- 创建三维线（根据地面坐标和埋深）
INSERT INTO underground_pipes (pipe_type, diameter_mm, depth_start_m, depth_end_m, geom_3d)
SELECT
    '供水',
    300,
    2.0,
    3.5,
    ST_SetSRID(
        ST_MakeLine(
            ST_MakePoint(ST_X(ST_StartPoint(surface_geom)), ST_Y(ST_StartPoint(surface_geom)), -2.0),
            ST_MakePoint(ST_X(ST_EndPoint(surface_geom)), ST_Y(ST_EndPoint(surface_geom)), -3.5)
        ),
        4326
    )
FROM raw_pipe_routes;

-- 检查管线与建筑基础的三维冲突
SELECT p.id AS pipe_id, b.name AS building_name
FROM underground_pipes p, buildings_3d b
WHERE ST_3DIntersects(p.geom_3d, b.geom_3d);

-- 安装 SFCGAL 扩展
CREATE EXTENSION IF NOT EXISTS postgis_sfcgal;

-- 三维挤出（将 2D 面拉伸为 3D 体）
SELECT ST_Extrude(
    ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'),
    0, 0, 30  -- X偏移, Y偏移, Z挤出高度
) AS building_volume;

-- 三维并集
SELECT ST_3DUnion(vol_a, vol_b) FROM volumes;

-- 三维交集
SELECT ST_3DIntersection(vol_a, vol_b) FROM volumes;

-- 三维差集
SELECT ST_3DDifference(vol_a, vol_b) FROM volumes;

-- 体积计算
SELECT ST_Volume(
    ST_Extrude(
        ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))'),
        0, 0, 20
    )
);
-- 输出: 2000 (10 * 10 * 20)

-- 导出建筑物为 GeoJSON（含 Z 坐标）
SELECT jsonb_build_object(
    'type', 'FeatureCollection',
    'features', jsonb_agg(
        jsonb_build_object(
            'type', 'Feature',
            'geometry', ST_AsGeoJSON(geom_3d)::jsonb,
            'properties', jsonb_build_object(
                'name', name,
                'height', height_m,
                'floors', floors
            )
        )
    )
) AS geojson
FROM buildings_3d;

-- 为 Cesium 3D Tiles 准备数据
-- 提取建筑物底面 + 高度，供 Cesium 挤出
SELECT
    id,
    name,
    height_m,
    ST_AsGeoJSON(geom_2d) AS footprint_geojson
FROM buildings_3d;
-- Cesium 可使用 footprint + height 自动挤出三维模型

-- 创建三维空间索引
CREATE INDEX idx_buildings_3d_geom ON buildings_3d USING GIST (geom_3d);

-- 注意: GiST 索引在三维空间中使用 3D 边界框
-- 使用 &&& 操作符进行三维边界框过滤
SELECT name FROM buildings_3d
WHERE geom_3d &&& ST_GeomFromText('LINESTRING Z(116.4 39.9 0, 116.4 39.9 500)', 4326);

-- 3D 范围查询优化
-- 步骤 1: 边界框过滤（利用索引）
-- 步骤 2: 精确 3D 距离/相交判断
SELECT name
FROM buildings_3d
WHERE geom_3d &&& ST_Expand(target_3d, 100)  -- 边界框粗过滤
  AND ST_3DDWithin(geom_3d, target_3d, 100);  -- 精确 3D 距离

-- 按高程分区（适用于大量地下管网数据）
CREATE TABLE pipes_surface (
    CHECK (depth_start_m >= 0)
) INHERITS (underground_pipes);

CREATE TABLE pipes_underground_shallow (
    CHECK (depth_start_m < 0 AND depth_start_m >= -5)
) INHERITS (underground_pipes);

CREATE TABLE pipes_underground_deep (
    CHECK (depth_start_m < -5)
) INHERITS (underground_pipes);