office_locations.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "487391b6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: osmium in /usr/local/lib/python3.11/site-packages (3.6.0)\n",
      "Requirement already satisfied: folium in /usr/local/lib/python3.11/site-packages (0.14.0)\n",
      "Requirement already satisfied: geodatasets in /usr/local/lib/python3.11/site-packages (2023.3.0)\n",
      "Requirement already satisfied: requests in /usr/local/lib/python3.11/site-packages (from osmium) (2.30.0)\n",
      "Requirement already satisfied: branca>=0.6.0 in /usr/local/lib/python3.11/site-packages (from folium) (0.6.0)\n",
      "Requirement already satisfied: jinja2>=2.9 in /usr/local/lib/python3.11/site-packages (from folium) (3.1.2)\n",
      "Requirement already satisfied: numpy in /usr/local/lib/python3.11/site-packages (from folium) (1.24.3)\n",
      "Requirement already satisfied: pooch in /usr/local/lib/python3.11/site-packages (from geodatasets) (1.7.0)\n",
      "Requirement already satisfied: MarkupSafe>=2.0 in /usr/local/lib/python3.11/site-packages (from jinja2>=2.9->folium) (2.1.2)\n",
      "Requirement already satisfied: platformdirs>=2.5.0 in /usr/local/lib/python3.11/site-packages (from pooch->geodatasets) (3.5.0)\n",
      "Requirement already satisfied: packaging>=20.0 in /usr/local/lib/python3.11/site-packages (from pooch->geodatasets) (23.1)\n",
      "Requirement already satisfied: charset-normalizer<4,>=2 in /usr/local/lib/python3.11/site-packages (from requests->osmium) (3.1.0)\n",
      "Requirement already satisfied: idna<4,>=2.5 in /usr/local/lib/python3.11/site-packages (from requests->osmium) (3.4)\n",
      "Requirement already satisfied: urllib3<3,>=1.21.1 in /usr/local/lib/python3.11/site-packages (from requests->osmium) (2.0.2)\n",
      "Requirement already satisfied: certifi>=2017.4.17 in /usr/local/lib/python3.11/site-packages (from requests->osmium) (2022.12.7)\n",
      "\n",
      "\u001b[1m[\u001b[0m\u001b[34;49mnotice\u001b[0m\u001b[1;39;49m]\u001b[0m\u001b[39;49m A new release of pip is available: \u001b[0m\u001b[31;49m23.0.1\u001b[0m\u001b[39;49m -> \u001b[0m\u001b[32;49m23.1.2\u001b[0m\n",
      "\u001b[1m[\u001b[0m\u001b[34;49mnotice\u001b[0m\u001b[1;39;49m]\u001b[0m\u001b[39;49m To update, run: \u001b[0m\u001b[32;49mpython3.11 -m pip install --upgrade pip\u001b[0m\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    }
   ],
   "source": [
    "%pip install osmium folium geodatasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "3fe3e81c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import folium\n",
    "import osmium as osm\n",
    "import pandas as pd\n",
    "import urllib.request\n",
    "from matplotlib import pyplot as plt\n",
    "from shapely.geometry import Point, Polygon"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "4ddb8996",
   "metadata": {},
   "outputs": [],
   "source": [
    "#you can create shape here https://clydedacruz.github.io/openstreetmap-wkt-playground/\n",
    "#copy polygon into var wkt_poly\n",
    "\n",
    "#seattle downtown\n",
    "# wkt_poly = '-122.3567 47.5933,-122.3139 47.5933,-122.3139 47.6517,-122.3567 47.6517'\n",
    "\n",
    "#new york manhattan downtown\n",
    "# wkt_poly = '-74.00239853421225 40.76848093731104,-73.95889190956949 40.752874609831736,-73.97423808462916 40.70989961182363,-73.99736844934523 40.704987174650086,-74.01250212220474 40.69698290095204,-74.0223849727772 40.69959263102922,-74.02202142868191 40.71276213012328,-74.01762143243103 40.73881298686783,-74.01512450538576 40.7547003536466,-74.00239853421225 40.76848093731104'\n",
    "\n",
    "#sf\n",
    "# wkt_poly = '-122.53185241587468 37.809559644767006,-122.51502511406065 37.710653461982176,-122.51204497967186 37.70676699804295,-122.36787600686763 37.70685478619541,-122.3456333312914 37.713680481105854,-122.34470397313827 37.74027641177274,-122.37476706258099 37.774478724975836,-122.38975963122566 37.805581044953044,-122.40176055172024 37.81335615786739,-122.43924826368664 37.81356889134105,-122.4675810788144 37.8122783423365,-122.49125005056311 37.805329437469155,-122.49451932787737 37.79418788424093,-122.53185241587468 37.809559644767006'\n",
    "\n",
    "#sv\n",
    "wkt_poly = '-122.16301466803996 37.486094621836685,-122.21170388627796 37.412707517346064,-122.16430812142787 37.38434852909499,-122.13205091189592 37.35399235923582,-122.10420128423718 37.34060791527514,-122.0776901207864 37.32213615696999,-122.07062748726456 37.29601352469203,-122.0484747039154 37.27603613974286,-121.9691434688866 37.2752793578006,-121.88139156438409 37.26995962602973,-121.81655913591383 37.28730773167956,-121.79111410863696 37.335420643991895,-121.81434480473398 37.39040580901978,-121.89833717420694 37.421568095242066,-121.9401141721755 37.43385473835333,-121.9943742547184 37.42129866120064,-122.0350014232099 37.42909243556316,-122.07515040412544 37.4453550823531,-122.10126862395553 37.47097268828807,-122.11850931867956 37.48897093733271,-122.13452558498828 37.50144482955908,-122.16301466803996 37.486094621836685'\n",
    "\n",
    "poly_points = [(float(i.split(' ')[1]), float(i.split(' ')[0])) for i in wkt_poly.split(',')]\n",
    "\n",
    "current_dir = os.getcwd()\n",
    "filename = f'{current_dir}/map-data.osm'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "c37dbe88",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div style=\"width:100%;\"><div style=\"position:relative;width:100%;height:0;padding-bottom:60%;\"><span style=\"color:#565656\">Make this Notebook Trusted to load map: File -> Trust Notebook</span><iframe srcdoc=\"&lt;!DOCTYPE html&gt;\n",
       "&lt;html&gt;\n",
       "&lt;head&gt;\n",
       "    \n",
       "    &lt;meta http-equiv=&quot;content-type&quot; content=&quot;text/html; charset=UTF-8&quot; /&gt;\n",
       "    \n",
       "        &lt;script&gt;\n",
       "            L_NO_TOUCH = false;\n",
       "            L_DISABLE_3D = false;\n",
       "        &lt;/script&gt;\n",
       "    \n",
       "    &lt;style&gt;html, body {width: 100%;height: 100%;margin: 0;padding: 0;}&lt;/style&gt;\n",
       "    &lt;style&gt;#map {position:absolute;top:0;bottom:0;right:0;left:0;}&lt;/style&gt;\n",
       "    &lt;script src=&quot;https://cdn.jsdelivr.net/npm/leaflet@1.9.3/dist/leaflet.js&quot;&gt;&lt;/script&gt;\n",
       "    &lt;script src=&quot;https://code.jquery.com/jquery-1.12.4.min.js&quot;&gt;&lt;/script&gt;\n",
       "    &lt;script src=&quot;https://cdn.jsdelivr.net/npm/bootstrap@5.2.2/dist/js/bootstrap.bundle.min.js&quot;&gt;&lt;/script&gt;\n",
       "    &lt;script src=&quot;https://cdnjs.cloudflare.com/ajax/libs/Leaflet.awesome-markers/2.0.2/leaflet.awesome-markers.js&quot;&gt;&lt;/script&gt;\n",
       "    &lt;link rel=&quot;stylesheet&quot; href=&quot;https://cdn.jsdelivr.net/npm/leaflet@1.9.3/dist/leaflet.css&quot;/&gt;\n",
       "    &lt;link rel=&quot;stylesheet&quot; href=&quot;https://cdn.jsdelivr.net/npm/bootstrap@5.2.2/dist/css/bootstrap.min.css&quot;/&gt;\n",
       "    &lt;link rel=&quot;stylesheet&quot; href=&quot;https://netdna.bootstrapcdn.com/bootstrap/3.0.0/css/bootstrap.min.css&quot;/&gt;\n",
       "    &lt;link rel=&quot;stylesheet&quot; href=&quot;https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free@6.2.0/css/all.min.css&quot;/&gt;\n",
       "    &lt;link rel=&quot;stylesheet&quot; href=&quot;https://cdnjs.cloudflare.com/ajax/libs/Leaflet.awesome-markers/2.0.2/leaflet.awesome-markers.css&quot;/&gt;\n",
       "    &lt;link rel=&quot;stylesheet&quot; href=&quot;https://cdn.jsdelivr.net/gh/python-visualization/folium/folium/templates/leaflet.awesome.rotate.min.css&quot;/&gt;\n",
       "    \n",
       "            &lt;meta name=&quot;viewport&quot; content=&quot;width=device-width,\n",
       "                initial-scale=1.0, maximum-scale=1.0, user-scalable=no&quot; /&gt;\n",
       "            &lt;style&gt;\n",
       "                #map_5e44d4abdef10a092e653c47c8136780 {\n",
       "                    position: relative;\n",
       "                    width: 100.0%;\n",
       "                    height: 100.0%;\n",
       "                    left: 0.0%;\n",
       "                    top: 0.0%;\n",
       "                }\n",
       "                .leaflet-container { font-size: 1rem; }\n",
       "            &lt;/style&gt;\n",
       "        \n",
       "&lt;/head&gt;\n",
       "&lt;body&gt;\n",
       "    \n",
       "    \n",
       "            &lt;div class=&quot;folium-map&quot; id=&quot;map_5e44d4abdef10a092e653c47c8136780&quot; &gt;&lt;/div&gt;\n",
       "        \n",
       "&lt;/body&gt;\n",
       "&lt;script&gt;\n",
       "    \n",
       "    \n",
       "            var map_5e44d4abdef10a092e653c47c8136780 = L.map(\n",
       "                &quot;map_5e44d4abdef10a092e653c47c8136780&quot;,\n",
       "                {\n",
       "                    center: [0, 0],\n",
       "                    crs: L.CRS.EPSG3857,\n",
       "                    zoom: 1,\n",
       "                    zoomControl: true,\n",
       "                    preferCanvas: false,\n",
       "                }\n",
       "            );\n",
       "\n",
       "            \n",
       "\n",
       "        \n",
       "    \n",
       "            var tile_layer_c81cff0e05c88c922c76452e5a44f1e8 = L.tileLayer(\n",
       "                &quot;https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png&quot;,\n",
       "                {&quot;attribution&quot;: &quot;Data by \\u0026copy; \\u003ca target=\\&quot;_blank\\&quot; href=\\&quot;http://openstreetmap.org\\&quot;\\u003eOpenStreetMap\\u003c/a\\u003e, under \\u003ca target=\\&quot;_blank\\&quot; href=\\&quot;http://www.openstreetmap.org/copyright\\&quot;\\u003eODbL\\u003c/a\\u003e.&quot;, &quot;detectRetina&quot;: false, &quot;maxNativeZoom&quot;: 18, &quot;maxZoom&quot;: 18, &quot;minZoom&quot;: 0, &quot;noWrap&quot;: false, &quot;opacity&quot;: 1, &quot;subdomains&quot;: &quot;abc&quot;, &quot;tms&quot;: false}\n",
       "            ).addTo(map_5e44d4abdef10a092e653c47c8136780);\n",
       "        \n",
       "    \n",
       "        function geo_json_ba8af77e975f478b03730e9182dae4e7_styler(feature) {\n",
       "            switch(feature.id) {\n",
       "                default:\n",
       "                    return {&quot;fillColor&quot;: &quot;orange&quot;};\n",
       "            }\n",
       "        }\n",
       "\n",
       "        function geo_json_ba8af77e975f478b03730e9182dae4e7_onEachFeature(feature, layer) {\n",
       "            layer.on({\n",
       "            });\n",
       "        };\n",
       "        var geo_json_ba8af77e975f478b03730e9182dae4e7 = L.geoJson(null, {\n",
       "                onEachFeature: geo_json_ba8af77e975f478b03730e9182dae4e7_onEachFeature,\n",
       "            \n",
       "                style: geo_json_ba8af77e975f478b03730e9182dae4e7_styler,\n",
       "        });\n",
       "\n",
       "        function geo_json_ba8af77e975f478b03730e9182dae4e7_add (data) {\n",
       "            geo_json_ba8af77e975f478b03730e9182dae4e7\n",
       "                .addData(data)\n",
       "                .addTo(map_5e44d4abdef10a092e653c47c8136780);\n",
       "        }\n",
       "            geo_json_ba8af77e975f478b03730e9182dae4e7_add({&quot;features&quot;: [{&quot;geometry&quot;: {&quot;coordinates&quot;: [[[-122.16301466803996, 37.486094621836685], [-122.21170388627796, 37.412707517346064], [-122.16430812142787, 37.38434852909499], [-122.13205091189592, 37.35399235923582], [-122.10420128423718, 37.34060791527514], [-122.0776901207864, 37.32213615696999], [-122.07062748726456, 37.29601352469203], [-122.0484747039154, 37.27603613974286], [-121.9691434688866, 37.2752793578006], [-121.88139156438409, 37.26995962602973], [-121.81655913591383, 37.28730773167956], [-121.79111410863696, 37.335420643991895], [-121.81434480473398, 37.39040580901978], [-121.89833717420694, 37.421568095242066], [-121.9401141721755, 37.43385473835333], [-121.9943742547184, 37.42129866120064], [-122.0350014232099, 37.42909243556316], [-122.07515040412544, 37.4453550823531], [-122.10126862395553, 37.47097268828807], [-122.11850931867956, 37.48897093733271], [-122.13452558498828, 37.50144482955908], [-122.16301466803996, 37.486094621836685]]], &quot;type&quot;: &quot;Polygon&quot;}, &quot;id&quot;: &quot;0&quot;, &quot;properties&quot;: {}, &quot;type&quot;: &quot;Feature&quot;}], &quot;type&quot;: &quot;FeatureCollection&quot;});\n",
       "\n",
       "        \n",
       "    \n",
       "        var popup_37dd7d76d3269a6faa2e601982954070 = L.popup({&quot;maxWidth&quot;: &quot;100%&quot;});\n",
       "\n",
       "        \n",
       "            \n",
       "                var html_2b5e96b7e92ebff11b0055a674e2ab91 = $(`&lt;div id=&quot;html_2b5e96b7e92ebff11b0055a674e2ab91&quot; style=&quot;width: 100.0%; height: 100.0%;&quot;&gt;location&lt;/div&gt;`)[0];\n",
       "                popup_37dd7d76d3269a6faa2e601982954070.setContent(html_2b5e96b7e92ebff11b0055a674e2ab91);\n",
       "            \n",
       "        \n",
       "\n",
       "        geo_json_ba8af77e975f478b03730e9182dae4e7.bindPopup(popup_37dd7d76d3269a6faa2e601982954070)\n",
       "        ;\n",
       "\n",
       "        \n",
       "    \n",
       "    \n",
       "            map_5e44d4abdef10a092e653c47c8136780.fitBounds(\n",
       "                [[37.26995962602973, -122.21170388627796], [37.50144482955908, -121.79111410863696]],\n",
       "                {}\n",
       "            );\n",
       "        \n",
       "&lt;/script&gt;\n",
       "&lt;/html&gt;\" style=\"position:absolute;width:100%;height:100%;left:0;top:0;border:none !important;\" allowfullscreen webkitallowfullscreen mozallowfullscreen></iframe></div></div>"
      ],
      "text/plain": [
       "<folium.folium.Map at 0x1268ad910>"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "map_data = {\n",
    "  \"type\": \"FeatureCollection\",\n",
    "  \"features\": [\n",
    "    {\n",
    "      \"type\": \"Feature\",\n",
    "      \"properties\": {},\n",
    "      \"geometry\": {\n",
    "        \"coordinates\": [[[i[1], i[0]] for i in poly_points]],\n",
    "        \"type\": \"Polygon\"\n",
    "      }\n",
    "    }\n",
    "  ]\n",
    "}\n",
    "m = folium.Map()\n",
    "geo_j = folium.GeoJson(data=map_data, style_function=lambda x: {\"fillColor\": \"orange\"})\n",
    "folium.Popup('location').add_to(geo_j)\n",
    "geo_j.add_to(m)\n",
    "m.fit_bounds([[min([i[0] for i in poly_points]), min([i[1] for i in poly_points])], [max([i[0] for i in poly_points]), max([i[1] for i in poly_points])]]) \n",
    "m"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5b6c88d6",
   "metadata": {},
   "source": [
    "<h1>Download map data</h1>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "2b35cd92",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "wkt polygon:POLYGON((-122.16301466803996 37.486094621836685,-122.21170388627796 37.412707517346064,-122.16430812142787 37.38434852909499,-122.13205091189592 37.35399235923582,-122.10420128423718 37.34060791527514,-122.0776901207864 37.32213615696999,-122.07062748726456 37.29601352469203,-122.0484747039154 37.27603613974286,-121.9691434688866 37.2752793578006,-121.88139156438409 37.26995962602973,-121.81655913591383 37.28730773167956,-121.79111410863696 37.335420643991895,-121.81434480473398 37.39040580901978,-121.89833717420694 37.421568095242066,-121.9401141721755 37.43385473835333,-121.9943742547184 37.42129866120064,-122.0350014232099 37.42909243556316,-122.07515040412544 37.4453550823531,-122.10126862395553 37.47097268828807,-122.11850931867956 37.48897093733271,-122.13452558498828 37.50144482955908,-122.16301466803996 37.486094621836685))\n",
      "CPU times: user 2.23 s, sys: 3.8 s, total: 6.03 s\n",
      "Wall time: 59.6 s\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "('/Users/chaoqinglu/src/office_locations/map-data.osm',\n",
       " <http.client.HTTPMessage at 0x1267ad690>)"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(f\"wkt polygon:POLYGON(({','.join([f'{i[1]} {i[0]}' for i in poly_points])}))\")\n",
    "poly = ' '.join([f'{i[0]} {i[1]}' for i in poly_points])\n",
    "url = f'''http://overpass-api.de/api/interpreter?data=(node(poly:\"{poly}\");way(poly:\"{poly}\"););(._;>;);out;'''\n",
    "url = url.replace(\" \", \"%20\")\n",
    "%time urllib.request.urlretrieve(url, filename)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "004f3da2",
   "metadata": {},
   "source": [
    "<h1>Process map data</h1>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "1c42900e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "missing 0 nodes in ways\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>type</th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>tag_key</th>\n",
       "      <th>tag_val</th>\n",
       "      <th>is_building</th>\n",
       "      <th>building_tag</th>\n",
       "      <th>tags</th>\n",
       "      <th>points</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8803</th>\n",
       "      <td>way</td>\n",
       "      <td>639145764</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>True</td>\n",
       "      <td>school</td>\n",
       "      <td>building:school,ele:61.51,height:3.46</td>\n",
       "      <td>[(-121.9231349, 37.2777541), (-121.9232086, 37...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4009</th>\n",
       "      <td>way</td>\n",
       "      <td>132296660</td>\n",
       "      <td></td>\n",
       "      <td>office</td>\n",
       "      <td>company</td>\n",
       "      <td>True</td>\n",
       "      <td>yes</td>\n",
       "      <td>addr:housenumber:654,addr:street:high street,b...</td>\n",
       "      <td>[(-122.1614154, 37.4427508), (-122.1616556, 37...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8452</th>\n",
       "      <td>way</td>\n",
       "      <td>563781015</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>True</td>\n",
       "      <td>school</td>\n",
       "      <td>building:school,ele:38.33,height:3.40</td>\n",
       "      <td>[(-121.8389414, 37.3303943), (-121.8391476, 37...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8451</th>\n",
       "      <td>way</td>\n",
       "      <td>563781014</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>True</td>\n",
       "      <td>school</td>\n",
       "      <td>building:school,building:prefabricated:yes,ele...</td>\n",
       "      <td>[(-121.8386946, 37.3300083), (-121.8387606, 37...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8450</th>\n",
       "      <td>way</td>\n",
       "      <td>563781013</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>True</td>\n",
       "      <td>school</td>\n",
       "      <td>building:school,building:prefabricated:yes,ele...</td>\n",
       "      <td>[(-121.8387916, 37.330112), (-121.8386623, 37....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8268</th>\n",
       "      <td>way</td>\n",
       "      <td>546549901</td>\n",
       "      <td>信望愛 faith, hope, and love chinese school</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>True</td>\n",
       "      <td>yes</td>\n",
       "      <td>addr:city:san jose,addr:housenumber:6350,addr:...</td>\n",
       "      <td>[(-122.0166338, 37.3009343), (-122.0168683, 37...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1494</th>\n",
       "      <td>node</td>\n",
       "      <td>8813107045</td>\n",
       "      <td>利达快递</td>\n",
       "      <td>amenity</td>\n",
       "      <td>post_office</td>\n",
       "      <td>False</td>\n",
       "      <td></td>\n",
       "      <td>addr:city:cupertino,addr:housenumber:10601,add...</td>\n",
       "      <td>[(-122.032704, 37.3141899)]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9676</th>\n",
       "      <td>way</td>\n",
       "      <td>917702297</td>\n",
       "      <td>基督教義興教育中心;christian righteousness education ce...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>True</td>\n",
       "      <td>yes</td>\n",
       "      <td>addr:city:saratoga,addr:housenumber:19888,addr...</td>\n",
       "      <td>[(-122.0311546, 37.2938068), (-122.0311635, 37...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8301</th>\n",
       "      <td>way</td>\n",
       "      <td>553223583</td>\n",
       "      <td>方舟浸信教會;ark baptist church</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>True</td>\n",
       "      <td>commercial</td>\n",
       "      <td>addr:city:milpitas,addr:housenumber:380,addr:p...</td>\n",
       "      <td>[(-121.8952005, 37.4066914), (-121.8949566, 37...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7336</th>\n",
       "      <td>way</td>\n",
       "      <td>390108834</td>\n",
       "      <td>대성 장로 교회;daesung presbyterian church</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>True</td>\n",
       "      <td>commercial</td>\n",
       "      <td>addr:city:sunnyvale,addr:housenumber:425,addr:...</td>\n",
       "      <td>[(-122.0131188, 37.404156), (-122.0127312, 37....</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10221 rows × 9 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      type          id                                               name  \\\n",
       "8803   way   639145764                                                      \n",
       "4009   way   132296660                                                      \n",
       "8452   way   563781015                                                      \n",
       "8451   way   563781014                                                      \n",
       "8450   way   563781013                                                      \n",
       "...    ...         ...                                                ...   \n",
       "8268   way   546549901           信望愛 faith, hope, and love chinese school   \n",
       "1494  node  8813107045                                               利达快递   \n",
       "9676   way   917702297  基督教義興教育中心;christian righteousness education ce...   \n",
       "8301   way   553223583                          方舟浸信教會;ark baptist church   \n",
       "7336   way   390108834               대성 장로 교회;daesung presbyterian church   \n",
       "\n",
       "      tag_key      tag_val  is_building building_tag  \\\n",
       "8803                               True       school   \n",
       "4009   office      company         True          yes   \n",
       "8452                               True       school   \n",
       "8451                               True       school   \n",
       "8450                               True       school   \n",
       "...       ...          ...          ...          ...   \n",
       "8268                               True          yes   \n",
       "1494  amenity  post_office        False                \n",
       "9676                               True          yes   \n",
       "8301                               True   commercial   \n",
       "7336                               True   commercial   \n",
       "\n",
       "                                                   tags  \\\n",
       "8803              building:school,ele:61.51,height:3.46   \n",
       "4009  addr:housenumber:654,addr:street:high street,b...   \n",
       "8452              building:school,ele:38.33,height:3.40   \n",
       "8451  building:school,building:prefabricated:yes,ele...   \n",
       "8450  building:school,building:prefabricated:yes,ele...   \n",
       "...                                                 ...   \n",
       "8268  addr:city:san jose,addr:housenumber:6350,addr:...   \n",
       "1494  addr:city:cupertino,addr:housenumber:10601,add...   \n",
       "9676  addr:city:saratoga,addr:housenumber:19888,addr...   \n",
       "8301  addr:city:milpitas,addr:housenumber:380,addr:p...   \n",
       "7336  addr:city:sunnyvale,addr:housenumber:425,addr:...   \n",
       "\n",
       "                                                 points  \n",
       "8803  [(-121.9231349, 37.2777541), (-121.9232086, 37...  \n",
       "4009  [(-122.1614154, 37.4427508), (-122.1616556, 37...  \n",
       "8452  [(-121.8389414, 37.3303943), (-121.8391476, 37...  \n",
       "8451  [(-121.8386946, 37.3300083), (-121.8387606, 37...  \n",
       "8450  [(-121.8387916, 37.330112), (-121.8386623, 37....  \n",
       "...                                                 ...  \n",
       "8268  [(-122.0166338, 37.3009343), (-122.0168683, 37...  \n",
       "1494                        [(-122.032704, 37.3141899)]  \n",
       "9676  [(-122.0311546, 37.2938068), (-122.0311635, 37...  \n",
       "8301  [(-121.8952005, 37.4066914), (-121.8949566, 37...  \n",
       "7336  [(-122.0131188, 37.404156), (-122.0127312, 37....  \n",
       "\n",
       "[10221 rows x 9 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "valid_building_tags = set(['office', 'commercial', 'university', 'school', 'college', 'bank'])\n",
    "class OSMHandler(osm.SimpleHandler):\n",
    "    def __init__(self):\n",
    "        osm.SimpleHandler.__init__(self)\n",
    "        self.osm_data = []\n",
    "        self.node_coordinates = {}\n",
    "        self.missing_nodes = 0\n",
    "\n",
    "    def tag_office(self, elem, elem_type):\n",
    "        name = ''\n",
    "        tag_key_office = ''\n",
    "        tag_val_office = ''\n",
    "        is_building = False\n",
    "        building_tag_val = ''\n",
    "        \n",
    "        for tag in elem.tags:\n",
    "            if tag.k.lower() == 'website':\n",
    "                continue\n",
    "            if 'office' in tag.k.lower() and tag_key_office != 'office':\n",
    "                tag_key_office = tag.k.lower()\n",
    "                tag_val_office = tag.v.lower()\n",
    "            if 'office' in tag.v.lower() and tag_val_office != 'office':\n",
    "                tag_key_office = tag.k.lower()\n",
    "                tag_val_office = tag.v.lower()\n",
    "            if ('building'==tag.k):\n",
    "                is_building = True\n",
    "                building_tag_val = tag.v.lower()\n",
    "            if tag.k == 'name':\n",
    "                name = tag.v\n",
    "        if tag_key_office != '' or tag_val_office != '' or \\\n",
    "            (is_building and (\n",
    "                building_tag_val in valid_building_tags or \\\n",
    "                building_tag_val == 'yes' and name != ''\n",
    "            )):\n",
    "            points = []\n",
    "            if elem_type == 'way':\n",
    "                for n in elem.nodes:\n",
    "                    if n.ref in self.node_coordinates:\n",
    "                        points.append(self.node_coordinates[n.ref])\n",
    "                    else:\n",
    "                        self.missing_nodes += 1\n",
    "                        #print(f'missing loc for {n.ref} {elem.id}')\n",
    "            else:\n",
    "                points.append((elem.location.lon, elem.location.lat))\n",
    "            self.osm_data.append([\n",
    "                elem_type, \n",
    "                int(elem.id), \n",
    "                name.lower(),\n",
    "                tag_key_office,\n",
    "                tag_val_office,\n",
    "                is_building, \n",
    "                building_tag_val,\n",
    "                ','.join([f'{tag.k.lower()}:{tag.v.lower()}' for tag in elem.tags]),\n",
    "                points,\n",
    "            ])\n",
    "\n",
    "    def node(self, n):\n",
    "        self.node_coordinates[n.id] = (n.location.lon, n.location.lat)\n",
    "        self.tag_office(n, \"node\")\n",
    "    def way(self, w):\n",
    "        self.tag_office(w, \"way\")\n",
    "\n",
    "\n",
    "osmhandler = OSMHandler()\n",
    "# scan the input file and fills the handler list accordingly\n",
    "osmhandler.apply_file(filename)\n",
    "print(f'missing {osmhandler.missing_nodes} nodes in ways')\n",
    "# transform the list into a pandas DataFrame\n",
    "data_colnames = ['type', 'id', 'name', 'tag_key', 'tag_val', 'is_building', 'building_tag', 'tags', 'points']\n",
    "df_osm = pd.DataFrame(osmhandler.osm_data, columns=data_colnames)\n",
    "df_osm.sort_values(by=['name'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "edb393bb",
   "metadata": {},
   "source": [
    "<h1>QAs</h1>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "8a9d8b98",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "type  tag_key   tag_val                                                   is_building\n",
       "way                                                                       True           7209\n",
       "      building  office                                                    True            513\n",
       "node  office    company                                                   False           252\n",
       "                yes                                                       False           247\n",
       "way   office    company                                                   True            180\n",
       "                                                                                         ... \n",
       "node  subject   san josé police officer michael johnson memorial highway  False             2\n",
       "      office    investment                                                False             2\n",
       "way   name      fedex office                                              True              2\n",
       "node  building  office                                                    True              2\n",
       "way   amenity   post_office                                               False             2\n",
       "Length: 100, dtype: int64"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_osm[['type', 'tag_key', 'tag_val', 'is_building']].value_counts()[:100]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "bbd179bf",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "                                             2971\n",
       "guadalupe parkway                              62\n",
       "7-eleven                                       33\n",
       "the ups store                                  30\n",
       "h&r block                                      20\n",
       "                                             ... \n",
       "329                                             3\n",
       "cardenas                                        3\n",
       "santa clara county social services agency       3\n",
       "bioabsorbant therapeutics                       3\n",
       "science building                                3\n",
       "Name: name, Length: 100, dtype: int64"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_osm['name'].value_counts()[:100]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "dd4af257",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>type</th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>tag_key</th>\n",
       "      <th>tag_val</th>\n",
       "      <th>is_building</th>\n",
       "      <th>building_tag</th>\n",
       "      <th>tags</th>\n",
       "      <th>points</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1583</th>\n",
       "      <td>node</td>\n",
       "      <td>9131017876</td>\n",
       "      <td>sutter hill ventures</td>\n",
       "      <td>office</td>\n",
       "      <td>company</td>\n",
       "      <td>False</td>\n",
       "      <td></td>\n",
       "      <td>addr:city:palo alto,addr:housenumber:755,addr:...</td>\n",
       "      <td>[(-122.1427342, 37.4203955)]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1802</th>\n",
       "      <td>node</td>\n",
       "      <td>10844739563</td>\n",
       "      <td>linkedin building n</td>\n",
       "      <td>office</td>\n",
       "      <td>yes</td>\n",
       "      <td>False</td>\n",
       "      <td></td>\n",
       "      <td>addr:city:sunnyvale,addr:housenumber:479,addr:...</td>\n",
       "      <td>[(-122.0348812, 37.3893634)]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1804</th>\n",
       "      <td>node</td>\n",
       "      <td>10844739565</td>\n",
       "      <td>linkedin building m</td>\n",
       "      <td>office</td>\n",
       "      <td>yes</td>\n",
       "      <td>False</td>\n",
       "      <td></td>\n",
       "      <td>addr:city:sunnyvale,addr:housenumber:845,addr:...</td>\n",
       "      <td>[(-122.0350464, 37.3916454)]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1805</th>\n",
       "      <td>node</td>\n",
       "      <td>10844739566</td>\n",
       "      <td>linkedin</td>\n",
       "      <td>office</td>\n",
       "      <td>yes</td>\n",
       "      <td>False</td>\n",
       "      <td></td>\n",
       "      <td>addr:city:sunnyvale,addr:housenumber:815,addr:...</td>\n",
       "      <td>[(-122.034442, 37.3912438)]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1807</th>\n",
       "      <td>node</td>\n",
       "      <td>10844739568</td>\n",
       "      <td>linkedin building j</td>\n",
       "      <td>office</td>\n",
       "      <td>yes</td>\n",
       "      <td>False</td>\n",
       "      <td></td>\n",
       "      <td>addr:city:sunnyvale,addr:housenumber:580,addr:...</td>\n",
       "      <td>[(-122.0363024, 37.3920298)]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1808</th>\n",
       "      <td>node</td>\n",
       "      <td>10844739569</td>\n",
       "      <td>linkedin</td>\n",
       "      <td>office</td>\n",
       "      <td>yes</td>\n",
       "      <td>False</td>\n",
       "      <td></td>\n",
       "      <td>addr:city:sunnyvale,addr:housenumber:880,addr:...</td>\n",
       "      <td>[(-122.0372172, 37.3913742)]</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1941</th>\n",
       "      <td>way</td>\n",
       "      <td>25796511</td>\n",
       "      <td>linkedin</td>\n",
       "      <td>building:use</td>\n",
       "      <td>office</td>\n",
       "      <td>True</td>\n",
       "      <td>commercial</td>\n",
       "      <td>addr:city:sunnyvale,addr:housenumber:1000,addr...</td>\n",
       "      <td>[(-122.0422986, 37.3927221), (-122.042388, 37....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1942</th>\n",
       "      <td>way</td>\n",
       "      <td>25796520</td>\n",
       "      <td>linkedin</td>\n",
       "      <td>building:use</td>\n",
       "      <td>office</td>\n",
       "      <td>True</td>\n",
       "      <td>commercial</td>\n",
       "      <td>addr:city:sunnyvale,addr:housenumber:950,addr:...</td>\n",
       "      <td>[(-122.0412881, 37.3924897), (-122.0412188, 37...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3343</th>\n",
       "      <td>way</td>\n",
       "      <td>102376955</td>\n",
       "      <td>linkedin</td>\n",
       "      <td>building</td>\n",
       "      <td>office</td>\n",
       "      <td>True</td>\n",
       "      <td>office</td>\n",
       "      <td>addr:city:sunnyvale,addr:housenumber:845,addr:...</td>\n",
       "      <td>[(-122.0350768, 37.3921368), (-122.0347451, 37...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3351</th>\n",
       "      <td>way</td>\n",
       "      <td>102376971</td>\n",
       "      <td>linkedin</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>True</td>\n",
       "      <td>commercial</td>\n",
       "      <td>addr:housenumber:755,addr:street:north mathild...</td>\n",
       "      <td>[(-122.0304461, 37.3944523), (-122.0299343, 37...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3353</th>\n",
       "      <td>way</td>\n",
       "      <td>102376975</td>\n",
       "      <td>linkedin</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>True</td>\n",
       "      <td>yes</td>\n",
       "      <td>addr:city:sunnyvale,addr:housenumber:580,addr:...</td>\n",
       "      <td>[(-122.0336759, 37.3920608), (-122.0333962, 37...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3359</th>\n",
       "      <td>way</td>\n",
       "      <td>102376988</td>\n",
       "      <td>linkedin</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>True</td>\n",
       "      <td>commercial</td>\n",
       "      <td>addr:city:sunnyvale,addr:housenumber:680,addr:...</td>\n",
       "      <td>[(-122.0311746, 37.3945986), (-122.0308902, 37...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3366</th>\n",
       "      <td>way</td>\n",
       "      <td>102377004</td>\n",
       "      <td>linkedin</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>True</td>\n",
       "      <td>commercial</td>\n",
       "      <td>addr:city:sunnyvale,addr:housenumber:599,addr:...</td>\n",
       "      <td>[(-122.0321242, 37.3922418), (-122.0320345, 37...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3604</th>\n",
       "      <td>way</td>\n",
       "      <td>112511815</td>\n",
       "      <td>linkedin</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>True</td>\n",
       "      <td>commercial</td>\n",
       "      <td>addr:city:sunnyvale,addr:housenumber:880,addr:...</td>\n",
       "      <td>[(-122.0377083, 37.3915325), (-122.0373896, 37...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4558</th>\n",
       "      <td>way</td>\n",
       "      <td>197045240</td>\n",
       "      <td>linkedin</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>True</td>\n",
       "      <td>commercial</td>\n",
       "      <td>addr:city:sunnyvale,addr:housenumber:1101,addr...</td>\n",
       "      <td>[(-122.0460082, 37.394076), (-122.0462397, 37....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4560</th>\n",
       "      <td>way</td>\n",
       "      <td>197045242</td>\n",
       "      <td>linkedin</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>True</td>\n",
       "      <td>commercial</td>\n",
       "      <td>addr:city:mountain view,addr:housenumber:700,a...</td>\n",
       "      <td>[(-122.0468619, 37.391744), (-122.0467876, 37....</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4561</th>\n",
       "      <td>way</td>\n",
       "      <td>197045243</td>\n",
       "      <td>linkedin</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>True</td>\n",
       "      <td>commercial</td>\n",
       "      <td>addr:city:mountain view,addr:housenumber:700,a...</td>\n",
       "      <td>[(-122.0477275, 37.3926406), (-122.0472586, 37...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4562</th>\n",
       "      <td>way</td>\n",
       "      <td>197045244</td>\n",
       "      <td>linkedin</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>True</td>\n",
       "      <td>commercial</td>\n",
       "      <td>addr:city:mountain view,addr:housenumber:700,a...</td>\n",
       "      <td>[(-122.0486258, 37.3922433), (-122.0486371, 37...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6145</th>\n",
       "      <td>way</td>\n",
       "      <td>301730085</td>\n",
       "      <td>linkedin</td>\n",
       "      <td>building</td>\n",
       "      <td>office</td>\n",
       "      <td>True</td>\n",
       "      <td>office</td>\n",
       "      <td>addr:city:sunnyvale,addr:housenumber:605,addr:...</td>\n",
       "      <td>[(-122.0335729, 37.3912775), (-122.0328569, 37...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6146</th>\n",
       "      <td>way</td>\n",
       "      <td>301730087</td>\n",
       "      <td>linkedin</td>\n",
       "      <td>building:use</td>\n",
       "      <td>office</td>\n",
       "      <td>True</td>\n",
       "      <td>commercial</td>\n",
       "      <td>addr:city:sunnyvale,addr:housenumber:580,addr:...</td>\n",
       "      <td>[(-122.036803, 37.392416), (-122.0366585, 37.3...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8051</th>\n",
       "      <td>way</td>\n",
       "      <td>495766796</td>\n",
       "      <td>linkedin</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>True</td>\n",
       "      <td>commercial</td>\n",
       "      <td>addr:city:sunnyvale,addr:housenumber:479,addr:...</td>\n",
       "      <td>[(-122.0352514, 37.3897049), (-122.0351983, 37...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9079</th>\n",
       "      <td>way</td>\n",
       "      <td>706491990</td>\n",
       "      <td>linkedin</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>True</td>\n",
       "      <td>commercial</td>\n",
       "      <td>addr:city:sunnyvale,addr:housenumber:580,addr:...</td>\n",
       "      <td>[(-122.0346382, 37.391521), (-122.0346606, 37....</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      type           id                  name       tag_key  tag_val  \\\n",
       "1583  node   9131017876  sutter hill ventures        office  company   \n",
       "1802  node  10844739563   linkedin building n        office      yes   \n",
       "1804  node  10844739565   linkedin building m        office      yes   \n",
       "1805  node  10844739566              linkedin        office      yes   \n",
       "1807  node  10844739568   linkedin building j        office      yes   \n",
       "1808  node  10844739569              linkedin        office      yes   \n",
       "1941   way     25796511              linkedin  building:use   office   \n",
       "1942   way     25796520              linkedin  building:use   office   \n",
       "3343   way    102376955              linkedin      building   office   \n",
       "3351   way    102376971              linkedin                          \n",
       "3353   way    102376975              linkedin                          \n",
       "3359   way    102376988              linkedin                          \n",
       "3366   way    102377004              linkedin                          \n",
       "3604   way    112511815              linkedin                          \n",
       "4558   way    197045240              linkedin                          \n",
       "4560   way    197045242              linkedin                          \n",
       "4561   way    197045243              linkedin                          \n",
       "4562   way    197045244              linkedin                          \n",
       "6145   way    301730085              linkedin      building   office   \n",
       "6146   way    301730087              linkedin  building:use   office   \n",
       "8051   way    495766796              linkedin                          \n",
       "9079   way    706491990              linkedin                          \n",
       "\n",
       "      is_building building_tag  \\\n",
       "1583        False                \n",
       "1802        False                \n",
       "1804        False                \n",
       "1805        False                \n",
       "1807        False                \n",
       "1808        False                \n",
       "1941         True   commercial   \n",
       "1942         True   commercial   \n",
       "3343         True       office   \n",
       "3351         True   commercial   \n",
       "3353         True          yes   \n",
       "3359         True   commercial   \n",
       "3366         True   commercial   \n",
       "3604         True   commercial   \n",
       "4558         True   commercial   \n",
       "4560         True   commercial   \n",
       "4561         True   commercial   \n",
       "4562         True   commercial   \n",
       "6145         True       office   \n",
       "6146         True   commercial   \n",
       "8051         True   commercial   \n",
       "9079         True   commercial   \n",
       "\n",
       "                                                   tags  \\\n",
       "1583  addr:city:palo alto,addr:housenumber:755,addr:...   \n",
       "1802  addr:city:sunnyvale,addr:housenumber:479,addr:...   \n",
       "1804  addr:city:sunnyvale,addr:housenumber:845,addr:...   \n",
       "1805  addr:city:sunnyvale,addr:housenumber:815,addr:...   \n",
       "1807  addr:city:sunnyvale,addr:housenumber:580,addr:...   \n",
       "1808  addr:city:sunnyvale,addr:housenumber:880,addr:...   \n",
       "1941  addr:city:sunnyvale,addr:housenumber:1000,addr...   \n",
       "1942  addr:city:sunnyvale,addr:housenumber:950,addr:...   \n",
       "3343  addr:city:sunnyvale,addr:housenumber:845,addr:...   \n",
       "3351  addr:housenumber:755,addr:street:north mathild...   \n",
       "3353  addr:city:sunnyvale,addr:housenumber:580,addr:...   \n",
       "3359  addr:city:sunnyvale,addr:housenumber:680,addr:...   \n",
       "3366  addr:city:sunnyvale,addr:housenumber:599,addr:...   \n",
       "3604  addr:city:sunnyvale,addr:housenumber:880,addr:...   \n",
       "4558  addr:city:sunnyvale,addr:housenumber:1101,addr...   \n",
       "4560  addr:city:mountain view,addr:housenumber:700,a...   \n",
       "4561  addr:city:mountain view,addr:housenumber:700,a...   \n",
       "4562  addr:city:mountain view,addr:housenumber:700,a...   \n",
       "6145  addr:city:sunnyvale,addr:housenumber:605,addr:...   \n",
       "6146  addr:city:sunnyvale,addr:housenumber:580,addr:...   \n",
       "8051  addr:city:sunnyvale,addr:housenumber:479,addr:...   \n",
       "9079  addr:city:sunnyvale,addr:housenumber:580,addr:...   \n",
       "\n",
       "                                                 points  \n",
       "1583                       [(-122.1427342, 37.4203955)]  \n",
       "1802                       [(-122.0348812, 37.3893634)]  \n",
       "1804                       [(-122.0350464, 37.3916454)]  \n",
       "1805                        [(-122.034442, 37.3912438)]  \n",
       "1807                       [(-122.0363024, 37.3920298)]  \n",
       "1808                       [(-122.0372172, 37.3913742)]  \n",
       "1941  [(-122.0422986, 37.3927221), (-122.042388, 37....  \n",
       "1942  [(-122.0412881, 37.3924897), (-122.0412188, 37...  \n",
       "3343  [(-122.0350768, 37.3921368), (-122.0347451, 37...  \n",
       "3351  [(-122.0304461, 37.3944523), (-122.0299343, 37...  \n",
       "3353  [(-122.0336759, 37.3920608), (-122.0333962, 37...  \n",
       "3359  [(-122.0311746, 37.3945986), (-122.0308902, 37...  \n",
       "3366  [(-122.0321242, 37.3922418), (-122.0320345, 37...  \n",
       "3604  [(-122.0377083, 37.3915325), (-122.0373896, 37...  \n",
       "4558  [(-122.0460082, 37.394076), (-122.0462397, 37....  \n",
       "4560  [(-122.0468619, 37.391744), (-122.0467876, 37....  \n",
       "4561  [(-122.0477275, 37.3926406), (-122.0472586, 37...  \n",
       "4562  [(-122.0486258, 37.3922433), (-122.0486371, 37...  \n",
       "6145  [(-122.0335729, 37.3912775), (-122.0328569, 37...  \n",
       "6146  [(-122.036803, 37.392416), (-122.0366585, 37.3...  \n",
       "8051  [(-122.0352514, 37.3897049), (-122.0351983, 37...  \n",
       "9079  [(-122.0346382, 37.391521), (-122.0346606, 37....  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_osm[df_osm.apply(lambda x:'linkedin ' in x['name'] or 'linkedin' in x['tags'], axis=1)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "06142d77",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>type</th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>tag_key</th>\n",
       "      <th>tag_val</th>\n",
       "      <th>is_building</th>\n",
       "      <th>building_tag</th>\n",
       "      <th>tags</th>\n",
       "      <th>points</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [type, id, name, tag_key, tag_val, is_building, building_tag, tags, points]\n",
       "Index: []"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_osm[df_osm['id']==512681243]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e930cf49",
   "metadata": {
    "scrolled": false
   },
   "source": [
    "<h1>Lets do some dedupping if we think its necessary, if a WAY has a node that is already included in the data set, remove the WAY</h1>\n",
    "Note: this may under estimate the num of offices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b3d323bd",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "dup_way_and_nodes = []\n",
    "for _, way in df_osm[df_osm['type'] == 'way'].iterrows():\n",
    "#     if way.id != 512681243:\n",
    "#         continue\n",
    "    if len(way.points) < 3:\n",
    "        continue\n",
    "    for _, node in df_osm[df_osm['type'] == 'node'].iterrows():\n",
    "#         if node.id != 10653099469:\n",
    "#             continue\n",
    "        polygon = Polygon(way.points)\n",
    "        point = Point(node.points[0][0], node.points[0][1])\n",
    "        if polygon.contains(point):\n",
    "            dup_way_and_nodes.append([way.id, node.id])\n",
    "df_way_and_nodes = pd.DataFrame(dup_way_and_nodes, columns=['wid', 'nid'], dtype='int')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ded58f1a",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_way_and_nodes[df_way_and_nodes['wid']==264624898]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7f3aa568",
   "metadata": {},
   "outputs": [],
   "source": [
    "dup_ways = set(df_way_and_nodes['wid'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "93eac01b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_offices = df_osm[\n",
    "    df_osm[['type','id']].apply(\n",
    "        lambda x:True if x['type']=='node' else True if x['id'] not in dup_ways else False, \n",
    "        axis=1\n",
    "    )\n",
    "]\n",
    "df_offices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b6ba6cb4",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_offices[df_offices.apply(lambda x:'linkedin' in x['name'] or 'linkedin' in x['tags'], axis=1)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c138c839",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}