PostgreSQL 教程: 使用 PostGIS 进行基础的地理空间数据查询

摘要：地理空间数据在许多领域变得越来越重要，从城市规划到环境科学。在本教程中，我们将演示用于处理此类数据的基础 PostGIS 查询。

目录

地理空间数据和 PostGIS 简介

地理空间数据包含了有关地球上位置的信息，需要专门的工具才能有效使用。PostGIS 是一个功能强大的 PostgreSQL 扩展，可将 PostgreSQL 数据库转换为一个功能齐全的地理信息系统（GIS）。使用 PostGIS，您可以直接用 SQL 来存储地理对象、运行空间查询和执行高级分析。对于任何需要处理基于位置的数据的人员，这使其成为了他们的必备工具。

安装 PostGIS 允许您执行空间操作，这通常需要专用的 GIS 软件来完成。无论您是需要计算距离、测量面积还是分析空间关系，PostGIS 都能为您提供高效处理复杂空间数据所需的工具。

在本教程中，我们将向您介绍 PostGIS 的基础知识。我们将从简单的空间查询开始，然后转向邻近分析和网络分析等高级技术。

我们的地理空间样例数据

在本文中，我们将使用从加利福尼亚 San Francisco 提取的空间数据集。我们将会使用到几个表：

• sf_tram_stops：此表包含了全市有轨电车站的位置；它还存储了位置坐标，以及该停靠点是否可以在线路之间换乘。它包含以下列：

  • id – 每个停靠点的唯一标识符。
  • coordinates – 停靠点的位置坐标。
  • transfer_possible – 乘客是否可以在该站换乘电车。

• sf_planning_districts：此表定义了 San Francisco 规划区的边界；这些信息对于市区分析至关重要。它包含以下列：

  • id – 每个规划区的唯一标识符。
  • name – 规划区的名称。
  • boundaries – 规划区的边界（作为位置坐标）。

• sf_bicycle_routes：此表存储了有关城市自行车路线的信息；您将会需要此数据来分析 San Francisco 的自行车基础设施。它包含以下列：

  • id – 每条自行车路线的唯一标识符。
  • course – 路线的过程。
  • condition_rating – 路线的评级（基于其条件）。

• sf_restaurants：此表列出了有关 San Francisco 餐厅的信息，包括其名称、位置、评级和美食类型。它包含以下列：

  • id – 每个餐厅的唯一标识符。
  • name – 餐厅的名称。
  • food_type – 这家餐厅供应的美食类型。
  • rating – 餐厅的评级。
  • coordinates – 餐厅的位置坐标。

• sf_sights：此表记录了城市内著名地标和景点（POI）的信息。它包含以下列：

  • id – 每个景点的唯一标识符。
  • name – 景点的名称。
  • coordinates – 景点的位置。

• sf_atms：此表存储了 San Francisco 的 ATM 的详细信息，包括每个 ATM 的位置以及运营它的公司。它包含以下列：

  • id – 每个 ATM 的唯一标识符。
  • company – 运营此 ATM 的公司。
  • coordinates – ATM 的位置。

这些数据将是我们要探索的空间查询的基础。我们将使用这些查询来分析地标和便利设施之间的接近度，计算规划区内的面积等等。每个查询都将展示，PostGIS 如何帮助您从空间数据中提取有意义的见解。

使用 PostGIS 进行基础的空间查询

使用 PostGIS 将地理空间数据存储在 PostgreSQL 中后，您就可以开始查询它了！空间查询允许您，根据地理的位置、形状和空间关系，来选择、筛选和操作数据。那么，让我们来开始使用地理空间数据。

可视化地理空间数据

首先，让我们看看地理空间数据是什么样的。在这里，我们为乘客可以换乘的那些站点，查询其电车站 ID 和坐标：

SELECT
  id,
  coordinates
FROM sf_tram_stops
WHERE transfer_possible = true;

结果如下所示：

如您所见，坐标列中的地理空间数据并不是真正可读的。要使它真正可用，您需要专门的软件来在地图上绘制它。使用一种内置的地图，您可以查看这些电车站的位置：

将地理空间数据转换为文本

也许您会想，在没有地图的情况下，来查看上述电车站的位置坐标。您可以使用以下查询将数据转换为人类可读的坐标：

SELECT
  id,
  ST_AsText(coordinates),
  ST_Y(coordinates),
  ST_X(coordinates)
FROM sf_tram_stops
WHERE transfer_possible = true;

此查询使用 PostGIS 函数ST_AsText以一种可读格式获取坐标。它使用ST_Y和ST_X来提取纬度和经度值。下面是部分结果：

空间关系

PostGIS 提供了多种功能来探索地理空间对象的空间关系。让我们快速回顾一下它们。

ST_Intersection

此函数返回两个几何形状的共享部分（即交集）。以下查询显示 Downtown 市区边界内的所有自行车路线（或其部分）：

SELECT
  sfbr.id,
  ST_Intersection(sfpd.boundaries, sfbr.course)
FROM sf_bicycle_routes sfbr
JOIN sf_planning_districts sfpd
  ON ST_Intersects(sfpd.boundaries, sfbr.course)
WHERE sfpd.name = 'Downtown';

ST_Intersection函数查找两个形状重叠的区域：在本例中，自行车路线穿过 “Downtown” 市区的边界。ST_Intersects函数检查自行车路线和市区边界是否相互接触或交叉，确保仅包含那些相互接触或交叉的路线。

下面是文本形式的部分结果：

地图形式的结果如下：

ST_Contains

ST_Contains函数检查一个几何形状是否完全包含另一个几何形状。要列出包含来自 Crown Financial 公司的 ATM 的规划区，您可以运行这个查询：

SELECT DISTINCT sfn.name
FROM sf_planning_districts sfn
JOIN sf_atms sfa
  ON ST_Contains(sfn.boundaries, sfa.coordinates)
WHERE sfa.company = 'Crown Financial Inc.';

结果如下：

ST_Within

ST_Within 函数还会检查一个几何形状是否完全位于另一个几何形状中。下面查询查找 Northeast 市区评分高于 4.0 的餐厅：

SELECT
  sep.name,
  sep.coordinates
FROM sf_planning_districts spd
JOIN sf_restaurants sep
  ON ST_Within(sep.coordinates, spd.boundaries)
WHERE rating > 4.0
  AND spd.name = 'Northeast';

此查询返回餐馆的名称和坐标，使其易于在地图上可视化。

距离和面积计算

计算距离和面积是处理地理空间数据的另一个基本方面。这些计算可以帮助回答，诸如 “此 ATM 距离特定景点有多远” 或 “此规划区的面积是多少” 等问题。

距离计算

ST_Distance函数返回两个地理空间对象之间的距离。它需要两个几何形状的参数：ST_Distance(geometryA, geometryB)。

要可视化位于 Fisherman’s Wharf 景点 300 米范围内的所有 ATM，您可以使用以下查询：

SELECT  
  sa.id,
  sa.coordinates,
  ST_Distance(
    ST_Transform(sa.coordinates, 26910),
    ST_Transform(ss.coordinates, 26910)) AS distance
FROM sf_sights ss
JOIN sf_atms sa
  ON ST_Distance(
     ST_Transform(sa.coordinates, 26910),
     ST_Transform(ss.coordinates, 26910)) < 300
WHERE ss.name = 'Fisherman''s Wharf';

在此查询中，ST_Transform函数将给定的坐标转换为适合空间计算的特定坐标系。坐标系可确保所有空间操作（如距离计算）都准确，且与地理区域和工程的特定需求相关。

在此查询中，我们使用由 SRID（空间参考系统标识符）26910 定义的坐标系，它对应于 NAD83 / UTM zone 10N 系统。该系统通常用于 San Francisco 地区的精确距离计算。

如何找到正确的 SRID？尝试以下网站之一：

• 空间参考网站：使用 EPSG 选项卡。
• io：键入国家名称、地区代码或坐标系名称，以搜索和获取所有可能的坐标参考系统。
• EPSG-registry.org

通过转换坐标，查询可确保 ATM 和 Fisherman’s Wharf 景点之间的距离计算是精确的。结果如下：

面积计算

要计算规划区的面积，请使用ST_Area函数。这采用几何形状的参数（ST_Area(geometry)），并返回结果形状的面积。

这是查询。请注意，我们再次使用 SRID = 26910 作为我们的坐标系：

SELECT
  ST_Area(ST_Transform(boundaries, 26910))
FROM sf_planning_districts
WHERE name = 'Buena Vista';

结果如下：

当 SRID = 26910 时，默认单位是米。因此，ST_Area的结果以平方米为单位。

高级空间分析

随着您对 PostGIS 的熟练程度越来越高，可以使用其高级功能来执行深入的空间分析。这些技术允许您探索空间关系、执行邻域分析，甚至进行网络分析，从而让您对地理空间数据的洞察更具可操作性。

缓冲和邻域分析

缓冲会在空间特征周围创建区域。这在邻域分析中特别有用，因为在邻域分析中，您需要确定某些特征彼此之间的接近程度。例如，如果您想识别 “Palace of Fine Arts” 景点半径 3000 米内的所有餐厅，您可以使用ST_DWithin函数：

SELECT
  sep.name,
  sep.rating,
  sep.food_type,
  sep.coordinates,
  ST_Distance(
    ST_Transform(sep.coordinates, 26910),
    ST_Transform(ss.coordinates, 26910)) AS distance
FROM sf_sights ss
JOIN sf_restaurants sep
  ON ST_DWithin(
     ST_Transform(sep.coordinates, 26910),
     ST_Transform(ss.coordinates, 26910),
     3000)
WHERE ss.name = 'Palace of Fine Arts';

以下是表格和地图形式的结果：

叠加操作

在地图绘制中，叠加操作允许我们查看不同的地理信息集匹配或交互的程度。假设您有两张地图，一张显示城市中的所有公园，另一张显示易受洪水影响的区域。通过将一张地图放在另一张地图上，您可以找出哪些公园有洪水风险。这个过程有助于我们理解和可视化不同地理特征的重合之处；它让规划、分析和决策更加高效。

以下查询使用ST_Union函数，将所有 “recreational” 规划区的边界合并成一个统一的几何形状：

SELECT
  ST_Union(boundaries)
FROM sf_planning_districts
WHERE type = 'recreational';

同样，您可以使用ST_Intersection来完成相同的任务。

网络分析

网络分析涉及检查路线、识别最短路线以及提高交通网络内的出行效率。这对于了解和优化交通系统和连接至关重要。PostGIS 在搭配pgRouting使用时，为此类分析提供了强大的工具。

pgRouting 扩展提供了用于路线优化的高级算法。最著名的功能之一是pgr_dijkstra函数；它通过考虑道路类型、交通状况和其他变量等因素，来计算两点之间的最短或最快路线。此函数在导航系统、物流规划、运输管理等方面的价值不可估量。通过利用此类扩展，用户可以增强路线规划、减少出行时间，并提高整体交通网络的效率。