The tables the PostgreSQL labs run on: schema learn_curriculum in a database on your own computer, built by one file, learn_curriculum.sql. Set up your tools shows how to load it.
It holds the same keyed tables as the browser's schema, in PostgreSQL's types (text for VARCHAR, float8 for DOUBLE; a dataset's key is a uuid), plus the shapes the GIS modules join records to: country outlines, admin-1 areas (states and provinces) for nine countries, biomes and ecoregions, and the habitat map (Jung et al. 2020, v004): its classes, each country's estimated area per class, each record's class, and two clips of the 100 m map, all of Costa Rica and all of Tasmania, stored as raster tiles. Every shape is a geometry in WGS 84 (SRID 4326) with a GiST index; each clip is a raster stored in the database, one band of class codes in the same system. The habitat areas and classes are estimates from a map, not measurements. A record's point is a generated column, computed from its longitude and latitude, so it can never disagree with them.
The diagram
Read it as the browser's diagram: PK primary key, FK foreign key, UK unique key; || exactly one, |o zero or one, o{ zero or more, |{ one or more, with the crow's foot on the table that holds the foreign key.
The schema v2 tables in PostgreSQL types, plus the shapes: country outlines, admin-1 areas, biomes, ecoregions and the habitat map: its classes, each country's estimated area per class, each record's class, and two clips stored as raster tiles. Open the diagram alone to zoom in.
The relationships, in words
Each taxa row points at zero or one taxa row (another row of the same table); a taxa row has zero or more taxa rows: “parent of”.
Each species row points at exactly one taxa row; a taxa row has zero or more species rows: “genus of”.
Each species_countries row points at exactly one species row; a species row has zero or more species_countries rows: “recorded in”.
Each species_countries row points at exactly one countries row; a countries row has zero or more species_countries rows: “has records of”.
Each species_sources row points at exactly one species row; a species row has zero or more species_sources rows: “fields from”.
Each species_sources row points at exactly one sources row; a sources row has one or more species_sources rows: “supplies”.
Each occurrences row points at exactly one species row; a species row has zero or more occurrences rows: “recorded as”.
Each occurrences row points at exactly one datasets row; a datasets row has zero or more occurrences rows: “publishes”.
Each occurrences row points at zero or one countries row; a countries row has zero or more occurrences rows: “contains”.
Each country_shapes row points at exactly one countries row; a countries row has at most one country_shapes row: “outlined by”.
Each admin1_shapes row points at exactly one countries row; a countries row has zero or more admin1_shapes rows: “divided into”.
Each ecoregions row points at zero or one biomes row; a biomes row has zero or more ecoregions rows: “groups”.
Each habitat_classes row points at zero or one habitat_classes row (another row of the same table); a habitat_classes row has zero or more habitat_classes rows: “level 1 of”.
Each country_habitats row points at exactly one countries row; a countries row has zero or more country_habitats rows: “estimated to hold”.
Each country_habitats row points at exactly one habitat_classes row; a habitat_classes row has zero or more country_habitats rows: “estimated in”.
Each occurrence_habitats row points at exactly one occurrences row; a occurrences row has at most one occurrence_habitats row: “habitat at”.
Each occurrence_habitats row points at zero or one habitat_classes row; a habitat_classes row has zero or more occurrence_habitats rows: “class at”.
The view species_taxonomy
As in the browser: one row per species with its genus, family, order and class, walked up taxa. A view stores no rows, so it isn't drawn.
Tables the lessons make
Some lessons create tables of their own in the lab: a grid, a staging table, a star. They aren't part of the loaded lab, so they aren't drawn, and the check excuses exactly these names:
s4_cells
tx_smoke
field_notes
occurrences_by_year
sightings
sightings_runs
gbif_staging
occurrences_all
dim_species
dim_country
dim_dataset
dim_year
dim_habitat_class
fact_records
fact_species_country
fact_country_habitat
dim_species_history
The data dictionary
The tables that differ from the browser's: new ones, and ones with other columns. The others have the browser's columns, in PostgreSQL types; their entries are on the browser's page:
