Capital Projects Historical Spending

Agree internally and with capital planning on target schema

Proposed version below. We'll need to finesse the exact field names:

• FMS ID, Check Amount, Contract Purpose, Budget Code Agency, bc_category, cp_category, cpdb_category, final_category, {probably ending up with one category column but I'd be interested in knowing the variation so would advocate to keep all 4 in the interim), geomsource, geometry

Add in spatial data from other sources

CPDB spatial data is assigned via a waterfall (i.e. if we get data from DDC do not take spatial data from Parks Properties). I propose we do the data for historical spending with the initial hierarchy being the following:

1. From CPDB <-- done. This is a join between FMS ID and maprojid
2. From DPR park properties - See 3 scripts with attributes_dpr here to see how it's done for CPDB (forms of fuzzy sting matching for )
3. From Facilities database (forms of fuzzy sting matching)
4. Geocoding - can they extract an address, intersection, street segment, or place name that can be passed through GeoSupport to get a geometry (enhancement - this does not exist in CPDB and if this is successful then this may supersede DPR and FacDB as a source for geometries)

Improve categorization

1. Add key words to the lookup list to reduce the number of projects not assigned to a category

Start by reading the three attributes_dpr scripts in db-cpdb/sql and attributes_maprojid_facilities.sql, then trace how the existing CPDB join uses FMS ID and maprojid. The work is done when the target schema, spatial-source waterfall, geocoding approach, and expanded categorization lookup are agreed and implemented for historical spending.