Lookups can be used to create aggregate values based on geometry checks.

The PostgreSQL **aggregate** function is used for the aggregation of values which match the spatial lookup condition. The aggregate function will default to SUM. The aggregate function must be part of the function if `"fieldfix":`

is defined. If not defined the aggregate function must form part of the `"lookup":`

See examples below.

{"label": "OA pop_11","field": "pop_11_","type": "integer","lookup": {"table_a": "dev_polygons","geom_a": "a.geom","table_b": "schema.dev_points","geom_b": "b.geomcntr","condition": "ST_INTERSECTS","aggregate": "AVG"}}

*fig 1. (Look up that makes use of aggregate function) *

{"label": "Population Growth % (2011-17)","field": "pop_growth_11_17_5min","fieldfx": "SUM(b.pop__17 - b.pop__11) * 100 / SUM(b.pop__11)","type": "numeric","lookup": {"table_a": "schema.table_a","table_b": "schema.table_b","geom_a": "isoline_5min","geom_b": "geomcntr"}}

*fig 2. (Function field that overrides the aggregate)*

**table_a** must be the table which holds the location as defined by an ID in the lookup query.

**table_b** must be in the same database but can be in a different schema.

Geometries must be fixed with a. or b. to prevent ambiguous lookups.

**geom_a** can be translated to match the epsg of geom_b.

`"geom_a": "ST_Transform(ST_Setsrid(a.geom, 4326), 3857)"`

It is highly recommended that **geom_b** has a spatial index.

The lookup **condition** must be a PostGIS function which takes the locations geometry as first input and the lookup geometry as the second input. The condition will default to ST_Intersects.

â€‹