CBL Workflow

Covered Building List Workflow

Given a list of addresses, this workflow will automatically:

• Normalize each address
• Geocode each address via MapQuest to a lat/long coordinate
• Download the Microsoft Building Footprints for all areas encompassed by the geocoded coordinates
• Find the footprint that intersects (or is closest to) each geocoded coordinate
• Generate the UBID for each footprint
• Export the resulting data as csv and GeoJSON

Prerequisites

1. Optionally create a Virtualenv Environment
2. Dependencies are managed through Poetry, install with pip install poetry
3. Install dependencies with poetry install
4. Create a .env file in the root with your MapQuest API key in the format:

   MAPQUEST_API_KEY=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

   Note that if an env key for MAPQUEST_API_KEY exists in your profile, then it use that over the .env file.
5. Create a locations.json file in the root containing a list of addresses to process in the format:

   [
     {
       "street": "100 W 14th Ave Pkwy",
       "city": "Denver",
       "state": "CO"
     },
     {
       "street": "200 E Colfax Ave",
       "city": "Denver",
       "state": "CO"
     },
     {
       "street": "320 W Colfax Ave",
       "city": "Denver",
       "state": "CO"
     }
   ]

Running the Workflow

1. Run the workflow with python main.py or poetry run python main.py
2. The results will be saved to ./data/covered-buildings.csv and ./data/covered-buildings.geojson:
   1. e.g. covered-buildings.csv

      address,city,state,postal_code,side_of_street,neighborhood,county,country,latitude,longitude,quality,footprint_match,geometry,height,ubid
      100 W 14th Avenue Pkwy,Denver,CO,80204-2713,L,Civic Center,Denver,US,39.73777,-104.9896,P1AAA,closest,"POLYGON ((-104.9894511199044 39.7358944427259, -104.98900500638051 39.735501743726054, -104.98905480013478 39.735948362728976, -104.98884907624438 39.73587211865194, -104.98894270685662 39.736711235106, -104.98903619755599 39.73674588362176, -104.98910752339232 39.7366979676499, -104.9891382133996 39.7369730089668, -104.98903049000751 39.73714489200102, -104.98890179053156 39.7371533771491, -104.98885218764076 39.737232523178854, -104.98921230158399 39.7373659836236, -104.98904812441526 39.73747627505782, -104.98905988488102 39.73758175635951, -104.98938642691502 39.73770277424566, -104.98959556874871 39.73756227674395, -104.98962111739336 39.73779123879773, -104.98984261420652 39.7379862089293, -104.99008597096511 39.737822727137555, -104.99004184913788 39.73742731548642, -104.98982662939532 39.73723786914854, -104.9900166288242 39.737225332287714, -104.99000299062335 39.73710310843809, -104.9897357241851 39.73700404311292, -104.98966716413469 39.73705010085398, -104.9896828843468 39.7371909835172, -104.98965348979158 39.73723788525179, -104.98953077030399 39.737245976128726, -104.98945207219327 39.7365406907357, -104.98955465788889 39.73637700396526, -104.98938487977068 39.736314082278604, -104.98948083567923 39.73616097357164, -104.9894511199044 39.7358944427259))",20.939537048339844,85FQP2P6+Q658F5W-27601-4406-34511-5727
      200 E Colfax Ave,Denver,CO,80203-1716,L,Capitol Hill,Denver,US,39.73988,-104.98457,P1AAA,closest,"POLYGON ((-104.98452887354458 39.738819475962735, -104.98463382014751 39.73913852552085, -104.98442432057398 39.7391011939329, -104.98440529554036 39.73916434408205, -104.98438369382713 39.73916854680096, -104.98434367971852 39.73930132606697, -104.98440557600038 39.73931235558928, -104.98441566847005 39.739343029197364, -104.98462710898747 39.73938070651776, -104.98458877780907 39.73950793933834, -104.98452637039966 39.73949681876791, -104.98456988287018 39.73962910048519, -104.98472798048233 39.73965726382443, -104.9847769453537 39.73971414973129, -104.9851293416878 39.73964560877795, -104.98516946906118 39.73951241410107, -104.98509465118224 39.73949908207113, -104.98504733523438 39.7393552769577, -104.9852336333444 39.73938846403406, -104.98525528286132 39.739316602617635, -104.98533054137296 39.739301960736626, -104.98536811097296 39.739177293119575, -104.98525411848435 39.73915698647953, -104.98523148185932 39.73908816871919, -104.98506204352081 39.73912113380581, -104.98513870161791 39.73886668064069, -104.98512133828469 39.7388139086371, -104.98498510152693 39.73884040696267, -104.9849136277818 39.73875736985842, -104.98475860487412 39.73878752215217, -104.98476406145178 39.738804106221444, -104.9846807173819 39.7388465255792, -104.98452887354458 39.738819475962735))",19.991971969604492,85FQP2Q8+M3RQ2QV-11834-4147-12085-4245
      320 W Colfax Ave,Denver,CO,80204-2605,R,Civic Center,Denver,US,39.73924,-104.99231,P1AAA,intersection,"POLYGON ((-104.99199815071022 39.739882920659866, -104.99264715821575 39.73988871877881, -104.9926499934665 39.739699928205745, -104.99267949383152 39.73970019016987, -104.99267896733426 39.73973524803285, -104.99281842360514 39.739736486408056, -104.99283071886506 39.73891777755595, -104.99271003555369 39.738916705872356, -104.99271242027665 39.73875791234675, -104.99203927558436 39.73875193471979, -104.99203890393925 39.738776681788934, -104.99192626618196 39.738775681548894, -104.99192459377885 39.738887043251786, -104.99197018525203 39.7388874481102, -104.99196372701348 39.739317486542454, -104.99182158900608 39.739316216696, -104.99181909667605 39.739482174189305, -104.99193977998743 39.73948324586409, -104.99194170015396 39.739355387221934, -104.99200606458669 39.73935595878287, -104.99199815071022 39.739882920659866))",13.208232879638672,85FQP2Q5+P3C6R9C-14485-4541-13934-3746
      

   2. e.g. covered-buildings.geojson

      {
      "type": "FeatureCollection",
      "features": [
      { "type": "Feature", "properties": { "address": "100 W 14th Avenue Pkwy", "city": "Denver", "state": "CO", "postal_code": "80204-2713", "side_of_street": "L", "neighborhood": "Civic Center", "county": "Denver", "country": "US", "latitude": 39.73777, "longitude": -104.9896, "quality": "P1AAA", "footprint_match": "closest", "height": 20.939537048339844, "ubid": "85FQP2P6+Q658F5W-27601-4406-34511-5727" }, "geometry": { "type": "Polygon", "coordinates": [ [ [ -104.989451119904402, 39.7358944427259 ], [ -104.989005006380509, 39.735501743726054 ], [ -104.989054800134781, 39.735948362728976 ], [ -104.988849076244378, 39.73587211865194 ], [ -104.988942706856619, 39.736711235106 ], [ -104.98903619755599, 39.736745883621758 ], [ -104.98910752339232, 39.736697967649903 ], [ -104.989138213399599, 39.7369730089668 ], [ -104.989030490007508, 39.73714489200102 ], [ -104.988901790531557, 39.737153377149099 ], [ -104.988852187640759, 39.737232523178854 ], [ -104.98921230158399, 39.737365983623597 ], [ -104.989048124415262, 39.737476275057823 ], [ -104.989059884881016, 39.737581756359511 ], [ -104.989386426915019, 39.737702774245662 ], [ -104.989595568748712, 39.737562276743951 ], [ -104.989621117393355, 39.737791238797733 ], [ -104.989842614206523, 39.7379862089293 ], [ -104.990085970965112, 39.737822727137555 ], [ -104.990041849137882, 39.737427315486421 ], [ -104.989826629395324, 39.737237869148537 ], [ -104.990016628824193, 39.737225332287714 ], [ -104.990002990623353, 39.737103108438092 ], [ -104.989735724185095, 39.737004043112918 ], [ -104.989667164134687, 39.737050100853978 ], [ -104.989682884346806, 39.737190983517202 ], [ -104.989653489791579, 39.737237885251787 ], [ -104.989530770303986, 39.737245976128726 ], [ -104.989452072193274, 39.736540690735701 ], [ -104.989554657888888, 39.736377003965259 ], [ -104.989384879770682, 39.736314082278604 ], [ -104.989480835679231, 39.736160973571643 ], [ -104.989451119904402, 39.7358944427259 ] ] ] } },
      { "type": "Feature", "properties": { "address": "200 E Colfax Ave", "city": "Denver", "state": "CO", "postal_code": "80203-1716", "side_of_street": "L", "neighborhood": "Capitol Hill", "county": "Denver", "country": "US", "latitude": 39.73988, "longitude": -104.98457, "quality": "P1AAA", "footprint_match": "closest", "height": 19.991971969604492, "ubid": "85FQP2Q8+M3RQ2QV-11834-4147-12085-4245" }, "geometry": { "type": "Polygon", "coordinates": [ [ [ -104.984528873544576, 39.738819475962735 ], [ -104.984633820147508, 39.73913852552085 ], [ -104.984424320573979, 39.739101193932903 ], [ -104.984405295540355, 39.739164344082049 ], [ -104.984383693827127, 39.739168546800961 ], [ -104.984343679718521, 39.739301326066972 ], [ -104.984405576000384, 39.739312355589277 ], [ -104.984415668470049, 39.739343029197364 ], [ -104.984627108987468, 39.739380706517757 ], [ -104.984588777809066, 39.739507939338338 ], [ -104.984526370399664, 39.739496818767911 ], [ -104.984569882870176, 39.739629100485189 ], [ -104.984727980482333, 39.739657263824427 ], [ -104.984776945353701, 39.739714149731292 ], [ -104.985129341687795, 39.739645608777948 ], [ -104.985169469061177, 39.739512414101071 ], [ -104.985094651182237, 39.739499082071127 ], [ -104.985047335234384, 39.739355276957703 ], [ -104.985233633344393, 39.739388464034057 ], [ -104.985255282861317, 39.739316602617635 ], [ -104.98533054137296, 39.739301960736626 ], [ -104.985368110972956, 39.739177293119575 ], [ -104.985254118484349, 39.739156986479529 ], [ -104.985231481859316, 39.739088168719192 ], [ -104.985062043520813, 39.73912113380581 ], [ -104.985138701617913, 39.73886668064069 ], [ -104.985121338284685, 39.738813908637098 ], [ -104.984985101526931, 39.738840406962673 ], [ -104.984913627781793, 39.738757369858419 ], [ -104.984758604874116, 39.738787522152172 ], [ -104.984764061451784, 39.738804106221444 ], [ -104.984680717381906, 39.738846525579198 ], [ -104.984528873544576, 39.738819475962735 ] ] ] } },
      { "type": "Feature", "properties": { "address": "320 W Colfax Ave", "city": "Denver", "state": "CO", "postal_code": "80204-2605", "side_of_street": "R", "neighborhood": "Civic Center", "county": "Denver", "country": "US", "latitude": 39.73924, "longitude": -104.99231, "quality": "P1AAA", "footprint_match": "intersection", "height": 13.208232879638672, "ubid": "85FQP2Q5+P3C6R9C-14485-4541-13934-3746" }, "geometry": { "type": "Polygon", "coordinates": [ [ [ -104.991998150710216, 39.739882920659866 ], [ -104.992647158215746, 39.739888718778808 ], [ -104.9926499934665, 39.739699928205745 ], [ -104.992679493831517, 39.73970019016987 ], [ -104.992678967334257, 39.739735248032851 ], [ -104.992818423605144, 39.739736486408056 ], [ -104.992830718865065, 39.738917777555947 ], [ -104.992710035553685, 39.738916705872356 ], [ -104.992712420276646, 39.738757912346749 ], [ -104.992039275584361, 39.738751934719787 ], [ -104.992038903939246, 39.738776681788934 ], [ -104.991926266181963, 39.738775681548894 ], [ -104.991924593778847, 39.738887043251786 ], [ -104.991970185252029, 39.738887448110198 ], [ -104.991963727013484, 39.739317486542454 ], [ -104.991821589006079, 39.739316216695997 ], [ -104.991819096676053, 39.739482174189305 ], [ -104.991939779987433, 39.739483245864093 ], [ -104.991941700153959, 39.739355387221934 ], [ -104.99200606458669, 39.739355958782873 ], [ -104.991998150710216, 39.739882920659866 ] ] ] } }
      ]
      }
      

Notes

• This workflow is optimized to be self-updating, and only downloads quadkeys and quadkey dataset-links if they haven't previously been downloaded or if an update is available
• Possible next steps:
  • Cache geocoding results to avoid API limit penalties when re-running
  • Allow other geocoders like Google, without persisting the geocoding results
  • Add distance from geocoded result to footprint boundary, proximity_to_geocoding_coord (intersections would be 0)
  • Update SEEDling to include this workflow, allowing you to upload an address list file and progressively update the map with records as they're processed (with a filterable sidebar containing list of results), and allowing you to fix which footprint is selected for a specific property