critwire · Learn · Learn's data

Bias in records s1

Prerequisites: Spatial joins (g3)

Before any statistic, know what the records are: basis of record, the datasets behind a species, GPS collars, recording effort, and the honest way to word a result. The first part, the listings step and the exercise run in this browser, on the tables the Schema page describes and, for the listings, one published file; the jaguar's and the devil's records run in your own PostgreSQL with PostGIS, on the lab tables (the setup page).

Concept

Before any statistic, know what the records are:

Worked example

The sample by basis of record, in the browser:

sum(count(*)) OVER () is a window over the grouped rows (Window functions, f4): the total of all groups, beside each one, so each row can show its share. Human observations are 43.5 % of the 40,395 records and preserved specimens 26.2 %. Machine observations are 20.8 %, from only 22 of the sample's 71 species: a few species carry nearly all the devices. The two rows below the cut are MATERIAL_CITATION (441) and OCCURRENCE (192).

Lab

  1. How much of each species' records one dataset holds, for the species with at least 100 records, in the browser:

    Of the 38 species with at least 100 records, 17 have more than half from one dataset and 6 have 90 % or more. Livingstone's flying fox's 109 come from a single dataset, the golden-rumped sengi's 98.1 % from one biodiversity survey, the jaguar's 97.4 % from one dataset. For these species, any pattern in the records is first a pattern in one project's work.

  2. The jaguar's records: GPS collars against everything else, with the places and years each covers. The title test (ILIKE '%GPS%') is a shortcut that works on this sample; on other data, read each dataset's description before sorting it:

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

    s1-jaguar · PostgreSQL with PostGIS 

    SELECT CASE WHEN o.basis_of_record = 'MACHINE_OBSERVATION' AND d.title ILIKE '%GPS%'
            THEN 'GPS collars' ELSE 'everything else' END AS source,
       count(DISTINCT o.dataset_key) AS datasets,
       count(*) AS records,
       round(100.0 * count(*) / sum(count(*)) OVER (), 1) AS pct,
       count(DISTINCT o.country_code) AS countries,
       count(DISTINCT (o.longitude, o.latitude)) AS locations,
       min(o.year) AS first_year,
       max(o.year) AS last_year
FROM occurrences AS o
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 = 'Panthera onca'
GROUP BY 1
ORDER BY records DESC;
    -- result: s1-jaguar · 2 rows · sha256 c6055b9de80b
source | datasets | records | pct | countries | locations | first_year | last_year
GPS collars | 1 | 1948 | 97.4 | 5 | 1948 | 2001 | 2016
everything else | 24 | 52 | 2.6 | 8 | 47 | 1949 | 2025

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

    The collar dataset holds 1,948 records at 1,948 different points in 5 countries, between 2001 and 2016; the other 24 datasets hold 52 records (8 countries, 1949 to 2025). count(DISTINCT (o.longitude, o.latitude)) counts distinct pairs of coordinates, written as one row value. Every collar fix is a new point, so nothing in a list of locations shows that they come from collared animals; only the dataset does: the Jaguar Movement Database, 134,690 fixes from 117 jaguars in all, of which the sample keeps these 1,948. Any map of the jaguar's records is mostly a map of those animals' paths.

  3. The devil's records, which the next four modules analyse, by dataset and basis of record:

    s1-devil · PostgreSQL with PostGIS 

    SELECT d.title, o.basis_of_record,
       count(*) AS records,
       round(100.0 * count(*) / sum(count(*)) OVER (), 1) AS pct,
       min(o.year) AS first_year,
       max(o.year) AS last_year
FROM occurrences AS o
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 d.title, o.basis_of_record
ORDER BY records DESC, d.title, o.basis_of_record;
    -- result: s1-devil · 12 rows · sha256 889e2a2404f5
title | basis_of_record | records | pct | first_year | last_year
Tasmanian Natural Values Atlas | OBSERVATION | 1367 | 68.4 | 1599 | 2012
Tasmanian Museum and Art Gallery provider for OZCAM | MATERIAL_SAMPLE | 558 | 27.9 | 2000 | 2023
Tasmanian Natural Values Atlas | OCCURRENCE | 46 | 2.3 | 2005 | 2010
Tasmanian Museum and Art Gallery provider for OZCAM | PRESERVED_SPECIMEN | 10 | 0.5 | NULL | NULL
Museums Victoria provider for OZCAM | PRESERVED_SPECIMEN | 5 | 0.3 | NULL | NULL
… 7 more rows

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

    The devil's 2,000 records are 70.7 % the Tasmanian Natural Values Atlas (1,367 OBSERVATION and 46 OCCURRENCE) and 27.9 % material samples, such as tissue, at the Tasmanian Museum and Art Gallery (558 MATERIAL_SAMPLE, 2000 to 2023); the other 9 rows are museum specimens and one observation. The atlas's first year, 1599, comes before the first European sighting of Tasmania (1642): a date to check before trusting a time series (Quality and provenance, e4). 20 of the 28 museum specimens have no year at all.

  4. What the records can't say on their own is where a source expects the animal. For that there's a different kind of list. /v1/range-listings holds what named sources list: each row is one source's claim about one animal in one country. Every mammal in critwire has rows from the Mammal Diversity Database (MDD), the American Society of Mammalogists' open list of mammal species; Licenses gives its license and where its data came from. MDD's rows here all say native or introduced before 1500. No lab table holds this file, so this step runs in the browser and reads it where it's published, with read_parquet('range-listings.parquet'). For the mammals, MDD's rows marked present against species_countries, in both directions: listed asks how many of those rows have open records, and recorded asks how many countries with open records have no row from any source.

    On this page, native, introduced and range words describe only /v1/range-listings rows, worded as what a named source lists ("MDD lists it in Kenya as native or introduced before 1500"); records are still never range.

    MDD lists these animals as present in 364 animal and country pairs: 275 of them (75.5 %) have open records and 89 have none. A listed country with no record shows nothing either way: the animal may be gone, or nobody may have shared a record. The other way round, the mammals have open records in 379 animal and country pairs, and 94 of those are on no listing from any source. Each of the 94 is a question to ask, not a finding: a captive animal and a misplaced record look the same here, and no row means critwire holds no open listing, not that no source anywhere lists the animal there.

    The same split per animal, the largest gaps first:

    These 50 rows start with the Aardvark: MDD lists it in 42 countries, 19 with open records and 23 without. The African Savanna Elephant has 10 of 34 without, and the Giant Pangolin 10 of 17. A gap says where open records were shared, and nothing about whether the animal is still there.

    Sources can also disagree. A conflict is a claim against its opposite, for one animal and country, on rows with no narrower area: native against introduced, or present against absent. Among the mammals, one place qualifies, and the block shows both its rows:

    For the Black Rhinoceros in South Africa, South Africa's list in the Global Register of Introduced and Invasive Species says introduced, and MDD says native or introduced before 1500. Each row is a named source's claim with its own link; both stay in the file, and this lesson doesn't pick one. This block reads mammals only, so the axolotl's conflict in Germany isn't here; the query under Range listings lists every animal's.

Result

Human observations are 43.5 % of the sample's 40,395 records, preserved specimens 26.2 % and machine observations 20.8 %, from only 22 species. Of the 38 species with at least 100 records, 17 have more than half from one dataset and 6 have 90 % or more. The jaguar's 97.4 % is one GPS-collar dataset: 1,948 records at 1,948 points in 5 countries between 2001 and 2016, against 52 records from 24 other datasets. The devil's records are 70.7 % one atlas and 27.9 % one museum's material samples. So the next four modules measure where the atlas and the museum's field teams recorded devils, and say so; the honest sentence is always about records. Listings are a different kind of claim, by a named source: of MDD's 364 rows marked present for the mammals, 275 (75.5 %) have open records and 89 have none, and 94 of the mammals' 379 animal and country pairs with open records are on no listing from any source.

Exercise

Datasets aren't the only thing that piles up: so do years. For each species with at least 100 records, find the year with the most records (the earliest one on a tie), how many records it holds, and its share of all the species' records. Records with no year count in the total but can't be a busiest year. Run it in the browser. Which species have half or more of their records in one year, and why?

Show answer
WITH totals AS (
  SELECT species_id, count(*) AS records
  FROM occurrences
  GROUP BY species_id
  HAVING count(*) >= 100
), per_year AS (
  SELECT species_id, year, count(*) AS n,
         row_number() OVER (PARTITION BY species_id ORDER BY count(*) DESC, year) AS place
  FROM occurrences
  WHERE year IS NOT NULL
  GROUP BY species_id, year
)
SELECT s.common_name, t.records, p.year AS top_year, p.n AS top_year_records,
       round(100.0 * p.n / t.records, 1) AS top_pct
FROM totals AS t
JOIN per_year AS p ON p.species_id = t.species_id AND p.place = 1
JOIN species AS s ON s.id = t.species_id
ORDER BY top_pct DESC, t.records DESC, s.id;

Check

The answer: one count over all records per species, and one count per species and year that ranks the years with row_number() over the grouped rows.

Four species have half or more of their records in one year: the golden poison frog (251 of 299 in 2017, 83.9 %), Grévy's zebra (257 of 321 in 2008, 80.1 %), the tiger (1,202 of 2,000 in 2021, 60.1 %) and Livingstone's flying fox (61 of 109 in 2016, 56 %). Each peak is one project: the frog's year is one population survey in Colombia, the zebra's one aerial census in Kenya, and 1,198 of the tiger's are photographs of tigers posted on Facebook in India, gathered into one dataset. The cheetah's busiest year, 2007, is 919 fixes from one tracking study. A chart of records by year shows when projects ran, not when animals were common. Without WHERE year IS NOT NULL, the records with no year form a group of their own, and for 11 species it outnumbers every real year (the spiny softshell turtle's 362 against 87 in 1976), so the "busiest year" comes out empty.

Dialects

Schema: the browser's tables

critwire Learn schema v2, the tables in the browser

Every column, in words · The diagram alone

Lab schema: the PostGIS tables

critwire Learn lab database, PostGIS schema learn_curriculum

Every column, in words · The diagram alone