はじめに

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

PostGISとは

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

使用するデータ

GCPプロジェクトの作成とSQLインスタンスの作成

GCPのページから新しいプロジェクトを作成する。

作成したプロジェクトを選択しSQLを選択する。

インスタンスを作成を選択する。

PostgresSQLを選択する。

APIを有効にする。

各設定をする。ここでは料金が最小になる構成とした。

インスタンスを作成。最小構成にしたので立ち上がるまでにかなり時間がかかります。

インスタンスのアクセス設定

SQLインスタンスへの接続はIPホワイトリストを作成して接続元を制限するか、プロキシクラインとを使いトンネリング接続するかのどちらか。
今回は試験的に行うので全てのIPアドレスからの接続を許可する。

接続、ネットワーキング、ネットワークの追加を選択する。

適当な名前とネットワークに0.0.0.0/0を入れる。
どこからでもアクセスできるので注意が必要。

実際には接続側のIPを固定して指定するか、プロキシを使用するべき。
プロキシの使用方法は以下参照。
https://qiita.com/cognitom/items/c6b2ccb6e6b0f731850a

インスタンスへのアクセス確認

psqlで接続を確認する。インストール方法は以下記事参照。
https://qiita.com/tom-sato/items/037b8f8cb4b326710f71

概要からパブリックIPアドレスをコピー

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

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

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

ちなみにパスワードはユーザーから変更できる

postgisの導入

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

CREATE EXTENSION postgis;

成功すると以下のようになる。

データのインポート

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

インストールできたら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 "テーブル名"

psqlでコマンド\dtでテーブル一覧を表示すると無事インポートされている。