postgresqlに郵便番号データをつっこむ(cent os編)
実験機としてたちあげるならば、
郵便番号データをDBにいれて実験するのがよろしいかと
http://d.hatena.ne.jp/toshi_hirasawa/20120816/1345122460
上はオラクルの場合。
以下はpostgresの場合
- インストール手順概要
388 rpm -aq | grep postgres 389 yum install postgres* 390 yum install postgresql postgrsql-server 391 yum install postgresql-server 392 rpm -aq | grep postgresql 393 id postgres 394 finger postges 395 which psql 396 ls -ltr /etc/init.d/ | grep postgres 397 chkconfig postgesql on 398 chkconfig postgresql on 399 chkconfig --list postgresql 400 /etc/init.d/postgresql start 401 service postgresql start 402 cat /etc/init.d/postgresql | grep initdb 403 /etc/init.d/postgresql initdb 404 /etc/init.d/postgresql start 405 history 406 su - postgres
インストールが終了したとして
-bash-3.2$ psql -l
List of databases
Name | Owner | Encoding
-----------+----------+----------
Syslog | postgres | UTF8
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8
(4 rows)
-bash-3.2$
-bash-3.2$ てな感じが最初の状態。
zipって名前のDBを作成する
-bash-3.2$ createdb zip
CREATE DATABASE
-bash-3.2$ psql -l
List of databases
Name | Owner | Encoding
-----------+----------+----------
Syslog | postgres | UTF8
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8
zip | postgres | UTF8
(5 rows)
-bash-3.2$ 発行するSQL文は
-bash-3.2$ cat zip.txt
CREATE TABLE ZIP(
publicCode numeric(5) NOT NULL,
OldZipcode CHAR(5) NOT NULL,
Zipcode CHAR(7) NOT NULL,
PrefectureKana VARCHAR(30) NOT NULL,
CityKana VARCHAR(300) NOT NULL,
TownKana VARCHAR(300) NOT NULL,
Prefecture VARCHAR(30) NOT NULL,
City VARCHAR(300) NOT NULL,
Town VARCHAR(300) NOT NULL,
TownDivideFlg numeric(1) NOT NULL,
KoazaBranchFlg numeric(1) NOT NULL,
ChomeFlg numeric(1) NOT NULL,
HasSomeTownsFlg numeric(1) NOT NULL,
UpdateStatus numeric(1) NOT NULL,
UpdateReason numeric(1) NOT NULL
);
-bash-3.2$
-bash-3.2$ psql zip
Welcome to psql 8.1.23, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
zip=#
zip=# CREATE TABLE ZIP(
zip(# publicCode numeric(5) NOT NULL,
zip(# OldZipcode CHAR(5) NOT NULL,
zip(# Zipcode CHAR(7) NOT NULL,
zip(# PrefectureKana VARCHAR(30) NOT NULL,
zip(# CityKana VARCHAR(300) NOT NULL,
zip(# TownKana VARCHAR(300) NOT NULL,
zip(# Prefecture VARCHAR(30) NOT NULL,
zip(# City VARCHAR(300) NOT NULL,
zip(# Town VARCHAR(300) NOT NULL,
zip(# TownDivideFlg numeric(1) NOT NULL,
zip(# KoazaBranchFlg numeric(1) NOT NULL,
zip(# ChomeFlg numeric(1) NOT NULL,
zip(# HasSomeTownsFlg numeric(1) NOT NULL,
zip(# UpdateStatus numeric(1) NOT NULL,
zip(# UpdateReason numeric(1) NOT NULL
zip(# );
CREATE TABLE
zip=# select * from zip;
publiccode | oldzipcode | zipcode | prefecturekana | citykana | townkana | prefecture | city | town | towndivideflg | koazabranchflg | chomeflg | hassometownsflg | updatestatus | updatereason
------------+------------+---------+----------------+----------+----------+------------+------+------+---------------+----------------+----------+-----------------+--------------+--------------
(0 rows)
zip=#
オラクルに突っ込んだデータを持ってくる
[root@cent5-64b-40 ~]# cp -pr /home/oracle/zipcode/ken_all.utf8.csv /var/lib/pgsql/
[root@cent5-64b-40 ~]#
-bash-3.2$ pwd
/var/lib/pgsql
-bash-3.2$ ls -ltr
合計 17596
drwx------ 2 postgres postgres 4096 6月 26 03:31 backups
-rw------- 1 postgres postgres 5225 8月 25 08:35 pgstartup.log
drwx------ 11 postgres postgres 4096 8月 25 08:38 data
-rw-r--r-- 1 oracle oinstall 17949348 8月 25 12:06 ken_all.utf8.csv
-rw-r--r-- 1 postgres postgres 693 8月 25 12:45 zip.txt
-bash-3.2$ psql zip
Welcome to psql 8.1.23, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
zip=#
zip=# \copy zip from ken_all.utf8.csv with csv
\.
zip=#
zip=# select city,town from zip where zipcode='2030013';
city | town
------------+--------
東久留米市 | 新川町
(1 row)
zip=#
はい、一丁あがりｗ
