OpenStreetMap 可以回答哪些类型的问题？——利用 Athena 使云数据可视化

发布于: Jul 29, 2022

Amazon Athena 与每周更新的 ORC 版本的“星球”文件结合，将是一个极为强大和高性价比的组合。任何人都可立即通过简单的 SQL 查询数十亿条记录，让您有机会专注于分析，而不是基础设施

 

云数据可视化是我们利用数据的最终目的，以 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 手机走出去，核实相关建筑物的实际位置，并添加相关的额外信息，例如楼层数、用途（住宅、商业等）、材料和状况。

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

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

地图和数据 © OpenStreetMap 贡献者

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

查询建筑物要比处理点或更改集要更为复杂。在 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 查询数十亿条记录，让您有机会专注于分析，而不是基础设施。

 

相关文章

开发应用程序迁移方法以使用 Amazon Redshift 使您的数据仓库现代化