Amazon Athenaで位置情報を扱う

2024年9月26日 10:00

こんにちは。キングカズです。ナビタイムジャパンのインフラ部門でログデータを管理しています。

AWSの分析サービスである『Amazon Athena』は地理空間関数をサポートしており、位置情報を扱う事ができます。本記事ではAmazon Athena(以下、Athena)の地理空間関数の使用方法をオープンデータの集計を交えながら説明します。

利用データ

国土交通省がオープンデータとして公開している『国土数値情報』から鉄道時系列データと行政区域データと利用します。

ダウンロードしたデータはshapefile形式なのでAthenaで扱う為にはテキスト形式に変換する必要があります。最も簡単な方法はQGISを利用してshapefileを読み込み、csvファイルとしてエクスポートする方法です。具体的な操作方法は割愛します。

Athenaテーブル作成

csvファイルをS3にアップロードし、Athenaのテーブルを作成します。

-- 駅(鉄道時系列データより)
CREATE EXTERNAL TABLE station (
  WKT STRING,
  N05_001 STRING,
  N05_002 STRING,
  N05_003 STRING,
  N05_004 STRING,
  N05_005b STRING,
  N05_005e STRING,
  N05_006 STRING,
  N05_007 STRING,
  N05_008 STRING,
  N05_009 STRING,
  N05_011 STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
  'quoteChar'='\"',
  'separatorChar'=','
)
STORED AS TEXTFILE
LOCATION 's3://your-bucket/station/'
TBLPROPERTIES (
  'skip.header.line.count'='1'
)

-- 行政区域データ
CREATE EXTERNAL TABLE admin_boundary (
  WKT STRING,
  N03_001 STRING,
  N03_002 STRING,
  N03_003 STRING,
  N03_004 STRING,
  N03_005 STRING,
  N03_007 STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
  'quoteChar'='\"',
  'separatorChar'=','
)
STORED AS TEXTFILE
LOCATION 's3://your-bucket/admin_boundary/'
TBLPROPERTIES (
  'skip.header.line.count'='1'
)

駅データは以下のクエリで項目の重複を除去しています。

CREATE TABLE station_unique
WITH (external_location = 's3://your-bucket/station_unique')
AS
SELECT
  WKT,
  N05_002,
  N05_011
FROM (
  SELECT
    WKT,
    N05_002,
    N05_011,
    ROW_NUMBER() OVER (PARTITION BY N05_002,N05_011 ORDER BY cnt DESC,WKT) AS n
  FROM (
    SELECT
      WKT,
      N05_002,
      N05_011,
      COUNT(1) AS cnt
    FROM
      station
    GROUP BY 1,2,3
  )
)
WHERE
  n = 1

2点間の直線距離

まずは2点間の直線距離を求めてみましょう。東京都庁(緯度:35.689501,経度:139.691722)と大阪府庁(緯度:34.686344,経度:135.520037)の距離を求めるクエリを書いてみます。

SELECT
  ST_Distance(ST_Point(139.691722, 35.689501),
    ST_Point(135.520037, 34.686344)) AS distance

結果は4.2906036528528215と謎の数値になります。この結果は平面座標系で計算した値なので誤りです。
地理空間データを扱う場合は平面座標系であるGEOMETRY型から地球を球体に見立てたGEOGRAPHY型に変換する必要があります。

SELECT
  ST_Distance(to_spherical_geography(ST_Point(139.691722, 35.689501)),
    to_spherical_geography(ST_Point(135.520037, 34.686344))) AS distance

今度は395137.64315383293(約395.137km)と適切な結果が返されます。

東京都庁(緯度:35.689501,経度:139.691722)の最寄り駅を出してみましょう。stationテーブルのWKTカラムの文字列はWell-know text形式である為、ST_GeometryFromText関数を用いる事でGEOMETRY型に変換できます。

SELECT
  n05_002 AS "路線名",
  n05_011 AS "駅名",
  ST_Distance(to_spherical_geography(ST_Point(139.691722, 35.689501)),
    to_spherical_geography(ST_GeometryFromText(wkt))) AS distance
FROM
  station_unique
ORDER BY
  distance
LIMIT 10

$$
\begin{array}{|l|l|l|} \hline
路線名 & 駅名 & distance \\ \hline
12号線大江戸線 & 都庁前 & 145.99 \\
高円寺線 & 柏木一丁目 & 536.41 \\
4号線丸ノ内線 & 西新宿 & 562.64 \\
... & ... & ... \\ \hline
\end{array}
$$

面積

次に面積を求めてみます。行政区域データから都道府県毎の面積を集計します。
addressテーブルのWKTカラムもWell-know text形式である為、文字列型→GEOMETRY型→GEOGRAPHY型と変換する事が可能です。GEOGRAPHY型にすればST_Area関数で面積を平方メートル単位で求められます。

SELECT
  SUBSTR(n03_007,1,2) || '_' || n03_001 AS "都道府県",
  CAST(SUM(ST_Area(to_spherical_geography(ST_GeometryFromText(wkt)))) / 1000000.0 AS BIGINT) AS area_km2
FROM
  admin_boundary
GROUP BY
  1
ORDER BY
  1

$$
\begin{array}{|l|l|} \hline
都道府県 & area\_km2 \\ \hline
01\_北海道 & 83267 \\
02\_青森県 & 9633 \\
03\_岩手県 & 15259 \\
… & … \\ \hline
\end{array}
$$

点とポリゴン・ポリラインの距離

Athenaクエリバージョン3で対応している地理空間関数は、ここまで説明した2点間の直線距離と面積のみです。以下のクエリで点とポリゴンの距離を求めようとするとエラーになってしまいます。

SELECT
  ST_Distance(to_spherical_geography(ST_Point(139.691722, 35.689501)), 
    to_spherical_geography(ST_GeometryFromText(wkt))) AS distance
FROM
  admin_boundary
LIMIT 10

INVALID_FUNCTION_ARGUMENT: When applied to SphericalGeography inputs, ST_Distance only supports POINT. Input type is: MULTI_POLYGON

回避策としてGEOMETRY型を併用して近似値を求めます。geometry_nearest_points関数によって点とGEOMETRYとの最寄り点を求め、求めた2点間の距離をST_Distance関数で計算します。

WITH tokyo_23ku AS (
  SELECT
    n03_001 || n03_004 AS "市区",
    geometry_union_agg(ST_GeometryFromText(wkt)) AS geom
  FROM
    admin_boundary
  WHERE
    n03_001 = '東京都' AND n03_004 LIKE '%区'
  GROUP BY 1
)
SELECT
  "市区",
  ST_Distance(to_spherical_geography(p[1]), to_spherical_geography(p[2])) AS distance
FROM (
  SELECT
    *,
    geometry_nearest_points(ST_Point(139.691722, 35.689501), geom) AS p
  FROM
    tokyo_23ku
)
ORDER BY distance

$$
\begin{array}{|l|l|} \hline
市区 & distance \\ \hline
東京都新宿区 & 0.0 \\
東京都渋谷区 & 440.2631463685545 \\
東京都中野区 & 760.983553305599 \\
... & ... \\ \hline
\end{array}
$$

点とポリゴンの内外判定(逆ジオコーディング)

最後に駅毎に住所を割り当てる為に点とポリゴンの内外判定を行います。内外判定するST_Contains関数はGEOGRAPHY型には対応してませんが、日本国内レベルの範囲であればGEOMETRY型でも十分な精度の結果を得る事ができます。
以下は内外判定によって駅毎の住所を求め、住所毎の駅数を集計するクエリです。

SELECT
  "市区",
  COUNT(DISTINCT "駅名") AS station_num
FROM (
  SELECT
    n05_011 AS "駅名",
    n03_001 || n03_004 AS "市区"
  FROM
    station_unique s, admin_boundary a
  WHERE
    ST_Contains(ST_GeometryFromText(a.wkt), ST_GeometryFromText(s.wkt))
)
GROUP BY 1
ORDER BY 2 DESC

$$
\begin{array}{|l|l|} \hline
市区 & station\_num \\ \hline
大阪府大阪市 & 551 \\
愛知県名古屋市 & 398 \\
京都府京都市 & 373 \\
... & ... \\ \hline
\end{array}
$$

上位に東京都がないのは、東京23区は区毎に項目が分かれている為です。

終わりに

Amazon Athenaの地理空間関数はPostGISと比較すると機能が不足しているものの、使いこなす事でS3に蓄積されたログデータを変換やDBへのデータ投入なしに即座に分析できます。
本記事では比較的小規模なデータを用いて基本的なクエリの書き方を説明しましたが、機会があればビッグデータを用いた実践的なクエリを紹介します。