Hatena::ブログ(Diary)

meketenの趣味日記

2012-12-09

Raspberry PiでPostgreSQL(PostGIS)を動かして、現在地をしゃべらせてみた。

PostgreSQLのAdvent Calendarに初めて参加させて頂きます。

Hatena Diaryもこの投稿が初めてです。
真面目な投稿が多い中、全く役に立たない上に、OSSの無駄遣いと怒られそうですが。。頑張ります。

概要

Raspberry PiPostgreSQL(PostGIS)をインストールし、国土交通省が公開している位置参照情報を取り込みました。
位置情報(緯度・経度)から現住所をGISっぽく取得し、OpenJTalkを使って喋らせてみました。
D
喋ってる場所は緯度:35.448575 経度139.642403の場所(神奈川県庁付近)です。
Google マップ

Raspberry Pi(ラズベリーパイ)のご紹介

Raspberry Piは名刺サイズのLinuxが動くシングルボードARMコンピュータです。
http://ja.wikipedia.org/wiki/Raspberry_Pi
http://www.raspberrypi.org/
CPUは700MHz(ARM)、メモリは512Mbyte、ストレージSDカードを使用します。
$35で購入する事が出来るため、元は教育目的で開発されたものですが、世界中のGeekに大人気(?)な大人のオモチャです。

OSはRaspbianというRaspberry Pi最適化されたDebianベースのLinuxディストリビューションを利用します。

PostgreSQL(とPostGIS)のインストール

詳細については後ほど別の日記に投稿致します。
PostgreSQLPostGISはそれぞれ最新版のPostgreSQL 9.2.2とPostGIS 2.0.2をソースからインストールしました。
インストールに必要だったパッケージは以下の通りです。
apt-get install libreadline-dev libxml2-dev libgeos-dev libproj-dev libgdal-dev

住所情報の取り込み

国土交通省の位置参照情報ダウンロードサービスから、住所や番地などの情報がダウンロード出来ます。
http://nlftp.mlit.go.jp/isj/
神奈川県全域の平成23年度版(街区:14000-10.0a.zip)を使用しました。
住所情報のCSVを取り込みたいのですが、文字コードcp932なので、iconvを使ってUTF-8に変換します。

postgres@raspberrypi:~/14000-10.0a$ ls -l
合計 43908
-rw-r--r-- 1 postgres postgres 44933280 2月 23 2012 14_2011.csv
-rw-r--r-- 1 postgres postgres 3255 6月 11 19:25 format_2011.html
-rw-r--r-- 1 postgres postgres 17083 1月 26 2012 md_14_2011.xml
postgres@raspberrypi:~/14000-10.0a$ iconv -f cp932 -t UTF-8 14_2011.csv > 14_2011_UTF-8.csv
postgres@raspberrypi:~/14000-10.0a$ head -5 14_2011_UTF-8.csv
"都道府県名","市区町村名","大字・町丁目名","街区符号・地番","座標系番号","X座標","Y座標","緯度","経度","住居表示フラグ","代表フラグ","更新前履歴フラグ","更新後履歴フラグ"
"神奈川県","横浜市鶴見区","安善町一丁目","1","9","-55830.6","-12085.9","35.496691","139.700117","0","1","0","0"
"神奈川県","横浜市鶴見区","安善町一丁目","2","9","-55670.1","-11736.8","35.498142","139.703963","0","1","0","0"
"神奈川県","横浜市鶴見区","安善町一丁目","3","9","-55670.1","-11736.8","35.498142","139.703963","0","1","0","0"
"神奈川県","横浜市鶴見区","安善町一丁目","4","9","-55670.1","-11736.8","35.498142","139.703963","0","1","0","0"
postgres@raspberrypi:~/14000-10.0a$

CSVファイルの各カラムの詳細は展開した同じディレクトリにあるformat_2011.htmlに記載されています。
これをもとに取り込むテーブルのCREATE TABLE文を作ってみました。geomはlat(緯度),lng(経度)からgeometry型に変換して格納します。
後ほど説明します。
UTF-8に変換したCSV(14_2011_UTF-8.csv)をCOPYコマンドを使用して取り込みます。
http://www.postgresql.jp/document/9.2/html/sql-copy.html

postgis=# CREATE TABLE address(
postgis(# address_no bigserial PRIMARY KEY,
postgis(# pref_name text,
postgis(# city_name text,
postgis(# street_name text,
postgis(# address text,
postgis(# point_no SMALLINT,
postgis(# x_point decimal(8,1),
postgis(# y_point decimal(8,1),
postgis(# lat decimal(8,5),
postgis(# lng decimal(8,5),
postgis(# jyukyo_flg bool,
postgis(# daihyo_flg bool,
postgis(# upd_bef_flg SMALLINT,
postgis(# upd_aft_flg SMALLINT,
postgis(# geom geometry
postgis(# );
NOTICE: CREATE TABLE will create implicit sequence "address_address_no_seq" for serial column "address.address_no"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "address_pkey" for table "address"
CREATE TABLE
postgis=# COPY address(pref_name,city_name,street_name,address,point_no,x_point,y_point,lat,lng,jyukyo_flg,daihyo_flg,upd_bef_flg,upd_aft_flg) FROM '/home/postgres/14000-10.0a/14_2011_UTF-8.csv' CSV HEADER;
COPY 417941
postgis=#

geom列にlatとlngからgeometry型に変換したジオメトリ情報を更新します。GISを取り扱う上で、geometry型にしておいたほうが便利なようです。
geom列にGISTインデックスを追加します。

postgis=# UPDATE address SET geom=ST_GeomFromText('POINT('||lat||' '||lng||')',4326);
UPDATE 417941
postgis=# CREATE INDEX geom_idx1 ON ADDRESS USING GIST (geom);
CREATE INDEX


緯度・経度から最寄りの住所を検索する

ようやくPostGISっぽいお話です。。

取り込まれたデータを参照してみましょう。

postgis=# SELECT address_no,pref_name,city_name,street_name,address,lat,lng FROM address ORDER BY address_no LIMIT 5;
address_no | pref_name | city_name | street_name | address | lat | lng
------------+-----------+--------------+--------------+---------+----------+-----------
1 | 神奈川県 | 横浜市鶴見区 | 安善町一丁目 | 1 | 35.49669 | 139.70012
2 | 神奈川県 | 横浜市鶴見区 | 安善町一丁目 | 2 | 35.49814 | 139.70396
3 | 神奈川県 | 横浜市鶴見区 | 安善町一丁目 | 3 | 35.49814 | 139.70396
4 | 神奈川県 | 横浜市鶴見区 | 安善町一丁目 | 4 | 35.49814 | 139.70396
5 | 神奈川県 | 横浜市鶴見区 | 安善町一丁目 | 4 | 35.49960 | 139.70068
(5 rows)

postgis=#

上記で取り込んだCSVと同じ情報が格納されている事が分かります。

緯度・経度から最寄りの住所(pref_name,city_name,street_name,address)を取得するSQLをget_address.sqlとして保存しました。

postgres@raspberrypi:~/gis$ cat get_address.sql
SELECT
'ご指定の場所の住所は,'||pref_name||city_name||','||street_name||address||'付近です。'
FROM
address
WHERE
st_distance_spheroid(geom, ST_GeomFromText('POINT(@@@@)',4326),'SPHEROID["GRS_1980",6378137,298.257222101]') < 10000
ORDER BY
st_distance_spheroid(geom, ST_GeomFromText('POINT(@@@@)',4326),'SPHEROID["GRS_1980",6378137,298.257222101]')
LIMIT
1
;

横着してしゃべらせたいテキストをすべてここで完結させてしまっています。。
city_nameとstreet_nameの間に,(カンマ)を入れているのは、OpenJTalkで喋らせる時に自然な読み方にする為です。
WHERE句のst_distance_spheroidは2点間の距離(m)を求めます。
第1引数には該当行のgeom列の座標(geometry型)が入ります。
第2引数にはしゃべらせたい緯度・経度を入れます。後ほどコマンドラインからsedにて座標を差し込む為、ここでは@@@@としています。
第3引数は回転楕円体というらしいです。。詳しくはGISの書籍等で調べて下さい。。オマジナイ的にこうしています。
ここでは10000m(10Km)以内に該当する地点を検索しています。
ORDER BYとLIMIT 1は一番近い列を取得する為です。

このSQLsedで@@@@を緯度・経度に置換しつつ、PostGIS標準入力で渡すと下記のようなテキストが出力されます。

postgres@raspberrypi:~/gis$ sed "s/@@@@/35.448575 139.642403/g" ./get_address.sql|psql -At postgis
ご指定の場所の住所は,神奈川県横浜市中区,海岸通一丁目2付近です。
postgres@raspberrypi:~/gis$


これをリダイレクトし、OpenJTalkに渡すことでコマンドラインに渡された緯度・経度から、PostGISにて最寄りの住所を取得し、しゃべらせることが出来ます。
Raspberry Piでテキスト読み上げ」はコチラを参考にさせて頂きました。
http://homebrew.jp/show?page=1462


Rapsberry PiにはGPIOというマイコン的なインタフェースを持っています。
GPIOを介して、さまざまな電子工作を楽しんでいる方が世界中にいます!
私も下記のようなGPSモジュールを組み込み、車載してスピーカーから現在地を一定間隔で喋り続ける、とかを試したいと思っています。
http://akizukidenshi.com/catalog/g/gM-04554/

いずれは組み込みPostgreSQL(PostGIS)で作る、オープンソースカーナビとか試してみたいです。

オチ

めちゃくちゃ遅いです。。

postgis=# VACUUM ANALYZE;
VACUUM
postgis=# explain analyze SELECT
pref_name,city_name,street_name,address,
st_distance_spheroid(geom, ST_GeomFromText('POINT(35.448575 139.642403)',4326),'SPHEROID["GRS_1980",6378137,298.257222101]')
FROM address WHERE
st_distance_spheroid(geom, ST_GeomFromText('POINT(35.448575 139.642403)',4326),'SPHEROID["GRS_1980",6378137,298.257222101]') < 10000 ORDER BY 5 LIMIT 1; QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=168298.58..168298.59 rows=1 width=74) (actual time=39106.656..39106.659 rows=1 loops=1)
-> Sort (cost=168298.58..168646.87 rows=139314 width=74) (actual time=39106.645..39106.645 rows=1 loops=1)
Sort Key: (st_distance_spheroid(geom, '0101000020E6100000CF66D5E76AB94140467BBC908E746140'::geometry, 'SPHEROID("GRS_1980",6378137,298.257222101004)'::spheroid))
Sort Method: top-N heapsort Memory: 17kB
-> Seq Scan on address (cost=0.00..167602.01 rows=139314 width=74) (actual time=548.105..37977.478 rows=65274 loops=1)
Filter: (st_distance_spheroid(geom, '0101000020E6100000CF66D5E76AB94140467BBC908E746140'::geometry, 'SPHEROID("GRS_1980",6378137,298.257222101004)'::spheroid) < 10000::double precision)
Rows Removed by Filter: 352667
Total runtime: 39107.071 ms
(8 rows)

postgis=# explain analyze SELECT
pref_name,city_name,street_name,address,
st_distance_spheroid(geom, ST_GeomFromText('POINT(35.448575 139.642403)',4326),'SPHEROID["GRS_1980",6378137,298.257222101]')
FROM address WHERE
geom && 'BOX3D(35 140.0,35.791083 138.630066)'::BOX3D AND
st_distance_spheroid(geom, ST_GeomFromText('POINT(35.448575 139.642403)',4326),'SPHEROID["GRS_1980",6378137,298.257222101]') < 10000 ORDER BY 5 LIMIT 1;
QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=31003.61..31003.61 rows=1 width=74) (actual time=41859.410..41859.413 rows=1 loops=1)
-> Sort (cost=31003.61..31020.44 rows=6732 width=74) (actual time=41859.397..41859.397 rows=1 loops=1)
Sort Key: (st_distance_spheroid(geom, '0101000020E6100000CF66D5E76AB94140467BBC908E746140'::geometry, 'SPHEROID("GRS_1980",6378137,298.257222101004)'::spheroid))
Sort Method: top-N heapsort Memory: 17kB
-> Bitmap Heap Scan on address (cost=634.39..30969.95 rows=6732 width=74) (actual time=2502.322..40617.784 rows=65274 loops=1)
Recheck Cond: (geom && '0103000000010000000500000000000000008041404A0A2C802954614000000000008041400000000000806140F6B52E3542E541400000000000806140F6B52E3542E541404A0A2C802954614000000000008041404A0A2C8029546140'::geometry)
Filter: (st_distance_spheroid(geom, '0101000020E6100000CF66D5E76AB94140467BBC908E746140'::geometry, 'SPHEROID("GRS_1980",6378137,298.257222101004)'::spheroid) < 10000::double precision)
Rows Removed by Filter: 352667
-> Bitmap Index Scan on geom_idx1 (cost=0.00..632.71 rows=20196 width=0) (actual time=2482.781..2482.781 rows=417941 loops=1)
Index Cond: (geom && '0103000000010000000500000000000000008041404A0A2C802954614000000000008041400000000000806140F6B52E3542E541400000000000806140F6B52E3542E541404A0A2C802954614000000000008041404A0A2C8029546140'::geometry)
Total runtime: 41860.021 ms
(11 rows)

geosに貼ったインデックスを使って無いな〜、と調べた所、GISでは2つ目のようにgeometryをある程度絞って検索させるのが常識らしい。
しかし、、Bitmap Index Scanにはなったけど、Bitmap Heap Scanがネックに。。
ちなみにPostgreSQLの設定はノーチューニングです。。
キャッシュが利けばもっと早くなるんだろう。。OSキャッシュ使ってくれてもいいのに…なぜだ。。

もっと粒度の荒い住所情報と結合する事で、絞り込むコストを減らせるかも知れません。

とおりすがりの社畜とおりすがりの社畜 2016/05/25 16:26 postgis=# UPDATE address SET geom=ST_GeomFromText('POINT('||lat||' '||lng||')',4326);
lat latitude
lng longitude
なら順番が逆ですよね?

スパム対策のためのダミーです。もし見えても何も入力しないでください
ゲスト


画像認証