PostgreSQL实战指南：多领域数据库应用与实践

第四部分：PostgreSQL与时空数据库

借助业界领先的 PostGIS 扩展，PostgreSQL 摇身一变，成为一个功能强大的地理空间信息系统（GIS）。本部分将带领读者深入探索 PostgreSQL 在时空数据领域的应用，从基础的地理数据处理到复杂的空间分析与路径规划，无所不包。

第16章：PostGIS基础与地理空间数据类型

PostGIS 是 PostgreSQL 的一个强大扩展，它为数据库增加了对地理空间对象的支持，使其能够存储、查询和分析地理位置数据。本章是 PostGIS 之旅的起点，我们将学习如何安装和启用 PostGIS，并深入了解其核心的地理空间数据类型和空间参考系统。

16.1 PostGIS 入门与安装

PostGIS 是一个扩展，使用前需要先安装 PostGIS 的二进制文件，然后在数据库中启用它。

1
2
3
-- 在数据库中启用 PostGIS 扩展
-- 这会自动创建一系列的数据类型、函数和系统表
CREATE EXTENSION postgis;

验证安装:

你可以通过查询 PostGIS 的版本信息来验证安装是否成功。

1
SELECT postgis_full_version();

16.2 核心地理空间数据类型

PostGIS 遵循 OGC (Open Geospatial Consortium) 的 Simple Feature Access 标准，定义了一系列几何数据类型。

GEOMETRY: 这是所有几何类型的基类。
POINT: 点，表示单个位置。例如，一个商店的位置。
LINESTRING: 线，由一系列有序的点连接而成。例如，一条街道或河流。
POLYGON: 多边形，由一个或多个闭合的线环（rings）定义。第一个环是外边界，后续的环是内边界（洞）。例如，一个公园的边界或一个国家的轮廓。
MULTIPOINT: 多个点的集合。
MULTILINESTRING: 多条线的集合。
MULTIPOLYGON: 多个多边形的集合。
GEOMETRYCOLLECTION: 可以包含任何类型几何对象的集合。

16.3 空间参考系统 (SRS)

地理空间数据是有坐标的，但坐标只有在特定的坐标系中才有意义。空间参考系统（Spatial Reference System, SRS）或称坐标参考系统（Coordinate Reference System, CRS）定义了如何将坐标与地球上的真实位置相对应。

SRID (Spatial Reference Identifier): PostGIS 使用 SRID 来唯一标识一个空间参考系统。这些 ID 定义在 spatial_ref_sys 表中。
WGS 84 (SRID 4326): 这是最常用的地理坐标系，也是 GPS 系统使用的坐标系。它的单位是度（经度和纬度）。
投影坐标系 (Projected Coordinate Systems): 这类坐标系将地球的球面“投影”到一个平面上，单位通常是米或英尺。例如，Web Mercator (SRID 3857) 是网络地图服务（如 Google Maps, OpenStreetMap）中广泛使用的投影坐标系。

重要: 在进行空间分析（如计算距离或面积）时，必须确保所有参与计算的几何对象都使用相同的、合适的 SRID。通常建议使用投影坐标系进行计算，因为地理坐标系（度）的计算会非常复杂且不直观。

16.4 创建和操作几何对象

PostGIS 提供了多种函数来从文本、二进制或其他格式创建几何对象。

ST_GeomFromText(text, srid): 从 WKT (Well-Known Text) 格式创建几何对象。
ST_MakePoint(x, y, [z], [m]): 从 x, y 坐标创建点。
ST_Transform(geom, srid): 将几何对象从一个坐标系转换到另一个。
ST_AsText(geom): 将几何对象转换为 WKT 文本表示。
ST_X(point), ST_Y(point): 提取点的 x, y 坐标。

16.5 场景实战：存储和管理城市兴趣点 (POI)

业务场景描述:

我们需要创建一个数据库来存储一个城市中的多个兴趣点（Points of Interest, POI），如餐厅、博物馆和公园。我们需要记录它们的名称、类型和地理位置。

数据建模与实现:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- 创建 POI 表
-- 使用 GEOMETRY 类型来存储地理位置
-- 我们明确指定存储的是 POINT 类型，并且 SRID 是 4326 (WGS 84)
CREATE TABLE points_of_interest (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    poi_type VARCHAR(50),
    geom GEOMETRY(POINT, 4326)
);

-- 为 geom 列创建 GiST 空间索引
-- 空间索引对于加速空间查询至关重要！
CREATE INDEX idx_gist_poi_geom ON points_of_interest USING GIST (geom);

-- 插入数据 (使用 ST_GeomFromText)
-- 格式: 'SRID=<srid>;<WKT>'
INSERT INTO points_of_interest (name, poi_type, geom) VALUES
('Central Park', 'Park', ST_GeomFromText('POINT(-73.9654 40.7829)', 4326)),
('Museum of Modern Art', 'Museum', ST_SetSRID(ST_MakePoint(-73.9776, 40.7614), 4326)),
('Times Square', 'Landmark', 'SRID=4326;POINT(-73.9855 40.7580)');

-- 查询数据，并以 WKT 格式显示几何信息
SELECT name, poi_type, ST_AsText(geom) AS location
FROM points_of_interest;

-- 查询 MoMA 的经纬度
SELECT
    name,
    ST_X(geom) AS longitude,
    ST_Y(geom) AS latitude
FROM points_of_interest
WHERE name = 'Museum of Modern Art';

-- 将一个点的坐标从 WGS 84 (4326) 转换为 Web Mercator (3857)
SELECT
    name,
    ST_AsText(ST_Transform(geom, 3857)) AS mercator_location
FROM points_of_interest
WHERE name = 'Central Park';

代码解释与思考:

GEOMETRY(POINT, 4326): 在定义列类型时，明确指定几何类型（POINT）和 SRID（4326）是一个非常好的实践。这会在数据库层面增加约束，防止插入错误类型或坐标系的数据，从而保证数据的一致性。
GiST 索引: GiST (Generalized Search Tree) 是 PostGIS 最常用的空间索引类型。如果没有空间索引，任何涉及地理位置的搜索（例如“查找我附近的点”）都将导致全表扫描，性能会随着数据量的增长而急剧下降。为几何列创建 GiST 索引是使用 PostGIS 的基本要求。
ST_SetSRID() vs ST_Transform(): 这是一个非常重要的区别。
- ST_SetSRID(geom, srid): 不改变坐标值，只是为几何对象打上一个 SRID 标签。当你确定你的坐标值是属于某个坐标系但几何对象本身没有 SRID 信息时使用。
- ST_Transform(geom, srid): 会改变坐标值，它将几何对象从其原始 SRID 转换到目标 SRID。这是进行坐标系变换的正确函数。
多种输入格式: PostGIS 接受多种格式的几何输入，如 WKT (ST_GeomFromText)、WKB (ST_GeomFromWKB)、GeoJSON (ST_GeomFromGeoJSON) 等，提供了很好的灵活性。

16.6 总结

本章我们成功地开启了 PostGIS 的大门。我们学习了如何安装和启用它，掌握了核心的几何数据类型，并理解了空间参考系统（SRID）的关键作用。通过兴趣点管理的实战案例，我们实践了如何创建空间数据表、如何插入和查询几何对象，以及最重要的——如何创建空间索引。

PostgreSQL实战指南：多领域数据库应用与实践 - 第16章：PostGIS基础与地理空间数据类型

文章目录