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

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

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

以下が、そのshellになります。何をどうしているのか？については、下記shellから読み取ってください。きっと僕もわかりません。

#!/bin/bash

# *************************************************************************************
# 国土交通省からダウンロードした、大字・町丁目レベル位置参照情報データ形式のCSVファイルを
# Postgresqlの対象テーブルに一括でロードするスクリプト
# ※すでに同名のテーブルが存在する場合は、DROPされますのでご注意ください※
# 尚、ダウンロードデータにつきましては
# http://nlftp.mlit.go.jp/isj/index.html を参照ください
# *************************************************************************************

# スクリプト開始時間/終了時間の設定
wstart_time=`date +"%Y-%m-%d %T"`
wend_time=`date +"%Y-%m-%d %T"`

# ダウンロードしたファイルが格納されているディレクトリを指定（*.zip）
wdir=/tmp/gaiku

# データベース接続設定 *********************************************
wdatabases="pgis"
wuserid="postgres"
wpasswd="postgresqlpass"
wtables="gaiku_geos"
# ******************************************************************

cd $wdir
# テーブルのCREATE TABLE文を生成
cat << CRE > ${wdir}/create_table_${wtables}.csql
drop table if exists ${wtables};
CREATE TABLE ${wtables} (
  id SERIAL PRIMARY KEY,
  prefecture varchar(64) DEFAULT NULL,
  city varchar(128) DEFAULT NULL,
  area varchar(128) DEFAULT NULL,
  jiban varchar(128) DEFAULT NULL,
  zaban int DEFAULT NULL,
  x decimal(7,1) DEFAULT NULL,
  y decimal(7,1) DEFAULT NULL,
  lat decimal(9,6) DEFAULT NULL,
  lng decimal(9,6) DEFAULT NULL,
  jukyo int DEFAULT NULL,
  daihyo int DEFAULT NULL,
  history16 int DEFAULT NULL,
  history17 int DEFAULT NULL,
  updated timestamp NOT NULL DEFAULT current_timestamp,
  created timestamp NOT NULL DEFAULT current_timestamp
) ;
SELECT AddGeometryColumn('public', '${wtables}', 'geom', 4326, 'POINT', 2);
CRE

# テーブルの作成
echo "PGPASSWORD=${wpasswd} psql ${wdatabases} -U ${wuserid} -f ${wdir}/create_table_${wtables}.csql"
PGPASSWORD=${wpasswd} psql ${wdatabases} -U ${wuserid} -f ${wdir}/create_table_${wtables}.csql

# zip ファイルの解凍

for file in *.zip
do
   echo "unzip ${file}";
   unzip -o $file;
done

# 不要なファイルを削除する
rm -f $wdir/*.html
rm -f $wdir/*.xml

# 解凍したCSVファイルをUTF-8に変更する
for file in *.CSV
do
   echo " to utf-8 ${file}";
   nkf -w $file > utf8_${file}.CSVx;
done

# ロードSQLスクリプトを生成する
for file in ${wdir}/utf8_*.CSVx
do
echo "create ${file}_load.sql"
cat << EOS > ${file}_load.sql
COPY ${wtables} (prefecture,city,area,jiban,zaban,x,y,lat,lng,jukyo,daihyo,history16,history17)
from
  '${file}' WITH CSV HEADER;
EOS
done

for file in *.sql
do
# ロード処理実行
  PGPASSWORD=${wpasswd} psql ${wdatabases} -U ${wuserid} -f ${file};
done
# geomフィールドの生成
PGPASSWORD=${wpasswd} psql ${wdatabases} -U ${wuserid} -c "update ${wtables} set geom = GeometryFromText('POINT(' || lng || ' ' || lat || ')',4326);";
# 索引の生成
PGPASSWORD=${wpasswd} psql ${wdatabases} -U ${wuserid} -c "create index idx01_${wtables} on ${wtables} using gist (geom);";
# バキュームと統計情報更新
PGPASSWORD=${wpasswd} psql ${wdatabases} -U ${wuserid} -c "vacuum analyze;";

# 不要なファイルを削除する
rm -f $wdir/*.CSVx
rm -f $wdir/*.CSV
rm -f $wdir/*.sql

# ログへ書き込み
wend_time=`date +"%Y-%m-%d %T"`
echo "table ${wtables} load complate ${wstart_time} - ${wend_time}" >> ${wdir}/${wtables}_load.log

exit 0