🗺️ geo¶
U.S. government geographic data including Census TIGER boundaries, HUD USPS ZIP code crosswalks, and demographic statistics. Enables spatial analysis, geographic aggregation, and crosswalk between ZIP codes, counties, census tracts, congressional districts, and metropolitan areas.
38 datasets · 223 columns
states · table¶
U.S. state boundaries and attributes (FIPS code, name, 2-letter postal abbreviation, land/water area, WKT geometry) from Census TIGER/Line shapefiles, one row per state per TIGER vintage year (2010-present). state_fips/state_abbr repeat across vintages so this table is not a valid FK target — join geo.state_ref for a stable, non-vintaged state-identity key.
| Column | Type | Null | Description |
|---|---|---|---|
state_ |
string | no | 2-digit FIPS code identifying the state (e.g., '06' for California) |
state_ |
string | no | Geographic identifier (GEOID) for the state, matches state_fips |
state_ |
string | no | Full state name (e.g., 'California') |
state_ |
string | no | 2-letter postal abbreviation (e.g., 'CA') |
land_ |
double | yes | Land area in square meters |
water_ |
double | yes | Water area in square meters |
geometry |
string | yes | WKT representation of boundary polygon |
state_ref · table¶
Conformed US state-identity reference dimension: 50 states + DC with FIPS code, 2-letter postal abbreviation, and full name. Static and non-vintaged — this is the canonical foreign-key target for state_abbr and state_fips across all govdata schemas (geo.states is a year-partitioned boundary snapshot and cannot be an FK target). Join here to attach state names/codes to any table carrying a state abbreviation or FIPS code.
| Column | Type | Null | Description |
|---|---|---|---|
state_ |
string | no | 2-digit state FIPS code, zero-padded (e.g., '06' for California) |
state_ |
string | no | 2-letter USPS state abbreviation (e.g., 'CA') |
state_ |
string | no | Full state name (e.g., 'California') |
census_regions · table¶
US Census Bureau statistical regions: 4 rows (Northeast, Midwest, South, West). Canonical FK target for a Census region_code (integer 1-4). Static and non-vintaged.
| Column | Type | Null | Description |
|---|---|---|---|
region_ |
integer | no | Census region code (1=Northeast, 2=Midwest, 3=South, 4=West) |
region_ |
string | no | Census region name |
census_divisions · table¶
US Census Bureau statistical divisions: 9 rows (New England, Middle Atlantic, East/West North Central, South Atlantic, East/West South Central, Mountain, Pacific), each carrying its parent region. Canonical FK target for a Census division_code (integer 1-9). Static and non-vintaged.
| Column | Type | Null | Description |
|---|---|---|---|
division_ |
integer | no | Census division code (1-9) |
division_ |
string | no | Census division name |
region_ |
integer | no | Parent Census region code (FK to census_regions) |
region_ |
string | no | Parent Census region name (denormalized for convenience) |
zcta_ref · table¶
Conformed ZIP Code Tabulation Area reference dimension: one row per 5-digit ZCTA (2020 census vintage) with land/water area and internal-point centroid. Static and non-vintaged — the canonical foreign-key target for zip_code / ZCTA across all govdata schemas (geo.zctas is a year-partitioned boundary snapshot and cannot be an FK target).
| Column | Type | Null | Description |
|---|---|---|---|
zcta |
string | no | 5-digit ZIP Code Tabulation Area code |
land_ |
double | yes | Land area (square miles) |
water_ |
double | yes | Water area (square miles) |
latitude |
double | yes | Internal-point latitude (centroid) |
longitude |
double | yes | Internal-point longitude (centroid) |
counties · table¶
U.S. county boundaries and attributes (FIPS code, name, GEOID, land/water area, WKT geometry) from Census TIGER/Line shapefiles, one row per county per TIGER vintage year (2010-present), covering all 3,000+ counties and county equivalents. Does not carry CBSA/metro-area membership — see geo.cbsa for metropolitan and micropolitan statistical area boundaries.
| Column | Type | Null | Description |
|---|---|---|---|
county_ |
string | no | 5-digit FIPS code for county (state + county, e.g., '06037' for Los Angeles County) |
state_ |
string | no | 2-digit FIPS code identifying the state (e.g., '06' for California) |
county_ |
string | no | Full county name (e.g., 'Los Angeles County') |
county_ |
string | no | Geographic identifier (GEOID) for the county |
land_ |
double | yes | Land area in square meters |
water_ |
double | yes | Water area in square meters |
geometry |
string | yes | WKT representation of boundary polygon |
places · table¶
Census designated places (incorporated cities/towns and census-designated places/CDPs) with place FIPS code, name, place type, and WKT geometry, one row per place per TIGER vintage year (2010-present), fanned out by state. Carries no land/water area or population — see geo.gazetteer_places for area, population, and housing-unit counts.
| Column | Type | Null | Description |
|---|---|---|---|
place_ |
string | no | 7-digit FIPS code for place (state + place code) |
state_ |
string | no | 2-digit FIPS code identifying the state (e.g., '06' for California) |
place_ |
string | no | Name of incorporated place or census-designated place |
place_ |
string | yes | Classification of place (e.g., 'city', 'town', 'CDP') |
geometry |
string | yes | WKT representation of boundary polygon |
zctas · table¶
ZIP Code Tabulation Area boundaries (ZCTA code, land/water area, WKT geometry) from Census TIGER/Line shapefiles, one row per ZCTA per TIGER vintage year. Currently only 2020-present is loaded in production — unlike the other TIGER boundary tables in this schema, which go back to 2010 — see observedCoverage below for the real range rather than assuming a 2010 floor here too. Vintage-specific — codes and boundaries shift between vintages, so this is not a stable FK target; see geo.zcta_ref for the static, non-vintaged ZCTA dimension, and geo.gazetteer_zctas for population, housing units, and centroid coordinates.
| Column | Type | Null | Description |
|---|---|---|---|
zcta |
string | no | 5-digit ZIP Code Tabulation Area code |
land_ |
double | yes | Land area in square meters |
water_ |
double | yes | Water area in square meters |
geometry |
string | yes | WKT representation of boundary polygon |
census_tracts · table¶
Census tract boundary geometry (tract FIPS code, parent state/county FIPS, tract number, land/water area, WKT geometry) from Census TIGER/Line shapefiles, one row per tract per TIGER vintage year (2010-present), fanned out by state. Boundaries only — no demographic or population data; join to census schema ACS tables on tract FIPS for those statistics.
| Column | Type | Null | Description |
|---|---|---|---|
tract_ |
string | no | 11-digit FIPS code for census tract (state + county + tract) |
state_ |
string | no | 2-digit FIPS code identifying the state (e.g., '06' for California) |
county_ |
string | no | 5-digit FIPS code for county (state + county, e.g., '06037' for Los Angeles County) |
tract_ |
string | yes | Census tract number (e.g., '4201.02') |
land_ |
double | yes | Land area in square meters |
water_ |
double | yes | Water area in square meters |
geometry |
string | yes | WKT representation of boundary polygon |
block_groups · table¶
Census block group boundary geometry (block group FIPS code, parent state/county/tract FIPS, land/water area, WKT geometry) from Census TIGER/Line shapefiles, one row per block group per TIGER vintage year (2010-present), fanned out by state. Boundaries only — no population or demographic data.
| Column | Type | Null | Description |
|---|---|---|---|
block_ |
string | no | 12-digit FIPS code for block group (state + county + tract + block group) |
state_ |
string | no | 2-digit FIPS code identifying the state (e.g., '06' for California) |
county_ |
string | no | 5-digit FIPS code for county (state + county, e.g., '06037' for Los Angeles County) |
tract_ |
string | no | 11-digit FIPS code of the parent census tract |
land_ |
double | yes | Land area in square meters |
water_ |
double | yes | Water area in square meters |
geometry |
string | yes | WKT representation of boundary polygon |
cbsa · table¶
Core Based Statistical Area boundaries (CBSA code, name, Metropolitan/Micropolitan designation via metro_micro, land/water area, WKT geometry) from Census TIGER/Line shapefiles, one row per CBSA per TIGER vintage year (2010-present, but not every year in between — see observedCoverage below, which currently has a 2022 gap), national file (not fanned out by state). Carries no member-county list — see geo.zip_cbsa_crosswalk to relate ZIP codes to CBSAs.
| Column | Type | Null | Description |
|---|---|---|---|
cbsa_ |
string | no | 5-digit CBSA code for Core Based Statistical Area |
cbsa_ |
string | no | Name of metropolitan or micropolitan statistical area |
metro_ |
string | yes | LSAD code indicating Metropolitan or Micropolitan designation |
land_ |
double | yes | Land area in square meters |
water_ |
double | yes | Water area in square meters |
geometry |
string | yes | WKT representation of boundary polygon |
congressional_districts · table¶
U.S. Congressional district boundaries for the House of Representatives (district code, parent state FIPS, district name, land/water area, WKT geometry), one row per district, national file per vintage. Unlike other TIGER boundary tables in this schema (which go back to 2010), congressional_districts only covers the current-cycle redistricting vintages forward — check observedCoverage below for the real floor rather than assuming a specific starting vintage year.
| Column | Type | Null | Description |
|---|---|---|---|
cd_ |
string | no | 4-digit congressional district code (state + district number) |
state_ |
string | no | 2-digit FIPS code identifying the state (e.g., '06' for California) |
cd_ |
string | yes | Congressional district name or number |
land_ |
double | yes | Land area in square meters |
water_ |
double | yes | Water area in square meters |
geometry |
string | yes | WKT representation of boundary polygon |
school_districts · table¶
Elementary, secondary, and unified school district boundaries (LEA code, parent state FIPS, district name, district type — elementary/secondary/unified, land/water area, WKT geometry) from Census TIGER/Line shapefiles, one row per district per TIGER vintage year (2010-present), fanned out by state.
| Column | Type | Null | Description |
|---|---|---|---|
sd_ |
string | no | Local Education Agency (LEA) code identifying the school district |
state_ |
string | no | 2-digit FIPS code identifying the state (e.g., '06' for California) |
sd_ |
string | yes | School district name |
sd_ |
string | yes | School district type (elementary, secondary, or unified) |
land_ |
double | yes | Land area in square meters |
water_ |
double | yes | Water area in square meters |
geometry |
string | yes | WKT representation of boundary polygon |
state_legislative_lower · table¶
State legislative district boundaries for lower chambers (State House/Assembly) — SLDL code, parent state FIPS, district name, land/water area, WKT geometry — one row per district per TIGER vintage year (2010-present). Excludes DC and Nebraska, which have no lower chamber (Nebraska's legislature is unicameral); see geo.state_legislative_upper for upper-chamber (State Senate) districts, which covers every state.
| Column | Type | Null | Description |
|---|---|---|---|
sldl_ |
string | no | State legislative district lower chamber code (state + district) |
state_ |
string | no | 2-digit FIPS code identifying the state (e.g., '06' for California) |
district_ |
string | yes | District name or number |
land_ |
double | yes | Land area in square meters |
water_ |
double | yes | Water area in square meters |
geometry |
string | yes | WKT representation of boundary polygon |
state_legislative_upper · table¶
State legislative district boundaries for upper chambers (State Senate) — SLDU code, parent state FIPS, district name, land/water area, WKT geometry — one row per district per TIGER vintage year (2010-present), covering every state. See geo.state_legislative_lower for lower-chamber (State House/Assembly) districts, which excludes DC and Nebraska.
| Column | Type | Null | Description |
|---|---|---|---|
sldu_ |
string | no | State legislative district upper chamber code (state + district) |
state_ |
string | no | 2-digit FIPS code identifying the state (e.g., '06' for California) |
district_ |
string | yes | District name or number |
land_ |
double | yes | Land area in square meters |
water_ |
double | yes | Water area in square meters |
geometry |
string | yes | WKT representation of boundary polygon |
county_subdivisions · table¶
County subdivisions including Minor Civil Divisions (MCDs), census county divisions, and unorganized territories. Represents administrative or statistical subdivisions of counties.
| Column | Type | Null | Description |
|---|---|---|---|
cousub_ |
string | no | County subdivision FIPS code (state + county + subdivision) |
state_ |
string | no | 2-digit FIPS code identifying the state (e.g., '06' for California) |
county_ |
string | no | 5-digit FIPS code for county (state + county, e.g., '06037' for Los Angeles County) |
cousub_ |
string | yes | County subdivision name |
cousub_ |
string | yes | Legal/statistical area description code |
land_ |
double | yes | Land area in square meters |
water_ |
double | yes | Water area in square meters |
geometry |
string | yes | WKT representation of boundary polygon |
tribal_areas · table¶
American Indian reservations, Alaska Native village statistical areas, and Native Hawaiian home lands (area code, name, area type — federal reservation, state reservation, TDSA, etc., land/water area, WKT geometry) from Census TIGER/Line AIANNH shapefiles, one row per area per TIGER vintage year (2010-present), national file (not fanned out by state).
| Column | Type | Null | Description |
|---|---|---|---|
aiannhce |
string | no | American Indian/Alaska Native/Native Hawaiian area code |
name |
string | yes | Area name |
namelsad |
string | yes | Name and legal/statistical area description |
area_ |
string | yes | Area type (federal reservation, state reservation, TDSA, etc.) |
land_ |
double | yes | Land area in square meters |
water_ |
double | yes | Water area in square meters |
geometry |
string | yes | WKT representation of boundary polygon |
urban_areas · table¶
Census Bureau urban area boundaries, one row per urban area per TIGER vintage year. The 2020-Census-cycle definition (minimum threshold 5,000 people or 2,000 housing units; the Urbanized-Area/Urban-Cluster distinction was eliminated, all qualifying areas are now simply 'urban areas') is the current methodology, but "2020" here describes the definition in effect, not the vintage year loaded — see observedCoverage below for which TIGER vintage years are actually present.
| Column | Type | Null | Description |
|---|---|---|---|
uace |
string | no | Urban area code |
name |
string | yes | Urban area name |
urban_ |
string | yes | Urban type code (legacy field from pre-2020 Census, may be blank for 2020+ data) |
land_ |
double | yes | Land area in square meters |
water_ |
double | yes | Water area in square meters |
geometry |
string | yes | WKT representation of boundary polygon |
pumas · table¶
Public Use Microdata Areas for Census microdata products. Each PUMA contains at least 100,000 people and is built from census tracts and counties.
| Column | Type | Null | Description |
|---|---|---|---|
puma_ |
string | no | 5-digit PUMA code (unique within state) |
state_ |
string | no | 2-digit FIPS code identifying the state (e.g., '06' for California) |
puma_ |
string | yes | PUMA name |
land_ |
double | yes | Land area in square meters |
water_ |
double | yes | Water area in square meters |
geometry |
string | yes | WKT representation of boundary polygon |
voting_districts · table¶
Voting district boundaries (precincts, wards, election districts) as reported by states for redistricting. TigerDataProvider handles two distinct URL patterns: 2012 (2010 census vintage boundaries) and 2020-forward (2020 census vintage boundaries, same URL scheme continuing through later TIGER releases) — see observedCoverage below for which years are actually loaded, since it's more than just those two. Attributes are normalized to canonical form.
| Column | Type | Null | Description |
|---|---|---|---|
vtd_ |
string | no | Voting district code (state + county + VTD) |
state_ |
string | no | 2-digit FIPS code identifying the state (e.g., '06' for California) |
county_ |
string | no | 5-digit FIPS code for county (state + county, e.g., '06037' for Los Angeles County) |
vtd_ |
string | yes | Voting district name |
land_ |
double | yes | Land area in square meters |
water_ |
double | yes | Water area in square meters |
geometry |
string | yes | WKT representation of boundary polygon |
zip_county_crosswalk · table¶
ZIP Code to County crosswalk mapping ZIP codes to counties, with residential/business/other/total address-ratio splits (res_ratio, bus_ratio, oth_ratio, tot_ratio), one row per ZIP-county pair per year. Source: HUD USPS ZIP Code Crosswalk, Q4 snapshot only (not all four quarters). Reverse mapping: see geo.county_zip_crosswalk.
| Column | Type | Null | Description |
|---|---|---|---|
zip |
string | no | 5-digit ZIP Code |
county_ |
string | no | 5-digit FIPS code for county (state + county, e.g., '06037' for Los Angeles County) |
res_ |
double | yes | Ratio of residential addresses in this ZIP that are in this county |
bus_ |
double | yes | Ratio of business addresses in this ZIP that are in this county |
oth_ |
double | yes | Ratio of other addresses in this ZIP that are in this county |
tot_ |
double | yes | Ratio of total addresses in this ZIP that are in this county |
zip_cbsa_crosswalk · table¶
ZIP Code to Core Based Statistical Area crosswalk mapping ZIP codes to metropolitan/micropolitan statistical areas, with residential/business/other/total address-ratio splits (res_ratio, bus_ratio, oth_ratio, tot_ratio), one row per ZIP-CBSA pair per year. Source: HUD USPS ZIP Code Crosswalk, Q4 snapshot only (not all four quarters).
| Column | Type | Null | Description |
|---|---|---|---|
zip |
string | no | 5-digit ZIP Code |
cbsa_ |
string | no | 5-digit CBSA code for Core Based Statistical Area |
res_ |
double | yes | Ratio of residential addresses in this ZIP that are in this CBSA |
bus_ |
double | yes | Ratio of business addresses in this ZIP that are in this CBSA |
oth_ |
double | yes | Ratio of other addresses in this ZIP that are in this CBSA |
tot_ |
double | yes | Ratio of total addresses in this ZIP that are in this CBSA |
tract_zip_crosswalk · table¶
Census tract to ZIP Code crosswalk mapping tract FIPS to ZIP codes, with residential/business/other/total address-ratio splits (res_ratio, bus_ratio, oth_ratio, tot_ratio), one row per tract-ZIP pair per year. Source: HUD USPS ZIP Code Crosswalk, Q4 snapshot only (not all four quarters). Reverse mapping: see geo.zip_tract_crosswalk.
| Column | Type | Null | Description |
|---|---|---|---|
tract_ |
string | no | 11-digit census tract FIPS code |
zip |
string | no | 5-digit ZIP Code |
res_ |
double | yes | Ratio of residential addresses in this tract that are in this ZIP |
bus_ |
double | yes | Ratio of business addresses in this tract that are in this ZIP |
oth_ |
double | yes | Ratio of other addresses in this tract that are in this ZIP |
tot_ |
double | yes | Ratio of total addresses in this tract that are in this ZIP |
zip_tract_crosswalk · table¶
ZIP Code to Census Tract crosswalk mapping ZIP codes to census tracts, with residential/business/other/total address-ratio splits (res_ratio, bus_ratio, oth_ratio, tot_ratio), one row per ZIP-tract pair per year. Source: HUD USPS ZIP Code Crosswalk, Q4 snapshot only (not all four quarters). Reverse mapping: see geo.tract_zip_crosswalk.
| Column | Type | Null | Description |
|---|---|---|---|
zip |
string | no | 5-digit ZIP Code |
tract_ |
string | no | 11-digit census tract FIPS code |
state_ |
string | no | 2-digit FIPS code identifying the state (e.g., '06' for California) |
res_ |
double | yes | Ratio of residential addresses in this ZIP that are in this tract |
bus_ |
double | yes | Ratio of business addresses in this ZIP that are in this tract |
oth_ |
double | yes | Ratio of other addresses in this ZIP that are in this tract |
tot_ |
double | yes | Ratio of total addresses in this ZIP that are in this tract |
zip_cd_crosswalk · table¶
ZIP Code to Congressional District crosswalk mapping ZIP codes to congressional districts, with residential/business/other/total address-ratio splits (res_ratio, bus_ratio, oth_ratio, tot_ratio), one row per ZIP-CD pair per year. Source: HUD USPS ZIP Code Crosswalk, Q4 snapshot only (not all four quarters). Reverse mapping: see geo.cd_zip_crosswalk.
| Column | Type | Null | Description |
|---|---|---|---|
zip |
string | no | 5-digit ZIP Code |
cd_ |
string | no | Congressional district code (state + district number) |
res_ |
double | yes | Ratio of residential addresses in this ZIP that are in this CD |
bus_ |
double | yes | Ratio of business addresses in this ZIP that are in this CD |
oth_ |
double | yes | Ratio of other addresses in this ZIP that are in this CD |
tot_ |
double | yes | Ratio of total addresses in this ZIP that are in this CD |
county_zip_crosswalk · table¶
County to ZIP Code crosswalk for allocating county-level data to ZIP codes, with residential/business/other/total address-ratio splits (res_ratio, bus_ratio, oth_ratio, tot_ratio), one row per county-ZIP pair per year. Source: HUD USPS ZIP Code Crosswalk, Q4 snapshot only (not all four quarters). Reverse mapping of geo.zip_county_crosswalk.
| Column | Type | Null | Description |
|---|---|---|---|
county_ |
string | no | 5-digit FIPS code for county (state + county, e.g., '06037' for Los Angeles County) |
zip |
string | no | 5-digit ZIP Code |
res_ |
double | yes | Ratio of residential addresses in this county that are in this ZIP |
bus_ |
double | yes | Ratio of business addresses in this county that are in this ZIP |
oth_ |
double | yes | Ratio of other addresses in this county that are in this ZIP |
tot_ |
double | yes | Ratio of total addresses in this county that are in this ZIP |
cd_zip_crosswalk · table¶
Congressional District to ZIP Code crosswalk for allocating district-level data to ZIP codes, with residential/business/other/total address-ratio splits (res_ratio, bus_ratio, oth_ratio, tot_ratio), one row per CD-ZIP pair per year. Source: HUD USPS ZIP Code Crosswalk, Q4 snapshot only (not all four quarters). Reverse mapping of geo.zip_cd_crosswalk.
| Column | Type | Null | Description |
|---|---|---|---|
cd_ |
string | no | Congressional district code (state + district number) |
zip |
string | no | 5-digit ZIP Code |
res_ |
double | yes | Ratio of residential addresses in this CD that are in this ZIP |
bus_ |
double | yes | Ratio of business addresses in this CD that are in this ZIP |
oth_ |
double | yes | Ratio of other addresses in this CD that are in this ZIP |
tot_ |
double | yes | Ratio of total addresses in this CD that are in this ZIP |
rural_urban_continuum · table¶
USDA Economic Research Service Rural-Urban Continuum Codes (RUCC) classifying counties by degree of urbanization and adjacency to metro areas, with a 9-category code (rucc_code), description, county population, and a metro/nonmetro flag (metro_nonmetro), one row per county for the 2023 vintage (RUCC is updated decennially; next release ~2033). See geo.ruca_codes for the finer-grained, tract-level rural-urban classification.
| Column | Type | Null | Description |
|---|---|---|---|
county_ |
string | no | 5-digit FIPS code for county (state + county, e.g., '06037' for Los Angeles County) |
state_ |
string | no | 2-digit FIPS code identifying the state (e.g., '06' for California) |
county_ |
string | yes | County name |
rucc_ |
int | no | Rural-Urban Continuum Code (1-9) |
rucc_ |
string | yes | Description of the RUCC category |
population |
int | yes | County population from Census |
metro_ |
string | yes | Metro (1-3) or Nonmetro (4-9) designation |
ruca_codes · table¶
USDA Rural-Urban Commuting Area (RUCA) codes at census tract level (2020-vintage FIPS), more granular than the county-level geo.rural_urban_continuum (RUCC) table. Carries a primary RUCA code (primary_ruca, 1-10), a secondary subdivision code (secondary_ruca), a description, and tract population, one row per tract for the 2020 vintage (RUCA is updated roughly decennially; next release ~2030).
| Column | Type | Null | Description |
|---|---|---|---|
tract_ |
string | yes | 11-digit census tract FIPS code (2020) |
state_ |
string | yes | 2-digit state FIPS code |
county_ |
string | yes | 5-digit county FIPS code (state + county) |
primary_ |
int | yes | Primary RUCA code (1-10) |
secondary_ |
double | yes | Secondary RUCA code for subdivision |
primary_ |
string | yes | Description of the primary RUCA category |
population |
int | yes | Tract population from Census |
gazetteer_counties · table¶
Census Bureau Gazetteer file for counties: county FIPS code, name, land/water area in square miles, and centroid latitude/longitude, one row per county per TIGER vintage year — currently starting 2012 in production, not 2010 like the boundary tables in this schema (geo.counties etc.); see observedCoverage below for the real floor rather than assuming the two match. Centroid and area only, no boundary polygon — see geo.counties for full WKT boundary geometry.
| Column | Type | Null | Description |
|---|---|---|---|
county_ |
string | no | 5-digit FIPS code for county (state + county, e.g., '06037' for Los Angeles County) |
state_ |
string | no | 2-digit FIPS code identifying the state (e.g., '06' for California) |
county_ |
string | no | Official county name |
land_ |
double | yes | Land area in square miles |
water_ |
double | yes | Water area in square miles |
latitude |
double | yes | Internal point latitude (centroid) |
longitude |
double | yes | Internal point longitude (centroid) |
gazetteer_places · table¶
Census Bureau Gazetteer file for incorporated places and CDPs: place FIPS code, name, place type, land/water area in square miles, and centroid latitude/longitude, one row per place per TIGER vintage year — currently starting 2012 in production, not 2010 like the boundary tables in this schema (geo.places etc.); see observedCoverage below for the real floor rather than assuming the two match. Unlike geo.places (boundary polygons only), this table also carries population and housing_units — but the Gazetteer file itself is a geometry/boundary product with no such columns, so both are enriched separately from ACS 5-year estimates (B01003_001E, B25001_001E), joined by place FIPS; a row with no ACS match, or a vintage more than 2 years newer than the latest published ACS 5-year release, correctly carries NULL rather than a guessed value.
| Column | Type | Null | Description |
|---|---|---|---|
place_ |
string | no | Place FIPS code (state + place) |
state_ |
string | no | 2-digit FIPS code identifying the state (e.g., '06' for California) |
place_ |
string | no | Official place name |
place_ |
string | yes | Place type (city, town, CDP, etc.) |
land_ |
double | yes | Land area in square miles |
water_ |
double | yes | Water area in square miles |
latitude |
double | yes | Internal point latitude (centroid) |
longitude |
double | yes | Internal point longitude (centroid) |
population |
int | yes | Total population (ACS 5-year estimate B01003_001E), joined by place_fips; not sourced from the Gazetteer file itself |
housing_ |
int | yes | Total housing units (ACS 5-year estimate B25001_001E), joined by place_fips; not sourced from the Gazetteer file itself |
gazetteer_zctas · table¶
Census Bureau Gazetteer file for ZIP Code Tabulation Areas: ZCTA code, land/water area in square miles, and centroid latitude/longitude, one row per ZCTA per TIGER vintage year — currently starting 2012 in production; see observedCoverage below for the real floor rather than assuming it matches the other TIGER boundary tables' 2010 start. Unlike geo.zctas (boundary polygons only) or geo.zcta_ref (static, non-vintaged reference), this table also carries population and housing_units — but the Gazetteer file itself is a geometry/boundary product with no such columns, so both are enriched separately from ACS 5-year estimates (B01003_001E, B25001_001E), joined by ZCTA code; a row with no ACS match, or a vintage more than 2 years newer than the latest published ACS 5-year release, correctly carries NULL rather than a guessed value.
| Column | Type | Null | Description |
|---|---|---|---|
zcta |
string | no | 5-digit ZCTA code |
land_ |
double | yes | Land area in square miles |
water_ |
double | yes | Water area in square miles |
latitude |
double | yes | Internal point latitude (centroid) |
longitude |
double | yes | Internal point longitude (centroid) |
population |
int | yes | Total population (ACS 5-year estimate B01003_001E), joined by zcta; not sourced from the Gazetteer file itself |
housing_ |
int | yes | Total housing units (ACS 5-year estimate B25001_001E), joined by zcta; not sourced from the Gazetteer file itself |
watersheds_huc2 · table¶
USGS Watershed Boundary Dataset HUC2 regions - the largest hydrologic units dividing the country into 22 major drainage basins.
| Column | Type | Null | Description |
|---|---|---|---|
huc2 |
string | no | 2-digit Hydrologic Unit Code |
name |
string | yes | Watershed region name |
area_ |
double | yes | Total area in square kilometers |
geometry |
string | yes | WKT representation of boundary polygon |
watersheds_huc4 · table¶
USGS Watershed Boundary Dataset HUC4 subregions - subdivisions of HUC2 regions into approximately 223 subregions.
| Column | Type | Null | Description |
|---|---|---|---|
huc4 |
string | no | 4-digit Hydrologic Unit Code |
huc2 |
string | no | Parent HUC2 region code |
name |
string | yes | Watershed subregion name |
area_ |
double | yes | Total area in square kilometers |
geometry |
string | yes | WKT representation of boundary polygon |
watersheds_huc8 · table¶
USGS Watershed Boundary Dataset HUC8 subbasins - commonly used watershed level for water quality and resource management with ~2,300 units.
| Column | Type | Null | Description |
|---|---|---|---|
huc8 |
string | no | 8-digit Hydrologic Unit Code |
huc4 |
string | no | Parent HUC4 subregion code |
name |
string | yes | Watershed subbasin name |
area_ |
double | yes | Total area in square kilometers |
geometry |
string | yes | WKT representation of boundary polygon |
watersheds_huc12 · table¶
USGS Watershed Boundary Dataset HUC12 subwatersheds - the smallest standard hydrologic units with approximately 100,000 units nationally.
| Column | Type | Null | Description |
|---|---|---|---|
huc12 |
string | no | 12-digit Hydrologic Unit Code |
huc8 |
string | no | Parent HUC8 subbasin code |
name |
string | yes | Subwatershed name |
area_ |
double | yes | Total area in square kilometers |
geometry |
string | yes | WKT representation of boundary polygon |
county_adjacency · table¶
Real Census Bureau County Adjacency File (https://www2.census.gov/geo/docs/ reference/county_adjacency.txt) — one row per adjacent county pair, undirected-symmetric as published (A/B and B/A each get a row; verified live 2026-08-21, sampled 2000 pairs, 0 missing reverse pairs). Excludes the source file's self-pair row (every county's block starts by listing itself) — 3,234 excluded in the verified live file, matching the county count. year is a fetch/vintage marker, not a field carried by the source file itself — the Census Bureau publishes one evergreen, non-year-versioned file (confirmed live: county_adjacency2023.txt/2024.txt/2025.txt all 404; only the un-suffixed county_adjacency.txt resolves), so this table is re-fetched under each configured year purely to give the ETL pool a freshness/re-fetch cadence. Column names match the table this replaces (county_fips, state_fips, county_name, neighbor_county_fips, neighbor_state_fips, neighbor_county_name) for compatibility with existing queries against county_adjacency.
| Column | Type | Null | Description |
|---|---|---|---|
county_ |
string | no | 5-digit county FIPS (state+county) — PK component. FK to geo.counties.county_fips. |
state_ |
string | no | 2-digit state FIPS, derived from county_fips[0:2] — FK to geo.state_ref.state_fips |
county_ |
string | yes | County name with the source file's trailing ", ST" state-abbreviation suffix stripped (e.g. source "Autauga County, AL" -> "Autauga County"), to match geo.counties.county_name's convention. |
neighbor_ |
string | no | 5-digit FIPS of the adjacent county — PK component. FK to geo.counties.county_fips. |
neighbor_ |
string | no | 2-digit state FIPS of the adjacent county, derived from neighbor_county_fips[0:2] — FK to geo.state_ref.state_fips |
neighbor_ |
string | yes | Adjacent county's name, state suffix stripped — same convention as county_name |
year |
int | no | Fetch/vintage year — PK component. Not a field in the source file (see table comment); the source is refetched once per configured year. |
county_adjacency_st_touches_legacy · view¶
SUPERSEDED — county_adjacency is now a real ingested table (see partitionedTables above), sourced directly from the Census Bureau's published County Adjacency File, not derived from boundary geometry. This view is kept only for comparison against the old derivation and is no longer wired up as county_adjacency; nothing else in this schema references it. Original comment, preserved as-is: One row per adjacent county pair (undirected, both directions present — A/B and B/A each get a row) for the most recent TIGER vintage year present in counties (MAX(year), not a hard-coded year — this table is partitioned by TIGER vintage and a self-join across multiple vintages would produce a cartesian blowup of spurious pairs). Adjacency is true geometric adjacency (ST_Touches on the WKT boundary polygons via DuckDB's spatial extension) — counties that share only a single point (e.g. the Four Corners) count as touching, same as sharing a full border segment. Island counties with no land or water-crossing land border to any other county (all of Hawaii, plus isolated mainland island counties) legitimately have zero rows here — see geo_dq.sql's county_adjacency coverage check for the acknowledged-exception list. KNOWN BAD: this ST_Touches derivation covers only roughly 15% of true county adjacency, because TIGER shared-edge polygons rarely align exactly enough for an exact-touch predicate to fire — this is why it was replaced.
View — columns are resolved by the query engine at runtime.