snowflake 地理空間関数でエリア情報を集計してみた

snowflake 地理空間関数でエリア情報を集計してみた | Tableau-id Press -タブロイド-
snowflake-logo-1200x630-960x504-1

snowflakeの地理空間関数が充実してきたようなので、これらを使ってPODBのデータを空間データを集計してみました。

本記事で紹介する関数

ST_POINT

ST_INTERSECTS

ST_DISTANCE

ST_DWITHIN

ポイントに町丁目ポリゴンを紐づける

まず一つ目は、緯度・経度列からポイントデータを作り、

そのポイントに、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)場合、その町丁目のデータを駅のデータに結合します。

 

※町丁目ポリゴンデータは仕様上、隣り合う町丁目でポリゴンが重なる場合があるため、場合によって、それらを考慮して処理を行う必要があります。

 

ポイントの周辺人口の集計

次にポイントから指定した距離内に、代表点が入る町丁目の人口を集計してみようと思います。

今回は病院から直線距離500m以内の町丁目の人口を、病院ごとに集計します。

ポイントデータとして、日本の医療機関データを使用しています。

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関数を用い、ポイントからの距離を測り、

医療機関のポイントから町丁目の代表点までの距離が、

500m以内の場合、医療機関データに町丁目データが結合され、

結合した町丁目の人口を医療機関ごとに集計しています。

 

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のデータを使っていただければ、

上記のクエリをそのまま、コピペでテストしていただけるので、ぜひお試しください。

 

Prepper Open Data Bank

 

野口