Mapping & codelists¶

mapping is an ordered list of output columns. Each item has a to (the output column name) and exactly one value source.

See them in action

Mapping by example walks through every value source, func and cast with real output from a sample dataset.

mapping:
  - {to: name,        from: duck_name}
  - {to: dataset,     const: "Annual Duck Census"}
  - {to: label,       expr: "duck_name || ' of ' || pond_name"}
  - {to: id,          func: uuid}
  - {to: ducklings,   from: duckling_count, cast: INTEGER}
  - to: species
    codelist:
      source: species_code
      cases:
        - {match: "MAL", value: "Mallard"}
        - {match: "TEA", value: "Teal"}
      default: "Mystery duck"

An empty mapping: [] writes every upstream column unchanged. The output geometry is always written as geom, and geom can't be used as a to.

Value sources¶

Key	Meaning
`from`	Copy a column from the row (base columns plus fields pulled by steps).
`const`	A literal: string, number or boolean.
`expr`	Any DuckDB SQL expression over the row's columns. Multi-line expressions are fine (use a YAML `\|` block).
`func`	A built-in, see below.
`codelist`	Translate a column's value, see Codelists.

`cast`¶

Any item can add cast: <SQL type> (INTEGER, DOUBLE, VARCHAR, BOOLEAN, DATE, TIMESTAMP, …). It's applied as TRY_CAST, so a value that can't be converted becomes NULL instead of failing the run.

DATE and TIMESTAMP read ISO dates (2024-03-14, 2024-03-14 08:30:00) and also day.month.year, optionally followed by a time (14.03.2024, 14.03.2024 08:30, 14.03.2024 08:30:15).

Numbers need a decimal point: cast: DOUBLE turns 1 234,5 into NULL. Use an expr instead, which drops the spaces and replaces the comma with a point (it's also among the expression builder's Convert snippets):

- to: length_m
  expr: TRY_CAST(REPLACE(REGEXP_REPLACE(lengde, '[\s\x{00A0}\x{202F}]', '', 'g'), ',', '.') AS DOUBLE)

Dates before 1970 in GeoPackage

GeoPackage output can't store DATE values before 1970. Cast older dates to TIMESTAMP instead. See cast in the mapping tutorial.

Very large numbers in GeoPackage

A GeoPackage stores integers in 64 bits and has no exact decimals. sum() returns wider types than that: a 128-bit HUGEINT for integer columns, and DECIMAL(38, …) for decimal ones. When writing a GeoPackage, duck soup converts those columns (also inside lists and structs): 128-bit integers to 64-bit ones, and decimals wider than 18 digits to DOUBLE. The run log lists each column it converted. A value too large for 64 bits stops the run with an error naming the column; cast: DOUBLE on that column writes it as a floating-point number instead. GeoParquet output keeps the types as they are.

`func`¶

`func`	Output
`uuid`	A random UUIDv4 string per row.
`seq`	Row number (1, 2, 3, …) in output order, counted per layer.
`now`	Current timestamp.
`today`	Current date.
`lon`	Longitude (EPSG:4326) of the feature's centroid, 7 decimals.
`lat`	Latitude (EPSG:4326) of the feature's centroid, 7 decimals.
`x`	Easting (working CRS) of the feature's centroid, 3 decimals.
`y`	Northing (working CRS) of the feature's centroid, 3 decimals.
`mgrs`	MGRS grid reference of the centroid.
`geohash`	Geohash of the centroid.
`wkb`	The geometry in EPSG:4326 as hex WKB.
`wkt`	The geometry in EPSG:4326 as WKT.
`geom_type`	The geometry type, e.g. `POINT`, `MULTIPOLYGON`.
`area`	`ST_Area` in working-CRS units (m² in a metric CRS).
`length`	`ST_Length` in working-CRS units: perimeter for polygons.

Centroids are computed in the working CRS and then reprojected, so lon/lat stay accurate for long lines and large polygons. area and length require a projected working CRS. All geometry functions require a spatial base source.

Codelists¶

A codelist translates one column's values into another set of values: the YAML equivalent of a lookup table or a CASE WHEN.

Rules¶

- to: species
  codelist:
    source: raw_species        # the column being translated
    case_insensitive: true     # default true
    cases:
      - {match: "mall",     value: "Mallard"}    # exact match
      - {like:  "%gadwall%", value: "Gadwall"}   # SQL LIKE pattern
      - {regex: "^teal",    value: "Teal"}       # regular expression
      - {is_blank: true,    value: "Unknown"}    # NULL or ''
    default: "Other"           # when no rule matches; omit for NULL

Rules are evaluated top to bottom, first match wins.
Each case has exactly one of match, like, regex or is_blank: true.
is_blank lets an empty value get its own result, in order with the other rules. Without it, an empty value falls through to default like any other unmatched value.

File lookup¶

For big code tables, point at a CSV instead of listing rules:

- to: species_name
  codelist:
    source: species_code
    file: codelists/species.csv
    file_match_col: code          # key column in the CSV
    file_value_col: common_name   # value column in the CSV
    default: "Unidentified"

A codelist has either cases or file, not both.

Codelist or attribute join?

A codelist produces one translated column. If you need several columns from the same lookup table, add an attribute_join step instead and map its fields. It's a real join and more efficient for that case.

Per-layer mapping¶

A layer can override the pipeline mapping with its own:

layers:
  - layer: full
    crs: EPSG:25833
  - layer: slim
    crs: EPSG:4326
    mapping:
      - {to: name, from: duck_name}
      - {to: id,   func: uuid}

Without its own mapping, a layer uses the pipeline mapping. In the editor, turn on own column mapping for this layer on the layer's card. See Output layers.