Skip to main content

Dealer isochrones

The following tables describe the drive-time isochrones of the franchises in market/us/composite/dealers, where each row is a franchise:

  • market/us/dealers/isochrone_polygons: the isochrone polygons of each location;
  • market/us/dealers/isochrone_neighbors: the other franchises inside each isochrone;
  • market/us/dealers/isochrone_tracts: the census tracts each isochrone covers, and how much of each tract's area and housing units;
  • market/us/dealers/demographics: the ACS demographic features of each location's tract and of each of its isochrones, and the CBP features of the tract's county and of each isochrone.

An isochrone is the area reachable by car from a dealer's location within a number of minutes. Each number of minutes in the isochrone_minutes setting of defs.yaml is a band. Each location has one isochrone per band. The isochrone_polygons asset routes the isochrones with pyvalhalla on the Valhalla routing tiles, in processes the asset starts.

Assets​

defs/market/us/dealers/isochrones/defs.yaml of ai_public defines:

  • the asset isochrone_polygons, which writes the polygons;
  • the asset isochrone_neighbors, which reads the polygons and writes the neighbors;
  • the asset isochrone_tracts, which reads the polygons, the tract outlines of census/tiger/cb_tract and the blocks of census/tiger/tabblock20, and writes the tracts;
  • the asset demographics, which reads the tracts, the ACS values of census/acs/acs5_tract and the counts of census/cbp/cbp_county, and writes the features.

The tables have daily partitions, since the set of dealers changes from day to day. Each partition reads the same partition of market/us/composite/dealers. eager automation materializes a partition after the dealers.

Automation of isochrone_polygons waits until the tiles_version partition of osm/us/valhalla/tiles is materialized in the deployment. Automation of isochrone_tracts waits until the tract_vintage partition of census/tiger/cb_tract and the block_vintage partition of census/tiger/tabblock20 are materialized in the deployment. Automation of demographics waits until the tract_vintage partition of census/acs/acs5_tract and the cbp_vintage partition of census/cbp/cbp_county are materialized in the deployment. On a branch deployment, which has no materialization of those partitions, launch the assets by hand.

Each asset fails when an input partition it reads has no rows. The error names the input and the partition.

  • isochrone_polygons and isochrone_neighbors fail on an empty partition of market/us/composite/dealers.
  • isochrone_tracts fails on an empty partition of isochrone_polygons, an empty tract_vintage partition of census/tiger/cb_tract or an empty block_vintage partition of census/tiger/tabblock20.
  • demographics fails on an empty partition of isochrone_tracts, an empty tract_vintage partition of census/acs/acs5_tract or an empty cbp_vintage partition of census/cbp/cbp_county.

A run launched for a day whose dealers are not yet written therefore fails instead of writing an empty partition.

demographics also fails when a row of its partition of isochrone_tracts has a null housing_unit_fraction. Materialize that partition of isochrone_tracts again before demographics.

Franchises of several brands at one address share a location and its isochrones. A franchise without coordinates has no location, so it has no isochrones and no rows in isochrone_neighbors.

ai_public.sources.valhalla.isochrones.location_key() builds a location's key from a frame's latitude and longitude columns. To join franchises to their isochrones, add the key to market/us/composite/dealers with location_key() and join on location_key. A franchise with a location and no rows in isochrone_neighbors for a band has no other franchise inside its isochrone for that band.

market/us/dealers/isochrone_polygons​

One row per location and band.

ColumnTypeMeaning
_partition_dateDatePartition of market/us/composite/dealers
location_keyStringLatitude and longitude rounded to 5 decimal places (about 1 m), joined with a comma, such as 38.9072,-77.0369
latitude, longitudeFloat64The rounded coordinates the isochrone is routed from
isochrone_minutesInt32The band, one of the isochrone_minutes values in defs.yaml
geometryBinaryPolygon or multipolygon as WKB, longitude and latitude in EPSG:4326
area_km2Float64Geodesic area of the isochrone
tiles_versionStringPartition of osm/us/valhalla/tiles the isochrone was routed on
isochrone_requestStringThe request settings, such as auto_minutes-15-30-45_denoise-0.5_generalize-50
routed_atDatetimeWhen the isochrone was routed

market/us/dealers/isochrone_neighbors​

One row per franchise, band and neighbor. A neighbor is another franchise whose location is inside the franchise's isochrone for that band or on its edge.

ColumnTypeMeaning
_partition_dateDatePartition of market/us/composite/dealers
oem, brand, dealer_codeStringThe franchise
location_keyStringThe franchise's location
isochrone_minutesInt32The band
neighbor_oem, neighbor_brand, neighbor_dealer_codeStringThe neighbor
neighbor_location_keyStringThe neighbor's location
distance_kmFloat64Geodesic distance between the two locations
same_brandBooleanbrand equals neighbor_brand
same_locationBooleanThe two franchises share a location

A franchise is never its own neighbor. Other franchises at its location are neighbors, with same_location true and distance_km 0.

The neighbor relation is not symmetric. An isochrone holds the places reachable by car from its own location. One-way roads and highway ramps make the drive time from A to B differ from the drive time from B to A. So B can be inside A's isochrone while A is outside B's. To require both directions, join the table to itself with the two franchises swapped.

market/us/dealers/isochrone_tracts​

One row per location, band and tract. A location's isochrone for a band has a row for each tract with at least min_area_fraction (1%) of its area or of its housing units inside the isochrone, and a row for the location's tract.

ColumnTypeMeaning
_partition_dateDatePartition of market/us/composite/dealers
location_keyStringThe location
isochrone_minutesInt32The band
geo_idString11-digit GEOID of the tract, the key of census/tiger/cb_tract and census/acs/acs5_tract
area_fractionFloat64Share of the tract's area inside the isochrone, from 0 to 1
housing_unit_fractionFloat64Share of the tract's 2020 housing units inside the isochrone, from 0 to 1; the area_fraction of a tract without housing units. See Housing-unit weights
tract_housing_unitsInt64The tract's 2020 housing units; 0 for a tract without housing units, null for a tract without blocks
location_tractBooleanThe tract is the location's tract
location_tract_methodStringOn the location's tract, contains or nearest; null on other rows
tiles_version, isochrone_requestStringThose of the isochrone's row in isochrone_polygons
tract_vintageInt32Partition of census/tiger/cb_tract the tracts come from

market/us/dealers/demographics aggregates the ACS values of the tracts over each isochrone.

A location's tract is the tract whose outline contains the location, with location_tract_method contains. A location on the border of several tracts is in the one with the lowest GEOID.

When no outline contains the location, its tract is the nearest tract within max_location_tract_km (1 km), with location_tract_method nearest. Of tracts equally near, the one with the lowest GEOID is the location's tract. The outlines are generalized to the 1:500,000 scale and clipped to the shoreline, so a dealer on the waterfront can fall just outside every outline. A location with no tract within max_location_tract_km has no row with location_tract true.

Each band has the row of the location's tract, whatever its area_fraction and housing_unit_fraction. That row has an area_fraction of 0 when the tract does not intersect the isochrone, as when Valhalla's simplification leaves the location outside its own isochrone.

market/us/dealers/demographics​

One row per location and area. Franchises join through location_key, as for the other tables.

ColumnTypeMeaning
_partition_dateDatePartition of market/us/composite/dealers
location_keyStringThe location
areaStringtract, or isochrone_<minutes> for each band, such as isochrone_15
tract_countInt32Number of the area's tracts with a positive weight
one column per ACS featureFloat64The ACS demographic features, scalars first, in the order of feature_defs.json
county_countInt32Number of the area's counties with CBP rows and a positive weight
one column per CBP featureFloat64The CBP features, scalars first, in the order of ai_core/cbp/feature_defs.json

The tracts of an area and their weights come from isochrone_tracts:

  • The tract area is the location's tract, the row with location_tract true, at weight 1. A location with no tract within max_location_tract_km has no tract row.
  • The isochrone_<minutes> area of a band holds each tract of the band's rows, weighted by its housing_unit_fraction. The row of the location's tract is included at its housing_unit_fraction, which can be 0.

The features of an area are computed from the tract_vintage (2024) partition of census/acs/acs5_tract, as described in Features of several geographies. The gini_index of an isochrone_<minutes> area is that of a mixture of its tracts' log-normal income distributions, as described in Gini index of several geographies. Each tract area has its tract's published gini_index, or the mixture's value where the published value is null.

Weighting a tract's value by housing_unit_fraction assumes that the value is spread over the tract's blocks in proportion to their 2020 housing units. Every feature takes this one weight, including those whose universe is people rather than households. Weights of partial tracts compares it with area and population weights. Other than the location's tract, tracts with less than min_area_fraction of their area and of their housing units inside an isochrone have no row in isochrone_tracts, so they are left out of the band's isochrone_<minutes> area.

Null and absent values describes how a tract without an ACS value counts. An area whose tracts all have a weight of 0 has a total_population of 0 and a null value for each ratio, median and gini_index. The asset fails when a variable the features need has no row in the ACS partition.

County features​

The asset computes the CBP features of an area with aggregate_acs_features(), from the counties of the area's tracts and the cbp_vintage (2023) partition of census/cbp/cbp_county. Each county has a weight, the share of its residents that belongs to the area:

  • The tract area holds the county of the location's tract at weight 1. Its features are those of the whole county.
  • The isochrone_<minutes> area of a band holds each county of the band's tracts. A tract's residents inside the isochrone are its 2024 ACS total population B01001_001E times its housing_unit_fraction. The county's weight is the sum of that over the county's tracts in the band, divided by the county's residents.

A county's residents are the sum of the population of its tracts in census/acs/acs5_tract. A county all of whose tracts have a housing_unit_fraction of 1 thus has a weight of exactly 1. The asset reads no ACS row of the county itself.

Each count is the sum of the counties' counts times their weights. Each per-capita feature divides that sum by the area's residents in units of 10,000. The residents of an isochrone are the sum of its tracts' population times their housing_unit_fraction. A per-capita feature is thus the mean of the counties' own per-capita values, each weighted by the county's residents in the area. cbp_employees_per_new_car_dealer divides two such sums.

The residents inside the isochrone take the same housing-unit weight as the ACS features, rather than the 2020 block population POP20 of census/tiger/tabblock20. The counts and the per-capita denominators then come from one population, the ACS 2024 population of the same tracts as the ACS features. In Weights of partial tracts, population weights estimate household counts no more closely than housing-unit weights.

The weight assumes that a county's establishments are distributed in proportion to its residents. Establishments cluster along commercial roads and in town centers. An isochrone that covers the residential part of a county is therefore assigned more of the county's dealers than are located in that part, and one that covers the commercial center is assigned fewer.

ai_core.acs.counties.county_weights() computes the weights of counties from tract weights and tract populations, for any source of county values.

A county without rows in the CBP partition, such as a Puerto Rico municipio, is left out of the area. An area none of whose counties has rows has a county_count of 0 and null CBP features. An area whose counties with rows all have a weight of 0 has a county_count of 0, counts of 0 and null per-capita features and cbp_employees_per_new_car_dealer.

Routing​

Each request uses the auto costing model and no departure time, so drive times are free-flow times. defs.yaml sets the request:

  • isochrone_minutes: the bands, 15, 30 and 45;
  • denoise: 0.5, which drops polygon parts smaller than half the largest;
  • generalize: 50, the tolerance in meters to which Valhalla simplifies each polygon.

tiles_version pins the partition of osm/us/valhalla/tiles to route on. The asset reads the archive that prod writes, including on a branch deployment. It declares a dependency on that partition.

A valhalla.Actor cannot be shared between threads; concurrent isochrone() calls on one Actor crash the process. The asset routes with one Actor in each of os.process_cpu_count() processes. Routing is deterministic: the same location, request and tiles give the same polygon.

Tract coverage​

Each run of isochrone_tracts intersects every isochrone of the partition of isochrone_polygons. It reads the whole partition, about 1.4 GB, and does not reuse the previous day's rows.

isochrone_tracts finds the tracts of each isochrone in three steps:

  1. An STRtree over the bounding boxes of the tract outlines gives the tracts whose box intersects the isochrone. Of those, the tracts whose outline intersects it are kept.
  2. The isochrone and those tracts are projected to a Lambert azimuthal equal-area projection centered on the location. In an equal-area projection, the ratio of two areas equals the ratio of their areas on the ellipsoid. The projection's distortion of angles grows with the distance from its center and is small over the extent of a 45-minute isochrone.
  3. area_fraction is the area of the tract's intersection with the isochrone divided by the tract's area, both in that projection. A tract inside the isochrone gets an area_fraction of 1 without computing the intersection. For a tract that crosses the isochrone's edge, the asset first clips the isochrone to the tract's bounding box. The time an intersection takes grows with the number of vertices of both polygons, and an isochrone has thousands of vertices.

The denominator is the area of the tract's outline, not its land area ALAND (land_area_m2). The outline is generalized to the 1:500,000 scale and can include water inside the shoreline, such as a lake, so its area differs from ALAND. An intersection with the outline divided by ALAND could exceed 1. With both areas taken from the outline, a tract inside the isochrone has an area_fraction of exactly 1.

Valhalla simplifies each polygon with the generalize tolerance, which can make a ring cross itself. The asset repairs each isochrone with shapely.make_valid and its structure method. The default linework method decides what is inside by counting crossings of the polygon's rings, so it would count a hole that crosses its shell as area on the side outside the shell.

The distance to the nearest tract is measured in the same projection, where a distance of 1 km from the center differs from the distance on the ellipsoid by far less than a meter.

tract_vintage (2024) pins the partition of census/tiger/cb_tract to read. The asset declares a dependency on that partition.

Housing-unit weights​

A tract's area is a poor guide to where its households live when the tract is large and rural, or holds a park, water or industrial land. housing_unit_fraction counts housing units instead. isochrone_tracts reads the block_vintage (2020) partition of census/tiger/tabblock20, which has each 2020 census block's housing units and internal point, and declares a dependency on that partition.

housing_unit_fraction is the sum of the housing units of the tract's blocks whose internal point is inside the isochrone or on its edge, divided by the tract's tract_housing_units, the sum over all its blocks:

  • A tract whose outline is inside the isochrone has a housing_unit_fraction of 1 and an area_fraction of 1. Its blocks are not tested.
  • For a tract that crosses the isochrone's edge, each block with housing units is tested against the isochrone in longitude and latitude. Tracts are found as in Tract coverage, so only the blocks of tracts whose outline intersects the isochrone are tested.
  • A tract with 0 housing units, such as a tract of water, parkland or an airport, takes its area_fraction. Such a tract had no households in 2020, so its ACS counts are near 0, and its weight changes the area's features little. Taking the area_fraction keeps the weight from being null, and counts housing built in the tract since 2020 in proportion to area.
  • A tract without blocks in census/tiger/tabblock20 also takes its area_fraction, and has a null tract_housing_units. These are the tracts of American Samoa, Guam, the Northern Mariana Islands and the US Virgin Islands, which census/tiger/cb_tract includes and census/tiger/tabblock20 leaves out.

A tract has a row when its area_fraction or its housing_unit_fraction is at least min_area_fraction, so a tract with little area but most of its housing units inside the isochrone is kept. The location's tract has a row whatever its fractions.

The first two rules differ from counting every block whose internal point is inside the isochrone. A block of a tract inside the isochrone counts even when its internal point is outside the isochrone. A block of a tract whose generalized outline does not intersect the isochrone does not count, even when its internal point is inside the isochrone. The housing units the rows count differ little from those of every block whose internal point is inside the isochrone.

Tract vintages​

The blocks are those of the 2020 census, and their tract_geo_id is a 2020 tract. Tract outlines and GEOIDs are fixed for the decade, except in Connecticut. The asset matches a block's tract to the outlines of tract_vintage as follows:

  • A block's tract matches the outline with the same GEOID.
  • Since 2022, a Connecticut tract's GEOID has had the code of a planning region in place of the code of a county. In Connecticut, a block's tract without an outline of the same GEOID matches the outline with the same state and 6-digit tract code. It matches only when that tract code is unique among those 2020 tracts and among the outlines without a 2020 tract of the same GEOID.
  • Some tracts of the blocks have no outline, because the cartographic boundary file leaves them out. They are tracts of water (codes 99xxxx) and of special land use (98xxxx), and none of them has housing units. The asset ignores them.

The asset fails when a tract of the blocks with housing units has no outline, or when a tract of tract_vintage in a state of the blocks has no blocks. Either means that a vintage changed tracts in a way the rules above do not cover.

Reusing the previous day's polygons​

Since routing is deterministic, each partition of isochrone_polygons reads the previous day's partition of itself. It keeps the rows whose location is among the partition's locations and whose tiles_version and isochrone_request match the current settings. It routes the other locations. On most days these are a few new addresses. A partition with no location to route does not download the tile archive.

The asset routes every location when:

  • tiles_version or a request setting changes, because no previous row matches;
  • the previous day's partition was not materialized, because the asset reads only that partition;
  • it is the first run after the table is created.

Creating the table before the first run​

The Iceberg IO manager raises NoSuchTableError for the input from the previous day while the table does not exist, so every partition fails until the table is created. Create it once on each Nessie branch, before the asset's first run there:

aws login
uv run --directory packages/ai_nessie nessie login
./scripts/migrations/create_isochrone_polygons_table.py \
--market us --first-partition <first partition> --branch <branch>

<branch> is main for prod, or the Nessie branch of a branch deployment. A branch created after the table exists on main already has it.

The script writes an empty frame with the IO manager's own writer, so the table's schema and its partition spec on _partition_date match what the asset writes. Writing to main needs the service or admin role on Nessie.

A run routes all its new locations before writing, so a run that fails routes them all again when it is relaunched. Backfill partitions in date order: each partition reads only the partition before it, so a backfill that runs partitions out of order routes every location in each partition whose previous day is not yet written.

Automation of isochrone_polygons does not wait for the previous day's partition, so a missing day does not block the days after it. The partition after a missing day routes every location.

Checks​

isochrone_coverage, a check on isochrone_polygons, warns when fewer than min_coverage (98%) of the franchises with coordinates have an isochrone of at least min_area_km2 (1 km²) for every band.

Valhalla does not fail for a location with no road nearby. It returns a polygon of a few hundred square meters around the point. A 15-minute isochrone from a road is many square kilometers, so an isochrone under min_area_km2 means the coordinates are off the road network. The check's metadata has the number of such locations in locations_under_min_area and the first 50 of them in locations_under_min_area_sample.

isochrone_tract_coverage, a check on isochrone_tracts, warns when an isochrone overlaps no tract area or a location has no tract within max_location_tract_km. The row of a location's own tract does not count as coverage when its area_fraction is 0. Either means that the coordinates are off the tract outlines, such as in the sea. The check's metadata has the numbers in isochrones_without_tracts and locations_without_tract, and the first 50 of each in isochrones_without_tracts_sample and locations_without_tract_sample. locations_by_nearest_tract counts the locations whose tract is the nearest one rather than one that contains them.

The check's metadata also describes the housing-unit weights, without changing whether the check passes:

  • isochrones_without_housing_units: the number of isochrones whose rows hold no housing units, with the first 50 in isochrones_without_housing_units_sample;
  • rows_kept_by_housing_units: rows other than the location's tract with an area_fraction under min_area_fraction, which only their housing_unit_fraction keeps;
  • rows_without_housing_units: rows whose housing_unit_fraction is their area_fraction, because their tract has no housing units or no blocks.

demographics_area_coverage, a check on demographics, warns when a location of the partition has no row with a tract_count of at least 1 for its tract area or for one of the bands. The check's metadata has:

  • the share of locations with every area, in coverage;
  • the number of locations that lack an area, in locations_missing_areas;
  • the first 50 of those locations, with the areas each lacks, in locations_missing_areas_sample;
  • the number of counties with a positive weight and without rows in the CBP partition, in counties_without_cbp, and the first 50 of them in counties_without_cbp_sample. These counties do not change whether the check passes.

Task size​

The task asks for:

  • 8 vCPU;
  • 16 GB of memory;
  • 40 GiB of ephemeral storage, set with op_tags: {ecs/ephemeral_storage: "40"}, for the 15 GB tile archive.

On this task, 8 processes route about 8 locations a second, so routing all of the US's roughly 16,000 locations takes about 35 minutes. The polygons of one location take about 86 KB in Parquet, about 1.4 GB per partition.

isochrone_tracts intersects in os.process_cpu_count() processes. The asset writes files to a temporary directory:

  • the tract outlines;
  • the internal points and housing units of the blocks with housing units, sorted by tract.

Every process maps the files into memory, so the processes share one copy of each. On this task, intersecting all of the US's locations takes about 5 to 10 minutes. The asset holds the polygons of the partition and the rows it writes in memory. While it writes the block files, it also holds the 8.2 million blocks in memory.

Sorting the blocks and writing their files takes about 2 seconds. The files of the 5.9 million blocks with housing units take about 140 MB.