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;

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