PostGISで緯度経度付きデータを扱う

緯度経度付きデータを入れた後にGeometory型にするときのSQLでよく忘れるのでメモ。

SELECT AddGeometryColumn('schema','table','geom',4326, 'POINT', 2);
もしくは
ALTER TABLE schema.table ADD geom GEOMETRY(POINT,4326);
その後Geometory型のデータを作るため
UPDATE schema.table SET geom=ST_GeometryFromText('SRID=4326;POINT('||lng||' '||lat||')')
でGeometory型のデータが作られる。（latとlngはカラム名）

あとは
SELECT * FROM schema.table
WHERE ST_DWithin(geom, ST_GeomFromText('POINT(135 35)', 4326), 500, true)
LIMIT 100;
みたいな感じで緯度経度（35,135）の位置で500m以内のデータを取得できる。

indexを作るには
CREATE INDEX indexname ON schema.table USING GIST (geom);

あと本題と違うけどPostGISをMacでPythonから使うのにPsycopg2を使うためのメモ。
インストールはpipでできるが少しだけはまったので
pip install psycopg2
でインストールできるとあるが、PATHにPostgresの設定をしてないと
Error: pg_config executable not found.
というエラーでインストールできない。PATH設定は
$ export PATH=/usr/lib/postgresql/X.Y/bin/:$PATH
source ~/.bash_profile