snowflakeの地理空間関数が充実してきたようなので、これらを使ってPODBのデータを空間データを集計してみました。
本記事で紹介する関数
ポイントに町丁目ポリゴンを紐づける
まず一つ目は、緯度・経度列からポイントデータを作り、
そのポイントに、2020年の町丁目ポリゴンデータを紐づける処理です。
ポイントデータには駅の位置情報データを使用しています。
SELECT *
FROM "PREPPER_OPEN_DATA_BANK_POINT_AND_POLYLINE"."E_PODB"."POINT_STATION_01" AS POINT
,"PREPPER_OPEN_DATA_BANK"."BETA"."STREET_00_FUNDAMENTAL_2020" AS STREET
WHERE STATION_NAME='上野' AND ST_INTERSECTS(ST_POINT(POINT.LONGITUDE,POINT.LATITUDE),STREET.POLYGON_JP_STREET)=TRUE;緯度経度からポイントデータを作るのにはST_POINT関数を使用しています。
ST_POINTで作成したポイントに、ポイントが所在する町丁目の情報を紐づけるために、ST_INTERSECTS関数を使っています。
この関数は指定した二つの空間オブジェクトが、空間のいずれかの部分を共有する場合TRUEを返します。
WHERE句にて、ST_INTERSECTS関数を用い、駅のポイントと町丁目のポリゴンを比較し、
領域が重なる（関数の結果がTRUE）場合、その町丁目のデータを駅のデータに結合します。
※町丁目ポリゴンデータは仕様上、隣り合う町丁目でポリゴンが重なる場合があるため、場合によって、それらを考慮して処理を行う必要があります。
ポイントの周辺人口の集計
次にポイントから指定した距離内に、代表点が入る町丁目の人口を集計してみようと思います。
今回は病院から直線距離500ｍ以内の町丁目の人口を、病院ごとに集計します。
ポイントデータとして、日本の医療機関データを使用しています。
SELECT
MEDICAL_INSTITUTION_NAME,
PODB_MEDICAL_INSTITUTION_ID,
SUM(POPULATION) as POPULATION_1000m
FROM "PREPPER_OPEN_DATA_BANK_POINT_AND_POLYLINE"."E_PODB"."MEDICAL_INSTITUTIONS_POINT_2020" AS MED
,"PREPPER_OPEN_DATA_BANK"."BETA"."STREET_00_FUNDAMENTAL_2020" AS STREET
WHERE ST_DISTANCE(POINT_MEDICAL_INSTITUTION,STREET.POINT_JP_STREET)<500
GROUP BY
MEDICAL_INSTITUTION_NAME,
PODB_MEDICAL_INSTITUTION_ID;WHERE句内で、ST_DISTANCE関数を用い、ポイントからの距離を測り、
医療機関のポイントから町丁目の代表点までの距離が、
500ｍ以内の場合、医療機関データに町丁目データが結合され、
結合した町丁目の人口を医療機関ごとに集計しています。
ST_DWITHIN関数でも同様の処理を行うことが可能ですが、
ST_DISTANCE関数を用いた上記の場合はWHのサイズがSサイズで3秒ほどで処理が終わるのに対し、
下記のST_DWITHIN関数を使用した場合は、処理が数分かかりました。（重いので途中でやめた。データ絞れば同様の結果が出る）
SELECT
MEDICAL_INSTITUTION_NAME,
PODB_MEDICAL_INSTITUTION_ID,
SUM(POPULATION) as POPULATION_1000m
from "PREPPER_OPEN_DATA_BANK_POINT_AND_POLYLINE"."E_PODB"."MEDICAL_INSTITUTIONS_POINT_2020" AS MED
,"PREPPER_OPEN_DATA_BANK"."BETA"."STREET_00_FUNDAMENTAL_2020" AS STREET
where ST_DWITHIN(POINT_MEDICAL_INSTITUTION,STREET.POINT_JP_STREET,500)=TRUE
GROUP BY
MEDICAL_INSTITUTION_NAME,
PODB_MEDICAL_INSTITUTION_ID;まとめ
Prepper Open Data Bankのデータを使っていただければ、
上記のクエリをそのまま、コピペでテストしていただけるので、ぜひお試しください。
野口
