PostgreSQL PostGIS 扩展

介绍

在这个实验中，你将学习如何在 PostgreSQL 数据库中安装和使用 PostGIS 扩展（extension）。PostGIS 使 PostgreSQL 能够存储和查询空间数据，允许你执行空间查询和分析。

Skills Graph

安装 PostGIS 扩展

在这个步骤中，你将在 PostgreSQL 数据库中安装 PostGIS 扩展（extension）。PostGIS 为 PostgreSQL 增加了对地理对象的支持。

首先，使用 psql 命令以 postgres 用户身份连接到 PostgreSQL 服务器：

sudo -u postgres psql

这将打开 PostgreSQL 交互式终端。你将看到类似以下的提示符：

psql (14.7 (Debian 14.7-1.pgdg110+1))
Type "help" for help.

postgres=#

接下来，创建一个名为 spatial_db 的数据库：

CREATE DATABASE spatial_db;

你应该看到以下输出：

CREATE DATABASE

现在，连接到 spatial_db 数据库：

\c spatial_db

提示符将更改为：

You are now connected to database "spatial_db" as user "postgres".
spatial_db=#

安装 PostGIS 扩展：

CREATE EXTENSION postgis;

这会将 PostGIS 功能添加到 spatial_db 数据库。输出应为：

CREATE EXTENSION

通过检查 PostGIS 版本来验证安装：

SELECT PostGIS_full_version();

输出将显示 PostGIS 扩展的完整版本信息。

最后，退出 psql shell：

\q

创建空间表并插入数据

在这个步骤中，你将创建一个表来存储空间数据，并插入一些示例数据。

首先，使用 psql 命令连接到 spatial_db 数据库：

sudo -u postgres psql -d spatial_db

创建一个名为 cities 的表，其中包含 id、name 和 location 列。location 列将以 geometry 类型存储空间数据。

CREATE TABLE cities (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    location GEOMETRY(Point, 4326)
);

此命令创建一个名为 cities 的表，其中包含一个自动递增的 id，一个城市 name，以及一个 location 列，用于使用 SRID 4326 (WGS 84) 将城市的地理坐标存储为 Point 几何（geometry）。

你应该看到以下输出：

CREATE TABLE

现在，插入三个城市的数据：New York、London 和 Tokyo。

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));

这会将三行数据插入到 cities 表中，使用 ST_GeomFromText 函数从表示每个城市坐标的 Well-Known Text (WKT) 字符串创建 Point 几何（geometries）。

你应该看到以下输出：

INSERT 0 3

要验证数据是否已正确插入，请查询该表：

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)

最后，退出 psql shell：

\q

执行空间查询

在这个步骤中，你将使用 PostGIS 扩展执行一些基本的空间查询。

首先，使用 psql 命令连接到 spatial_db 数据库：

sudo -u postgres psql -d spatial_db

计算 New York 和 London 之间的距离，以米为单位：

SELECT ST_Distance(
    (SELECT location::geography FROM cities WHERE name = 'New York'),
    (SELECT location::geography FROM cities WHERE name = 'London')
);

这个查询计算 New York 和 London 位置之间的距离。::geography 转换用于在地球表面上执行计算，并返回以米为单位的距离。你应该看到类似以下的输出：

   st_distance
-----------------
 5570299.7888681
(1 row)

在 London 周围创建一个 200 千米（200000 米）的缓冲区（buffer），并找到所有与该缓冲区相交的城市：

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);

这个查询首先使用 ST_Buffer 在 London 的位置周围创建一个缓冲区。然后，它选择所有位置与缓冲区相交的城市的名称，使用 ST_Intersects 函数。输出应为：

  name
--------
 London
(1 row)

最后，退出 psql shell：

\q

移除 PostGIS 扩展和数据库

在这个步骤中，你将移除 PostGIS 扩展和 spatial_db 数据库。

首先，使用 psql 命令连接到 spatial_db 数据库：

sudo -u postgres psql -d spatial_db

移除 PostGIS 扩展：

DROP EXTENSION postgis;

此命令从 spatial_db 数据库中移除 PostGIS 功能。你应该看到以下输出：

DROP EXTENSION

现在，删除 cities 表：

DROP TABLE cities;

你应该看到以下输出：

DROP TABLE

退出 psql shell：

\q

最后，删除 spatial_db 数据库：

sudo -u postgres psql -c "DROP DATABASE spatial_db;"

总结

在这个实验中，你已经学习了如何在 PostgreSQL 数据库中安装和使用 PostGIS 扩展。你创建了一个数据库，安装了 PostGIS 扩展，创建了一个空间表（spatial table），插入了数据，执行了空间查询，最后移除了扩展和数据库。这为你在 PostgreSQL 中处理空间数据提供了一个基础。