-- 查看当前 PostGIS 版本
SELECT PostGIS_Version();
-- 输出示例: 3.4 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

-- 点 (Point)
SELECT ST_GeomFromText('POINT(116.4074 39.9042)');  -- 北京天安门

-- 线 (LineString)
SELECT ST_GeomFromText('LINESTRING(116.40 39.90, 116.42 39.92, 116.44 39.91)');

-- 面 (Polygon) — 注意首尾坐标相同，形成闭合环
SELECT ST_GeomFromText('POLYGON((116.38 39.90, 116.42 39.90, 116.42 39.93, 116.38 39.93, 116.38 39.90))');

-- 多面 (MultiPolygon)
SELECT ST_GeomFromText('MULTIPOLYGON(((0 0, 1 0, 1 1, 0 1, 0 0)), ((2 2, 3 2, 3 3, 2 3, 2 2)))');

-- 输出 WKB 的十六进制表示
SELECT ST_AsHexEWKB(ST_GeomFromText('POINT(116.4074 39.9042)'));

-- 从 WKB 创建几何
SELECT ST_GeomFromEWKB('\x01010000005839b4c876be5d407b14ae47e1524340');

-- 查看 PostGIS 编译时的依赖支持
SELECT name, default_version, installed_version
FROM pg_available_extensions
WHERE name IN ('postgis', 'postgis_topology', 'postgis_raster',
               'pgrouting', 'address_standardizer', 'postgis_sfcgal');

-- 查找某点 3 公里范围内的所有门店
SELECT name, address,
       ST_Distance(
         geom::geography,
         ST_SetSRID(ST_MakePoint(116.4074, 39.9042), 4326)::geography
       ) AS distance_m
FROM stores
WHERE ST_DWithin(
  geom::geography,
  ST_SetSRID(ST_MakePoint(116.4074, 39.9042), 4326)::geography,
  3000
)
ORDER BY distance_m;

-- 创建城市表
CREATE TABLE cities (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    province VARCHAR(50),
    population INTEGER,
    geom GEOMETRY(Point, 4326)
);

-- 插入示例数据
INSERT INTO cities (name, province, population, geom) VALUES
('北京', '北京', 21890000, ST_SetSRID(ST_MakePoint(116.4074, 39.9042), 4326)),
('上海', '上海', 24870000, ST_SetSRID(ST_MakePoint(121.4737, 31.2304), 4326)),
('广州', '广东', 18680000, ST_SetSRID(ST_MakePoint(113.2644, 23.1291), 4326)),
('深圳', '广东', 17560000, ST_SetSRID(ST_MakePoint(114.0579, 22.5431), 4326)),
('成都', '四川', 20940000, ST_SetSRID(ST_MakePoint(104.0668, 30.5728), 4326)),
('杭州', '浙江', 12200000, ST_SetSRID(ST_MakePoint(120.1551, 30.2741), 4326)),
('武汉', '湖北', 12330000, ST_SetSRID(ST_MakePoint(114.3055, 30.5928), 4326)),
('西安', '陕西', 13000000, ST_SetSRID(ST_MakePoint(108.9402, 34.2613), 4326));

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

-- 查询 1：计算北京到上海的直线距离（米）
SELECT ST_Distance(
    (SELECT geom FROM cities WHERE name = '北京')::geography,
    (SELECT geom FROM cities WHERE name = '上海')::geography
) / 1000 AS distance_km;
-- 输出: ~1068 公里

-- 查询 2：查找北京 1500 公里范围内的城市
SELECT name, province, population,
       ROUND(ST_Distance(geom::geography,
             ST_SetSRID(ST_MakePoint(116.4074, 39.9042), 4326)::geography
       ) / 1000) AS distance_km
FROM cities
WHERE ST_DWithin(
    geom::geography,
    ST_SetSRID(ST_MakePoint(116.4074, 39.9042), 4326)::geography,
    1500000
)
ORDER BY distance_km;

-- 查询 3：生成 GeoJSON 输出
SELECT jsonb_build_object(
    'type', 'FeatureCollection',
    'features', jsonb_agg(
        jsonb_build_object(
            'type', 'Feature',
            'geometry', ST_AsGeoJSON(geom)::jsonb,
            'properties', jsonb_build_object(
                'name', name,
                'province', province,
                'population', population
            )
        )
    )
) AS geojson
FROM cities;