介绍
在本实验中，你将学习如何使用 PostGIS 扩展为 PostgreSQL 数据库添加空间能力。PostGIS 将你的数据库转变为一个空间数据库，使其能够存储和查询定义在几何空间中的数据。
你将首先创建一个新数据库并启用 PostGIS 扩展。然后，你将创建一个带有 GEOMETRY 列的表来存储位置数据。最后，你将插入多个城市的数据，并执行空间查询来计算距离和查找特定区域内的数据。
创建空间数据库并启用 PostGIS
在此步骤中，你将创建一个新数据库并在其中启用 PostGIS 扩展。使用专用数据库来存储空间数据是一个好习惯，可以保持其组织性。
首先，打开终端并使用 psql 交互式终端连接到 PostgreSQL 服务器。你将以默认的 postgres 超级用户身份连接。
sudo -u postgres psql
你将看到 psql 提示符，它看起来像 postgres=#。
接下来，创建一个名为 spatial_db 的新数据库。在 psql 中，所有 SQL 命令都必须以分号 (;) 结尾。
CREATE DATABASE spatial_db;
你应该会看到以下确认消息：
CREATE DATABASE
现在，连接到你新创建的数据库。\c 命令用于连接到不同的数据库。
\c spatial_db
你的提示符将更改，表明你现在已连接到 spatial_db：
You are now connected to database "spatial_db" as user "postgres".
spatial_db=#
数据库准备就绪后，启用 PostGIS 扩展。此命令会将所有 PostGIS 空间类型、函数和表加载到你的数据库中。
CREATE EXTENSION postgis;
输出将确认已创建扩展：
CREATE EXTENSION
为了验证 PostGIS 是否安装正确，你可以检查其版本。
SELECT PostGIS_full_version();
这将显示 PostGIS 及其相关库的详细版本信息，确认设置成功。你现在可以继续下一步。不要退出 psql shell。
创建空间表并插入数据
现在你已经有了空间数据库，可以创建表来存储地理数据了。在此步骤中，你将创建一个 cities 表，并插入几个主要城市的坐标。
你应该仍然在 psql shell 中，连接到 spatial_db 数据库。
首先，创建一个名为 cities 的表。这个表将包含标准的 id 和 name 列，以及一个类型为 GEOMETRY 的 location 列。
理解 GEOMETRY(Point, 4326)
GEOMETRY: 这是 PostGIS 用于存储空间要素的基本数据类型。Point: 这指定了几何列将只存储点。其他类型包括LINESTRING和POLYGON。4326: 这是空间参考系统标识符 (SRID)。SRID 4326 对应于 WGS 84，这是 GPS 和 Google Maps 使用的标准坐标系统。
执行以下命令创建表：
CREATE TABLE cities (
id SERIAL PRIMARY KEY,
name VARCHAR(50),
location GEOMETRY(Point, 4326)
);
你将看到确认信息：
CREATE TABLE
接下来，向 cities 表插入数据。你将使用 ST_GeomFromText 函数将 Well-Known Text (WKT) 表示的点转换为几何对象。格式为 POINT(经度 纬度)。
INSERT INTO cities (name, location) VALUES
('New York', ST_GeomFromText('POINT(-74.0060 40.7128)', 4326)),
('London', ST_GeomFromText('POINT(-0.1278 51.5074)', 4326)),
('Tokyo', ST_GeomFromText('POINT(139.6917 35.6895)', 4326));
输出表明已插入三行：
INSERT 0 3
为了验证数据是否正确插入，你可以查询该表。使用 ST_AsText 函数将几何对象转换回人类可读的文本格式。
SELECT id, name, ST_AsText(location) FROM cities;
输出应显示你刚刚插入的数据：
id | name | st_astext
----+----------+-----------------------------
1 | New York | POINT(-74.006 40.7128)
2 | London | POINT(-0.1278 51.5074)
3 | Tokyo | POINT(139.6917 35.6895)
(3 rows)
你已成功创建了一个空间表并填充了数据。
执行空间查询和分析
PostGIS 的真正强大之处在于其执行空间查询的能力。在此步骤中，你将使用 PostGIS 函数来计算两点之间的距离，以及找出哪些点位于某个区域内。
你应该仍然在 psql shell 中，连接到 spatial_db 数据库。
首先，让我们计算纽约和伦敦之间的距离。为了在地球表面进行准确的距离计算，最好将 geometry 类型转换为 geography 类型。geography 类型考虑了地球的曲率，ST_Distance 将以米为单位返回结果。
SELECT ST_Distance(
(SELECT location::geography FROM cities WHERE name = 'New York'),
(SELECT location::geography FROM cities WHERE name = 'London')
);
该查询将以米为单位返回大圆距离。结果将是一个较大的数字，大约为 557 万米。
st_distance
-----------------
5570299.7888681
(1 row)
接下来，让我们执行一个分析，找出我们城市中有哪些在伦敦 200 公里范围内。为此，你将首先在伦敦的位置周围创建一个圆形缓冲区，然后检查哪些城市的地点与该缓冲区相交。
ST_Buffer: 创建一个几何图形，表示所有与源几何图形距离小于或等于指定距离的所有点。当与geography类型一起使用时，距离单位为米。ST_Intersects: 如果两个几何图形“空间相交”——即它们有任何共同的空间——则返回true。
WITH london_buffer AS (
SELECT ST_Buffer(location::geography, 200000) AS geom FROM cities WHERE name = 'London'
)
SELECT c.name
FROM cities c, london_buffer lb
WHERE ST_Intersects(c.location::geography, lb.geom);
此查询使用公共表表达式 (CTE) 在伦敦周围创建了一个 200,000 米（200 公里）的缓冲区。然后，它找出与此缓冲区相交的所有城市。不出所料，只有伦敦本身在这个区域内。
name
--------
London
(1 row)
你现在已经执行了基本空间分析。你可以通过输入 \q 来退出 psql shell。
\q
清理数据库
完成工作后清理环境是一个好习惯。在此步骤中，你将删除 spatial_db 数据库。删除数据库会移除数据库本身及其包含的所有对象，例如表和扩展。
你应该已经回到了常规终端提示符 (labex@vm:~$)。
要删除数据库，你必须连接到另一个数据库。连接到默认的 postgres 数据库。
sudo -u postgres psql
现在，执行 DROP DATABASE 命令。
DROP DATABASE spatial_db;
你将看到一条确认消息：
DROP DATABASE
你可以使用 \l 命令列出所有可用的数据库来验证数据库是否已被删除。
\l
你将看到 spatial_db 已不再列表中。
最后，退出 psql shell。
\q
总结
在此次实验中，你学习了如何使用 PostGIS 扩展为 PostgreSQL 添加空间数据支持。你成功创建了一个专用数据库，启用了 PostGIS 扩展，定义了一个包含 geometry 列的表，插入了位置数据，并执行了空间查询以计算距离和查找相交点。这些技能为你使用 PostgreSQL 构建支持位置感知（location-aware）的应用程序奠定了坚实的基础。
