Advanced Spatial Analysis with Open Source Tools

For Windows and Mac users

• Install PostgreSQL from the EntrepriseDB website
• When prompted to install stackbuilder, accept to install it and choose to install PostGIS under spatial extensions. When prompted, choose to create a spatial database and name it qcbs_workshop. Answer “Yes” to questions about GDAL_DATA, POSTGIS_GDAL_ENABLED_DRIVERS, POSTGIS_ENABLED_OUTDB_RASTERS.

sudo apt-get install postgresql postgis

To create a spatially enabled database

CREATE DATABASE workshop;
\c workshop;
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;

Click here to download the datasets that will be used for the exercises below. Note that all files are in Latitude/Longitude (WGS84 datum).

• obis_toothed_whales.shp → Occurrences of toothed whales (Odontoceti) downloaded from the OBIS website (http://iobis.org/).
• ETOPO1_Bed_g_geotiff_LR.tif → Bedrock based topography downloaded from here: http://www.ngdc.noaa.gov/mgg/global/global.html
• EEZ_IHO_union_v2.shp → Marine and land zones: the union of world country boundaries and Exclusive Economic Zones Boundaries (EEZ) downloaded from here http://www.marineregions.org/downloads.php
• TM_WORLD_BORDERS-0.3.shp → World political borders downloaded from here http://thematicmapping.org/downloads/world_borders.php
• meow_ecos.shp → Marine Ecoregions of the World (MEOW) downloaded from here http://www.marineregions.org/downloads.php

• Introduction to PostGIS
• PostGIS reference

1. Click on Layer…Add Layer…Add PostGIS layer.
2. Click on New to create a new connection
3. Give the connection a name “qcbs workshop”, specify “localhost” as the host, “workshop” as the database, and put your PostgreSQL Username (usually “postgres”).
4. Make sur the DB Manager plugin is activated in the list of plugins.
5. Go to Database… DB Manager. Click on PostGIS and navigate to the spatially enabled database you created earlier.
6. Click on the Import Layer/File icon and choose file called “muni_s.shp”. Click on “Create spatial index” and leave other options unchecked. Choose “muni_s” as the table name. Remember that PostgreSQL table names should never include uppercase letters, spaces, special characters, or accented characters.
7. Repeat these steps to import the “ProtectedAreas-Quebec.shp”, “Hydrography-Surface.shp” and

ST_Length ST_Area ST_

Sum the area of all districts

SELECT tri_cep, sum(ST_Area(geom)) FROM section_vote_31h5_utm GROUP BY tri_cep

CREATE TABLE ce03_gbif_occ AS SELECT a.*, count(b.geom) as num_occ FROM cer03 a LEFT JOIN gbif_mammals b ON (ST_Within(a.geom,b.geom)) GROUP BY a.id 

CREATE TABLE routes_test AS SELECT (ST_Dump(ST_Intersection(section_vote_31h5_utm.geom,routes_utm.geom))).geom as geom FROM section_vote_31h5_utm, routes_utm WHERE tri_cep='CHATEAUGUAY'

SELECT sum(ST_Length(geom)) FROM (SELECT (ST_Dump(ST_Intersection(section_vote_31h5_utm.geom,routes_utm.geom))).geom as geom FROM section_vote_31h5_utm, routes_utm WHERE tri_cep='CHATEAUGUAY') a

SELECT avg(elevation) FROM elevation_utm a, section_vote_31h5_utm b WHERE ST_Within(a.geom,b.geom) AND tri_cep='OUTREMONT'

raster. A raster layer.
vector. A vector layer.
table. A table.
number. A numerical value. A default value must be provided. For instance, depth=number 2.4.
string. A text string. As in the case of numerical values, a default value must be added. For instance, name=string Victor.
boolean. A boolean value. Add True or False after it to set the default value. For example, verbose=boolean True.
multiple raster. A set of input raster layers.
multiple vector. A set of input vector layers.
field. A field in the attributes table of a vector layer. The name of the layer has to be added after the field tag. For instance, if you have declared a vector input with mylayer=vector, you could use myfield=field mylayer to add a field from that layer as parameter.
folder. A folder.
file. A filename.