critwire · Learn · Learn's data

critwire Learn

A course in working with data: SQL, database design, GIS in SQL, spatial statistics, ETL and Power BI, taught on critwire's own open species data and a sample of open GBIF occurrence records.

Start with Foundations: four short modules of SQL that run in this browser, with nothing to install. The tracks after it build on Foundations and on each other; each module names what it needs first. Every module answers real questions with the lab data, has an exercise (the browser's check your answer, the others show theirs to compare), and says how long it takes, lab and exercise included.

Each module runs on one or two tools, the capstone on three:

A record is one observation of an animal at a place and time. The course counts and maps records; it never presents them as an animal's range.

Ticks show the modules you marked done. They're kept in this browser only, never sent anywhere.

Foundations

4 modules, 6 hours

  1. f1 Select and filter

    • Browser
    • 1 hour

  2. f2 Group and aggregate

    • Browser
    • 1.5 hours

    Prerequisites: Select and filter (f1)

  3. f3 Joins

    • Browser
    • 2 hours

    Prerequisites: Group and aggregate (f2)

  4. f4 Window functions

    • Browser
    • 1.5 hours

    Prerequisites: Joins (f3)

Database architecture

6 modules, 12 hours

  1. d1 Keys and constraints

    • Browser
    • PostgreSQL with PostGIS
    • 2 hours

    Prerequisites: Window functions (f4)

  2. d2 Normalization

    • Browser
    • 2 hours

    Prerequisites: Keys and constraints (d1)

  3. d3 Indexes and query plans

    • PostgreSQL with PostGIS
    • 2 hours

    Prerequisites: Keys and constraints (d1)

  4. d4 Transactions

    • PostgreSQL with PostGIS
    • 2 hours

    Prerequisites: Indexes and query plans (d3)

  5. d5 Roles and privileges

    • PostgreSQL with PostGIS
    • 1.5 hours

    Prerequisites: Transactions (d4)

  6. d6 Star schemas

    • PostgreSQL with PostGIS
    • Browser
    • 2.5 hours

    Prerequisites: Normalization (d2), Transactions (d4)

GIS in SQL

7 modules, 12.5 hours

  1. g1 Coordinate reference systems

    • PostgreSQL with PostGIS
    • 1.5 hours

    Prerequisites: Window functions (f4)

  2. g2 Loading and validating shapes

    • PostgreSQL with PostGIS
    • 1.5 hours

    Prerequisites: Coordinate reference systems (g1)

  3. g3 Spatial joins

    • PostgreSQL with PostGIS
    • 2 hours

    Prerequisites: Loading and validating shapes (g2)

  4. g4 Distance and overlay

    • PostgreSQL with PostGIS
    • 2 hours

    Prerequisites: Spatial joins (g3)

  5. g5 Spatial indexes

    • PostgreSQL with PostGIS
    • 1.5 hours

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

  6. g6 Grids

    • PostgreSQL with PostGIS
    • 1.5 hours

    Prerequisites: Distance and overlay (g4)

  7. g7 Rasters

    • PostgreSQL with PostGIS
    • 2.5 hours

    Prerequisites: Grids (g6)

Spatial statistics

6 modules, 12.5 hours

  1. s1 Bias in records

    • PostgreSQL with PostGIS
    • Browser
    • 1.5 hours

    Prerequisites: Spatial joins (g3)

  2. s2 Point patterns

    • PostgreSQL with PostGIS
    • Python
    • 2.5 hours

    Prerequisites: Bias in records (s1), Grids (g6)

  3. s3 Spatial weights

    • PostgreSQL with PostGIS
    • Python
    • 2 hours

    Prerequisites: Point patterns (s2)

  4. s4 Global Moran's I

    • PostgreSQL with PostGIS
    • Python
    • 2 hours

    Prerequisites: Spatial weights (s3)

  5. s5 Hot and cold spots

    • PostgreSQL with PostGIS
    • Python
    • 2.5 hours

    Prerequisites: Global Moran's I (s4)

  6. s6 Zonal statistics

    • PostgreSQL with PostGIS
    • Python
    • 2 hours

    Prerequisites: Hot and cold spots (s5), Rasters (g7)

ETL

5 modules, 10 hours

  1. e1 Extract

    • Python
    • 2 hours

    Prerequisites: Group and aggregate (f2)

  2. e2 Transform and load

    • Python
    • PostgreSQL with PostGIS
    • 2.5 hours

    Prerequisites: Extract (e1), Transactions (d4)

  3. e3 Pipelines you can re-run

    • Python
    • PostgreSQL with PostGIS
    • 2 hours

    Prerequisites: Transform and load (e2)

  4. e4 Quality and provenance

    • Python
    • PostgreSQL with PostGIS
    • 2 hours

    Prerequisites: Pipelines you can re-run (e3)

  5. e5 Scheduling

    • Python
    • 1.5 hours

    Prerequisites: Quality and provenance (e4)

Power BI

4 modules, 7 hours

  1. p1 Power Query

    • Power BI Desktop
    • 1.5 hours

    Prerequisites: Star schemas (d6)

  2. p2 The data model

    • Power BI Desktop
    • PostgreSQL with PostGIS
    • 2 hours

    Prerequisites: Power Query (p1)

  3. p3 DAX measures

    • Power BI Desktop
    • PostgreSQL with PostGIS
    • 2 hours

    Prerequisites: The data model (p2)

  4. p4 Maps and reports

    • Power BI Desktop
    • PostgreSQL with PostGIS
    • 1.5 hours

    Prerequisites: DAX measures (p3)

Capstone

1 module, 8 hours

  1. c1 A feed for a game

    • PostgreSQL with PostGIS
    • Python
    • Power BI Desktop
    • 8 hours

    Prerequisites: Hot and cold spots (s5), Scheduling (e5), Maps and reports (p4)