PostGIS の変更点   

• 追加された行はこの色です。
• 削除された行はこの色です。
• PostGIS へ行く。

---

#author("2022-01-31T01:19:40+00:00","default:pentacle","pentacle")
* 概要 [#id62ebd3]
 PostGIS は postgres のプラグインで、各種地理的な計算をするライブラリ。
 たとえば緯度経度ベースの2点間の距離を求めたり、ポリラインで、移動距離を求めるなど。

* INSTALL for linux[#u13ec0d3]
postgres が動いていたら停止
 /etc/init.d/postgresql[-9.1] stop

投射等の演算ライブラリ proj
 cd /usr/local/src 
 wget http://download.osgeo.org/proj/proj-4.8.0.tar.gz
 tar xzfp proj-4.8.0.tar.gz
 cd proj-4.8.0
 ./configure
 make 
 make install
  

geocoding 等のライブラリ geos
 cd /usr/local/src
 wget http://download.osgeo.org/geos/geos-3.4.2.tar.bz2
 tar xjfp  geos-3.4.2.tar.bz2
 cd geos-3.4.2
 ./configure
 make 
 make install
  

KML 等のために使用する XML ライブラリ
 yum -y install libxml2-devel

GISラスターデータフォーマット用ライブラリ
 cd /usr/local/src
 wget http://download.osgeo.org/gdal/gdal-1.9.2.tar.gz
 tar xzfp gdal-1.9.2.tar.gz
 cd gdal-1.9.2
 ./configure
 make
 make install
  

postgis 本体
 cd /usr/local/src
 wget http://download.osgeo.org/postgis/source/postgis-2.1.0.tar.gz
 tar xzpf postgis-2.1.0.tar.gz
 cd postgis-2.1.0
 #export PATH=/usr/pgsql-9.3/bin/:$PATH
 ./configure
 make
 make install
  

lib の設定~
/etc/ld.so.conf
 include ld.so.conf.d/*.conf
 /usr/local/lib
↑1行追記~
~
システムに反映
 ldconfig

 /etc/init.d/postgresql[-9.1] start

 su - postgres

postgis用のスケルトンＤＢを生成
 createdb template_postgis
 createlang plpgsql template_postgis
 psql -d template_postgis -f /usr/share/pgsql/contrib/postgis-2.1/postgis.sql 
 psql -d template_postgis -f /usr/share/pgsql/contrib/postgis-2.1/spatial_ref_sys.sql 
 psql -d template_postgis -f /usr/share/pgsql/contrib/postgis-2.1/rtpostgis.sql 
 psql -d template_postgis -f /usr/share/pgsql/contrib/postgis-2.1/topology.sql

for rails
 gem install GeoRuby
 gem install spacial_adapter

* install for windows [#ob4ca849]
普通に postgresの installer から入れられる~
template の概念はなく、db を作成してから、
\c database
 create extension postgis;
を実行する

----
* 動作確認 [#k2080313]
postgis のテンプレートを使った DB (DB名: SampleDB) の作成
 createdb -U postgres -E UTF-8 -T template_postgis SampleDB

 psql -U postgres -d SampleDB

東京駅から新大阪駅までの2点間距離を求める
 SELECT ST_Distance(
                ST_GeomFromText('POINT(139.76608399999998 35.681382)',4326)::Geography,
                ST_GeomFromText('POINT(135.50010900000007 34.73348 )',4326)::Geography
	);
  

結果
     st_distance
 ------------------
  402392.122486525
402.392km

* 使い方 [#d544f662]
postGIS が使えるコンソールを開く(DBを指定して開くのみ)
 psql -U postgres -d SampleDB

テーブル(sample)を作成
 CREATE TABLE sample (id SERIAL);

postGISコマンドは select文経由で発行(sample テーブルに geometry型のカラムを追加)~
(4326は、世界測地系(WGS84) のＩＤ）
 SELECT AddGeometryColumn('sample', 'point', 4326, 'POINT', 2);

テーブルの型の確認
 \d sample
                              テーブル "public.sample"
  カラム |          型          |                       修飾語
 --------+----------------------+-----------------------------------------------------
  id     | integer              | not null default nextval('sample_id_seq'::regclass)
  point  | geometry(Point,4326) |

東京駅と新大阪駅を追加
 insert into sample (point) values (ST_GeomFromText('POINT(139.76608399999998 35.681382)',4326));
 insert into sample (point) values (ST_GeomFromText('POINT(135.50010900000007 34.73348)',4326));

SQL 文で距離を算出
 select ST_Distance( (select point from sample where id=1)::geography , (select point from sample where id=2)::geography );

geometry 型から 緯度経度を表示
 select ST_X(point),ST_Y(point) from sample where id=1;

----
* 測地系変換 [#w5e12c91]
postgis の変換パラメータは若干間違っているので下記を実行 
 create extension postgis;
 update spatial_ref_sys set proj4text=replace(proj4text,'-148,507,685,0,0,0,0','-146.414,507.337,680.507,0,0,0,0') where srtext like '%-148,507%';
 update spatial_ref_sys set srtext=replace(srtext,'-148,507,685,0,0,0,0','-146.414,507.337,680.507,0,0,0,0') where srtext like '%-148,507%';

 SELECT ST_AsText(ST_Transform(ST_GeomFromText('POINT(140 40)', 4301), 4326));

結果
 POINT(139.996550822303 40.0027470831033)

----
* 平面直角座標 => 緯度経度変換 [#u5458df8]
|x    |double|基準点からの距離(m)|
|y    |double|基準点からの距離(m)|
|zone |int| 日本の座標系(1-19)|
|geom |geometry||

x,y,zone を入れたらgeom を補完するfuntion
 CREATE FUNCTION latlng_complete() RETURNS TRIGGER AS $$ BEGIN 
      NEW.geom := ST_Transform(ST_SetSRID( ST_MakePoint(NEW.y, NEW.x),2442+NEW.zone),4326);
    RETURN NEW; 
 END; $$ LANGUAGE plpgsql;
 CREATE TRIGGER trigger_latlng_complete BEFORE INSERT OR UPDATE ON point FOR EACH ROW EXECUTE PROCEDURE latlng_complete();

例：東京駅 (9系)
 insert into point values (-35365.116 ,-6012.105 ,9);


* 日本の県境を作成 [#s2c6c1d1]

国土数値情報から全国分をDL
 http://nlftp.mlit.go.jp/ksj/gml/datalist/KsjTmplt-N03.html

 unzip N03-190101_GML.zip
 shp2pgsql -W cp932 -D -I -s 4612 N03-19_190101.shp t1 > N03-19_190101.sql
 psql -U postgres prefecture  < N03-19_190101.sql

*** postgres 準備 [#r5809c6c]
 create database prefecture;
 \c prefecture;
 create extension postgis;


* sql snipet [#x99e355d]

まず緯度経度のデータを入れる
 create table points (lat double precision,lng double precision );
 \copy points from 'hoge.csv' with csv

次に geography型のフィールドを追加して計算
 alter table points add column geog geography(POINT,4326);
 update points set geog=ST_GeographyFromTEXT('SRID=4326;POINT('||lng||' '||lat||')');

必要に応じて index を付けておく

50m 以内抽出
 where ST_GeographyFromText( geog, 'SRID=4326;POINT(139.0 35.9)', 50)

| 新規 | 編集 |