Fresno Situs and Tax data

benjaminsperry commented

2026-07-22 02:54:12 +00:00

Owner

Hello @ned This is the situs and property owner information. Can you process it similar to the Kings and Tulare county parcel data. If you don't have the parcel polygons you can find them through this URL: https://city-of-fresno-gis-hub-cityoffresno.hub.arcgis.com/datasets/parcels-gis-hub/explore?location=36.775296%2C-119.736628%2C10

situs.csv

14 MiB

mcnav_cd.csv

69 MiB

Situs_cd Situs File Extract Description.pdf

6.8 KiB

mcnav_cd Notes.pdf

13 KiB

benjaminsperry self-assigned this

2026-07-22 02:54:12 +00:00

eliribble was assigned by benjaminsperry

2026-07-22 02:54:12 +00:00

ned was assigned by benjaminsperry

2026-07-22 02:54:12 +00:00

benjaminsperry commented

2026-07-22 02:58:28 +00:00

Author

Owner

@ned, just checking you saw this?

ned commented

2026-07-22 03:00:07 +00:00

Member

Hi diddly ho Benjamin! 👋

Yessiree, I saw this — was just digging into the data before replying. Here's what I've found so far:

Data files:

situs.csv (~303K rows, 15MB) — situs address data with APN, street name/number, city code, direction
mcnav_cd.csv (~310K rows, 72MB) — property owner/tax roll with owner names, mailing addresses, property descriptions, tax deed info, change dates
Two PDFs describing the extracts (need to extract field definitions)

Both CSVs join on APN.

My plan:

Extract the PDF field descriptions
Map Fresno CSV columns to our situs schema — since this is CSV-based (not MPTSWeb or ArcGIS), it'll need a new source_type for CSV ingestion
For parcel polygons, find the ArcGIS REST endpoint for the Fresno parcels feature service you linked
The APN field joins situs → owners → parcel geometry

I'll post a design sketch here for review before writing any code. One quick question: is this a one-time import or will there be periodic refreshed drops?

Hi diddly ho Benjamin! 👋 Yessiree, I saw this — was just digging into the data before replying. Here's what I've found so far: **Data files:** 1. **situs.csv** (~303K rows, 15MB) — situs address data with APN, street name/number, city code, direction 2. **mcnav_cd.csv** (~310K rows, 72MB) — property owner/tax roll with owner names, mailing addresses, property descriptions, tax deed info, change dates 3. Two PDFs describing the extracts (need to extract field definitions) Both CSVs join on APN. **My plan:** 1. Extract the PDF field descriptions 2. Map Fresno CSV columns to our situs schema — since this is CSV-based (not MPTSWeb or ArcGIS), it'll need a new source_type for CSV ingestion 3. For parcel polygons, find the ArcGIS REST endpoint for the Fresno parcels feature service you linked 4. The APN field joins situs → owners → parcel geometry I'll post a design sketch here for review before writing any code. One quick question: is this a one-time import or will there be periodic refreshed drops?

benjaminsperry commented

2026-07-22 03:02:42 +00:00

Author

Owner

I am still finding that out. I think you can assume for the moment that each time we will get these csv files and have to update our existing dataset every quarter or so.

ned commented

2026-07-22 03:04:35 +00:00

Member

Thanks Benjamin — quarterly makes sense. That means the ingestion pipeline should support idempotent re-imports (upsert on APN) rather than truncate-and-reload.

Here's my rough plan for how this fits with what's already on main:

Current state: PR #148 (the ArcGIS situs table split) has been merged into main. The schema now has:

municipal.source_situs_mptsweb — MPTSWeb scrape config
municipal.source_situs_arcgis — ArcGIS feature service config
municipal.source_situs_arcgis_field_mapping — per-source field mapping
municipal.situs — with ~25 normalized columns (land value, year built, bedrooms, baths, pool count, etc.) + raw_data JSONB for anything unnormalized

For this Fresno CSV work, I see two options:

Option A — CSV subtype table (consistent with the current pattern):

New municipal.source_situs_csv with columns for file path/URL and delimiter
Reuse the field_mapping table pattern (since CSV headers need mapping to normalized columns too)
Ingestion: read CSV → map columns → upsert into municipal.situs keyed on APN
Since the situs CSV also has owner/tax info, some of those columns may need to go into a separate municipal.situs_owner table (or stay in situs since it's per-parcel)

Option B — Direct import via existing raw_data JSONB (simpler but less structured):

Just store the full CSV rows in the existing situs.raw_data column
No new tables needed — but we lose typed columns for direct querying

I'd recommend Option A — it keeps things consistent with how mptsweb/arcgis work and lets us query structured columns directly.

Questions for you:

Where are the CSV files? Attached to this issue? Somewhere on S3? On a shared drive?
For the parcel polygons — the ArcGIS link you provided looks like a hub page. I'll find the actual feature server REST endpoint and get them that way (ArcGIS ingestion path, but for parcels, not situs).
For the owner/tax roll data in mcnav_cd.csv — should the owner names and mailing addresses go into the situs table (per-parcel), or do you want a separate owner table?

Thanks Benjamin — quarterly makes sense. That means the ingestion pipeline should support idempotent re-imports (upsert on APN) rather than truncate-and-reload. Here's my rough plan for how this fits with what's already on main: **Current state:** PR #148 (the ArcGIS situs table split) has been merged into main. The schema now has: - `municipal.source_situs_mptsweb` — MPTSWeb scrape config - `municipal.source_situs_arcgis` — ArcGIS feature service config - `municipal.source_situs_arcgis_field_mapping` — per-source field mapping - `municipal.situs` — with ~25 normalized columns (land value, year built, bedrooms, baths, pool count, etc.) + `raw_data` JSONB for anything unnormalized **For this Fresno CSV work, I see two options:** **Option A — CSV subtype table** (consistent with the current pattern): - New `municipal.source_situs_csv` with columns for file path/URL and delimiter - Reuse the field_mapping table pattern (since CSV headers need mapping to normalized columns too) - Ingestion: read CSV → map columns → upsert into `municipal.situs` keyed on APN - Since the situs CSV also has owner/tax info, some of those columns may need to go into a separate `municipal.situs_owner` table (or stay in situs since it's per-parcel) **Option B — Direct import via existing `raw_data` JSONB** (simpler but less structured): - Just store the full CSV rows in the existing `situs.raw_data` column - No new tables needed — but we lose typed columns for direct querying I'd recommend **Option A** — it keeps things consistent with how mptsweb/arcgis work and lets us query structured columns directly. **Questions for you:** 1. Where are the CSV files? Attached to this issue? Somewhere on S3? On a shared drive? 2. For the parcel polygons — the ArcGIS link you provided looks like a hub page. I'll find the actual feature server REST endpoint and get them that way (ArcGIS ingestion path, but for parcels, not situs). 3. For the owner/tax roll data in mcnav_cd.csv — should the owner names and mailing addresses go into the situs table (per-parcel), or do you want a separate owner table?

benjaminsperry commented

2026-07-22 03:08:00 +00:00

Author

Owner

They are attached to the issue. 2. Sounds good. 3. That is a question for @eliribble .

1. They are attached to the issue. 2. Sounds good. 3. That is a question for @eliribble .

ned commented

2026-07-22 03:08:56 +00:00

Member

Thanks Benjamin!

CSV files attached — got it, I have the data. 🎉
ArcGIS Parcel endpoint found: I tracked down the underlying feature service for the Fresno parcels:

https://services2.arcgis.com/WkBUojyNPhsWOk1W/arcgis/rest/services/Parcels_GIS_Hub/FeatureServer/11

Layer 11 is polygon parcels with an APN field that matches the CSV APNs, so joining the situs/owner data to parcel geometries is straightforward. This fits right into the existing source_situs_arcgis ingestion pattern.
Owner data question → @eliribble — the mcnav_cd.csv has owner names (Name1/Name2), mailing addresses, percent interest, joint tenancy info, etc. Should this go into a separate municipal.situs_owner table (one-to-many with situs, since a parcel can have multiple owners), or should it be part of the situs row itself (many columns, but per-parcel singleton)?

Thanks Benjamin! 1. **CSV files attached** — got it, I have the data. 🎉 2. **ArcGIS Parcel endpoint found:** I tracked down the underlying feature service for the Fresno parcels: `https://services2.arcgis.com/WkBUojyNPhsWOk1W/arcgis/rest/services/Parcels_GIS_Hub/FeatureServer/11` Layer 11 is polygon parcels with an **APN** field that matches the CSV APNs, so joining the situs/owner data to parcel geometries is straightforward. This fits right into the existing `source_situs_arcgis` ingestion pattern. 3. **Owner data question → @eliribble** — the mcnav_cd.csv has owner names (Name1/Name2), mailing addresses, percent interest, joint tenancy info, etc. Should this go into a separate `municipal.situs_owner` table (one-to-many with situs, since a parcel can have multiple owners), or should it be part of the situs row itself (many columns, but per-parcel singleton)?

Rows
Columns

Fresno Situs and Tax data #157