#!/bin/sh
#
# This script will create a database containing 
# 1. Geobase NRN road data from shapefiles
# 2. OSM data through osm2pgsql
# 3. The NrCan NTS tiles.
# 4. Stored procedures for extracting the data
#
#
##

#
# If your postgres accepts connections on localhost
# (or is on another machine) set this variable
#export PGHOST=localhost
#export PGUSER=mypostgresuser
#export PGPASSWORD=mypostgrespassword

#
# The directory where the extracted geobase SHAPEFILES are
export GEOBASE_FILES=geobase

#The database name we want to use for conversion
export DBNAME=gis

#
# The NTS tilesheets, available from 
# http://ftp2.cits.rncan.gc.ca/pub/index/nts_snrc.zip
export NTS_TILES=nts/decoupage_snrc50k_2.shp

#Path to where the postgis share files bet installed
export POSTGIS_SQL_PATH=/usr/share/postgresql/


#
# The directory that contains your .osm.bz2 files for import
# usually extracts from cloudmade.
export OSM_FILES=osm

#
# The Geobase administrative/provincial boundaries shapefile
# This is needed to only extract OSM data for the province
# you are working on.
#
export PROV_BOUNDARIES=provinces/prov_ab_p_geo83_e.shp

export STATSCAN_DATA=statscan

#
# The script proper 
#
#
createdb --tablespace nas --encoding ascii $DBNAME 
createlang plpgsql $DBNAME
psql $DBNAME -f $POSTGIS_SQL_PATH/lwpostgis.sql
psql $DBNAME -f $POSTGIS_SQL_PATH/spatial_ref_sys.sql

#Load the Tiles
shp2pgsql -s 4269 -S -D -I -Wlatin1 $NTS_TILES nts_50k_index | psql $DBNAME

#Load the Geobase Roads
FIRST=1
for PROVINCE in `ls $GEOBASE_FILES/NRN*ROADSEG.shp`
do
  echo $PROVINCE
  if [ "$FIRST" -eq "1" ] 
  then
      shp2pgsql -s 4617 -I -Wlatin1 -p -S $PROVINCE geobase_roadseg | psql $DBNAME
      FIRST=0
  fi
  shp2pgsql -s 4617 -D -S -a -Wlatin1 $PROVINCE geobase_roadseg |psql $DBNAME
done

#Load the provincial boundaries from Geobase
shp2pgsql -s 4617 -D -I -Wlatin1 $PROV_BOUNDARIES geobase_prov_ab_p |psql $DBNAME

#
# Load the OSM data
#
osm2pgsql -l -s -C 250 -u -d $DBNAME `$OSM_FILES`


#
# Load the StatsCan data for names
#
#

FIRST=1
for PROVINCE in $STATSCAN_DATA/*shp
do
  if [ "$FIRST" -eq "1" ] 
      then
      shp2pgsql -s 4269 -I -S -Wlatin1 -p $PROVINCE statscan_road_network |psql $DBNAME
      FIRST=0
  fi
  shp2pgsql -s 4269 -D -a -S -Wlatin1 $PROVINCE statscan_road_network|psql $DBNAME
done


#
# Load the stored procedures
#
psql -f shapefile_stored_procs.sql $DBNAME