building footprint update 2022

[tomay]

We have a list of new buildings and old deprecated buildings, and we need building footprints that match

1. Use overpass turbo to get the current set of building footprints. For that I used this query:

   
   [out:json];

area[name="Washington"]->.b; rel(area.b)[name="Seattle"]; map_to_area -> .a;

way"building"; out geom;

relation"building";out; way(r)[!"building:part"]; out geom;

This was a large result (~200 MB), and I wonder if there's a more direct route for such a large download, bypassing the map/website? 

2. Still within overpass, download/export as GeoJSON

3. Load into QGIS. 
This works, but it's about 200,000 features, and includes all footprints. Looking into how we can differentiate commercial buildings from the rest.

┆Issue is synchronized with this [Asana task](https://app.asana.com/0/1201108652060192/1203423628604901)

[danrademacher]

You can also hit the Overpass API directly, at http://overpass-api.de/, at CLI. You'd just have to see what sort of request payload it requires. OP Turbo is just a GUI in front of that API as far as I know.

[tomay]

I was able to match 55 of 58 building lat-lngs to the footprints from overpass. The following do not match:

1. 602 E HOWELL ST (47.61784, -122.32439)

Here's a comparison to this block on CARTO:

And Google Maps/Satellite

Not easy to see what's going on, but seems likely we need to grab the one footprint closest to the point

2. 420 BOYLSTON AVE E (47.62267, -122.32278)

Google Satellite view:

This looks like a new building that we will have to digitize

3. 1157 N 130TH ST (47.72299, -122.34293)

Google Streets view:

Google Sat view:

Not sure what to make of this one. There doesn't seem to be anything here.

The lat/lng maps here:

Could it be the temporary fire station?

[tomay]

Getting ready to merge the new 58 buildings into the existing building footprint data from Carto called "seattle_building_outlines_20181126", I'm noting a number of instances where we've found a new footprint that matches an old footprint exactly, e.g. the one here at lower right:

New

Old

Seems like we'd want to delete those prior to merge, but also makes me wonder about the original process for identifying the 58 missing footprints

I think Ill go through them all and flag duplicates for discussion

Update

I've gone through all 58 new footprints, and flagged 12 that overlap an existing footprint. 8 of these seem to be exact duplicates, and the other 4 look like modifications or replacements of an existing footprint. See additional notes below under "rectification/clarification"

[tomay]

Buildings to remove

1. Select the following by OSEBuildingID:

   SELECT * FROM public.seattle_buildings_master_copy_for_update2020
   where id in ('244','21417','21530','23008','23022','23418','23888','24454','25025','25092','25839','27205','50069','441','611','19845','20376','50068','50681') order by id ASC

2. Export as CSV, and reduce these to a single record for each row, with id,lat,long

id	latitude	longitude
244	47.60105	-122.33518
441	47.62036	-122.34588
611	47.59856	-122.33379
19845	47.66460037	-122.2997208
20376	47.57405	-122.30301
21417	47.62569	-122.33146
21530	47.62007385	-122.3454149
23008	47.70983	-122.31865
23022	47.70519257	-122.3224258
23418	47.59725918	-122.3220338
23888	47.57290555	-122.3864134
24454	47.59191	-122.3266
25025	47.5867	-122.30519
25092	47.70625	-122.3424
25839	47.55317	-122.33289
27205	47.70614609	-122.3612496
50068	47.54425	-122.31776
50069	47.53161	-122.29944
50681	47.66964	-122.37918

3. In QGIS, import as X,Y data, and spatial join on seattle_building_outlines_20181126to identify buildings to remove

Results

All but two match. This one is clear - same ID in point and footprint, just falls a bit outside the footprint:

This one, however, has no footprint remotely in the vicinity of the mapped location. It's at the Airport, so I guess this is one that was simply missing previously

id	latitude	longitude
50069	47.53161	-122.29944

[tomay]

Buildings needing rectification or clarification

NW ENVIROSERVICE/EVERGREEN SVCS

OSEBuildingID	Address	Latitude	Longitude
50761	1700 AIRPORT WAY S	47.58802	-122.32111

There is an existing and identical footprint here with ID 23867. It looks like these two records refer to the same building.

820 SOUTH ADAMS BUILDING

OSEBuildingID	Address	Latitude	Longitude
50760	820 S ADAMS ST	47.5668	-122.32149

There is an existing and identical footprint here with ID 24132. It looks like these two records refer to the same building.

NORTHCUT LANDING

OSEBuildingID	Address	Latitude	Longitude
50767	4915 25TH AVE NE	47.66517	-122.30186

There is an existing and identical footprint here with ID 565. It looks like these two records refer to the same building.

Buildings with multiple footprints over time

The following have multiple IDs and footprints. They look like rebuilds on the same site. We'll keep both footprints and both IDs

LICTON SPRINGS K-8

OSEBuildingID	Address	Latitude	Longitude
50781	3015 NW 68TH ST	47.67767	-122.39631

Older existing building/footprint ID is 228 (Webster Building).

KING COUNTY YOUTH SERVICES - CAMPUS

OSEBuildingID	Address	Latitude	Longitude
50689	1211 E ALDER ST	47.60462	-122.31549

Older existing building/footprint ID is 187

[tomay]

Testing new complete footprint dataset against the latest complete data update by building ID

• All buildings have a matching footprint
• Every footprint has a matching building, except for the following IDs, which we can probably delete, or leave, since they will never match a building, and are harmless

buildingid
-1
0

[tomay]

All updated in CARTO as a new table called seattle_building_outlines_2021

Here's the Licton Springs example from above, showing overlapping footprints from different years, different rows in the data:

2017

2021