【GCP】Cloud SQLにPostGISで地理情報データベースを構築する

# はじめに
地理空間情報をデータベース化する必要があったので備忘録として書きます。

# PostGISとは
【以下ChatGPTの要約】
PostGISは、PostgreSQLデータベースの拡張機能であり、地理空間情報を扱うための機能を提供します。これにより、地理空間データ（位置情報、地図データなど）を保存、検索、管理することができます。SQL標準に基づいた地理空間クエリの実行が可能になり、空間分析や地図作成など、幅広いアプリケーションでの使用が可能です。

# 使用するデータ
https://www.geospatial.jp/ckan/dataset/npli-pref-1km

# GCPプロジェクトの作成とSQLインスタンスの作成
GCPのページから新しいプロジェクトを作成する。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2605106/53cfe5da-45de-4b54-90c1-716c21dfc069.png)
<br>
作成したプロジェクトを選択しSQLを選択する。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2605106/fded637e-3854-383a-323a-c88d4c2d47d6.png)
<br>
インスタンスを作成を選択する。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2605106/c4d85dfb-d61e-f93f-62be-fa996bc5853b.png)
<br>
PostgresSQLを選択する。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2605106/4c586d47-5d1a-7ea9-1245-fdc5244a2d3c.png)
<br>
APIを有効にする。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2605106/c75e000e-7981-99ac-d111-f9f3897898e3.png)
<br>
各設定をする。ここでは料金が最小になる構成とした。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2605106/89264d66-9402-64a7-f4be-1b9e4b5e7ed3.png)
<br>
インスタンスを作成。最小構成にしたので立ち上がるまでにかなり時間がかかります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2605106/ecbc9e3d-3310-3e25-a6b1-9f529472d2f4.png)

# インスタンスのアクセス設定
SQLインスタンスへの接続はIPホワイトリストを作成して接続元を制限するか、プロキシクラインとを使いトンネリング接続するかのどちらか。
今回は試験的に行うので全てのIPアドレスからの接続を許可する。
<br>
接続、ネットワーキング、ネットワークの追加を選択する。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2605106/339e91a5-e8a8-dd05-9942-4dad8178cbb6.png)
<br>
適当な名前とネットワークに0.0.0.0/0を入れる。
どこからでもアクセスできるので注意が必要。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2605106/7a254455-2a0d-1e71-5f8c-588267bb2738.png)
<br>
実際には接続側のIPを固定して指定するか、プロキシを使用するべき。
プロキシの使用方法は以下参照。
https://qiita.com/cognitom/items/c6b2ccb6e6b0f731850a

# インスタンスへのアクセス確認
psqlで接続を確認する。インストール方法は以下記事参照。
https://qiita.com/tom-sato/items/037b8f8cb4b326710f71
<br>
概要からパブリックIPアドレスをコピー
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2605106/61db1807-6bc4-f340-390e-82a6dace08ac.png)

<br>
以下のコマンドでインスタンスにアクセスする。

```
psql -h "IPアドレス" -p 5432 -U postgres -d postgres
```

パスワードはインスタンス作成時に設定したものを入力。
以下のように表示されればアクセス成功。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2605106/617e72c8-ed30-634b-308d-ce7a9daac950.png)
<br>
ちなみにパスワードはユーザーから変更できる
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2605106/b1359e8a-ac34-abe3-b96b-3612dd70b974.png)

# postgisの導入
psql上で以下のコマンドでpostgisエクステンションを適用する。

```
CREATE EXTENSION postgis;
```

成功すると以下のようになる。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2605106/5fe871cd-f668-974b-209a-f61506e08e29.png)


# データのインポート
geojsonファイルをSQLインスタンスにインポートする。
やり方はいろいろあるみたいだが、GDALのogr2ogrを使うのが一番手っ取り早そう。
以下を参照しconda経由でGDALをインストールする。
https://info.qchizu.xyz/knowledge/knowledge-gdal-install/
（ちなみにいろいろなインストール方法があるが他のやり方だとうまくいかなかった）

<br>
インストールできたらAnaconda Promptより以下のコマンドでgeojsonファイルをインポートする。
"IPアドレス"にはインスタンスのIPアドレスを、passwordには設定したパスワードを、"geojsonファイル名"にはインポートするgeojsonファイルを、"テーブル名”には任意のテーブル名を設定する。

```
ogr2ogr -lco ENCODING=UTF-8 -oo ENCODING=UTF-8 -f "PostgreSQL" PG:"host=”IPアドレス” port=5432 dbname="postgres" user=postgres password='***'" "geojsonファイル名" -nln "テーブル名"
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2605106/58b16cc5-e667-4b64-8b79-bc8dcba3c7a6.png)
<br>
psqlでコマンド`\dt`でテーブル一覧を表示すると無事インポートされている。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2605106/42de09ca-e11f-1e47-be2c-24428d732b2f.png)


# QGISでインスタンスにアクセス
QGISからDBインスタンスにアクセスし、postGISサーバとして動作していることを確認する。
QGISのPostGISを左クリックし新規接続
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2605106/923b182e-60b7-9a75-17c2-c53d18b4666b.png)
<br>
以下の通り設定する。
名前：任意
ホスト：インスタンスのIPアドレス
ポート番号：5432
データベース：postgres
SSLモード：allow
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2605106/3b59da87-795a-d209-59d4-4fb574014470.png)

ユーザー名とパスワードを聞かれるので入力する。
<br>
追加したDBが選択できるようになりテーブルをレイヤに追加できる。
レイヤに追加するとDBからデータをダウンロードしてきて表示できる。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2605106/3fc5e385-fad2-aa4f-2dab-8a62edfd7a98.png)