Project

General

Profile

1
# Bash script to create a new postgis database and prep it with GADM
2
# data for geovalidation purposes.
3
#
4
# Basic workflow:
5
#  1. Create postgis-enabled db
6
#  2. Load gadm2 data into database
7
#     a. create polygon geometry upon import (loader does this)
8
#     b. create simplified polygon geometry after import
9
#     c. create polygon geography after import
10
#     d. create indexes
11
#     e. cluster data in geom index order
12
# 
13
# For postgis installation see:
14
#   http://trac.osgeo.org/postgis/wiki/UsersWikiPostGIS20Ubuntu1204
15
#
16
# todo:
17
# * better handle Antarctica problem so it can be included instead of
18
#   just dropped? probably not a huge deal for plants...
19
#
20
# Jim Regetz
21
# NCEAS
22
# Created November 2012
23

    
24
PGROOT="/usr/share/postgresql/9.1/contrib/postgis-2.0"
25

    
26
# create postgis template if one doesn't already exist
27
sudo -u postgres createdb template_postgis
28
sudo -u postgres psql -d template_postgis -f $PGROOT/postgis.sql
29
sudo -u postgres psql -d template_postgis -f $PGROOT/spatial_ref_sys.sql
30
sudo -u postgres psql -d template_postgis -f $PGROOT/postgis_comments.sql
31

    
32
# create postgis db from template
33
createdb geoscrub -T template_postgis
34

    
35
#
36
# assemble input data
37
#
38

    
39
# get GADM data (320MB zip file) and unzip
40
wget http://www.gadm.org/data2/gadm_v2_shp.zip
41
unzip gadm_v2_shp.zip
42

    
43
#
44
# create and populate gadm2 table
45
#
46

    
47
# load gadm2 data (took 4.7 minutes on willow, 26-Oct-2012)
48
shp2pgsql -s 4326 -W latin1 gadm2 gadm2 geoscrub | psql -d geoscrub
49

    
50
# create indexes
51
psql -c 'CREATE INDEX "gadm2_iso_idx" ON gadm2 (iso)' geoscrub
52
psql -c 'CREATE INDEX "gadm2_geom_gist" ON gadm2 USING GIST (geom)' geoscrub
53
psql -c 'CREATE INDEX "gadm2_name_0_idx" ON gadm2 (name_0)' geoscrub
54
psql -c 'CREATE INDEX "gadm2_name_1_idx" ON gadm2 (name_1)' geoscrub
55

    
56

    
57
# physically reorder tuples on disk in country name index order
58
# nb: i _think_ this helped, but am not 100% sure
59
psql -c 'CLUSTER gadm2 USING gadm2_name_0_idx' geoscrub
60

    
61
# create simplified country polygons
62
psql -c \
63
  "SELECT AddGeometryColumn('public', 'gadm2', 'simple_geom', 4326, 'MULTIPOLYGON', 2 );
64
   UPDATE gadm2 SET simple_geom = (SELECT st_simplify(geom, 0.001))" geoscrub
65
psql -c \
66
  'CREATE INDEX "gadm2_simple_geom_gist" ON gadm2 USING gist (simple_geom)' geoscrub
67
# ... and create geography versions ...
68
# note workaround to exclude Antarctica because it has an invalid
69
# western extent of -180.000015258789
70
psql -c \
71
  "ALTER TABLE gadm2 ADD COLUMN simple_geog geography;
72
   UPDATE gadm2 SET simple_geog = simple_geom::geography WHERE name_0<>'Antarctica'" geoscrub
73
psql -c \
74
  'CREATE INDEX "gadm2_simple_geog_gist" ON gadm2 USING gist (simple_geog)' geoscrub
(7-7/9)