はじめに
仕事柄、地理情報を扱うですがこれまでは件数も少なく込み入った処理もなかったのでファイルで出力されたものを参照するくらいでした。ただ、これだと今後の拡張性とかちょっと込み入ったことをするのになかなか難しいなーと思っていたこともありPostGISを試してみることに。もう一つ大事なきっかけとしてこれまでFirestoreに地理情報を一部保存していたのですが、これもちょっと込み入ったことするには全く向いてなくてそのために回りくどいことをゴニョゴニョと実装する必要性に迫られていたのです。
この「ちょっと込み入ったこと」ってのは例えばある地点から何メートル以内の情報だけ抽出するとかそういうのです。
PostGISって何？
PostGISってのはすごーくざっくり説明すると、PostgreSQLで地理情報を扱えるようにするための拡張モジュールです。地理情報を扱える、つまり地理情報システム（GIS）としてPostgreSQLを使うためのものだと思ってください。
緯度経度の情報などを単なる文字列とか数値として格納して扱うこともできますが、そうではなく地理情報を格納するためのデータ型が追加されるのでいい感じに扱えるようになります。また、単に地理情報を格納できるようになるだけでなく非常に多くの関数が用意されていて、文字列とか数値として格納するだけだとアプリ側で頑張って計算する必要があったものも関数だけで簡単に扱えます。例えば先にあげた特定の地点の範囲内の情報をクエリするなんてのもSQLと組み合わせて簡単にできるようになります。
開発はPostgreSQLとは独立していて、カナダのRefractions Research Inc.というところが開発しています。もちろんOSSです。
というわけでPostGISが使えそうと思った僕は早速本を買ったのですが日本語の本はあまりなくて結果的にこの本を買いました。困ったときのManning Publications。どうでもいい話ですがオライリーよりManning Publicationsの本のほうが買ってるの多い。SpringとかRabbitMQ、かつてはMongoDBなんかも買った記憶がある。
Amazon Aurora ?
Amazon AuroraってのはAmazon Web Services（AWS）が提供するRDBMSのマネージドサービスです。詳細はAWSのサイトに譲りますが、エンジンとしてMySQLとPostgreSQLを選べます。そしてこのPostgreSQLが昨年の秋くらいにPostGISに対応したのです。
Amazon Aurora PostgreSQL が PostGIS 3.1 をサポート
というわけで、PostGISが使えるならAuroraを使うに決まってるということで今回はAuroraでPostGISを試していきました。
セットアップ
まず、Auroraを使うのでそのインスタンス作ったりを最初にやる必要があるのですが、それ自体は本題じゃないので割愛します。ぶっちゃけマネジメントコンソールで画面ポチポチしてれば起動されます。素晴らしい。
インスタンスができたら早速接続するのですがここも割愛します。これは単にPostgreSQLのクライアントで接続するだけなので何も難しいことはありません。ドキュメントとおりにやればOK。
さて、インスタンスを作っただけではPostGISは有効になっていないようです。PostGISを使いたい場合はエクステンションを読み込む必要があるとのこと。
というわけでやっていきます。読み込むのはこの4つでいいらしい。以下の4つを psqlでデータベースにつないだ状態で実行していけばいいです。
CREATE EXTENSION postgis; CREATE EXTENSION fuzzystrmatch; CREATE EXTENSION postgis_tiger_geocoder; CREATE EXTENSION postgis_topology;
読み込み終わったら有効になってるか確認してみます。
postgres=> select * from postgis_version();
postgis_version
---------------------------------------
3.1 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
(1 row)
無事に有効になったようだ。
続いて rds_superuser ロールにエクステンションの所有権を移してあげる必要があるらしい。何を言ってるかさっぱりですね。このあたりはAWSのAuroraを使う場合に必要となる作業です。
まず、\dn で所有権のリストが確認できます。
postgres=> \dn
List of schemas
Name | Owner
------------+----------
public | postgres
tiger | rdsadmin
tiger_data | rdsadmin
topology | rdsadmin
このtigerとかそのあたりのオーナーを rds_superuser にしてあげる必要があるということらしい。と言ってもこれも簡単で先ほどと同じく以下を実行するだけです。もちろん psql等でデータベースに接続した上で、です。
ALTER SCHEMA tiger OWNER TO rds_superuser; ALTER SCHEMA tiger_data OWNER TO rds_superuser; ALTER SCHEMA topology OWNER TO rds_superuser;
終わったら、もう一度 \dn を実行。
postgres=> \dn
List of schemas
Name | Owner
------------+---------------
public | postgres
tiger | rds_superuser
tiger_data | rds_superuser
topology | rds_superuser
無事に変更されたようです。
続いて rds_superuser ロールにオブジェクトの所有権を転送する。何を言ってるからわからないがドキュメントにそう書いてある。これもドキュメントのコピペで実行します。
postgres=> CREATE FUNCTION exec(text) returns text language plpgsql volatile AS $f$ BEGIN EXECUTE $1; RETURN $1; END; $f$;
CREATE FUNCTION
postgres=> SELECT exec('ALTER TABLE ' || quote_ident(s.nspname) || '.' || quote_ident(s.relname) || ' OWNER TO rds_superuser;')
FROM (
SELECT nspname, relname
FROM pg_class c JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE nspname in ('tiger','topology') AND
relkind IN ('r','S','v') ORDER BY relkind = 'S')
s;
exec
--------------------------------------------------------------------
ALTER TABLE tiger.loader_variables OWNER TO rds_superuser;
ALTER TABLE tiger.loader_lookuptables OWNER TO rds_superuser;
ALTER TABLE tiger.zip_lookup OWNER TO rds_superuser;
ALTER TABLE tiger.tract OWNER TO rds_superuser;
ALTER TABLE tiger.geocode_settings OWNER TO rds_superuser;
ALTER TABLE tiger.tabblock OWNER TO rds_superuser;
ALTER TABLE tiger.county OWNER TO rds_superuser;
ALTER TABLE tiger.bg OWNER TO rds_superuser;
ALTER TABLE tiger.geocode_settings_default OWNER TO rds_superuser;
ALTER TABLE tiger.pagc_gaz OWNER TO rds_superuser;
ALTER TABLE tiger.state_lookup OWNER TO rds_superuser;
ALTER TABLE tiger.pagc_lex OWNER TO rds_superuser;
ALTER TABLE tiger.state OWNER TO rds_superuser;
ALTER TABLE tiger.pagc_rules OWNER TO rds_superuser;
ALTER TABLE tiger.direction_lookup OWNER TO rds_superuser;
ALTER TABLE topology.topology OWNER TO rds_superuser;
ALTER TABLE topology.layer OWNER TO rds_superuser;
ALTER TABLE tiger.place OWNER TO rds_superuser;
ALTER TABLE tiger.zip_state OWNER TO rds_superuser;
ALTER TABLE tiger.zip_state_loc OWNER TO rds_superuser;
ALTER TABLE tiger.secondary_unit_lookup OWNER TO rds_superuser;
ALTER TABLE tiger.cousub OWNER TO rds_superuser;
ALTER TABLE tiger.street_type_lookup OWNER TO rds_superuser;
ALTER TABLE tiger.edges OWNER TO rds_superuser;
ALTER TABLE tiger.place_lookup OWNER TO rds_superuser;
ALTER TABLE tiger.addrfeat OWNER TO rds_superuser;
ALTER TABLE tiger.county_lookup OWNER TO rds_superuser;
ALTER TABLE tiger.faces OWNER TO rds_superuser;
ALTER TABLE tiger.countysub_lookup OWNER TO rds_superuser;
ALTER TABLE tiger.featnames OWNER TO rds_superuser;
ALTER TABLE tiger.zip_lookup_all OWNER TO rds_superuser;
ALTER TABLE tiger.addr OWNER TO rds_superuser;
ALTER TABLE tiger.zip_lookup_base OWNER TO rds_superuser;
ALTER TABLE tiger.zcta5 OWNER TO rds_superuser;
ALTER TABLE tiger.tabblock20 OWNER TO rds_superuser;
ALTER TABLE tiger.loader_platform OWNER TO rds_superuser;
ALTER TABLE tiger.pagc_lex_id_seq OWNER TO rds_superuser;
ALTER TABLE tiger.county_gid_seq OWNER TO rds_superuser;
ALTER TABLE tiger.state_gid_seq OWNER TO rds_superuser;
ALTER TABLE tiger.place_gid_seq OWNER TO rds_superuser;
ALTER TABLE tiger.cousub_gid_seq OWNER TO rds_superuser;
ALTER TABLE tiger.edges_gid_seq OWNER TO rds_superuser;
ALTER TABLE tiger.addrfeat_gid_seq OWNER TO rds_superuser;
ALTER TABLE tiger.faces_gid_seq OWNER TO rds_superuser;
ALTER TABLE tiger.featnames_gid_seq OWNER TO rds_superuser;
ALTER TABLE tiger.addr_gid_seq OWNER TO rds_superuser;
ALTER TABLE tiger.zcta5_gid_seq OWNER TO rds_superuser;
ALTER TABLE tiger.tract_gid_seq OWNER TO rds_superuser;
ALTER TABLE tiger.tabblock_gid_seq OWNER TO rds_superuser;
ALTER TABLE tiger.bg_gid_seq OWNER TO rds_superuser;
ALTER TABLE tiger.pagc_gaz_id_seq OWNER TO rds_superuser;
ALTER TABLE tiger.pagc_rules_id_seq OWNER TO rds_superuser;
ALTER TABLE topology.topology_id_seq OWNER TO rds_superuser;
(53 rows)
終わったらこれもテストしてみます。
postgres=> SET search_path=public,tiger;
SET
postgres=> SELECT na.address, na.streetname, na.streettypeabbrev, na.zip
FROM normalize_address('1 Devonshire Place, Boston, MA 02109') AS na;
address | streetname | streettypeabbrev | zip
---------+------------+------------------+-------
1 | Devonshire | Pl | 02109
(1 row)
postgres=> SELECT topology.createtopology('my_new_topo',26986,0.5);
createtopology
createtopology
----------------
1
(1 row)
というわけで下準備は完了。
早速試してみる
では早速テーブルを作ってみます。といっても普通のテーブル同様に create table で作成するだけ。地理情報をもたせる列はgeometry型で、ジオメトリタイプとSRIDというものも指定する。
ジオメトリタイプというのは地図情報を扱うとよく目にするPOINT、 LINESTRING、 POLYGONとかってやつ。それぞれMulti〜ってのもある。ここではとりあえずPOINTでやってみた。
また、SRIDってのは空間参照IDというものらしく、ArcGISのページによると
空間参照 ID (SRID) は、特定の座標系、許容値、および解像度に関連付けられた一意の ID です。
とのこと。こっちのブログにはもうちょっと詳しく載ってる。
PostGISで使用されるSRIDについて | JURI★GIS
正直なところ現時点では知識不足で何を選択したらいいかわからないが、このブログいわく世界でメジャーと言われる測地系のWGS84と地理座標系の4326を選択してみよう。
postgres=> CREATE TABLE sample1 ( gid SERIAL PRIMARY KEY, geo GEOMETRY(POINT, 4326) );
DBに存在するジオメトリなカラムの一覧も検索できる。
postgres=> SELECT * FROM geometry_columns; f_table_catalog | f_table_schema | f_table_name | f_geometry_column | coord_dimension | srid | type -----------------+----------------+--------------+-------------------+-----------------+-------+----------------- postgres | tiger | county | the_geom | 2 | 4269 | MULTIPOLYGON postgres | tiger | state | the_geom | 2 | 4269 | MULTIPOLYGON postgres | tiger | place | the_geom | 2 | 4269 | MULTIPOLYGON postgres | tiger | cousub | the_geom | 2 | 4269 | MULTIPOLYGON postgres | tiger | edges | the_geom | 2 | 4269 | MULTILINESTRING postgres | tiger | addrfeat | the_geom | 2 | 4269 | LINESTRING postgres | tiger | faces | the_geom | 2 | 4269 | MULTIPOLYGON postgres | tiger | zcta5 | the_geom | 2 | 4269 | MULTIPOLYGON postgres | tiger | tabblock20 | the_geom | 2 | 4269 | MULTIPOLYGON postgres | tiger | tract | the_geom | 2 | 4269 | MULTIPOLYGON postgres | tiger | tabblock | the_geom | 2 | 4269 | MULTIPOLYGON postgres | tiger | bg | the_geom | 2 | 4269 | MULTIPOLYGON postgres | my_new_topo | face | mbr | 2 | 26986 | POLYGON postgres | my_new_topo | node | geom | 2 | 26986 | POINT postgres | my_new_topo | edge_data | geom | 2 | 26986 | LINESTRING postgres | my_new_topo | edge | geom | 2 | 26986 | LINESTRING postgres | public | sample1 | geo | 2 | 4326 | POINT
作ったテーブルに試しにデータ入れてみる。
SQLで入れるにはST_GeomFromText()っていう関数を使う。これはwkt形式で記述されたテキストを変換する関数。POINTに入れる座標はlongitude（経度）、latitude（緯度）の順で指定する。とりあえず東京駅の座標を入れてみます。
各地の座標はGoogle Mapで調べられる。Google Mapで座標を知りたい場所を右クリックすると表示されるのでそれをコピーすればいい。ちなみにGoogle Mapはlatitude、longitudeで表示されているので注意。
実際に入れます。
postgres=> INSERT INTO sample1 (gid, geo) values(1,ST_GeomFromText('POINT(139.7671677130547 35.681436619443254)', 4326));
INSERT 0 1
無事にインサートできたので、この時点でselectしてみるとこんな感じです。
postgres=> select * from sample1; gid | geo -----+---------------------------------------------------- 1 | 0101000020E6100000BFC34DA38C7861401D67AD5039D74140 (1 row)
ジオメトリ型のカラムに入ってるデータはそのままだと上記のような値になってしまう。ここは出力を変換する必要があるんだけどその関数も用意されている。それについては後ほど。
また、冒頭で述べたある地点から指定した距離内のレコードだけをselectするとかも簡単にできる。ここでは1件しかまだ入れていないんだけど、試しにGoogle Map上では900mほど離れているコレド東京の座標（経度139.77458133855586、緯度35.68270896117478）を使って試す。指定距離内を検索するのはST_DWithinという関数でできる。
postgres=> SELECT * FROM sample1 WHERE ST_DWithin(geo, ST_GeomFromText('POINT(139.77458133855586 35.68270896117478)', 4326), 500, true);
gid | geo
-----+-----
(0 rows)
注意が必要なのはこの ST_DWithin という関数は距離を指定できるんだけどその単位はSRIDによって異なる。今回使った4326だと度が単位でその値は実際にはメートルだそうだ。なので指定したい距離をメートルで普通に入れる。
500m以内で検索すると当然引っかからない。というわけで1000mにしてみる。
postgres=> SELECT * FROM sample1 WHERE ST_DWithin(geo, ST_GeomFromText('POINT(139.77458133855586 35.68270896117478)', 4326), 1000, true);
gid | geo
-----+----------------------------------------------------
1 | 0101000020E6100000BFC34DA38C7861401D67AD5039D74140
(1 row)
無事に検索できた。でもこれだとなんだかわからない。というわけでジオメトリ型をGeoJSONに変換する関数である ST_AsGeoJSON()` を使って出力する。
postgres=> SELECT gid, ST_AsGeoJson(geo) FROM sample1 WHERE ST_DWithin(geo, ST_GeomFromText('POINT(139.77458133855586 35.68270896117478)', 4326), 1000, true);
gid | st_asgeojson
-----+-------------------------------------------------------------
1 | {"type":"Point","coordinates":[139.767167713,35.681436619]}
(1 row)
うむ、いい感じ。
CSVファイルをインポートしてみる
さて、もうちょっとガッツリとデータを入れたいなと思ったので手持ちのデータをインポートしてみることに。なお、PostgreSQLに用意されている COPY はCSVファイル中にwkt形式で書かれた情報があれば自動的にジオメトリ型に変換してくれるらしいが、自分は横着してTwitterで教えてもらったposticoを利用した。超絶簡単に取り込めた。
こんな感じのCSVファイルを取り込む。
gid,geo 06dmUFSUzYzTZA8NodFi, LINESTRING (142.3849795 43.7489348,142.3849795 43.7489348,142.3850022 43.7489459,142.3850022 43.7489459,142.3850022 43.7489459,142.3849791 43.7489387,142.3849791 43.7489387,142.384979 43.7489386,142.384979 43.7489386,142.3849791 43.7489387,142.3849791 43.7489387,142.384979 43.7489386,142.384979 43.7489386) 09MSZxBYpOujH17lhogX, LINESTRING (142.3849736 43.7489202,142.3849736 43.7489202,142.3849736 43.7489202,142.3849736 43.7489202,142.3849736 43.7489202,142.3849736 43.7489202,142.3849736 43.7489202,142.3849736 43.7489202,142.3849736 43.7489202,142.3849736 43.7489202,142.3849739 43.7489202,142.3849739 43.7489202,142.3849739 43.7489202,142.3849739 43.7489202,142.3849736 43.7489202,142.3849736 43.7489202,142.3849735 43.7489203,142.3849735 43.7489203,142.384973 43.7489204,142.384973 43.7489204,142.384967 43.7489213,142.384967 43.7489213,142.3849711 43.7489221,142.3849711 43.7489221,142.3849678 43.7489224,142.3849678 43.7489224,142.3849716 43.7489207,142.3849716 43.7489207)
このデータではidが文字列になってますが気にしないでください。実際には自分が定義したテーブルの型にあわせてもらえればと思います。
このLINESTRING(lon1 lat1, lon2 lat2)ってのがwkt形式です。経度と緯度の組み合わせを空白区切りで書いて、各地点をカンマ区切りで記す感じです。これはLINESTRING（つまり地点を結んだ線の表現）の場合ですがPOLYGONとかも同じような感じで記述します。
wkt形式ってのは Well-known textのことで ベクタ形式幾何学オブジェクトを投影法 (地図)を基に変換し地図上に表現させるマークアップ言語である とWikipediaに書いてありました。
なお、注意点というか当たり前ではあるんだが地理情報がPOINTとLINSTRINGとかPOLYGONとMULTIPOLYGONで混在しているとダメなのでそれだけ気をつけてください。まあ同じ列に異なる型の値が入らないってだけなので当たり前の話なんだけどね。
最後にこれはPostgreSQLもPostGISも関係ないんだけど今回CSVインポートに使ったGUIのPostgreSQLクライアントツールであるposticoではなぜか上記のCSVだとうまく認識されず、wktの中身がカンマ区切りで認識されてしまった。LINESTRING全体をダブルクオーテーションでくくったりしたけどダメ。でも似たデータのほうは問題なく認識される。何が原因かはわからなかったけど今回は列の区切り文字をカンマではなくセミコロンにすることで対応しました。つまりComma Separated ValueではなくSemicolon Separated Valueになってしまった。だが特に支障はない。
インデックスを作成する
もちろん普通にインデックスを作成することもできる。通常の列に対するインデックスは普通のDBと同じように作ればいい。
PostGISの場合にはジオメトリ型の列を対象にした空間インデックスも作成が可能になっていて、この場合はGiSTインデックスを作成することになる。GiSTってのはGeneralized Search Treeという意味らしい。詳細はこちら。
とにもかくにもGiSTインデックスを作るにはこんな感じでDDL分を実行すればいい。
CREATE INDEX ix_sample1_geo ON sample1 USING GiST (geo);
まとめ
というわけでAWSのAurora PostgreSQLを使ってPostGISを試してみた。試してみた結果、どうしようかなーと思っていた実際のワークロード上の課題もPostGISを使うことで簡単に解決できそうなことがわかったので実際に導入してみようと思う。
