UW campus footprint work

[danrademacher]

As part of our Campus data completion, client wants us to at least get the UW main campus building outlintes sorted.

So we would replace this giant shape:

With all the child buildings inside that shape.

Mike says that UW will supply a shapefile of the related buildings before Thanskgiving. We shall see!

This is building id 49967 so in theory, if the SHP really does include only footprints on that one campus, we could just export them all as individual footprint records with the same Dashboard ID, though we'd also need to remove the dozen or so buildings that have their own scores already:

[danrademacher]

Mike sent us a UW shapefile: https://drive.google.com/open?id=1MPf1lxNOZwyP6gFS1jVeSgu4nq22Do7k&usp=drive_fs

This includes data that is outside the above blue trapezoid, but it is only the main campus, so I think it should be susceptible to a bulk update solution

In concept, we would want to do the following:

1. Load the UW shapefile and default to including all outlines in the SHP with Dashboard ID 49967
2. Remove from this new layer any building that already exists in the building footprint table -- this will have to be by some spatial comparison, since I don't think there are IDs or other fields to match.
3. Add remaining new UW shapes to the main footprints table

[danrademacher]

Huh, this just in from Mike:

One quick note – within the last couple months, a bunch of those miscellaneous standalone UW buildings were brought under the main campus record in our database – as represented in the latest campus parent-child crosswalk Excel file. In other words, in the latest benchmarking data pull provided for 2022, I don’t think those seemingly random standalones will be there anymore. Some UW buildings legitimately should stand apart from the campus benchmarking record, but we need to make sure that we’re capturing the current relationship.

Oof. I am not sure how we will parse this, to be honest. I keep hoping we can avoid slogging through the Excel file, but maybe not.

So we have 132 child buildings with the UW parent ID, as shown in SharePoint.

Meanwhile, the shapefile from UW has 351 features in it. So 219 buildings that maybe have their own records, but we'd have a hard time figuring that out.

On a quick scan, the NAME field is not consistently similar between the two files. The ADDRESS field looks more promising, though there will be cases, possibly many cases, where multiple buildings in either source share the same address.

[danrademacher]

Discussing with Mike on 11/27:

• There will definitely be buildings in the UW shapefile, like a hospital campus, that should not be part of the main campus
• Start with address matching and see what's left

[danrademacher]

All the addresses for Seattle buildings are here: https://data-seattlecitygis.opendata.arcgis.com/datasets/SeattleCityGIS::addresses-maf/explore?location=47.654016%2C-122.306944%2C16.00

[danrademacher]

Split the task:

• Look first inside the main campus shape and make sure that is al accounted for
• Only then look at the buildings outside the main campus shape

[danrademacher]

OK, I jumped in here to make some progress on this since Dariya is out sick. Here's what I did:

1. Loaded seattle_building_outlines_2023_updated.shp
2. Loaded UWSeattleBuildings.shp
3. Used a polygon select to select all UW buildings inside the main UW campus. Dumb but easy
4. Exported those to a new layer, seattle_uw_main_campus_core.shp. Included all attributes for now.
5. In that new layer, added fields for buildingid and source
6. Set all buildingid to be 49967
7. Concatenated values for source so they all read like this UW GIS data FacNum 1128 FacCode PPG FacName Padelford Parking Garage pulling data from the parent file in case we need it in future
8. Exported a new file that includes only buildingid and source, seattle_uw_main_campus_core_finalfields.shp
9. Copied and pasted everything in that file into seattle_building_outlines_2023_updated.shp in QGIS
10. Deleted the large trapezoid that was 49967

I think the last piece is that there are some buildings that are now stacked on top of each other, like this:

These should be buildings that historically have been reported separately from the main campus, but Mike says they no longer will be. So ideally, I would just delete any footprint within the main campus that is not buildingid 49967 but I remain concerned that Mike may not be correct that all these footprints are in fact subumed under the main campus

[danrademacher]

That's 11 building footprints

So a check for these would be to see if they show up in the latest data.

buildingid | Link -- | -- 49716 | https://greeninfo-network.github.io/seattle-building-dashboard/#seattle/2022?layer=total_ghg_emissions&sort=total_ghg_emissions&order=desc&lat=47.65547&lng=-122.31182&zoom=14&building=49716 49975 | https://greeninfo-network.github.io/seattle-building-dashboard/#seattle/2022?layer=total_ghg_emissions&sort=total_ghg_emissions&order=desc&lat=47.65547&lng=-122.31182&zoom=14&building=49975 49983 | https://greeninfo-network.github.io/seattle-building-dashboard/#seattle/2022?layer=total_ghg_emissions&sort=total_ghg_emissions&order=desc&lat=47.65547&lng=-122.31182&zoom=14&building=49983 49971 | https://greeninfo-network.github.io/seattle-building-dashboard/#seattle/2022?layer=total_ghg_emissions&sort=total_ghg_emissions&order=desc&lat=47.65547&lng=-122.31182&zoom=14&building=49971 49969 | https://greeninfo-network.github.io/seattle-building-dashboard/#seattle/2022?layer=total_ghg_emissions&sort=total_ghg_emissions&order=desc&lat=47.65547&lng=-122.31182&zoom=14&building=49969 49984 | https://greeninfo-network.github.io/seattle-building-dashboard/#seattle/2022?layer=total_ghg_emissions&sort=total_ghg_emissions&order=desc&lat=47.65547&lng=-122.31182&zoom=14&building=49984 49968 | https://greeninfo-network.github.io/seattle-building-dashboard/#seattle/2022?layer=total_ghg_emissions&sort=total_ghg_emissions&order=desc&lat=47.65547&lng=-122.31182&zoom=14&building=49968 49981 | https://greeninfo-network.github.io/seattle-building-dashboard/#seattle/2022?layer=total_ghg_emissions&sort=total_ghg_emissions&order=desc&lat=47.65547&lng=-122.31182&zoom=14&building=49981 49972 | https://greeninfo-network.github.io/seattle-building-dashboard/#seattle/2022?layer=total_ghg_emissions&sort=total_ghg_emissions&order=desc&lat=47.65547&lng=-122.31182&zoom=14&building=49972 49977 | https://greeninfo-network.github.io/seattle-building-dashboard/#seattle/2022?layer=total_ghg_emissions&sort=total_ghg_emissions&order=desc&lat=47.65547&lng=-122.31182&zoom=14&building=49977 29390 | https://greeninfo-network.github.io/seattle-building-dashboard/#seattle/2022?layer=total_ghg_emissions&sort=total_ghg_emissions&order=desc&lat=47.65547&lng=-122.31182&zoom=14&building=29390

[danrademacher]

These IDs have buildings in the current data:

49975 https://greeninfo-network.github.io/seattle-building-dashboard/#seattle/2022?layer=total_ghg_emissions&sort=total_ghg_emissions&order=desc&lat=47.65547&lng=-122.31182&zoom=14&building=49975

49969 https://greeninfo-network.github.io/seattle-building-dashboard/#seattle/2022?layer=total_ghg_emissions&sort=total_ghg_emissions&order=desc&lat=47.65420&lng=-122.30942&zoom=14&building=49969

49981 https://greeninfo-network.github.io/seattle-building-dashboard/#seattle/2022?layer=total_ghg_emissions&sort=total_ghg_emissions&order=desc&lat=47.65264&lng=-122.30504&zoom=14&building=49981

29390 https://greeninfo-network.github.io/seattle-building-dashboard/#seattle/2022?layer=total_ghg_emissions&sort=total_ghg_emissions&order=desc&lat=47.65570&lng=-122.31242&zoom=14&building=29390

These are those buildings:

[danrademacher]

Maike asked me to send him those 4 building ids so he can check them. He already found that 49969 should not be exporting from upstream. If the others are that way too, then we should be able to complete this core task very quickly. He's fine letting go of the rest of the "non-core" buildings for this phase.

[danrademacher]

Mike says the only one that should stay separate is this housing building: https://greeninfo-network.github.io/seattle-building-dashboard/#seattle/2022?layer=total_ghg_emissions&sort=total_ghg_emissions&order=desc&lat=47.65570&lng=-122.31242&zoom=14&building=29390

So we can delete these ten footprints, and then this task should be complete and ready to load into CARTO:

49716 
49975 
49983 
49971 
49969 
49984 
49968 
49981 
49972 
49977 

[danrademacher]

Looks good running locally:

Confusing why UW is out of compliance, but I hope that will be solved by the final data update from Mike

[danrademacher]

Unsaved notes: I deleted the 10 we needed to delete and saved all data back to P with a little reorganization.

Most current footprints are here: P:\proj_p_s\Seattle Building Dashboard\2023 update\seattle_building_outlines_2023_updated_20231204

I uploaded to CARTO, though I had to delete the cartodb_id field to do so, since QGIS saved it as a float (and it actually doesn't matter if those get reassigned every time we upload). The copy on P still has the cartodb_id

It's on CARTO here: https://cityenergy-seattle.carto.com/dataset/seattle_building_outlines_2023_1204

[danrademacher]

Huh, somehow I ended up with overlapping buildings, which I saw when Tom updated the data from Mike.

The one on top here should have been deleted:

And now it is gone from P and CARTO.

[danrademacher]

Email to Mike:

I discovered an issue where one buildings are still stacked and likely should be part of the main campus: 49973, UW - FOEGE BUILDING (genome sciences) That one causes a problem where it is stacked on top of the main campus record, since they have the same street address.

Can you confirm if that is included in the main campus record?

If so, I will delete the record from CARTO for both the energy data and footprints, and you could delete upstream.

At that point, Phase 2 will be complete.

[tomay]

In addition to Foege (above) we have similar issues for two additional buildings on UW Main campus: Hitchcock Hall and Molecular Engineering & Sciences Building

To fix, using a table of UW Seattle Buildings, here is the SQL applied to the table seattle_building_outlines_2023_1204:

-- FOEGE
-- ADD CORRECT FP FOR 2022 AS PART OF UW MAIN
INSERT INTO public.seattle_building_outlines_2023_1204 SELECT 4779 AS cartodb_id, the_geom, null, null, null from uwseattlebuildings where FacName = 'William H. Foege Genome Sciences'
UPDATE public.seattle_building_outlines_2023_1204 SET source = 'UW GIS data FacNum 4058 FacCode GNOM FacName William H. Foege Genome Sciences' WHERE cartodb_id = 4779

-- HITCHCOCK
-- ADD CORRECT FP FOR 2022 AS PART OF UW MAIN
-- ADD DUPLICATE FOR 2017-2022 STANDALONE BENCHMARK DATA
INSERT INTO public.seattle_building_outlines_2023_1204 SELECT 4780 AS cartodb_id, the_geom, null, null, null from uwseattlebuildings where FacName = 'Hitchcock Hall'
UPDATE public.seattle_building_outlines_2023_1204 SET buildingid = 49977, source = 'UW GIS data FacNum 1324 FacCode HCK FacName Hitchcock Hall' WHERE cartodb_id = 4781
INSERT INTO public.seattle_building_outlines_2023_1204 SELECT 4781 AS cartodb_id, the_geom, null, null, null from uwseattlebuildings where FacName = 'Hitchcock Hall'
UPDATE public.seattle_building_outlines_2023_1204 SET buildingid = 49967, source = 'UW GIS data FacNum 1324 FacCode HCK FacName Hitchcock Hall' WHERE cartodb_id = 4780

-- MOL
-- DELETE EXISTING FOOTPRINT AND 2022 BENCHMARK DATA
-- ADD CORRECT FP FOR 2022
-- ADD DUPLICATE FP FOR 2017-2022 STANDALONE BENCHMARK DATA
DELETE FROM public.seattle_building_outlines_2023_1204 WHERE buildingid = 41927
INSERT INTO public.seattle_building_outlines_2023_1204 SELECT 4782 AS cartodb_id, the_geom, null, null, null from uwseattlebuildings where FacName = 'Molecular Engineering & Sciences Building'
UPDATE public.seattle_building_outlines_2023_1204 SET buildingid = 49967, source = 'UW GIS data FacNum 6105 FacCode MOL Molecular Engineering & Sciences Building' WHERE cartodb_id = 4782
INSERT INTO public.seattle_building_outlines_2023_1204 SELECT 4783 AS cartodb_id, the_geom, null, null, null from uwseattlebuildings where FacName = 'Molecular Engineering & Sciences Building'
UPDATE public.seattle_building_outlines_2023_1204 SET buildingid = 41927, source = 'UW GIS data FacNum 6105 FacCode MOL Molecular Engineering & Sciences Building' WHERE cartodb_id = 4783

I also fixed the address and lat/lngs for Foege and Hitchcock in the benchmarking data and reuploaded that to CARTO

[tomay]

Copy of the latest (final?) building footprint table on CARTO here:

https://drive.google.com/file/d/1NTLfzJOfuhJw8jz8lXklY3YbAt5p7a8j/view?usp=drive_link

[tomay]

Note I've renamed the latest footprint table on CARTO to seattle_building_outlines_2022 https://cityenergy-seattle.carto.com/dataset/seattle_building_outlines_2022

To be consistent with the names for prior data releases

[tomay]

That change is done in seattle.json and committed and deployed. With that I believe we're done with footprints and campuses for this round

[tomay]

There is still a row for UW - FOEGE BUILDING (genome sciences) in the 2022 data, so we need to delete that: DELETE FROM public.seattle_buildings_2022_update where property_name = 'UW - FOEGE BUILDING (genome sciences)' and year = 2022

Also, the footprint for this should be comprised of two buildings from the UW Campus Data, not the one genome sciences. We already have a shape for this on board that we can reuse:

DELETE FROM public.seattle_building_outlines_2022 where cartodb_id = 4779
INSERT INTO public.seattle_building_outlines_2022 SELECT 4784 AS cartodb_id, the_geom, null, null, null FROM public.seattle_building_outlines_2022 WHERE buildingid=49982;
UPDATE public.seattle_building_outlines_2022 SET buildingid = 49967 where cartodb_id = 4784

Done: