More than 1 year has passed since last update.

RDBMS-GIS(MySQL,PostgreSQLなど)Advent Calendar 2021Day 19

pgGeocoderとpg_featureservで、簡易ジオコーダAPIを試してみる

Posted at 2021-12-19

はじめに

先日(2021/12/04)の FOSS4G Japan 2021 Online イベントでは、「pgGeocoderのご紹介」というタイトルで発表させて頂きました。

FOSS4G Japan 2021 Online - pgGeocoderのご紹介

pgGeocoderのご紹介 from Ko Nagase

スライドの9ページ目で、 ※Web APIサービスとして動作させる場合は、別途開発が必要 と記載していたのですが、最近の PostGIS Day で、 pg_featureserv なるものが取り上げられてましたので、少し試してみました。

PostGIS Day 2020 - Lightweight PostGIS Web Services Using pg tileserv and pg featureserv

環境構築

前提条件

macOS + Homebrew 環境を前提とします。

PostgreSQL/PostGIS のインストール・設定

Homebrew で PostgreSQL/PostGIS をインストール
```
$ brew install postgresql postgis
```
postgres ユーザーをスーパーユーザーで作成
```
$ createuser -s -l -w postgres
```
postgres サービスを起動
```
$ brew services start postgresql
```

psql が動作することを確認

$ psql -U postgres
postgres=# 
# \q と入力後、Enterキー押下で終了

pgGeocoderの設定

https://github.com/gtt-project/pgGeocoder/README.md を参考に、pgGeocoderのデータベースをインストールします。

pgGeocoderをローカル環境にクローン

$ git clone git@github.com:gtt-project/pgGeocoder.git
$ cd pgGeocoder

.env.example を .env にコピー
```
$ cp .env.example .env
```
addresses データベースを作成
```
$ createdb -U postgres addresses
```

pgGeocoderのインストール、ダウンロード・インポートスクリプトを順に実行

$ bash scripts/install.sh
$ bash scripts/download_isj.sh 2020
$ bash scripts/import_isj.sh 2020
$ bash scripts/download_estat.sh 2015
$ bash scripts/import_estat.sh 2015
$ bash scripts/download_ksj.sh 2021
$ bash scripts/import_ksj.sh 2021

メンテナンススクリプトを実行
```
$ bash scripts/maintenance.sh
```

サンプルクエリが通ることを確認

$ psql -U postgres addresses

SELECT * FROM geocoder('神奈川県横浜市西区みなとみらい３−６−３');

 code |     x      |     y     |                 address                 | todofuken | shikuchoson |       ooaza        | chiban | go 
------+------------+-----------+-----------------------------------------+-----------+-------------+--------------------+--------+----
    2 | 139.632805 | 35.458282 | 神奈川県横浜市西区みなとみらい三丁目6番      | 神奈川県  | 横浜市西区   | みなとみらい三丁目    | 6      | 
(1 row)

SELECT * FROM reverse_geocoder(141.342094, 43.050264);

 code |     x      |     y     |               address               | todofuken | shikuchoson  |      ooaza       | chiban | go 
------+------------+-----------+-------------------------------------+-----------+--------------+------------------+--------+----
    1 | 141.341681 | 43.050529 | 北海道札幌市中央区南七条西十一丁目3    | 北海道     | 札幌市中央区  | 南七条西十一丁目  | 3      | 
(1 row)

pg_featureservの設定

Goのインストール
```
$ brew install go
```
pg_featureservをローカル環境にクローンし、ビルド
```
$ mkdir -p 
$ git clone git@github.com:CrunchyData/pg_featureserv.git
$ cd pg_featureserv
$ go build
```
※ README.md には $GOPATH/src/github.com/CrunchyData/pg_featureserv にクローンするように記載されていますが、任意のフォルダにクローンしても特に問題なさそうでした。
pg_featureservユーザーガイドの Quick Start の章を参考に、NaturalEarthのデータを naturalearth データベースに取り込み、pg_featureservが正しく動作することを確認します。
- pg_featureserv > Quick Start
```
$ cd /path/to/pg_featureserv
$ export DATABASE_URL=postgresql://postgres:postgres@localhost/naturalearth
$ ./pg_featureserv
```
http://localhost:9000/collections/ne.countries/items.html

pgGeocoderとpg_featureservの接続

pg_featureservは、通常、ジオメトリもしくはジオグラフィカラムが存在するレコードを、GeoJSON形式で返しますが、pg_featureservユーザーガイドの以下の章に、非空間関数のサンプルがありましたので、これを参考にすることにしました。

pg_featureserv > Examples > Example: Retrieve Country Name for Coordinate Pair

Non-spatial functions (i.e. functions that don’t return spatial data) can also be accessed via pg_featureserv, as long as they are published in the postgisftw schema.

pgGeocoder側でのバイパス関数追加

上記ページのサンプルSQLを参考に、pgGeocoderの主要関数(geocoder, reverse_geocoder)を、 postgisftw スキーマにバイパスする関数を追加します。

pgGeocoderの addresses データベースに接続
```
$ psql -U postgres addresses
```
postgisftw スキーマを作成
```
CREATE SCHEMA postgisftw;
```

geocoder のバイパス関数を作成

CREATE OR REPLACE FUNCTION postgisftw.geocoder(
	addrs character varying)
RETURNS TABLE(
	code integer,
	x double precision,
	y double precision,
	address character varying,
	todofuken character varying,
	shikuchoson character varying,
	ooaza character varying,
	chiban character varying,
	go character varying
)
AS $$
DECLARE
  res geores;
BEGIN
  res := public.geocoder(addrs);
  RETURN QUERY
    SELECT
      res.code,
      res.x,
      res.y,
      res.address,
      res.todofuken,
      res.shikuchoson,
      res.ooaza,
      res.chiban,
      res.go;
END;
$$ LANGUAGE plpgsql;

reverse_geocoder のバイパス関数を作成

CREATE OR REPLACE FUNCTION postgisftw.reverse_geocoder(
	lon numeric,
	lat numeric,
	dist numeric default 50
)
RETURNS TABLE(
	code integer,
	x double precision,
	y double precision,
	address character varying,
	todofuken character varying,
	shikuchoson character varying,
	ooaza character varying,
	chiban character varying,
	go character varying
)
AS $$
DECLARE
  res geores;
BEGIN
  res := public.reverse_geocoder(lon, lat, dist);
  RETURN QUERY
    SELECT
      res.code,
      res.x,
      res.y,
      res.address,
      res.todofuken,
      res.shikuchoson,
      res.ooaza,
      res.chiban,
      res.go;
END;
$$ LANGUAGE plpgsql;

pg_featureservの接続とAPI動作確認

pg_featureservの DATABASE_URL に、pgGeocoderの addresses データベースを指定して起動

$ cd /path/to/pg_featureserv
$ export DATABASE_URL=postgresql://postgres:postgres@localhost/addresses
$ ./pg_featureserv

geocoder APIの動作確認
URL: http://localhost:9000/functions/geocoder/items.json?addrs=神奈川県横浜市西区みなとみらい３−６−３
reverse_geocoder APIの動作確認
URL: http://localhost:9000/functions/reverse_geocoder/items.json?lon=141.342094&lat=43.050264

いずれも、 psql 実行時と同じ結果が返ってくるのを確認できました。

おわりに

駆け足となりましたが、pgGeocoderとpg_featureservで、ジオコーダのWeb APIを実現できそうなことが分かりました。

実際にWeb APIを公開する場合は、セキュリティ面の検証や高負荷時のパフォーマンスなど、色々と検証する必要があると思いますが、pgGeocoderに限らず、PostgreSQLデータベース + ストアド(PL/pgSQL)関数が既にある場合は、Web APIを検討する場合の候補の一つに挙げられるかもしれません。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up