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)
);
导入空间数据
- 使用 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));
- 使用 WKB (Well-Known Binary) 格式
INSERT INTO spatial_data (name, geom)
VALUES ('London', ST_GeomFromWKB(E'\\x0101000020E6100000AAF1D24D6210C0BF1B2FDD2406C14940', 4326));
导出空间数据
- 导出为 WKT
SELECT ST_AsText(geom) FROM spatial_data WHERE id = 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;