postGisでのよく使うQueryのメモ

やりたいこと

• 緯度経度情報(POINT)から、そのポイントの情報を取得
• 範囲を指定すると、その範囲内にあるポイントを取得

環境・条件

環境

• macOS Big Sur 11.2.3
• psql (PostgreSQL) 13.3
• postGis 3.1.2

1.緯度経度情報(POINT)から、そのポイントの情報を取得

使用例

緯度経度情報から、そのポイントの県や市区町村名を取得する場合

条件

• POLYGONのGeography型データを持つテーブルが登録済み(データの取得は扱わない)

SELECT * FROM table_name
WHERE ST_Covers(
    geog, 
    ST_GeogFromText('POINT(lon lat)')
)
ORDER BY column_name desc;

2．範囲を指定すると、その範囲内にあるポイントを取得

使用例

表示中の地図範囲に存在する建物(病院とか学校とか)の一覧を取得する場合

条件

• POINTのGeography型データを持つテーブルが登録済み(データの取得は扱わない)

SELECT * FROM table_name
WHERE ST_Covers(
    ST_GeogFromText('
        POLYGON(
            (lon0 lat0, lon1 lat1, lon1 lat1, lon1 lat1, lon0 lat0)
        )
    '),
    geog
);