产品指南
查询与优化
地理信息检索

PostGIS 地理信息检索

ProtonBase 支持 PostGIS 地理空间扩展，PostGIS 为 ProtonBase 添加了对地理对象的支持，允许在 SQL 中运行空间查询，包括空间关系判断、空间测量、几何操作等。

PostGIS 扩展安装

启用 PostGIS 扩展

-- 创建 PostGIS 扩展
CREATE EXTENSION postgis;
 
-- 验证安装
SELECT PostGIS_version();

空间数据类型

ProtonBase 通过 PostGIS 支持以下主要空间数据类型：

  • POINT - 点 (0维几何对象)
  • LINESTRING - 线 (1维几何对象)
  • POLYGON - 多边形 (2维几何对象)
  • MULTIPOINT, MULTILINESTRING, MULTIPOLYGON - 多点、多线、多多边形
  • GEOMETRYCOLLECTION - 几何集合
  • GEOGRAPHY - 地理坐标系下的几何对象

空间数据导入导出

创建空间数据表

CREATE TABLE spatial_data (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    geom GEOMETRY(GEOMETRY, 4326)  -- SRID 4326 表示 WGS84 坐标系
);
 
-- 或者使用 GEOGRAPHY 类型
CREATE TABLE geographic_data (
    id SERIAL PRIMARY KEY,
    location GEOGRAPHY(POINT, 4326)
);

导入空间数据

  1. 使用 WKT (Well-Known Text) 格式
INSERT INTO spatial_data (name, geom)
VALUES ('Central Park', ST_GeomFromText('POLYGON((-73.9687 40.8006, -73.9587 40.8006, -73.9587 40.7631, -73.9687 40.7631, -73.9687 40.8006))', 4326));
  1. 使用 WKB (Well-Known Binary) 格式
INSERT INTO spatial_data (name, geom)
VALUES ('London', ST_GeomFromWKB(E'\\x0101000020E6100000AAF1D24D6210C0BF1B2FDD2406C14940', 4326));

导出空间数据

  1. 导出为 WKT
SELECT ST_AsText(geom) FROM spatial_data WHERE id = 1;
  1. 导出为 GeoJSON
SELECT ST_AsGeoJSON(geom) FROM spatial_data WHERE id = 1;

空间查询

计算面积

SELECT name, ST_Area(geom)
FROM spatial_data
WHERE ST_GeometryType(geom) = 'ST_Polygon';

计算距离

-- 两点之间距离（单位：米）
SELECT ST_Distance(
    ST_GeomFromText('POINT(-73.935242 40.730610)', 4326)::GEOGRAPHY,
    ST_GeomFromText('POINT(-74.005941 40.712784)', 4326)::GEOGRAPHY
);

包含关系

-- 1. 创建测试表
CREATE TABLE countries (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    geom GEOMETRY(POLYGON, 4326)
);
 
CREATE TABLE cities (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    country_id INT,
    geom GEOMETRY(POINT, 4326)
);
 
CREATE TABLE lakes (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    area_sqkm FLOAT,
    geom GEOMETRY(POLYGON, 4326)
);
 
-- 2. 插入国家数据（简化版边界）
INSERT INTO countries (name, geom) VALUES
('France', ST_GeomFromText('POLYGON((2.55 51.1, -4.8 48.6, -1.8 43.4, 7.6 43.6, 8.7 42.6, 6.8 41.9, 3.0 42.4, 2.55 51.1))', 4326)),
('Germany', ST_GeomFromText('POLYGON((5.9 51.0, 6.1 50.2, 8.2 49.7, 10.2 47.3, 12.1 47.7, 13.0 48.8, 14.3 48.6, 15.0 50.9, 12.1 53.5, 8.7 53.9, 5.9 51.0))', 4326));
 
-- 3. 插入城市数据
INSERT INTO cities (name, country_id, geom) VALUES
('Paris', 1, ST_GeomFromText('POINT(2.3522 48.8566)', 4326)),
('Lyon', 1, ST_GeomFromText('POINT(4.8357 45.7640)', 4326)),
('Berlin', 2, ST_GeomFromText('POINT(13.4050 52.5200)', 4326)),
('Munich', 2, ST_GeomFromText('POINT(11.5820 48.1351)', 4326)),
('London', NULL, ST_GeomFromText('POINT(-0.1278 51.5074)', 4326)); -- 不属于任何国家的城市
 
-- 4. 插入湖泊数据
INSERT INTO lakes (name, area_sqkm, geom) VALUES
('Lake Constance', 536, ST_GeomFromText('POLYGON((9.2 47.6, 9.5 47.5, 9.7 47.6, 9.5 47.7, 9.2 47.6))', 4326)),
('Lake Geneva', 580, ST_GeomFromText('POLYGON((6.5 46.4, 6.9 46.2, 7.2 46.4, 6.8 46.5, 6.5 46.4))', 4326));
 
-- 5. 包含关系查询示例
 
-- 示例1：查找包含特定点的国家
SELECT name FROM countries
WHERE ST_Contains(geom, ST_GeomFromText('POINT(2.3522 48.8566)', 4326));
 
-- 示例2：查找完全在法国境内的湖泊
SELECT l.name, l.area_sqkm
FROM lakes l, countries c
WHERE c.name = 'France' AND ST_Contains(c.geom, l.geom);
 
-- 示例3：统计每个国家包含的城市数量（包括没有城市的国家）
SELECT c.name AS country, COUNT(ci.id) AS city_count
FROM countries c
LEFT JOIN cities ci ON ST_Contains(c.geom, ci.geom)
GROUP BY c.id, c.name
ORDER BY city_count DESC;
 
-- 示例4：查找不属于任何国家的城市
SELECT name FROM cities
WHERE id NOT IN (
    SELECT ci.id FROM cities ci, countries co
    WHERE ST_Contains(co.geom, ci.geom))
AND country_id IS NULL;
 
-- 示例5：查找跨越多个国家的湖泊
SELECT l.name, COUNT(DISTINCT c.id) AS country_count
FROM lakes l, countries c
WHERE ST_Intersects(l.geom, c.geom)
GROUP BY l.id, l.name
HAVING COUNT(DISTINCT c.id) > 1;
 
-- 示例6：使用ST_Within（与ST_Contains相反的关系）
-- 查找巴黎所在的国家
SELECT c.name FROM countries c, cities ci
WHERE ci.name = 'Paris' AND ST_Within(ci.geom, c.geom);
 
-- 示例7：查找完全包含在德国境内且面积大于500平方公里的湖泊
SELECT l.name, l.area_sqkm
FROM lakes l, countries c
WHERE c.name = 'Germany'
  AND ST_Contains(c.geom, l.geom)
  AND l.area_sqkm > 500;
 
-- 示例8：验证城市是否在指定的国家边界内（使用JOIN条件）
SELECT ci.name AS city, co.name AS country
FROM cities ci JOIN countries co ON ST_Contains(co.geom, ci.geom)
WHERE ci.country_id = co.id OR ci.country_id IS NULL;
 
-- 示例9：查找与法国接壤的国家（使用ST_Touches）
SELECT n.name
FROM countries f, countries n
WHERE f.name = 'France'
  AND f.id != n.id
  AND ST_Touches(f.geom, n.geom);
 
-- 示例10：查找部分在法国境内的湖泊（使用ST_Intersects）
SELECT DISTINCT l.name
FROM lakes l, countries c
WHERE c.name = 'France' AND ST_Intersects(l.geom, c.geom);

相交关系

-- 1. 创建测试表
CREATE TABLE roads (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    road_type VARCHAR(50),
    geom GEOMETRY(LINESTRING, 4326)
);
 
CREATE TABLE buildings (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    floors INT,
    geom GEOMETRY(POLYGON, 4326)
);
 
CREATE TABLE parks (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    area_sqkm FLOAT,
    geom GEOMETRY(POLYGON, 4326)
);
 
-- 2. 插入道路数据
INSERT INTO roads (name, road_type, geom) VALUES
('Main Street', 'primary', ST_GeomFromText('LINESTRING(-118.481 34.028, -118.471 34.025, -118.461 34.020, -118.451 34.015)', 4326)),
('Oak Avenue', 'secondary', ST_GeomFromText('LINESTRING(-118.480 34.020, -118.475 34.025, -118.470 34.030, -118.465 34.035)', 4326)),
('Pine Road', 'tertiary', ST_GeomFromText('LINESTRING(-118.455 34.030, -118.460 34.025, -118.465 34.020, -118.470 34.015)', 4326));
 
-- 3. 插入建筑物数据
INSERT INTO buildings (name, floors, geom) VALUES
('City Hall', 5, ST_GeomFromText('POLYGON((-118.473 34.022, -118.470 34.022, -118.470 34.020, -118.473 34.020, -118.473 34.022))', 4326)),
('Library', 3, ST_GeomFromText('POLYGON((-118.463 34.018, -118.460 34.018, -118.460 34.015, -118.463 34.015, -118.463 34.018))', 4326)),
('Shopping Mall', 2, ST_GeomFromText('POLYGON((-118.468 34.028, -118.465 34.028, -118.465 34.025, -118.468 34.025, -118.468 34.028))', 4326));
 
-- 4. 插入公园数据
INSERT INTO parks (name, area_sqkm, geom) VALUES
('Central Park', 1.2, ST_GeomFromText('POLYGON((-118.478 34.032, -118.475 34.032, -118.475 34.028, -118.478 34.028, -118.478 34.032))', 4326)),
('Riverside Park', 0.8, ST_GeomFromText('POLYGON((-118.458 34.022, -118.455 34.022, -118.455 34.018, -118.458 34.018, -118.458 34.022))', 4326));
 
-- 5. 相交关系查询示例
 
-- 示例1：查找与特定道路相交的建筑物
SELECT b.name, b.floors
FROM buildings b, roads r
WHERE r.name = 'Main Street' AND ST_Intersects(b.geom, r.geom);
 
-- 示例2：查找穿过公园的道路
SELECT r.name, r.road_type
FROM roads r, parks p
WHERE p.name = 'Central Park' AND ST_Intersects(r.geom, p.geom);
 
-- 示例3：统计每个公园内相交的道路数量
SELECT p.name, COUNT(r.id) AS road_count
FROM parks p
LEFT JOIN roads r ON ST_Intersects(r.geom, p.geom)
GROUP BY p.id, p.name
ORDER BY road_count DESC;
 
-- 示例4：查找与任何建筑物都不相交的道路
SELECT r.name
FROM roads r
WHERE NOT EXISTS (
    SELECT 1 FROM buildings b
    WHERE ST_Intersects(b.geom, r.geom)
);
 
-- 示例5：查找与多个公园相交的道路
SELECT r.name, COUNT(DISTINCT p.id) AS park_count
FROM roads r, parks p
WHERE ST_Intersects(r.geom, p.geom)
GROUP BY r.id, r.name
HAVING COUNT(DISTINCT p.id) > 1;
 
-- 示例6：使用ST_Crosses查找真正穿过建筑物的道路（不仅仅是接触）
SELECT r.name, b.name
FROM roads r, buildings b
WHERE ST_Crosses(r.geom, b.geom);
 
-- 示例7：查找与道路相交且面积大于1平方公里的公园
SELECT p.name, p.area_sqkm
FROM parks p, roads r
WHERE ST_Intersects(p.geom, r.geom) AND p.area_sqkm > 1;
 
-- 示例8：使用ST_Overlaps查找部分重叠的建筑物（三维关系）
-- 假设建筑物有高度信息
SELECT b1.name AS building1, b2.name AS building2
FROM buildings b1, buildings b2
WHERE b1.id < b2.id AND ST_Overlaps(b1.geom, b2.geom);
 
-- 示例9：查找与道路网络相交的公园边界
SELECT p.name, ST_Length(ST_Intersection(p.geom, r.geom)) AS intersection_length
FROM parks p, roads r
WHERE ST_Intersects(p.geom, r.geom);
 
-- 示例10：使用缓冲区查找接近道路的建筑物（相交缓冲区）
SELECT b.name, ST_Distance(b.geom, r.geom) AS distance
FROM buildings b, roads r
WHERE ST_Intersects(ST_Buffer(r.geom::geography, 50)::geometry, b.geom)
ORDER BY distance;

缓冲区分析

-- 创建建筑物表
CREATE TABLE buildings (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    geom GEOMETRY(POLYGON, 4326)
);
 
-- 创建兴趣点表
CREATE TABLE points_of_interest (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    geom GEOMETRY(POINT, 4326)
);
 
-- 插入建筑物数据（多边形）
INSERT INTO buildings (name, geom) VALUES
('Main Building', ST_GeomFromText('POLYGON((-74.006 40.7128, -74.005 40.7128, -74.005 40.7125, -74.006 40.7125, -74.006 40.7128))', 4326)),
('Science Center', ST_GeomFromText('POLYGON((-74.007 40.7135, -74.006 40.7135, -74.006 40.7130, -74.007 40.7130, -74.007 40.7135))', 4326));
 
-- 插入兴趣点数据（点）
INSERT INTO points_of_interest (name, geom) VALUES
('Statue A', ST_GeomFromText('POINT(-74.0065 40.7126)', 4326)),
('Fountain', ST_GeomFromText('POINT(-74.004 40.7127)', 4326)),
('Monument', ST_GeomFromText('POINT(-74.0062 40.7132)', 4326));
 
 
-- 查找建筑物周围100米缓冲区内所有兴趣点，使用地理类型(geography)确保以米为单位计算
SELECT poi.name AS interest_point,
       b.name AS building,
       ST_Distance(poi.geom::geography, b.geom::geography) AS distance_meters
FROM points_of_interest poi, buildings b
WHERE ST_DWithin(
    poi.geom::geography,
    b.geom::geography,
    100  -- 100米缓冲距离
)
ORDER BY b.name, distance_meters;
 
-- 可视化缓冲区（生成缓冲多边形），为每个建筑物创建100米缓冲区并显示
SELECT
    b.name,
    ST_AsText(ST_Buffer(b.geom::geography, 100)::geometry) AS buffer_geom
FROM buildings b;
近似计算诊断调优