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 ] ] ] } }
      ]
      }
      

      Loading

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 (if allowed) 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

Disclaimer

When using this tool with the MapQuest geocoding API (or any other geocoder) always confirm that the terms of service allow for using and storing geocoding results (as with the MapQuest Enterprise license)