PostGISでGeoなSELECT

PostGISの環境は整ったので、
OSMのデータを使ってPostGISのGeoなSQLについて、学んでいきたいと思います！

今日はGeoなSELECTです

まずはフツーにSELECTしてみると、、、

1
2
3
4
5
japan=# select way from planet_osm_point  where name = '東京';
                        way
----------------------------------------------------
 010100002031BF0D00A4703D6A13AD6D411F85EBA10E3D5041
(1 row)

こんな感じでわけがわかりません、、、

ST_SRID

ジオメトリのSRIDを確認
テーブル定義を確認すればわかるんですけどね・・

1
2
3
4
5
japan=# select ST_SRID(way) from planet_osm_point  where name = '東京';
 st_srid
---------
  900913
(1 row)

ST_AsText

GeoなデータをTextで返してくれます。

1
2
3
4
5
japan=#  select ST_Astext(way) from planet_osm_point  where name = '東京';
           st_astext
-------------------------------
 POINT(15558811.32 4256826.53)
(1 row)

私のデータのSRIDは900913、メルカトル図法なので、XY座標で返ってきます。

ST_Transform

空間座標系を変換してくれます

1
2
3
4
5
japan=#  select ST_Astext(ST_Transform(way,4326)) from planet_osm_point  where name = '東京';
                st_astext
------------------------------------------
 POINT(139.767180114878 35.6810784606139)
(1 row)

地理座標系を使っている4326に変換したので、緯度経度になりましたね！

ST_Simplify

ジオメトリを「簡略化」したものを返します
たとえばクネクネの道を高縮尺のときは簡略化したい、、、なんていうときに使えます。

都庁を簡略化してみましょう

1
2
3
4
5
6
7
8
9
10
11
12
13
japan=# select name ,ST_Astext(way) from planet_osm_polygon where name like '東 京都庁%';
                                name                                 |             st_astext
---------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 東京都庁舎 第二本庁舎 (Tokyo Metropolitan Government Building No.2) | POLYGON((15550413.95 4257817.44,15550418.97 4257818.58,15550417.58 4257827.96,15550426.27 4257829.92,15550424.56 4257841.44,15550448.89 4257846.93,15550450.66 4257835.04,15550459.39 4257837.01,15550461.36 4257823.76,15550471.75 4257826.1,15550475.47 4257801.06,15550467.95 4257799.36,15550468.74 4257794.06,15550476.9 4257795.91,15550481.11 4257767.62,15550474.16 4257766.05,15550474.97 4257760.64,15550482.44257762.31,15550486.53 4257734.52,15550479.1 4257732.85,15550479.98 4257726.9,15550474.89 4257725.75,15550476.11 4257717.52,15550447.25 4257711.03,15550445.94 4257719.76,15550436.73 4257717.67,15550435.77 4257724.14,15550428.9 4257722.59,15550424.48 4257752.31,15550430.88 4257753.75,15550429.96 4257760.01,15550423.3 4257758.52,15550419.28 4257785.53,15550425.7 4257786.97,15550424.97 4257791.88,15550417.98 4257790.31,15550413.95 4257817.44))
 東京都庁舎 第一本庁舎 (Tokyo Metropolitan Government Building No.1) | POLYGON((15550365.85 4258023.11,15550373.12 4258024.66,15550371.66 4258031.54,15550379.49 4258033.2,15550377.03 4258044.83,15550406.24 4258051.04,15550408.39 4258040.91,15550416.42 4258042.61,15550417.74 4258036.42,15550427.38 4258038.46,15550433.63 4258008.99,15550422.58 4258006.65,15550432.79 4257958.54,15550441.95 4257960.49,15550447.51 4257934.2,15550437.02 4257931.98,15550438.75 4257923.78,15550430.27 4257921.97,15550432.86 4257909.71,15550403.65 4257903.51,15550401.37 4257914.26,15550393.99 4257912.69,15550392.39 4257920.22,15550385.48 4257918.75,15550379.91 4257945.03,15550388.16 4257946.78,15550377.96 4257994.9,15550372.1 4257993.66,15550365.85 4258023.11))
(2 rows)
japan=# select name ,ST_Astext(ST_Simplify(way,10)) from planet_osm_polygon where name like '東京都庁%';
                                name                                 |                          st_astext
---------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 東京都庁舎 第二本庁舎 (Tokyo Metropolitan Government Building No.2) | POLYGON((15550413.95 4257817.44,15550424.56 4257841.44,15550448.89 4257846.93,15550471.75 4257826.1,15550486.53 4257734.52,15550476.11 4257717.52,15550428.9 4257722.59,15550413.95 4257817.44))
 東京都庁舎 第一本庁舎 (Tokyo Metropolitan Government Building No.1) | POLYGON((15550365.85 4258023.11,15550377.03 4258044.83,15550427.38 4258038.46,15550433.63 4258008.99,15550422.58 4258006.65,15550447.51 4257934.2,15550432.86 4257909.71,15550403.65 4257903.51,15550385.48 4257918.75,15550365.85 4258023.11))
(2 rows)

簡略化して、ポリゴンの頂点の数がぐっと減りました。

今回10を指定した二つ目の引数ですが、簡略化を行う場合のしきい値で、この値が大きければ大きいほど簡略化されます。

ST_Area

ポリゴンの面積を返します。

1
2
3
4
5
japan=# select name,ST_Area(way) from planet_osm_polygon where name like '台東%' and boundary = 'administrative';
      name      |     st_area
----------------+------------------
 台東区 (Taitō) | 15340131.5390491
(1 row)

単位がSRIDに依存するらしいです。（900913が何の単位になるのかよくわからない。。。）
Geography型に変換するとメートル単位になるらしいのでやってみます。

1
2
3
4
5
japan=# select name,ST_Area(Geography( ST_Transform( way,4326 ))) from planet_osm_polygon where name like '台東%' and boundary = 'administrative';
      name      |     st_area
----------------+------------------
 台東区 (Taitō) | 10090876.0546307
(1 row)

台頭区の面積をググったところ、10.08 km² だったので、だいたい合ってますかね

ST_Length

LINEの長さを返します

1
2
3
4
5
6
7
8
9
10
11
japan=#  select name,ST_Length( way ) from planet_osm_line where name like '京王電鉄京王線%';
                  name                   |    st_length
-----------------------------------------+------------------
 京王電鉄京王線 (Keio Railway Keio Line) | 1186.87117791301
 京王電鉄京王線 (Keio Railway Keio Line) | 370.687813260911
 京王電鉄京王線 (Keio Railway Keio Line) | 97.0250626921177
 京王電鉄京王線 (Keio Railway Keio Line) | 1021.35274896948
 京王電鉄京王線 (Keio Railway Keio Line) |  617.29770580598
 京王電鉄京王線 (Keio Railway Keio Line) | 87.7408468154708
・・・・・・・

同じ電鉄でもひとつの地物ではないので、たくさん結果が返ってきました。

これもメートルにしてみましょう

1
2
3
4
5
6
7
8
9
10
japan=# select name,ST_Length( Geography( ST_Transform( way,4326 ))) from planet_osm_line where name like '京王電鉄京王線%';
                  name                   |    st_length
-----------------------------------------+------------------
 京王電鉄京王線 (Keio Railway Keio Line) | 961.734727315897
 京王電鉄京王線 (Keio Railway Keio Line) | 301.046955913747
 京王電鉄京王線 (Keio Railway Keio Line) | 78.8656422237801
 京王電鉄京王線 (Keio Railway Keio Line) |  830.57417351145
 京王電鉄京王線 (Keio Railway Keio Line) | 501.064401755556
 京王電鉄京王線 (Keio Railway Keio Line) | 71.3841606965213
 ・・・・・・・

ST_distance

2つのジオメトリの距離を返します。

1
2
3
4
5
japan=# select ST_distance(kudan.way , tokyo.way) from planet_osm_point kudan , planet_osm_point tokyo where kudan.name = '九段下' and kudan.railway = 'station' and tokyo.name = '東京' and tokyo.railway = 'station' ;
   st_distance
------------------
 2640.28394709686
(1 row)

緯度経度の空間参照系にして、第3引数をfalseにするとメートルになるらしいのでやってみます。

1
2
3
4
5
japan=# select ST_distance(ST_Transform(kudan.way,4326) , ST_Transform(tokyo.way,4326),false) from planet_osm_point kudan , planet_osm_point tokyo where kudan.name = '九段下' and kudan.railway = 'station' and tokyo.name = '東京' and tokyo.railway = 'station' ;
  st_distance
----------------
 2142.050032445
(1 row)

九段下と東京の距離は2kmくらいなのでだいたい合ってます。

他にもPostGISの機能はたくさんあるので、これから順次勉強していきます！！！

今日はここまで！