CREATE EXTENSION postgis;

-- 查看版本
SELECT PostGIS_Version();

-- 创建地理数据表
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    geom GEOMETRY(Point, 4326)  -- WGS84坐标系
);

-- 插入数据(经度, 纬度)
INSERT INTO locations (name, geom) VALUES
    ('北京', ST_SetSRID(ST_MakePoint(116.4074, 39.9042), 4326)),
    ('上海', ST_SetSRID(ST_MakePoint(121.4737, 31.2304), 4326));

-- 距离计算
SELECT
    a.name AS from_city,
    b.name AS to_city,
    ST_Distance(a.geom::geography, b.geom::geography) / 1000 AS distance_km
FROM locations a, locations b
WHERE a.id <> b.id;

-- 空间索引
CREATE INDEX idx_locations_geom ON locations USING GIST(geom);