<jupyter_start><jupyter_text>Create car street table from ways, with unwind order and lengths 🚗 🚗 🚗This notebook assembles a table of streets from already derived ways, determining street components and paths (unwind order) and street lengths.  **Derived streets is dropped and re-inserted!** Created on:  2016-11-15  Last update: 2017-01-22  Contact: <EMAIL>, <EMAIL> (<NAME>)  Preliminaries  Parameters<jupyter_code>cityname = "amsterdam"<jupyter_output><empty_output><jupyter_text>Imports<jupyter_code>from __future__ import unicode_literals
import sys
import csv
import os
import math
import pprint
pp = pprint.PrettyPrinter(indent=4)
import requests
import gzip
from collections import defaultdict
import time
import datetime
import numpy as np
from scipy import stats
import pyprind
import itertools
import logging
from ast import literal_eval as make_tuple
from collections import OrderedDict
from retrying import retry

import osmnx as ox
import networkx as nx
import json
from shapely.geometry import mapping, shape, LineString, LinearRing, Polygon, MultiPolygon
import shapely
import shapely.ops as ops
from functools import partial
import pyproj
from scipy import spatial
from haversine import haversine

import pymongo
from pymongo import MongoClient

# plotting stuff
import matplotlib.pyplot as plt

with open("citydata/" + cityname +"_streetnames.txt", "r") as ins:
    streetnames = []
    for line in ins:
        streetnames.append(line.rstrip('\n'))<jupyter_output><empty_output><jupyter_text>DB Connection<jupyter_code>client = MongoClient()
db_raw = client[cityname+'_raw']
ways_raw = db_raw['ways']
nodes_raw = db_raw['nodes']
db_derived = client[cityname+'_derived']
streets = db_derived['streets']
ways_derived = db_derived['ways']<jupyter_output><empty_output><jupyter_text>Functions<jupyter_code>def file_len(fname): # http://stackoverflow.com/questions/845058/how-to-get-line-count-cheaply-in-python
    with open(fname) as f:
        for i, l in enumerate(f):
            pass
    return i + 1

def assembleStreet(ways_raw, ways_derived, nodes_raw, streetname = "Achillesstraße", drawit = False, debug = False):
    oxelements = []
    elemnodesint = set()
    elemnodesint_real = set()
    elemways = []
    elemnodes = []
    nodesdict = {}
    lanes = 0
    # We include service roads if they have the same name
    for i,way in enumerate(ways_raw.find({"$and": [{"tags.name.name":streetname},{"$or": [{"tags.highway.highway":"service"},{"tags.highway.highway":"residential"},{"tags.highway.highway":"primary"}, {"tags.highway.highway":"secondary"}, {"tags.highway.highway":"tertiary"},{"tags.highway.highway":"unclassified"}]}]})):
        if "public_transport" not in way["tags"]:
            for n in way["nodes"]:
                elemnodesint.add(int(n))

    for n in elemnodesint:
        for nd in nodes_raw.find({"_id": n}):
            elemnodesint_real.add(int(nd["_id"]))
            elemnodes.append({"id": int(nd["_id"]), "lat": nd["loc"]["coordinates"][1], "lon": nd["loc"]["coordinates"][0], "type": "node"})
            nodesdict[str(int(nd["_id"]))] = {"lat": nd["loc"]["coordinates"][1], "lon": nd["loc"]["coordinates"][0]}

    streetlength = 0
    for i,way in enumerate(ways_raw.find({"$and": [{"tags.name.name":streetname},{"$or": [{"tags.highway.highway":"service"},{"tags.highway.highway":"residential"},{"tags.highway.highway":"primary"}, {"tags.highway.highway":"secondary"}, {"tags.highway.highway":"tertiary"},{"tags.highway.highway":"unclassified"}]}]})):
        if "public_transport" not in way["tags"]:
            wayinfo = ways_derived.find_one({"_id":int(way["_id"])})
            waylength = wayinfo["properties_derived"]["length"]
            streetlength += waylength
            if "lanes" in way["tags"]:
                try:
                    lanes = int(way["tags"]["lanes"]["lanes"])
                except:
                    pass
            elemways.append({"id": int(way["_id"]), "nodes":[int(way["nodes"][k]) for k in range(len(way["nodes"])) if int(way["nodes"][k]) in elemnodesint_real], "tags": way["tags"], "type": "way"})

    oxelements = [{"elements": elemnodes + elemways}]
    if debug:
        # Check if nodes are missing
        if len(elemnodesint_real) < len(elemnodesint):
            print(str(len(elemnodesint)-len(elemnodesint_real)) + " nodes are missing.")
        print("oxelements:")
        pp.pprint(oxelements)
        
    G = ox.create_graph(oxelements, retain_all=True)
    if drawit:
        fig, ax = ox.plot_graph(G)
    G = nx.Graph(G)
    G = G.to_undirected()
    ccs = list(nx.connected_component_subgraphs(G))

    components = []
    for c in range(len(ccs)):
        deglist = np.array(list((ccs[c].degree_iter())))
        endptindices = np.where(deglist[:, 1] == 1)
        # Look which of the endpoints lies most western, take that as the source for DFS traversal
        west = float('inf')
        source = deglist[0, 0]
        for i in list(endptindices[0]):
            westthis = nodesdict[str(deglist[i, 0])]["lon"]
            if westthis < west:
                source = deglist[i, 0]
                west = westthis

        component = []
        dfsedges = list(nx.dfs_edges(ccs[c], source))
        nend = dfsedges[0][0]
        path = [str(nend)]
        for e in dfsedges:
            if e[0] == nend: # path grows
                path.append(str(e[1]))
            else: # new path
                component.append(path)
                path = [str(e[0]), str(e[1])]
            nend = e[1]    
        component.append(path) # last path
        components.append(component) 
 
    output = {"tags":{"name":streetname, "length": streetlength, "lanes": lanes}, "components":components, "nodes":nodesdict, "ways":[elemways[k]["id"] for k in range(len(elemways))]}
    return output<jupyter_output><empty_output><jupyter_text>Assemble streets and unwind orders<jupyter_code>streets.drop()  
bar = pyprind.ProgBar(len(streetnames), bar_char='█', update_interval=1)
streetnames_error = []
for i,streetname in enumerate(streetnames):
    try:
        res = assembleStreet(ways_raw, ways_derived, nodes_raw, streetname)
        res["_id"] = i+1
        streets.update_one({"_id":res["_id"]}, {"$set":res}, upsert=True)
    except:
        streetnames_error.append(streetname)
    bar.update(item_id = streetname)
    
with open("logs/" + cityname +"_streetnameserror.txt", "w") as f: 
    for s in streetnames_error:
        f.write("%s\n" % s)<jupyter_output><empty_output><jupyter_text>Tests<jupyter_code>test = assembleStreet(ways_raw, ways_derived, nodes_raw, "Schillerstraße", True, True)
# Test case. To get its output, insert this in the above function after oxelements = [{"elements": elemnodes + elemways}]
oxelements = [{"elements": [{   'id': 1,
                            'lat': 0,
                            'lon': -1,
                            'type': 'node'},
                            {   'id': 2,
                            'lat': 0,
                            'lon': 1,
                            'type': 'node'},
                            {   'id': 3,
                            'lat': -1,
                            'lon': 1,
                            'type': 'node'},
                            {   'id': 4,
                            'lat': -2,
                            'lon': 1,
                            'type': 'node'},
                            {   'id': 5,
                            'lat': 0,
                            'lon': 2,
                            'type': 'node'},
                            {   'id': 6,
                            'lat': -1,
                            'lon': 0,
                            'type': 'node'},
                            {   'id': 7,
                            'lat': 0,
                            'lon': 3,
                            'type': 'node'},
                            {   'id': 8,
                            'lat': -1,
                            'lon': 4,
                            'type': 'node'},
                            {   'id': 91,
                            'nodes': [1, 2, 3],
                            'type': 'way'},
                            {   'id': 92,
                            'nodes': [2, 5],
                            'type': 'way'},
                            {   'id': 93,
                            'nodes': [3, 4],
                            'type': 'way'},
                            {   'id': 94,
                            'nodes': [6, 3],
                            'type': 'way'},
                            {   'id': 95,
                            'nodes': [7, 8],
                            'type': 'way'}
                           ]}]    
nodesdict = {"1":{'lat': 0, 'lon': -1}, 
            "2":{'lat': 0, 'lon': 1}, 
            "3":{'lat': -1, 'lon': 1}, 
            "4":{'lat': -2, 'lon': 1}, 
            "5":{'lat': 0, 'lon': 2}, 
            "6":{'lat': -1, 'lon': 0}, 
            "7":{'lat': 0, 'lon': 3}, 
            "8":{'lat': -1, 'lon': 4}
            }<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.15, 'fraction_numerical': 0.012637362637362638, 'mean_word_length': 1.9947351102336295, 'pattern_counts': {'":': 50, '<': 30, '<?xml version=': 0, '>': 28, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '403002', 'license': 'MIT', 'n_tokens_mistral': 2900, 'n_tokens_neox': 2765, 'n_words': 644, 'path': '04_unwindstreet.ipynb', 'repo_name': 'move-lab/lab-what-the-street-datawrangling', 'size': '9140'}