| name | trade-area-analysis |
| description | Builds trade area and catchment analysis workflows in CARTO. Triggers when the user mentions trade area, catchment area, isochrone, site selection, where to open, best location, billboard, OOH, audience targeting, drive time, walk time, coverage area, commercial hotspot, site scoring, location ranking, or wants to generate isochrones, score candidate locations, or identify the best sites for retail, advertising, or services. |
Trade Area Analysis
Builds CARTO Workflows that define catchment areas around candidate locations, enrich them with data, and score/rank locations for site selection, billboard placement, or coverage analysis.
Prerequisites: Load build-carto-workflow for the full workflow development process, JSON structure, and validation commands.
Instructions
A trade area workflow always follows this pipeline:
Candidate Locations → (Filter) → Catchment Areas → Spatial Index → Enrich → Aggregate → Score/Rank → Save
Step 1: Load Candidate Locations
Use native.gettablebyname to load stores, billboards, POIs, or any point dataset representing candidate locations.
Success: Node outputs a table with a geometry column (e.g. geom) and a unique location identifier.
Step 2: Filter/Limit Candidates (optional)
Use native.wheresimplified to narrow candidates (e.g. specific city, category). Use native.orderby + native.limit for top-N by a metric (e.g. top 50 by revenue).
Success: Output contains only the candidate subset relevant to the analysis. Reducing candidates early prevents combinatorial explosion downstream.
Step 3: Generate Catchment Areas
Choose the catchment method based on the use case:
| Method | Component | When to use | Key params |
|---|
| Isochrones | native.isolines | Site selection, retail — realistic road-network areas | mode: car/walk; type: time/distance; range: seconds or meters |
| Buffers | native.buffer | Billboard coverage, proximity — simple geometric circles | distance: meters |
Isochrones vs Buffers: Isochrones follow the road network and produce realistic catchment shapes but require a CARTO LDS API call. Buffers are purely geometric circles — simpler and faster, suitable for proximity analysis or billboard coverage.
Success: Each candidate location has an associated polygon representing its catchment area.
Step 4: Convert Catchment to Spatial Index
Use native.h3polyfill or native.quadbinpolyfill to tessellate catchment polygons into grid cells. The resolution must match the enrichment dataset you plan to use.
Success: Output contains one row per grid cell per location, with both the spatial index column and the location identifier preserved.
Step 5: Enrich with Data
Join grid cells with a spatial features dataset or use native.h3enrich:
- JOIN approach:
native.joinv2 or native.spatialjoin with a pre-indexed enrichment table
- Enrich approach:
native.h3enrich to pull variables from CARTO's Data Observatory
Success: Each grid cell row has enrichment variables (e.g. population, income, foot traffic) attached.
Step 6: Aggregate Back to Locations
Use native.groupby to collapse grid-cell rows back to one row per location:
- Group by: the location identifier column
- Aggregation:
population,sum,income,avg (comma-separated column,method pairs)
Success: Output has one row per candidate location with aggregated enrichment metrics.
Step 7: Score and Rank
Three-part scoring pattern:
- Normalize each variable to [0,1] using
native.normalize (one call per variable, or chain multiple)
- Composite score via
native.selectexpression: weighted addition of normalized variables, e.g. normalized_population * 0.4 + normalized_income * 0.3 + normalized_traffic * 0.3
- Rank using
native.orderby (descending by composite score) + native.limit (top N)
Success: Output is a ranked list of candidate locations with a composite score and the contributing normalized variables.
Step 8: Save
Use native.saveastable to persist the ranked results.
Success: Validated workflow that can be uploaded via carto workflows create.
Gotchas
- Isochrone and route components call CARTO's LDS API — they require a valid connection with API access enabled. Buffers do not.
- Cross Join for distance matrices can explode with many locations x many grid cells. Filter and limit candidates first (Step 2) to keep the pipeline manageable.
- H3 resolution must match the enrichment dataset resolution. Check the enrichment table's index column before choosing resolution.
- Buffer distance is in meters. Isoline range is in seconds (for
type=time) or meters (for type=distance).
- The
native.commercialhotspots component expects variablecolumns as a Python-style list string (['col1', 'col2']) and weights as comma-separated values — inconsistent with other components.
- When aggregating enrichment back to locations (Step 6), ensure the GROUP BY uses the location identifier, not the grid cell index. Grouping by the grid cell produces per-cell results instead of per-location.
- When using
native.h3distance for competitor proximity, the output is grid-based. Join it back to the location table to get per-location distance metrics.
Reference Templates
These files are working examples (skill-local files in trade-area-analysis/, the routing example is in the project root):
| File | Description |
|---|
isochrones_from_points.sql | Retail stores in Boston — 5-min walk-time isochrones via LDS API |
identify_best_billboards.sql | Billboard site scoring — buffer, enrich, normalize, weighted composite score, top-N |
commercial_hotspots.sql | Commercial hotspot detection — H3 distance to competitors, weighted hotspot analysis |
../../routes_od_matrix.sql | Origin-destination routing matrix — related routing/distance example |
Common Variations
| Variant | How |
|---|
| Retail site selection | Isochrones (walk/drive) -> H3 polyfill -> enrich with demographics -> score by population + income |
| Billboard/OOH placement | Buffers -> H3 polyfill -> enrich with audience/traffic -> normalize + weighted score -> top-N |
| Commercial hotspot detection | H3 grid -> native.h3distance for competitor proximity -> native.commercialhotspots with weights and p-value threshold |
| Drive-time coverage analysis | Isochrones (car, multiple ranges) -> union -> dissolve to find total coverage area |
| Walk-time catchment comparison | Isochrones (walk, 5/10/15 min) -> enrich each band -> compare population captured per band |
| Franchise territory planning | Isochrones per candidate -> check overlap -> filter non-overlapping set -> score remaining |
