All tracks

GIS in SQL

7 modules, 12.5 hours. Work through them in order: each module lists the ones it needs first.

1. g1 Coordinate reference systems

   • PostgreSQL with PostGIS
   • 1.5 hours

   Prerequisites: Window functions (f4)

   Skills: SRIDs, ST_Transform, geometry vs geography, Web Mercator's area error

   • How big is Tasmania in EPSG:3857, in EPSG:3577 and as geography?
   • Why does Web Mercator overstate it?

2. g2 Loading and validating shapes

   • PostgreSQL with PostGIS
   • 1.5 hours

   Prerequisites: Coordinate reference systems (g1)

   Skills: The loader, WKT and WKB, ST_IsValid, ST_MakeValid

   • Which shapes are invalid, and why?
   • What does ST_MakeValid change?

3. g3 Spatial joins

   • PostgreSQL with PostGIS
   • 2 hours

   Prerequisites: Loading and validating shapes (g2)

   Skills: ST_Contains, ST_Covers, ST_Intersects; point in polygon; records on boundaries

   • Do GBIF's country codes agree with the country outlines?
   • Which records fall on a coast or in no country?

4. g4 Distance and overlay

   • PostgreSQL with PostGIS
   • 2 hours

   Prerequisites: Spatial joins (g3)

   Skills: Distances on the ellipsoid, ST_DWithin, buffers, ST_Intersection

   • How much of each country does each ecoregion cover?
   • Which records lie within 10 km of a border?

5. g5 Spatial indexes

   • PostgreSQL with PostGIS
   • 1.5 hours

   Prerequisites: Spatial joins (g3), Indexes and query plans (d3)

   Skills: GiST, the && operator, nearest neighbours with <->, reading spatial plans

   • Which record is nearest to a point, found fast?
   • When doesn't a spatial index help?

6. g6 Grids

   • PostgreSQL with PostGIS
   • 1.5 hours

   Prerequisites: Distance and overlay (g4)

   Skills: ST_HexagonGrid in an equal-area SRID; the modifiable areal unit problem (MAUP)

   • How many Tasmanian devil records fall in each 5 km hexagon, and in each 10 km one?
   • How does the picture change with the grid?

7. g7 Rasters

   • PostgreSQL with PostGIS
   • 2.5 hours

   Prerequisites: Grids (g6)

   Skills: Value at a point; ST_Clip, ST_ValueCount; estimated areas and their error

   • Which habitat classes hold Costa Rica's records?
   • How much of each class does a clip hold, and how good is that estimate?