-- 查看 PostGIS 注册的类型
SELECT typname, typtype
FROM pg_type
WHERE typname IN ('geometry', 'geography', 'box2d', 'box3d');

-- 查看 PostGIS 注册的函数数量
SELECT count(*) AS function_count
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname = 'public'
  AND p.proname LIKE 'st_%';

-- Geometry -> Geography
SELECT geom::geography FROM cities WHERE name = '北京';

-- Geography -> Geometry
SELECT geog::geometry FROM geog_table;

-- 使用 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 (公里)

-- 查看 SRID 详细信息
SELECT srid, auth_name, auth_srid, srtext
FROM spatial_ref_sys
WHERE srid = 4326;

-- 搜索中国相关的坐标系
SELECT srid, srtext
FROM spatial_ref_sys
WHERE srtext ILIKE '%china%' OR srtext ILIKE '%CGCS%'
LIMIT 10;

-- GEOS 版本信息
SELECT PostGIS_GEOS_Version();

-- 示例：创建缓冲区
SELECT ST_AsText(ST_Buffer(ST_GeomFromText('POINT(0 0)'), 10));

-- PROJ 版本信息
SELECT PostGIS_PROJ_Version();

-- 将北京坐标从 WGS84 (4326) 转换为 CGCS2000 三度带 39 带 (4547)
SELECT ST_AsText(
    ST_Transform(
        ST_SetSRID(ST_MakePoint(116.4074, 39.9042), 4326),
        4547
    )
);
-- 输出: POINT(447963.89 4418554.07) (单位：米)

-- 创建 GiST 空间索引
CREATE INDEX idx_cities_geom ON cities USING GIST (geom);

-- 索引支持的操作符
-- &&  — 边界框相交
-- @   — 被包含于
-- ~   — 包含
-- <-> — 距离排序（KNN 查询）
-- |&> | <&  — 各种边界框关系

-- SP-GiST 索引（适合大量随机分布的点数据）
CREATE INDEX idx_points_spgist ON points USING SPGIST (geom);

-- BRIN 索引（极小的索引体积，适合有序插入的大表）
CREATE INDEX idx_events_brin ON events USING BRIN (geom);

-- 查看几何对象的边界框
SELECT ST_Extent(geom) AS bbox FROM cities;
-- 输出: BOX(104.0668 22.5431,121.4737 39.9042)

-- ST_Envelope 返回几何对象
SELECT ST_AsText(ST_Envelope(
    ST_GeomFromText('LINESTRING(0 0, 10 10)')
));
-- 输出: POLYGON((0 0,10 0,10 10,0 10,0 0))

SELECT
    name,
    ST_GeometryType(geom) AS type,       -- 几何类型
    ST_NDims(geom) AS ndims,              -- 维度 (2D/3D)
    ST_SRID(geom) AS srid,               -- 坐标系
    ST_NumPoints(geom) AS npoints,       -- 坐标点数量
    ST_IsEmpty(geom) AS is_empty,        -- 是否为空
    ST_IsValid(geom) AS is_valid,        -- 是否有效
    ST_IsSimple(geom) AS is_simple       -- 是否简单
FROM cities;

-- 2D 几何
SELECT ST_GeomFromText('POINT(116.40 39.90)');

-- 3D 几何 (带 Z 坐标 — 高程)
SELECT ST_GeomFromText('POINT Z(116.40 39.90 50.0)');

-- 4D 几何 (带 Z 和 M — 测量值)
SELECT ST_GeomFromText('POINT ZM(116.40 39.90 50.0 100.0)');

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

-- 获取 Z 坐标
SELECT ST_Z(ST_GeomFromText('POINT Z(116.40 39.90 50.0)'));
-- 输出: 50.0

-- 查看所有空间表
SELECT f_table_schema, f_table_name, f_geometry_column,
       coord_dimension, srid, type
FROM geometry_columns
WHERE f_table_schema = 'public';

-- 查看特定表的空间列信息
SELECT * FROM geometry_columns
WHERE f_table_name = 'cities';

-- 如果表已存在但未注册空间列
SELECT Populate_Geometry_Columns('public.my_table'::regclass);

-- 或手动注册
INSERT INTO geometry_columns
(f_table_catalog, f_table_schema, f_table_name, f_geometry_column,
 coord_dimension, srid, type)
VALUES
('', 'public', 'my_table', 'geom', 2, 4326, 'POINT');

-- 总共支持多少种坐标系？
SELECT count(*) FROM spatial_ref_sys;
-- 输出: 数千条

-- 查询特定坐标系
SELECT srid, auth_name, auth_srid, srtext
FROM spatial_ref_sys
WHERE srid IN (4326, 3857, 4490, 4547);

-- 添加自定义坐标系
INSERT INTO spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext)
VALUES (
    990001,
    'CUSTOM',
    990001,
    '+proj=tmerc +lat_0=0 +lon_0=117 +k=1 +x_0=39500000 +y_0=0 +datum=WGS84 +units=m',
    'PROJCS["Custom Transverse Mercator",GEOGCS["WGS 84",DATUM["WGS_84",...]]'
);