Set up your tools

The course runs on up to four tools. Each is set up once, and every module names the ones it uses. Foundations needs only this browser.

Every file these tools read comes from one place. critwire commits its data as snapshots in its repository; one offline build turns them into the published files, and each tool reads its own:

The browser

Nothing to install. A current desktop browser opens a module page, and its SQL runs there in DuckDB, which critwire serves itself: no request leaves critwire.com except for the animals' photos. Each visit builds the database once, from four published files: /v1/species.parquet, /v1/countries.parquet, /learn/data/datasets.csv and /learn/data/occurrences.parquet. The page also fetches /v1/range-listings.parquet, which builds no table: a few lesson blocks read that file in place. Every learner starts from the same tables, and a reload starts fresh. A result keeps at most 10,000 rows. Stop ends a long query, and Reset database rebuilds the tables, both by restarting the database. The browser's tables lists every column.

This query counts the rows of every table, and of the view species_taxonomy. Run it: the same numbers mean your browser built the same database as everyone else's.

Coordinates as numbers. The browser has no spatial functions (the ST_ family): DuckDB's spatial extension is large, so critwire doesn't serve it. The foundations still use longitude and latitude as plain numbers: a bounding box is longitude BETWEEN … AND latitude BETWEEN …, floor(longitude / 10) and floor(latitude / 10) put records into 10° boxes, and the haversine formula gives the distance along the Earth's surface. Anything with shapes (outlines, projections, spatial joins and indexes) runs in PostGIS.

PostgreSQL with PostGIS, on your computer

The database architecture and GIS modules run in PostgreSQL, a database server, with PostGIS, its spatial extension, on your own computer. The labs are tested on PostgreSQL 16 with PostGIS 3.4. On Windows, the simplest way to get exactly that pair is Docker.

With Docker Desktop (recommended)

Install Docker Desktop with its WSL 2 backend. Then, in PowerShell:

# PostgreSQL 16 with PostGIS 3.4, the tested pair, reachable only from this computer.
docker run -d --name learn-pg -e POSTGRES_PASSWORD=choose-a-password -p 127.0.0.1:5432:5432 -v learn-pg:/var/lib/postgresql/data postgis/postgis:16-3.4
docker exec learn-pg psql -U postgres -c "CREATE DATABASE learn"
docker exec learn-pg psql -U postgres -d learn -c "CREATE EXTENSION postgis" -c "CREATE EXTENSION postgis_raster"
# The lab file: every lab table, in one transaction.
curl.exe -fLo learn_curriculum.sql https://critwire.com/learn/data/postgis/learn_curriculum.sql
docker cp learn_curriculum.sql learn-pg:/tmp/
docker exec learn-pg psql -X -U postgres -d learn -v ON_ERROR_STOP=1 -f /tmp/learn_curriculum.sql

The image postgis/postgis:16-3.4 is the tested pair, but it hasn't been rebuilt since October 2024, so its PostgreSQL minor release is old. That is fine for a lab only your own computer can reach (the 127.0.0.1: in -p), and for nothing else. The tags 16-3.5 and 16-3.6 run the labs too, but their newer GEOS (the geometry library under PostGIS) can change the last digits of some measurements, so a result may differ slightly from the one printed. PowerShell has no < redirection, so the file is copied into the container and read with -f. On Linux or macOS the same commands work in a terminal, with curl for curl.exe. The image is built for x86-64 only.

Without Docker

Install PostgreSQL 16 with the EDB installer, then run Stack Builder → Spatial Extensions → the PostGIS 3.4 or 3.5 bundle for PostgreSQL 16. If port 5432 is taken (by another PostgreSQL, or by ArcGIS), choose 5433 in the installer and add -p 5433 to each psql line below.

$psql = "C:\Program Files\PostgreSQL\16\bin\psql.exe"
& $psql -U postgres -c "CREATE DATABASE learn"
& $psql -U postgres -d learn -c "CREATE EXTENSION postgis" -c "CREATE EXTENSION postgis_raster"
curl.exe -fLo learn_curriculum.sql https://critwire.com/learn/data/postgis/learn_curriculum.sql
& $psql -X -U postgres -d learn -v ON_ERROR_STOP=1 -f learn_curriculum.sql

psql on Windows talks to the server in the console's code page unless told otherwise. The lab file sets its own encoding to UTF-8, so names like "Côte d'Ivoire" arrive intact either way.

Loading, and loading again

learn_curriculum.sql (its size is on the lab data page) builds the schema learn_curriculum in one transaction: it drops the schema, creates it again, loads every table, adds the keys and indexes, and checks every table's row count before it commits. A failed load changes nothing. A second load replaces the schema, including any table you made in it, so keep your own work in another schema. Another schema keeps your tables, but not what depends on the lab: a view, foreign key, trigger or policy of yours that reads learn_curriculum would be dropped with it. The lab file refuses that reload instead, naming each such object, and changes nothing; move or drop them, then load again. The lab schema lists every table and column.

Connection settings

Any lab file you run with psql finds the database through the standard environment variables of libpq, PostgreSQL's client library, so the same file runs on your computer and on critwire's test server. In PowerShell, for one session:

$env:PGHOST = "localhost"; $env:PGPORT = "5432"; $env:PGUSER = "postgres"; $env:PGDATABASE = "learn"
# The password goes in %APPDATA%\postgresql\pgpass.conf, one line: localhost:5432:learn:postgres:choose-a-password

On Linux or macOS: export PGHOST=localhost PGPORT=5432 PGUSER=postgres PGDATABASE=learn, and the same line in ~/.pgpass, readable only by you (chmod 600 ~/.pgpass).

Privileges

The postgres user is a superuser and can do everything the labs do. As another user, you need:

• CONNECT, CREATE and TEMPORARY on the database, to connect, to load the lab and to make temporary tables (the database's owner has them; otherwise, as the superuser: GRANT CONNECT, CREATE, TEMPORARY ON DATABASE learn TO you;), and ownership of learn_curriculum once it exists;
• CREATEROLE, for the labs that create roles: as the superuser, once, ALTER ROLE you CREATEROLE;, and on PostgreSQL 16 and later also ALTER ROLE you SET createrole_self_grant = 'set, inherit';, so you can act as the roles you create (SET ROLE) and drop what they hold (the roles module explains);
• nothing more for the raster labs: their raster clips are stored in the database itself.

Give the labs a database of their own, one you could drop and create again, and keep other work out of it. CREATEROLE applies to the whole server, not one database, so give it to a user you keep for the labs. Every role or schema a lab creates is named learn_… and removed by the same block, so the labs leave nothing in your database besides learn_curriculum.

Check your lab

Connect your SQL client (psql, pgAdmin, DBeaver or VS Code) to the database learn and run these four checks in order. They are also one file, pg-checks.sql: psql -X -d learn -f pg-checks.sql. That needs psql on your PATH, which neither route above gives on Windows. With the EDB installer, write & $psql for psql. With Docker, psql is inside the container: docker exec -it learn-pg psql -U postgres -d learn opens it, and a file goes in as the lab file did (docker cp pg-checks.sql learn-pg:/tmp/, then docker exec learn-pg psql -X -U postgres -d learn -f /tmp/pg-checks.sql). Each result below is what critwire's own run of the block printed.

Every PostgreSQL lab starts with this session block. It puts learn_curriculum first on the search path, so plain table names find the lab's tables, while PostGIS's functions still come from public.

Checked 2026-11-02 on PostgreSQL 16.15, PostGIS 3.4.2, GEOS 3.12.1, GDAL 3.8.4; Python 3.12.3 with requirements.txt sha256 d5bbe28024cc.

SET search_path = learn_curriculum, public;
SHOW search_path;

-- result: pg-session · 1 row · sha256 791623fd72db
search_path
learn_curriculum, public

The rows in every lab table, 16 counts. The page prints the first five; the lab file checked all 16 before it committed, so a lab that loaded has them.

SELECT 'taxa' AS table_name, count(*) AS n_rows FROM taxa
UNION ALL SELECT 'species', count(*) FROM species
UNION ALL SELECT 'sources', count(*) FROM sources
UNION ALL SELECT 'species_sources', count(*) FROM species_sources
UNION ALL SELECT 'countries', count(*) FROM countries
UNION ALL SELECT 'species_countries', count(*) FROM species_countries
UNION ALL SELECT 'datasets', count(*) FROM datasets
UNION ALL SELECT 'occurrences', count(*) FROM occurrences
UNION ALL SELECT 'country_shapes', count(*) FROM country_shapes
UNION ALL SELECT 'admin1_shapes', count(*) FROM admin1_shapes
UNION ALL SELECT 'biomes', count(*) FROM biomes
UNION ALL SELECT 'ecoregions', count(*) FROM ecoregions
UNION ALL SELECT 'habitat_classes', count(*) FROM habitat_classes
UNION ALL SELECT 'country_habitats', count(*) FROM country_habitats
UNION ALL SELECT 'occurrence_habitats', count(*) FROM occurrence_habitats
UNION ALL SELECT 'habitat_clips', count(*) FROM habitat_clips
ORDER BY table_name;

-- result: pg-smoke · 16 rows · sha256 958789792d50
table_name | n_rows
admin1_shapes | 294
biomes | 14
countries | 250
country_habitats | 5578
country_shapes | 247
… 11 more rows

Labs that create roles or schemas clean up in the same block, so running a block twice, or stopping halfway through a module, leaves nothing behind. This check creates a read-only role, reads a table as that role, and removes it: DROP OWNED BY takes away what the role was granted, and only then can DROP ROLE remove it. If it fails with "permission denied to create role", see Privileges above.

CREATE ROLE learn_smoke NOLOGIN;
GRANT USAGE ON SCHEMA learn_curriculum TO learn_smoke;
GRANT SELECT ON species TO learn_smoke;
SET ROLE learn_smoke;
SELECT current_user AS role, count(*) AS species FROM species;
RESET ROLE;
DROP OWNED BY learn_smoke;
DROP ROLE learn_smoke;
SELECT count(*) AS learn_smoke_left FROM pg_roles WHERE rolname = 'learn_smoke';

-- result: pg-role-smoke · 1 row · sha256 fcfe7fd06dfa
learn_smoke_left
0

Most lab blocks are run inside a transaction that critwire's tests roll back. The transaction labs manage their own, with BEGIN and COMMIT or ROLLBACK, exactly as you type them in psql. This check rolls its table back, so it is gone afterwards. A block that needs earlier blocks names them under its heading, as Runs after: run those first, in the same transaction. The page's download does it for you.

BEGIN;
CREATE TABLE tx_smoke (id integer PRIMARY KEY);
INSERT INTO tx_smoke VALUES (1), (2);
ROLLBACK;
SELECT to_regclass('tx_smoke') IS NULL AS rolled_back;

-- result: pg-tx-smoke · 1 row · sha256 a90c4cf411fd
rolled_back
true

Python

The spatial statistics and ETL modules run Python scripts that read the same lab database. They run in a virtual environment: a folder holding one Python and exactly the package versions the labs were tested with. uv builds it with the same commands on Windows, Linux and macOS, installs Python 3.12 itself, and installs the versions requirements.txt pins. --no-build makes it stop with an error rather than compile a package that has no prebuilt version for your computer.

Windows (PowerShell)

powershell -ExecutionPolicy ByPass -c "irm https://astral.sh/uv/0.13.5/install.ps1 | iex"
$env:Path = "$HOME\.local\bin;$env:Path"   # so this terminal finds uv; new ones already do
mkdir $HOME\learn; cd $HOME\learn
curl.exe -fLo requirements.txt https://critwire.com/learn/labs/requirements.txt
uv python install 3.12
uv venv --python 3.12
uv pip sync --no-build requirements.txt
.venv\Scripts\python -c "import geopandas, esda; print(geopandas.__version__, esda.__version__)"

Linux and macOS

curl -LsSf https://astral.sh/uv/0.13.5/install.sh | sh
source $HOME/.local/bin/env   # so this terminal finds uv; new ones already do
mkdir -p ~/learn && cd ~/learn
curl -fLo requirements.txt https://critwire.com/learn/labs/requirements.txt
uv python install 3.12
uv venv --python 3.12
uv pip sync --no-build requirements.txt
.venv/bin/python -c "import geopandas, esda; print(geopandas.__version__, esda.__version__)"

The last line prints two version numbers. GeoPandas and esda import most of the rest, so if they load, the environment is complete.

The environment

requirements.txt pins 44 packages, each to one version: GeoPandas and Shapely for shapes, pyproj for coordinate systems, psycopg to talk to PostgreSQL, and PySAL's libpysal, esda and pointpats for spatial statistics, with what they need. Every pin has a prebuilt package for Windows on x86-64, Linux on x86-64 and ARM, and macOS 14 or later on Apple silicon. Intel Macs aren't covered: pyproj 3.8.0 has no build for them. uv pip sync installs exactly the file and removes anything else, so run it again whenever you download a newer requirements.txt.

Scripts and notebooks

Each module's lab is one .py file, split into cells by # %% lines, and the same cells as a notebook (.ipynb). Run a file whole (.venv\Scripts\python setup.py on Windows, .venv/bin/python setup.py elsewhere), or cell by cell in VS Code or Jupyter. VS Code asks, the first time, to add ipykernel, the package that lets it run cells, to the environment. Let it: nothing here depends on it, and the next uv pip sync removes it again. Jupyter isn't in the pins either. In the same folder, uv pip install --constraint requirements.txt jupyterlab adds it, ipykernel included, without moving a pin, and .venv\Scripts\jupyter lab (.venv/bin/jupyter lab elsewhere) starts it. The next uv pip sync removes that too.

A lab file finds the database through the same PG* variables as psql (Connection settings, above), with the password in the same pgpass file or in PGPASSWORD. critwire runs every Python lab as an ordinary user with the privileges listed above, not as a superuser.

Check your environment

Every Python lab starts with this helper. It connects and sets the search path, so plain table names find the lab tables in learn_curriculum and PostGIS's functions in public. It prints nothing.

import psycopg


def connect():
    """A connection to the lab database: plain table names find learn_curriculum's tables, then PostGIS in public."""
    return psycopg.connect(options="-c search_path=learn_curriculum,public")

-- result: py-connect · python · sha256 e3b0c44298fc

This check prints the search path through the helper, then the versions of Python and the main packages. The same lines mean your environment matches the tested one, except perhaps Python's last number: uv python install 3.12 installs the newest 3.12 release. These blocks and the next two are one file, setup.py, also as a notebook, setup.ipynb.

import platform
from importlib.metadata import version

with connect() as conn:
    print("search_path:", conn.execute("SHOW search_path").fetchone()[0])
print("python:", platform.python_version())
for package in ["psycopg", "pandas", "pyarrow", "shapely", "pyproj", "pyogrio", "geopandas", "libpysal", "esda", "pointpats"]:
    print(f"{package}: {version(package)}")

-- result: py-smoke · python · sha256 f75ba856b9f7
search_path: learn_curriculum,public
python: 3.12.3
psycopg: 3.3.6
pandas: 3.1.0
pyarrow: 26.0.0
shapely: 2.2.0
pyproj: 3.8.0
pyogrio: 0.13.0
geopandas: 1.2.0
libpysal: 4.15.1
esda: 2.10.0
pointpats: 2.6.0

Moving data to another GIS

To take lab tables into ArcGIS Pro, QGIS or any tool built on GDAL, write a GeoPackage: one file, an open standard, holding layers with their geometry, fields and coordinate system. This block writes the 5 km hexagons that s4 builds over Tasmania, each with its count of devil records n and the value y = ln(1 + n) that s4 tests, to s4_cells.gpkg in the folder you run it from, then reads the file back. It needs the s4_cells table, so setup.py builds it first, right after the helper, as every lab file that uses it does.

import geopandas as gpd
import pyogrio
from sqlalchemy import create_engine

engine = create_engine("postgresql+psycopg://", creator=connect)
cells = gpd.read_postgis("SELECT id, n, y, geom FROM s4_cells WHERE edge_m = 5000 ORDER BY id", engine, geom_col="geom")
cells.to_file("s4_cells.gpkg", layer="s4_cells", driver="GPKG")

info = pyogrio.read_info("s4_cells.gpkg", layer="s4_cells")
back = gpd.read_file("s4_cells.gpkg", layer="s4_cells")
print("layer: s4_cells")
print("crs:", back.crs.to_string())
print("geometry:", info["geometry_type"])
print("fields:", ", ".join(info["fields"]))
print("rows:", len(back), "records:", int(back["n"].sum()))

-- result: gpkg-export · python · sha256 d91667382d4a
layer: s4_cells
crs: EPSG:3577
geometry: Polygon
fields: id, n, y
rows: 1094 records: 1972

arcpy notes (ArcGIS Pro)

For anyone with ArcGIS Pro (the Basic license is enough). This is optional: no lab needs it.

• arcpy, Pro's Python package, lives in Pro's own conda environment, arcgispro-py3. Don't pip-install into it. If you want a lab package there, clone the environment in Pro (Settings → Package Manager → Environment Manager) and add the package to the clone.
• Keep the uv environment for the labs, and move data between the two as a GeoPackage (the block above). Pro lists a GeoPackage's layers as main.<name>.
• Pro Basic's Spatial Statistics toolbox covers s4 and s5: Spatial Autocorrelation (Global Moran's I) and Hot Spot Analysis (Getis-Ord Gi*). Pro reports Moran's z-score under the randomization assumption, so compare it with esda's z_rand, not z_norm (s4 prints both).

This block runs s4's global Moran's I in Pro's Python window, on the GeoPackage written above. critwire can't run it (Pro is licensed and Windows-only), so setup.py carries it commented out and the notebook as text.

# s4's global Moran's I on the 5 km hexagons, in ArcGIS Pro's Python window. Fixed distance band of 10,000 m
# (twice the edge, as s4), distances between polygon centroids, row-standardized weights.
import arcpy

cells = r"C:\Users\you\learn\s4_cells.gpkg\main.s4_cells"
result = arcpy.stats.SpatialAutocorrelation(
    cells, "y", "NO_REPORT", "FIXED_DISTANCE_BAND", "EUCLIDEAN_DISTANCE", "ROW", "10000 Meters"
)
moran_i, z_score, p_value = (float(result.getOutput(k)) for k in range(3))
print(f"I = {moran_i:.6f}  z = {z_score:.6f}  p = {p_value:.3g}")
# Expected, from s4: I = 0.273296 and z close to esda's z_rand, 15.246590.

Power BI Desktop

Power BI Desktop is free and runs on Windows only. It reads the star schema as CSV files under https://critwire.com/learn/data/star/: no gateway, no account, no database connection. To check your setup, make a blank query (Home → Get data → Blank query → Advanced editor) and paste this in. The first time, Power BI asks how to connect to critwire.com: choose Anonymous, and privacy level Public.

let
    Source = Web.Contents("https://critwire.com", [RelativePath = "learn/data/star/dim_country.csv"]),
    Rows = Csv.Document(Source, [Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]),
    Promoted = Table.PromoteHeaders(Rows, [PromoteAllScalars = true]),
    Typed = Table.TransformColumnTypes(Promoted, {{"country_code", type text}, {"name", type text}, {"continent", type text}, {"region", type text}, {"subregion", type text}}, "en-US")
in
    Typed

It should show 251 rows: 250 countries, plus ZZ, the star's "unknown" member for records in no country. Namibia's code NA stays text. Three choices let it refresh anywhere:

• A fixed base URL, with the file in RelativePath. The Power BI Service can then refresh the published report on a schedule, with anonymous access. A whole URL assembled from text ("https://critwire.com/learn/data/star/" & file) is a dynamic data source, which the Service won't refresh.
• Encoding = 65001 (UTF-8) and QuoteStyle.Csv: names with accents, and commas inside quotes, are read as written.
• The culture "en-US" on the type change: critwire's files write 12.5, which a computer set to a comma-decimal locale would otherwise misread.

critwire checks every file a Power BI query names against the star's manifest (header, keys, rows and sha256) on every build. It can't run Power BI itself, which has no Linux version, so the queries, measures and visuals aren't run by critwire.

Schema: the browser's tables

Every column, in words · The diagram alone

Close

Lab schema: the PostGIS tables

Every column, in words · The diagram alone

Close