⛳

Lambda・Aurora Postgres・PostGISで地理情報APIを構築

2024/02/02に公開

Python

やりたいこと

APIGateway, lambdaでAPIを作成
lambdaからAurora Postgresに接続
Aurora PostgresでPost GISを使用
住所を送ると、DBに登録したlocationを近い順にtop10を返す

1. は終わっているので2. 3. 4.をやります。

AuroraとAurora Serverlessの違い

・主な違いはRDS Proxyに対応したこと
RDS Proxyはコネクションをプールさせておくことで同時接続数などの問題を回避できる。
・

参考：

Lambda関数とAuroraPostgresの接続

やりかた

直接接続
・単純なアプリケーションや、低いコネクション数で十分な場合
RDSプロキシ経由で接続
・コネクションをプールしておくことで、コネクションの再利用や同時接続数を減らしデータベースのパフォーマンスが上られる。

直接接続するのとRDSプロキシ経由どちらがいい？？
➡︎
今回の場合はコネクション数が少ないのでRDS Proxyは使わずに直接接続をする。
同時接続数などが増えてきたらProxyを経由させるようにする。

参考：

Aurora serverless v2の構築

コンソール上から構築
RDS➡︎Aurora Postgres
で進んでいくとserverlessを選べるので選択

参考:
【AWS】Amazon Aurora（Serverless v2）の構築手順

PostgreSQL クライアントを使用したAurora Postgresへの接続

PgAdminを利用して接続

注意：
セキュリティグループにポート5432へのインバウンドルールがあることを確認してください。

・Host：エンドポイント名
・Database：DB名
デフォルトでpostgresというDBが作成されるので、何も設定してない時は「postgres」
・ユーザ名：postgres
・パスワード：設定したパスワード

PgAdminの使用方法

参考:
Aurora Serverless(v2)のPostgreSQLに外部のSQLクライアントから接続してみた

PostgreSQL クライアントを使用した DB クラスターへの接続

接続できたら、PostGISを有効にする

PostGISの拡張機能をオンにする

CREATE EXTENSION IF NOT EXISTS postgis;

バージョン確認

SELECT PostGIS_version();

ダミーデータでテスト

テーブルの作成

CREATE TABLE aed (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    address VARCHAR(255),
    latitude DOUBLE PRECISION,
    longitude DOUBLE PRECISION
);

データの挿入

INSERT INTO aed (id, name, address, latitude, longitude) VALUES
(1, 'いわき明星大学保健管理センター', '福島県いわき市中央台飯野5-5-1', 37.018524, 140.918915),
(2, '県立保健大学', '青森県青森市大字浜館字間瀬58-1', 40.814526, 140.792755),
(3, '古賀ゴルフ・クラブ', '福岡県古賀市鹿部1310-1', 33.725086, 130.454056),
(4, '国営沖縄記念公園（海洋博覧会地区）', '沖縄県国頭郡本部町字石川424', 26.693777, 127.878372),
(5, '県庁行政棟 １階ロビー', '福岡県福岡市博多区東公園7-7', 33.606197, 130.418015),
(6, '県庁議会棟 １階', '福岡県福岡市博多区東公園7-7', 33.606197, 130.418015),
(7, '筑紫保健福祉環境事務所', '福岡県大野城市白木原3-5-25', 33.530331, 130.483597),
(8, '粕屋保健福祉事務所', '福岡県糟屋郡粕屋町戸原東１丁目７−２６', 33.619884, 130.474899),
(9, '宗像保健福祉環境事務所', '福岡県宗像市東郷1-2-1', 33.804657, 130.540314),
(10, '遠賀保健福祉環境事務所', '福岡県遠賀郡水巻町吉田西2-17-7', 33.848404, 130.702682);

クエリ
(4, '国営沖縄記念公園（海洋博覧会地区）', '沖縄県国頭郡本部町字石川424', 26.693777, 127.878372),
のlatitude, lontitudeを挿入

SELECT id, name, address, latitude, longitude, 
       ST_Distance(
           ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)::geography,
           ST_SetSRID(ST_MakePoint(127.878372, 26.693777), 4326)::geography
       ) AS distance
FROM aed
ORDER BY ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)::geography <-> 
         ST_SetSRID(ST_MakePoint(127.878372, 26.693777), 4326)::geography
LIMIT 3;

一番近い点やdistanceが返ってくる

今回は同じVPC内のサブネットに配置

[Lambda 接続のセットアップ]ページを使用して、既存のDB クラスターを新規および既存の Lambda 関数に接続できます。セットアッププロセスでは、必要なセキュリティグループが自動的にセットアップされます

zipフォルダをlambdaにアップロードする

ローカル環境で新規フォルダを作成
・lambda_function.pyを作成
・psycopg2-binaryをインストール
下記リンクからフォルダごとzipでダウンロード
https://github.com/jkehler/awslambda-psycopg2

3 .ダウンロード後、3.9用のフォルダを新規作成したフォルダにコピーする

ターミナルでzipする

zip -r location_search.zip ./*

lambdaのランタイムが、pip installしたローカルのPythonバージョンと一致しているか確認
フォルダ名をpsycopg2-3.9からpsycopg2に変更

注意：

パッケージの正しいビルドとパッケージ化
環境: psycopg2-binary を含む依存関係は、Lambda 実行環境（Amazon Linux）と互換性がある必要がある。今回はaws用にビルドされたpsycopg2を使用
本番環境ではpsycopg2-binaryではなくpsycopg2をビルドして使用が推奨されている

参考：

Lambdaに環境変数を設定

Secret Managerを使う

下記コードでテスト

# 環境変数からデータベース接続情報を取得
    db_host = os.environ['DB_HOST']
    db_name = os.environ['DB_NAME']
    db_user = os.environ['DB_USER']
    db_password = os.environ['DB_PASSWORD']
    
    # データベースに接続
    conn = psycopg2.connect(
        dbname=db_name,
        user=db_user,
        password=db_password,
        host=db_host
    )
    
    # カーソルを作成してクエリを実行
    with conn.cursor() as cur:
        cur.execute("""
            SELECT id, name, address, latitude, longitude, 
                   ST_Distance(
                       ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)::geography,
                       ST_SetSRID(ST_MakePoint(127.878372, 26.693777), 4326)::geography
                   ) AS distance
            FROM aed
            ORDER BY ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)::geography <-> 
                     ST_SetSRID(ST_MakePoint(127.878372, 26.693777), 4326)::geography
            LIMIT 5;
        """)
        result = cur.fetchall()
        print(result)
    
    # データベース接続を閉じる
    conn.close()
    
    return result

Aurora PostgreSQLにデータをimportする

前提

CSVのヘッダーが、格納するテーブルのカラム名と一致していること

copyコマンドを利用してローカルPCからファイルをインポート

DBに接続

例:

psql -h エンドポイント -d DB名 -U postgres -W

その後パスワードを入力

copyコマンドを使用して接続

なぜか1度目にエラーになるときが、2回目実行すると通る

COPY 件数
が出てきたら成功

例:

\copy テーブル名 FROM 'コピーしたいファイルの絶対パス' WITH (FORMAT CSV, DELIMITER ',', HEADER);

詳細：

参考:
Amazon S3のデータをAmazon Auroraにインポートする

API経由でクエリを叩く

実際にAPIにリクエストを送り結果が返ってきているかを確認

lambdaのレスポンスの中身は下記にしておくこと

{
     'statusCode': 200,
    'body': json_data
}