使用 Amazon Athena 查询 OpenStreetMap

Original URL:https://aws.amazon.com/blogs/big-data/querying-openstreetmap-with-amazon-athena/

这是 2017 年 OpenStreetMap 美国理事会成员 Seth Fitzsimmons 的一篇特邀博文。Seth 为 Humanitarian OpenStreetMap Team、Mapzen、美国红十字会以及世界银行等客户设计创新的地理空间解决方案。

OpenStreetMap (OSM) 式一个免费的可编辑世界地图，由志愿者创建和维护，可依据开放许可证使用。Mapbox、Foursquare、Mapzen、世界银行、美国红十字会以及其他公司和非营利组织使用 OSM 为全世界的用户提供地图、方向指引和地理背景。

在 OSM 诞生以来的 12 年里，编辑者们创建和修改了数十亿个特征（道路或建筑物等地面实物）。支持 OSM 编辑界面的主要 PostgreSQL 数据库现已超过 2TB，包含了可追溯至 2007 年的历史数据。随着新用户加入开放地图社区，越来越多的珍贵数据不断添加到 OpenStreetMap，需要更加强大的工具、界面和方法来探索它的浩瀚数据。

本博文解释了如何使用 Amazon Athena 快速查询以 AWS 公开数据集方式存储在 Amazon S3 中的公开 OSM 数据（每周更新）。假设您为一家非政府组织工作，希望增加对非洲地区医疗中心的了解和接触。您可能希望知道哪些医疗中心已经在地图上存在，协助编制有关周边村落的地图，以及确定在哪里进行基础设施投资可能会最为有效。

注意：如果您运行本博文中的所有查询，根据扫描的字节数，您大约需要支付 1 USD 的费用。本博文中的所有查询可以在此 GitHub gist中找到。

什么是 OpenStreetMap？

作为一个开放内容项目，正常的 OSM 数据档案通过 planet.openstreetmap.org 以不同的格式（XML、PBF）向公众开放。这包括 OSM 中当前数据状态的快照和历史存档数据。

所谓的“星球”（planet，即存档数据）用起来可能会不太顺当。因为它包含覆盖全世界的数据，单个档案的大小以 50GB 为单位。其格式也是定制，特别针对 OSM 的。数据异常丰富、有意义和有用，但由于其大小、格式和工具的原因，经常会导致甚至难以开始提出复杂的问题。

大量的 OSM 数据用户通常会下载原始数据，然后导入到他们自己的系统中，这些系统会根据用户的具体使用案例定制，例如地图渲染、驾驶导向或一般性分析。自从在 Amazon S3 中以 Apache ORC 格式提供 OSM 数据后，无需下载数据即可在 Athena 中查询数据。

Athena 有什么作用？

您可以将 Athena 与通过 OSM on AWS 公开提供的数据结合使用。您无需了解如何安装、配置和运行自己的服务器实例，也无需完成将数据下载并转换为可查询格式的多个步骤。在 AWS 以及合作伙伴的努力下，S3 上托管了一个会定期更新的“星球”文件副本（在 OSM 每周推送计划后几小时内即可用），它采用的格式支持使用 Athena 进行高效率的查询。

使用 Athena 提问涉及将此 OSM 星球文件注册为一个表以及进行 SQL 查询。一切搞定。无需任何下载，无需任何配置，也无需提取任何数据。Athena 会发布您的查询并在几秒钟内返回答案，即使查询超过 9 年的数十亿 OSM 元素也能达到此性能水平。

一切都由您控制。S3 提供极高的数据可用性，Athena 按照您扫描的数据 TB 数向您收费。此外，我们将 OSM 的定制格式转码为 ORC，从而尽可能降低扫描费用。所有将数据转换为高度可查询的格式并使其公开可用的繁重工作都已解决，您只需提出问题即可。

表的注册

OSM 公开数据集由三个表组成：

planet
其中包含 OSM 中所存在所有元素的当前版本。
planet_history
其中包含所有元素所有版本的历史记录（甚至包含已经删除的版本）。
changesets
包含记录了元素更改（并且与 planet 和 planet_history 文件之间存在外键关系）的更改集的信息。

要在您的 AWS 账户中注册 OSM 公开数据集，以便您能够进行查询，请打开 Athena 控制台（请务必使用 us-east-1 区域），然后粘贴并执行如下表定义：

planet

CREATE EXTERNAL TABLE planet (
  id BIGINT,
  type STRING,
  tags MAP<STRING,STRING>,
  lat DECIMAL(9,7),
  lon DECIMAL(10,7),
  nds ARRAY<STRUCT<ref: BIGINT>>,
  members ARRAY<STRUCT<type: STRING, ref: BIGINT, role: STRING>>,
  changeset BIGINT,
  timestamp TIMESTAMP,
  uid BIGINT,
  user STRING,
  version BIGINT
)
STORED AS ORCFILE
LOCATION 's3://osm-pds/planet/';

planet_history

CREATE EXTERNAL TABLE planet_history (
    id BIGINT,
    type STRING,
    tags MAP<STRING,STRING>,
    lat DECIMAL(9,7),
    lon DECIMAL(10,7),
    nds ARRAY<STRUCT<ref: BIGINT>>,
    members ARRAY<STRUCT<type: STRING, ref: BIGINT, role: STRING>>,
    changeset BIGINT,
    timestamp TIMESTAMP,
    uid BIGINT,
    user STRING,
    version BIGINT,
    visible BOOLEAN
)
STORED AS ORCFILE
LOCATION 's3://osm-pds/planet-history/';

changesets

CREATE EXTERNAL TABLE changesets (
    id BIGINT,
    tags MAP<STRING,STRING>,
    created_at TIMESTAMP,
    open BOOLEAN,
    closed_at TIMESTAMP,
    comments_count BIGINT,
    min_lat DECIMAL(9,7),
    max_lat DECIMAL(9,7),
    min_lon DECIMAL(10,7),
    max_lon DECIMAL(10,7),
    num_changes BIGINT,
    uid BIGINT,
    user STRING
)
STORED AS ORCFILE
LOCATION 's3://osm-pds/changesets/';

功能揭秘：提取、转换、加载

那么在幕后发生了哪些事情才让您可以轻松使用？简而言之，数据从 OSM PBF 格式转码为 Apache ORC 格式。

有一个 AWS Lambda 函数（通过 CloudWatch Events 触发，每 15 分钟运行一次）会关注 planet.openstreetmap.org，以检查是否有每周更新（使用 rsync 功能）。如果该函数检测到有新版本可用，则它会提交一组 AWS Batch 作业，以将该版本镜像、转码并设置为“最新”版本。此函数的代码可在 osm-pds-pipelines GitHub 存储库获取。

为便于将数据转码为适合 Athena 的格式，我们开发了一个开源工具 OSM2ORC。此工具还包含一个 Osmosis 插件，您可以使用此插件来处理复杂筛选管道。此工具会输出一个 ORC 文件，您可以将此文件上传到 S3 中以便使用 Athena，也可在本地保存以便使用来自 Hadoop 生态系统的其他工具。

OpenStreetMap 可以回答哪些类型的问题？

OpenStreetMap 数据的用途很多；以下是三个主要用途以及如何使用 Athena 来满足这些需要。

案例 1：查找西部非洲地区的当地医疗中心

作为“遗忘的地图 (Missing Maps)”项目的一部分，美国红十字会为西部非洲受埃博拉瘟疫影响的地区超过 7,000 个社区绘制了地图，他们发现采集广泛类别的数据对他人不仅重要，也大有裨益。准确的地图对于了解人类社区至关重要，尤其是对于面临风险的人群。由于缺乏有关西部非洲地区的详细地图，在 2014 年埃博拉危机期间造成了极大的问题，因此在全世界采集和编制数据可能有利于改进未来的灾害应急工作。

作为数据采集工作的一部分，志愿者们收集了当地医疗中心的位置和相关信息，这些信息将有利于未来危机的处理（当然在日常工作中也更加重要）。结合有关市场准入和清洁饮用水的信息以及有关自然灾害的历史经验，利用此数据创建了一个脆弱性指数来选择要进行详细地图绘制的社区。

在此例中，您将看到西部非洲地区的所有医疗中心（许多都是通过 Missing Maps 项目绘制的）。这是 healthsites.io 为公众开展的工作（面向全球公众，可编辑，基于 OSM 数据），但您要处理的是原始数据。

下面的查询会获取有关几内亚、塞拉利昂和利比里亚境内所有医疗中心的信息，以节点（点）标记。

SELECT * from planet
WHERE type = 'node'
  AND tags['amenity'] IN ('hospital', 'clinic', 'doctors')
  AND lon BETWEEN -15.0863 AND -7.3651
  AND lat BETWEEN 4.3531 AND 12.6762;

作为由节点（点）组成的“道路”（在此例中为多边形），建筑物也可以标记为医疗设施。要找到这些点，您需要进行几何重组。这里您将利用组成建筑物的所有节点的平均值（这将大致为建筑物的中点，对此目的而言已足够近）。下面的查询将会找到所有标记为医疗设施的建筑物和点：

-- select out nodes and relevant columns
WITH nodes AS (
  SELECT
    type,
    id,
    tags,
    lat,
    lon
  FROM planet
  WHERE type = 'node'
),
-- select out ways and relevant columns
ways AS (
  SELECT
    type,
    id,
    tags,
    nds
  FROM planet
  WHERE type = 'way'
    AND tags['amenity'] IN ('hospital', 'clinic', 'doctors')
),
-- filter nodes to only contain those present within a bounding box
nodes_in_bbox AS (
  SELECT *
  FROM nodes
  WHERE lon BETWEEN -15.0863 AND -7.3651
    AND lat BETWEEN 4.3531 AND 12.6762
)
-- find ways intersecting the bounding box
SELECT
  ways.type,
  ways.id,
  ways.tags,
  AVG(nodes.lat) lat,
  AVG(nodes.lon) lon
FROM ways
CROSS JOIN UNNEST(nds) AS t (nd)
JOIN nodes_in_bbox nodes ON nodes.id = nd.ref
GROUP BY (ways.type, ways.id, ways.tags)
UNION ALL
SELECT
  type,
  id,
  tags,
  lat,
  lon
FROM nodes_in_bbox
WHERE tags['amenity'] IN ('hospital', 'clinic', 'doctors');

您还可以更进一步，查询这些包含的其他标签（例如 opening_hours），并将此作为一个指标来衡量数据集的“完整性”，专注于要采集的其他数据（以及要填补的位置）。

案例 2：生成有关地图马拉松的统计数据

OSM 拥有举办地图派对的传统。地图派对活动将兴趣爱好者汇聚一堂，然后在外游荡，收集和改进有关所经过地点（和景观）的信息。地图派对的另一个形式是地图马拉松，汇聚那些只有书本知识的地图爱好者，让他们关注改进现实世界的数据。

地图马拉松是 Missing Maps 招募志愿者的一个流行方式，此项目是许多非政府组织、教育机构和公民组织的协作项目，旨在为发展中国家最落后地区绘制地图，从而为国际和当地非政府组织和个人的工作提供支持。志愿者们参与的一个常见方式是通过空中摄影的方式来跟踪建筑物和道路，提供基线数据，以便 Missing Maps 工作人员以及在地图绘制区域工作的志愿者在后来验证核实。

（图片和数据由美国红十字会提供）

在这些活动期间采集的数据本身也提出了多个不同类型的问题。人都喜欢竞争，因此 Missing Maps 开发了一系列的排行榜，让人们了解自己相比他人的位置，以及不同团队的比较。为支持这一功能，OSM 的更改集备注中包含了哈希标签（例如 #missingmaps）。要进行类似的任意分析，您需要查询更改集列表，按照备注中是否包含哈希标签来筛选，以及按用户名对事物分组。

现在查找在乔治梅森大学举行的 Missing Maps 地图马拉松期间作出的更改（使用哈希标签 #gmu）：

SELECT *
FROM changesets
WHERE regexp_like(tags['comment'], '(?i)#gmu');

这包含了与一个更改集关联的所有标签，通常包含地图绘制者提供的有关所作更改的备注（经常会有对应 OSM Tasking Manager 项目的额外哈希标签）以及有关所用编辑器、所引用图像等的信息。

如果您对参加 Missing Maps 项目绘制地图的个人用户数量感兴趣，可以编写与以下类似的查询：

SELECT COUNT(DISTINCT uid)
FROM changesets
WHERE regexp_like(tags['comment'], '(?i)#missingmaps');

25610 人（在本文撰写时）！

在乔治梅森大学，您可能需要知道谁是最多产的地图绘制者：

SELECT user, count(*) AS edits
FROM changesets
WHERE regexp_like(tags['comment'], '(?i)#gmu')
GROUP BY user
ORDER BY count(*) DESC;

棒极了，难以想象！

此外了解添加或更改了哪些类型的特征也很有意思。您可以通过联结更改集和“星球”表来执行此操作：

SELECT planet.*, changesets.tags
FROM planet
JOIN changesets ON planet.changeset = changesets.id
WHERE regexp_like(changesets.tags['comment'], '(?i)#gmu');

以此为起点，您可以深入分析特征的类型，突出全世界受欢迎的地点，或者完全不同的其他事情。

案例 3：建筑物状况

世界各地的地图绘制者们绘制好建筑物的轮廓后，当地 Missing Maps 志愿者（往往来自当地红十字/红新月协会）带上运行 OpenDataKit 和 OpenMapKit 的 Android 手机走出去，核实相关建筑物的实际位置，并添加相关的额外信息，例如楼层数、用途（住宅、商业等）、材料和状况。

此数据的用途很多：它可以提供当地地理背景（包含在地图源数据中），以及促进世界银行等开发机构的投资。

下面是在孟加拉国达卡市绘制的建筑物地图：

为便于非政府组织工作人员确定资源的分配，罗列并显示不同状况的建筑物将很有帮助。一个区域的建筑物状况可能有利于了解未来的投资应重点考虑哪些区域。

查询建筑物要比处理点或更改集要更为复杂。在 OSM 的三个核心元素类型（节点、道路和关系）中，仅有节点（点）含有与之相关的地理信息。道路（线条或多边形）由节点组成，继承了节点的顶点。这意味着道路可以通过边框重构以支持有效的查询。

这会导致较为复杂的查询。您将会发现这种查询与上文所述寻找标记为医疗设施的建筑的查询类似。下面的查询会根据建筑物的条件来统计达卡地区的建筑物：

-- select out nodes and relevant columns
WITH nodes AS (
  SELECT
    id,
    tags,
    lat,
    lon
  FROM planet
  WHERE type = 'node'
),
-- select out ways and relevant columns
ways AS (
  SELECT
    id,
    tags,
    nds
  FROM planet
  WHERE type = 'way'
),
-- filter nodes to only contain those present within a bounding box
nodes_in_bbox AS (
  SELECT *
  FROM nodes
  WHERE lon BETWEEN 90.3907 AND 90.4235
    AND lat BETWEEN 23.6948 AND 23.7248
),
-- fetch and expand referenced ways
referenced_ways AS (
  SELECT
    ways.*,
    t.*
  FROM ways
  CROSS JOIN UNNEST(nds) WITH ORDINALITY AS t (nd, idx)
  JOIN nodes_in_bbox nodes ON nodes.id = nd.ref
),
-- fetch *all* referenced nodes (even those outside the queried bounding box)
exploded_ways AS (
  SELECT
    ways.id,
    ways.tags,
    idx,
    nd.ref,
    nodes.id node_id,
    ARRAY[nodes.lat, nodes.lon] coordinates
  FROM referenced_ways ways
  JOIN nodes ON nodes.id = nd.ref
  ORDER BY ways.id, idx
)
-- query ways matching the bounding box
SELECT
  count(*),
  tags['building:condition']
FROM exploded_ways
GROUP BY tags['building:condition']
ORDER BY count(*) DESC;

大多数建筑物都没有勘察（125000 是一个很大的数字！），但在已经勘察的建筑物中，大多数建筑物都为普通状况（与预期一样）。如果您要在地理上对这些建筑物进一步分组，则可以以此为起点来确定达卡的哪些区域可能会受益最多。

小结

OSM 数据极为丰富和珍贵，但由于其大小和数据模型的原因，可能难以利用。除下载大型文件以在本地处理所需的时间外，安装和配置工具以及将数据转换为更可查询格式需要的时间也都需要时间。我们认为，Amazon Athena 与每周更新的 ORC 版本的“星球”文件结合，将是一个极为强大和高性价比的组合。任何人都可立即通过简单的 SQL 查询数十亿条记录，让您有机会专注于分析，而不是基础设施。

要下载数据并使用其他工具进行试验，最新的 OSM ORC 格式文件可通过 OSM on AWS 下载，地址为 s3://osm-pds/planet/planet-latest.orc、s3://osm-pds/planet-history/history-latest.orc 和 s3://osm-pds/changesets/changesets-latest.orc。

我们期待看到您查询的结果！

亚马逊AWS官方博客