国土数値情報(行政区域(面))の shape データから、postgis に入れる SQL を生成する

(出力結果データ_富山県)N03-120401_16_GML.sql

SET CLIENT_ENCODING TO UTF8;
SET STANDARD_CONFORMING_STRINGS TO ON;
BEGIN;
COPY "admarea" ("n03_001","n03_002","n03_003","n03_004","n03_007",geom) FROM stdin;
富山県  \N      下新川郡        朝日町  16343   0106000020041200000100000....
 : (中略)
\.
COMMIT;

create-admarea.sql

-- $ (cd N03-120401_16_GML; shp2pgsql -s 4612 -p -W cp932 *.shp admarea) > create-admarea.sql
-- Shapefile type: Polygon
-- Postgis type: MULTIPOLYGON[2]
-- $ cat create-admarea.sql 
SET CLIENT_ENCODING TO UTF8;
SET STANDARD_CONFORMING_STRINGS TO ON;
BEGIN;
CREATE TABLE "admarea" (gid serial,
"n03_001" varchar(10),
"n03_002" varchar(20),
"n03_003" varchar(20),
"n03_004" varchar(20),
"n03_007" varchar(5));
ALTER TABLE "admarea" ADD PRIMARY KEY (gid);
SELECT AddGeometryColumn('','admarea','geom','4612','MULTIPOLYGON',2);
COMMIT;
-- $ 
-- ## 動かしてみる
-- $ psql -U postgres -d postgis_db -f create-admarea.sql 
-- SET
-- SET
-- BEGIN
-- psql:create-admarea.sql:9: NOTICE:  CREATE TABLE will create implicit sequence "admarea_gid_seq" for serial column "admarea.gid"
-- CREATE TABLE
-- psql:create-admarea.sql:10: NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "admarea_pkey" for table "admarea"
-- ALTER TABLE
--                     addgeometrycolumn                    
-- ---------------------------------------------------------
--  public.admarea.geom SRID:4612 TYPE:MULTIPOLYGON DIMS:2 
-- (1 row)
-- 
-- COMMIT
-- $ 

memo.txt

## 国土数値情報ダウンロードサービス http://nlftp.mlit.go.jp/ksj/ から、
## 「JPGIS2.1(GML)準拠及びSHAPE形式データのダウンロードサービスへ」のリンクをクリックし、
## 国土骨格のベクトルデータ 「行政区域（面）」のリンクをクリック。
## 必要な都道府県をチェックし、先へ進んで利用規約をよく読んで同意、ダウンロード。
## 
## こんな zip ファイルがダウンロードできる。
$ unzip -l N03-120401_16_GML.zip 
Archive:  N03-120401_16_GML.zip
  Length     Date   Time    Name
 --------    ----   ----    ----
    13957  08-21-12 15:27   KS-META-N03-12_16_120401.xml
     2778  04-20-12 15:52   N03-12_16_120401.dbf
   930728  03-05-12 18:02   N03-12_16_120401.shp
      372  03-05-12 18:02   N03-12_16_120401.shx
  1766804  03-05-12 18:31   N03-12_16_120401.xml
 --------                   -------
  2714639                   5 files
$ 
## 幾つかまとめてダウンロードした場合、ファイル数が多くなるので、ディレクトリに分けて解凍する。
##
$ for file in *.zip; do base=`basename $file .zip`; echo $base; mkdir $base; (cd $base; unzip ../$file); done
N03-120401_16_GML
Archive:  ../N03-120401_16_GML.zip
  inflating: KS-META-N03-12_16_120401.xml  
  inflating: N03-12_16_120401.dbf    
  inflating: N03-12_16_120401.shp    
  inflating: N03-12_16_120401.shx    
  inflating: N03-12_16_120401.xml    
N03-120401_17_GML
Archive:  ../N03-120401_17_GML.zip
  inflating: KS-META-N03-12_17_120401.xml  
  inflating: N03-12_17_120401.dbf
     : (中略)
  inflating: N03-12_40_120401.shx    
  inflating: N03-12_40_120401.xml    
$
## で、各フォルダにある shape ファイルを、PostGIS が扱える形式(PostgreSQLのダンプ形式)に shp2pgsql で変換する。
## 標準出力に出力されるのでリダイレクトする。`admarea`が出力先テーブル名。
$ for dir in *_GML; do (echo $dir; cd $dir; shp2pgsql -a -s 4612 -D -i -W cp932 ./*.shp admarea > ../${dir}.sql) done
N03-120401_16_GML
Shapefile type: Polygon
Postgis type: MULTIPOLYGON[2]
N03-120401_17_GML
Shapefile type: Polygon
Postgis type: MULTIPOLYGON[2]
    : (中略)
$ 
## できあがった sql を実行してみる。
$ for file in N03*.sql; do psql -U postgres postgis_db -f $file; done
SET
SET
BEGIN
COMMIT
SET
SET
    : (中略)
COMMIT
$ psql -U postgres -d postgis_db 
psql (9.2.1)
Type "help" for help.

postgis_db=# select count(*) from admarea;
 count 
-------
 18064
(1 row)

postgis_db=# CREATE INDEX "admarea_geom_gist" ON "admarea" USING GIST ("geom");  -- これいらんかった。
ERROR:  relation "admarea_geom_gist" already exists
postgis_db=# \q
$ 

shp2pgsql-memo.txt

$ shp2pgsql
shp2pgsql
RELEASE: 2.0.1 (r9979)
USAGE: shp2pgsql [<options>] <shapefile> [[<schema>.]<table>]
OPTIONS:
  -s [<from>:]<srid> Set the SRID field. Defaults to 0.
      Optionally reprojects from given SRID (cannot be used with -D).
 (-d|a|c|p) These are mutually exclusive options:
     -d  Drops the table, then recreates it and populates
         it with current shape file data.
     -a  Appends shape file into current table, must be
         exactly the same table schema.
     -c  Creates a new table and populates it, this is the
         default if you do not specify any options.
     -p  Prepare mode, only creates the table.
  -g <geocolumn> Specify the name of the geometry/geography column
      (mostly useful in append mode).
  -D  Use postgresql dump format (defaults to SQL insert statements).
  -e  Execute each statement individually, do not use a transaction.
      Not compatible with -D.
  -G  Use geography type (requires lon/lat data or -r to reproject).
  -k  Keep postgresql identifiers case.
  -i  Use int4 type for all integer dbf fields.
  -I  Create a spatial index on the geocolumn.
  -S  Generate simple geometries instead of MULTI geometries.
  -t <dimensionality> Force geometry to be one of '2D', '3DZ', '3DM', or '4D'
  -w  Output WKT instead of WKB.  Note that this can result in
      coordinate drift.
  -W <encoding> Specify the character encoding of Shape's
      attribute column. (default: "UTF-8")
  -N <policy> NULL geometries handling policy (insert*,skip,abort).
  -n  Only import DBF file.
  -T <tablespace> Specify the tablespace for the new table.
      Note that indexes will still use the default tablespace unless the
      -X flag is also used.
  -X <tablespace> Specify the tablespace for the table's indexes.
      This applies to the primary key, and the spatial index if
      the -I flag is used.
  -?  Display this help screen.
$