40. 高级几何构造

``nyc_subway_stations``图层目前已经提供了许多有趣的案例，但其中存在一个显著特点：

虽然该数据库包含所有站点信息，但无法直观展示地铁线路！本章将利用PostgreSQL和PostGIS的高级功能，基于地铁站点的点图层构建全新的线性线路图层。

我们的任务尤其受到两个问题的困扰：

• nyc_subway_stations 表中的``routes`` 列每行包含多个线路标识符，因此一个可能出现在多条线路中的站点在表中仅出现一次。

• 与前一个问题相关，站点表中缺少线路顺序信息。虽然可以查询到某条线路上的所有站点，但无法通过现有属性确定列车经过各站点的运行顺序。

第二个问题更为棘手:给定一条线路上无序的站点集合，如何对它们进行排序以匹配实际线路走向。

以下是 'Q' 号线列车的站点列表：

SELECT s.gid, s.geom
FROM nyc_subway_stations s
WHERE (strpos(s.routes, 'Q') <> 0);

如图所示，各站点均标注有其唯一主键``gid`` 值。

若从任一终点站出发，线路上的下一站似乎始终是距离最近的站点。只要在搜索中排除已确定的站点，即可重复此过程完成全线排序。

在数据库中执行此类迭代计算有两种实现方式：

• 使用过程化语言，例如`PL/PgSQL <http://www.postgresql.org/docs/current/static/plpgsql.html>`_。

• 使用递归式`公用表表达式 <http://www.postgresql.org/docs/current/static/queries-with.html>`_。

Common table expressions (CTE)的优势在于无需预定义函数即可执行。以下是通过CTE计算'Q'号线路径的示例，从最北端站点（``gid``为304的位置）开始构建线路。

WITH RECURSIVE next_stop(geom, idlist) AS (
    (SELECT
      geom,
      ARRAY[gid] AS idlist
    FROM nyc_subway_stations
    WHERE gid = 304)
    UNION ALL
    (SELECT
      s.geom,
      array_append(n.idlist, s.gid) AS idlist
    FROM nyc_subway_stations s, next_stop n
    WHERE strpos(s.routes, 'Q') != 0
    AND NOT n.idlist @> ARRAY[s.gid]
    ORDER BY ST_Distance(n.geom, s.geom) ASC
    LIMIT 1)
)
SELECT geom, idlist FROM next_stop;

该表达式(CTE)由通过UNION连接的两部分组成：

• 前半部分为递归表达式设定起始点：通过获取 "gid" 为 304（线路终点站）的初始几何图形，并初始化已访问标识符数组来完成。

• 后半部分通过递归迭代运行，直至无法找到新的记录为止。每次迭代时，通过自引用"next_stop"获取前一次迭代的值。我们会搜索Q线路上所有站点（strpos(s.routes,'Q')），且未加入已访问列表的站点（NOT n.idlist @> ARRAY[s.gid]），然后按照与前一站点的距离排序，仅选择最近的一个站点（LIMIT 1）。

除递归CTE本身外，此实现还运用了多项PostgreSQL高级数组特性：

• 我们正在使用数组功能！PostgreSQL支持任意类型的数组——当前示例使用整数数组存储站点ID，但同样支持构建几何图形数组或任何PostgreSQL数据类型的数组。

• 我们正在使用 array_append 函数动态扩展已访问标识符数组。

• 我们使用 @> 数组运算符（"数组包含"）来筛选已访问过的 Q 线路站点。 @> 要求两侧均为 ARRAY 类型，因此需通过 ARRAY[] 语法将单独的 "gid" 数值转换为单元素数组。

执行查询时，系统会按检索顺序（即线路顺序）返回每个几何图形及已访问的标识符列表。通过PostGIS的 ST_MakeLine 聚合函数，可将这些几何图形按给定顺序构建为单一的线性输出。

WITH RECURSIVE next_stop(geom, idlist) AS (
    (SELECT
      geom,
      ARRAY[gid] AS idlist
    FROM nyc_subway_stations
    WHERE gid = 304)
    UNION ALL
    (SELECT
      s.geom,
      array_append(n.idlist, s.gid) AS idlist
    FROM nyc_subway_stations s, next_stop n
    WHERE strpos(s.routes, 'Q') != 0
    AND NOT n.idlist @> ARRAY[s.gid]
    ORDER BY ST_Distance(n.geom, s.geom) ASC
    LIMIT 1)
)
SELECT ST_MakeLine(geom) AS geom FROM next_stop;

其语法结构如下：

成功！

然而存在两个技术问题：

• 当前仅计算单条地铁线路，而实际需求是计算所有线路的路径。

• 我们的查询语句包含一项*先验知识*——作为路径搜索算法初始种子节点的起始站点标识符。

让我们优先攻克核心难题：如何在不人工识别的情况下，自动确定某条线路的起始站点。

'Q'线地铁站点可作为分析起点，那么该线路的终点站具有哪些特征？

一种观点认为"终点站就是线路最北端和最南端的站点"。但假设'Q'线改为东西走向运行，这一判定条件是否仍然成立？

对终点站的一种方向无关的界定方式是："它们为距离线路中点最远的站点"。该定义不受线路走向（南北/东西）影响，仅需满足。

既然不存在百分之百可靠的启发式规则来确定终点站，我们暂时搁置第二条判定准则（"距离线路中点最远"规则），转而探索其他解决方案。

注解

"距线路中点最远"这一判定规则存在明显缺陷，典型反例即英国伦敦的环线（如Circle Line）。所幸纽约地铁目前不存在环形线路，该规则在此仍可适用！

确定各线路终点站的首要步骤是提取唯一线路标识。

WITH routes AS (
  SELECT DISTINCT unnest(string_to_array(routes,',')) AS route
  FROM nyc_subway_stations ORDER BY route
)
SELECT * FROM routes;

请注意此处使用的两项PostgreSQL高级数组函数特性：

• string_to_array 函数通过分隔符将字符串拆分为数组。`PostgreSQL 支持任意数据类型的数组 http://www.postgresql.org/docs/current/static/arrays.html`_，因此既可构建字符串数组（如本例所示），亦可生成几何图形/地理要素数组（后续示例将具体展示）。

• unnest 函数将数组展开为多行记录，实现从"横向"数组（单行存储）到"纵向"数组（每行一个值）的转换。

该查询结果将返回地铁系统中所有唯一的线路标识符列表。

 route
-------
 1
 2
 3
 4
 5
 6
 7
 A
 B
 C
 D
 E
 F
 G
 J
 L
 M
 N
 Q
 R
 S
 V
 W
 Z
(24 rows)

我们可以基于该结果，通过与 nyc_subway_stations 表进行关联，创建一个新表，其中每条线路的每个站点都对应一行记录。

WITH routes AS (
  SELECT DISTINCT unnest(string_to_array(routes,',')) AS route
  FROM nyc_subway_stations ORDER BY route
),
stops AS (
  SELECT s.gid, s.geom, r.route
  FROM routes r
  JOIN nyc_subway_stations s
  ON (strpos(s.routes, r.route) <> 0)
)
SELECT * FROM stops;

 gid |                      geom                      | route
-----+----------------------------------------------------+-------
   2 | 010100002026690000CBE327F938CD21415EDBE1572D315141 | 1
   3 | 010100002026690000C676635D10CD2141A0ECDB6975305141 | 1
  20 | 010100002026690000AE59A3F82C132241D835BA14D1435141 | 1
  22 | 0101000020266900003495A303D615224116DA56527D445141 | 1
                            ...etc...

现在，我们可以通过将每条线路的所有站点收集为一个多点（multi-point），然后计算该多点的中心点（centroid）来找到中心点。

WITH routes AS (
  SELECT DISTINCT unnest(string_to_array(routes,',')) AS route
  FROM nyc_subway_stations ORDER BY route
),
stops AS (
  SELECT s.gid, s.geom, r.route
  FROM routes r
  JOIN nyc_subway_stations s
  ON (strpos(s.routes, r.route) <> 0)
),
centers AS (
  SELECT ST_Centroid(ST_Collect(geom)) AS geom, route
  FROM stops
  GROUP BY route
)
SELECT * FROM centers;

'Q'线列车站点集合的中心点如下：

所以最北端的车站（终点站）似乎也是距离中心最远的车站。让我们计算每条线路距离中心最远的站点。

WITH routes AS (
  SELECT DISTINCT unnest(string_to_array(routes,',')) AS route
  FROM nyc_subway_stations ORDER BY route
),
stops AS (
  SELECT s.gid, s.geom, r.route
  FROM routes r
  JOIN nyc_subway_stations s
  ON (strpos(s.routes, r.route) <> 0)
),
centers AS (
  SELECT ST_Centroid(ST_Collect(geom)) AS geom, route
  FROM stops
  GROUP BY route
),
stops_distance AS (
  SELECT s.*, ST_Distance(s.geom, c.geom) AS distance
  FROM stops s JOIN centers c
  ON (s.route = c.route)
  ORDER BY route, distance DESC
),
first_stops AS (
  SELECT DISTINCT ON (route) stops_distance.*
  FROM stops_distance
)
SELECT * FROM first_stops;

这次我们添加了两个子查询：

• stops_distance 将中心点与车站表连接，并计算每条线路中车站与中心点之间的距离。结果按线路分批排序，每批的第一条记录是距离中心最远的车站。

• first_stops 通过仅获取每个不同分组的第一条记录来过滤**stops_distance** 的输出。由于 **stops_distance**已按特定方式排序，因第一条记录就是距离中心最远的记录，也就是我们希望用作起始点来构建每条地铁线路的站点。

现在我们已经确定了每条线路，并且大致知道了每条线路的起始站点：我们可以开始生成路线线条了！

首先，我们需要将递归 CTE 表达式转换为一个可以通过参数调用的函数。

CREATE OR REPLACE function walk_subway(integer, text) returns geometry AS
$$
WITH RECURSIVE next_stop(geom, idlist) AS (
    (SELECT
      geom AS geom,
      ARRAY[gid] AS idlist
    FROM nyc_subway_stations
    WHERE gid = $1)
    UNION ALL
    (SELECT
      s.geom AS geom,
      array_append(n.idlist, s.gid) AS idlist
    FROM nyc_subway_stations s, next_stop n
    WHERE strpos(s.routes, $2) != 0
    AND NOT n.idlist @> ARRAY[s.gid]
    ORDER BY ST_Distance(n.geom, s.geom) ASC
    LIMIT 1)
)
SELECT ST_MakeLine(geom) AS geom
FROM next_stop;
$$
language 'sql';

现在，我们已经准备就绪，可以开始了！

CREATE TABLE nyc_subway_lines AS
-- Distinct route identifiers!
WITH routes AS (
  SELECT DISTINCT unnest(string_to_array(routes,',')) AS route
  FROM nyc_subway_stations ORDER BY route
),
-- Joined back to stops! Every route has all its stops!
stops AS (
  SELECT s.gid, s.geom, r.route
  FROM routes r
  JOIN nyc_subway_stations s
  ON (strpos(s.routes, r.route) <> 0)
),
-- Collects stops by routes and calculate centroid!
centers AS (
  SELECT ST_Centroid(ST_Collect(geom)) AS geom, route
  FROM stops
  GROUP BY route
),
-- Calculate stop/center distance for each stop in each route.
stops_distance AS (
  SELECT s.*, ST_Distance(s.geom, c.geom) AS distance
  FROM stops s JOIN centers c
  ON (s.route = c.route)
  ORDER BY route, distance DESC
),
-- Filter out just the furthest stop/center pairs.
first_stops AS (
  SELECT DISTINCT ON (route) stops_distance.*
  FROM stops_distance
)
-- Pass the route/stop information into the linear route generation function!
SELECT
  ascii(route) AS gid, -- QGIS likes numeric primary keys
  route,
  walk_subway(gid, route) AS geom
FROM first_stops;

-- Do some housekeeping too
ALTER TABLE nyc_subway_lines ADD PRIMARY KEY (gid);

这是我们的最终表在 QGIS 中的可视化效果：

和往常一样，我们对数据的简单理解存在一些问题：

• 系统中实际存在两条简称均为'S'的短途接驳线路：一条位于曼哈顿区，另一条位于洛克威区。尽管二者地理上不相连，但因共享线路标识符'S'，我们在查询中将其合并处理;

• '4' 号线（以及其他几条线路）在某一端分成了两个终点站，因此“沿着一条线路前进”的假设被打破，导致结果在末端出现了一个奇怪的弯钩。

希望这个示例能让大家体验到，结合 PostgreSQL 和 PostGIS 的高级特性，可以实现多种复杂的数据处理操作。

40.1. 另请参阅

• PostgreSQL Arrays

• PostgreSQL Array Functions

• PostgreSQL Recursive Common TABLE Expressions

• PostGIS ST_MakeLine