Spatial Data Processing

An address-cleaning and geocoding pipeline that turns a raw building inventory from a telecommunications provider into a validated, geocoded dataset joined to switch service areas.

The problem

Building inventories arrive as spreadsheets written by humans: inconsistent abbreviations, missing house numbers, PO boxes where street addresses should be, and the same building listed several times under slightly different spellings.

Geocoding that directly produces poor results — bad addresses either fail outright or, worse, resolve to the centre of a city and look plausible. This pipeline standardises and validates before geocoding, then filters the results by confidence.

Pipeline

Step	What happens
1	Read the raw building inventory spreadsheet
2	Reformat into the address layout SmartyStreets expects
3	Run SmartyStreets to standardise and validate addresses
4	Reassemble standardised components into a clean address string
5	Flag bad addresses — missing house number, missing street name, PO boxes
6	Remove duplicates on address + state + ZIP, exporting the duplicates for review
7	Geocode the deduplicated list
8	Keep only results with accuracy_type = house number and accuracy ≥ 0.8
9	Recover borderline addresses using SmartyStreets coordinates where available
10	Spatially join to switch boundaries to attach sw_clli, npa, nxx and lata

Step 8 is the quality gate. A geocoder will happily return a result for a partial address by falling back to the street, ZIP, or city centroid — those are recorded as different accuracy types. Only rooftop-level matches are accepted into the final dataset.

Step 9 exists because that gate is strict: some addresses geocode to a lower accuracy type but were already validated by SmartyStreets, so their coordinates are recovered from there rather than discarded.

Requirements

• Python 3.8+ with Jupyter
• pandas, geopandas, shapely, openpyxl
• SmartyStreets — external address validation tool
• A geocoding tool, invoked from c2f_geocoder/

pip install pandas geopandas shapely openpyxl jupyter

Usage

Open Script.ipynb and run the cells in order.

Steps 3 and 9 are manual — the notebook prepares the input CSV, you run the external tool, and the notebook reads its output back. The markdown cells mark where to stop.

Input data

Not included — the building inventory is client data. To run this you need:

File	Contents
Lit Building Inventory - Public.xlsx	Raw building list, with a 3-row header
clli_boundary/clli_boundary.shp	Switch service area boundaries
SmartyStreets/	Address validation tool and its input/output CSVs
c2f_geocoder/	Geocoding tool and its input/output CSVs

Output

• duplicated.xlsx — duplicate addresses removed, for review
• good.csv — validated, geocoded addresses
• A joined GeoDataFrame carrying switch identifiers per building