A feed for a game c1

PostgreSQL with PostGIS
Python
Power BI Desktop
8 hours

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

What could a player meet here, honestly? One pipeline from a GBIF download to a file a game reads, each stage a module you have done, with its honesty rule kept. The worked example is the Tasmanian devil, on s4's hexagons and s5's hot spots; the worked example and the exercise run in your own PostgreSQL with PostGIS, on the lab tables (the setup page), and the Lab adds Python and Power BI.

Concept

Records can only say where people recorded an animal, so a game built on them says that too, in its own fields. The pipeline:

Extract and load (Extract, e1, to Quality and provenance, e4): a GBIF download for the species and region the game needs, loaded into PostGIS in one transaction, re-runnable, checked, and with its credits.
Look before counting (Bias in records, s1): which datasets, which basis of record, any GPS collars? A species whose records are mostly one telemetry dataset gives a feed about that dataset, and only looking tells you so.
Grid and hot spots (Grids, g6, and Hot and cold spots, s5): equal-area hexagons, records per cell, Gi* with the false discovery rate. The feed flags the surviving cells as recording hot spots: places people recorded the animal most, not places it is most common.
Report (Power Query, p1, to Maps and reports, p4): Power BI reads the feed itself, with p1's Web.Contents pattern, and turns its cells into a typed table; the report maps the cells by their centre, sizes them by records, colours them by hot spot, and takes its title from p4's measure. Reading the feed, not a copy of its cells, means the report shows exactly what the game gets.
The feed: one static JSON file per species and region, written by one SQL statement, valid against a published JSON Schema, versioned. Each cell has an encounter weight, its share of the records. The feed carries its own license (CC BY 4.0) and attribution, the download's citation, and every dataset with CC BY 4.0 records in it with its DOI, because the attribution travels with anything derived from them and a game copies the feed alone.
Shipping and refresh: the game ships the file with its own static files, so it reads it from its own origin. Scheduling, e5, rebuilds it; a change to its fields bumps version and the schema. A game with its own PostGIS can run the same statement there instead.

The data: s4_cells (s4's 5 km hexagons over Tasmania's main island, with the devil's record counts), s5_gistar (s5's Gi* per cell), occurrences, datasets and species.

Worked example

The feed for the Tasmanian devil, in two blocks. c1-feed builds the feed with one statement, from s4's 5 km cells and s5's Gi* (so it runs after s5-gistar), and keeps it as a temporary view, c1_feed: one row, one jsonb column, doc. It then returns the feed's summary and its fullest cell. Four parts make the feed:

sp: the species, by its scientific name.
cells: the cells holding a record (c.n > 0), each with its centre in WGS 84 (ST_Transform of the hexagon's centre from EPSG:3577), its weight, rounded to six places (n / sum(n) OVER ()), and whether Gi* made it a hot spot that survives the false discovery rate (z > 0 AND fdr).
credit: the datasets with a CC BY 4.0 record inside those cells, with title, publisher and DOI. ST_Contains finds a record's cell, after ST_Transform puts the record's point in the cells' projection.
The final SELECT: jsonb_build_object writes the document, and jsonb_agg (… ORDER BY id) the cells in a fixed order, so the same data always gives the same file.

Checked 2026-11-02 on PostgreSQL 16.15, PostGIS 3.4.2, GEOS 3.12.1, GDAL 3.8.4.

c1-feed · PostgreSQL with PostGIS

Runs after: s5-gistar

CREATE OR REPLACE TEMP VIEW c1_feed AS
WITH sp AS (
  SELECT id, scientific_name, common_name FROM species WHERE scientific_name = 'Sarcophilus harrisii'
), cells AS (
  SELECT c.id, c.n, c.geom, ST_Transform(ST_Point(c.cx, c.cy, 3577), 4326) AS centre,
         round(c.n::numeric / sum(c.n) OVER (), 6) AS weight,
         g.z > 0 AND g.fdr AS hot_spot
  FROM s4_cells AS c JOIN s5_gistar AS g ON g.id = c.id
  WHERE c.edge_m = 5000 AND c.n > 0
), credit AS (
  SELECT DISTINCT d.title, d.publisher, d.doi
  FROM occurrences AS o
  JOIN sp ON sp.id = o.species_id
  JOIN cells AS c ON ST_Contains(c.geom, ST_Transform(o.geom, 3577))
  JOIN datasets AS d ON d.dataset_key = o.dataset_key
  WHERE o.license = 'CC BY 4.0'
)
SELECT jsonb_build_object(
  'version', 1,
  'species', (SELECT jsonb_build_object('scientific_name', scientific_name, 'common_name', common_name) FROM sp),
  'cell', '5 km hexagon, EPSG:3577',
  'meaning', 'share of the open GBIF records in each cell: where people recorded the animal, not its range',
  'license', 'CC BY 4.0',
  'license_url', 'https://creativecommons.org/licenses/by/4.0/',
  'attribution', 'critwire (https://critwire.com), CC BY 4.0',
  'source', 'GBIF.org (23 October 2026) GBIF Occurrence Download https://doi.org/10.15468/dl.fbpk9w',
  'credit', coalesce((SELECT jsonb_agg(to_jsonb(credit) ORDER BY title, publisher, doi) FROM credit), '[]'),
  'cells', jsonb_agg(jsonb_build_object(
    'id', id, 'lon', round(ST_X(centre)::numeric, 4), 'lat', round(ST_Y(centre)::numeric, 4),
    'records', n, 'weight', weight, 'recording_hot_spot', hot_spot) ORDER BY id)
) AS doc
FROM cells;
SELECT jsonb_array_length(doc -> 'cells') AS cells,
       (SELECT sum((c ->> 'records')::int) FROM jsonb_array_elements(doc -> 'cells') AS c) AS records,
       (SELECT sum((c ->> 'weight')::numeric) FROM jsonb_array_elements(doc -> 'cells') AS c) AS weight_sum,
       (SELECT count(*) FROM jsonb_array_elements(doc -> 'cells') AS c
        WHERE (c ->> 'recording_hot_spot')::boolean) AS hot_spots,
       jsonb_array_length(doc -> 'credit') AS datasets_to_credit,
       octet_length(doc::text) AS bytes,
       (SELECT c::text FROM jsonb_array_elements(doc -> 'cells') AS c
        ORDER BY (c ->> 'records')::int DESC, (c ->> 'id')::int LIMIT 1) AS fullest
FROM c1_feed;

-- result: c1-feed · 1 row · sha256 4eb751b94b5d
cells | records | weight_sum | hot_spots | datasets_to_credit | bytes | fullest
307 | 1972 | 0.999954 | 37 | 7 | 35178 | {"id": 917, "lat": -42.8981, "lon": 147.8865, "weight": 0.12…

In c1.sql, with the blocks it runs after.

307 of the 1,094 cells hold a record, so the feed has 307 cells and 1,972 records: the devil's 2,000, less the 28 that fall outside the main island's cells, as in g6. The weights, rounded to six places, sum to 0.999954, which is why the game divides by their sum. 37 cells are recording hot spots: s5's 39, less 2 cells with no record of their own, which Gi* flagged because their neighbours are full (Gi* counts a cell's neighbours too) and which a feed of recorded cells leaves out. The fullest cell, 917, holds 256 records, 13.0 % of the weight, and is a hot spot. 7 datasets must be credited. The file is 35,178 bytes, small enough to ship with any game; critwire publishes it, and the report in the Lab reads it.

c1-feed-file returns the file itself: doc::text writes the document as one line of JSON, and that one text cell is, byte for byte, the worked feed critwire publishes, c1-feed.json. The result shows the cell's first 60 characters; its sha256 covers the whole file.

c1-feed-file · PostgreSQL with PostGIS

Runs after: s5-gistar, c1-feed

SELECT doc::text AS feed FROM c1_feed;

-- result: c1-feed-file · 1 row · sha256 330d13fc6135
feed
{"cell": "5 km hexagon, EPSG:3577", "cells": [{"id": 6, "lat…

In c1.sql, with the blocks it runs after.

The feed's fields

A game reads these, so each says what it is.

version: 1. A change to the fields bumps it and the schema.
species: the scientific and common name.
cell: the cells' shape, size and projection, 5 km hexagon, EPSG:3577 (Australian Albers, an equal-area projection, so every cell covers the same ground).
meaning: what a weight is, in words a player-facing screen can quote: where people recorded the animal, not its range.
license and license_url: the feed's own license, CC BY 4.0, and its deed: anyone may copy and change the feed, crediting as the next three fields say.
attribution: who made the feed, in the words they ask to be credited with: critwire (https://critwire.com), CC BY 4.0. A feed you make names you.
source: GBIF's citation of the download, with its DOI, which lists every dataset in the download.
credit: every dataset with CC BY 4.0 records in the feed's cells, by title, publisher and DOI (null for a dataset without one), sorted by those three.
cells: one object per cell that holds a record: id (s4's cell id), lon and lat (the cell's centre in WGS 84, four decimal places, about 10 m), records, weight and recording_hot_spot.

A game draws a cell with probability weight ÷ Σ weight: it adds the weights up in order (a cumulative sum), takes one random number between 0 and the total, and picks the first cell whose running sum passes it. A cell missing from the feed means "no record", never "no animal".

The feed's JSON Schema (draft 2020-12), c1-feed.schema.json, states those rules so a program can check them: every field required and no other allowed, version exactly 1, a license URL starting https://, a DOI or null for each credit, at least one cell, each with an integer id, a longitude and latitude in range, at least one record and a weight between 0 and 1. A game's build can validate a new feed against it before shipping it; critwire's end-to-end tests validate the worked feed against it.

Lab

The pipeline again, for a species and region of your own; each stage keeps its honesty rule.

Extract and load (e1 to e4, Python). Choose a species and a region. Build the request with e1's build_request and your species' GBIF key, and run e1's client with your GBIF account (e1.py carries it commented out); then load, check and credit the download as e2, e3 and e4 do, pointing their downloads (e2.py, e3.py, e4.py) at your files instead of the lab's. The rule: the feed's source is your download's citation, with its DOI, and its credit comes from your loaded datasets, never typed by hand. Without a GBIF account, take a species of the lab sample in a region of your own, and keep the course download's citation.
Look before counting (s1). Run s1-devil with your species' name: which datasets, which basis of record, which years. 1,948 of the jaguar's 2,000 records are GPS-collar fixes, so a jaguar feed would describe collared animals' paths. If one dataset dominates, say so in meaning, or leave it out and say that instead.
Grid and hot spots (g6, s5). Copy s4-cells and change the region (island), the species and the projection: EPSG:3577, Australian Albers, is equal-area over Australia only; EPSG:6933 (WGS 84 / NSIDC EASE-Grid 2.0 Global) is equal-area anywhere. Change every 3577 in it, in s4_neighbours and in c1-feed. Then run s5-gistar unchanged. The rule: equal-area cells, so each covers the same ground, and hot spots only after the false discovery rate, called recording hot spots.
The feed. Copy c1-feed and change the species' name, cell (your cells' size and projection), source (your download's citation) and attribution: your name and site, followed by CC BY 4.0, because a feed you make names you, as critwire's names critwire. Keep meaning and the license. To save the file, in the same psql session: \pset format unaligned, \pset tuples_only on, \o my-feed.json, SELECT doc::text FROM c1_feed;, \o. Validate it against the schema before you ship it (Dialects names validators).
The report (p1 to p4). In Power BI Desktop: Home → Get data → Blank query, then Advanced editor, paste c1-report and name the query cells. It reads the worked feed from critwire.com as p1 reads the star; for your own feed, replace the Web.Contents (…) call with File.Contents ( "C:\path\my-feed.json" ) (the Service then needs a gateway to refresh it, as p1's connection to your own PostgreSQL does). It fails, rather than guessing, if a cell lacks a field.
c1-report · Power BI (M)
```
let
    Source = Json.Document(Web.Contents("https://critwire.com", [RelativePath = "learn/labs/c1-feed.json"]), 65001),
    Cells = Table.FromRecords(Source[cells], {"id", "lon", "lat", "records", "weight", "recording_hot_spot"}, MissingField.Error),
    Typed = Table.TransformColumnTypes(Cells, {
        {"id", Int64.Type}, {"lon", type number}, {"lat", type number},
        {"records", Int64.Type}, {"weight", type number}, {"recording_hot_spot", type logical}
    }),
    Species = Table.AddColumn(Typed, "species", each Source[species][common_name], type text),
    Kind = Table.AddColumn(Species, "kind", each if [recording_hot_spot] then "recording hot spot" else "other recorded cell", type text)
in
    Kind
```
The files it reads
File Rows sha256
labs/c1-feed.json 307 6c21ce55882662559b9beb92987a51b3f9decae2034237708298a81c63640429

Not run by critwire: Power BI Desktop is Windows-only.
Json.Document parses the file into records and lists; Table.FromRecords with MissingField.Error makes one row per cell and stops at a cell without one of the six fields. species repeats the feed's common name on each row, for the title; kind spells out the legend, so it never shows a bare True or False.
The map (p4). Set lat and lon to the Latitude and Longitude data categories, Don't summarize (p4, step 2). An Azure Maps visual: lat and lon, records to Size, kind to Legend, weight to Tooltips. The title, as p4's measure: Map title = SELECTEDVALUE ( cells[species] ) & ": " & FORMAT ( SUM ( cells[records] ), "#,0" ) & " open GBIF records in " & COUNTROWS ( cells ) & " cells (where people recorded it, not its range)". Credits: a second blank query on the same file as cells, Table.FromRecords ( Json.Document ( Web.Contents ( "https://critwire.com", [RelativePath = "learn/labs/c1-feed.json"] ) )[credit] ) (for your own feed, the same File.Contents swap), in a table visual, and a text box quoting attribution and source.
Ship and refresh (e5). The game copies the file in with its own static files and reads it from its own origin. Its credits screen shows attribution, source and each credit with its DOI (as a link, https://doi.org/ followed by the DOI). e5's schedule rebuilds the feed; new numbers keep version, a changed field bumps it and the schema.

The files it reads
File	Rows	sha256
labs/c1-feed.json	307	`6c21ce55882662559b9beb92987a51b3f9decae2034237708298a81c63640429`

Result

Your feed: one file, valid against the schema, with your species, your cells' size and projection, you in attribution, your download in source, every dataset with CC BY 4.0 records in credit, and "not its range" in meaning. On the worked feed, the report's title reads "Tasmanian Devil: 1,972 open GBIF records in 307 cells (where people recorded it, not its range)", 37 cells are recording hot spots, and the largest bubble is cell 917, with 256 records.

Exercise

Look before counting, on the feed's fullest cell. Cell 917 holds 13.0 % of the weight: a game would place about one devil in eight there. Who made its records, how, at how many places and in which years? Find the fullest cell from c1_feed, as c1-feed's summary does, and break its devil records down by dataset and basis of record, with the number of distinct places and the first and last year.

Show answer

c1-ex · PostgreSQL with PostGIS

Runs after: s5-gistar, c1-feed

WITH fullest AS (
  SELECT (c ->> 'id')::int AS id
  FROM c1_feed, jsonb_array_elements(doc -> 'cells') AS c
  ORDER BY (c ->> 'records')::int DESC, (c ->> 'id')::int
  LIMIT 1
)
SELECT f.id AS cell, d.title, o.basis_of_record,
       count(*) AS records,
       count(DISTINCT (o.longitude, o.latitude)) AS places,
       min(o.year) AS first_year, max(o.year) AS last_year
FROM fullest AS f
JOIN s4_cells AS c ON c.id = f.id AND c.edge_m = 5000
JOIN occurrences AS o ON ST_Contains(c.geom, ST_Transform(o.geom, 3577))
JOIN species AS s ON s.id = o.species_id
JOIN datasets AS d ON d.dataset_key = o.dataset_key
WHERE s.scientific_name = 'Sarcophilus harrisii'
GROUP BY f.id, d.title, o.basis_of_record
ORDER BY records DESC, d.title, o.basis_of_record;

-- result: c1-ex · 3 rows · sha256 161c0f8ea4f7
cell | title | basis_of_record | records | places | first_year | last_year
917 | Tasmanian Natural Values Atlas | OBSERVATION | 225 | 90 | 2005 | 2011
917 | Tasmanian Museum and Art Gallery provider for OZCAM | MATERIAL_SAMPLE | 22 | 1 | 2006 | 2009
917 | Tasmanian Natural Values Atlas | OCCURRENCE | 9 | 9 | 2007 | 2008

In c1.sql, with the blocks it runs after.

Check

The answer: jsonb_array_elements turns the feed's cells back into rows, and ORDER BY … LIMIT 1 takes the fullest, the same tie rule as c1-feed's fullest. The records are placed in that cell as c1-feed places them, with ST_Contains after ST_Transform. count(DISTINCT (o.longitude, o.latitude)) counts places: records at the same coordinates count once. The block runs after s5-gistar and c1-feed, so the view exists.

Three rows, 256 records. The Tasmanian Natural Values Atlas's observations supply 225 of them, at 90 places, all made from 2005 to 2011; 9 more atlas records, at 9 places, from 2007 and 2008. The Tasmanian Museum and Art Gallery's 22 material samples share one place, from 2006 to 2009. No record in the cell is later than 2011. The cell is full because people recorded devils there, intensively, for a few years; a weight can't tell such a survey from many devils, and says nothing about the years since. meaning already says what a weight is. A game designer who reads this may still choose to keep it, cap a cell's weight, or note the years: each is honest once it is stated.

Dialects

Python: json.dumps(doc, separators=(", ", ": ")) spaces the text as jsonb does, but jsonb orders an object's keys by length, then byte by byte ("id" before "lat", "cell" before "cells"), so a byte-identical file sorts them the same way. PostgreSQL's round(numeric, 6) rounds halves away from zero; Python's round on a float rounds the binary value, halves to even, so use decimal.Decimal with ROUND_HALF_UP for the same weights.
DuckDB: json_object builds the document, and to_json(list(… ORDER BY id)) the cells in order (json_group_array takes no ORDER BY); the spatial extension adds ST_Transform and ST_Contains. Its JSON text has no space after : or , and keeps keys in the order written: the same document, not the same bytes.
JSON Schema validators: ajv in JavaScript (critwire's tests validate the worked feed with it) and Python's jsonschema package (not in the course's pins) both read draft 2020-12.
Excel (Windows): Data → Get Data → From Other Sources → Blank Query runs the same M, as in p1; 3D Maps maps the cells. Microsoft's list of Excel for Mac's Power Query sources has no web source.
Tableau: the JSON file connector reads cells as a table; put lat and lon on the map as dimensions, records on Size and recording_hot_spot on Colour.
The game (JavaScript): with total the weights' sum, let r = Math.random() * total; then cells.find((c) => (r -= c.weight) < 0) ?? cells.at(-1) draws a cell; the fallback catches a float that passes every cell.