シェープファイルをスクリプトファイル(.sql)へ変換する
(1).サンプルとしてココからcountyp020というシェープファイルを取得する。
(2).shp2pgsqlを使ってスクリプトファイルへ変換する
shp2pgsql countyp020.shp countyp020 > mycounties.sql
(3).データベースへ登録する
psql -d mydb -f /Users/postgres/work/dw/countyp020/mycounties.sql
(4).結果を確認する
mydb=# \dt
List of relations
Schema | Name | Type | Owner
--------+------------------+-------+----------
public | account | table | postgres
public | auto_identified | table | postgres
public | countyp020 | table | postgres
public | geometry_columns | table | postgres
public | spatial_ref_sys | table | postgres
(5 rows)
mydb=# SELECT count(*) FROM countyp020;
count
-------
6138
(1 row)
(5).county020の中身を確認
mydb=# \d countyp020
Table "public.countyp020"
Column | Type | Modifiers
------------+-----------------------+----------------------------------------------------------
gid | integer | not null default nextval('countyp020_gid_seq'::regclass)
area | double precision |
perimeter | double precision |
countyp020 | numeric(11,0) |
state | character varying(2) |
county | character varying(50) |
fips | character varying(5) |
state_fips | character varying(2) |
square_mil | double precision |
the_geom | geometry |
Indexes:
"countyp020_pkey" PRIMARY KEY, btree (gid)
Check constraints:
"enforce_dims_the_geom" CHECK (st_ndims(the_geom) = 2)
"enforce_geotype_the_geom" CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL)
"enforce_srid_the_geom" CHECK (st_srid(the_geom) = (-1))
※the_geomに地理データを格納する
(6).テーブルcountyp020にどんなデータが入っているか確認
mydb=# SELECT DISTINCT county FROM countyp020 WHERE state = 'NM';
county
-------------------
Santa Fe County
Torrance County
Catron County
Cibola County
Hidalgo County
Rio Arriba County
DeBaca County
Socorro County
Lea County
Dona Ana County
Roosevelt County
Lincoln County
Taos County
Sandoval County
San Miguel County
Luna County
Quay County
Colfax County
※DISTINCT句：重複するデータを排除する。
(7).テーブルcountyp020のstate='NM'、county='Curry County'の全てのデータを表示してみる
※表示しやすいように表示拡張モード(¥x)を使う
mydb=# \x
Expanded display is on.
mydb=# SELECT * FROM countyp020 WHERE state = 'NM' AND county = 'Curry County';
gid | 4345
area | 0.358
perimeter | 2.69
countyp020 | 4346
state | NM
county | Curry County
fips | 35009
state_fips | 35
square_mil | 1404.995
the_geom | 010600000001000000010300000001……
(8).the_geomはジオメトリデータであり、見やすいように以下の通り変換して表示する
mydb=# SELECT astext(the_geom) FROM countyp020 WHERE state = 'NM' AND county = 'Curry County'
mydb-# ;
astext | MULTIPOLYGON(
(
(-103.042381286621 34.7472114562988,
-103.04264831543 34.3676338195801,
-103.04239654541 34.3099784851074,
-103.042411804199 34.3021659851074,
-103.73503112793 34.3031768798828,
-103.736526489258 34.3472938537598,
-103.736770629883 34.6058616638184,
-103.704917907715 34.6066055297852,
-103.701736450195 34.6946868896484,
-103.597763061523 34.6935043334961,
-103.491523742676 34.6935424804688,
-103.490394592285 34.7798385620117,
-103.386329650879 34.7780151367188,
-103.386734008789 34.8652534484863,
-103.281379699707 34.864673614502,
-103.283248901367 34.9546585083008,
-103.042610168457 34.9534721374512,
-103.042358398438 34.7807388305664,
-103.042381286621 34.7472114562988
)
)
)