PostgreSQL入門

テーブルの作成・変更

テーブルの作成： 別のテーブル定義をインデックスも含めてまるごとコピー

CREATE TABLE my_osm_areas (LIKE planet_osm_polygon INCLUDING ALL);

全カラムのコピー

INSERT INTO my_osm_areas SELECT * FROM planet_osm_polygon WHERE (way_area > 100000 and (COALESCE("natural", landuse, leisure, man_made, tourism, water, waterway) is not null or boundary in ('national_park', 'protected_area'))) or boundary='administrative' or place='island';

列の追加

列を追加するには、次のようにする。

ALTER TABLE planet_osm_line ADD COLUMN way_id SMALLINT;
ALTER TABLE planet_osm_line ADD COLUMN man_made TEXT;

数値と文字列

text to_hex(number int or bigint)

numberを、同等の16進数表現に変換する。 例 to_hex(10513152) -> 'a06b00'

16進表記文字列をintに変換

例 x'a06b00'::int -> 10513152

比較演算子

expression = null とか expression != null としてはならない。expression IS NULL、expression IS NOT NULL とする。 あるいは、これと同等の非標準構文 expression ISNULL、exprssion NOTNULL が使える。

パターンマッチング

similar to

• | は、二者択一（2つの選択肢のうちいずれか）を意味します。
• * は、直前の項目の0回以上の繰り返しを意味します。
• + は、直前の項目の1回以上の繰り返しを意味します。
• ? は、直前の項目の0回もしくは1回の繰り返しを意味します。
• {m} は、直前の項目の正確なm回の繰り返しを意味します。
• {m,}は、直前の項目のm回以上の繰り返しを意味します。
• {m,n}は、直前の項目のm回以上かつn回以下の繰り返しを意味します。
• 丸括弧 ( ) は、項目を1つの論理項目にグループ化することができます。
• 大括弧式 [...] は、POSIX正規表現と同様に文字クラスを指定します。

SIMILAR TOではピリオド（.）はメタ文字ではない。 実数かどうかの判定は次のようにする。

select '3.49' similar to '[0-9]+.?[0-9]*';      -- true

~

~ 演算子は SIMILAR TO演算子と同様のことができるが、細かい仕様がことなる。 ピリオド（.）はメタ文字のため、小数点として使うときは \. とする。 \d は [0-9] を意味する。^ は文字列の始まり、$ は文字列の終わりを表す。

 CASE WHEN tags->'ele' ~ '^-?\d{1,4}(\.\d+)?$' THEN (tags->'ele')::NUMERIC
      ELSE NULL
 END

文字列置換

文字列は replace('文字列','置換前文字列','置換後文字列') で置き換える。 下のupdate文は '12-3号棟' などから末尾の '号棟' を削除する。

update my_osmdata set name = replace(name,'号棟','') where key='building' and name like '%号棟';

外部結合

外部結合は、一方の条件に対するデータが存在しなかった場合でも片方のデータを結果セ ットに含める指定ができる[3]。

[test2.m]                  [testm]
   key    |  code1         key  | data1 | data2 | data3
----------+----------    -------+-------+-------+-------
 abc01    | a001          a001  |     1 |     2 |     3
 abc02    | a011          a011  |     1 |     2 |     3
 abc03    | z999          b002  |    10 |    20 |    30
                          c003  |   100 |   200 |   300

select test2m.key, code1, data1, data2, data3
from test2m left join testm on test2m.code1 = testm.key;

   key    |  code1   | data1 | data2 | data3
----------+----------+-------+-------+-------  
 abc01    | a001     |     1 |     2 |     3
 abc02    | a011     |     1 |     2 |     3
 abc03    | z999     |       |       |

出現頻度

select key, count(key) as key_counts from planet_osm_point 
 group by key order by key_counts desc;

select key, count(key) as key_counts from
 (select key from planet_osm_point where key is not null
  union all
  select key from planet_osm_polygon  where key is not null) as keys
group by key order by key_counts desc;

COPYコマンドによるCSV、TSV出力

カンマ区切り

COPY (テーブル名またはSQL文) TO '(ファイルパス)' WITH CSV DELIMITER ',';
COPY (テーブル名またはSQL文) TO '(ファイルパス)' WITH CSV DELIMITER ',' FORCE QUOTE *;

tsv出力

COPY (テーブル名またはSQL文) TO '(ファイルパス)' WITH CSV DELIMITER E'\t';

テキスト型カラムの文字列にタブが含まれないことが前提となるが 読み込み処理がカンマ区切りより簡単になる。 文字列にカンマが含まれないことが保証されるなら、CSV出力でもよい。

psqlコマンドによるCSV、TSV出力

psql データベース名 -c "SQL文" -A -F > CSVファイル名
psql データベース名 -c "SQL文" -A -F $'\t' > TSVファイル名

hatada@hatada:~$ su - postgres
パスワード: 
postgres@hatada:~$ psql osm -c "select osm_id,shop,name,way from japan_point where shop is not null limit 10" -A -F $'\t' > /media/sf_gis/test01.tsv

osm_id|shop|name|way
1864231226|supermarket|小祝商店|0101000020110F0000BF5309FDFA306E412A727F8FAEEB4741
1864231229|supermarket|小笠原生協|0101000020110F0000B05FD6C5FD306E41F657DF67A0EB4741
5403370661|ticket||0101000020110F0000B6E87580ADAC6D415B092C0939294D41
4840871622|supermarket|十一屋酒店|0101000020110F0000235B3DA9CEAC6D41BD8EAEFC4A2E4D41
5403370671|car_repair|青ヶ島整備工場|0101000020110F00003C35306ACEAC6D41C1E51730582E4D41
4840875527|car_repair|池之沢自動車修理工場|0101000020110F0000F505698929AD6D4191246537542C4D41
5126400264|supermarket|八丈ストア ミニミニ店|0101000020110F00006A962941A5AF6D41A9F45AB123C94D41
4840937221|supermarket|富次郎商店|0101000020110F0000220C752EC9AE6D41FBC601B337CA4D41
5103426811|farm|えこ・あぐりまーと|0101000020110F00002DC2E932BCAF6D419ADE459B0FCB4D41
5126406767|sports|菊池釣具店|0101000020110F0000A2C8B75D58AD6D4118C38A4609D24D41
(10 rows)

PostGISのgeometry型カラムは外部プログラムで扱うためにはテキスト型に変換した方がいい。 osm2pgsqlでインポートしたOSMデータベースの場合、ST_AsText(ST_Transform(way,4326)) AS wayとすればよい。

psql osm -c "select osm_id,shop,name,ST_AsText(ST_Transform(way,4326)) AS way from japan_point where shop is not null limit 10" -A -F $'\t' > /media/sf_gis/test01.tsv

osm_id|shop|name|way
1864231226|supermarket|小祝商店|POINT(142.1938943 27.0948349000474)
1864231229|supermarket|小笠原生協|POINT(142.1940944 27.0946085000474)
5403370661|ticket||POINT(139.7598561 32.4474825995951)
4840871622|supermarket|十一屋酒店|POINT(139.7622391 32.4671592995946)
5403370671|car_repair|青ヶ島整備工場|POINT(139.7622214 32.4673593995946)
4840875527|car_repair|池之沢自動車修理工場|POINT(139.7687699 32.4595378995948)
5126400264|supermarket|八丈ストア ミニミニ店|POINT(139.8144559 33.0660281995875)
4840937221|supermarket|富次郎商店|POINT(139.7986403 33.0701837995875)
5103426811|farm|えこ・あぐりまーと|POINT(139.8161048 33.0734343995875)
5126406767|sports|菊池釣具店|POINT(139.7721353 33.1003139995874)
(10 rows)

実際のosmデータの緯度・経度の有効桁数は小数点以下７桁である。 10⁷を掛けて、四捨五入して整数化すれば 32ビット整数で表すことができる。

参考文献