Docker + Posgre + SQLAlchemyで位置情報を使うまで #PostgreSQL

動作環境

Postgresql -> 11

事前準備

使用するpythonパッケージたち

sqlalchemy  = "1.2.16"
geoalchemy2 = "0.5.0"
psycopg2    = "2.7.6.1"

PosgreのDockerfileを準備する

FROM postgres:11

ENV POSTGIS_MAJOR 2.5
ENV POSTGIS_VERSION 2.5.1+dfsg-1.pgdg90+1

RUN apt-get update \
      && apt-cache showpkg postgresql-$PG_MAJOR-postgis-$POSTGIS_MAJOR \
      && apt-get install -y --no-install-recommends \
           postgresql-$PG_MAJOR-postgis-$POSTGIS_MAJOR=$POSTGIS_VERSION \
           postgresql-$PG_MAJOR-postgis-$POSTGIS_MAJOR-scripts=$POSTGIS_VERSION \
           postgis=$POSTGIS_VERSION \
      && rm -rf /var/lib/apt/lists/*

後々苦労することになったPostGISライブラリもapt-getで導入しておく

Posgre初回起動時に走らせるinit.shの設定

docker-composeを使用している場合は以下のファイルを作成しvolumeでゲストOSと共有する
- 一例(docker/db/conf/以下に下のスクリプトを置いておく)
  - ./docker/db/conf:/docker-entrypoint-initdb.d

1_init.sh

#!/bin/bash
set -e

# Geometryのためのモジュールインストール
psql -U root -d <DATABASE> -c "
    CREATE EXTENSION IF NOT EXISTS postgis;
    CREATE EXTENSION IF NOT EXISTS postgis_topology;
    CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;
    CREATE EXTENSION IF NOT EXISTS postgis_tiger_geocoder;
"

# ユーザの作成
psql -U root -d <DATABASE> << "EOSQL"
CREATE USER test;
alter role test with password 'test';
GRANT ALL PRIVILEGES ON DATABASE <DATABASE> TO test;
EOSQL

モジュールインストールとユーザ作成を同時にするとpermission deniedで怒られる
- 理由は調べてない

SQLAlchemyのORM作成

この辺に関しては他にも沢山記事があるので触りだけ

DATABASE = 'postgres://%s:%s@%s/%s' % (
    os.getenv("POSGRE_USER", <DB_USERNAME>),
    os.getenv("POSGRE_PASSWORD", <DB_PASSWORD>),
    "%s:%s" % (os.getenv("POSGRE_HOST", <DB_CONTAINER_NAME>), os.getenv("POSGRE_PORT", "5432")),
    os.getenv("POSGRE_DATABASE", <DATABASE>)
)

Modelの作成

上記設定でようやくGeometry型が使えるようになるはず

models.py

from datetime import datetime

from sqlalchemy import Column, Integer, String, Float, DateTime
from geoalchemy2 import Geometry

from setting import Base


class Place(Base):
    __tablename__ = '***'

    id = Column("id", String(255), unique=True, primary_key=True, nullable=False)
    geometry = Column("geometry", Geometry(geometry_type='POINT', dimension=2, srid=4326), nullable=False)
    created_at = Column("created_at", DateTime, default=datetime.now)
    updated_at = Column("updated_at", DateTime, default=datetime.now, onupdate=datetime.now)

まとめ

あまりに右往左往しすぎて作業ログがまともにない。笑
位置情報扱うのめんどくさい…笑