38. 高级几何构造¶

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。
使用递归公用表表达式。

公用表表达式 (CTE) 的优点是不需要函数定义即可运行。以下是计算“Q”列车路线的 CTE，从最北端的停靠站（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 由两个部分组成，它们联合在一起

第一部分为表达式建立一个起点。我们获取初始几何图形并使用“gid”为 304 的记录（线路的末端）初始化已访问标识符数组。
第二部分迭代，直到它找不到更多记录。在每次迭代中，它通过对“next_stop”的自引用获取前一次迭代的值。我们搜索 Q 线上的每个停靠站 (**strpos(s.routes,’Q’)**) ，这些停靠站尚未添加到我们的已访问列表中 (**NOT n.idlist @> ARRAY[s.gid]**)，并按它们与前一点的距离对其进行排序，只取第一个（最近的）。

除了递归 CTE 本身之外，这里还使用了一些高级的 PostgreSQL 数组功能。

我们使用的是 ARRAY！PostgreSQL 支持任何类型的数组。在本例中，我们有一个整数数组，但我们也可以构建一个几何数组，或任何其他 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”列从东到西运行。条件是否仍然成立？

对终点站的另一个不太方向性的描述是“它们是距离路线中间最远的站”。有了这种描述，路线是南北走向还是东西走向并不重要，重要的是它基本上在一个方向上运行，特别是在两端。

由于没有 100% 的启发式方法来找出端点，让我们尝试一下第二个规则。

注意

“距离中间最远”规则的一个明显的失败模式是环形线，比如英国伦敦的环线。幸运的是，纽约没有这样的线路！

要找出每条路线的终点站，我们首先必须找出有哪些路线！我们找到不同的路线。

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 支持任何类型的数组，因此可以构建字符串数组，如本例所示，还可以构建几何和地理数组，我们将在本例的后面部分看到。
**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...

现在我们可以通过将每条路线的所有站点收集到一个多点中，并计算该多点的质心来找到中心点。

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 的高级功能结合起来进行的一些复杂数据操作。

PostGIS 简介

38. 高级几何构造¶

38.1. 另请参阅¶

PostGIS 简介

38. 高级几何构造¶

38.1. 另请参阅¶

导航