postgis

pig repo add pgdg -u          # 添加 PGDG 仓库并更新缓存

CREATE EXTENSION postgis;

CREATE EXTENSION postgis;

SELECT PostGIS_Full_Version();

-- 地理类型列：距离以米为单位，始终使用 WGS 84
CREATE TABLE cities (
    name text PRIMARY KEY,
    location geography(Point, 4326)
);

INSERT INTO cities VALUES
    ('New York',  ST_GeogFromText('POINT(-74.006 40.7128)')),
    ('London',    ST_GeogFromText('POINT(-0.1278 51.5074)')),
    ('Tokyo',     ST_GeogFromText('POINT(139.6917 35.6895)'));

-- 以米为单位的距离（大圆距离）
SELECT a.name, b.name, ST_Distance(a.location, b.location) / 1000 AS distance_km
FROM cities a, cities b WHERE a.name < b.name;

CREATE TABLE buildings (
    id serial PRIMARY KEY,
    name text,
    geom geometry(Polygon, 4326)
);

CREATE TABLE roads (
    id serial PRIMARY KEY,
    name text,
    geom geometry(LineString, 4326)
);

CREATE TABLE sensors (
    id serial PRIMARY KEY,
    label text,
    geom geometry(Point, 4326)
);

INSERT INTO sensors (label, geom) VALUES
    ('S1', ST_GeomFromText('POINT(-73.985 40.748)', 4326)),
    ('S2', ST_GeomFromText('POINT(-73.979 40.754)', 4326));

INSERT INTO sensors (label, geom) VALUES
    ('S3', ST_SetSRID(ST_MakePoint(-73.990, 40.735), 4326));

INSERT INTO buildings (name, geom) VALUES
    ('Plaza', ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[[[-73.98,40.74],[-73.97,40.74],[-73.97,40.75],[-73.98,40.75],[-73.98,40.74]]]}'));

CREATE INDEX idx_sensors_geom ON sensors USING GIST (geom);
CREATE INDEX idx_buildings_geom ON buildings USING GIST (geom);
CREATE INDEX idx_roads_geom ON roads USING GIST (geom);

CREATE INDEX idx_sensors_geom_brin ON sensors USING BRIN (geom);

-- 从坐标创建点
SELECT ST_MakePoint(-73.985, 40.748);

-- 创建带 SRID 的点
SELECT ST_SetSRID(ST_MakePoint(-73.985, 40.748), 4326);

-- 从 WKT 创建
SELECT ST_GeomFromText('LINESTRING(0 0, 1 1, 2 1)', 4326);

-- 从 GeoJSON 创建
SELECT ST_GeomFromGeoJSON('{"type":"Point","coordinates":[-73.985,40.748]}');

-- 从两个点创建线段
SELECT ST_MakeLine(
    ST_MakePoint(0, 0),
    ST_MakePoint(1, 1)
);

-- 从闭合线串创建多边形
SELECT ST_MakePolygon(
    ST_GeomFromText('LINESTRING(0 0, 1 0, 1 1, 0 1, 0 0)')
);

-- 两个几何对象之间的距离（以 SRID 单位计）
SELECT ST_Distance(
    ST_GeomFromText('POINT(0 0)', 4326),
    ST_GeomFromText('POINT(1 1)', 4326)
);

-- 使用地理类型计算以米为单位的距离
SELECT ST_Distance(
    'SRID=4326;POINT(-73.985 40.748)'::geography,
    'SRID=4326;POINT(-0.1278 51.5074)'::geography
);

-- 面积（以 SRID 单位计，或地理类型以平方米计）
SELECT ST_Area(geom) FROM buildings;

-- 线串长度
SELECT ST_Length(geom) FROM roads;

-- 多边形周长
SELECT ST_Perimeter(geom) FROM buildings;

-- A 是否包含 B？
SELECT ST_Contains(a.geom, b.geom) FROM buildings a, sensors b;

-- A 与 B 是否相交？
SELECT ST_Intersects(a.geom, b.geom) FROM buildings a, roads b;

-- B 是否在 A 的距离 D 以内？（索引友好）
SELECT ST_DWithin(a.geom, b.geom, 0.01) FROM sensors a, sensors b;

-- A 和 B 是否在距离 D 以内？（地理类型，单位为米）
SELECT ST_DWithin(a.location, b.location, 10000) FROM cities a, cities b;

-- A 是否与 B 接触？（共享边界但不共享内部）
SELECT ST_Touches(a.geom, b.geom) FROM buildings a, roads b;

-- A 是否穿越 B？
SELECT ST_Crosses(a.geom, b.geom) FROM roads a, roads b;

-- A 是否与 B 重叠？（同维度，不完全相同）
SELECT ST_Overlaps(a.geom, b.geom) FROM buildings a, buildings b;

-- 缓冲区（按距离扩展几何对象）
SELECT ST_Buffer(geom, 0.001) FROM sensors;

-- 两个几何对象的交集
SELECT ST_Intersection(a.geom, b.geom) FROM buildings a, buildings b
WHERE ST_Intersects(a.geom, b.geom) AND a.id < b.id;

-- 几何对象的联合
SELECT ST_Union(geom) FROM buildings WHERE name LIKE 'Block%';

-- 凸包
SELECT ST_ConvexHull(ST_Collect(geom)) FROM sensors;

-- 简化几何对象（Douglas-Peucker 算法）
SELECT ST_Simplify(geom, 0.0001) FROM roads;

-- 质心
SELECT ST_Centroid(geom) FROM buildings;

-- 沃罗诺伊图
SELECT ST_VoronoiPolygons(ST_Collect(geom)) FROM sensors;

-- 从 WGS 84 (4326) 转换到 Web 墨卡托 (3857)
SELECT ST_Transform(geom, 3857) FROM sensors;

-- 转换到 UTM 分区以进行米制计算
SELECT ST_Area(ST_Transform(geom, 32618)) AS area_sqm FROM buildings;

-- 设置几何对象的 SRID（不会重新投影）
SELECT ST_SetSRID(geom, 4326) FROM sensors;

-- 获取当前 SRID
SELECT ST_SRID(geom) FROM sensors;

-- 转为 GeoJSON
SELECT ST_AsGeoJSON(geom) FROM sensors;

-- 转为 WKT
SELECT ST_AsText(geom) FROM sensors;

-- 转为 KML
SELECT ST_AsKML(geom) FROM sensors;

-- 转为 SVG 路径
SELECT ST_AsSVG(geom) FROM buildings;

-- 转为 EWKT（包含 SRID）
SELECT ST_AsEWKT(geom) FROM sensors;

SELECT label, ST_Distance(
    geom::geography,
    ST_SetSRID(ST_MakePoint(-73.985, 40.748), 4326)::geography
) AS distance_m
FROM sensors
WHERE ST_DWithin(
    geom::geography,
    ST_SetSRID(ST_MakePoint(-73.985, 40.748), 4326)::geography,
    500
)
ORDER BY distance_m;

SELECT s.label, b.name
FROM sensors s
JOIN buildings b ON ST_Contains(b.geom, s.geom);

SELECT b.name, COUNT(s.id) AS sensor_count
FROM buildings b
LEFT JOIN sensors s ON ST_Contains(b.geom, s.geom)
GROUP BY b.name;

SELECT label, geom <-> ST_SetSRID(ST_MakePoint(-73.985, 40.748), 4326) AS dist
FROM sensors
ORDER BY geom <-> ST_SetSRID(ST_MakePoint(-73.985, 40.748), 4326)
LIMIT 5;

-- 将所有传感器点收集为 MultiPoint
SELECT ST_Collect(geom) FROM sensors;

-- 计算最小外接圆
SELECT ST_MinimumBoundingCircle(ST_Collect(geom)) FROM sensors;

SELECT ST_Area(geom::geography) AS area_sqm FROM buildings;