Documentation → How-to guides

Query and model data with SQL

The SQL Workbench runs spatial SQL over the datasets you already have — joins, aggregates, buffers, filters. A query you keep becomes a model, and running a model produces a new dataset that tiles and maps like any other.

Two things that look similar and are not

	A query	A model
gives you	rows, on screen	a new dataset, tiled and mappable
creates	nothing	a dataset you can map, share and query again
costs	a moment	a tiling job
needs geometry	no	yes — exactly one geometry column
use it to	check an assumption, count something, explore	keep a result and put it on a map

Explore with queries; commit with a model. Almost every mistake here is running a tiling job to answer a question a query would have answered in a second.

Writing a query

Open SQL Workbench in the sidebar.
Add the datasets you want to query as inputs. Each gets a short alias — that alias is the table name in your SQL.
Write the query. FROM parks reads the input aliased parks.
Choose Preview to run it and see rows.

The schema browser beside the editor lists each input's columns, and the table tab shows real rows from it. That is the authoritative answer to what a field is called — field names in published data are rarely what the documentation claims.

The dialect

It is DuckDB with its spatial extension, so the whole ST_* family is available, along with DuckDB's own conveniences — QUALIFY, GROUP BY ALL, SELECT * EXCLUDE (…).

The geometry column is geom on every input.
Everything is WGS84 (EPSG:4326) — longitude/latitude, degrees. Every dataset is normalised to it on ingest, so joins across sources line up without reprojection.
Measuring in degrees is almost never what you want. Use ST_Distance_Sphere for metres, or project into a metric CRS for areas and buffers — and if you do reproject, pass always_xy := true to ST_Transform. Without it you get silently empty geometry rather than an error.

Reference data you did not have to import

Your operator may have loaded shared reference datasets — OpenStreetMap roads, waterways, buildings, points of interest and so on. They appear in the catalogue alongside your own, and can be used as model inputs or map layers with no import and no storage of your own. They are read-only: you cannot edit or reprocess one.

Turning a query into a model

Get the query returning what you want in the preview.
Choose Run.
Give the output dataset a name and a layer name — the same rules as any dataset, and the layer name is equally permanent.
Run it. A tiling job queues, and when it is Ready the output is an ordinary dataset: style it, map it, share it, use it as the input to another model.

Your result must have exactly one geometry column. A model produces a map layer, and a layer has one geometry. Select parks.geom rather than parks.* when you have joined two spatial inputs, or you will have two — and remember that an aggregate needs the geometry named in the GROUP BY or produced by something like ST_Union.

Re-running a model

Edit the SQL and run again. Running a model that already has an output replaces that dataset — the same id, so styling and map memberships survive and published links keep working. mapbench says so before it does it.

Models do not run on a schedule, and they do not re-run when an input changes. Refresh an input, then re-run the models built on it.

Lineage — what was built from what

A model records its inputs, so a dataset produced by one knows where it came from. That chain is worth keeping honest: it is what tells you, six weeks later, which of four similarly-named layers a published map is actually drawing, and which upstream import broke it. Archiving an input does not hide it from the lineage — an archived upstream is usually exactly what an investigation is looking for.

Limits

A query returns at most 1000 rows. It is a tool for checking and aggregating, not for exporting a dataset.
Queries run 30 per minute from the app, and 10 per minute for an API key or a connected agent.
A query that runs too long, or wants too much memory, is stopped. Narrow it with a WHERE or a bounding box rather than retrying it unchanged.
SQL never touches the database that holds your account and catalogue. It runs in a sandbox over copies of the data files, with no file, network or write access of any kind — which is also why ATTACH, COPY and read_csv on a path all fail.

Common traps

A column in my result is entirely null: mapbench flags this because it is almost always a bug, not a finding — a mistyped field name that a left join happily produced nulls for, or a reprojection that failed silently. Check the field name in the schema browser before you assume the data is empty.
A spatial join returns nothing: Usually the two inputs do not overlap where you think they do. Query the bounding box of each (ST_Extent(geom)) and compare — a dataset imported with a broken CRS lands somewhere in the Gulf of Guinea, and the join is correct to find nothing.
Distances and areas are tiny decimals: You measured in degrees. See the note on WGS84 above.
“The model result must have exactly one geometry column”: You selected * across two spatial inputs. Name the geometry you want and exclude the other.
The preview looks right but the tiled dataset is missing features: The preview shows the first rows of your result; the model runs the whole query. If the counts disagree, compare SELECT count(*) against the tiled dataset's own feature count before suspecting the tiler.