Baselight

Gapminder Concepts And Entities

Concept dictionary and entity (geo) reference tables for joining with per-indicator datasets

@gapminder.metadata

Loading...
Loading...

About this Dataset

Gapminder Concepts And Entities

What’s in @gapminder and how it’s organized

Gapminder data is split into two kinds of datasets you combine via simple joins:

  1. @gapminder.metadata ← (this dataset)

    • concepts (@gapminder.metadata.concepts): one row per indicator, identified by a concept id.
      Use it to turn ids into human-friendly name, description (and sometimes unit/format, source, tags).
    • entities (@gapminder.metadata.entities_*): reference tables for codes like geo (countries/areas)
      and groupings (regions, income groups, etc.). Key tables you’ll use:
      • entities_geo_country (key: geo) — country/area names and attributes. Often includes columns like
        world_4region, world_6region, income_groups, which point to group tables below.
      • Group tables keyed by a group code, e.g.:
        entities_geo_world_4region, entities_geo_world_6region, entities_geo_income_groups,
        entities_geo_income_3groups, entities_geo_unicef_region, entities_geo_unhcr_region,
        entities_geo_g77_and_oecd_countries, entities_geo_landlocked, entities_geo_global, etc.
      • Non-geo entities such as entities_gender, and topical vocabularies like entities_tag.
  2. Per-indicator datasets (one per concept in @gapminder.metadata.concepts)

    • Named after the concept id, e.g. @gapminder.age_at_1st_marriage_women,
      @gapminder.adults_with_hiv_percent_age_15_49.
    • Each typically has a table named <concept_id>_by_<dimensions> (most often <concept_id>_by_geo_time)
      with columns geo, time, and a value column named exactly as the concept id.

─────────────────────────────────────────────────────────────────────────────

HOW TO USE
  1. Pick an indicator

    • Search @gapminder.metadata.concepts for your topic/keywords; note the concept id.
    • Read name, description (and unit/format if present) to confirm it matches your intent.
  2. Retrieve the datapoints

  3. Add readable labels and groupings

─────────────────────────────────────────────────────────────────────────────

COMMON QUERIES

-- Concept metadata (labels/units)
SELECT name, description
FROM @gapminder.metadata.concepts
WHERE concept = '<concept_id>';

-- Country names and region membership
SELECT d.time,
c.name AS country,
c.world_4region AS region_code,
r.name AS region_name,
d.<concept_id> AS value
FROM @gapminder.<concept_id>.<concept_id>_by_geo_time d
JOIN @gapminder.metadata.entities_geo_country c ON d.geo = c.country
LEFT JOIN @gapminder.metadata.entities_geo_world_4region r ON c.world_4region = r.world_4region;

-- Income groups example
SELECT d.time, c.name AS country, ig.name AS income_group, d.<concept_id> AS value
FROM @gapminder.<concept_id>.<concept_id>_by_geo_time d
JOIN @gapminder.metadata.entities_geo_country c ON d.geo = c.country
LEFT JOIN @gapminder.metadata.entities_geo_income_groups ig ON c.income_groups = ig.income_groups;

─────────────────────────────────────────────────────────────────────────────

TIPS & CAVEATS
  • geo codes may include aggregates (regions/world); treat aggregates differently from countries.
  • Time is usually an integer time (year). Some concepts may use other time forms—check the table schema.
  • Grouping can be derived via a two-step join: (country → group code) then (group code → group label table).

TL;DR: Use @gapminder.metadata.concepts to interpret indicators and @gapminder.metadata.entities_*
to label countries and groupings; then query the per-concept datasets (e.g., @gapminder.age_at_1st_marriage_women) for the actual values and join on geo (and group keys) as shown above.

Tables

Entities | Geo | World 4region

@gapminder.metadata.entities_geo_world_4region
  • 30.09 kB
  • 4 rows
  • 11 columns
Loading...
CREATE TABLE entities_geo_world_4region (
  "world_4region" VARCHAR,
  "color" VARCHAR,
  "description" VARCHAR,
  "is_world_4region" VARCHAR  -- Is--world 4region,
  "latitude" DOUBLE,
  "longitude" DOUBLE,
  "name" VARCHAR,
  "name_long" VARCHAR,
  "name_short" VARCHAR,
  "rank" BIGINT,
  "shape_lores_svg" VARCHAR
);

Share link

Anyone who has the link will be able to view this.