Zum Inhalt springen
getgeolens.com

Aggregate a dataset

Dieser Inhalt ist noch nicht in deiner Sprache verfügbar.

POST /api/datasets/{dataset_id}/aggregate/ answers one exact question about a dataset’s rows: how many, what range, which values are most common. A map dashboard widget is one such call, so a script can ask the same question and reproduce the number on screen. The generated operation page has the full schemas.

Replace https://geolens.example.com and $DATASET_ID with your instance’s URL and the dataset’s id. The dataset needs a feature table: a vector dataset or a table. Any other record answers 404.

Every request has an op and may add a filter (CQL2-JSON, as the OGC API Features filter accepts) and a bbox ([west, south, east, north] in WGS 84). Every response repeats the op, adds cached and carries the answer in result. approximate is always false.

Terminal window
curl -X POST https://geolens.example.com/api/datasets/$DATASET_ID/aggregate/ \
-H 'Content-Type: application/json' \
-d '{"op": "count"}'
{"approximate": false, "cached": false, "op": "count", "result": {"count": 496}}

stats takes a numeric column and one to five of count, sum, avg, min and max. Over no rows each is null.

{"op": "stats", "column": "ada", "stats": ["count", "sum", "max"]}
{"op": "stats", "result": {"count": 496, "sum": 181, "max": 2}, "...": "..."}

sum, min and max are exact. When JSON cannot carry the value, such as an integer beyond 2^53, it comes back as text.

The most frequent values of a column, with limit from 1 to 50 (default 10).

{"op": "value_counts", "column": "borough", "limit": 3, "blank_text_as_null": true}
{"op": "value_counts", "result": {
"values": [{"value": "Bk", "count": 169}, {"value": "M", "count": 153}, {"value": "Q", "count": 83}],
"other_count": 91
}, "...": "..."}

other_count is the number of rows whose value is not listed. blank_text_as_null (v1.31.0) counts a text column’s empty strings with its nulls as one null key, before the groups are limited. Whitespace-only text keeps its own key.

Groups rows by group_column and computes a statistic for each group. Name one statistic with stat (and column, which count does not need), or up to five with stats, a list of {"stat", "column"} objects. Other fields:

  • date_bucket: day, week (Monday first), month or year, in UTC, for a date or timestamp group_column. Groups then sort by date, oldest first.
  • sort_by: group, or the index of a statistic. The default is the first statistic, largest first.
  • sort: asc or desc.
  • limit: from 1 to 50 (default 10).
  • blank_text_as_null: as for value_counts.
{"op": "group_by", "group_column": "borough", "stat": "count", "limit": 2}
{"op": "group_by", "result": {
"groups": [{"key": "Bk", "value": 169}, {"key": "M", "value": 153}],
"truncated": true
}, "...": "..."}

With stats, each group also carries values, one entry per statistic in order. truncated is true when more groups matched than limit lists.

Equal-width bins over a numeric column, with bins from 1 to 50 (default 10).

{"op": "histogram", "column": "ada", "bins": 2}
{"op": "histogram", "result": {
"bins": [{"lower": 0.0, "upper": 2.0, "count": 487}, {"lower": 2.0, "upper": 3.0, "count": 9}],
"whole_numbers": true
}, "...": "..."}

A bin holds its lower edge but not its upper one, and the last bin holds both. On an integer column whole_numbers is true, each bin holds the whole numbers from its lower edge to one below its upper edge, and you may get fewer bins than you asked for.

A page of features, for lists. columns names one to 20 columns to return; sort_column and sort order them (nulls last, then by gid), and limit is from 1 to 100 (default 25). The response gives next_cursor and has_more; send the cursor back as cursor with the same sort for the next page.

{"op": "rows", "columns": ["stop_name"], "limit": 2}
{"op": "rows", "result": {
"rows": [{"gid": 1, "values": {"stop_name": "Astoria-Ditmars Blvd"}, "bbox": [-73.912034, 40.775036, -73.912034, 40.775036]}],
"next_cursor": "WzIsbnVsbCwiYXNjIl0",
"has_more": true
}, "...": "..."}

bbox is present when the dataset has geometry. The route never returns a geometry.

A saved dashboard stores each widget’s layer, column and statistic, not its number. The viewer sends one request per data widget, with the dataset behind the widget’s layer:

WidgetRequest
Indicatorcount, or stats with the one statistic on its column
Bar chart, Values of a columnvalue_counts for Count, otherwise group_by with group_column and the statistic
Bar chart, Datesgroup_by with date_bucket
Bar chart, One bar per featurerows, sorted by the value field
Pie chartvalue_counts for Count, otherwise group_by
Histogramhistogram
Tablegroup_by with stats and sort_by
Feature listrows, one page at a time
Category selectorvalue_counts

Widgets that cover the map view add its bbox, and every widget adds its layer’s saved filter as filter. Widgets that group a text column ask for blank_text_as_null, except the date bar chart. To get the same number as the screen, send the same operation with the same filter and bbox. The python/stories-dashboards.py example reads a published dashboard and recomputes an indicator and a pie chart this way.

Anonymous callers can read public, published datasets; a private dataset answers 404. Signed-in callers use their normal dataset access. An embed token in X-Embed-Token authorizes the datasets in its scope, and a read only the token authorizes stays inside the dataset’s tile_columns allowlist, as described under Tile endpoints. See Authentication for the credential forms.

The route allows 10 requests a second. A signed-in caller has a budget of their own; anonymous and embed viewers share one per address. Keep at most two requests in flight per caller. A 429 means retry after 200 to 400 ms with jitter, and a 503 with query_unavailable means retry after its Retry-After.

A bbox over a view the planner expects to hold more than 500,000 rows is refused with 422 and the code too_wide rather than approximated. Other refusals carry a code in detail as well, such as invalid_column, invalid_filter and invalid_cursor.

Unless an administrator lists the caller’s origin under allowed CORS origins, this route sends no CORS headers to other origins, so call it from a server or script, not from a page on another site.