# TDF Buildings - Excel to ArcGIS Online Feature Layer Pipeline Version: v10 direct hosted feature service publish. This notebook reads the TDF Buildings workbook, applies reviewer-managed overrides, geocodes addresses, validates geocode results, and publishes a simplified operational hosted feature layer schema by directly creating a hosted feature service and adding a named point layer. Full QA fields remain available in the generated CSV outputs but are not published to the feature layer. ## Table of Contents 1. [Configuration](#1.-Configuration) 2. [Imports, logging, and ArcGIS connection](#2.-Imports,-logging,-and-ArcGIS-connection) 3. [Read the workbook](#3.-Read-the-workbook) 4. [Standardize fields](#4.-Standardize-fields) 5. [Address and attribute quality checks](#5.-Address-and-attribute-quality-checks) 6. [Manual override control file](#6.-Manual-override-control-file) 7. [Geocode unique addresses](#7.-Geocode-unique-addresses) 8. [Join geocode results and apply manual overrides](#8.-Join-geocode-results-and-apply-manual-overrides) 9. [Create a Spatially Enabled DataFrame](#9.-Create-a-Spatially-Enabled-DataFrame) 10. [Optional map preview](#10.-Optional-map-preview) 11. [Publish or refresh hosted feature layer](#11.-Publish-or-refresh-hosted-feature-layer) 12. [Run Summary](#12.-Run-Summary) 13. [Final output checklist](#13.-Final-output-checklist) ## 1. Configuration Upload the Excel workbook into the notebook files area. In ArcGIS Online Notebooks, uploaded files are normally available under `/arcgis/home`. Set `run_geocoding = True` only after reviewing the normalized data and QA tables. Set `run_publish = True` only after reviewing the geocoded outputs. Manual corrections should be entered in `tdf_buildings_manual_overrides.csv`, not in the raw source workbook. The override file supports corrected address values, manual coordinates, acceptance of a verified geocoder candidate, and intentional publish exclusions. New publishes are created directly as hosted feature services; the notebook no longer uses the Spatially Enabled DataFrame `to_featurelayer()` helper for new publishes, which avoids creating a separate source File Geodatabase item. `pd.read_excel()` reads this `.xlsx` workbook with the `openpyxl` engine. ArcGIS Online Notebooks normally include `openpyxl`; local notebook environments may need `pip install openpyxl`. The default workbook path assumes this file name exists in `/arcgis/home`: `RPP 2026 AGR Building & Land Status Check.xlsx` ```python from __future__ import annotations from dataclasses import dataclass from pathlib import Path from typing import Optional # ArcGIS Online Notebooks normally use /arcgis/home. HOME = Path('/arcgis/home/tdf_facilities') if Path('/arcgis/home/tdf_facilities').exists() else Path.cwd() @dataclass(frozen=True) class NotebookConfig: project_name: str = 'tdf_buildings_pipeline' # Paths source_path: Path = HOME / 'RPP 2026 AGR Building & Land Status Check.xlsx' sheet_name: str = 'Buildings' header_row: int = 1 # pandas is zero-based; 1 means Excel row 2. excel_engine: str = 'openpyxl' output_dir: Path = HOME / 'tdf_buildings_outputs' log_dir: Path = HOME / 'tdf_buildings_outputs' / 'logs' # Manual review/correction file. Do not edit the raw workbook for pipeline-only fixes. manual_overrides_file_name: str = 'tdf_buildings_manual_overrides.csv' # Safety switches. Leave publishing off until QA and geocoding outputs are reviewed. run_geocoding: bool = False run_publish: bool = True # Geocoding settings. geocode_score_min: int = 85 geocode_mode: str = 'single' # 'single' or 'batch' geocode_batch_size: Optional[int] = None # None uses locator SuggestedBatchSize, else 100 geocode_delay_seconds: float = 0.25 source_country: str = 'USA' geocode_location_type: str = 'rooftop' # 'rooftop' or 'street' geocode_category: Optional[str] = None # Example: 'Point Address,Street Address' geocode_cache_version: str = 'v2_result_validation' force_regeocode: bool = False # Keep False after a validated cache exists; set True only to rebuild geocodes. max_allowed_suspect_assignments: int = 10 minimum_publish_ready_records: int = 1 write_full_geocode_review: bool = True # True writes all geocode-review records, not only assignment suspects. # Manual coordinate sanity bounds. These broad bounds catch sign mistakes and transposed coordinates. coordinate_min_latitude: float = 34.0 coordinate_max_latitude: float = 37.5 coordinate_min_longitude: float = -91.5 coordinate_max_longitude: float = -80.5 # Publishing settings # publish_title is the ArcGIS Online item title. # publish_service_name is the URL-safe FeatureServer service name. # publish_layer_name is the Layer (0) name shown inside the service. publish_title: str = 'TDF Buildings and Facilities' publish_service_name: str = 'TDF_Buildings_and_Facilities' publish_layer_name: str = 'TDF Buildings and Facilities' allow_service_name_suffix_if_unavailable: bool = False max_record_count: int = 2000 publish_tags: tuple[str, ...] = ('TDF', 'Buildings', 'Facilities', 'Assets', 'Bureaus Veritas') publish_summary: str = 'Point locations for buildings owned and managed by Tennessee Division of Forestry.' publish_description: str = "This hosted feature layer is generated from the Buildings worksheet in the RPP building and land status workbook. Coordinates are produced from address geocoding, reviewer-managed manual overrides, and notebook QA outputs. This layer is intended for planning, asset management, and public-information workflows. It is not a legal survey or substitute for official deeds, surveys, or property records." # Leave blank to create a new layer. After first publish, paste item ID here to refresh. feature_layer_item_id: str = 'a2f0811ed0e44e3e8933411739302e92' # Existing-layer refresh behavior refresh_strategy: str = 'upsert' # 'upsert', 'truncate_add', or 'delete_add' sync_key_field: str = 'building_code' upsert_delete_missing_records: bool = True publish_batch_size: int = 200 truncate_async: bool = False CONFIG = NotebookConfig() CONFIG.output_dir.mkdir(parents=True, exist_ok=True) CONFIG.log_dir.mkdir(parents=True, exist_ok=True) NORMALIZED_CSV = CONFIG.output_dir / 'tdf_buildings_normalized.csv' GEOCODED_CSV = CONFIG.output_dir / 'tdf_buildings_geocoded.csv' GEOCODE_CACHE_CSV = CONFIG.output_dir / 'tdf_buildings_geocode_cache.csv' GEOCODE_VALIDATION_CSV = CONFIG.output_dir / 'tdf_buildings_geocode_validation_review.csv' QA_REPORT_CSV = CONFIG.output_dir / 'tdf_buildings_quality_report.csv' ADDRESS_REVIEW_CSV = CONFIG.output_dir / 'tdf_buildings_address_review.csv' SHARED_ADDRESS_SUMMARY_CSV = CONFIG.output_dir / 'tdf_buildings_shared_address_summary.csv' ZIP_CONFLICTS_CSV = CONFIG.output_dir / 'tdf_buildings_zip_conflicts.csv' POTENTIAL_TYPOS_CSV = CONFIG.output_dir / 'tdf_buildings_potential_typos.csv' SQUARE_FEET_REVIEW_CSV = CONFIG.output_dir / 'tdf_buildings_square_feet_review.csv' QA_REVIEW_XLSX = CONFIG.output_dir / 'tdf_buildings_qa_review.xlsx' MANUAL_OVERRIDES_CSV = CONFIG.output_dir / CONFIG.manual_overrides_file_name LEGACY_MANUAL_COORDINATE_OVERRIDES_CSV = CONFIG.output_dir / 'tdf_buildings_manual_coordinate_overrides.csv' MANUAL_OVERRIDE_AUDIT_CSV = CONFIG.output_dir / 'tdf_buildings_manual_override_audit.csv' PUBLISH_FIELDS_PREVIEW_CSV = CONFIG.output_dir / 'tdf_buildings_publish_fields_preview.csv' # The generated local file geodatabase/source item is intentionally avoided in v10 new-publish mode. # New publishing uses gis.content.create_service() + FeatureLayerCollection.manager.add_to_definition(). # Operational hosted-feature-layer schema. # Internal QA/geocoding outputs keep additional diagnostic fields; only these fields are sent to AGOL when publishing. PUBLISH_ATTRIBUTE_FIELDS = ( 'building_code', 'building_name', 'address', 'city', 'county', 'state', 'postal_code', 'longitude', 'latitude', 'square_feet', 'predominant_facility_use', 'building_utilization', 'asset_status', 'strategic_asset_status', 'building_status', 'geocode_source', 'override_notes', 'override_source', ) PUBLISH_FIELD_ALIASES = { 'building_code': 'Building Code', 'building_name': 'Building Name', 'address': 'Address', 'city': 'City', 'county': 'County', 'state': 'State', 'postal_code': 'Postal Code', 'longitude': 'Longitude', 'latitude': 'Latitude', 'square_feet': 'Square Feet', 'predominant_facility_use': 'Predominant Facility Use', 'building_utilization': 'Building Utilization', 'asset_status': 'Asset Status', 'strategic_asset_status': 'Strategic Asset Status', 'building_status': 'Building Status', 'geocode_source': 'Geocode Source', 'override_notes': 'Override Notes', 'override_source': 'Override Source', } CONFIG ``` NotebookConfig(project_name='tdf_buildings_pipeline', source_path=PosixPath('/arcgis/home/tdf_facilities/RPP 2026 AGR Building & Land Status Check.xlsx'), sheet_name='Buildings', header_row=1, excel_engine='openpyxl', output_dir=PosixPath('/arcgis/home/tdf_facilities/tdf_buildings_outputs'), log_dir=PosixPath('/arcgis/home/tdf_facilities/tdf_buildings_outputs/logs'), manual_overrides_file_name='tdf_buildings_manual_overrides.csv', run_geocoding=False, run_publish=True, geocode_score_min=85, geocode_mode='single', geocode_batch_size=None, geocode_delay_seconds=0.25, source_country='USA', geocode_location_type='rooftop', geocode_category=None, geocode_cache_version='v2_result_validation', force_regeocode=False, max_allowed_suspect_assignments=10, minimum_publish_ready_records=1, write_full_geocode_review=True, coordinate_min_latitude=34.0, coordinate_max_latitude=37.5, coordinate_min_longitude=-91.5, coordinate_max_longitude=-80.5, publish_title='TDF Buildings and Facilities', publish_service_name='TDF_Buildings_and_Facilities', publish_layer_name='TDF Buildings and Facilities', allow_service_name_suffix_if_unavailable=False, max_record_count=2000, publish_tags=('TDF', 'Buildings', 'Facilities', 'Assets', 'Bureaus Veritas'), publish_summary='Point locations for buildings owned and managed by Tennessee Division of Forestry.', publish_description='This hosted feature layer is generated from the Buildings worksheet in the RPP building and land status workbook. Coordinates are produced from address geocoding, reviewer-managed manual overrides, and notebook QA outputs. This layer is intended for planning, asset management, and public-information workflows. It is not a legal survey or substitute for official deeds, surveys, or property records.', feature_layer_item_id='', refresh_strategy='upsert', sync_key_field='building_code', upsert_delete_missing_records=True, publish_batch_size=200, truncate_async=False) ## 2. Imports, logging, and ArcGIS connection This uses the active ArcGIS Online notebook session. Standard execution logs are dumped to a `logs/` directory. ```python import json import logging import re import sys import time from datetime import datetime, timezone import pandas as pd from IPython.display import display try: import openpyxl except ImportError as exc: raise ImportError( 'Reading .xlsx files with pandas requires openpyxl. ' 'ArcGIS Online Notebooks normally include it; local environments can install it with: pip install openpyxl' ) from exc from arcgis.gis import GIS from arcgis.geocoding import get_geocoders, geocode, batch_geocode from arcgis.features import GeoAccessor, GeoSeriesAccessor, FeatureLayerCollection RUN_STARTED_UTC = datetime.now(timezone.utc) RUN_ID = RUN_STARTED_UTC.strftime("%Y%m%dT%H%M%SZ") log_path = CONFIG.log_dir / f"{CONFIG.project_name}_{RUN_ID}.log" logger = logging.getLogger(CONFIG.project_name) logger.setLevel(logging.INFO) logger.handlers.clear() formatter = logging.Formatter( fmt="%(asctime)s | %(levelname)s | %(name)s | %(message)s", datefmt="%Y-%m-%d %H:%M:%S", ) stream_handler = logging.StreamHandler(sys.stdout) stream_handler.setFormatter(formatter) file_handler = logging.FileHandler(log_path, encoding="utf-8") file_handler.setFormatter(formatter) logger.addHandler(stream_handler) logger.addHandler(file_handler) logger.info("Notebook run started") logger.info("Run ID: %s", RUN_ID) logger.info("Log path: %s", log_path) # Uses the authenticated ArcGIS Online notebook context. gis = GIS('home') logger.info('Connected to: %s', gis.properties.get('name', gis.url)) logger.info('User: %s', gis.users.me.username) logger.info('openpyxl version: %s', openpyxl.__version__) ``` 2026-07-08 19:25:06 | INFO | tdf_buildings_pipeline | Notebook run started 2026-07-08 19:25:06 | INFO | tdf_buildings_pipeline | Run ID: 20260708T192506Z 2026-07-08 19:25:06 | INFO | tdf_buildings_pipeline | Log path: /arcgis/home/tdf_facilities/tdf_buildings_outputs/logs/tdf_buildings_pipeline_20260708T192506Z.log You are logged on as colin.stiles_tndof with an administrator role, proceed with caution. 2026-07-08 19:25:07 | INFO | tdf_buildings_pipeline | Connected to: Tennessee Division of Forestry 2026-07-08 19:25:07 | INFO | tdf_buildings_pipeline | User: colin.stiles_tndof 2026-07-08 19:25:07 | INFO | tdf_buildings_pipeline | openpyxl version: 3.1.5 ## 3. Read the workbook The workbook has a title/notes row above the headers. The `Buildings` headers are on Excel row 2, so the configuration uses `header_row = 1`. ```python if not CONFIG.source_path.exists(): xlsx_files = sorted(p.name for p in HOME.glob('*.xlsx')) raise FileNotFoundError( f'Workbook not found: {CONFIG.source_path}. XLSX files found in {HOME}: {xlsx_files}' ) raw = pd.read_excel( CONFIG.source_path, sheet_name=CONFIG.sheet_name, header=CONFIG.header_row, dtype=str, engine=CONFIG.excel_engine, ) raw = raw.dropna(how='all').copy() raw.columns = [re.sub(r'\s+', ' ', str(c).strip()) for c in raw.columns] logger.info('Read %s records from sheet "%s".', len(raw), CONFIG.sheet_name) display(raw.head()) ``` 2026-07-08 19:25:07 | INFO | tdf_buildings_pipeline | Read 463 records from sheet "Buildings".
Building Code Building Name Address 1 City Code State Code Postal Code County SF Strategic Asset Status Predominant Facility Use Asset Status Building Utilization Historical Status Building Status Comments
0 10000006 Holston Mtn Radio Building National Forest 56 ELIZABETHTON TN 37643 CARTER 600 Long Term 15+ yrs Antenna/Tower Current Mission Need Utilized Not Evaluated Active NaN
1 10010001 Carter County Residence 2651 Gap Creek Road HAMPTON TN 37658 CARTER 1400 Long Term 15+ yrs Residential Current Mission Need Utilized Not Evaluated Active NaN
2 10010002 Carter County Outbuilding #2 2651 Gap Creek Road HAMPTON TN 37658 CARTER 1600 Long Term 15+ yrs Warehouse Space Current Mission Need Utilized Not Evaluated Active NaN
3 10010003 Carter County Shop 2643 Gap Creek Road HAMPTON TN 37658 CARTER 1400 Long Term 15+ yrs Public Safety Current Mission Need Utilized Not Evaluated Active NaN
4 01010001 Bluebird Storage Building 395 Firetower Lane HEISKELL TN 37754 ANDERSON 48 Long Term 15+ yrs Warehouse Space Current Mission Need Utilized Not Evaluated Active NaN
## 4. Standardize fields The output field names are stable, lowercase, and GIS-friendly. This makes future refreshes safer because hosted layer schemas are sensitive to field name changes. This section also preserves `building_code` as an 8-character text value and `postal_code` as a 5-character text value so leading zeroes are not lost during CSV reads, joins, or hosted-layer refreshes. ```python SOURCE_TO_OUTPUT_FIELD = { 'Building Code': 'building_code', 'Building Name': 'building_name', 'Address 1': 'address', 'City Code': 'city', 'State Code': 'state', 'Postal Code': 'postal_code', 'County': 'county', 'SF': 'square_feet', 'Strategic Asset Status': 'strategic_asset_status', 'Predominant Facility Use': 'predominant_facility_use', 'Asset Status': 'asset_status', 'Building Utilization': 'building_utilization', 'Historical Status': 'historical_status', 'Building Status': 'building_status', 'Comments': 'comments', } missing_columns = [c for c in SOURCE_TO_OUTPUT_FIELD if c not in raw.columns] if missing_columns: raise ValueError(f'Missing expected source columns: {missing_columns}') df = raw.rename(columns=SOURCE_TO_OUTPUT_FIELD)[list(SOURCE_TO_OUTPUT_FIELD.values())].copy() def clean_text(value) -> str: """Normalize a workbook cell to a stripped string without Excel-style .0 artifacts.""" if pd.isna(value): return '' text = str(value).strip() text = re.sub(r'\.0$', '', text) text = re.sub(r'\s+', ' ', text) return text def clean_identifier(value, width: int | None = None) -> str: """Clean an identifier and left-pad numeric identifiers to a stable text width.""" text = clean_text(value).replace(' ', '') if width and text.isdigit(): return text.zfill(width) return text def clean_postal_code(value) -> str: """Clean ZIP values while preserving leading zeroes and the first five ZIP digits.""" text = clean_text(value) if not text: return '' match = re.match(r'^(\d{1,5})(?:-?\d{4})?$', text) if match: return match.group(1).zfill(5) digits = re.sub(r'\D+', '', text) if 1 <= len(digits) <= 5: return digits.zfill(5) if len(digits) > 5: return digits[:5] return text def parse_bool(value) -> bool: text = clean_text(value).upper() return text in {'1', 'TRUE', 'T', 'YES', 'Y', 'X'} def clean_display_part(value) -> str: return clean_text(value) def clean_address_key_part(value) -> str: """Create stable address-key components without trailing-space artifacts.""" text = clean_text(value).upper() text = re.sub(r'[^A-Z0-9]+', ' ', text) text = re.sub(r'\s+', ' ', text).strip() return text def make_full_address(row) -> str: parts = [row['address'], row['city'], row['state'], row['postal_code']] return ', '.join(clean_display_part(p) for p in parts if clean_display_part(p)) def make_address_key(row) -> str: parts = [row['address'], row['city'], row['state'], row['postal_code']] return '|'.join(clean_address_key_part(p) for p in parts) def make_address_city_key(row) -> str: parts = [row['address'], row['city'], row['state']] return '|'.join(clean_address_key_part(p) for p in parts) def coordinate_in_configured_bounds(latitude, longitude) -> bool: if pd.isna(latitude) or pd.isna(longitude): return False return ( CONFIG.coordinate_min_latitude <= float(latitude) <= CONFIG.coordinate_max_latitude and CONFIG.coordinate_min_longitude <= float(longitude) <= CONFIG.coordinate_max_longitude ) TEXT_FIELDS = [c for c in df.columns if c != 'square_feet'] for col in TEXT_FIELDS: df[col] = df[col].map(clean_text) # Preserve key fields as text. Do not cast these fields to numeric. df['building_code'] = df['building_code'].map(lambda value: clean_identifier(value, width=8)) df['postal_code'] = df['postal_code'].map(clean_postal_code) df['square_feet_raw'] = df['square_feet'].map(clean_text) df['square_feet'] = ( df['square_feet_raw'] .str.replace(',', '', regex=False) .str.strip() ) df['square_feet'] = pd.to_numeric(df['square_feet'], errors='coerce') # Preserve source values before applying reviewer-managed overrides. SOURCE_VALUE_COLUMNS = { 'address': 'source_address', 'city': 'source_city', 'state': 'source_state', 'postal_code': 'source_postal_code', 'county': 'source_county', } for source_col, preserved_col in SOURCE_VALUE_COLUMNS.items(): df[preserved_col] = df[source_col] MANUAL_OVERRIDE_COLUMNS = [ 'building_code', 'building_name', 'source_address', 'source_city', 'source_state', 'source_postal_code', 'source_county', 'override_address', 'override_city', 'override_state', 'override_postal_code', 'override_county', 'latitude', 'longitude', 'accept_geocode_candidate', 'exclude_from_publish', 'override_notes', 'override_source', ] MANUAL_OVERRIDE_EDITABLE_COLUMNS = [ 'override_address', 'override_city', 'override_state', 'override_postal_code', 'override_county', 'latitude', 'longitude', 'accept_geocode_candidate', 'exclude_from_publish', 'override_notes', 'override_source', ] def read_manual_override_file() -> pd.DataFrame: """Read or create the reviewer-managed override CSV. The file is intentionally separate from the raw source workbook. It contains one row per building_code plus editable override fields. The notebook refreshes reference columns but preserves reviewer-entered override values. """ if MANUAL_OVERRIDES_CSV.exists(): overrides = pd.read_csv(MANUAL_OVERRIDES_CSV, dtype=str) logger.info('Loaded manual override file: %s', MANUAL_OVERRIDES_CSV) elif LEGACY_MANUAL_COORDINATE_OVERRIDES_CSV.exists(): overrides = pd.read_csv(LEGACY_MANUAL_COORDINATE_OVERRIDES_CSV, dtype=str) logger.warning( 'Migrating legacy coordinate override file into expanded manual override file: %s', MANUAL_OVERRIDES_CSV, ) else: overrides = pd.DataFrame(columns=MANUAL_OVERRIDE_COLUMNS) logger.info('Creating new manual override file: %s', MANUAL_OVERRIDES_CSV) for col in MANUAL_OVERRIDE_COLUMNS: if col not in overrides.columns: overrides[col] = '' overrides['building_code'] = overrides['building_code'].map(lambda value: clean_identifier(value, width=8)) overrides = overrides.loc[overrides['building_code'].ne('')].copy() if overrides['building_code'].duplicated().any(): duplicate_codes = sorted(overrides.loc[overrides['building_code'].duplicated(keep=False), 'building_code'].unique())[:20] logger.warning( 'Manual override file has duplicate building_code rows. Keeping the last row for each code. Examples: %s', duplicate_codes, ) overrides = overrides.drop_duplicates('building_code', keep='last') current_reference = df[[ 'building_code', 'building_name', 'source_address', 'source_city', 'source_state', 'source_postal_code', 'source_county' ]].copy() # Preserve editable values from the existing override file and refresh reference columns from the source workbook. editable = overrides[['building_code'] + MANUAL_OVERRIDE_EDITABLE_COLUMNS].copy() merged = current_reference.merge(editable, on='building_code', how='left') # Keep any override rows that no longer exist in the source workbook, so reviewer work is not silently discarded. extra_override_rows = overrides.loc[~overrides['building_code'].isin(current_reference['building_code'])].copy() if not extra_override_rows.empty: extra_override_rows = extra_override_rows[MANUAL_OVERRIDE_COLUMNS].copy() extra_override_rows['building_name'] = extra_override_rows['building_name'].fillna('[not in current source workbook]') merged = pd.concat([merged, extra_override_rows], ignore_index=True) logger.warning('Manual override file includes %s building_code rows not found in the current source workbook.', len(extra_override_rows)) for col in MANUAL_OVERRIDE_COLUMNS: if col not in merged.columns: merged[col] = '' merged = merged[MANUAL_OVERRIDE_COLUMNS].fillna('') merged.to_csv(MANUAL_OVERRIDES_CSV, index=False) logger.info('Wrote/refreshed manual override file without changing reviewer-entered override values: %s', MANUAL_OVERRIDES_CSV) return merged manual_overrides = read_manual_override_file() manual_override_current = manual_overrides.loc[manual_overrides['building_code'].isin(df['building_code'])].copy() manual_override_by_code = manual_override_current.set_index('building_code', drop=False) # Join override values onto the working dataframe. override_join_columns = ['building_code'] + MANUAL_OVERRIDE_EDITABLE_COLUMNS df = df.merge( manual_override_current[override_join_columns], on='building_code', how='left', ) for col in MANUAL_OVERRIDE_EDITABLE_COLUMNS: if col not in df.columns: df[col] = '' df[col] = df[col].fillna('').map(clean_text) # Clean override-specific fields. df['override_postal_code'] = df['override_postal_code'].map(clean_postal_code) df['manual_latitude'] = pd.to_numeric(df['latitude'], errors='coerce') df['manual_longitude'] = pd.to_numeric(df['longitude'], errors='coerce') df['manual_coordinate_entered'] = df['manual_latitude'].notna() | df['manual_longitude'].notna() df['manual_coordinate_valid'] = [ coordinate_in_configured_bounds(lat, lon) for lat, lon in zip(df['manual_latitude'], df['manual_longitude']) ] df['manual_coordinate_invalid'] = df['manual_coordinate_entered'] & ~df['manual_coordinate_valid'] df['accepted_geocode_candidate'] = df['accept_geocode_candidate'].map(parse_bool) df['exclude_from_publish_override'] = df['exclude_from_publish'].map(parse_bool) override_field_pairs = [ ('override_address', 'address'), ('override_city', 'city'), ('override_state', 'state'), ('override_postal_code', 'postal_code'), ('override_county', 'county'), ] for override_col, target_col in override_field_pairs: mask = df[override_col].ne('') if target_col == 'postal_code': df.loc[mask, target_col] = df.loc[mask, override_col].map(clean_postal_code) else: df.loc[mask, target_col] = df.loc[mask, override_col].map(clean_text) address_override_cols = ['override_address', 'override_city', 'override_state', 'override_postal_code', 'override_county'] df['has_address_override'] = df[address_override_cols].apply(lambda row: any(clean_text(v) for v in row), axis=1) df['has_any_manual_override'] = ( df['has_address_override'] | df['manual_coordinate_entered'] | df['accepted_geocode_candidate'] | df['exclude_from_publish_override'] | df['override_notes'].ne('') | df['override_source'].ne('') ) # Build effective address keys after address/city/state/ZIP overrides are applied. df['full_address'] = df.apply(make_full_address, axis=1) df['address_key'] = df.apply(make_address_key, axis=1) df['address_city_key'] = df.apply(make_address_city_key, axis=1) # Add an automatic timestamp for when the record was processed. df['last_processed_utc'] = RUN_STARTED_UTC.replace(microsecond=0).isoformat() manual_override_audit = df.loc[df['has_any_manual_override'], [ 'building_code', 'building_name', 'source_address', 'source_city', 'source_state', 'source_postal_code', 'source_county', 'address', 'city', 'state', 'postal_code', 'county', 'override_address', 'override_city', 'override_state', 'override_postal_code', 'override_county', 'manual_latitude', 'manual_longitude', 'manual_coordinate_valid', 'manual_coordinate_invalid', 'accepted_geocode_candidate', 'exclude_from_publish_override', 'override_notes', 'override_source', ]].copy() manual_override_audit.to_csv(MANUAL_OVERRIDE_AUDIT_CSV, index=False) logger.info('Saved manual override audit CSV: %s', MANUAL_OVERRIDE_AUDIT_CSV) manual_override_summary = pd.DataFrame([ {'metric': 'Manual override file rows', 'count': len(manual_overrides)}, {'metric': 'Current source records with any override', 'count': int(df['has_any_manual_override'].sum())}, {'metric': 'Records with address/city/state/ZIP/county override', 'count': int(df['has_address_override'].sum())}, {'metric': 'Records with valid manual coordinates', 'count': int(df['manual_coordinate_valid'].sum())}, {'metric': 'Records with invalid manual coordinates', 'count': int(df['manual_coordinate_invalid'].sum())}, {'metric': 'Records accepting geocode candidate', 'count': int(df['accepted_geocode_candidate'].sum())}, {'metric': 'Records excluded from publish by override', 'count': int(df['exclude_from_publish_override'].sum())}, ]) display(manual_override_summary) display(df.head()) logger.info('Dataframe dtypes:\n%s', df.dtypes) ``` 2026-07-08 19:25:07 | INFO | tdf_buildings_pipeline | Loaded manual override file: /arcgis/home/tdf_facilities/tdf_buildings_outputs/tdf_buildings_manual_overrides.csv 2026-07-08 19:25:07 | INFO | tdf_buildings_pipeline | Wrote/refreshed manual override file without changing reviewer-entered override values: /arcgis/home/tdf_facilities/tdf_buildings_outputs/tdf_buildings_manual_overrides.csv 2026-07-08 19:25:07 | INFO | tdf_buildings_pipeline | Saved manual override audit CSV: /arcgis/home/tdf_facilities/tdf_buildings_outputs/tdf_buildings_manual_override_audit.csv
metric count
0 Manual override file rows 463
1 Current source records with any override 9
2 Records with address/city/state/ZIP/county ove... 9
3 Records with valid manual coordinates 9
4 Records with invalid manual coordinates 0
5 Records accepting geocode candidate 0
6 Records excluded from publish by override 0
building_code building_name address city state postal_code county square_feet strategic_asset_status predominant_facility_use ... manual_coordinate_valid manual_coordinate_invalid accepted_geocode_candidate exclude_from_publish_override has_address_override has_any_manual_override full_address address_key address_city_key last_processed_utc
0 10000006 Holston Mtn Radio Building National Forest 56 ELIZABETHTON TN 37643 CARTER 600.0 Long Term 15+ yrs Antenna/Tower ... False False False False False False National Forest 56, ELIZABETHTON, TN, 37643 NATIONAL FOREST 56|ELIZABETHTON|TN|37643 NATIONAL FOREST 56|ELIZABETHTON|TN 2026-07-08T19:25:06+00:00
1 10010001 Carter County Residence 2651 Gap Creek Road HAMPTON TN 37658 CARTER 1400.0 Long Term 15+ yrs Residential ... False False False False False False 2651 Gap Creek Road, HAMPTON, TN, 37658 2651 GAP CREEK ROAD|HAMPTON|TN|37658 2651 GAP CREEK ROAD|HAMPTON|TN 2026-07-08T19:25:06+00:00
2 10010002 Carter County Outbuilding #2 2651 Gap Creek Road HAMPTON TN 37658 CARTER 1600.0 Long Term 15+ yrs Warehouse Space ... False False False False False False 2651 Gap Creek Road, HAMPTON, TN, 37658 2651 GAP CREEK ROAD|HAMPTON|TN|37658 2651 GAP CREEK ROAD|HAMPTON|TN 2026-07-08T19:25:06+00:00
3 10010003 Carter County Shop 2643 Gap Creek Road HAMPTON TN 37658 CARTER 1400.0 Long Term 15+ yrs Public Safety ... False False False False False False 2643 Gap Creek Road, HAMPTON, TN, 37658 2643 GAP CREEK ROAD|HAMPTON|TN|37658 2643 GAP CREEK ROAD|HAMPTON|TN 2026-07-08T19:25:06+00:00
4 01010001 Bluebird Storage Building 395 Firetower Lane HEISKELL TN 37754 ANDERSON 48.0 Long Term 15+ yrs Warehouse Space ... False False False False False False 395 Firetower Lane, HEISKELL, TN, 37754 395 FIRETOWER LANE|HEISKELL|TN|37754 395 FIRETOWER LANE|HEISKELL|TN 2026-07-08T19:25:06+00:00

5 rows × 45 columns

2026-07-08 19:25:07 | INFO | tdf_buildings_pipeline | Dataframe dtypes: building_code str building_name str address str city str state str postal_code str county str square_feet float64 strategic_asset_status str predominant_facility_use str asset_status str building_utilization str historical_status str building_status str comments str square_feet_raw str source_address str source_city str source_state str source_postal_code str source_county str override_address str override_city str override_state str override_postal_code str override_county str latitude str longitude str accept_geocode_candidate str exclude_from_publish str override_notes str override_source str manual_latitude float64 manual_longitude float64 manual_coordinate_entered bool manual_coordinate_valid bool manual_coordinate_invalid bool accepted_geocode_candidate bool exclude_from_publish_override bool has_address_override bool has_any_manual_override bool full_address str address_key str address_city_key str last_processed_utc str dtype: object ## 5. Address and attribute quality checks Review these tables before enabling geocoding. The notebook flags conditions that typically create weak geocoding results, including blank or placeholder addresses, PO Boxes, addresses that do not start with a physical site/house number, legacy route/box addresses, missing city/state/postal values, duplicate building codes, shared site addresses, ZIP conflicts, and likely typo candidates. ```python PLACEHOLDER_VALUES = {'', 'NONE', 'UNIDENTIFIED', 'UNKNOWN', 'N/A', 'NA', 'TBD'} PO_BOX_PATTERN = r'\bP\.?\s*O\.?\s*BOX\b|\bPOST\s+OFFICE\s+BOX\b' HOUSE_NUMBER_PATTERN = r'^\s*\d+[A-Z]?(?:[-/]\d+[A-Z]?)?\b' # Target legacy rural-route patterns without treating valid numbered road addresses # such as "962 Old State Route 48" as legacy route/box addresses. LEGACY_ROUTE_PATTERN = ( r'^\s*(?:HCR|HC|RR|RFD)\b' r'|^\s*(?:RT|RTE|ROUTE)\s+\d+\b' r'|\b(?:BOX|BX)\s+\d+\b' ) address_upper = df['address'].fillna('').astype(str).str.upper().str.strip() df['is_placeholder_address'] = ( address_upper.isin(PLACEHOLDER_VALUES) | address_upper.str.contains(r'UNIDENTIFIED|UNKNOWN|NO ADDRESS', regex=True, na=False) ) df['is_po_box_address'] = address_upper.str.contains(PO_BOX_PATTERN, regex=True, na=False) df['legacy_route_or_box_address'] = ( address_upper.str.contains(LEGACY_ROUTE_PATTERN, regex=True, na=False) & ~df['is_po_box_address'] ) df['address_lacks_digit'] = ~address_upper.str.contains(r'\d', regex=True, na=False) df['address_starts_with_site_number'] = address_upper.str.match(HOUSE_NUMBER_PATTERN, na=False) df['address_does_not_start_with_site_number'] = ~df['address_starts_with_site_number'] df['missing_city'] = df['city'].eq('') df['missing_state'] = df['state'].eq('') df['missing_postal_code'] = df['postal_code'].eq('') df['missing_building_code'] = df['building_code'].eq('') df['duplicate_building_code'] = df['building_code'].ne('') & df.duplicated('building_code', keep=False) duplicate_address_counts = df.groupby('address_key')['building_code'].transform('count') df['shared_address'] = duplicate_address_counts.gt(1) # This is intentionally stricter than address_lacks_digit. A road name such as "Hwy 100" # contains a digit but does not identify a physical building/site location. df['manual_location_review_needed'] = ( df['is_placeholder_address'] | df['is_po_box_address'] | df['legacy_route_or_box_address'] | df['address_does_not_start_with_site_number'] | df['missing_city'] | df['missing_state'] | df['missing_postal_code'] ) def manual_location_review_reason(row) -> str: reasons = [] if row['is_placeholder_address']: reasons.append('placeholder_or_unknown_address') if row['is_po_box_address']: reasons.append('po_box_address') if row['legacy_route_or_box_address']: reasons.append('legacy_route_or_box_address') if row['address_does_not_start_with_site_number']: reasons.append('address_does_not_start_with_site_number') if row['missing_city']: reasons.append('missing_city') if row['missing_state']: reasons.append('missing_state') if row['missing_postal_code']: reasons.append('missing_postal_code') return '; '.join(reasons) df['manual_location_review_reason'] = df.apply(manual_location_review_reason, axis=1) zip_conflict_keys = ( df.loc[df['postal_code'].ne('')] .groupby('address_city_key')['postal_code'] .nunique() ) zip_conflict_key_set = set(zip_conflict_keys.loc[zip_conflict_keys.gt(1)].index) df['zip_conflict_for_address_city'] = df['address_city_key'].isin(zip_conflict_key_set) address_number = address_upper.str.extract(r'^\s*(\d+)')[0].fillna('') df['address_leading_number_digits'] = address_number.str.len() TYPO_PATTERNS = [ (r'\bMURFRESSBORO\b', 'verify_street_spelling_murfressboro'), (r'\bSULPHER\b', 'verify_street_spelling_sulpher'), (r'\bCUMBERLAND\s+FUR\.?\b', 'verify_city_abbreviation_cumberland_fur'), ] def potential_typo_reason(row) -> str: text = f"{row['address']} {row['city']}".upper() reasons = [] if row['address_leading_number_digits'] >= 6: reasons.append('verify_long_leading_address_number') for pattern, reason in TYPO_PATTERNS: if re.search(pattern, text): reasons.append(reason) return '; '.join(reasons) df['potential_typo_reason'] = df.apply(potential_typo_reason, axis=1) df['potential_typo_review_needed'] = df['potential_typo_reason'].ne('') qa_summary = pd.DataFrame([ {'check': 'Total records', 'count': len(df)}, {'check': 'Unique building codes', 'count': df['building_code'].nunique(dropna=True)}, {'check': 'Records with any manual override', 'count': int(df['has_any_manual_override'].sum())}, {'check': 'Records with address/city/state/ZIP/county override', 'count': int(df['has_address_override'].sum())}, {'check': 'Records with valid manual coordinates', 'count': int(df['manual_coordinate_valid'].sum())}, {'check': 'Records accepting geocode candidate', 'count': int(df['accepted_geocode_candidate'].sum())}, {'check': 'Records excluded from publish by override', 'count': int(df['exclude_from_publish_override'].sum())}, {'check': 'Blank or placeholder addresses', 'count': int(df['is_placeholder_address'].sum())}, {'check': 'PO Box addresses', 'count': int(df['is_po_box_address'].sum())}, {'check': 'Legacy route or box addresses', 'count': int(df['legacy_route_or_box_address'].sum())}, {'check': 'Addresses lacking any digit', 'count': int(df['address_lacks_digit'].sum())}, {'check': 'Addresses not starting with site number', 'count': int(df['address_does_not_start_with_site_number'].sum())}, {'check': 'Manual location review needed', 'count': int(df['manual_location_review_needed'].sum())}, {'check': 'Missing city', 'count': int(df['missing_city'].sum())}, {'check': 'Missing state', 'count': int(df['missing_state'].sum())}, {'check': 'Missing postal code', 'count': int(df['missing_postal_code'].sum())}, {'check': 'Address/city combinations with ZIP conflicts', 'count': int(len(zip_conflict_key_set))}, {'check': 'Potential typo review records', 'count': int(df['potential_typo_review_needed'].sum())}, {'check': 'Duplicate building codes', 'count': int(df['duplicate_building_code'].sum())}, {'check': 'Records sharing an address with another building', 'count': int(df['shared_address'].sum())}, ]) display(qa_summary) qa_summary.to_csv(QA_REPORT_CSV, index=False) logger.info('Saved QA report: %s', QA_REPORT_CSV) review_cols = [ 'building_code', 'building_name', 'source_address', 'source_city', 'source_state', 'source_postal_code', 'address', 'city', 'state', 'postal_code', 'county', 'has_address_override', 'manual_coordinate_valid', 'accepted_geocode_candidate', 'exclude_from_publish_override', 'manual_location_review_needed', 'manual_location_review_reason', 'potential_typo_reason', ] address_review = df.loc[ df['manual_location_review_needed'] | df['potential_typo_review_needed'] | df['zip_conflict_for_address_city'] | df['manual_coordinate_invalid'], review_cols + [ 'is_placeholder_address', 'is_po_box_address', 'legacy_route_or_box_address', 'address_lacks_digit', 'address_starts_with_site_number', 'address_does_not_start_with_site_number', 'missing_city', 'missing_state', 'missing_postal_code', 'zip_conflict_for_address_city', 'manual_coordinate_invalid', ], ].copy().sort_values(['manual_location_review_needed', 'building_code'], ascending=[False, True]) address_review.to_csv(ADDRESS_REVIEW_CSV, index=False) logger.info('Saved address review CSV: %s', ADDRESS_REVIEW_CSV) display(address_review.head(75)) ```
check count
0 Total records 463
1 Unique building codes 463
2 Records with any manual override 9
3 Records with address/city/state/ZIP/county ove... 9
4 Records with valid manual coordinates 9
5 Records accepting geocode candidate 0
6 Records excluded from publish by override 0
7 Blank or placeholder addresses 4
8 PO Box addresses 1
9 Legacy route or box addresses 24
10 Addresses lacking any digit 37
11 Addresses not starting with site number 65
12 Manual location review needed 65
13 Missing city 0
14 Missing state 0
15 Missing postal code 0
16 Address/city combinations with ZIP conflicts 3
17 Potential typo review records 6
18 Duplicate building codes 0
19 Records sharing an address with another building 400
2026-07-08 19:25:08 | INFO | tdf_buildings_pipeline | Saved QA report: /arcgis/home/tdf_facilities/tdf_buildings_outputs/tdf_buildings_quality_report.csv 2026-07-08 19:25:08 | INFO | tdf_buildings_pipeline | Saved address review CSV: /arcgis/home/tdf_facilities/tdf_buildings_outputs/tdf_buildings_address_review.csv
building_code building_name source_address source_city source_state source_postal_code address city state postal_code ... is_po_box_address legacy_route_or_box_address address_lacks_digit address_starts_with_site_number address_does_not_start_with_site_number missing_city missing_state missing_postal_code zip_conflict_for_address_city manual_coordinate_invalid
12 04000007 Hendon Fire Tower Fire Tower Road GRAYSVILLE TN 37338 Fire Tower Road GRAYSVILLE TN 37338 ... False False True False True False False False False False
13 04010001 Bledsoe SF Smoke House Route 3 Box 180 PIKEVILLE TN 37367 Route 3 Box 180 PIKEVILLE TN 37367 ... False True False False True False False False False False
14 04010002 Bledsoe SF Storm Shelter Route 3 Box 180 PIKEVILLE TN 37367 Route 3 Box 180 PIKEVILLE TN 37367 ... False True False False True False False False False False
15 04010005 Bledsoe SF Sawmill Shed Route 3 Box 180 PIKEVILLE TN 37367 Route 3 Box 180 PIKEVILLE TN 37367 ... False True False False True False False False False False
16 04010006 Bledsoe SF Implement Shed Route 3 Box 180 PIKEVILLE TN 37367 Route 3 Box 180 PIKEVILLE TN 37367 ... False True False False True False False False False False
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
173 36020004 Olive Hill Shop 1485 Firetower Rd SAVANNAH TN 38372 1485 Firetower Rd SAVANNAH TN 38372 ... False False False True False False False False True False
266 55040001 Ramer Fire Tower Storage Shed 9001 Sulpher Springs Rd. RAMER TN 38367 9001 Sulpher Springs Rd. RAMER TN 38367 ... False False False True False False False False False False
267 55040002 Ramer Fire Tower 9001 Sulpher Springs Rd. RAMER TN 38367 9001 Sulpher Springs Rd. RAMER TN 38367 ... False False False True False False False False False False
268 55040003 Ramer Fire Tower Crew Shed 9001 Sulpher Springs Rd. RAMER TN 38367 9001 Sulpher Springs Rd. RAMER TN 38367 ... False False False True False False False False False False
318 63000030 Cunningham Office 4275 Louise Road CUMBERLAND FUR. TN 37051 4275 Louise Road CUMBERLAND FUR. TN 37051 ... False False False True False False False False False False

75 rows × 29 columns

```python duplicate_address_summary = ( df.groupby('address_key', dropna=False) .agg( building_count=('building_code', 'count'), full_address=('full_address', 'first'), building_codes=('building_code', lambda s: ', '.join(sorted(set(x for x in s if x)))) ) .query('building_count > 1') .sort_values(['building_count', 'full_address'], ascending=[False, True]) .reset_index(drop=True) ) duplicate_address_summary.to_csv(SHARED_ADDRESS_SUMMARY_CSV, index=False) logger.info('Duplicate/shared address groups: %s', len(duplicate_address_summary)) logger.info('Saved shared address summary: %s', SHARED_ADDRESS_SUMMARY_CSV) display(duplicate_address_summary.head(50)) zip_conflicts = ( df.loc[df['zip_conflict_for_address_city']] .groupby('address_city_key', dropna=False) .agg( address=('address', 'first'), city=('city', 'first'), state=('state', 'first'), postal_codes=('postal_code', lambda s: ', '.join(sorted(set(x for x in s if x)))), building_count=('building_code', 'count'), building_codes=('building_code', lambda s: ', '.join(sorted(set(x for x in s if x)))) ) .sort_values(['city', 'address']) .reset_index(drop=True) ) zip_conflicts.to_csv(ZIP_CONFLICTS_CSV, index=False) logger.info('Saved ZIP conflicts CSV: %s', ZIP_CONFLICTS_CSV) display(zip_conflicts) potential_typos = ( df.loc[df['potential_typo_review_needed'], [ 'building_code', 'building_name', 'address', 'city', 'state', 'postal_code', 'potential_typo_reason', 'full_address' ]] .sort_values(['potential_typo_reason', 'building_code']) .reset_index(drop=True) ) potential_typos.to_csv(POTENTIAL_TYPOS_CSV, index=False) logger.info('Saved potential typo review CSV: %s', POTENTIAL_TYPOS_CSV) display(potential_typos) square_feet_review = ( df.loc[ df['square_feet'].isna() | df['square_feet'].fillna(0).eq(0), ['building_code', 'building_name', 'address', 'city', 'state', 'postal_code', 'square_feet_raw', 'square_feet'] ] .sort_values('building_code') .reset_index(drop=True) ) square_feet_review.to_csv(SQUARE_FEET_REVIEW_CSV, index=False) logger.info('Saved square feet review CSV: %s', SQUARE_FEET_REVIEW_CSV) display(square_feet_review.head(50)) # Save normalized pre-geocode data with all QA flags included. df.to_csv(NORMALIZED_CSV, index=False) logger.info('Saved normalized CSV: %s', NORMALIZED_CSV) # Optional consolidated QA workbook for easier review outside the notebook. with pd.ExcelWriter(QA_REVIEW_XLSX, engine=CONFIG.excel_engine) as writer: qa_summary.to_excel(writer, sheet_name='QA Summary', index=False) address_review.to_excel(writer, sheet_name='Address Review', index=False) zip_conflicts.to_excel(writer, sheet_name='ZIP Conflicts', index=False) potential_typos.to_excel(writer, sheet_name='Potential Typos', index=False) duplicate_address_summary.to_excel(writer, sheet_name='Shared Addresses', index=False) square_feet_review.to_excel(writer, sheet_name='Square Feet Review', index=False) logger.info('Saved QA review workbook: %s', QA_REVIEW_XLSX) ``` 2026-07-08 19:25:08 | INFO | tdf_buildings_pipeline | Duplicate/shared address groups: 105 2026-07-08 19:25:08 | INFO | tdf_buildings_pipeline | Saved shared address summary: /arcgis/home/tdf_facilities/tdf_buildings_outputs/tdf_buildings_shared_address_summary.csv
building_count full_address building_codes
0 15 Route 3 Box 180, PIKEVILLE, TN, 37367 04010001, 04010002, 04010005, 04010006, 040100...
1 12 420 Ozier Rd., PINSON, TN, 38366 57020001, 57020002, 57020003, 57020004, 570200...
2 10 400 Firetower Ln., LIVINGSTON, TN, 38570 67010001, 67010002, 67010003, 67010004, 670100...
3 9 16965 South Pittsburg Road, SEWANEE, TN, 37380 26010001, 26010002, 26020001, 26020002, 260200...
4 9 4266 Pickett Park Hwy, JAMESTOWN, TN, 38556 25040001, 25040002, 25040003, 25040004, 250400...
5 8 9063 Highway 411, DELANO, TN, 37325 70010001, 70010002, 70010003, 70010004, 700100...
6 7 1865 Fire Tower Rd., MEDON, TN, 38356 57010001, 57010002, 57010003, 57010004, 570100...
7 7 22117 Natchez Trace Rd, WILDERSVILLE, TN, 38388 39040001, 39040002, 39040003, 39040004, 390600...
8 7 3497 Church Street, BURNS, TN, 37029 22020001, 22020002, 22020003, 22020004, 220200...
9 6 1327 Montlake Road, SODDY DAISY, TN, 37379 33030001, 33030002, 33030003, 33030004, 330300...
10 6 1745 Leach Road, CEDAR GROVE, TN, 38321 09010001, 09010002, 09010003, 09010005, 090100...
11 6 195 Macedonia Road, HENRY, TN, 38231 40010001, 40010002, 40010003, 40010004, 400100...
12 6 310 Fire Tower Road, SEWANEE, TN, 37375 26030001, 26030002, 26030003, 26030004, 260300...
13 6 500 FireTower Road, DICKSON, TN, 37055 22010001, 22010002, 22010003, 22010004, 220100...
14 5 1454 Waynesboro Hwy, HOHENWALD, TN, 38462 51010001, 51010002, 51010003, 51010004, 51010005
15 5 147 Forest Tower Road, DOVER, TN, 37058 81000002, 81010001, 81010002, 81010003, 81010004
16 5 1657 Lower Fire Tower Road, SEQUATCHIE, TN, 37374 58020001, 58020002, 58020003, 58020004, 58020005
17 5 1855 Hiwassee Road, MADISONVILLE, TN, 37354 62010001, 62010002, 62010003, 62010004, 62010005
18 5 20111 Clarkrange Hwy., MONTEREY, TN, 38574 71010001, 71010002, 71010003, 71010004, 71010005
19 5 38 Snoddy Road, KELSO, TN, 37348 52010001, 52010002, 52010003, 52010004, 52010005
20 5 4377 Firetower Rd., COLUMBIA, TN, 38401 60010001, 60010002, 60010003, 60010004, 60010005
21 5 4766 Hollow Springs Road, BRADYVILLE, TN, 37026 08010001, 08010002, 08010003, 08020001, 08020002
22 5 6101 Millsfield Highway, DYERSBURG, TN, 38024 23010001, 23010002, 23010003, 23010004, 23010005
23 5 757 Ridgeview Road, BEAN STATION, TN, 37708 29010001, 29010002, 29010003, 29010004, 29010005
24 5 774 Mousetail Rd, PARSONS, TN, 38364 20010001, 20010002, 20010003, 20010004, 20010005
25 5 884 Highway 70 W, LENOIR CITY, TN, 37771 53010001, 53010002, 53010003, 53010004, 53010005
26 5 927 Firetower Road, MARTIN, TN, 38237 92010001, 92010002, 92010003, 92010004, 92010005
27 5 962 Old State Route 48, CENTERVILLE, TN, 37033 41020001, 41020002, 41020003, 41020004, 41020005
28 5 HCR 77 Box 66, ALTAMONT, TN, 37301 31020001, 31020002, 31020003, 31020004, 31020005
29 4 1084 Westel Road, ROCKWOOD, TN, 37854 73010001, 73010002, 73010003, 73010004
30 4 1100 Jones Road, HENDERSONVILLE, TN, 37075 83010001, 83010002, 83010003, 83010004
31 4 121 Pig Ranch Road, GORDONSVILLE, TN, 37030 80010001, 80010002, 80030001, 80030002
32 4 195 Fire Tower Lane, MORRISON, TN, 37357 89010001, 89010002, 89010003, 89010004
33 4 2030 Riley Creek Road, WHITLEYVILLE, TN, 38588 44010001, 44010002, 44030001, 44030002
34 4 220 Eastern Shores Dr., LEXINGTON, TN, 38351 39050001, 39050002, 39050003, 39050004
35 4 2880 Petway Road, ASHLAND CITY, TN, 37015 11050001, 11050002, 11050003, 11050004
36 4 3476 Sharps Chapel Road, SHARPS CHAPEL, TN, 37866 87020001, 87020002, 87020004, 87040001
37 4 395 Firetower Lane, HEISKELL, TN, 37754 01010001, 01010002, 01010003, 01010004
38 4 3998 Game Reserve Road, CHATTANOOGA, TN, 37405 58030001, 58030002, 58030004, 58030005
39 4 423 Fire Tower Drive, DUNLAP, TN, 37327 77020001, 77020002, 77020003, 77020004
40 4 444 Fox Hunter Road, MAYNARDVILLE, TN, 37807 87010001, 87010002, 87010003, 87010004
41 4 460 Firetower Road, NEW MARKET, TN, 37820 45040001, 45040002, 45040003, 45040004
42 4 490 Fire Tower Rd, LINDEN, TN, 37096 68020001, 68020002, 68020004, 68020005
43 4 5098 U.S. 127, SIGNAL MOUNTAIN, TN, 37377 77040001, 77040002, 77040003, 77040004
44 4 7691 Pewitt Road, FRANKLIN, TN, 37064 94010001, 94010002, 94010003, 94010004
45 4 Cabo Rd., ENVILLE, TN, 38332 12010001, 12010002, 12010003, 12010004
46 3 100 Clinch Ridge Rd, THORN HILL, TN, 37881 37020001, 37020002, 37020003
47 3 115 Fire Tower Rd, SELMER, TN, 38375 55010001, 55010002, 55010003
48 3 1336 County Road 875, ETOWAH, TN, 37331 54010001, 54010002, 54010003
49 3 1342 Hurricane Bridge Rd., SMITHVILLE, TN, 37166 21010001, 21010002, 21010003
2026-07-08 19:25:08 | INFO | tdf_buildings_pipeline | Saved ZIP conflicts CSV: /arcgis/home/tdf_facilities/tdf_buildings_outputs/tdf_buildings_zip_conflicts.csv
address city state postal_codes building_count building_codes
0 2643 Gap Creek Road HAMPTON TN 37643, 37658 2 10010003, 10010004
1 400 Firetower Ln. LIVINGSTON TN 38570, 38571 11 67010001, 67010002, 67010003, 67010004, 670100...
2 1485 Firetower Rd SAVANNAH TN 38367, 38372 4 36020001, 36020002, 36020003, 36020004
2026-07-08 19:25:08 | INFO | tdf_buildings_pipeline | Saved potential typo review CSV: /arcgis/home/tdf_facilities/tdf_buildings_outputs/tdf_buildings_potential_typos.csv
building_code building_name address city state postal_code potential_typo_reason full_address
0 63000030 Cunningham Office 4275 Louise Road CUMBERLAND FUR. TN 37051 verify_city_abbreviation_cumberland_fur 4275 Louise Road, CUMBERLAND FUR., TN, 37051
1 63000031 Cunningham Lookout Tower 4275 Louise Road CUMBERLAND FUR. TN 37051 verify_city_abbreviation_cumberland_fur 4275 Louise Road, CUMBERLAND FUR., TN, 37051
2 95000007 Cedars Storage Shed 5112 Murfressboro Rd. LEBANON TN 37090 verify_street_spelling_murfressboro 5112 Murfressboro Rd., LEBANON, TN, 37090
3 55040001 Ramer Fire Tower Storage Shed 9001 Sulpher Springs Rd. RAMER TN 38367 verify_street_spelling_sulpher 9001 Sulpher Springs Rd., RAMER, TN, 38367
4 55040002 Ramer Fire Tower 9001 Sulpher Springs Rd. RAMER TN 38367 verify_street_spelling_sulpher 9001 Sulpher Springs Rd., RAMER, TN, 38367
5 55040003 Ramer Fire Tower Crew Shed 9001 Sulpher Springs Rd. RAMER TN 38367 verify_street_spelling_sulpher 9001 Sulpher Springs Rd., RAMER, TN, 38367
2026-07-08 19:25:08 | INFO | tdf_buildings_pipeline | Saved square feet review CSV: /arcgis/home/tdf_facilities/tdf_buildings_outputs/tdf_buildings_square_feet_review.csv
building_code building_name address city state postal_code square_feet_raw square_feet
0 03010004 Camden Storage Building 625 Fire Tower Rd CAMDEN TN 38320 0 0.0
1 04010016 Bledsoe SF Sawmill Residence Route 3 Box 180 PIKEVILLE TN 37367 0 0.0
2 09010003 Leach Storage Building #3 1745 Leach Road CEDAR GROVE TN 38321 0 0.0
3 10010004 Carter County Outbuilding #1 2643 Gap Creek Road HAMPTON TN 37643 0 0.0
4 11010001 Sycamore Crew Cabin 1106 Bryant Road ASHLAND CITY TN 37015 0 0.0
5 11010002 Sycamore Fire Tower 1106 Bryant Road ASHLAND CITY TN 37015 0 0.0
6 11050001 Petway Cabin 2880 Petway Road ASHLAND CITY TN 37015 0 0.0
7 11050003 Petway House 2880 Petway Road ASHLAND CITY TN 37015 0 0.0
8 12010002 Jacks Creek Fire Tower Quonset Hut Cabo Rd. ENVILLE TN 38332 0 0.0
9 12010004 Jacks Creek Fire Tower Crew Shed Cabo Rd. ENVILLE TN 38332 0 0.0
10 15010002 English Mtn Radio Tower Building 1400 Carson Springs Road NEWPORT TN 37821 0 0.0
11 19000098 Forestry Radio Warehouse 1134 Menzler Road NASHVILLE TN 37210 0 0.0
12 19010027 Nashville Main Forestry Office, Bruer Building 406 Hogan Road NASHVILLE TN 37220 NaN
13 19010028 Porter Building 436 Hogan Road NASHVILLE TN 37220 NaN
14 20010003 Parsons Firetower Shed #1 774 Mousetail Rd PARSONS TN 38364 0 0.0
15 23010002 Millsfield Office 6101 Millsfield Highway DYERSBURG TN 38024 0 0.0
16 23010004 Millsfield Radio Building 6101 Millsfield Highway DYERSBURG TN 38024 0 0.0
17 23010005 Millsfield Radio Tower 6101 Millsfield Highway DYERSBURG TN 38024 0 0.0
18 24010002 Williston Fire Tower Residence 3570 Ebenezer Loop SOMERVILLE TN 38068 0 0.0
19 24010003 Williston Fire Tower Radio Repeater Building 3570 Ebenezer Loop SOMERVILLE TN 38068 0 0.0
20 25080001 Wolf River Forestry Building Unidentified JAMESTOWN TN 38556 0 0.0
21 25080002 Wolf River Tower Unidentified JAMESTOWN TN 38556 0 0.0
22 28000011 Brooklyn Plaza Area Office 57 Firetower Road PULASKI TN 38478 0 0.0
23 33030001 Montlake Shop/Office 1327 Montlake Road SODDY DAISY TN 37379 0 0.0
24 33080001 District 3 Dispatch Office 5530 Hwy 153 HIXSON TN 37343 0 0.0
25 33080002 District 3 Office 5530 Hwy 153 HIXSON TN 37343 0 0.0
26 35000003 Whiteville Radio Tower 134 West Main Street WHITEVILLE TN 38075 0 0.0
27 35040001 Hickory Valley Crew Shed 185 Buster Woods Road HICKORY VALLEY TN 38042 0 0.0
28 35050003 Hornsby Office 435 Fire Tower Lane BOLIVAR TN 38008 0 0.0
29 36030001 Savannah Radio Tower & Building 1680 Irvin Rd SAVANNAH TN 38372 0 0.0
30 36030002 Savannah Office 1680 Irvin Rd SAVANNAH TN 38372 0 0.0
31 36040001 Pickwick Firetower 339 Turkey Knob Ln. COUNCE TN 38326 0 0.0
32 36040002 Pickwick Office 339 Turkey Knob Ln. COUNCE TN 38326 0 0.0
33 36040003 Pickwick Shop 339 Turkey Knob Ln. COUNCE TN 38326 0 0.0
34 37020001 Mooresburg Building 100 Clinch Ridge Rd THORN HILL TN 37881 0 0.0
35 39040001 Natchez Trace State Forest Office 22117 Natchez Trace Rd WILDERSVILLE TN 38388 0 0.0
36 39040002 Natchez Trace State Forest Equipment Shed 22117 Natchez Trace Rd WILDERSVILLE TN 38388 0 0.0
37 39040003 Natchez Trace State Forest Tractor Shed 22117 Natchez Trace Rd WILDERSVILLE TN 38388 0 0.0
38 39060002 Natchez Trace Storage Shed #2 22117 Natchez Trace Rd WILDERSVILLE TN 38388 0 0.0
39 40010002 Paris Storage Building 195 Macedonia Road HENRY TN 38231 0 0.0
40 40010004 Paris Office 195 Macedonia Road HENRY TN 38231 0 0.0
41 44030002 Haydenburg Tower House 2030 Riley Creek Road WHITLEYVILLE TN 38588 0 0.0
42 45040001 Jefferson County Storage Building 460 Firetower Road NEW MARKET TN 37820 0 0.0
43 45040002 Jefferson County 911 Radio System 460 Firetower Road NEW MARKET TN 37820 0 0.0
44 45040003 Jefferson County Ag Crime Unit Office 460 Firetower Road NEW MARKET TN 37820 0 0.0
45 45040004 Jefferson County Fire Tower 460 Firetower Road NEW MARKET TN 37820 0 0.0
46 47000013 DOA Regulatory Services, Solar House 3211 Alcoa Hwy, Solar House #3 KNOXVILLE TN 37920 0 0.0
47 49010001 Ripley Fire Tower Shop 247 Joe Critchfield Rd. KNOXVILLE TN 38063 0 0.0
48 50070001 Westpoint Office Westpoint Road LAWRENCEBURG TN 38486 0 0.0
49 50070002 Westpoint Fire Tower Westpoint Road LAWRENCEBURG TN 38486 0 0.0
2026-07-08 19:25:08 | INFO | tdf_buildings_pipeline | Saved normalized CSV: /arcgis/home/tdf_facilities/tdf_buildings_outputs/tdf_buildings_normalized.csv 2026-07-08 19:25:08 | INFO | tdf_buildings_pipeline | Saved QA review workbook: /arcgis/home/tdf_facilities/tdf_buildings_outputs/tdf_buildings_qa_review.xlsx ## 6. Manual override control file Use `tdf_buildings_manual_overrides.csv` as the editable correction layer. The notebook creates/refreses this file with one row per `building_code`, preserves reviewer-entered override values, and applies those overrides before QA/geocoding or before publishing. Editable columns: - `override_address`, `override_city`, `override_state`, `override_postal_code`, `override_county`: replace source address components for geocoding and publishing. - `latitude`, `longitude`: manually verified WGS 84 coordinates. A valid coordinate override bypasses geocoder-quality blockers. - `accept_geocode_candidate`: set to `TRUE` only after visually verifying the geocoder result; this allows a flagged geocoder candidate to publish. - `exclude_from_publish`: set to `TRUE` to intentionally keep a record out of the hosted layer. - `override_notes`, `override_source`: reviewer notes and source of the decision. Reference columns such as `building_name` and `source_address` are refreshed from the source workbook. Do not use them for edits. ```python manual_override_display_cols = [ 'building_code', 'building_name', 'source_address', 'source_city', 'source_state', 'source_postal_code', 'override_address', 'override_city', 'override_state', 'override_postal_code', 'latitude', 'longitude', 'accept_geocode_candidate', 'exclude_from_publish', 'override_notes', 'override_source', ] print(f'Manual override file: {MANUAL_OVERRIDES_CSV}') print('Edit this file for manual corrections. The notebook will preserve editable override columns on reruns.') manual_override_work_queue = df.loc[ df['manual_location_review_needed'] | df['potential_typo_review_needed'] | df['zip_conflict_for_address_city'] | df['manual_coordinate_invalid'] | df['has_any_manual_override'], manual_override_display_cols + [ 'full_address', 'manual_location_review_reason', 'potential_typo_reason', 'zip_conflict_for_address_city', 'manual_coordinate_valid', 'manual_coordinate_invalid', ], ].copy() logger.info('Manual override file rows: %s', len(manual_overrides)) logger.info('Manual override current QA/edit queue rows: %s', len(manual_override_work_queue)) display(manual_override_work_queue.head(100)) ``` Manual override file: /arcgis/home/tdf_facilities/tdf_buildings_outputs/tdf_buildings_manual_overrides.csv Edit this file for manual corrections. The notebook will preserve editable override columns on reruns. 2026-07-08 19:25:08 | INFO | tdf_buildings_pipeline | Manual override file rows: 463 2026-07-08 19:25:08 | INFO | tdf_buildings_pipeline | Manual override current QA/edit queue rows: 97
building_code building_name source_address source_city source_state source_postal_code override_address override_city override_state override_postal_code ... accept_geocode_candidate exclude_from_publish override_notes override_source full_address manual_location_review_reason potential_typo_reason zip_conflict_for_address_city manual_coordinate_valid manual_coordinate_invalid
0 10000006 Holston Mtn Radio Building National Forest 56 ELIZABETHTON TN 37643 ... National Forest 56, ELIZABETHTON, TN, 37643 address_does_not_start_with_site_number False False False
3 10010003 Carter County Shop 2643 Gap Creek Road HAMPTON TN 37658 ... 2643 Gap Creek Road, HAMPTON, TN, 37658 True False False
12 04000007 Hendon Fire Tower Fire Tower Road GRAYSVILLE TN 37338 ... Fire Tower Road, GRAYSVILLE, TN, 37338 address_does_not_start_with_site_number False False False
13 04010001 Bledsoe SF Smoke House Route 3 Box 180 PIKEVILLE TN 37367 ... Route 3 Box 180, PIKEVILLE, TN, 37367 legacy_route_or_box_address; address_does_not_... False False False
14 04010002 Bledsoe SF Storm Shelter Route 3 Box 180 PIKEVILLE TN 37367 ... Route 3 Box 180, PIKEVILLE, TN, 37367 legacy_route_or_box_address; address_does_not_... False False False
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
407 81000003 Lookout Tower (Model) LBL Ginger Bay Road DOVER TN 37058 ... Ginger Bay Road, DOVER, TN, 37058 address_does_not_start_with_site_number False False False
412 81020001 Model Storage Building (LBL) Fire Tower Road DOVER TN 37058 ... Fire Tower Road, DOVER, TN, 37058 address_does_not_start_with_site_number False False False
413 81020002 Model Shop And Office Fire Tower Road DOVER TN 37058 ... Fire Tower Road, DOVER, TN, 37058 address_does_not_start_with_site_number False False False
461 95000006 Lebanon Tower Dead End of Firetower Rd. LEBANON TN 37090 ... Dead End of Firetower Rd., LEBANON, TN, 37090 address_does_not_start_with_site_number False False False
462 95000007 Cedars Storage Shed 5112 Murfressboro Rd. LEBANON TN 37090 ... 5112 Murfressboro Rd., LEBANON, TN, 37090 verify_street_spelling_murfressboro False False False

97 rows × 22 columns

## 7. Geocode unique addresses This section geocodes unique effective address records only, then joins the results back to individual buildings. Existing results are read from `tdf_buildings_geocode_cache.csv` and only uncached addresses are submitted to the locator. Address overrides in `tdf_buildings_manual_overrides.csv` are applied before this step, so corrected address values create new address keys and can be geocoded without changing the raw workbook. Records with valid manual coordinates or `exclude_from_publish = TRUE` are not submitted to the locator. This section displays a cache-status table. If `run_geocoding = False` and no current-version cache rows are found, downstream geocoded outputs will contain no coordinates. Set `run_geocoding = True` to rebuild the cache. ```python def coerce_positive_int(value): try: value = int(value) except (TypeError, ValueError): return None return value if value > 0 else None def get_locator_batch_limits(geocoder): locator_props = getattr(geocoder.properties, 'locatorProperties', None) if not locator_props: return None, None suggested = coerce_positive_int(getattr(locator_props, 'SuggestedBatchSize', None)) maximum = coerce_positive_int(getattr(locator_props, 'MaxBatchSize', None)) return suggested, maximum def effective_geocode_batch_size(): requested = coerce_positive_int(CONFIG.geocode_batch_size) if requested: size = requested elif LOCATOR_SUGGESTED_BATCH_SIZE: size = LOCATOR_SUGGESTED_BATCH_SIZE else: size = 100 if LOCATOR_MAX_BATCH_SIZE and size > LOCATOR_MAX_BATCH_SIZE: size = LOCATOR_MAX_BATCH_SIZE return size geocoders = get_geocoders(gis) if not geocoders: raise RuntimeError('No geocoders are configured for this GIS.') geocoder = geocoders[0] LOCATOR_SUGGESTED_BATCH_SIZE, LOCATOR_MAX_BATCH_SIZE = get_locator_batch_limits(geocoder) logger.info('Using geocoder: %s', geocoder.properties.get('name', geocoder.url)) logger.info('SuggestedBatchSize: %s', LOCATOR_SUGGESTED_BATCH_SIZE or 'not reported') logger.info('MaxBatchSize: %s', LOCATOR_MAX_BATCH_SIZE or 'not reported') logger.info('Effective batch size: %s', effective_geocode_batch_size()) logger.info('Geocode mode: %s', CONFIG.geocode_mode) ``` 2026-07-08 19:25:10 | INFO | tdf_buildings_pipeline | Using geocoder: https://geocode.arcgis.com/arcgis/rest/services/World/GeocodeServer 2026-07-08 19:25:10 | INFO | tdf_buildings_pipeline | SuggestedBatchSize: 150 2026-07-08 19:25:10 | INFO | tdf_buildings_pipeline | MaxBatchSize: 1000 2026-07-08 19:25:10 | INFO | tdf_buildings_pipeline | Effective batch size: 150 2026-07-08 19:25:10 | INFO | tdf_buildings_pipeline | Geocode mode: single ```python GEOCODE_CACHE_COLUMNS = [ 'cache_version', 'address_key', 'input_address', 'input_city', 'input_state', 'input_postal_code', 'full_address', 'longitude', 'latitude', 'score', 'match_address', 'addr_type', 'geocode_status', 'geocode_source', 'geocode_run_utc', 'raw_result' ] # If force_regeocode is True and run_geocoding is True, the previous cache is ignored # and overwritten with new validated rows. When run_geocoding is False, the current # versioned cache can still be loaded for downstream review or publishing. ignore_existing_cache = bool(CONFIG.force_regeocode and CONFIG.run_geocoding) if GEOCODE_CACHE_CSV.exists() and not ignore_existing_cache: geocode_cache = pd.read_csv( GEOCODE_CACHE_CSV, dtype={ 'cache_version': 'string', 'address_key': 'string', 'input_address': 'string', 'input_city': 'string', 'input_state': 'string', 'input_postal_code': 'string', 'full_address': 'string', 'match_address': 'string', 'addr_type': 'string', 'geocode_status': 'string', 'geocode_source': 'string', 'geocode_run_utc': 'string', 'raw_result': 'string', }, ) for col in GEOCODE_CACHE_COLUMNS: if col not in geocode_cache.columns: geocode_cache[col] = '' geocode_cache = geocode_cache[GEOCODE_CACHE_COLUMNS].copy() geocode_cache = geocode_cache.loc[ geocode_cache['cache_version'].fillna('').eq(CONFIG.geocode_cache_version) ].copy() logger.info('Loaded versioned geocode cache rows: %s', len(geocode_cache)) elif GEOCODE_CACHE_CSV.exists() and ignore_existing_cache: logger.warning( 'Ignoring existing geocode cache for this run because CONFIG.force_regeocode is True. ' 'The cache will be rebuilt if CONFIG.run_geocoding is True.' ) geocode_cache = pd.DataFrame(columns=GEOCODE_CACHE_COLUMNS) else: geocode_cache = pd.DataFrame(columns=GEOCODE_CACHE_COLUMNS) # Do not spend geocoding credits on known placeholder/PO Box addresses, valid manual coordinates, # or records that are intentionally excluded from publishing. geocode_eligible_mask = ( ~df['is_placeholder_address'] & ~df['is_po_box_address'] & ~df['manual_coordinate_valid'] & ~df['exclude_from_publish_override'] ) unique_addresses = ( df.loc[ geocode_eligible_mask, ['address_key', 'address', 'city', 'state', 'postal_code', 'full_address'] ] .drop_duplicates('address_key') .sort_values('full_address') .reset_index(drop=True) ) cached_keys = set(geocode_cache['address_key'].dropna()) if not geocode_cache.empty else set() to_geocode = unique_addresses.loc[~unique_addresses['address_key'].isin(cached_keys)].copy() logger.info('Geocode cache version: %s', CONFIG.geocode_cache_version) logger.info('Force regeocode: %s', CONFIG.force_regeocode) logger.info('Unique geocode-eligible addresses: %s', len(unique_addresses)) logger.info('Already cached with current version: %s', len(unique_addresses) - len(to_geocode)) logger.info('Remaining to geocode: %s', len(to_geocode)) geocode_cache_status = pd.DataFrame([ {'metric': 'run_geocoding', 'value': CONFIG.run_geocoding}, {'metric': 'run_publish', 'value': CONFIG.run_publish}, {'metric': 'geocode_cache_file_exists', 'value': GEOCODE_CACHE_CSV.exists()}, {'metric': 'current_version_cache_rows', 'value': len(geocode_cache)}, {'metric': 'records_skipped_due_valid_manual_coordinates', 'value': int(df['manual_coordinate_valid'].sum())}, {'metric': 'records_skipped_due_manual_exclusion', 'value': int(df['exclude_from_publish_override'].sum())}, {'metric': 'unique_geocode_eligible_addresses', 'value': len(unique_addresses)}, {'metric': 'addresses_still_needing_geocoding', 'value': len(to_geocode)}, {'metric': 'force_regeocode', 'value': CONFIG.force_regeocode}, {'metric': 'geocode_cache_version', 'value': CONFIG.geocode_cache_version}, ]) display(geocode_cache_status) if not CONFIG.run_geocoding and len(geocode_cache) == 0 and len(unique_addresses) > 0: logger.warning( 'No current-version geocode cache rows were loaded. This is expected for a QA-only run, ' 'but geocoded outputs will contain no coordinates until CONFIG.run_geocoding is True ' 'or a valid tdf_buildings_geocode_cache.csv exists in the output folder.' ) if CONFIG.run_publish and not CONFIG.run_geocoding and len(geocode_cache) == 0 and len(unique_addresses) > 0: raise RuntimeError( 'Publishing stopped before geocoding. CONFIG.run_publish is True, CONFIG.run_geocoding is False, ' 'and no current-version geocode cache rows were loaded. Set run_geocoding=True or provide a valid cache.' ) display(to_geocode.head(20)) ``` 2026-07-08 19:25:10 | INFO | tdf_buildings_pipeline | Loaded versioned geocode cache rows: 166 2026-07-08 19:25:10 | INFO | tdf_buildings_pipeline | Geocode cache version: v2_result_validation 2026-07-08 19:25:10 | INFO | tdf_buildings_pipeline | Force regeocode: False 2026-07-08 19:25:10 | INFO | tdf_buildings_pipeline | Unique geocode-eligible addresses: 164 2026-07-08 19:25:10 | INFO | tdf_buildings_pipeline | Already cached with current version: 164 2026-07-08 19:25:10 | INFO | tdf_buildings_pipeline | Remaining to geocode: 0
metric value
0 run_geocoding False
1 run_publish True
2 geocode_cache_file_exists True
3 current_version_cache_rows 166
4 records_skipped_due_valid_manual_coordinates 9
5 records_skipped_due_manual_exclusion 0
6 unique_geocode_eligible_addresses 164
7 addresses_still_needing_geocoding 0
8 force_regeocode False
9 geocode_cache_version v2_result_validation
address_key address city state postal_code full_address
```python def first_nonempty(*values): for value in values: if value is not None and value != '': return value return None def make_geocode_address(row, object_id=None): payload = { 'Address': row['address'], 'City': row['city'], 'Region': row['state'], 'Postal': row['postal_code'], } if object_id is not None: payload['OBJECTID'] = int(object_id) return payload def cache_context_from_row(row): return { 'cache_version': CONFIG.geocode_cache_version, 'address_key': row['address_key'], 'input_address': row['address'], 'input_city': row['city'], 'input_state': row['state'], 'input_postal_code': row['postal_code'], 'full_address': row['full_address'], } def parse_geocode_result(row, result, source, run_time): base = cache_context_from_row(row) if not result: return { **base, 'longitude': None, 'latitude': None, 'score': 0, 'match_address': '', 'addr_type': '', 'geocode_status': 'no_match', 'geocode_source': source, 'geocode_run_utc': run_time, 'raw_result': '', } attrs = result.get('attributes', {}) or {} location = result.get('location', {}) or {} score = first_nonempty(result.get('score'), attrs.get('Score')) longitude = first_nonempty(location.get('x'), attrs.get('X'), attrs.get('DisplayX')) latitude = first_nonempty(location.get('y'), attrs.get('Y'), attrs.get('DisplayY')) match_address = first_nonempty(result.get('address'), attrs.get('Match_addr'), attrs.get('LongLabel'), '') addr_type = first_nonempty(attrs.get('Addr_type'), attrs.get('Type'), '') status = attrs.get('Status') if longitude is None or latitude is None or pd.isna(score) or float(score or 0) <= 0: geocode_status = 'no_match' elif status: geocode_status = f'matched_{status}' else: geocode_status = 'matched' return { **base, 'longitude': longitude, 'latitude': latitude, 'score': score, 'match_address': match_address, 'addr_type': addr_type, 'geocode_status': geocode_status, 'geocode_source': source, 'geocode_run_utc': run_time, 'raw_result': json.dumps(result, default=str), } def geocode_one_address(row): run_time = datetime.now(timezone.utc).isoformat() try: candidates = geocode( address=make_geocode_address(row), max_locations=1, out_fields='*', for_storage=True, source_country=CONFIG.source_country, category=CONFIG.geocode_category, out_sr={'wkid': 4326}, location_type=CONFIG.geocode_location_type, geocoder=geocoder, ) except Exception as exc: return { **cache_context_from_row(row), 'longitude': None, 'latitude': None, 'score': None, 'match_address': '', 'addr_type': '', 'geocode_status': f'error: {exc}', 'geocode_source': 'arcgis.geocoding.geocode', 'geocode_run_utc': run_time, 'raw_result': '', } best = candidates[0] if candidates else None return parse_geocode_result(row, best, 'arcgis.geocoding.geocode', run_time) def geocode_single_sequence(rows_df): results = [] total = len(rows_df) for i, (_, row) in enumerate(rows_df.iterrows(), start=1): if i == 1 or i % 25 == 0 or i == total: logger.info('Geocoding %s of %s: %s', i, total, row['full_address']) results.append(geocode_one_address(row)) time.sleep(CONFIG.geocode_delay_seconds) return results def extract_result_id(result): attrs = result.get('attributes', {}) or {} for key in ('ResultID', 'OBJECTID', 'ObjectID', 'InputID'): value = result.get(key, attrs.get(key)) if value is not None and str(value).strip() != '': try: return int(value) except (TypeError, ValueError): return None return None def normalize_batch_results(batch_results): if hasattr(batch_results, 'features'): return [ { 'address': feature.attributes.get('Match_addr', ''), 'location': feature.geometry, 'score': feature.attributes.get('Score'), 'attributes': feature.attributes, } for feature in batch_results.features ] return batch_results def geocode_batch(rows_df): """Batch geocode with ResultID/OBJECTID mapping. This intentionally avoids assigning results back to input rows by list position. If returned results cannot be safely mapped by ResultID or OBJECTID, the function falls back to single-address requests for the batch. """ run_time = datetime.now(timezone.utc).isoformat() rows_by_result_id = {} address_payload = [] for result_id, (_, row) in enumerate(rows_df.iterrows()): rows_by_result_id[result_id] = row address_payload.append(make_geocode_address(row, object_id=result_id)) try: batch_results = batch_geocode( addresses=address_payload, source_country=CONFIG.source_country, category=CONFIG.geocode_category, out_sr={'wkid': 4326}, geocoder=geocoder, as_featureset=False, match_out_of_range=True, location_type=CONFIG.geocode_location_type, out_fields='*', ) except Exception as exc: logger.warning( 'Batch geocode failed for %s addresses. Falling back to single-address requests. Error: %s', len(rows_df), exc, ) return geocode_single_sequence(rows_df) batch_results = normalize_batch_results(batch_results) if not isinstance(batch_results, list): logger.warning('Batch geocode returned a non-list result. Falling back to single-address requests for this batch.') return geocode_single_sequence(rows_df) parsed_results = [] seen_result_ids = set() missing_or_unmapped = 0 for result in batch_results: result_id = extract_result_id(result) if result_id is None or result_id not in rows_by_result_id or result_id in seen_result_ids: missing_or_unmapped += 1 continue seen_result_ids.add(result_id) parsed_results.append( parse_geocode_result( rows_by_result_id[result_id], result, 'arcgis.geocoding.batch_geocode_resultid', run_time, ) ) if missing_or_unmapped or len(seen_result_ids) != len(rows_by_result_id): logger.warning( 'Batch geocode results could not be safely mapped by ResultID/OBJECTID ' '(%s unmapped; %s mapped of %s input rows). Falling back to single-address requests.', missing_or_unmapped, len(seen_result_ids), len(rows_by_result_id), ) return geocode_single_sequence(rows_df) return parsed_results if CONFIG.run_geocoding: new_results = [] total = len(to_geocode) if total == 0: logger.info('No uncached addresses need geocoding.') elif CONFIG.geocode_mode == 'single': new_results.extend(geocode_single_sequence(to_geocode)) elif CONFIG.geocode_mode == 'batch': batch_size = effective_geocode_batch_size() for start in range(0, total, batch_size): stop = min(start + batch_size, total) batch_df = to_geocode.iloc[start:stop].copy() logger.info('Batch geocoding records %s-%s of %s', start + 1, stop, total) new_results.extend(geocode_batch(batch_df)) else: raise ValueError("CONFIG.geocode_mode must be either 'batch' or 'single'.") if new_results: new_cache_rows = pd.DataFrame(new_results, columns=GEOCODE_CACHE_COLUMNS) geocode_cache = ( pd.concat([geocode_cache, new_cache_rows], ignore_index=True) .drop_duplicates('address_key', keep='last') ) geocode_cache = geocode_cache[GEOCODE_CACHE_COLUMNS] geocode_cache.to_csv(GEOCODE_CACHE_CSV, index=False) logger.info('Updated geocode cache: %s', GEOCODE_CACHE_CSV) else: logger.info('CONFIG.run_geocoding is False. Toggle it to True in the configuration cell to geocode uncached addresses.') # Reload from disk if the cache exists, so downstream cells use the latest current-version cache. if GEOCODE_CACHE_CSV.exists(): geocode_cache = pd.read_csv(GEOCODE_CACHE_CSV, dtype=str) for col in GEOCODE_CACHE_COLUMNS: if col not in geocode_cache.columns: geocode_cache[col] = '' geocode_cache = geocode_cache[GEOCODE_CACHE_COLUMNS].copy() geocode_cache = geocode_cache.loc[ geocode_cache['cache_version'].fillna('').eq(CONFIG.geocode_cache_version) ].copy() for numeric_col in ['longitude', 'latitude', 'score']: if numeric_col in geocode_cache.columns: geocode_cache[numeric_col] = pd.to_numeric(geocode_cache[numeric_col], errors='coerce') display(geocode_cache.head()) ``` 2026-07-08 19:25:10 | INFO | tdf_buildings_pipeline | CONFIG.run_geocoding is False. Toggle it to True in the configuration cell to geocode uncached addresses.
cache_version address_key input_address input_city input_state input_postal_code full_address longitude latitude score match_address addr_type geocode_status geocode_source geocode_run_utc raw_result
0 v2_result_validation 100 CLINCH RIDGE RD|THORN HILL|TN|37881 100 Clinch Ridge Rd THORN HILL TN 37881 100 Clinch Ridge Rd, THORN HILL, TN, 37881 -83.267914 36.464212 86.69 Ridge Rd, Sneedville, Tennessee, 37869 StreetName matched_M arcgis.geocoding.geocode 2026-07-08T17:10:43.204738+00:00 {"address": "Ridge Rd, Sneedville, Tennessee, ...
1 v2_result_validation 101 FIRETOWER DR|COLLINWOOD|TN|38450 101 Firetower Dr. COLLINWOOD TN 38450 101 Firetower Dr., COLLINWOOD, TN, 38450 -87.742596 35.148928 100.00 101 Fire Tower Dr, Collinwood, Tennessee, 38450 PointAddress matched_M arcgis.geocoding.geocode 2026-07-08T17:10:43.797947+00:00 {"address": "101 Fire Tower Dr, Collinwood, Te...
2 v2_result_validation 10150 CORINTH RD|WILDERSVILLE|TN|38388 10150 Corinth Rd WILDERSVILLE TN 38388 10150 Corinth Rd, WILDERSVILLE, TN, 38388 -88.276893 35.767041 100.00 10150 Corinth Rd, Wildersville, Tennessee, 38388 PointAddress matched_M arcgis.geocoding.geocode 2026-07-08T17:10:44.411247+00:00 {"address": "10150 Corinth Rd, Wildersville, T...
3 v2_result_validation 1050 FIRETOWER LN|LUTTS|TN|38471 1050 Firetower Ln LUTTS TN 38471 1050 Firetower Ln, LUTTS, TN, 38471 -87.919313 35.089074 98.04 1050 Firetower Rd, Lutts, Tennessee, 38471 PointAddress matched_M arcgis.geocoding.geocode 2026-07-08T17:10:45.244125+00:00 {"address": "1050 Firetower Rd, Lutts, Tenness...
4 v2_result_validation 1084 WESTEL ROAD|ROCKWOOD|TN|37854 1084 Westel Road ROCKWOOD TN 37854 1084 Westel Road, ROCKWOOD, TN, 37854 -84.743105 35.872672 100.00 1084 Westel Rd, Rockwood, Tennessee, 37854 PointAddress matched_M arcgis.geocoding.geocode 2026-07-08T17:10:45.636093+00:00 {"address": "1084 Westel Rd, Rockwood, Tenness...
## 8. Join geocode results and apply manual overrides This cell creates geocode QA fields and applies reviewer decisions from `tdf_buildings_manual_overrides.csv`. A valid manual coordinate override marks the record as `ManualOverride` and can publish even when the original/effective address would otherwise need review. `accept_geocode_candidate = TRUE` lets a visually verified geocoder candidate publish even if the automated parser flags city/ZIP/score/type issues. `exclude_from_publish = TRUE` keeps the record out of the hosted layer. The validation-review CSV includes all records needing geocode review and all records manually excluded from publishing. ```python # Remove any previous geocode columns before joining. geocode_cols_to_drop = [ 'cache_version', 'input_address', 'input_city', 'input_state', 'input_postal_code', 'longitude', 'latitude', 'score', 'match_address', 'addr_type', 'geocode_status', 'geocode_source', 'geocode_run_utc', 'raw_result', 'matched_street_part', 'matched_city_part', 'matched_zip_part', 'matched_address_number', 'input_address_number', 'input_zip_part', 'matched_city_agrees', 'matched_zip_agrees', 'matched_address_number_agrees', 'geocode_assignment_suspect' ] working = df.drop(columns=[c for c in geocode_cols_to_drop if c in df.columns], errors='ignore').copy() cache_for_join = geocode_cache.drop(columns=['full_address'], errors='ignore').copy() working = working.merge(cache_for_join, on='address_key', how='left') for col in ['longitude', 'latitude', 'score']: working[col] = pd.to_numeric(working[col], errors='coerce') # Apply building-level manual coordinate overrides when present and within broad configured bounds. if 'manual_coordinate_valid' not in working.columns: working['manual_coordinate_valid'] = False if working['manual_coordinate_valid'].any(): manual_coord_mask = working['manual_coordinate_valid'] working.loc[manual_coord_mask, 'latitude'] = working.loc[manual_coord_mask, 'manual_latitude'] working.loc[manual_coord_mask, 'longitude'] = working.loc[manual_coord_mask, 'manual_longitude'] working.loc[manual_coord_mask, 'score'] = 100 working.loc[manual_coord_mask, 'match_address'] = 'Manual coordinate override' working.loc[manual_coord_mask, 'addr_type'] = 'ManualOverride' working.loc[manual_coord_mask, 'geocode_status'] = 'manual_override' working.loc[manual_coord_mask, 'geocode_source'] = 'manual_override_csv' working.loc[manual_coord_mask, 'raw_result'] = working.loc[manual_coord_mask, 'override_notes'] WEAK_ADDR_TYPES = {'StreetName', 'Postal', 'PostalLoc', 'Locality', 'Subregion', 'Region', 'POI'} working['has_coordinates'] = working['latitude'].notna() & working['longitude'].notna() working['has_manual_coordinate_override'] = working['manual_coordinate_valid'].fillna(False) working['has_manual_override'] = working['has_any_manual_override'].fillna(False) working['meets_score_threshold'] = working['score'].fillna(0).ge(CONFIG.geocode_score_min) working['weak_match_type'] = working['addr_type'].fillna('').isin(WEAK_ADDR_TYPES) working['manual_location_review_unresolved'] = ( working['manual_location_review_needed'] & ~working['has_manual_coordinate_override'] & ~working['accepted_geocode_candidate'] ) CITY_NAME_ALIASES = { # Source workbook abbreviations or formatting that are equivalent to geocoder output. 'CUMBERLAND FUR': 'CUMBERLAND FURNACE', 'CUMBERLAND FURN': 'CUMBERLAND FURNACE', } def normalize_compare_text(value): if pd.isna(value): return '' text = str(value).upper().strip() text = re.sub(r'[^A-Z0-9\s]', ' ', text) text = re.sub(r'\s+', ' ', text).strip() return CITY_NAME_ALIASES.get(text, text) def compact_compare_text(value): return re.sub(r'\s+', '', normalize_compare_text(value)) def city_values_agree(input_city, matched_city): """Compare source/effective and matched city names while allowing harmless spacing/alias differences. Examples treated as equivalent: MCDONALD == Mc Donald LAFOLLETTE == La Follette CUMBERLAND FUR. == Cumberland Furnace """ input_norm = normalize_compare_text(input_city) matched_norm = normalize_compare_text(matched_city) if input_norm == matched_norm: return True return compact_compare_text(input_city) == compact_compare_text(matched_city) def first_address_number(value): if pd.isna(value): return '' match = re.search(r'\b\d+\b', str(value)) return match.group(0) if match else '' def parse_match_city_zip(match_address): """Best-effort parser for Esri match_address strings. Notes: - Use the last ZIP-like value in the match string, because street addresses can begin with five-digit house numbers such as '22117 Natchez Trace Rd'. - If the first comma-separated component is only a ZIP, treat the result as a postal-level match rather than as a street address. """ if pd.isna(match_address) or not str(match_address).strip(): return pd.Series({ 'matched_street_part': '', 'matched_city_part': '', 'matched_zip_part': '', 'matched_address_number': '', }) match_text = str(match_address) parts = [p.strip() for p in match_text.split(',')] street = parts[0] if len(parts) >= 1 else '' city = parts[1] if len(parts) >= 2 else '' zip_matches = re.findall(r'\b\d{5}(?:-\d{4})?\b', match_text) matched_zip = zip_matches[-1][:5] if zip_matches else '' # Postal-level matches often look like '37380, Sewanee, Tennessee'. # Do not treat the leading ZIP as a street address or address number. if re.fullmatch(r'\d{5}(?:-\d{4})?', street): street = '' city = parts[1] if len(parts) >= 2 else '' return pd.Series({ 'matched_street_part': street, 'matched_city_part': city, 'matched_zip_part': matched_zip, 'matched_address_number': first_address_number(street), }) matched_parts = working['match_address'].apply(parse_match_city_zip) working = pd.concat([working, matched_parts], axis=1) working['input_address_number'] = working['address'].apply(first_address_number) working['input_zip_part'] = ( working['postal_code'] .astype(str) .str.extract(r'(\d{5})', expand=False) .fillna('') ) working['matched_city_agrees'] = [ city_values_agree(input_city, matched_city) for input_city, matched_city in zip(working['city'], working['matched_city_part']) ] working['matched_zip_agrees'] = ( working['input_zip_part'] == working['matched_zip_part'].astype(str).str.extract(r'(\d{5})', expand=False).fillna('') ) working['matched_address_number_agrees'] = ( (working['input_address_number'] == '') | (working['matched_address_number'] == '') | (working['input_address_number'] == working['matched_address_number']) ) working['geocode_assignment_suspect'] = ( working['has_coordinates'] & ~working['has_manual_coordinate_override'] & ~working['accepted_geocode_candidate'] & ( ~working['matched_city_agrees'] | ~working['matched_zip_agrees'] | ~working['matched_address_number_agrees'] ) ) assignment_suspect_count = int(working['geocode_assignment_suspect'].sum()) manual_review_bypass = working['has_manual_coordinate_override'] | working['accepted_geocode_candidate'] working['geocode_quality_blocker'] = ( ~working['meets_score_threshold'] | working['weak_match_type'] | working['manual_location_review_unresolved'] | working['zip_conflict_for_address_city'] | working['potential_typo_review_needed'] | working['geocode_assignment_suspect'] ) working['geocode_review_needed'] = ( ~working['has_coordinates'] | working['manual_coordinate_invalid'] | (~manual_review_bypass & working['geocode_quality_blocker']) ) working['publish_ready'] = ( working['has_coordinates'] & ~working['geocode_review_needed'] & ~working['exclude_from_publish_override'] ) def build_geocode_review_reason(row): reasons = [] if bool(row.get('exclude_from_publish_override', False)): reasons.append('excluded_from_publish_by_manual_override') if bool(row.get('manual_coordinate_invalid', False)): reasons.append('manual_coordinate_invalid_or_out_of_bounds') if not bool(row.get('has_coordinates', False)): reasons.append('no_coordinates') if bool(row.get('accepted_geocode_candidate', False)): reasons.append('geocode_candidate_manually_accepted') if bool(row.get('has_manual_coordinate_override', False)): reasons.append('manual_coordinate_override') if not bool(row.get('accepted_geocode_candidate', False)) and not bool(row.get('has_manual_coordinate_override', False)): if not bool(row.get('meets_score_threshold', False)): reasons.append('below_score_threshold_or_no_score') if bool(row.get('weak_match_type', False)): reasons.append('weak_match_type') if bool(row.get('manual_location_review_unresolved', False)): reasons.append('manual_location_review_unresolved') if bool(row.get('zip_conflict_for_address_city', False)): reasons.append('zip_conflict_for_address_city') if bool(row.get('potential_typo_review_needed', False)): reasons.append('potential_typo_review_needed') if bool(row.get('geocode_assignment_suspect', False)): reasons.append('geocode_assignment_suspect') return '; '.join(reasons) working['geocode_review_reason'] = working.apply(build_geocode_review_reason, axis=1) coordinate_count = int(working['has_coordinates'].sum()) publish_ready_count = int(working['publish_ready'].sum()) geocode_review_count = int(working['geocode_review_needed'].sum()) manual_excluded_count = int(working['exclude_from_publish_override'].sum()) geocode_summary = pd.DataFrame([ {'metric': 'Total records', 'count': len(working)}, {'metric': 'Records with coordinates', 'count': coordinate_count}, {'metric': 'Records without coordinates', 'count': int((~working['has_coordinates']).sum())}, {'metric': 'Records with any manual override', 'count': int(working['has_manual_override'].sum())}, {'metric': 'Records with valid manual coordinate overrides', 'count': int(working['has_manual_coordinate_override'].sum())}, {'metric': 'Records accepting geocode candidate', 'count': int(working['accepted_geocode_candidate'].sum())}, {'metric': 'Records excluded from publish by manual override', 'count': manual_excluded_count}, {'metric': 'Records meeting score threshold', 'count': int(working['meets_score_threshold'].sum())}, {'metric': 'Records with weak match type', 'count': int(working['weak_match_type'].sum())}, {'metric': 'Records with suspect geocode assignment', 'count': assignment_suspect_count}, {'metric': 'Records with unresolved manual location review', 'count': int(working['manual_location_review_unresolved'].sum())}, {'metric': 'Records needing geocode review', 'count': geocode_review_count}, {'metric': 'Publish-ready records', 'count': publish_ready_count}, ]) display(geocode_summary) if coordinate_count == 0 and len(unique_addresses) > 0: logger.warning( 'No coordinates are present in the geocoded output. This means no current-version cache rows were joined ' 'and geocoding did not run during this notebook execution.' ) if CONFIG.run_publish and coordinate_count == 0: raise RuntimeError( 'Publishing stopped. The geocoded dataframe contains zero coordinate-bearing records. ' 'Set run_geocoding=True or restore a valid current-version geocode cache before publishing.' ) review_fields = [ 'building_code', 'building_name', 'full_address', 'latitude', 'longitude', 'score', 'match_address', 'addr_type', 'geocode_status', 'geocode_review_reason', 'source_address', 'source_city', 'source_state', 'source_postal_code', 'override_address', 'override_city', 'override_state', 'override_postal_code', 'has_address_override', 'manual_latitude', 'manual_longitude', 'manual_coordinate_valid', 'accepted_geocode_candidate', 'exclude_from_publish_override', 'override_notes', 'override_source', 'manual_location_review_reason', 'potential_typo_reason', 'has_coordinates', 'meets_score_threshold', 'weak_match_type', 'has_manual_override', 'has_manual_coordinate_override', 'manual_location_review_unresolved', 'zip_conflict_for_address_city', 'potential_typo_review_needed', 'geocode_assignment_suspect', 'matched_street_part', 'matched_city_part', 'input_zip_part', 'matched_zip_part', 'input_address_number', 'matched_address_number', 'matched_city_agrees', 'matched_zip_agrees', 'matched_address_number_agrees', ] review_fields = [field for field in review_fields if field in working.columns] if CONFIG.write_full_geocode_review: geocode_review = working.loc[ working['geocode_review_needed'] | working['exclude_from_publish_override'], review_fields, ].copy() else: geocode_review = working.loc[ working['geocode_assignment_suspect'] | working['exclude_from_publish_override'], review_fields, ].copy() geocode_review.to_csv(GEOCODE_VALIDATION_CSV, index=False) logger.info('Saved geocode validation review CSV: %s', GEOCODE_VALIDATION_CSV) display(geocode_review.head(100)) # The full geocoded output includes records that are not publish-ready. The publish step can filter them. working.to_csv(GEOCODED_CSV, index=False) logger.info('Saved geocoded CSV: %s', GEOCODED_CSV) df_geocoded = working.copy() ```
metric count
0 Total records 463
1 Records with coordinates 458
2 Records without coordinates 5
3 Records with any manual override 9
4 Records with valid manual coordinate overrides 9
5 Records accepting geocode candidate 0
6 Records excluded from publish by manual override 0
7 Records meeting score threshold 450
8 Records with weak match type 69
9 Records with suspect geocode assignment 37
10 Records with unresolved manual location review 65
11 Records needing geocode review 125
12 Publish-ready records 338
2026-07-08 19:25:10 | INFO | tdf_buildings_pipeline | Saved geocode validation review CSV: /arcgis/home/tdf_facilities/tdf_buildings_outputs/tdf_buildings_geocode_validation_review.csv
building_code building_name full_address latitude longitude score match_address addr_type geocode_status geocode_review_reason ... geocode_assignment_suspect matched_street_part matched_city_part input_zip_part matched_zip_part input_address_number matched_address_number matched_city_agrees matched_zip_agrees matched_address_number_agrees
0 10000006 Holston Mtn Radio Building National Forest 56, ELIZABETHTON, TN, 37643 36.382348 -82.148698 96.42 56 Forest Dr, Elizabethton, Tennessee, 37643 StreetAddress matched_M manual_location_review_unresolved ... False 56 Forest Dr Elizabethton 37643 37643 56 56 True True True
3 10010003 Carter County Shop 2643 Gap Creek Road, HAMPTON, TN, 37658 36.260423 -82.214202 100.00 2643 Gap Creek Rd, Hampton, Tennessee, 37658 PointAddress matched_M zip_conflict_for_address_city ... False 2643 Gap Creek Rd Hampton 37658 37658 2643 2643 True True True
12 04000007 Hendon Fire Tower Fire Tower Road, GRAYSVILLE, TN, 37338 35.448263 -85.246788 88.27 Hendon Fire Tower Rd, Graysville, Tennessee, 3... StreetName matched_M weak_match_type; manual_location_review_unreso... ... False Hendon Fire Tower Rd Graysville 37338 37338 True True True
13 04010001 Bledsoe SF Smoke House Route 3 Box 180, PIKEVILLE, TN, 37367 35.579069 -85.145580 94.03 3rd St, Pikeville, Tennessee, 37367 StreetName matched_M weak_match_type; manual_location_review_unreso... ... False 3rd St Pikeville 37367 37367 3 True True True
14 04010002 Bledsoe SF Storm Shelter Route 3 Box 180, PIKEVILLE, TN, 37367 35.579069 -85.145580 94.03 3rd St, Pikeville, Tennessee, 37367 StreetName matched_M weak_match_type; manual_location_review_unreso... ... False 3rd St Pikeville 37367 37367 3 True True True
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
363 71000015 Cookeville District Office 390 SOUTH LOWE, SUITE 10, COOKEVILLE, TN, 38501 36.153647 -85.499045 98.84 390 S Lowe Ave, Suite 10, Cookeville, Tennesse... Subaddress matched_M geocode_assignment_suspect ... True 390 S Lowe Ave Suite 10 38501 38501 390 390 False True True
369 71020001 Cookeville Tower Storage NONE, COOKEVILLE, TN, 38501 NaN NaN NaN NaN NaN NaN no_coordinates; below_score_threshold_or_no_sc... ... False 38501 False False True
370 71020002 Cookeville Tower NONE, COOKEVILLE, TN, 38501 NaN NaN NaN NaN NaN NaN no_coordinates; below_score_threshold_or_no_sc... ... False 38501 False False True
371 72010001 Smyrna Shop 208 Fire Tower Road, EVANSVILLE, TN, 37332 35.580564 -84.921875 89.27 208 Smyrna Firetower Rd, Evensville, Tennessee... PointAddress matched_M geocode_assignment_suspect ... True 208 Smyrna Firetower Rd Evensville 37332 37332 208 208 False True True
372 72010002 Smyrna Fire Tower 208 Fire Tower Road, EVANSVILLE, TN, 37332 35.580564 -84.921875 89.27 208 Smyrna Firetower Rd, Evensville, Tennessee... PointAddress matched_M geocode_assignment_suspect ... True 208 Smyrna Firetower Rd Evensville 37332 37332 208 208 False True True

100 rows × 46 columns

2026-07-08 19:25:10 | INFO | tdf_buildings_pipeline | Saved geocoded CSV: /arcgis/home/tdf_facilities/tdf_buildings_outputs/tdf_buildings_geocoded.csv ## 9. Create a Spatially Enabled DataFrame This step filters to publishable records, trims the outgoing schema to the operational field list, writes a publish-field preview CSV, and creates the Spatially Enabled DataFrame used for previewing and publishing. ```python # Choose publication filter. # Recommended for first production publish: publish_ready == True. # For internal QA layers, you may prefer has_coordinates == True so questionable records can be mapped and reviewed. PUBLISH_ONLY_READY_RECORDS = True if PUBLISH_ONLY_READY_RECORDS: publish_source_df = working.loc[working['publish_ready']].copy() else: publish_source_df = working.loc[working['has_coordinates']].copy() publish_candidate_suspect_count = int( publish_source_df['geocode_assignment_suspect'].sum() ) if 'geocode_assignment_suspect' in publish_source_df.columns else 0 if CONFIG.run_publish and publish_candidate_suspect_count > CONFIG.max_allowed_suspect_assignments: raise RuntimeError( f'Publishing stopped. Found {publish_candidate_suspect_count:,} suspect geocode assignments ' 'inside the selected publish dataset. ' f'The configured maximum is {CONFIG.max_allowed_suspect_assignments:,}. ' 'Use PUBLISH_ONLY_READY_RECORDS = True or review tdf_buildings_geocode_validation_review.csv.' ) if CONFIG.run_publish and publish_source_df.empty: raise RuntimeError( 'Publishing stopped. The selected publish dataframe is empty. ' 'Review geocoding output, cache status, and PUBLISH_ONLY_READY_RECORDS before publishing.' ) missing_publish_fields = [field for field in PUBLISH_ATTRIBUTE_FIELDS if field not in publish_source_df.columns] if missing_publish_fields: raise ValueError(f'Missing fields required for publication schema: {missing_publish_fields}') if CONFIG.sync_key_field not in PUBLISH_ATTRIBUTE_FIELDS: raise ValueError( f'CONFIG.sync_key_field ({CONFIG.sync_key_field}) must be included in PUBLISH_ATTRIBUTE_FIELDS ' 'so the upsert refresh strategy can match existing records.' ) publish_df = publish_source_df.loc[:, list(PUBLISH_ATTRIBUTE_FIELDS)].copy() # Normalize key text fields before publishing. The generated QA/geocode outputs are not altered here. publish_df['building_code'] = publish_df['building_code'].map(lambda value: clean_identifier(value, width=8)) publish_df['postal_code'] = publish_df['postal_code'].map(clean_postal_code) text_publish_fields = [ field for field in PUBLISH_ATTRIBUTE_FIELDS if field not in {'longitude', 'latitude', 'square_feet'} ] for field in text_publish_fields: publish_df[field] = publish_df[field].map(clean_text) publish_df['longitude'] = pd.to_numeric(publish_df['longitude'], errors='coerce') publish_df['latitude'] = pd.to_numeric(publish_df['latitude'], errors='coerce') publish_df['square_feet'] = pd.to_numeric(publish_df['square_feet'], errors='coerce') invalid_publish_coordinates = publish_df['longitude'].isna() | publish_df['latitude'].isna() if invalid_publish_coordinates.any(): invalid_count = int(invalid_publish_coordinates.sum()) raise ValueError( f'{invalid_count:,} selected publish records are missing latitude or longitude. ' 'Review publish_ready logic and manual overrides before publishing.' ) publish_df.to_csv(PUBLISH_FIELDS_PREVIEW_CSV, index=False) logger.info('Wrote publish-field preview CSV: %s', PUBLISH_FIELDS_PREVIEW_CSV) logger.info('Publish attribute field count: %s', len(PUBLISH_ATTRIBUTE_FIELDS)) logger.info('Publish attribute fields: %s', ', '.join(PUBLISH_ATTRIBUTE_FIELDS)) if publish_df.empty: logger.warning('No records met the current spatial publication filter.') sdf = publish_df.copy() else: sdf = pd.DataFrame.spatial.from_xy( publish_df, x_column='longitude', y_column='latitude', sr=4326, ) logger.info('Spatial dataframe records: %s', len(sdf)) logger.info('PUBLISH_ONLY_READY_RECORDS: %s', PUBLISH_ONLY_READY_RECORDS) display(sdf.head()) ``` 2026-07-08 19:25:10 | INFO | tdf_buildings_pipeline | Wrote publish-field preview CSV: /arcgis/home/tdf_facilities/tdf_buildings_outputs/tdf_buildings_publish_fields_preview.csv 2026-07-08 19:25:10 | INFO | tdf_buildings_pipeline | Publish attribute field count: 18 2026-07-08 19:25:10 | INFO | tdf_buildings_pipeline | Publish attribute fields: building_code, building_name, address, city, county, state, postal_code, longitude, latitude, square_feet, predominant_facility_use, building_utilization, asset_status, strategic_asset_status, building_status, geocode_source, override_notes, override_source 2026-07-08 19:25:10 | INFO | tdf_buildings_pipeline | Spatial dataframe records: 338 2026-07-08 19:25:10 | INFO | tdf_buildings_pipeline | PUBLISH_ONLY_READY_RECORDS: True
building_code building_name address city county state postal_code longitude latitude square_feet predominant_facility_use building_utilization asset_status strategic_asset_status building_status geocode_source override_notes override_source SHAPE
1 10010001 Carter County Residence 2651 Gap Creek Road HAMPTON CARTER TN 37658 -82.213546 36.260446 1400.0 Residential Utilized Current Mission Need Long Term 15+ yrs Active arcgis.geocoding.geocode {"spatialReference": {"wkid": 4326}, "x": -82....
2 10010002 Carter County Outbuilding #2 2651 Gap Creek Road HAMPTON CARTER TN 37658 -82.213546 36.260446 1600.0 Warehouse Space Utilized Current Mission Need Long Term 15+ yrs Active arcgis.geocoding.geocode {"spatialReference": {"wkid": 4326}, "x": -82....
4 01010001 Bluebird Storage Building 395 Firetower Lane HEISKELL ANDERSON TN 37754 -84.063614 36.139897 48.0 Warehouse Space Utilized Current Mission Need Long Term 15+ yrs Active arcgis.geocoding.geocode {"spatialReference": {"wkid": 4326}, "x": -84....
5 01010002 Bluebird Shop 395 Firetower Lane HEISKELL ANDERSON TN 37754 -84.063614 36.139897 800.0 Public Safety Utilized Current Mission Need Long Term 15+ yrs Active arcgis.geocoding.geocode {"spatialReference": {"wkid": 4326}, "x": -84....
6 01010003 Bluebird Office 395 Firetower Lane HEISKELL ANDERSON TN 37754 -84.063614 36.139897 120.0 Office Space Utilized Current Mission Need Long Term 15+ yrs Active arcgis.geocoding.geocode {"spatialReference": {"wkid": 4326}, "x": -84....
## 10. Optional map preview Run this after geocoding. It previews the in-memory points before publishing. ```python if len(sdf) > 0: preview_map = gis.map('Tennessee') sdf.spatial.plot(preview_map) display(preview_map) else: logger.info('No spatial records available for preview.') ``` Map() ## 11. Publish or refresh hosted feature layer Publishing uses the trimmed operational schema prepared in the previous step. New publishes are created by directly creating an empty hosted feature service, adding a single named point layer, and then adding features. This avoids the extra File Geodatabase/source item that can be created by the Spatially Enabled DataFrame `to_featurelayer()` helper. The new layer name is controlled by `CONFIG.publish_layer_name`. Existing hosted layers can be refreshed only when they contain the required published fields. Extra fields on an existing layer are not removed by upsert. To physically remove old fields from the hosted layer schema, publish a new layer with `feature_layer_item_id = ""`. ```python def chunked(sequence, size): for start in range(0, len(sequence), size): yield sequence[start:start + size] def normalize_sync_key(value): # Building codes are the sync key for this workflow and must remain 8-character text. return clean_identifier(value, width=8) def get_attr_case_insensitive(attributes, field_name, default=None): if not attributes: return default target = field_name.lower() for key, value in attributes.items(): if str(key).lower() == target: return value return default def get_layer_from_item(item_id): item = gis.content.get(item_id) if item is None: raise ValueError(f'No ArcGIS Online item found for item ID: {item_id}') if not getattr(item, 'layers', None): raise ValueError(f'Item has no layers: {item_id}') return item, item.layers[0] def update_item_metadata(item): """Update only stable item properties that the notebook is allowed to manage. Metadata policy for this project: - The notebook manages the item title and summary/snippet only. - Description, tags, credits, terms of use, thumbnail, categories, sharing, formal metadata, pop-ups, and other AGOL-authored item details are intentionally preserved after the initial hosted feature layer is created. """ item.update(item_properties={ 'title': CONFIG.publish_title, 'snippet': CONFIG.publish_summary, }) return item def sanitize_feature_service_name(value): """Return a URL-safe hosted FeatureServer service name.""" text = clean_text(value) text = re.sub(r'[^A-Za-z0-9_]+', '_', text) text = re.sub(r'_+', '_', text).strip('_') if not text: raise ValueError('Feature service name cannot be blank after sanitizing.') if not re.match(r'^[A-Za-z]', text): text = f'TDF_{text}' return text[:120] def resolve_new_service_name(): requested = CONFIG.publish_service_name or CONFIG.publish_title service_name = sanitize_feature_service_name(requested) if gis.content.is_service_name_available(service_name, 'featureService'): return service_name if CONFIG.allow_service_name_suffix_if_unavailable: suffixed = sanitize_feature_service_name(f'{service_name}_{RUN_ID}') if gis.content.is_service_name_available(suffixed, 'featureService'): logger.warning( 'Requested feature service name is unavailable: %s. Using suffixed name: %s', service_name, suffixed, ) return suffixed raise ValueError( f'Feature service name is already unavailable: {service_name}. ' 'Delete/rename the existing service, set CONFIG.publish_service_name to a new URL-safe name, ' 'or set CONFIG.allow_service_name_suffix_if_unavailable = True for test publishes.' ) def assert_valid_sync_key(sdf): if CONFIG.sync_key_field not in sdf.columns: raise ValueError(f'Sync key field is missing from publish dataframe: {CONFIG.sync_key_field}') keys = sdf[CONFIG.sync_key_field].map(normalize_sync_key) missing_count = int(keys.eq('').sum()) duplicate_count = int(keys.duplicated(keep=False).sum()) if missing_count: raise ValueError(f'{missing_count:,} publish records have blank {CONFIG.sync_key_field}; upsert requires a populated key.') if duplicate_count: duplicate_keys = sorted(keys[keys.duplicated(keep=False)].unique())[:20] raise ValueError( f'{duplicate_count:,} publish records have duplicate {CONFIG.sync_key_field} values. ' f'Examples: {duplicate_keys}' ) def layer_field_lookup(layer): return {field['name'].lower(): field['name'] for field in layer.properties.fields} def layer_system_field_names(layer): object_id_field = layer.properties.objectIdField geometry_field = getattr(layer.properties, 'geometryField', None) system_fields = {object_id_field.lower(), 'shape'} if geometry_field: system_fields.add(str(geometry_field).lower()) return system_fields def warn_existing_layer_has_extra_fields(layer, sdf): fields_by_lower = layer_field_lookup(layer) system_fields = layer_system_field_names(layer) incoming_fields = {str(col).lower() for col in sdf.columns} extra_fields = [ actual_name for lower_name, actual_name in fields_by_lower.items() if lower_name not in system_fields and lower_name not in incoming_fields ] if extra_fields: logger.warning( 'Existing hosted layer contains %s fields that are not in the current publish schema. ' 'The upsert refresh strategy will not remove fields from an existing layer. ' 'Publish a new layer with CONFIG.feature_layer_item_id blank if you need the hosted layer schema trimmed. ' 'Extra fields: %s', len(extra_fields), extra_fields, ) def validate_layer_schema(layer, sdf): fields_by_lower = layer_field_lookup(layer) system_fields = layer_system_field_names(layer) missing_fields = [ col for col in sdf.columns if col.lower() not in system_fields and col.lower() not in fields_by_lower ] if missing_fields: raise ValueError( 'The existing hosted layer is missing fields that are present in the publish dataframe: ' f'{missing_fields}. Add these fields to the layer or republish a new layer.' ) if CONFIG.sync_key_field.lower() not in fields_by_lower: raise ValueError(f'The existing hosted layer does not contain sync key field: {CONFIG.sync_key_field}') warn_existing_layer_has_extra_fields(layer, sdf) def get_feature_list_from_sdf(sdf): return sdf.spatial.to_featureset().features def align_feature_attributes_to_layer(layer, features): fields_by_lower = layer_field_lookup(layer) object_id_field = layer.properties.objectIdField for feature in features: attrs = feature.attributes or {} aligned = {} for key, value in attrs.items(): if str(key).lower() == 'shape': continue target_field = fields_by_lower.get(str(key).lower()) if target_field: aligned[target_field] = value attrs.clear() attrs.update(aligned) return features, fields_by_lower[CONFIG.sync_key_field.lower()], object_id_field def publish_field_definition(field_name): alias = PUBLISH_FIELD_ALIASES.get(field_name, field_name.replace('_', ' ').title()) string_lengths = { 'building_code': 32, 'building_name': 255, 'address': 255, 'city': 100, 'county': 100, 'state': 16, 'postal_code': 20, 'predominant_facility_use': 255, 'building_utilization': 255, 'asset_status': 255, 'strategic_asset_status': 255, 'building_status': 255, 'geocode_source': 100, 'override_notes': 1000, 'override_source': 255, } if field_name in {'longitude', 'latitude', 'square_feet'}: return { 'name': field_name, 'type': 'esriFieldTypeDouble', 'alias': alias, 'nullable': True, 'editable': True, } return { 'name': field_name, 'type': 'esriFieldTypeString', 'alias': alias, 'length': string_lengths.get(field_name, 255), 'nullable': True, 'editable': True, } def build_publish_layer_definition(sdf): xmin = float(sdf['longitude'].min()) xmax = float(sdf['longitude'].max()) ymin = float(sdf['latitude'].min()) ymax = float(sdf['latitude'].max()) fields = [ { 'name': 'OBJECTID', 'type': 'esriFieldTypeOID', 'alias': 'OBJECTID', 'nullable': False, 'editable': False, } ] fields.extend(publish_field_definition(field_name) for field_name in PUBLISH_ATTRIBUTE_FIELDS) default_attributes = {field_name: None for field_name in PUBLISH_ATTRIBUTE_FIELDS} return { 'id': 0, 'name': CONFIG.publish_layer_name, 'type': 'Feature Layer', 'displayField': 'building_name', 'description': CONFIG.publish_summary, 'geometryType': 'esriGeometryPoint', 'hasZ': False, 'hasM': False, 'objectIdField': 'OBJECTID', 'uniqueField': { 'name': 'OBJECTID', 'isSystemMaintained': True, }, 'spatialReference': {'wkid': 4326, 'latestWkid': 4326}, 'extent': { 'xmin': xmin, 'ymin': ymin, 'xmax': xmax, 'ymax': ymax, 'spatialReference': {'wkid': 4326, 'latestWkid': 4326}, }, 'fields': fields, 'indexes': [ { 'name': 'PK_OBJECTID', 'fields': 'OBJECTID', 'isAscending': True, 'isUnique': True, 'description': 'Primary key index', }, ], 'types': [], 'templates': [ { 'name': CONFIG.publish_layer_name, 'description': '', 'drawingTool': 'esriFeatureEditToolPoint', 'prototype': {'attributes': default_attributes}, } ], 'drawingInfo': { 'renderer': { 'type': 'simple', 'symbol': { 'type': 'esriSMS', 'style': 'esriSMSCircle', 'size': 7, 'color': [0, 112, 255, 180], 'outline': {'color': [255, 255, 255, 255], 'width': 0.75}, }, }, }, 'capabilities': 'Query,Create,Update,Delete,Editing', } def add_to_definition_succeeded(result): if isinstance(result, dict): if result.get('success') is True: return True if str(result.get('status', '')).lower() == 'completed': return True if 'error' in result: return False return bool(result) def wait_for_first_layer(item, timeout_seconds=60): deadline = time.time() + timeout_seconds refreshed_item = item while time.time() < deadline: refreshed_item = gis.content.get(item.id) if refreshed_item is not None and getattr(refreshed_item, 'layers', None): return refreshed_item, refreshed_item.layers[0] time.sleep(2) raise RuntimeError( f'Hosted feature service was created, but no layer appeared within {timeout_seconds} seconds. ' f'Item ID: {item.id}' ) def apply_publish_field_aliases(layer): """Best-effort alias update for the operational fields. Field order comes from the publish layer definition when creating a new layer. Existing hosted layers keep their current field order; upsert edits records but does not restructure the layer schema. """ fields_by_lower = layer_field_lookup(layer) field_updates = [] for field_name, alias in PUBLISH_FIELD_ALIASES.items(): actual_name = fields_by_lower.get(field_name.lower()) if actual_name: field_updates.append({'name': actual_name, 'alias': alias}) if not field_updates: return None try: result = layer.manager.update_definition({'fields': field_updates}) logger.info('Applied publish field aliases: %s', result) return result except Exception as exc: logger.warning('Could not apply field aliases automatically: %s', exc) return None def apply_publish_layer_name(layer): """Best-effort update for Layer (0) display name.""" try: current_name = clean_text(getattr(layer.properties, 'name', '')) if current_name != CONFIG.publish_layer_name: result = layer.manager.update_definition({'name': CONFIG.publish_layer_name}) logger.info('Applied layer name %r: %s', CONFIG.publish_layer_name, result) return result except Exception as exc: logger.warning('Could not apply layer name automatically: %s', exc) return None def apply_publish_layer_metadata(item, layer): apply_publish_layer_name(layer) apply_publish_field_aliases(layer) update_item_metadata(item) return item def create_empty_hosted_feature_service(): service_name = resolve_new_service_name() logger.info('Creating hosted feature service. Item title: %s', CONFIG.publish_title) logger.info('FeatureServer service name: %s', service_name) item = gis.content.create_service( name=service_name, service_type='featureService', service_description=CONFIG.publish_summary, description=CONFIG.publish_description, capabilities='Query,Create,Update,Delete,Editing', max_record_count=CONFIG.max_record_count, supported_query_formats='JSON,geoJSON', wkid=4326, item_properties={ 'title': CONFIG.publish_title, 'snippet': CONFIG.publish_summary, 'description': CONFIG.publish_description, 'tags': ','.join(CONFIG.publish_tags), }, tags=','.join(CONFIG.publish_tags), snippet=CONFIG.publish_summary, ) if item is None: raise RuntimeError('gis.content.create_service() returned None. The hosted feature service was not created.') return item, service_name def publish_new_feature_layer(sdf): """Create a named hosted feature service/layer directly, avoiding a separate File Geodatabase item.""" assert_valid_sync_key(sdf) item, service_name = create_empty_hosted_feature_service() try: flc = FeatureLayerCollection.fromitem(item) layer_definition = build_publish_layer_definition(sdf) add_layer_result = flc.manager.add_to_definition({'layers': [layer_definition]}) logger.info('Add layer definition result: %s', add_layer_result) if not add_to_definition_succeeded(add_layer_result): raise RuntimeError(f'Could not add layer definition to hosted feature service: {add_layer_result}') item, layer = wait_for_first_layer(item) validate_layer_schema(layer, sdf) features, _, _ = align_feature_attributes_to_layer(layer, get_feature_list_from_sdf(sdf)) add_results = add_features_in_batches(layer, features) apply_publish_layer_metadata(item, layer) return item, { 'new_publish': True, 'item_id': item.id, 'feature_service_name': service_name, 'layer_name': CONFIG.publish_layer_name, 'add_layer_result': add_layer_result, 'add_results': add_results, 'created_without_source_file_geodatabase': True, } except Exception: logger.exception('New hosted feature service publish failed. Attempting to delete the partially created service item: %s', item.id) try: item.delete() except Exception as cleanup_exc: logger.warning('Could not delete partially created service item %s: %s', item.id, cleanup_exc) raise def add_features_in_batches(layer, features, batch_size=CONFIG.publish_batch_size): add_results = [] for batch_number, feature_batch in enumerate(chunked(features, batch_size), start=1): logger.info('Adding batch %s with %s features', batch_number, len(feature_batch)) result = layer.edit_features(adds=feature_batch) add_results.append(result) return add_results def refresh_existing_feature_layer_delete_add(item_id, sdf): item, layer = get_layer_from_item(item_id) validate_layer_schema(layer, sdf) features, _, _ = align_feature_attributes_to_layer(layer, get_feature_list_from_sdf(sdf)) logger.info('Deleting existing features from layer: %s', layer.properties.name) delete_result = layer.delete_features(where='1=1') logger.info('Delete result: %s', delete_result) add_results = add_features_in_batches(layer, features) apply_publish_layer_metadata(item, layer) return item, {'delete_result': delete_result, 'add_results': add_results} def refresh_existing_feature_layer_truncate_add(item_id, sdf): item, layer = get_layer_from_item(item_id) validate_layer_schema(layer, sdf) features, _, _ = align_feature_attributes_to_layer(layer, get_feature_list_from_sdf(sdf)) logger.info('Truncating layer: %s', layer.properties.name) truncate_result = layer.manager.truncate(asynchronous=CONFIG.truncate_async, wait=True) logger.info('Truncate result: %s', truncate_result) add_results = add_features_in_batches(layer, features) apply_publish_layer_metadata(item, layer) return item, {'truncate_result': truncate_result, 'add_results': add_results} def query_existing_sync_keys(layer, sync_field_name, object_id_field): out_fields = f'{object_id_field},{sync_field_name}' existing = layer.query( where='1=1', out_fields=out_fields, return_geometry=False, return_all_records=True, ) existing_by_key = {} duplicate_existing_keys = set() for feature in existing.features: attrs = feature.attributes or {} key = normalize_sync_key(get_attr_case_insensitive(attrs, sync_field_name)) oid = get_attr_case_insensitive(attrs, object_id_field) if not key: continue if key in existing_by_key: duplicate_existing_keys.add(key) existing_by_key[key] = oid if duplicate_existing_keys: examples = sorted(duplicate_existing_keys)[:20] raise ValueError( f'The existing hosted layer has duplicate {CONFIG.sync_key_field} values. ' f'Upsert would be ambiguous. Examples: {examples}' ) return existing_by_key def refresh_existing_feature_layer_upsert(item_id, sdf): assert_valid_sync_key(sdf) item, layer = get_layer_from_item(item_id) validate_layer_schema(layer, sdf) features, sync_field_name, object_id_field = align_feature_attributes_to_layer(layer, get_feature_list_from_sdf(sdf)) existing_by_key = query_existing_sync_keys(layer, sync_field_name, object_id_field) incoming_by_key = {} for feature in features: key = normalize_sync_key(get_attr_case_insensitive(feature.attributes, sync_field_name)) incoming_by_key[key] = feature updates = [] adds = [] for key, feature in incoming_by_key.items(): if key in existing_by_key: feature.attributes[object_id_field] = existing_by_key[key] updates.append(feature) else: feature.attributes.pop(object_id_field, None) adds.append(feature) delete_oids = [] if CONFIG.upsert_delete_missing_records: delete_oids = [oid for key, oid in existing_by_key.items() if key not in incoming_by_key] logger.info('Existing layer records: %s', len(existing_by_key)) logger.info('Incoming records: %s', len(incoming_by_key)) logger.info('Updates: %s', len(updates)) logger.info('Adds: %s', len(adds)) logger.info('Deletes: %s', len(delete_oids)) edit_results = {'update_results': [], 'add_results': [], 'delete_results': []} for batch_number, feature_batch in enumerate(chunked(updates, CONFIG.publish_batch_size), start=1): logger.info('Updating batch %s with %s features', batch_number, len(feature_batch)) edit_results['update_results'].append(layer.edit_features(updates=feature_batch)) for batch_number, feature_batch in enumerate(chunked(adds, CONFIG.publish_batch_size), start=1): logger.info('Adding batch %s with %s features', batch_number, len(feature_batch)) edit_results['add_results'].append(layer.edit_features(adds=feature_batch)) for batch_number, oid_batch in enumerate(chunked(delete_oids, CONFIG.publish_batch_size), start=1): logger.info('Deleting batch %s with %s features', batch_number, len(oid_batch)) edit_results['delete_results'].append(layer.edit_features(deletes=','.join(map(str, oid_batch)))) apply_publish_layer_metadata(item, layer) return item, edit_results def refresh_existing_feature_layer(item_id, sdf): strategy = CONFIG.refresh_strategy.strip().lower() if strategy == 'upsert': return refresh_existing_feature_layer_upsert(item_id, sdf) if strategy == 'truncate_add': return refresh_existing_feature_layer_truncate_add(item_id, sdf) if strategy == 'delete_add': return refresh_existing_feature_layer_delete_add(item_id, sdf) raise ValueError("CONFIG.refresh_strategy must be 'upsert', 'truncate_add', or 'delete_add'.") if CONFIG.run_publish: if len(sdf) == 0: raise ValueError('No spatial records are available to publish.') if CONFIG.feature_layer_item_id.strip(): published_item, edit_results = refresh_existing_feature_layer(CONFIG.feature_layer_item_id.strip(), sdf) logger.info('Refreshed existing hosted feature layer item ID: %s', published_item.id) logger.info('Refresh strategy: %s', CONFIG.refresh_strategy) display(published_item) else: published_item, edit_results = publish_new_feature_layer(sdf) logger.info('Created new hosted feature layer item ID: %s', published_item.id) logger.info('FeatureServer service name: %s', edit_results.get('feature_service_name')) logger.info('Layer (0) name: %s', edit_results.get('layer_name')) logger.info('Paste this item ID into CONFIG.feature_layer_item_id for future refreshes.') display(published_item) else: logger.info('CONFIG.run_publish is False. Toggle it to True in the configuration cell to publish or refresh the hosted feature layer.') ``` 2026-07-08 19:25:19 | INFO | tdf_buildings_pipeline | Creating hosted feature service. Item title: TDF Buildings and Facilities 2026-07-08 19:25:19 | INFO | tdf_buildings_pipeline | FeatureServer service name: TDF_Buildings_and_Facilities 2026-07-08 19:25:38 | INFO | tdf_buildings_pipeline | Add layer definition result: {'success': True, 'layers': [{'name': 'TDF Buildings and Facilities', 'id': 0}]} 2026-07-08 19:25:39 | INFO | tdf_buildings_pipeline | Adding batch 1 with 200 features 2026-07-08 19:25:42 | INFO | tdf_buildings_pipeline | Adding batch 2 with 138 features 2026-07-08 19:25:46 | INFO | tdf_buildings_pipeline | Applied publish field aliases: {'success': True} 2026-07-08 19:25:48 | INFO | tdf_buildings_pipeline | Created new hosted feature layer item ID: a2f0811ed0e44e3e8933411739302e92 2026-07-08 19:25:48 | INFO | tdf_buildings_pipeline | FeatureServer service name: TDF_Buildings_and_Facilities 2026-07-08 19:25:48 | INFO | tdf_buildings_pipeline | Layer (0) name: TDF Buildings and Facilities 2026-07-08 19:25:48 | INFO | tdf_buildings_pipeline | Paste this item ID into CONFIG.feature_layer_item_id for future refreshes.
TDF Buildings and Facilities
Point locations for buildings owned and managed by Tennessee Division of Forestry.
Feature Layer Collection by colin.stiles_tndof
Last Modified: July 08, 2026
0 comments, 3 views
## 12. Run Summary Write a JSON summary for troubleshooting and auditability. ```python def write_run_summary(summary: dict[str, object], config: NotebookConfig = CONFIG) -> Path: """Write a JSON run summary to the logs folder.""" summary_path = config.log_dir / f"{config.project_name}_{RUN_ID}_summary.json" with summary_path.open("w", encoding="utf-8") as file: json.dump(summary, file, indent=2, default=str) logger.info("Wrote run summary: %s", summary_path) return summary_path run_summary = { "project_name": CONFIG.project_name, "run_id": RUN_ID, "started_utc": RUN_STARTED_UTC.replace(microsecond=0).isoformat(), "completed_utc": datetime.now(timezone.utc).replace(microsecond=0).isoformat(), "run_geocoding": CONFIG.run_geocoding, "run_publish": CONFIG.run_publish, "geocode_mode": CONFIG.geocode_mode, "geocode_cache_version": CONFIG.geocode_cache_version, "force_regeocode": CONFIG.force_regeocode, "max_allowed_suspect_assignments": CONFIG.max_allowed_suspect_assignments, "minimum_publish_ready_records": CONFIG.minimum_publish_ready_records, "write_full_geocode_review": CONFIG.write_full_geocode_review, "source_path": str(CONFIG.source_path), "manual_overrides_csv": str(MANUAL_OVERRIDES_CSV), "manual_override_audit_csv": str(MANUAL_OVERRIDE_AUDIT_CSV), "normalized_csv": str(NORMALIZED_CSV), "qa_report_csv": str(QA_REPORT_CSV), "address_review_csv": str(ADDRESS_REVIEW_CSV), "qa_review_xlsx": str(QA_REVIEW_XLSX), "geocode_cache_csv": str(GEOCODE_CACHE_CSV), "geocoded_csv": str(GEOCODED_CSV), "geocode_validation_csv": str(GEOCODE_VALIDATION_CSV), "publish_fields_preview_csv": str(PUBLISH_FIELDS_PREVIEW_CSV), "publish_title": CONFIG.publish_title, "publish_service_name": CONFIG.publish_service_name, "publish_layer_name": CONFIG.publish_layer_name, "publish_attribute_fields": list(PUBLISH_ATTRIBUTE_FIELDS), } if 'df' in locals(): run_summary['total_records'] = len(df) run_summary['records_with_any_manual_override'] = int(df['has_any_manual_override'].sum()) if 'has_any_manual_override' in df.columns else None run_summary['records_with_address_overrides'] = int(df['has_address_override'].sum()) if 'has_address_override' in df.columns else None run_summary['records_with_valid_manual_coordinates'] = int(df['manual_coordinate_valid'].sum()) if 'manual_coordinate_valid' in df.columns else None run_summary['records_accepting_geocode_candidate'] = int(df['accepted_geocode_candidate'].sum()) if 'accepted_geocode_candidate' in df.columns else None run_summary['records_excluded_from_publish_by_override'] = int(df['exclude_from_publish_override'].sum()) if 'exclude_from_publish_override' in df.columns else None run_summary['manual_location_review_needed'] = int(df['manual_location_review_needed'].sum()) if 'manual_location_review_needed' in df.columns else None run_summary['potential_typo_review_needed'] = int(df['potential_typo_review_needed'].sum()) if 'potential_typo_review_needed' in df.columns else None run_summary['zip_conflict_records'] = int(df['zip_conflict_for_address_city'].sum()) if 'zip_conflict_for_address_city' in df.columns else None run_summary['unique_address_keys'] = int(df['address_key'].nunique()) if 'address_key' in df.columns else None run_summary['unique_geocode_eligible_addresses'] = len(unique_addresses) if 'unique_addresses' in locals() else None if 'geocode_cache_status' in locals(): run_summary['geocode_cache_status'] = geocode_cache_status.to_dict(orient='records') if 'geocode_summary' in locals(): run_summary['geocode_summary'] = geocode_summary.to_dict(orient='records') run_summary['assignment_suspect_count'] = assignment_suspect_count if 'assignment_suspect_count' in locals() else None run_summary['records_with_coordinates'] = coordinate_count if 'coordinate_count' in locals() else None run_summary['records_needing_geocode_review'] = geocode_review_count if 'geocode_review_count' in locals() else None run_summary['publish_ready_records'] = publish_ready_count if 'publish_ready_count' in locals() else None run_summary['manually_excluded_records'] = manual_excluded_count if 'manual_excluded_count' in locals() else None if 'sdf' in locals(): run_summary['publish_candidate_records'] = len(sdf) run_summary['publish_candidate_fields'] = [str(col) for col in sdf.columns if str(col).lower() != 'shape'] if CONFIG.run_publish and 'edit_results' in locals(): run_summary['publish_strategy'] = CONFIG.refresh_strategy run_summary['publish_item_id'] = getattr(published_item, 'id', '') run_summary['edit_results'] = edit_results summary_path = write_run_summary(run_summary) ``` 2026-07-08 19:25:48 | INFO | tdf_buildings_pipeline | Wrote run summary: /arcgis/home/tdf_facilities/tdf_buildings_outputs/logs/tdf_buildings_pipeline_20260708T192506Z_summary.json ## 13. Final output checklist Review these generated files after each run: - `tdf_buildings_geocoded.csv` - full diagnostic output with all QA/geocoding fields. - `tdf_buildings_geocode_validation_review.csv` - records still blocked from production publishing. - `tdf_buildings_manual_overrides.csv` - reviewer-managed correction file; preserve this between runs. - `tdf_buildings_publish_fields_preview.csv` - exact attribute columns selected for the hosted feature layer. For new publishes, v10 creates the hosted feature service directly and adds a named Layer (0), avoiding the extra source File Geodatabase item. If you need the hosted layer schema physically reduced, publish a new layer with `feature_layer_item_id = ""`. Upsert can refresh records in an existing layer, but it does not remove fields that already exist on that hosted layer.