carto-trade-area-analysis

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 carto-create-workflow for the development process, JSON structure, and validation. It covers both access paths — the MCP server's workflow tools (create_workflow, validate_workflow, run_workflow) when attached, the carto workflows CLI otherwise. Routing signals: carto-basics/references/access-paths.md.

Ad-hoc catchments: for a one-off isochrone around a handful of points, use the calculate_isolines MCP tool (available over OAuth, or on a token session whose Allowed APIs include LDS) — the workflow patterns below are for table-scale, repeatable analysis.

---

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 pattern (see carto-composite-scoring for the full method): normalize each variable to [0,1] with native.normalize; composite score via native.selectexpression, e.g. normalized_population * 0.4 + normalized_income * 0.3 + normalized_traffic * 0.3; rank with native.orderby (descending) + native.limit (top N).

Success: 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 create_workflow (MCP) or carto workflows create (CLI).

---

Gotchas

• Provider casing & SQL dialect. This skill uses lowercase column names (geom, population, income, normalized_population, etc.) — BigQuery / Databricks / Postgres / Redshift convention. On Snowflake, unquoted identifiers surface UPPERCASE — reference them as GEOM, POPULATION, INCOME, NORMALIZED_POPULATION. See carto-create-workflow/references/providers/<provider>.md for casing rules and SQL dialect equivalents.
• 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 in this skill directory:

File	Description
isochrones_from_points.json	Retail stores in Boston — 5-min walk-time isochrones via LDS API. native.isolines is on v2 (lowercase wire-value mode list, customoptions / traveltime_* inputs).
identify_best_billboards.json	Billboard site scoring — buffer, enrich, normalize, weighted composite score, top-N
commercial_hotspots.json	Commercial hotspot detection — H3 distance to competitors, weighted hotspot analysis

Refreshing component versions. Templates pin each node's data.version to what was current when harvested. When a native component bumps versions (e.g. native.isolines v1 → v2 added transport modes plus customoptions and traveltime_* inputs), the older template still parses but flags verify-remote warnings and misses new inputs. To refresh, read the live schema — read_workflow_components (MCP) or carto workflows components get <component> --connection <conn> --json (CLI) — then update the node's data.version and inputs in lockstep. Cross-reference Selection values against carto-create-workflow/SKILL.md: options may show display labels, but wire values are the lowercase / snake_case forms.

---

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