如何在Nominatim数据库中按省、区层级查询获取公社列表

Nominatim所有行政边界数据均存储在place表中，层级关联核心规则：

• 每个行政单元通过place_id作为唯一标识
• 直接上级行政单元ID存储在parent_place_id字段
• 行政层级由admin_level字段标记，行政边界数据固定满足class = 'boundary'、type = 'administrative'，查询时加该条件可过滤POI、道路等非行政单元数据
• 名称存储在HSTORE类型的name字段，可通过name->'zh'提取中文名称、name->'name'提取默认当地语言名称

前置校验：确认当前库层级对应值 #

不同区域的OSM数据admin_level映射规则存在差异，执行以下语句可确认当前库中各行政层级的对应数值，后续查询可根据结果调整参数：

SELECT admin_level, count(*) 
FROM place 
WHERE class = 'boundary' AND type = 'administrative' 
GROUP BY admin_level 
ORDER BY admin_level;

以下示例默认映射规则：省级admin_level=4、地级市admin_level=6、市辖区/县级行政区admin_level=7、公社admin_level=8，可根据实际查询结果替换。

场景1：按省份查询下属区、公社完整层级 #

使用递归CTE从指定省份向下遍历所有子行政节点，自动拼接完整行政路径：

WITH RECURSIVE admin_hierarchy AS (
    -- 锚点：定位目标省份作为根节点
    SELECT 
        place_id,
        parent_place_id,
        admin_level,
        name->'zh' AS node_name,
        ARRAY[name->'zh'] AS hierarchy_path,
        1 AS depth
    FROM place
    WHERE 
        class = 'boundary' 
        AND type = 'administrative'
        AND admin_level = 4
        AND name->'zh' = '替换为目标省份名称' -- 例：'浙江省'
    
    UNION ALL
    
    -- 递归向下遍历到公社层级停止
    SELECT 
        p.place_id,
        p.parent_place_id,
        p.admin_level,
        p.name->'zh' AS node_name,
        h.hierarchy_path || (p.name->'zh'),
        h.depth + 1
    FROM place p
    INNER JOIN admin_hierarchy h ON p.parent_place_id = h.place_id
    WHERE 
        p.class = 'boundary' 
        AND p.type = 'administrative'
        AND p.admin_level <= 8
)
-- 整理输出层级关系
SELECT
    hierarchy_path[1] AS province_name,
    hierarchy_path[array_position(ARRAY[4,6,7,8], 6)] AS city_name,
    hierarchy_path[array_position(ARRAY[4,6,7,8], 7)] AS district_name,
    hierarchy_path[array_position(ARRAY[4,6,7,8], 8)] AS commune_name,
    hierarchy_path AS full_path
FROM admin_hierarchy
WHERE admin_level = 8
ORDER BY full_path;

场景2：按指定区查询下属所有公社 #

如果公社的直接上级即为区，可直接用表关联查询，性能更高：

SELECT
    d.name->'zh' AS district_name,
    c.name->'zh' AS city_name,
    commune.name->'zh' AS commune_name
FROM place d
-- 关联区所属地级市
LEFT JOIN place c 
    ON d.parent_place_id = c.place_id 
    AND c.class = 'boundary' 
    AND c.type = 'administrative'
-- 关联区下属公社
LEFT JOIN place commune 
    ON commune.parent_place_id = d.place_id 
    AND commune.class = 'boundary' 
    AND commune.type = 'administrative' 
    AND commune.admin_level = 8
WHERE
    d.class = 'boundary' 
    AND d.type = 'administrative'
    AND d.admin_level = 7
    AND d.name->'zh' = '替换为目标区名称'; -- 例：'杭州市余杭区'

如果区和公社之间存在镇、街道等中间层级，使用递归CTE遍历即可：

WITH RECURSIVE commune_under_district AS (
    SELECT 
        place_id,
        parent_place_id,
        name->'zh' AS node_name,
        admin_level
    FROM place
    WHERE 
        class = 'boundary' 
        AND type = 'administrative'
        AND admin_level = 7
        AND name->'zh' = '替换为目标区名称'
    
    UNION ALL
    
    SELECT 
        p.place_id,
        p.parent_place_id,
        p.name->'zh' AS node_name,
        p.admin_level
    FROM place p
    INNER JOIN commune_under_district h ON p.parent_place_id = h.place_id
    WHERE 
        p.class = 'boundary' 
        AND p.type = 'administrative' 
        AND p.admin_level <= 8
)
SELECT
    (SELECT node_name FROM commune_under_district WHERE admin_level = 7) AS district_name,
    node_name AS commune_name
FROM commune_under_district
WHERE admin_level = 8;

场景3：全量匹配所有公社所属区、城市名称 #

一次性拉取所有公社对应的上级行政信息，从公社节点向上递归遍历即可：

WITH RECURSIVE full_admin_map AS (
    SELECT
        place_id,
        parent_place_id,
        name->'zh' AS node_name,
        admin_level,
        ARRAY[name->'zh'] AS node_path,
        ARRAY[admin_level] AS level_path
    FROM place
    WHERE class = 'boundary' AND type = 'administrative' AND admin_level = 8
    
    UNION ALL
    
    SELECT
        p.place_id,
        p.parent_place_id,
        p.name->'zh' AS node_name,
        p.admin_level,
        m.node_path || (p.name->'zh'),
        m.level_path || p.admin_level
    FROM place p
    INNER JOIN full_admin_map m ON p.place_id = m.parent_place_id
    WHERE p.class = 'boundary' AND p.type = 'administrative' AND p.admin_level >= 2
)
SELECT
    node_path[array_position(level_path, 4)] AS province_name,
    node_path[array_position(level_path, 6)] AS city_name,
    node_path[array_position(level_path, 7)] AS district_name,
    node_path[array_position(level_path, 8)] AS commune_name
FROM full_admin_map
WHERE admin_level = 8;

性能提示：如果数据量较大，可提前为place表的parent_place_id、class、type、admin_level字段建立联合索引，可将查询耗时降低90%以上。

内容的提问来源于stack exchange，提问作者Dev Nam