| 1 |
10707
|
aaronmk
|
# 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
|
