Mapping by example

The mapping decides exactly which columns a layer gets, what they're called, and what goes in them. Each row of the mapping creates one output column from one value source:

Value source	Fills the column with	Example
from	a column that's already there	{to: name, from: name}
const	the same value on every row	{to: dataset, const: "Pondsworth open data"}
expr	a SQL expression you write	{to: name_upper, expr: "upper(name)"}
func	a built-in calculation	{to: id, func: uuid}
codelist	a translation of a column's values	see below

Any of them can add cast to convert the result to a specific type.

The examples run on the Pondsworth places, after a spatial join that adds each place's district. They're the map_* pipelines in pipelines/pondsworth.yaml (from duck-soup tutorial). In the editor, these are the value source and cast columns of the mapping grid.

No mapping at all

A layer with an empty mapping gets every column as-is. Add a mapping as soon as you want to choose, rename, reorder or compute columns. Once there is a mapping, only the columns in it are written. The geometry is always written, as geom, so don't map it yourself.

from: copy a column

- {to: name, from: name}
- {to: place_name, from: name}     # the same column under a new name

from copies a column that exists at the end of the steps: one from the base source, or one a join step pulled in through its fields. to is the name it gets in the output. Mapping rows are written in order, so the mapping also sets the column order.

const: a fixed value

- {to: dataset, const: "Pondsworth open data"}
- {to: version, const: 2}

Every row gets the same value. Handy for provenance columns ("where did this come from?") or for a column a target schema requires but your data doesn't have.

expr: a SQL expression

expr takes any DuckDB SQL expression that can be computed from a single row's columns.

mapping:
- {to: name, from: name}
- {to: dataset, const: "Pondsworth open data"}
- {to: name_upper, expr: "upper(name)"}
- to: size
  expr: |-
    CASE
      WHEN bread_crumbs >= 50000 THEN 'large'
      WHEN bread_crumbs >= 15000 THEN 'medium'
      WHEN bread_crumbs IS NULL THEN 'unknown'
      ELSE 'small'
    END
- {to: label, expr: "name || ' (' || coalesce(district, 'outside town') || ')'"}

name	dataset	name_upper	size	label
Mallard Museum	Pondsworth open data	MALLARD MUSEUM	large	Mallard Museum (Old Puddleton)
Puddle Lane Café	Pondsworth open data	PUDDLE LANE CAFÉ	medium	Puddle Lane Café (Old Puddleton)
Quackington Library	Pondsworth open data	QUACKINGTON LIBRARY	large	Quackington Library (Old Puddleton)
Soggy Crust Café	Pondsworth open data	SOGGY CRUST CAFÉ	small	Soggy Crust Café (Mallard Quay)
Duckling Swim School	Pondsworth open data	DUCKLING SWIM SCHOOL	unknown	Duckling Swim School (Mallard Quay)

How these work:

• upper(name) calls a function on a column. DuckDB has hundreds: text (lower, trim, replace, regexp_replace, split_part), numbers (round, abs, greatest), dates (year, strftime, date_diff) and more.
• CASE WHEN … THEN … ELSE … END picks a value by testing conditions top to bottom; the first true one wins. Nobody has counted the crumbs at the Duckling Swim School, so it falls into the IS NULL branch. A long expression can span several lines, as here (YAML's | keeps the line breaks).
• || joins text. If any part is NULL, the whole result is NULL. That's why the label uses coalesce(district, 'outside town'): the Old Lighthouse has no district, and without coalesce its label would be empty instead of "Old Lighthouse (outside town)".

The rules of SQL apply. Text values go in single quotes ('large'). Column names can be written bare, or in double quotes if they have spaces or capitals ("Opened Year"). In the YAML, wrap the whole expression in double quotes, as above. If the expression itself contains double quotes, write it as a | block instead, which needs no quoting.

The editor's expression builder lists the available columns and common functions, and checks your expression against real rows as you type.

Spatial functions work too

The row's geometry is available as geom, in the working CRS, so expressions like round(ST_Area(geom) / 10000, 2) (hectares) or ST_GeometryType(geom) work. See the area example below.

func: built-in values

func covers values that are awkward or impossible to write as an expression.

Coordinates and geometry

mapping:
- {to: name, from: name}
- {to: longitude, func: lon}
- {to: latitude, func: lat}
- {to: grid_ref, func: mgrs}
- {to: geom_wkb, func: wkb}

name	longitude	latitude	grid_ref	geom_wkb
Mallard Museum	3.0028786	51.4570268	31UET0019900649	0101000000BBDD2637E505…
Puddle Lane Café	3.0046058	51.4574764	31UET0031900700	0101000000B1F902C46E09…
Quackington Library	3.006045	51.456667	31UET0041900609	01010000003F6F2A52610C…

func	Gives	How it's calculated
lon, lat	longitude / latitude in WGS84 (EPSG:4326), 7 decimals	The centroid of the geometry, taken in the working CRS and then converted to lon/lat. For lines and polygons you get the middle, not the first vertex.
mgrs	an MGRS grid reference to 1 m	From the same centroid. 31UET0019900649 is zone 31U, square ET, then a 5-digit easting and northing.
wkb	the geometry as hex-encoded WKB, in EPSG:4326	The full geometry (not the centroid), for systems that expect geometry in a text column.

mapping:
- {to: name, from: name}
- {to: easting, func: x}
- {to: northing, func: y}
- {to: cell, func: geohash}
- {to: kind, func: geom_type}
- {to: geom_wkt, func: wkt}

name	easting	northing	cell	kind	geom_wkt
Mallard Museum	500199.999	5700649.996	u14hsr6wujh9	POINT	POINT (3.0028786000000003 51.4570268)
Puddle Lane Café	500319.999	5700700.003	u14hsrecs2u7	POINT	POINT (3.0046058000000007 51.4574764)
Quackington Library	500419.998	5700609.995	u14hsrkuvcvw	POINT	POINT (3.006045 51.45666700000001)

func	Gives	How it's calculated
x, y	easting / northing in the working CRS, 3 decimals	The same centroid as lon/lat, before it's converted. Use these when the target system wants projected coordinates (UTM, say) rather than lon/lat.
geohash	a geohash of the centroid	Nearby features share a prefix, so it works as a sortable location code or for grouping by area.
geom_type	the geometry type: POINT, LINESTRING, POLYGON, MULTIPOLYGON, …	Handy when a source mixes geometry types and the target needs to tell them apart.
wkt	the geometry as WKT text, in EPSG:4326	Like wkb, but readable.

These work whatever the working CRS is, and need a base source with geometry.

IDs and timestamps

mapping:
- {to: name, from: name}
- {to: row_no, func: seq}
- {to: id, func: uuid}
- {to: loaded_at, func: now}
- {to: loaded_on, func: today}

name	row_no	id	loaded_at	loaded_on
Mallard Museum	1	906a21b4-bc39-44b9-9883-4c061c25c6fc	2026-10-10 12:35:53	2026-10-10
Puddle Lane Café	2	d028599e-cc97-4f11-86c8-cb4660ec1bbd	2026-10-10 12:35:53	2026-10-10
Quackington Library	3	d78d4f75-3dc4-462f-a973-b6ac3ca40d64	2026-10-10 12:35:53	2026-10-10

func	Gives
seq	a row number, 1, 2, 3, … in the order rows are written, counted separately for each output layer. Like uuid, it follows the data, not the feature: if the source order changes, so do the numbers.
uuid	a random UUID (version 4) per row, new on every run. Use it when a target system needs unique IDs, but note that it won't stay the same for a feature between runs.
now	the date and time the run started, the same on every row
today	the date the run started

Area and length

mapping:
- {to: name, from: name}
- {to: area_m2, func: area}
- {to: perimeter_m, func: length}
- {to: area_ha, expr: "round(ST_Area(geom) / 10000, 2)"}

name	area_m2	perimeter_m	area_ha
Central Puddle Park	41800	820	4.18
Willow Wallow	22400	600	2.24
Kingfisher Meadow	34000	740	3.4
Bulrush Fields	36000	760	3.6

func	Gives
area	the area of polygons, in working-CRS units (m² with a metric CRS); 0 for points and lines
length	the length of lines, or the perimeter of polygons, in working-CRS units

Both are measured in the working CRS, so they need a projected one: duck soup refuses to compute them in degrees. For other units, use an expr like area_ha above.

codelist: translate codes

A codelist turns one column's values into other values, such as category codes into readable names. There are two kinds: rules you write in the YAML, or a lookup in a CSV file.

Codelist rules

mapping:
- {to: name, from: name}
- {to: category_code, from: category}
- to: category_name
  codelist:
    source: category
    cases:
    - {match: MUS, value: Museum}
    - {match: LIB, value: Library}
    - {like: "CA%", value: "Café"}
    - {regex: "^(SCH|UNI)$", value: Education}
    - {is_blank: true, value: "Not categorised"}
    default: Other

name	category_code	category_name
Mallard Museum	MUS	Museum
Puddle Lane Café	CAF	Café
Quackington Library	LIB	Library
Soggy Crust Café	CAF	Café
Duckling Swim School	SCH	Education
Breadcrumb Hill	PRK	Other
Featherhill Library	LIB	Library
Museum of Rubber Ducks	MUS	Museum
Reedmoor Pond Café	CAF	Café
Old Duck House	''	Not categorised
Old Lighthouse	LND	Other

• source is the column being translated.

• Each case has one way of matching and a value to output:

  Match type	Matches when the source value…	Here
  match	equals the text exactly	MUS → Museum
  like	fits a SQL LIKE pattern: % is any text, _ one character	CA% → Café
  regex	matches a regular expression	^(SCH\|UNI)$ → Education
  is_blank: true	is NULL or empty text	Old Duck House → Not categorised

• Cases are checked top to bottom, and the first match wins. Put specific rules before general ones.

• default is used when nothing matches (Breadcrumb Hill's PRK, the Old Lighthouse's LND). Without a default, unmatched values become NULL.
• case_insensitive is on unless you set it to false, so mus would match MUS too.

Codelist from a CSV file

For long code tables, keep the codes in a CSV file instead of the YAML:

mapping:
- {to: name, from: name}
- {to: category_code, from: category}
- to: category_name
  codelist:
    source: category
    file: data/tutorial/categories.csv
    file_match_col: code
    file_value_col: label
    default: "Unknown category"

name	category_code	category_name
Mallard Museum	MUS	Museum
Puddle Lane Café	CAF	Café
Quackington Library	LIB	Library
Soggy Crust Café	CAF	Café
Duckling Swim School	SCH	School
Breadcrumb Hill	PRK	Park
Featherhill Library	LIB	Library
Museum of Rubber Ducks	MUS	Museum
Reedmoor Pond Café	CAF	Café
Old Duck House	''	Unknown category
Old Lighthouse	LND	Unknown category

• file is a CSV with a header row; file_match_col is the column compared with source, and file_value_col is the column returned.
• Matching ignores case unless case_insensitive: false. Values are compared as text. If a key appears more than once in the file, only one of its rows is used, so keep keys unique.
• default works as with rules. Here both the blank category and LND get "Unknown category".

Codelist or attribute join?

A codelist gives you one translated column. When you need several columns from the same table (a label and a description and an owner), use an attribute_join step and map its fields instead.

cast: set the column type

Every value has a type: text, integer, decimal number, date, and so on. cast converts a mapping's result to the type you name, and the output layer gets a column of that type.

mapping:
- {to: name, from: name}
- {to: opened_text, from: opened}
- {to: opened_year, from: opened, cast: INTEGER}
- {to: inspected_text, from: inspected}
- {to: inspected_date, from: inspected, cast: DATE}

name	opened_text	opened_year	inspected_text	inspected_date
Mallard Museum	1887	1887	2024-03-14	2024-03-14
Puddle Lane Café	2015	2015	14.03.2024	2024-03-14
Quackington Library	1962	1962	2023-11-02	2023-11-02
Soggy Crust Café	1999	1999	NULL	NULL
Duckling Swim School	1975	1975	March 2024	NULL
Breadcrumb Hill	unknown	NULL	NULL	NULL
Featherhill Library	2008	2008	2024-01-20	2024-01-20
Museum of Rubber Ducks	2021	2021	2022-09-30	2022-09-30
Reedmoor Pond Café	2010	2010	NULL	NULL
Old Duck House	1901	1901	NULL	NULL
Old Lighthouse	1820	1820	NULL	NULL

Why it matters: Pondsworth's opened years are stored as text. Written as-is, the output column is text too: you can't filter it with opened > 1950 or style it on a graduated scale in QGIS, and text sorts character by character, so "950" would come after "1887". Cast to INTEGER, it becomes a real number column.

How it works:

• duck soup converts with DuckDB's TRY_CAST: a value that can't be converted becomes NULL instead of stopping the run. Breadcrumb Hill's unknown and the Duckling Swim School's March 2024 show this. Check for unexpected NULLs after adding a cast.
• DATE and TIMESTAMP accept ISO dates (2024-03-14) and also day.month.year (14.03.2024, optionally with a time: 14.03.2024 08:30), which is common in European data.
• Numbers need a decimal point: cast: DOUBLE turns 12,5 into NULL. For a decimal comma, use the expression builder's Convert snippet instead of a cast.
• cast works with every value source. It's applied last, to the finished value of a from, const, expr, func or codelist.

Common types:

cast	For
INTEGER / BIGINT	whole numbers (BIGINT for values over about 2.1 billion, or a very generous duck)
DOUBLE	decimal numbers
VARCHAR	text, such as codes with leading zeros (0042) that must stay text
BOOLEAN	true/false (accepts true/false, t/f, yes/no, 1/0)
DATE	dates
TIMESTAMP	date and time

Any DuckDB type name works, such as DECIMAL(10,2).

Dates before 1970 in GeoPackage

GeoPackage output can't store DATE values before 1970 through duck soup's writer. For older dates, cast to TIMESTAMP instead.