Tableau 2019.2 新機能紹介：PostgreSQL+PostGIS の空間データが使えるようになりました #tableau
日本時間2019年05月22日(水)、Tableauの最新バージョンとなる「v2019.2」がリリースされました。
Ready to have your day made? Tableau 2019.2 is here! ?? With vector maps, parameter actions, a new Tableau Server browsing experience and more: https://t.co/WUL0IUcFbo pic.twitter.com/2qEouiumEc
— Tableau Software (@tableau) 2019年5月22日
当エントリでは、バージョン2019.2の新機能の中から「PostgreSQL+PostGISの空間データに対応」についてご紹介したいと思います。
PostgreSQL+PostGISの空間データに対応
Tableauでは従来SQL Serverの空間データに対応するなどしてきていましたが、今回のv2019.2のタイミングでPostGIS拡張機能を持つデータベース(PostgreSQLやPivotal Greenplum Database)の空間データにも対応するようになりました。
関連公式ヘルプは以下となります。当エントリでは『PostgreSQL+PostGIS』の環境を用意してTableauから繋ぐ検証をしてみたいと思います。
PostGISデータを投入するPostgreSQL環境を準備
(Amazon RDS/PostgreSQL)
まずはじめに、Tableauで扱うPostgreSQL+PostGISのデータを入れる『器』を用意します。Amazon RDS(PostgreSQL)では既にPostGISに対応しているので、検証用に外部からのアクセスが可能な最新バージョンのRDS(PostgreSQL)を起動しました。
データベースへの接続を確認後、PostGISデータを取り込めるように関連拡張を有効化しておきます。
$ psql -h cmpostgis.xxxxxxxxxx.us-east-1.rds.amazonaws.com -U xxxxxxxx -d cmpostgisdb -p 5432
Password for user root:
psql (11.2)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
cmpostgisdb=> \timing
Timing is on.
cmpostgisdb=> CREATE EXTENSION postgis;
CREATE EXTENSION
Time: 2273.828 ms (00:02.274)
cmpostgisdb=> SELECT postgis_version();
postgis_version
---------------------------------------
2.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
(1 row)
Time: 286.277 ms
投入データの準備
TableauからPostgreSQL+PostGISに投入するデータも必要です。今回は下記のサイトから『関東地方』に関するデータ(kanto-latest.osm.pbf, 200MB超)を入手しました。
『osm2pgsql』をMacにインストール
PostgreSQLへのデータ投入手段は幾つかあるようですが、コマンド『osm2pgsql』を使うのが分かりやすい＆進めやすそうだったのでこれで試してみたいと思います。今回はMacでの作業を想定していましたので下記ページの内容を参考にコマンドインストールを進めます。
下記Rubyのコマンドを実行。
$ ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)" < /dev/null 2> /dev/null
そしてbrewでosm2pgsqlをインストール。
$ brew install osm2pgsql $ osm2pgsql --version osm2pgsql version 0.96.0 (64 bit id space)
『osm2pgsql』コマンドによるデータのインポート
下記ページの「データのインポート」を参考に、osm2pgsqlコマンドを使って用意したDBにデータを取り込みます。
取り込みの際には別途"スタイルファイル"なるものが必要となるようです。正直この辺仕組みとか良く分かってなかったので、今回は下記リポジトリのファイルをそのまま使ってみることにしました。(後から気付いたけど https://learnosm.org/files/default.style からも入手出来た模様)
コマンド実行内容は以下の通り。接続先情報などは適宜読み替える形で。DB接続パスワード入力も必要となるため、-Wオプションも追加しています。
$ osm2pgsql -c -d cmpostgisdb -U xxxxxxxxx -H cmpostgis.xxxxxxxxxx.us-east-1.rds.amazonaws.com -S /Users/xxxxxxxx/Desktop/default.style /Users/xxxxxxxx/Desktop/kanto-latest.osm.pbf -W osm2pgsql version 0.96.0 (64 bit id space) Password: Using built-in tag processing pipeline Using projection SRS 3857 (Spherical Mercator) Setting up table: planet_osm_point Setting up table: planet_osm_line Setting up table: planet_osm_polygon Setting up table: planet_osm_roads Allocating memory for dense node cache Allocating dense node cache in one big chunk Allocating memory for sparse node cache Sharing dense sparse Node-cache: cache=800MB, maxblocks=12800*65536, allocation method=3 Reading in file: /Users/xxxxxxxx/Desktop/kanto-latest.osm.pbf Using PBF parser. Processing: Node(26371k 497.6k/s) Way(3821k 19.90k/s) Relation(16980 246.09/s) parse time: 314s Node stats: total(26371722), max(6492466079) in 53s Way stats: total(3821109), max(691761644) in 192s Relation stats: total(17163), max(9613135) in 69s : : : Completed planet_osm_line Copying planet_osm_polygon to cluster by geometry finished Creating geometry index on planet_osm_polygon Creating indexes on planet_osm_polygon finished All indexes on planet_osm_polygon created in 310s Completed planet_osm_polygon Osm2pgsql took 778s overall
データ投入完了後の状況はこちら。投入時間及びデータ量については投入ファイルに拠りますのでご参考程度に。
cmpostgisdb=> SELECT * FROM pg_tables WHERE schemaname = 'public';
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+--------------------+------------+------------+------------+----------+-------------+-------------
public | spatial_ref_sys | rdsadmin | | t | f | f | f
public | planet_osm_point | root | | t | f | f | f
public | planet_osm_line | root | | t | f | f | f
public | planet_osm_polygon | root | | t | f | f | f
public | planet_osm_roads | root | | t | f | f | f
(5 rows)
Time: 189.397 ms
cmpostgisdb=> SELECT postgis_version();
postgis_version
---------------------------------------
2.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
(1 row)
Time: 286.277 ms
cmpostgisdb=>
cmpostgisdb=> SELECT COUNT(*) FROM public.spatial_ref_sys;
count
-------
5757
(1 row)
Time: 188.486 ms
cmpostgisdb=> SELECT COUNT(*) FROM public.planet_osm_point;
count
--------
504310
(1 row)
Time: 336.869 ms
cmpostgisdb=> SELECT COUNT(*) FROM public.planet_osm_line;
count
---------
1742735
(1 row)
Time: 5640.578 ms (00:05.641)
cmpostgisdb=> SELECT COUNT(*) FROM public.planet_osm_polygon;
count
---------
2032352
(1 row)
Time: 6551.652 ms (00:06.552)
cmpostgisdb=> SELECT COUNT(*) FROM public.planet_osm_roads;
count
-------
93801
(1 row)
Time: 197.766 ms
cmpostgisdb=>
投入データの微調整
上記投入データの状況で「あとはTableauから突けば行けるぜ」と思っていたのですが試してみた結果どうも上手く行ってない模様。データ自体の件数はあるしアクセスは出来てるんだけどなぁ...と思って色々確認してみたところ、どうやら投入データのテーブル定義が、想定していたものと異なっていたために空間情報として認識されていなかったようです。前述の「正直この辺仕組みとか良く分かってなかった」部分ですねw
現行、Tableauで扱える空間情報データのSRID(空間参照系(Spatial Reference System, SRS)の識別コード)はPostgreSQLではWGS84（EPSG:4326/地理座標系）のみとなっています。
「空間参照系」に関する情報は下記のエントリが分かりやすいです。
上記投入作業で取り込んだデータ・テーブル(今回は点の情報を可視化まで持って行きたかったのでpublic.planet_osm_pointテーブルを対象としました)の対象項目(way)のSRIDを確認してみたところ「3857」という値を得る事が出来ました。
cmpostgisdb=> SELECT way, ST_SRID(way) FROM public.planet_osm_point LIMIT 10;
way | st_srid
----------------------------------------------------+---------
0101000020110F000095E96BD23DB66B41ED938C55E3314C41 | 3857
0101000020110F00004C34119D18B96B41ED1C367A074B4C41 | 3857
0101000020110F0000EC7BE4978B866B41D4EB1CA362A84D41 | 3857
0101000020110F00001503B48BFA8C6B4103D83D2FB1894D41 | 3857
0101000020110F0000F723E65D1B8B6B411F56E42576E24D41 | 3857
0101000020110F000096E37381E7EB6B418F00413CF4054E41 | 3857
0101000020110F00001E586BA28FF06B413A03FB55DCF74D41 | 3857
0101000020110F0000669129828FFF6B41514D12C7E3F84D41 | 3857
0101000020110F00001C5B6D33A0016C41E39EBB0183FD4D41 | 3857
0101000020110F0000CCC749B4B0076C41C4E49E31F8104E41 | 3857
(10 rows)
この「3857」というSRID、測地系としてはWGS84なのですが座標系が「球面(半径6378137m)メルカトル図法」となっていました。という事で投入時のデータの在り方が想定とズレてました...。
対象テーブルの定義を確認してみます。確かに定義としても3857が指定されている形になってます。
cmpostgisdb=> \d public.planet_osm_point;
Table "public.planet_osm_point"
Column | Type | Collation | Nullable | Default
--------------------+----------------------+-----------+----------+---------
osm_id | bigint | | |
access | text | | |
addr:housename | text | | |
addr:housenumber | text | | |
addr:interpolation | text | | |
admin_level | text | | |
aerialway | text | | |
aeroway | text | | |
amenity | text | | |
area | text | | |
barrier | text | | |
bicycle | text | | |
brand | text | | |
bridge | text | | |
boundary | text | | |
building | text | | |
capital | text | | |
construction | text | | |
covered | text | | |
culvert | text | | |
cutting | text | | |
denomination | text | | |
disused | text | | |
ele | text | | |
embankment | text | | |
foot | text | | |
generator:source | text | | |
harbour | text | | |
highway | text | | |
historic | text | | |
horse | text | | |
intermittent | text | | |
junction | text | | |
landuse | text | | |
layer | text | | |
leisure | text | | |
lock | text | | |
man_made | text | | |
military | text | | |
motorcar | text | | |
name | text | | |
natural | text | | |
office | text | | |
oneway | text | | |
operator | text | | |
place | text | | |
population | text | | |
power | text | | |
power_source | text | | |
public_transport | text | | |
railway | text | | |
ref | text | | |
religion | text | | |
route | text | | |
service | text | | |
shop | text | | |
sport | text | | |
surface | text | | |
toll | text | | |
tourism | text | | |
tower:type | text | | |
tunnel | text | | |
water | text | | |
waterway | text | | |
wetland | text | | |
width | text | | |
wood | text | | |
z_order | integer | | |
way | geometry(Point,3857) | | |
Indexes:
"planet_osm_point_index" gist (way) WITH (fillfactor='100')
cmpostgisdb=>
今回は空間情報の確認だけ行いたかった＆投入データが多く、また取り込んでいるDBのスペックが貧弱だったので処理に時間が掛かったため、利用する項目を絞り、更に件数も絞る形で別テーブルを用意してそちらの内容で検証する事にしました。
テーブル作成。扱う情報は点(POINT)なのでそこの定義はそのまま、SRIDの指定を本来在るべきだった4326で指定。
DROP TABLE IF EXISTS public.planet_osm_point_cities; CREATE TABLE IF NOT EXISTS public.planet_osm_point_cities ( osm_id BIGINT, name TEXT, place TEXT, population BIGINT, way GEOMETRY(POINT, 4326) );
データ投入。座標変換関数:ST_Transformを使い、データの内容を変換した上で対象データも都市名を持つデータに制限しています。
INSERT INTO public.planet_osm_point_cities ( SELECT osm_id, name, place, TO_NUMBER(population, '99999999') as population, ST_Transform(way, 4326) as geo_point FROM public.planet_osm_point WHERE place = 'city' );
投入データを確認。
cmpostgisdb=> SELECT COUNT(*) FROM public.planet_osm_point_cities; count ------- 167 (1 row) $ SELECT osm_id, name, place, population, way, ST_SRID(way) FROM public.planet_osm_point_cities WHERE population IS NOT NULL ORDER BY population DESC LIMIT 20; osm_id | name | place | population | way | st_srid ------------+------------+-------+------------+----------------------------------------------------+--------- 265018692 | 東京都 | city | 13613660 | 0101000020E61000002EC95C744D786140D4D62D4267D74140 | 4326 1973500311 | 横浜市 | city | 3708122 | 0101000020E610000075E4486760746140AC540F77F5B84140 | 4326 1933981790 | 川崎市 | city | 1509887 | 0101000020E61000000E99F221A87661406DEB2951F6C24140 | 4326 243993095 | さいたま市 | city | 1182115 | 0101000020E6100000B86CBE5BAA746140D35CDF394AEE4140 | 4326 3675848658 | 千葉市 | city | 975140 | 0101000020E6100000D5592DB0C783614005610CFD13CE4140 | 4326 1111454647 | 世田谷区 | city | 860071 | 0101000020E6100000116BF1290075614014661246B3D24140 | 4326 622868305 | 練馬区 | city | 733150 | 0101000020E6100000F0F96184707461409A32AE42CADF4140 | 4326 1111454606 | 大田区 | city | 728349 | 0101000020E61000004242942FE87661400D812399D5C74140 | 4326 2115186710 | 相模原市 | city | 722157 | 0101000020E6100000637AC2128F67614058D1CB4065C84140 | 4326 721783580 | 江戸川区 | city | 691417 | 0101000020E6100000E19A3BFADF7B6140332A41D0D1D64140 | 4326 1111454619 | 足立区 | city | 676761 | 0101000020E610000066DCD44073796140A2C6406150E44140 | 4326 2098183459 | 船橋市 | city | 633263 | 0101000020E610000040D3122BA37F61401C856E8099D94140 | 4326 714895390 | 板橋区 | city | 575432 | 0101000020E6100000B617D176CC7561406219291E17E34140 | 4326 864262454 | 川口市 | city | 560000 | 0101000020E61000006D7B16DF2B776140B1DFCCBC66E74140 | 4326 622423997 | 八王子市 | city | 555517 | 0101000020E610000046EEE9EA0E69614080AE489D80D44140 | 4326 718383251 | 杉並区 | city | 540180 | 0101000020E6100000361BD0775C7461404F03BCFB88D94140 | 4326 721916368 | 江東区 | city | 509438 | 0101000020E610000082902C60027A614019656F7A17D34140 | 4326 2098146352 | 市川市 | city | 469603 | 0101000020E610000097033DD4B67D61401A08565F5DDD4140 | 4326 721783585 | 葛飾区 | city | 451483 | 0101000020E61000005FB87361A47B6140038B74C938E04140 | 4326 623797057 | 町田市 | city | 433720 | 0101000020E610000096CCB1BC2B6E61404829E97937C84140 | 4326 (20 rows) Time: 165.688 ms
Tableauからの接続確認
データの準備が整ったので、ようやくTableauからの接続確認に入ります。Tableau Desktop v2019.2を起動後、PostgreSQL接続設定を行い対象テーブルを選択。対象の項目(way)が空間情報として自動認識されており、内容も「POINT」と表示されています。
編集画面にて該当項目を取り込んでみます。市区町村の位置に合わせて情報がプロットされました。
設定を幾つか行ってみた結果が以下となります。
まとめ
という訳で、Tableau 2019.2の新機能「PostgreSQL+PostGISの空間データに対応」に関する内容のご紹介でした。この手の情報は個別に作成するのに手間も掛かりますのでデータベースにある情報をサッと使えるようになるのは嬉しいですね。一度まるっと取り込んでおきつつ、必要なタイミングで必要な粒度・ボリュームで利用出来るような体制を整えておけると可視化・分析業務も捗りそうです。
![[レポート]AWSでデータメッシュアーキテクチャを構築する#ANT336 #reinvent](https://devio2023-media.developers.io/wp-content/uploads/2022/11/eyecatch_reinvent2022_session-report.png)
