Prototype 2023 data with new schema

[tomay]

To explore and document

• [x] Process for joining this to old data - possible with new fields? Might require a query to add/rename/match same field names to 2022 data. Process doc updated, also see below for some steps that should only need to be performed one time
• [x] Run through the app as a new table

┆Issue is synchronized with this Asana task

[tomay]

The process for joining the old and new data is a little tricky, as there are a number of new fields in the new data, and some old fields have been dropped. This is all documented in this sheet.

Do this before running the INSERT in the next comment

--- Add fields
ALTER TABLE public.benchmarking_production_copy ADD COLUMN propertygfatotal numeric
ALTER TABLE public.benchmarking_production_copy ADD COLUMN propertygfabuildings numeric
ALTER TABLE public.benchmarking_production_copy ADD COLUMN propertygfaparking numeric
ALTER TABLE public.benchmarking_production_copy ADD COLUMN selfreportgfatotal numeric
ALTER TABLE public.benchmarking_production_copy ADD COLUMN selfreportgfabuildings numeric
ALTER TABLE public.benchmarking_production_copy ADD COLUMN selfreportparking numeric
ALTER TABLE public.benchmarking_production_copy ADD COLUMN epaparticipationstatusflag char(10)
ALTER TABLE public.benchmarking_production_copy ADD COLUMN primarypropertytype char(255)
ALTER TABLE public.benchmarking_production_copy ADD COLUMN iscompliantflag char(10)
ALTER TABLE public.benchmarking_production_copy ADD COLUMN bepstarget_2031 float(2)
ALTER TABLE public.benchmarking_production_copy ADD COLUMN bepstarget_2036  float(2)
ALTER TABLE public.benchmarking_production_copy ADD COLUMN bepstarget_2041 float(2)
ALTER TABLE public.benchmarking_production_copy ADD COLUMN bepstarget_2046 float(2)
ALTER TABLE public.benchmarking_production_copy ADD COLUMN cbpseuitarget float(2)
ALTER TABLE public.benchmarking_production_copy ADD COLUMN beps_firstcomplianceyear int
--- Drop fields
ALTER TABLE public.benchmarking_production_copy DROP COLUMN parentid
ALTER TABLE public.benchmarking_production_copy DROP COLUMN cbps_euit

-- For pre-2023 data only, set new Gross Floor Area measure `propertygfabuildings` equal to `reported_gross_floor_area`, then drop the old field so historic data continues to report GFA 
UPDATE public.benchmarking_production_copy SET propertygfabuildings = reported_gross_floor_area
ALTER TABLE public.benchmarking_production_copy DROP COLUMN reported_gross_floor_area

[tomay]

Then delete

--- probably a noop at the moment since 2023 data doesn't yet exist here
DELETE from benchmarking_production_copy where year = 2023

...and update with the following:

INSERT INTO benchmarking_production_copy (the_geom, property_type, id, xepaid, year, property_name, reported_address, city, state, zip, latitude, longitude, neighborhood, councildistrict, yearbuilt, numfloors, numbuildings, energy_star_score, site_eui_wn, site_eui, total_kbtu, source_eui_wn, source_eui, electricity, steam, gas, epapropertytype, total_ghg_emissions, total_ghg_emissions_intensity, building_type_eui, building_type_eui_wn, electricity_pct, gas_pct, steam_pct, electricity_ghg, gas_ghg, steam_ghg, electricity_ghg_percent, gas_ghg_percent, steam_ghg_percent, cos_median_eui, percent_from_median, higher_or_lower, numunits, yearbuilt_string, ess_cert, cost_annual, cost_sq_ft, percent_save, amount_save, comments, pct_sum, cbps_date, cbps_flag, uniqueid, propertygfatotal, propertygfabuildings, propertygfaparking, selfreportgfatotal, selfreportgfabuildings, selfreportparking, epaparticipationstatusflag, primarypropertytype, iscompliantflag, bepstarget_2031, bepstarget_2036, bepstarget_2041, bepstarget_2046, cbpseuitarget, beps_firstcomplianceyear)
SELECT the_geom, property_type, id, xepaid, year, property_name, reported_address, city, state, zip, latitude, longitude, neighborhood, councildistrict, yearbuilt, numfloors, numbuildings, energy_star_score, site_eui_wn, site_eui, total_kbtu, source_eui_wn, source_eui, electricity, steam, gas, epapropertytype, total_ghg_emissions, total_ghg_emissions_intensity, building_type_eui, building_type_eui_wn, electricity_pct, gas_pct, steam_pct, electricity_ghg, gas_ghg, steam_ghg, electricity_ghg_percent, gas_ghg_percent, steam_ghg_percent, cos_median_eui, percent_from_median, higher_or_lower, numunits, yearbuilt_string, ess_cert, cost_annual, cost_sq_ft, percent_save, amount_save, comments, pct_sum, cbps_date, cbps_flag, uniqueid, propertygfatotal, propertygfabuildings, propertygfaparking, selfreportgfatotal, selfreportgfabuildings, selfreportparking, epaparticipationstatusflag, primarypropertytype, iscompliantflag, bepstarget_2031, bepstarget_2036, bepstarget_2041, bepstarget_2046, cbpseuitarget, beps_firstcomplianceyear
FROM carto_2023_test

[tomay]

Notes and questions

Found instances of "NA" in the following fields

• cbps_date
• BEPS_FirstComplianceYear

reported_gross_floor_area is required for the charts, popups and building report

• Replace with propertygfatotal in seattle.json?

[tomay]

Update from the client:

I think we should link to PropertyGFABuildings, which does not include parking GFA.

I've reverted the changes to seatte.json in fde72891fb706b811a896400aab466b08efbff14. But after we get the green light, go ahead with the above changes, with the following addition:

• Replace reported_gross_floor_area with PropertyGFABuildings: Before the join with 2023, set this new field equal to that old field on the 2022 and prior data.
• I've update notes above to reflect this

[tomay]

Additional fields for the above one-time queries, as well as for the update docs

• ghg_emissions_intensity_elec
• ghg_emissions_intensity_gas
• ghg_emissions_intensity_steam
• totalghgintensity

Question for the client: How do these relate to the existing fields for electic, gas, steam named:

• electricity_ghg
• electricity_ghg_percent
• electricity_pct

If any of these are replacements, then we'll have to "backfill" pre-2022 data similar to what we do above for square footage.

But I don't think they are as these "old" fields are also present in the latest same data.