|
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
|
