Break the Levee#

Property data analysis tool for investigating the effects of REITs on real estate, home values, and more.

Summary#

This project imports, geocodes, and analyzes property ownership data from Franklin County, Ohio. It maintains a SQLite database of property records with multiple coordinate sources (parcel-based LBRS data and Mapbox geocoding) to enable spatial analysis of property ownership patterns.

Setup and Sample Usage#

Installation#

pnpm install

Environment Setup#

Copy the environment sample file and add your Mapbox API key:

cp .env.sample .env

Edit .env and add your Mapbox API key from https://account.mapbox.com/access-tokens/

MAPBOX_API_KEY=pk.your_api_key_here

Import Property Data#

Import property records from CSV files in the data/ directory:

bun run src/import.ts

Geocode Properties#

Realtor.com Property ID Mapping#

pnpm fetch-realtor-ids

The script matches each property address to a Realtor.com mpr_id using the public suggest‑filters API. Exact matches are saved automatically. If the address does not match, you will be prompted to confirm the mismatch (y to accept, any other key to skip). The resulting IDs are stored in the property_realtor_ids table.

Note: Run pnpm db:migrate first if you haven’t applied the new migration.

Option 1: Parcel-based geocoding (Franklin County LBRS)

bun run src/geocode.ts

Uses Franklin County's LBRS Address Points dataset with State Plane coordinate conversion. Fast and accurate for Franklin County parcels.

Option 2: Mapbox API geocoding

bun run src/geocodeMapbox.ts

Uses Mapbox Geocoding API for address-based geocoding. Works nationwide, requires API key (free tier: 100k requests/month).

Database Queries#

# Open database studio
pnpm run db:studio

# Or query directly
sqlite3 database.sqlite3 "SELECT * FROM properties LIMIT 10;"

Data Sources#

Primary Sources#

Franklin County Auditor - Property records, ownership data, tax information
- Source: https://auditor.franklincountyohio.gov/
- Used for: Property details, owner names, tax IDs, addresses
Franklin County LBRS Address Points - Parcel coordinate data
- Source: https://auditor-fca.opendata.arcgis.com/datasets/fca::lbrs-address-points/about
- Used for: Precise parcel-level coordinates (State Plane → WGS84 conversion)
- Coordinate system: Custom Lambert Conformal Conic (lat_0=38.0, based on Ohio State Plane South)
Mapbox Geocoding API - Address geocoding service
- Source: https://www.mapbox.com/geocoding
- Used for: Fallback geocoding and validation
- Free tier: 100,000 requests/month

CSV Import Files#

Property ownership CSVs in data/ directory include:

Vinebrook Homes-related entities (various LLCs)
NREA VB V LLC
Other institutional landlord records

Directory Structure#

break-the-levee/
├── data/                 # Source CSV files for import
│   ├── nrea-vb-v-llc.csv
│   ├── vb-*.csv
│   ├── vinebrook-homes-*.csv
│   # (LBRS_Address_Points.csv not included; obtain from public dataset)
│
├── src/                  # Source code
│   ├── db/               # Database schema and configuration
│   │   └── schema/       # Drizzle ORM table definitions
│   ├── import.ts         # CSV import script
│   ├── geocode.ts        # LBRS-based geocoding (parcel coordinates)
│   └── geocodeMapbox.ts  # Mapbox API geocoding (address-based)
│
├── drizzle/              # Database migrations
│   ├── 0000_add_properties.sql
│   ├── 0001_add_property_coordinates.sql
│   └── 0002_add_mapbox_coordinates.sql
│
├── database.sqlite3      # SQLite database (auto-generated)
├── drizzle.config.ts     # Drizzle ORM configuration
├── package.json          # Dependencies and scripts
├── tsconfig.json         # TypeScript configuration
├── .env.sample           # Environment variable template
└── README.md             # This file

Key Directories#

data/ - Raw CSV files from Franklin County Auditor and other sources. Do not commit large data files to git.
src/db/schema/ - Database table definitions using Drizzle ORM
drizzle/ - SQL migrations for database schema changes
src/ - TypeScript source code for data import and geocoding

Database Tables#

properties - Main property records (taxid, owner, address, etc.)
property_coordinates - LBRS parcel-based coordinates (Franklin County only)
property_coordinates_mapbox - Mapbox geocoded coordinates (nationwide)
property_realtor_ids - Realtor.com property IDs (mpr_id) and match metadata

License#

GPL-2.0-or-later

Configure Feed