ビッグデータにおけるPostGIS ST_withinクエリの性能評価

#概要
ビッグデータのテーブルでPostGIS関数を使用すると時間がかかりすぎることがあります。この投稿ではST_withinクエリの最適な速度性能を評価します。

#1. PostGIS概要 : InputテーブルとGeometryカラム

アプリGPS対策テーブル、日本全国 : gps_log (1113万件)
市区町村テーブル : city_master (1909件)

Geometry系のカラムは主にEWKT形式で書いています

アプリGPSデータ : ポイント系

市区町村テーブル : ポリゴン系

→ 東京都 渋谷区のGPSデータを ST_Within で取得する

#2. PostGIS SELECT Queries評価

日本全国アプリGPSデータから渋谷区のデータをカウントするQueryをいろいろやってみる

INPUT DATA :
- アプリGPSデータ: gps_log (1113万件)
- 対象エリア 東京都 渋谷区 : polygon_shibuya

##　2.1. 緯度経度カラムをgeometryを作る

St_within_Select_1

SELECT COUNT(*) FROM gps_log
WHERE ST_WITHIN (ST_POINT(longitude,latitude)::geography::geometry,(SELECT geom FROM polygon_shibuya));

→結果 : 1分ぐらいで count=167918 が表示された

##　2.2. GPS Pointのgeoｍetryカラムを使う

St_within_Select_2

SELECT COUNT(*) FROM gps_log
WHERE ST_WITHIN (geom,(SELECT geom from polygon_shibuya));

→結果 : 40秒 でできた！ 前より速い！

アプリGPSデータのテーブルのgeomカラムにSpatial indexを追加したら、もっと速いかもしれませんのでやってみます

参考 : https://www.postgresql.jp/document/8.3/html/textsearch-indexes.html

add_index

CREATE INDEX gps_log_spatial_index ON gps_log USING GIST (geom);

そしてまた上記のQueryとして

→結果 : 42秒でできました...変わらないですね...

##　2.3. GPS Pointのgeometryカラムと市区町村のEWKT形式をテキストで記入

手動でDatabaseに渋谷区のポリゴンをEWKT形式でコピーして、以下みたいにPasteする。
ST_ASEWKT(geom)でgeometryをEWKT形式でも取得できます。

St_within_Select_3

SELECT COUNT(*) FROM gps_log
WHERE ST_WITHIN(geom,'SRID=4326;MULTIPOLYGON(((139.716558418 35.673433081,139.716560078  {...他のたくさん座標...} 35.675078081,139.715462309 35.673890306,139.716558418 35.673433081)))')

→ 7秒 でめっちゃ速くてできました！！！

#3. ST_WITHIN の SELECT Queries 評価まとめ

アプリGPS dataのgeomカラムにIndex付き/なしでもやってみて、処理時間を以下の表でまとめました。

• Polygon カラムをgeometryではなくEWKT形式でテキストで記入したらPerformanceが結構上がる
• 推定と違くて、gps tableのgeomカラムにIndexが付けても、なくても変わらなそう
• 今回は1市区町村だけにテストしましたが、今度全市区町村でどうやってReverse Geocodingやるべきかを調べます。

Method SELECT COUNT(*) FROM gps_log WHERE...	Indexなし	Index付き
2.1. アプリGPSデータの緯度経度カラムベース ST_WITHIN(ST_POINT(gps.longitude,gps.latitude) ::geography::geometry,shibuya.geom)	61秒	58秒
2.2. アプリGPSデータのGeomカラムベース ST_WITHIN(gps.geom,shibuya.geom)	40秒	42秒
2.3. GeomカラムをEWKT形式でテキストベース ST_WITHIN(gps.geom,'SRID=4326;MULTIPOLYGON(((139.716558418 35.673433081,139.716560078 {...他のたくさん座標...} 35.675078081,139.715462309 35.673890306,139.716558418 35.673433081)))')	7秒	6秒

以上