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

# はじめに

先日(2021/12/04)の [FOSS4G Japan 2021 Online](https://www.osgeo.jp/events/foss4g-2021/foss4g-2021-japan-online/foss4g-japan-2021-online-core-day) イベントでは、「[pgGeocoderのご紹介](https://www.osgeo.jp/events/foss4g-2021/foss4g-2021-japan-online/foss4g-japan-2021-online-core-day#sponsor5)」というタイトルで発表させて頂きました。

_FOSS4G Japan 2021 Online - pgGeocoderのご紹介_
<iframe src="//www.slideshare.net/slideshow/embed_code/key/ejpKhmySlR1rNt" width="595" height="485" frameborder="0" marginwidth="0" marginheight="0" scrolling="no" style="border:1px solid #CCC; border-width:1px; margin-bottom:5px; max-width: 100%;" allowfullscreen> </iframe> <div style="margin-bottom:5px"> <strong> <a href="//www.slideshare.net/geosanak/pggeocoder" title="pgGeocoderのご紹介" target="_blank">pgGeocoderのご紹介</a> </strong> from <strong><a href="https://www.slideshare.net/geosanak" target="_blank">Ko Nagase</a></strong> </div></iframe>
<br>

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

_PostGIS Day 2020 - Lightweight PostGIS Web Services Using pg tileserv and pg featureserv_
<iframe width="560" height="315" src="https://www.youtube.com/embed/TXPtocZWr78" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen></iframe>
<br>

# 環境構築

## 前提条件

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

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

1. Homebrew で PostgreSQL/PostGIS をインストール
    
    ```bash
    $ brew install postgresql postgis
    ```
2. `postgres` ユーザーをスーパーユーザーで作成
    
    ```bash
    $ createuser -s -l -w postgres
    ```
3. `postgres` サービスを起動
    
    ```bash
    $ brew services start postgresql
    ```
4. `psql` が動作することを確認
    
    ```bash
    $ psql -U postgres
    postgres=# 
    # \q と入力後、Enterキー押下で終了
    ```

## pgGeocoderの設定

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

1. pgGeocoderをローカル環境にクローン
    
    ```bash
    $ git clone git@github.com:gtt-project/pgGeocoder.git
    $ cd pgGeocoder
    ```
2. `.env.example` を `.env` にコピー
    
    ```bash
    $ cp .env.example .env
    ```
3. `addresses` データベースを作成
    
    ```bash
    $ createdb -U postgres addresses
    ```
4. pgGeocoderのインストール、ダウンロード・インポートスクリプトを順に実行
    
    ```bash
    $ 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
    ```
5. メンテナンススクリプトを実行
    
    ```bash
    $ bash scripts/maintenance.sh
    ```
6. サンプルクエリが通ることを確認
    
    ```bash
    $ psql -U postgres addresses
    ```
    ```sql
    SELECT * FROM geocoder('神奈川県横浜市西区みなとみらい３−６−３');
    ```
    ```
     code |     x      |     y     |                 address                 | todofuken | shikuchoson |       ooaza        | chiban | go 
    ------+------------+-----------+-----------------------------------------+-----------+-------------+--------------------+--------+----
        2 | 139.632805 | 35.458282 | 神奈川県横浜市西区みなとみらい三丁目6番      | 神奈川県  | 横浜市西区   | みなとみらい三丁目    | 6      | 
    (1 row)
    ```
    ```sql
    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の設定

1. Goのインストール
    
    ```bash
    $ brew install go
    ```
2. pg_featureservをローカル環境にクローンし、ビルド
    
    ```bash
    $ 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` にクローンするように記載されていますが、任意のフォルダにクローンしても特に問題なさそうでした。
3. pg_featureservユーザーガイドの [Quick Start](https://access.crunchydata.com/documentation/pg_featureserv/latest/quickstart/) の章を参考に、NaturalEarthのデータを `naturalearth` データベースに取り込み、pg_featureservが正しく動作することを確認します。
    - [pg_featureserv > Quick Start](https://access.crunchydata.com/documentation/pg_featureserv/latest/quickstart/)
    
    ```bash
    $ cd /path/to/pg_featureserv
    $ export DATABASE_URL=postgresql://postgres:postgres@localhost/naturalearth
    $ ./pg_featureserv
    ```
    http://localhost:9000/collections/ne.countries/items.html
    ![pg_featureserv-collections-ne.countries-items.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104365/9277032b-8d2c-2b3f-ce2f-1d4b61fe7531.png)

# pgGeocoderとpg_featureservの接続

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

- [pg_featureserv > Examples > Example: Retrieve Country Name for Coordinate Pair](https://access.crunchydata.com/documentation/pg_featureserv/latest/examples/ex_query_functions_locate_country/)
    
    > 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` スキーマにバイパスする関数を追加します。

1. pgGeocoderの `addresses` データベースに接続
    
    ```bash
    $ psql -U postgres addresses
    ```
2. `postgisftw` スキーマを作成
    
    ```sql
    CREATE SCHEMA postgisftw;
    ```
3. `geocoder` のバイパス関数を作成
    
    ```sql
    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;
    ```
4. `reverse_geocoder` のバイパス関数を作成
    
    ```sql
    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動作確認

1. pg_featureservの `DATABASE_URL` に、pgGeocoderの `addresses` データベースを指定して起動
    
    ```bash
    $ cd /path/to/pg_featureserv
    $ export DATABASE_URL=postgresql://postgres:postgres@localhost/addresses
    $ ./pg_featureserv
    ```
2. `geocoder` APIの動作確認
    URL: http://localhost:9000/functions/geocoder/items.json?addrs=神奈川県横浜市西区みなとみらい３−６−３
    ![geocoder-web-result.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104365/66f9ed31-1f2a-72b2-3162-949a4941902f.png)
3. `reverse_geocoder` APIの動作確認
    URL: http://localhost:9000/functions/reverse_geocoder/items.json?lon=141.342094&lat=43.050264
    ![reverse_geocoder-web-result.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104365/9627baf0-feb5-cfa5-de7b-d2547e0d8511.png)

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

# おわりに

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

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