Appearance
PostgreSQL 几何函数和运算符
概述
PostgreSQL 提供了强大的几何数据类型支持，包括 point、box、lseg、line、path、polygon 和 circle 七种几何类型。这些类型配备了丰富的函数和运算符，使得在数据库中处理空间数据变得非常便捷。
INFO
几何类型在 GIS（地理信息系统）、CAD 系统、游戏开发、建筑设计等领域有着广泛应用。掌握这些函数和运算符对于处理空间数据至关重要。
几何类型简介
几何运算符
PostgreSQL 为几何类型提供了丰富的运算符，可以进行平移、旋转、缩放、距离计算、包含关系判断等操作。
算术运算符
平移运算（+ 和 -）
应用场景：在地图应用中移动标记点、在 CAD 系统中平移图形等。
sql
-- 将盒子向右移动2个单位
SELECT box '(1,1),(0,0)' + point '(2,0)' AS moved_box;
-- 结果: (3,1),(2,0)
-- 将圆向左下移动
SELECT circle '<(5,5),2>' - point '(1,1)' AS moved_circle;
-- 结果: <(4,4),2>
-- 路径连接（仅适用于开放路径）
SELECT path '[(0,0),(1,1)]' + path '[(2,2),(3,3)]' AS connected_path;
-- 结果: [(0,0),(1,1),(2,2),(3,3)]sql
-- 场景：游戏中角色移动系统
CREATE TABLE characters (
id SERIAL PRIMARY KEY,
name VARCHAR(50),
position POINT,
collision_box BOX
);
-- 插入角色数据
INSERT INTO characters (name, position, collision_box) VALUES
('Player1', point '(10,10)', box '(11,11),(9,9)'),
('Enemy1', point '(20,15)', box '(21,16),(19,14)');
-- 角色向右移动5个单位
UPDATE characters
SET position = position + point '(5,0)',
collision_box = collision_box + point '(5,0)'
WHERE name = 'Player1';
-- 查看移动后的位置
SELECT name, position, collision_box FROM characters;分析过程：
- 平移运算符将第二个点的坐标加到或减去第一个几何对象的每个点上
- 这种操作不改变几何对象的形状和大小，只改变位置
- 对于路径连接，只有当两个路径都是开放的时才能连接
复数运算（* 和 /）
应用场景：图形的旋转和缩放操作，在计算机图形学中非常常用。
sql
-- 将路径放大3倍
SELECT path '((0,0),(1,0),(1,1))' * point '(3.0,0)' AS scaled_path;
-- 结果: ((0,0),(3,0),(3,3))
-- 将路径逆时针旋转45度
SELECT path '((0,0),(1,0),(1,1))' * point(cosd(45), sind(45)) AS rotated_path;
-- 结果: ((0,0),(0.707...,0.707...),(0,1.414...))
-- 将路径缩小一半
SELECT path '((0,0),(2,0),(2,2))' / point '(2.0,0)' AS shrunk_path;
-- 结果: ((0,0),(1,0),(1,1))sql
-- 建筑设计中的房间布局
CREATE TABLE rooms (
id SERIAL PRIMARY KEY,
name VARCHAR(50),
outline POLYGON,
center_point POINT
);
-- 插入一个矩形房间
INSERT INTO rooms (name, outline, center_point) VALUES
('会议室', polygon '((0,0),(4,0),(4,3),(0,3))', point '(2,1.5)');
-- 将房间逆时针旋转90度
UPDATE rooms
SET outline = outline * point(cosd(90), sind(90)),
center_point = center_point * point(cosd(90), sind(90))
WHERE name = '会议室';
SELECT name, outline, center_point FROM rooms;分析过程：
- 乘法运算将几何对象的每个点作为复数进行复数乘法
- 当第二个点为
(r,0)时，实现缩放操作，r 为缩放比例 - 当第二个点为
(cos(θ), sin(θ))时，实现逆时针旋转 θ 角度 - 除法运算相当于乘以倒数，实现反向的缩放和旋转
几何测量运算符
长度和中心点
sql
-- 计算路径总长度
SELECT @-@ path '[(0,0),(3,0),(3,4)]' AS path_length;
-- 结果: 7 (3+4)
-- 计算线段长度
SELECT @-@ lseg '[(0,0),(3,4)]' AS segment_length;
-- 结果: 5
-- 计算几何对象的中心点
SELECT @@ box '(4,4),(0,0)' AS box_center;
-- 结果: (2,2)
SELECT @@ circle '<(3,3),5>' AS circle_center;
-- 结果: (3,3)sql
-- 物流配送中心选址：找到所有配送点的几何中心
CREATE TABLE delivery_points (
id SERIAL PRIMARY KEY,
name VARCHAR(50),
location POINT,
service_area CIRCLE
);
INSERT INTO delivery_points (name, location, service_area) VALUES
('仓库A', point '(10,20)', circle '<(10,20),5>'),
('仓库B', point '(30,10)', circle '<(30,10),8>'),
('仓库C', point '(20,30)', circle '<(20,30),6>');
-- 计算所有配送点的边界框和中心
WITH boundary AS (
SELECT box(polygon(path(ARRAY[location]))) AS bounds
FROM delivery_points
)
SELECT @@ bounds AS optimal_center
FROM boundary;
-- 计算各仓库间的配送路径长度
SELECT
a.name AS from_warehouse,
b.name AS to_warehouse,
a.location <-> b.location AS distance
FROM delivery_points a
CROSS JOIN delivery_points b
WHERE a.id < b.id;距离和位置关系
距离计算
sql
-- 计算两点间距离
SELECT point '(0,0)' <-> point '(3,4)' AS point_distance;
-- 结果: 5
-- 计算圆之间的最近距离
SELECT circle '<(0,0),1>' <-> circle '<(5,0),1>' AS circle_distance;
-- 结果: 3 (5-1-1)
-- 计算点到线段的距离
SELECT point '(0,0)' <-> lseg '[(1,1),(3,3)]' AS point_to_line_distance;sql
-- 基于位置的餐厅推荐系统
CREATE TABLE restaurants (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
location POINT,
delivery_range CIRCLE,
rating DECIMAL(3,2)
);
INSERT INTO restaurants (name, location, delivery_range, rating) VALUES
('麻辣香锅', point '(116.3974, 39.9093)', circle '<(116.3974, 39.9093), 0.02>', 4.5),
('川菜馆', point '(116.4013, 39.9158)', circle '<(116.4013, 39.9158), 0.015>', 4.2),
('火锅店', point '(116.3945, 39.9078)', circle '<(116.3945, 39.9078), 0.025>', 4.7);
-- 用户当前位置
SET @user_location = point '(116.3987, 39.9100)';
-- 查找距离用户最近的餐厅，按距离排序
SELECT
name,
location,
@user_location <-> location AS distance_km,
rating,
-- 检查是否在配送范围内
@user_location <@ delivery_range AS in_delivery_range
FROM restaurants
ORDER BY @user_location <-> location
LIMIT 5;包含关系
sql
-- 点是否在圆内
SELECT point '(1,1)' <@ circle '<(0,0),2>' AS point_in_circle;
-- 结果: t (true)
-- 圆是否包含点
SELECT circle '<(0,0),2>' @> point '(1,1)' AS circle_contains_point;
-- 结果: t (true)
-- 盒子是否包含另一个盒子
SELECT box '(5,5),(0,0)' @> box '(3,3),(1,1)' AS box_contains_box;
-- 结果: t (true)sql
-- 电子围栏监控系统
CREATE TABLE geo_fences (
id SERIAL PRIMARY KEY,
name VARCHAR(50),
fence_area POLYGON,
alert_enabled BOOLEAN DEFAULT true
);
CREATE TABLE vehicles (
id SERIAL PRIMARY KEY,
license_plate VARCHAR(20),
current_location POINT,
last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 创建几个围栏区域
INSERT INTO geo_fences (name, fence_area) VALUES
('工业园区', polygon '((0,0),(10,0),(10,8),(0,8))'),
('居民区', polygon '((5,5),(15,5),(15,15),(5,15))'),
('禁行区', polygon '((12,12),(18,12),(18,18),(12,18))');
-- 插入车辆数据
INSERT INTO vehicles (license_plate, current_location) VALUES
('京A12345', point '(3,4)'),
('京B67890', point '(7,7)'),
('京C11111', point '(14,14)');
-- 检查哪些车辆在哪个围栏内
SELECT
v.license_plate,
v.current_location,
f.name AS fence_name,
f.fence_area @> v.current_location AS is_inside
FROM vehicles v
CROSS JOIN geo_fences f
WHERE f.fence_area @> v.current_location;
-- 查找违规进入禁行区的车辆
SELECT
v.license_plate,
v.current_location,
v.last_update
FROM vehicles v
JOIN geo_fences f ON f.name = '禁行区'
WHERE f.fence_area @> v.current_location;位置关系运算符
方向关系
sql
-- 左右关系
SELECT circle '<(0,0),1>' << circle '<(5,0),1>' AS left_of;
-- 结果: t (第一个圆在第二个圆的左边)
SELECT circle '<(5,0),1>' >> circle '<(0,0),1>' AS right_of;
-- 结果: t (第一个圆在第二个圆的右边)
-- 上下关系
SELECT box '(2,2),(0,0)' <<| box '(4,4),(2,3)' AS below;
-- 结果: t (第一个盒子在第二个盒子下方)
SELECT box '(4,4),(2,3)' |>> box '(2,2),(0,0)' AS above;
-- 结果: t (第一个盒子在第二个盒子上方)sql
-- 空间数据查询优化
CREATE TABLE buildings (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
footprint POLYGON,
height INTEGER,
building_type VARCHAR(50)
);
-- 创建空间索引
CREATE INDEX idx_buildings_footprint ON buildings USING GIST (footprint);
-- 插入建筑数据
INSERT INTO buildings (name, footprint, height, building_type) VALUES
('办公楼A', polygon '((0,0),(20,0),(20,15),(0,15))', 50, '办公'),
('住宅楼B', polygon '((25,5),(45,5),(45,25),(25,25))', 80, '住宅'),
('商场C', polygon '((10,20),(40,20),(40,40),(10,40))', 30, '商业');
-- 查找某个点东侧的所有建筑
SELECT name, building_type, height
FROM buildings
WHERE footprint >> box(point '(15,10)', point '(15,10)');
-- 查找某个区域南侧的建筑
SELECT name, building_type
FROM buildings
WHERE footprint <<| polygon '((20,25),(30,25),(30,35),(20,35))';相交和重叠
sql
-- 判断两个盒子是否重叠
SELECT box '(3,3),(0,0)' && box '(5,5),(2,2)' AS boxes_overlap;
-- 结果: t (有重叠区域)
-- 判断线段是否相交
SELECT lseg '[(-1,0),(1,0)]' ?# lseg '[(0,-1),(0,1)]' AS segments_intersect;
-- 结果: t (在原点相交)
-- 计算两个盒子的交集
SELECT box '(4,4),(0,0)' # box '(6,6),(2,2)' AS intersection;
-- 结果: (4,4),(2,2)sql
-- 游戏中的碰撞检测系统
CREATE TABLE game_objects (
id SERIAL PRIMARY KEY,
object_type VARCHAR(20),
position POINT,
bounding_box BOX,
velocity POINT,
is_solid BOOLEAN DEFAULT true
);
-- 插入游戏对象
INSERT INTO game_objects (object_type, position, bounding_box, velocity, is_solid) VALUES
('player', point '(10,10)', box '(12,12),(8,8)', point '(1,0)', true),
('enemy', point '(15,10)', box '(17,12),(13,8)', point '(-0.5,0)', true),
('wall', point '(20,10)', box '(22,15),(18,5)', point '(0,0)', true),
('item', point '(12,10)', box '(13,11),(11,9)', point '(0,0)', false);
-- 检测玩家与其他对象的碰撞
WITH player_obj AS (
SELECT * FROM game_objects WHERE object_type = 'player'
)
SELECT
go.object_type,
go.position,
go.bounding_box && p.bounding_box AS collision_detected,
go.bounding_box # p.bounding_box AS collision_area
FROM game_objects go
CROSS JOIN player_obj p
WHERE go.object_type != 'player'
AND go.bounding_box && p.bounding_box;
-- 预测下一帧的碰撞（简单物理模拟）
WITH next_frame AS (
SELECT
id,
object_type,
position + velocity AS next_position,
bounding_box + velocity AS next_bounding_box
FROM game_objects
)
SELECT
a.object_type AS obj1,
b.object_type AS obj2,
a.next_bounding_box && b.next_bounding_box AS will_collide
FROM next_frame a
CROSS JOIN next_frame b
WHERE a.id < b.id
AND a.next_bounding_box && b.next_bounding_box;几何函数
PostgreSQL 提供了丰富的几何函数用于计算几何对象的属性和进行几何分析。
面积和长度计算
sql
-- 计算面积
SELECT area(box '(4,4),(0,0)') AS box_area;
-- 结果: 16
SELECT area(circle '<(0,0),3>') AS circle_area;
-- 结果: 28.274333882308138 (π * 3²)
SELECT area(polygon '((0,0),(3,0),(3,4),(0,4))') AS polygon_area;
-- 结果: 12
-- 计算长度
SELECT length(lseg '[(0,0),(3,4)]') AS segment_length;
-- 结果: 5
SELECT length(path '[(0,0),(3,0),(3,4)]') AS path_length;
-- 结果: 7sql
-- 房地产管理系统
CREATE TABLE properties (
id SERIAL PRIMARY KEY,
property_id VARCHAR(20),
address TEXT,
lot_boundary POLYGON,
building_footprint POLYGON,
price_per_sqm DECIMAL(10,2)
);
INSERT INTO properties (property_id, address, lot_boundary, building_footprint, price_per_sqm) VALUES
('P001', '北京市朝阳区xxx路123号',
polygon '((0,0),(30,0),(30,25),(0,25))',
polygon '((5,5),(25,5),(25,20),(5,20))',
15000),
('P002', '北京市海淀区yyy街456号',
polygon '((0,0),(40,0),(40,30),(0,30))',
polygon '((8,8),(32,8),(32,22),(8,22))',
18000);
-- 计算每个房产的详细面积信息
SELECT
property_id,
address,
ROUND(area(lot_boundary)::NUMERIC, 2) AS lot_area_sqm,
ROUND(area(building_footprint)::NUMERIC, 2) AS building_area_sqm,
ROUND((area(lot_boundary) - area(building_footprint))::NUMERIC, 2) AS yard_area_sqm,
ROUND((area(building_footprint) / area(lot_boundary) * 100)::NUMERIC, 2) AS building_coverage_pct,
ROUND((area(building_footprint) * price_per_sqm)::NUMERIC, 2) AS estimated_value
FROM properties;
-- 查找建筑覆盖率超过60%的房产
SELECT property_id, address,
ROUND((area(building_footprint) / area(lot_boundary) * 100)::NUMERIC, 2) AS coverage_pct
FROM properties
WHERE area(building_footprint) / area(lot_boundary) > 0.6;几何属性函数
sql
-- 中心点计算
SELECT center(box '(6,4),(2,2)') AS box_center;
-- 结果: (4,3)
SELECT center(circle '<(5,5),3>') AS circle_center;
-- 结果: (5,5)
-- 圆的属性
SELECT radius(circle '<(0,0),5>') AS circle_radius;
-- 结果: 5
SELECT diameter(circle '<(0,0),5>') AS circle_diameter;
-- 结果: 10
-- 盒子的尺寸
SELECT width(box '(8,6),(2,2)') AS box_width;
-- 结果: 6
SELECT height(box '(8,6),(2,2)') AS box_height;
-- 结果: 4
-- 路径和多边形的点数
SELECT npoints(path '[(0,0),(1,1),(2,0),(1,-1)]') AS path_points;
-- 结果: 4
SELECT npoints(polygon '((0,0),(2,0),(2,2),(0,2))') AS polygon_points;
-- 结果: 4sql
-- 无线传感器网络覆盖分析
CREATE TABLE sensors (
id SERIAL PRIMARY KEY,
sensor_id VARCHAR(20),
location POINT,
coverage_area CIRCLE,
battery_level INTEGER,
sensor_type VARCHAR(30),
installation_date DATE
);
INSERT INTO sensors (sensor_id, location, coverage_area, battery_level, sensor_type, installation_date) VALUES
('S001', point '(10,10)', circle '<(10,10),8>', 85, '温度传感器', '2024-01-15'),
('S002', point '(25,15)', circle '<(25,15),6>', 72, '湿度传感器', '2024-01-20'),
('S003', point '(15,25)', circle '<(15,25),10>', 91, '光照传感器', '2024-02-01'),
('S004', point '(30,8)', circle '<(30,8),7>', 68, '温度传感器', '2024-02-10');
-- 分析传感器网络的覆盖情况
SELECT
sensor_id,
sensor_type,
location,
ROUND(area(coverage_area)::NUMERIC, 2) AS coverage_area_sqm,
radius(coverage_area) AS coverage_radius,
center(coverage_area) AS coverage_center,
battery_level
FROM sensors
ORDER BY area(coverage_area) DESC;
-- 查找覆盖重叠的传感器对
SELECT
s1.sensor_id AS sensor1,
s2.sensor_id AS sensor2,
s1.coverage_area && s2.coverage_area AS has_overlap,
ROUND((s1.location <-> s2.location)::NUMERIC, 2) AS distance_between,
CASE
WHEN s1.location <-> s2.location < (radius(s1.coverage_area) + radius(s2.coverage_area))
THEN '覆盖重叠'
ELSE '覆盖分离'
END AS coverage_status
FROM sensors s1
CROSS JOIN sensors s2
WHERE s1.id < s2.id;
-- 计算网络总覆盖面积（简化计算，不考虑重叠）
SELECT
COUNT(*) AS total_sensors,
ROUND(SUM(area(coverage_area))::NUMERIC, 2) AS total_coverage_area,
ROUND(AVG(battery_level)::NUMERIC, 2) AS avg_battery_level
FROM sensors;路径操作函数
sql
-- 检查路径是否闭合
SELECT isclosed(path '((0,0),(1,1),(2,0))') AS is_closed_path;
-- 结果: t (true，用圆括号表示闭合路径)
SELECT isopen(path '[(0,0),(1,1),(2,0)]') AS is_open_path;
-- 结果: t (true，用方括号表示开放路径)
-- 转换路径形式
SELECT pclose(path '[(0,0),(1,1),(2,0)]') AS closed_path;
-- 结果: ((0,0),(1,1),(2,0))
SELECT popen(path '((0,0),(1,1),(2,0))') AS open_path;
-- 结果: [(0,0),(1,1),(2,0)]sql
-- GPS轨迹数据分析系统
CREATE TABLE gps_tracks (
id SERIAL PRIMARY KEY,
user_id INTEGER,
track_name VARCHAR(100),
track_path PATH,
start_time TIMESTAMP,
end_time TIMESTAMP,
activity_type VARCHAR(30)
);
INSERT INTO gps_tracks (user_id, track_name, track_path, start_time, end_time, activity_type) VALUES
(1, '晨跑路线', path '[(116.3974,39.9093),(116.3985,39.9105),(116.3995,39.9115),(116.4010,39.9125)]',
'2024-03-01 06:00:00', '2024-03-01 06:30:00', '跑步'),
(1, '上班路线', path '[(116.3974,39.9093),(116.4013,39.9158),(116.4156,39.9042),(116.4307,39.9002)]',
'2024-03-01 08:00:00', '2024-03-01 08:45:00', '通勤'),
(2, '骑行路线', path '[(116.3945,39.9078),(116.3890,39.9145),(116.3845,39.9201),(116.3820,39.9245)]',
'2024-03-01 07:30:00', '2024-03-01 08:15:00', '骑行');
-- 分析轨迹特征
SELECT
user_id,
track_name,
activity_type,
npoints(track_path) AS waypoint_count,
ROUND(length(track_path)::NUMERIC, 6) AS track_length_km,
isopen(track_path) AS is_open_track,
EXTRACT(EPOCH FROM (end_time - start_time))/60 AS duration_minutes,
ROUND((length(track_path) / (EXTRACT(EPOCH FROM (end_time - start_time))/3600))::NUMERIC, 2) AS avg_speed_kmh
FROM gps_tracks;
-- 查找相似的轨迹起点
SELECT
t1.track_name AS track1,
t2.track_name AS track2,
ROUND((point(track_path,0) <-> point(track_path,0))::NUMERIC, 6) AS start_point_distance
FROM gps_tracks t1
CROSS JOIN gps_tracks t2
WHERE t1.id < t2.id
AND point(track_path,0) <-> point(track_path,0) < 0.01; -- 1公里内
-- 轨迹质量分析（检查是否有异常点跳跃）
WITH track_segments AS (
SELECT
id,
track_name,
activity_type,
track_path,
npoints(track_path) as total_points
FROM gps_tracks
)
SELECT
track_name,
activity_type,
total_points,
CASE
WHEN total_points < 3 THEN '数据不足'
WHEN length(track_path) = 0 THEN '无移动'
WHEN length(track_path) / total_points > 0.1 THEN '轨迹粗糙'
ELSE '轨迹正常'
END AS track_quality
FROM track_segments;几何类型转换函数
PostgreSQL 提供了丰富的类型转换函数，允许在不同几何类型之间进行转换。
基本类型转换
sql
-- 点转换为盒子
SELECT box(point '(5,3)') AS point_to_box;
-- 结果: (5,3),(5,3) (零面积的盒子)
-- 两点构造盒子
SELECT box(point '(1,1)', point '(4,3)') AS box_from_points;
-- 结果: (4,3),(1,1)
-- 圆转换为内切盒子
SELECT box(circle '<(0,0),5>') AS circle_to_box;
-- 结果: (3.536,3.536),(-3.536,-3.536)
-- 多边形转换为边界盒
SELECT box(polygon '((0,0),(3,0),(3,2),(0,2))') AS polygon_to_box;
-- 结果: (3,2),(0,0)
-- 盒子转换为圆
SELECT circle(box '(4,4),(0,0)') AS box_to_circle;
-- 结果: <(2,2),2.828...>
-- 中心和半径构造圆
SELECT circle(point '(3,3)', 5.0) AS circle_from_center_radius;
-- 结果: <(3,3),5>sql
-- 地理数据标准化处理
CREATE TABLE raw_spatial_data (
id SERIAL PRIMARY KEY,
object_name VARCHAR(100),
data_type VARCHAR(20),
raw_geometry TEXT,
processed_geometry GEOMETRY
);
-- 假设我们有不同格式的空间数据需要标准化
CREATE TABLE spatial_objects (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
object_type VARCHAR(30),
center_point POINT,
bounding_box BOX,
approximate_circle CIRCLE,
detailed_shape POLYGON
);
-- 插入测试数据
INSERT INTO spatial_objects (name, object_type, center_point) VALUES
('中央公园', '公园', point '(116.3912,39.9203)'),
('购物中心', '商业', point '(116.4074,39.9042)'),
('学校', '教育', point '(116.3845,39.9167)');
-- 为每个对象生成标准化的几何表示
UPDATE spatial_objects SET
bounding_box = box(center_point + point '(-0.01,-0.01)', center_point + point '(0.01,0.01)')),
approximate_circle = circle(center_point, 0.015),
detailed_shape = polygon(box(center_point + point '(-0.005,-0.005)', center_point + point '(0.005,0.005)')))
WHERE bounding_box IS NULL;
-- 查看标准化结果
SELECT
name,
object_type,
center_point,
bounding_box,
approximate_circle,
area(detailed_shape) as shape_area
FROM spatial_objects;
-- 计算对象间的关系矩阵
SELECT
o1.name AS object1,
o2.name AS object2,
ROUND((o1.center_point <-> o2.center_point)::NUMERIC, 6) AS center_distance,
o1.approximate_circle && o2.approximate_circle AS circles_overlap,
o1.bounding_box && o2.bounding_box AS boxes_overlap
FROM spatial_objects o1
CROSS JOIN spatial_objects o2
WHERE o1.id < o2.id;复杂转换应用
sql
-- 盒子对角线提取
SELECT diagonal(box '(5,4),(1,2)') AS box_diagonal;
-- 结果: [(5,4),(1,2)]
-- 等价于
SELECT lseg(box '(5,4),(1,2)') AS box_diagonal_lseg;
-- 结果: [(5,4),(1,2)]
-- 两点构造线段
SELECT lseg(point '(0,0)', point '(3,4)') AS line_segment;
-- 结果: [(0,0),(3,4)]
-- 多边形转换为路径
SELECT path(polygon '((0,0),(2,0),(2,2),(0,2))') AS polygon_to_path;
-- 结果: ((0,0),(2,0),(2,2),(0,2))sql
-- 建筑设计中的几何转换应用
CREATE TABLE building_elements (
id SERIAL PRIMARY KEY,
element_type VARCHAR(30),
element_name VARCHAR(100),
base_shape POLYGON,
center_point POINT,
bounding_rect BOX,
circulation_area CIRCLE
);
-- 插入建筑元素的基本形状
INSERT INTO building_elements (element_type, element_name, base_shape) VALUES
('房间', '主卧室', polygon '((0,0),(5,0),(5,4),(0,4))'),
('房间', '客厅', polygon '((6,0),(12,0),(12,6),(6,6))'),
('房间', '厨房', polygon '((0,5),(4,5),(4,8),(0,8))'),
('设施', '楼梯间', polygon '((5,5),(7,5),(7,8),(5,8))');
-- 自动计算每个元素的派生几何属性
UPDATE building_elements SET
center_point = point(base_shape),
bounding_rect = box(base_shape),
circulation_area = circle(point(base_shape),
LEAST(width(box(base_shape)), height(box(base_shape))) * 0.3)
WHERE center_point IS NULL;
-- 生成设计分析报告
SELECT
element_type,
element_name,
ROUND(area(base_shape)::NUMERIC, 2) AS floor_area_sqm,
center_point,
ROUND(width(bounding_rect)::NUMERIC, 2) AS width_m,
ROUND(height(bounding_rect)::NUMERIC, 2) AS length_m,
ROUND(area(circulation_area)::NUMERIC, 2) AS circulation_area_sqm,
ROUND((area(circulation_area) / area(base_shape) * 100)::NUMERIC, 1) AS circulation_ratio_pct
FROM building_elements
ORDER BY area(base_shape) DESC;
-- 检查元素间的空间关系
SELECT
e1.element_name AS element1,
e2.element_name AS element2,
e1.bounding_rect && e2.bounding_rect AS adjacency_possible,
ROUND((e1.center_point <-> e2.center_point)::NUMERIC, 2) AS center_distance,
CASE
WHEN e1.bounding_rect && e2.bounding_rect THEN '相邻'
WHEN e1.center_point <-> e2.center_point < 3 THEN '接近'
ELSE '分离'
END AS spatial_relationship
FROM building_elements e1
CROSS JOIN building_elements e2
WHERE e1.id < e2.id;
-- 优化布局建议（基于空间效率）
WITH space_efficiency AS (
SELECT
element_name,
element_type,
area(base_shape) as usable_area,
area(bounding_rect) as total_area,
area(base_shape) / area(bounding_rect) as efficiency_ratio
FROM building_elements
)
SELECT
element_name,
element_type,
ROUND(usable_area::NUMERIC, 2) as usable_area_sqm,
ROUND(efficiency_ratio::NUMERIC, 3) as space_efficiency,
CASE
WHEN efficiency_ratio > 0.9 THEN '高效'
WHEN efficiency_ratio > 0.7 THEN '良好'
ELSE '需要优化'
END as efficiency_rating
FROM space_efficiency
ORDER BY efficiency_ratio DESC;高级应用场景
地理信息系统 (GIS)
sql
-- 城市规划中的空间分析
CREATE TABLE city_zones (
id SERIAL PRIMARY KEY,
zone_name VARCHAR(100),
zone_type VARCHAR(30),
boundary POLYGON,
population_density INTEGER,
zoning_rules TEXT
);
CREATE TABLE public_facilities (
id SERIAL PRIMARY KEY,
facility_name VARCHAR(100),
facility_type VARCHAR(30),
location POINT,
service_radius CIRCLE,
capacity INTEGER
);
-- 插入城市区域数据
INSERT INTO city_zones (zone_name, zone_type, boundary, population_density) VALUES
('商务区A', '商业区', polygon '((0,0),(10,0),(10,8),(0,8))', 5000),
('居民区B', '住宅区', polygon '((12,0),(25,0),(25,15),(12,15))', 8000),
('工业区C', '工业区', polygon '((0,10),(8,10),(8,20),(0,20))', 1200),
('文教区D', '文教区', polygon '((10,10),(20,10),(20,20),(10,20))', 3500);
-- 插入公共设施数据
INSERT INTO public_facilities (facility_name, facility_type, location, service_radius, capacity) VALUES
('中心医院', '医疗', point '(15,12)', circle '<(15,12),8>', 500),
('第一中学', '教育', point '(15,15)', circle '<(15,15),5>', 1200),
('购物中心', '商业', point '(5,4)', circle '<(5,4),6>', 2000),
('体育中心', '体育', point '(18,5)', circle '<(18,5),7>', 800);
-- 分析公共设施的服务覆盖
SELECT
f.facility_name,
f.facility_type,
z.zone_name,
z.zone_type,
ROUND((area(z.boundary # box(f.service_radius)) / area(z.boundary) * 100)::NUMERIC, 1) AS coverage_percentage,
ROUND((z.population_density * area(z.boundary # box(f.service_radius)))::NUMERIC, 0) AS served_population
FROM public_facilities f
CROSS JOIN city_zones z
WHERE f.service_radius && box(z.boundary)
ORDER BY f.facility_name, coverage_percentage DESC;
-- 识别服务不足的区域
WITH zone_coverage AS (
SELECT
z.zone_name,
z.zone_type,
z.population_density,
COUNT(f.id) as facility_count,
AVG(z.boundary <-> f.location) as avg_distance_to_facilities
FROM city_zones z
LEFT JOIN public_facilities f ON f.service_radius && box(z.boundary)
GROUP BY z.id, z.zone_name, z.zone_type, z.population_density
)
SELECT
zone_name,
zone_type,
population_density,
facility_count,
ROUND(avg_distance_to_facilities::NUMERIC, 2) as avg_distance,
CASE
WHEN facility_count = 0 THEN '无服务'
WHEN facility_count < 2 AND population_density > 5000 THEN '服务不足'
ELSE '服务充足'
END as service_level
FROM zone_coverage
ORDER BY population_density DESC;物流优化
sql
-- 物流配送路线优化
CREATE TABLE distribution_centers (
id SERIAL PRIMARY KEY,
center_name VARCHAR(100),
location POINT,
capacity INTEGER,
operating_cost_per_km DECIMAL(8,2)
);
CREATE TABLE delivery_destinations (
id SERIAL PRIMARY KEY,
customer_name VARCHAR(100),
delivery_address TEXT,
location POINT,
package_weight DECIMAL(8,2),
delivery_window_start TIME,
delivery_window_end TIME,
priority_level INTEGER
);
-- 插入配送中心数据
INSERT INTO distribution_centers (center_name, location, capacity, operating_cost_per_km) VALUES
('北方配送中心', point '(116.3000, 39.9500)', 1000, 2.5),
('南方配送中心', point '(116.4500, 39.8500)', 800, 2.8),
('东方配送中心', point '(116.5000, 39.9200)', 1200, 2.3);
-- 插入配送点数据
INSERT INTO delivery_destinations (customer_name, delivery_address, location, package_weight,
delivery_window_start, delivery_window_end, priority_level) VALUES
('客户A', '朝阳区xxx', point '(116.4200, 39.9300)', 5.5, '09:00', '12:00', 1),
('客户B', '海淀区yyy', point '(116.3100, 39.9800)', 3.2, '14:00', '17:00', 2),
('客户C', '东城区zzz', point '(116.4800, 39.9100)', 8.1, '10:00', '15:00', 1),
('客户D', '西城区aaa', point '(116.3500, 39.9400)', 2.8, '13:00', '16:00', 3);
-- 为每个配送点选择最优配送中心
SELECT
dd.customer_name,
dd.delivery_address,
dc.center_name as optimal_center,
ROUND((dd.location <-> dc.location)::NUMERIC, 4) as distance_km,
ROUND((dc.operating_cost_per_km * (dd.location <-> dc.location))::NUMERIC, 2) as delivery_cost,
dd.package_weight,
dd.priority_level
FROM delivery_destinations dd
CROSS JOIN LATERAL (
SELECT center_name, location, operating_cost_per_km
FROM distribution_centers dc
ORDER BY dd.location <-> dc.location
LIMIT 1
) dc
ORDER BY dd.priority_level, delivery_cost;
-- 计算配送中心的工作负载
SELECT
dc.center_name,
dc.location,
COUNT(assignments.customer_name) as assigned_deliveries,
ROUND(SUM(assignments.package_weight)::NUMERIC, 2) as total_weight,
ROUND(SUM(assignments.distance_km)::NUMERIC, 2) as total_distance,
ROUND(SUM(assignments.delivery_cost)::NUMERIC, 2) as total_cost,
ROUND((SUM(assignments.package_weight) / dc.capacity * 100)::NUMERIC, 1) as capacity_utilization
FROM distribution_centers dc
LEFT JOIN (
SELECT
dd.customer_name,
dd.package_weight,
dc_nearest.center_name,
dd.location <-> dc_nearest.location as distance_km,
dc_nearest.operating_cost_per_km * (dd.location <-> dc_nearest.location) as delivery_cost
FROM delivery_destinations dd
CROSS JOIN LATERAL (
SELECT center_name, location, operating_cost_per_km
FROM distribution_centers
ORDER BY dd.location <-> location
LIMIT 1
) dc_nearest
) assignments ON assignments.center_name = dc.center_name
GROUP BY dc.id, dc.center_name, dc.location, dc.capacity
ORDER BY capacity_utilization DESC;
-- 识别配送热点区域（聚类分析）
WITH delivery_clusters AS (
SELECT
dd1.customer_name,
dd1.location,
COUNT(dd2.customer_name) as nearby_deliveries
FROM delivery_destinations dd1
LEFT JOIN delivery_destinations dd2
ON dd1.id != dd2.id
AND dd1.location <-> dd2.location < 0.05 -- 5公里内
GROUP BY dd1.id, dd1.customer_name, dd1.location
)
SELECT
customer_name,
location,
nearby_deliveries,
CASE
WHEN nearby_deliveries >= 2 THEN '高密度区域'
WHEN nearby_deliveries = 1 THEN '中密度区域'
ELSE '低密度区域'
END as delivery_density
FROM delivery_clusters
ORDER BY nearby_deliveries DESC;性能优化建议
空间索引
INFO
PostgreSQL 使用 GiST (Generalized Search Tree) 索引来优化几何类型的查询性能。对于包含大量几何数据的表，创建适当的空间索引至关重要。
sql
-- 为几何列创建 GiST 索引
CREATE INDEX idx_buildings_location ON buildings USING GIST (location);
CREATE INDEX idx_zones_boundary ON city_zones USING GIST (boundary);
CREATE INDEX idx_facilities_service_area ON public_facilities USING GIST (service_radius);
-- 查看索引使用情况
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM buildings
WHERE location <-> point '(116.4000, 39.9000)' < 0.01;sql
-- 创建测试表和索引
CREATE TABLE spatial_test (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
location POINT,
area_boundary POLYGON
);
-- 插入测试数据
INSERT INTO spatial_test (name, location, area_boundary)
SELECT
'Object_' || i,
point(random() * 360 - 180, random() * 180 - 90),
polygon(box(
point(random() * 360 - 180, random() * 180 - 90),
point(random() * 360 - 180, random() * 180 - 90)
))
FROM generate_series(1, 10000) i;
-- 创建空间索引
CREATE INDEX idx_spatial_test_location ON spatial_test USING GIST (location);
CREATE INDEX idx_spatial_test_boundary ON spatial_test USING GIST (area_boundary);
-- 比较有无索引的查询性能
-- 无索引查询
DROP INDEX IF EXISTS idx_spatial_test_location;
EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*) FROM spatial_test
WHERE location <-> point '(0,0)' < 10;
-- 有索引查询
CREATE INDEX idx_spatial_test_location ON spatial_test USING GIST (location);
EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*) FROM spatial_test
WHERE location <-> point '(0,0)' < 10;查询优化技巧
> **空间查询优化要点**
- 使用边界框预筛选：先用简单的边界框查询筛选，再进行精确几何计算
- 合理选择运算符：距离查询使用
<->，包含查询使用@>和<@ - 避免复杂几何计算：将复杂多边形简化为边界框进行初步筛选
- 使用合适的数据类型：根据精度需求选择合适的几何类型
sql
-- 优化前：直接进行复杂几何计算
SELECT COUNT(*) FROM buildings b1, buildings b2
WHERE b1.id != b2.id
AND b1.complex_shape && b2.complex_shape;
-- 优化后：先用边界框筛选，再精确计算
SELECT COUNT(*) FROM buildings b1, buildings b2
WHERE b1.id != b2.id
AND box(b1.complex_shape) && box(b2.complex_shape) -- 先筛选
AND b1.complex_shape && b2.complex_shape; -- 再精确计算
-- 优化距离查询：使用索引友好的运算符
-- 好的写法
SELECT * FROM locations
WHERE location <-> point '(116.4000, 39.9000)' < 0.01
ORDER BY location <-> point '(116.4000, 39.9000)';
-- 避免的写法（无法使用索引）
SELECT * FROM locations
WHERE sqrt(power(location[0] - 116.4000, 2) + power(location[1] - 39.9000, 2)) < 0.01;常见问题和陷阱
> **几何计算中的常见陷阱**
精度问题
sql
-- 浮点数精度问题
SELECT point '(1.0, 2.0)' = point '(1.000000001, 2.000000001)';
-- 可能返回 false
-- 使用容差比较
SELECT abs(point '(1.0, 2.0)' <-> point '(1.000000001, 2.000000001)') < 1e-6;
-- 返回 true
-- 对于面积计算，注意精度
SELECT
area(polygon '((0,0),(1,0),(1,1),(0,1))') AS exact_area,
ROUND(area(polygon '((0.0001,0.0001),(0.9999,0.0001),(0.9999,0.9999),(0.0001,0.9999))')::NUMERIC, 6) AS approximate_area;自相交多边形
自相交的多边形可能导致面积计算结果无意义，使用前应验证多边形的有效性。
sql
-- 检查多边形是否自相交（简单检查）
CREATE OR REPLACE FUNCTION is_simple_polygon(poly POLYGON)
RETURNS BOOLEAN AS $$
DECLARE
point_count INTEGER;
path_obj PATH;
BEGIN
-- 转换为路径进行分析
path_obj := path(poly);
point_count := npoints(path_obj);
-- 简单验证：点数是否合理
IF point_count < 3 THEN
RETURN FALSE;
END IF;
-- 更复杂的自相交检查需要自定义逻辑
RETURN TRUE;
END;
$$ LANGUAGE plpgsql;
-- 使用示例
SELECT
'Valid polygon' as test_case,
is_simple_polygon(polygon '((0,0),(2,0),(2,2),(0,2))') as is_valid
UNION ALL
SELECT
'Degenerate polygon' as test_case,
is_simple_polygon(polygon '((0,0),(0,0))') as is_valid;总结
PostgreSQL 的几何函数和运算符为处理空间数据提供了强大的工具集。通过合理使用这些功能，可以：
TIP
最佳实践总结
- 合理选择几何类型：根据实际需求选择最合适的几何类型
- 创建空间索引：为频繁查询的几何列创建 GiST 索引
- 注意精度问题：在进行几何计算时考虑浮点数精度
- 验证数据质量：确保几何数据的有效性，避免自相交等问题
- 优化查询策略：使用边界框预筛选等技术提升查询性能
通过掌握这些几何函数和运算符，您可以在 PostgreSQL 中高效地处理各种空间数据任务，从简单的距离计算到复杂的空间分析都能游刃有余。