PostgreSQL 位置参照データベースを作ってみる(GISデータ)


そもそも地図はよくわからないので、もう地図のことは書くまいと心に決めていたのですが、意外と見ていただいている方は多いので、やっぱり地図データベースのことを書きます。前回は、MySQLをベースに書きましたが、今回は、PostgreSQLで、PostGISを利用した位置参照データベースの構築、利用方法について書いてみたいと思います。PostGISとはPostgreSQL Geographic Information Systemの略で、まあなんでしょ?地図情報、位置情報などのライブラリを含めたツールの総称です。このページを見ているかたは、おおよそ理解しているかと思いますので細かい話は割愛しますね。逆におおよそ理解している方はこの内容を読むかどうかは別の話ですが・・・(汗

続きを読む “PostgreSQL 位置参照データベースを作ってみる(GISデータ)”

国土交通省位置測定データを一括登録する方法(PostgreSQL)


国土交通省の、街区データを、PostgreSQLに一括アップロードするshellを作ってみました。色々チューニングする必要がありますが、MySQLと比較してとにかく・・・遅い・シェル実行してから完了するまで数時間つぶれます。ここのようなプアーな環境ですと、一日かかるかもしれません。正直いって自動化はできても、運用は壊滅的にNGなんで、もっと効率のようなやり方はないかな?と思考中です。

国土交通省の街区データに関しては、ここからダウンロードできます。(街区情報のほうです)、細かい説明は割愛しますが、ダウンロードした、zipファイルを任意のディレクトリにZIPファイルのままアップロードして下記のshellを流してください、たぶん動きます。そのかわり500万件以上のデータを、データベースに一括でロードするわけなので、かなり長時間かかると思ってください。

続きを読む “国土交通省位置測定データを一括登録する方法(PostgreSQL)”

EnterpriseDB(PostgreSQL Plus 9.0) – glibc detectedのエラー


PostgreSQLでも、EnterpriseDBでもきっと同じだと思うのだけれども、コマンドラインでたとへな、\qとか、\hとかのコマンドをうった途端にスタックトレース(エラー)がでてしまう現象に対して、いや、本当に俺なんかした?
とあせりましたが、この現象はどうもバグみたいです。あ、ちなみにエラーメッセージがこんな感じで出力されます

勘弁してくれよ、オイオイ・・・・

pgis=# \q
*** glibc detected *** psql: realloc(): invalid next size: 0x0000000005774070 ***
======= Backtrace: =========
/lib64/[0x30ac8747a4]
/lib64/libc.so.6(realloc+0x102)[0x30ac875292]
/opt/PostgresPlus/9.0SS/bin/../lib/libedit.so[0x2ab1c22cae4a]
/opt/PostgresPlus/9.0SS/bin/../lib/libedit.so(history+0x63e)[0x2ab1c22cb775]
/opt/PostgresPlus/9.0SS/bin/../lib/libedit.so(write_history+0x47)[0x2ab1c22dbd1e]
psql[0x40a024]
psql[0x40a270]
/lib64/libc.so.6(exit+0xe5)[0x30ac8333a5]
/lib64/libc.so.6(__libc_start_main+0xfb)[0x30ac81d99b]
psql[0x403a69]

glibc detected というキーワードがでてきたら十中八九、以下の対応で改善されるかと思いますので、同じ現象に見舞われ、もう、PostgreSQL 9はいいや・・とかあきらめている方、おりましたらぜひとも試してみてください。
以下に手順を記載します。

  1. yum から、libedit モジュールをインストールする(リポジトリは、epelを指定します)
    # yum install libedit.x86_64
    
  2. 無事インストールされたか確認する
    # rpm -q libedit.x86_64
    libedit-2.11-2.20080712cvs.el5
    
  3. インストールされたパッケージの中身を確認する
    # rpm -q libedit.x86_64 --filesbypkg
    libedit                   /usr/lib64/libedit.so.0
    libedit                   /usr/lib64/libedit.so.0.0.27
    libedit                   /usr/share/doc/libedit-2.11
    libedit                   /usr/share/doc/libedit-2.11/COPYING
    libedit                   /usr/share/doc/libedit-2.11/ChangeLog
    libedit                   /usr/share/doc/libedit-2.11/THANKS
    
  4. ディレクトリを切り替え、シンボリックリンクを張りなおす
    # cd /opt/PostgresPlus/9.0SS/lib/
    # ls -l libedit*
    -rwxr-xr-- 1 root daemon 192738 12月 17 02:53 libedit.so
    # mv libedit.so libedit.so.bk
    # ln -s /usr/lib64/libedit.so.0 libedit.so
    

以上で問題はほぼ改善されます。

EnterpriseDB(PostgreSQL Plus) – クエリの応答時間を計測する


PostgreSQLの、コマンドで、クエリーの応答時間をとりたい場合は、以下のようなコマンドを記載します。
以下のSQLは、POSTGISを利用した、クエリーの応答時間がとれくらいかと算出している状況です。

pgis=# \timing
pgis=# SELECT * FROM
pgis-# (
pgis(# SELECT
pgis(# prefecture,city,area,rtrim(jiban) as banchi,X(geom) as lng,Y(geom) as lat,
pgis(# distance_spheroid(
pgis(# geom,
pgis(# GeometryFromText('POINT(139.724501 35.687577)',4326),
pgis(# 'SPHEROID["GRS_1980",6378137,298.257222101]'
pgis(# ) AS KYORI FROM geos
pgis(# where
pgis(#     geom && Box2D(ST_GeomFromText('LINESTRING(139.650 35.600, 139.800 35.750)'))
pgis(#   AND
pgis(#     distance_spheroid(geom,GeometryFromText('POINT(139.724501 35.687577)',4326),'SPHEROID["GRS_1980",6378137,298.257222101]') < 100
pgis(# ) AS GISX
pgis-# ORDER BY
pgis-#   GISX.KYORI;
prefecture |  city  |    area    | banchi |    lng    |   lat    |      kyori
------------+--------+------------+--------+-----------+----------+------------------
東京都     | 新宿区 | 四谷二丁目 | 12     | 139.72438 | 35.68781 | 28.0764049790079
東京都     | 新宿区 | 四谷二丁目 | 11     | 139.72457 | 35.68731 | 30.2759248844976
東京都     | 新宿区 | 四谷二丁目 | 10     |   139.725 | 35.68763 | 45.5497254663094
東京都     | 新宿区 | 三栄町     | 18     | 139.72524 |  35.6879 | 75.8879717852212
東京都     | 新宿区 | 三栄町     | 25     | 139.72415 | 35.68827 | 83.1962062966614
東京都     | 新宿区 | 四谷二丁目 | 7      | 139.72487 | 35.68687 | 85.2589884379294
東京都     | 新宿区 | 三栄町     | 18     | 139.72509 | 35.68819 | 86.4200439986426
東京都     | 新宿区 | 三栄町     | 24     | 139.72472 | 35.68834 | 86.9472210027482
東京都     | 新宿区 | 四谷二丁目 | 13     | 139.72364 | 35.68802 | 92.1407737733267
東京都     | 新宿区 | 四谷二丁目 | 8      | 139.72534 | 35.68706 | 95.1741204078278
東京都     | 新宿区 | 三栄町     | 17     | 139.72552 | 35.68781 | 95.7920604004985
(11 rows)

Time: 281.905 ms

上記通り、SQL発行前に、\timingを発行してあげることによってTimeを返すことができます。
ちょっとした豆知識なんだけど、みなさん知ってますかね?・・・

EnterpriseDB(PostgreSQL Plus)のメモ — 環境周り


PostgreSQL Plusの環境周りについて、通常のPostgreSQLと若干環境が違うので・・・のメモ

1. .bashrc の設定

# vi .bashrc
PATH="$PATH":/opt/PostgresPlus/8.4SS/bin/

2. 外部接続の設定

# vi /opt/PostgresPlus/8.4SS/data/postgresql.conf
他ホストからの接続を許可する
listen_addresses = '*'

3. 外部接続設定

# vi /opt/PostgresPlus/8.4SS/data/pg_hba.conf
すべてのクライアントからの接続を許可するが、パスワードが必要
# IPv4 local connections:
# host    all         all         127.0.0.1/32        md5
host    all         all         0.0.0.0/0             md5

4. postgresqlの再起動

# /etc/init.d/postgresql-8.4 restart

5. アンインストーラーの場所

# pwd
/opt/PostgresPlus/8.4SS
# ll
-rwx------  1 root     root     3554060  5月 24 21:39 uninstall-pgmemcache
-rwx------  1 root     root     3651816  5月 24 21:39 uninstall-postgresplus
-rwx------  1 root     daemon   3617934  5月 24 21:38 uninstall-postgresql

EnterpriseDB(PostgreSQL Plus)のメモ — コマンド


コンパイルしてインストールするのが、おっくうなので、PostgreSQLを利用する場合は、基本、Enterprisedb(EDB)を利用します。
Linuxでのインストールは、X-Windowが必要ですが、コンパイルするよりまし(笑)

とまあ、インストールは楽なのですが、コマンドは毎回毎回ですが、忘れます。ヘルプを見ればわかるのですが、ここは基本の個人のメモ帳扱いなので、
ぱっとみてできるようにヘルプファイルをコピーしております。

[psqlのコマンド] # /opt/PostgresPlus/9.0SS/bin/psql –help ででます。

[postgresql]内でのコマンド一覧

 

 

postgresql データを最適化(再編成)する方法


データを最適化する方法として、postgresqlは、vacuum という機能がございます。
まずデータの最適化とは何か?について説明します。
例えば、以下のようなデータが格納されているテーブルがあったと過程して説明いたします。

【現在のテーブル】

TAB1

Col1 Col2 Col3
1 A ABC
2 B ABC
3 C ABC
4 D ABC
5 E ABC
6 F ABC
7 G ABC
8 H ABC
9 I ABC

上記のTAB1テーブルに対して、下記、DELETE SQLを発行します。

  • DELETE FROM TAB1 WHERE COL1 = 3;
  • DELETE FROM TAB1 WHERE COL1 = 6;
  • DELETE FROM TAB1 WHERE COL1 = 2;

すると、テーブル的にどうなるかといいますと、以下のようになります。これがいわゆる虫食い状態といいます。
データベース全般がそうだと思っていますが、DELETEとは削除をするというか、実際は、nullで埋めるといったほうが
良いかもしれません。当然このままの状態ですと、SELECT等のSQLが発行された場合、必要な部分行までレコードを
シークしますので無駄なオーバーヘッドがかかります。

【DELETE後のテーブル状態】

TAB1

Col1 Col2 Col3
1 A ABC
4 D ABC
5 E ABC
7 G ABC
8 H ABC
9 I ABC

この虫食い状態を再配置するために必要なコマンドが、vacuumというコマンドになります。このコマンドを発行しますと、
上記のようなテーブルが最適化され、以下のような状態になります。

【vacuum実行後のテーブル状態】

TAB1

Col1 Col2 Col3
1 A ABC
4 D ABC
5 E ABC
7 G ABC
8 H ABC
9 I ABC

上記を、踏まえた上で、vacuumの実行方法について記載いたします。

# su – dbuser
$ vacuumdb qk
VACUUM

これだけです。VACUUMというメッセージが出力されれば、正常に完了しております。簡単ですね。
ただし、vacuumdbは、かなりの負荷がかかり、通常運用で頻繁に実行すると、最悪関連するアプリケーションが
停止してしまうということがあるかもしれません。V8.1 以前を利用していた方は、アクセス負荷が低い時間を狙い、定期的にこの
処理を流していたのです、このあたりがかなり悩みどころだったのですが、V8.1以降をお使いの方はこの問題が解決され。
autovacuumという機能が新規で追加されております。ついでなので、autovacuumの設定にするにはどうしたらいいのか?についても
触れてみたいと思います。

  1. postgresqlの設定を編集します。
    # vi /var/lib/pgsql/data/postgresql.conf
  2. 以下の行を探します。
    #autovacuum = off                       # enable autovacuum subprocess?
  3. このように直します。
    autovacuum = on # enable autovacuum subprocess?
  4. 以下の行を探します。
    #stats_row_level = off
  5. このように直します。
    stats_row_level = on
  6. 設定を反映させるため、postmasterを再起動します。
    su – postgres
    -bash-3.2$ pg_ctl restart
    waiting for postmaster to shut down…. done
    postmaster stopped
    postmaster starting

以上で、autovacuumの設定は完了です。