Campus visualization

[danrademacher]

Assess a standard approach for visualizing campus buildings in the visualization map

[danrademacher]

This seems like a composite task between data, code, and display.

Some things we need to figure out:

1. How do we know which "buildings" in the data represent campuses?
2. How many campuses are in the data?
3. How do we represent those in the map and on the scorecard?

[danrademacher]

Mike's conceptual presentation: https://seattlegov.sharepoint.com/:p:/r/sites/OSE-External/_layouts/15/Doc.aspx?sourcedoc=%7B7833061B-0C6D-44D0-9B6C-9447EBDDCA8C%7D&file=Campus%20Conceptual%20Diagrams.pptx&action=edit&mobileredirect=true

It basically boils down to this:

1. Implement a "selected state" for all buildings in the application, campus or not. He suggests either an outline of the shape or a "lot line" -- I think the former is a lot more feasible and understandable. In theory we could query for selected buildings as GeoJSON and put them on top of the map, or add another CARTO layer with more refined styling.
2. apply that state to all buildings in a campus -- we'll need to understand how we know which buildings are all on the same campus (maybe as simple as they all have the same OSE ID)
3. move the popup to somewhere else that doesn't obscure the selected building -- I agree with this but it might drag in some bigger redesign questions for the interface.

Seems like we'll need to pull Tim in for some of this!

[tomay]

Moving the popup could be a lot harder than it sounds. This gets into tricky internals related to converting map coordinates to pixel coordinates, and I could see that being very difficult to get right for all scenarios at all zoom levels. In short, it feels very much like something we should not try to improve on. Unless (and I think you alluded to this) we move the "popup" off the map completely, i.e. to a sidebar or something.

For campuses in general I was assuming these would either share the same ID (e.g. single row) or have a new "Campus ID" that we can use to link multiple rows together.

[danrademacher]

Agreed about the popup -- it either stays where it is or becomes a fixed location panel or drawer or something.

For the campus building shapes, I was hoping we would find the following and that the data would already support better behavior once we design what that should be:

1. A single row in a data table like seattle_buildings_2021_update with one set of values for all buildings, including some >1 value for numbuildings
2. Multiple rows in seattle_building_outlines_2021 with the same buildingid, where the count of rows in this table would be equivalent to the value in numbuildings. Essentially, these are child records.

What I actually found is not like that at all:

This query on this data

SELECT id,numbuildings FROM public.seattle_buildings_2021_update where numbuildings > 0 ORDER by numbuildings DESC

Returns:

• Multiple rows with the same OSE ID
• 111 buildings! That is the university of Washington campus, so 111 buildings is probably correct

But then we look at the Building outlines and that ID has only a single record!

And in fact if we run this query:

SELECT buildingid,count(buildingid) FROM public.seattle_building_outlines_2021 group by buildingid order by count(buildingid) DESC

It appears the max number of buildings we have with the same foreign key to the building energy table is 3.

This cmapusissue seems like much more of a data hairball than a deisgn or UI challenge...

[tomay]

Multiple rows with the same OSE id in the benchmark data makes sense because there are multiple years of data. And I'd only expect one corresponding id in the outlines table. Not sure why there's multiples of any ids there

[danrademacher]

Ah multiple years in the energy table makes sense.

For the building outlines, I assumed that an energy table row with 111 buildings might have 111 outlines.

If there's only ever 1 record in the footprints table, would we expect campuses to have multipolygons?

[tomay]

That's what I was thinking. And that's really the simplest approach that fits with the existing setup. But if he wants to support a campus that's comprised of multiple OSE ids, then we'll need some new field to identify them, like campus_id or something

[danrademacher]

Looking at ID 49967, it's the lotline of UW, not one or 111 buildings:

But then 22062 with 39 buildings is Seattle University, and that looks like this:

Just a single building.

[danrademacher]

OK, here's our answer:

Mike and the intern output this XLSX report to show parent buildings, but the really important thing is that there exists a ParentBuildingId field.

This is what Mike says:

One other thing – I added an Excel file to Sharepoint called “Campus Relationships”. This file should hopefully be a bit of a Rosetta Stone for the traditional campus properties (i.e. with Parent-Child relationships in EPA Portfolio Manager) that are the most problematic from the perspective of the viz (e.g. UW, Seattle U, hospitals).

In Portfolio Manager, property owners can create individual “child” building records with their own use attributes (addresses, square footage, lat lot, property type, etc…) and then associate them with “parent” records where use attributes and energy data all roll up. Parent records may or may not actually be logged in our database as “campus” records under the record type field. These parent properties are distinct from the ones where owners create a single EPA PM record for a property and just specify within that there happen to be 5 buildings; each child record in Portfolio Manager/our database has useful information associated with it.

We can walk through this in more detail next check in, but a couple points:

• The ParentBuildingId field of the Child records allows us to see what Parent Record the child is associated with.
• Child Properties may actually be benchmarked independently of the parent record they correspond to. That seems to be the case with those miscellaneous independent UW property records you can see in the viz. In the screen shot below, you can see South Seattle College - Cascade Hall has it’s own EPABuildingID – that would suggest it’s benchmarked independently. (You can also check the “EPAParticipationStatusFlag” to the same effect). While there is a “South Seattle College – Campus Master ID” Record, Cascade Hall is benchmarked independently.
• My hope is that by referencing the Lat/Lon, addresses, and names of the child records, we can pinpoint which building footprints are and are not part of each property benchmarked as a campus, and properly visually represent them.
• The Tabs are pretty self-explanatory, but please feel free to reach out with any questions

Given that this is a database relationship with simple primary and foreign keys, I am not sure why we would resort to using "Lat/Lon, addresses, and names of the child records" to figure out relations. And I wonder if instead of retaining this at the benchmark data level, we should instead push it through to the Building Footprint level, such that when someone clicks a building on the map, we select that building, then check if it has ParentBuildingId and if so, select all the records that have that same ID and load the benchmarking data from teh parent record.

One element that throws me for a loop here is this:

Child Properties may actually be benchmarked independently of the parent record they correspond to.

OK, so what do we do with those? Maybe we have to instead (1) check if a building has benchmarking data and if so load it and ignore other buildings it might be connected to, since it is reported separately. (2) if not, look for a parent ID then load the parent's benchmark data and highlight all outlines with the same parent ID

​We'd also need to rework other parts of the code to color code campus building members based on their parent building, rather than just showing them as if they have no data.

[danrademacher]

Ah, maybe the issue for "Lat/Lon, addresses, and names of the child records" is that we do not have outlines of all of these buildings associated with an existing BuildingId so we can't just join on that.

[tomay]

Trying to wrap my head around some of the implications here...

All of this assumes

• We have outlines and lat/lng for all child buildings and "campuses", ideally in same footprint table as "regular" buildings
• Someone maintains a "child/parent" table over time and this is also uploaded to CARTO with every data update
• Important to note that users can click on the map but also search for a building by "name, address, or ID"

When someone selects or searches for a "regular" building

No change, everything operates as before

When someone selects or searches for a "child" building without benchmarking data

• Map: Child footprint and parent campus footprint, color child same as parent (OR show parent footprint only)?
• Data: Parent benchmarking data

When someone selects or searches for a "child" with benchmarking data

• Map: Child footprint and parent campus footprint, color child independently of parent
• Data: Child benchmarking data

Issues and questions

• Does every campus with a footprint have a buildingid in the main benchmarking sheet? Or are there campuses that we display on the map but that don't have data
• Any search by "name, address or ID" will now have to search and handle results from the "child/parent table" as well as the regular benchmarking data
• For children with benchmarking data: are these already present in the regular benchmarking data table?
• On display of parent benchmark data (for children that don't have their own), do we need to somehow flag/indicate that this is a parent of the searched/clicked child? Or otherwise list children (and if so, how?)

[tomay]

I wonder if there's not an easier way to set this up, by somehow including all child data directly in the "parent" table

• Every child record is included in regular benchmarking data table, with a parentid
• We somehow flag (or infer) which of these are independently benchmarked, and which are not

[danrademacher]

I wonder if there's not an easier way to set this up, by somehow including all child data directly in the "parent" table

* Every child record is included in regular benchmarking data table, with a `parentid`

* We somehow flag (or infer) which of these are independently benchmarked, and which are not

That seems promising.

In the XLSX that Mike sent, we have a field called EPAParticipationStatusFlag that tells us if the building has it own benchmark. As far as I know, that is not in the current data, but seems like it could be added, and then we could use that to know whether to show the building's own data or to show data from the parentid and also highlight on the map all buildings with the same parentid.

For styling, I deconstructed the SQL and CartoCSS produced by the application so we could make an easier styling testbed in the form of a CARTO map: https://cityenergy-seattle.carto.com/builder/406023c2-78bf-434e-ba39-0bfd6a0a08cc/embed

This is simply two copies of the full buildings layer, one with all the buildings as is, and the other selecting only those where parentid is not null. There are not many! As a first pass, I threw on a white dashed line.

Meh. We'll need some help from Tim!

In principle though, I think the task will be to make a new variant of the buildings layers in this file that is limited only to the selected building(s)

[danrademacher]

I updated the CARTO test map to just select Seattle University buidlings, outline them in black and scale back all other buildings to 0.2 opacity

https://cityenergy-seattle.carto.com/builder/406023c2-78bf-434e-ba39-0bfd6a0a08cc/embed

This seems like a solid approach. With real data, these would all have the same energy benchmark data and therefore the same color.

Note that a treatment like this would require a much clearer method to deselect a building, to return map to full opacity.

[danrademacher]

We moved the building outline discussion to #67

[danrademacher]

Notes from data discussion today:

1. The current data structure is sufficient for campuses. So long as we can determine which buildingid to assign to each footrpint, any one Energy Benchmark record can have any number of footprints assigned to it
2. Dariya found this vis of UW buildings, https://depts.washington.edu/ceogis/Public/Buildings/, so we know this data exists and should be obtainable.

However, this raises some other issues about how we know whether "missing" buildings should be assigned to this campus, or not.

To pull the data out of that vis, I used the Network panel to find the data endpoint: https://prod.portal.es.fs.washington.edu/federated/rest/services/Federated/PUBLIC/MapServer/12/query?f=json&where=1=1&returnGeometry=true&spatialRel=esriSpatialRelIntersects&outFields=OBJECTID,FeatureStatus,FeatureStatusDate,FieldStatus,FieldStatusDate,Notes,FacNum,FacCode,FacName,Site,Address,Ownership,YearBuilt,GSF,BaseMap,Underground,RGUID,GlobalID,created_user,created_date,last_edited_user,last_edited_date,SHAPE.STArea(),SHAPE.STLength()&outSR=102100&resultOffset=0&resultRecordCount=100000

That includes f=json which is an annoying Esri JSON format.

But the root of that URL is https://prod.portal.es.fs.washington.edu/federated/rest/services/Federated/PUBLIC/MapServer/12/query and there we can build other queries and get the data. I noticed some records have null geometry, so I wrote this query to exclude those and get GeoJSON back: https://prod.portal.es.fs.washington.edu/federated/rest/services/Federated/PUBLIC/MapServer/12/query?where=SHAPE+IS+NOT+NULL&text=&objectIds=&time=&timeRelation=esriTimeRelationOverlaps&geometry=&geometryType=esriGeometryPolygon&inSR=&spatialRel=esriSpatialRelIntersects&distance=&units=esriSRUnit_Foot&relationParam=&outFields=&returnGeometry=true&returnTrueCurves=false&maxAllowableOffset=&geometryPrecision=&outSR=&havingClause=&returnIdsOnly=false&returnCountOnly=false&orderByFields=&groupByFieldsForStatistics=&outStatistics=&returnZ=false&returnM=false&gdbVersion=&historicMoment=&returnDistinctValues=false&resultOffset=&resultRecordCount=&returnExtentOnly=false&sqlFormat=none&datumTransformation=&parameterValues=&rangeValues=&quantizationParameters=&featureEncoding=esriDefault&f=geojson

I couldn't resist pulling the data to compare to what we have in the system:

https://cityenergy-seattle.carto.com/builder/406023c2-78bf-434e-ba39-0bfd6a0a08cc/embed

So then do we add ONLY the gray buildings inside the blue line? or are there other buildings nearby also part of this campus and should they be included?

[danrademacher]

Ok, so for UW campus assignments, the answer is we are waiting on UW to provide a data file that rolls their buildings up to campus. Mike is communicating with them about this.

For the rest, one through I had was to look at the Rosetta Stone Excel file, filter out the UW main campus (Parent Building 49967) and then see how many parents there are.

The answer is 68:

3
43
55
57
63
89
107
147
172
187
198
211
257
264
268
276
315
345
364
432
474
477
507
565
616
640
657
688
757
813
828
829
19564
19708
20432
21150
21399
22062
22950
23071
23113
23259
23265
23311
23622
24086
25251
27703
27799
28733
29214
49705
49825
49909
50092
50259
50271
50294
50300
50352
50376
50383
50416
50452
50642
50652
50656
50710

[danrademacher]

Deliverable report on campus data issue: https://docs.google.com/document/d/1MZa6b85UFjBGDriaMKyoHelcwWm2JHJPoL3026Bkbl0/edit

[danrademacher]

There's some additional data work coming on this task related to UW cmapus, but I am going to close this out and file that as a separate ticket.