Data to import for ThinkHazard project

.gitignore

g2015*

README.md

This gist helps storing data to import into the database for the ThinkHazard project to run.

GAUL data downloaded from fao.org geonetwork website:

• countries for 2014,
• first division for 2014,
• second division for2014

import.sh

#! /bin/bash

echo "Creating database"
sudo -u postgres dropdb gaul
sudo -u postgres createdb gaul
sudo -u postgres psql -d gaul -c "CREATE EXTENSION postgis;"

echo "Unzipping shapefiles"
unzip -o g2015_2014_0.zip
unzip -o g2015_2014_1.zip
unzip -o g2015_2014_2.zip

echo "Shapefile > PostGIS"
shp2pgsql -s 4326 -W LATIN1 g2015_2014_0/g2015_2014_0.shp | sudo -u postgres psql -d gaul
shp2pgsql -s 4326 -W LATIN1 g2015_2014_1/g2015_2014_1.shp | sudo -u postgres psql -d gaul
shp2pgsql -s 4326 -W LATIN1 g2015_2014_2/g2015_2014_2.shp | sudo -u postgres psql -d gaul

echo "Removing duplicates"
sudo -u postgres psql -d gaul -c "DELETE FROM g2015_2014_0 WHERE gid = 177;"

sudo -u postgres psql -d gaul -c "DELETE FROM g2015_2014_2 WHERE gid = 5340;"
sudo -u postgres psql -d gaul -c "DELETE FROM g2015_2014_2 WHERE gid = 5382;"
sudo -u postgres psql -d gaul -c "DELETE FROM g2015_2014_2 WHERE gid = 5719;"
sudo -u postgres psql -d gaul -c "DELETE FROM g2015_2014_2 WHERE gid = 20775;"
sudo -u postgres psql -d gaul -c "DELETE FROM g2015_2014_2 WHERE gid = 1059;"

echo "Creating administrative divisions"
sudo -u postgres psql -d gaul -c "
CREATE TABLE administrativedivision (
    id SERIAL PRIMARY KEY,
    code integer UNIQUE,
    leveltype_id integer,
    name varchar,
    parent_code integer
);

SELECT AddGeometryColumn('administrativedivision', 'geom', 3857, 'MULTIPOLYGON', 2);

INSERT INTO administrativedivision (code, leveltype_id, name, parent_code, geom)
SELECT adm0_code, 1, adm0_name, NULL, ST_Transform(geom, 3857) as geom
FROM g2015_2014_0;

INSERT INTO administrativedivision (code, leveltype_id, name, parent_code, geom)
SELECT adm1_code, 2, adm1_name, adm0_code, ST_Transform(geom, 3857) as geom
FROM g2015_2014_1;

INSERT INTO administrativedivision (code, leveltype_id, name, parent_code, geom)
SELECT adm2_code, 3, adm2_name, adm1_code, ST_Transform(geom, 3857) as geom
FROM g2015_2014_2;
"