获取多边形的子/父关系

Question

我正在尝试使用ST_Contains(parent, child)从开放街道地图数据库中提取父子关系，但是我在SQL方面特别糟糕。

简而言之，表格planet_osm_polygon如下所示：

| osm_id | name      | admin_level | way            |
-------------------------------------------------
| 4667   | France    | 2           | {polygon data} |
| 4667   | Normandie | 4           | {polygon data} |
| 4667   | A place   | 6           | {polygon data} |
etc...

我想提取父/子关系，其中子级是一行，其中多边形位于其父级内，并且admin_level尽可能低，但不等于其父级的(可能看起来很明显，只是说)。

到目前为止我做了什么

我已经设法编写了一个查询，为我提供了每个区域的父级，但没有一个可能admin_level最低的区域 - 因此 admin_level=6 的区域可能会出现父级为 admin_level=2。我希望孩子与admin_level最低的父母一起出现，在这种情况下为 4。

这是我失败的查询：

SELECT
DISTINCT ON (childTable.osm_id)
childTable.admin_level AS child_level,
childTable.name AS child_name,
parentTable.name AS parent,
parentTable.admin_level AS parent_level
FROM planet_osm_polygon AS childTable
RIGHT JOIN planet_osm_polygon AS parentTable
ON ST_Contains(parentTable.way, childTable.way)
AND childTable.admin_level > parentTable.admin_level;

下面是失败的示例结果：

| Child level | Child name | Parent name | Parent level |
---------------------------------------------------------
| 6           | Some place | France      | 2            |
| 4           | Another    | France      | 2            |
| 6           | And again  | France      | 2            |
etc...

以下是我希望它的样子：

| Child level | Child name | Parent name | Parent level |
---------------------------------------------------------
| 6           | Some place | A region    | 4            |
| 4           | A region   | France      | 2            |
etc...

注意：我已经删除了与admin_level 1、3、5 相关的所有数据，所以我只有 2、4 和 6 admin_level多边形，但是这可能因其他 contry 而异，所以我认为不可能对admin_levels进行硬编码

Answer 1

如果我答对了您的问题，您可以使用row_number()窗口功能为孩子的每个父母分配一个数字，该数字对于管理员级别最低的父母1并对其进行过滤。

SELECT child_level,
child_name,
parent_name,
parent_level
FROM (SELECT childtable.admin_level child_level,
childtable.name child_name,
parenttable.name parent_name,
parenttable.admin_level parent_level
row_number() OVER (PARTITION BY childtable.name
ORDER BY parenttable.admin_level) rn
FROM planet_osm_polygon childtable
RIGHT JOIN planet_osm_polygon parenttable
ON st_contains(parenttable.way, childtable.way)
AND childtable.admin_level > parenttable.admin_level) x
WHERE rn = 1;

如果每个面都有唯一的 ID<id>，则应PARTITION BY childtable.name替换为PARTITION BY childtable.<id>或检查名称是否唯一。否则，您可能会得到错误的结果，否则不同的地方共享一个名称。

我也不确定您的正确加入是否不应该是其他连接。如果要显示所有父项，即使不存在他们的子项，也要保留正确的联接。如果你想显示所有的孩子，孤儿，把它做一个左联接。如果只想显示具有父项的子项和具有子项的父项，请使用内部联接。