home       Inhaltsverzeichnis
erste Version am 03.03.2019
letzte Änderung am 14.03.2019

BigData-Spielchen, Seite 3


Highscore für Straßennamen

Wo ich nun schon mal bei Straßennamen war, will ich damit mal etwas Statistik machen.
Im Abschnitt Struktur der Daten hatte ich ja bereits herausgefunden, dass es in Deutschland offenbar einige Straßen mit dem Namen "Moorstraße" gibt. Also könnte man ja mal eine Highscore-Liste für die am häufigsten vorkommenden Straßennamen erstellen.
Der Query läuft nur gegen die Tabelle tags und liefert folgendes Ergebnis (Bild anklicken für volle Größe):
Screenshot SQLiteManager
Da lag ich mit meiner Annahme, dass die Namen "Schulstraße" und "Dorfstraße" ganz weit oben stehen werden, ja schon ziemlich richtig.
Allerdings sind die Werte unter "anzahl_vorkommen" etwas arg hoch und können so eigentlich nicht stimmen.

Offensichtlich war ich nicht der Erste, der die Idee für einen Straßennamen-Highscore hatte. Hier ist eine (von vielen) Seiten zu dem Thema zu finden. Deren Top-20-Rangfolge deckt sich nicht ganz mit meinem Ergebnis - im Großen und Ganzen passt es aber trotzdem.
Und diese sehr interessante Seite zeigt sogar schon genau das an, was ich mir als nächste Aufgabe vorgestellt hatte. Nämlich....


gleichnamige Straßen auf der OSM-Deutschlandkarte darstellen

Der Grund für die viel zu hohen Werte unter "anzahl_vorkommen" war schnell gefunden: ein way in OSM ist nicht immer eine komplette Straße. Vielmehr besteht eine reale Straße oft aus diversen Abschnitten, die dann jeweils ihre eigene way_id bekommen. Speziell große bzw. lange Straßen bestehen aus besonders vielen Abschitten. Das erklärt dann auch, warum meine Vorkommens-Anzahl gerade bei "Hauptstraße" so extrem von den verlinkten Highscore-Werten abweicht. Schließlich wird die "Hauptstraße" selbst im letzten Kuhdorf immer noch die dortig längste Straßen sein....

Mein erster Versuch zur Zusammenführung von Straßen-Abschnitten hat die Tabelle relations herangezogen. Leider musste ich das wieder verwerfen, weil teilweise gleichnamige Straßen einen gemeinsamen relation-Satz hatten, obwohl die Straßen über 100km voneinander entfernt waren. Gleichzeitig hatten gleichnamige Straßen keinen gemeinsamen Relation-Satz, obwohl sie nur durch z.B. Bahngleise voneinander getrennt waren.

Beim zweiten Ansatz habe ich ausschließlich die äußeren lat/lon-Koordinaten jeder Straße herangezogen. Sobald sich die jeweils äußeren Koordinaten zweier gleichnamiger Straßen überschneiden (oder zumindest sehr nah beieinander liegen), werden die beiden Teil-Straßen zu einer Gesamt-Straße verschmolzen. Diese Verschmelzung wird solange wiederholt, bis keine Verschmelzungen mehr möglich sind.

Das zugehörige Script ist dieses:
#!/usr/bin/env python3
# -*- coding: utf-8 -*-

# ###########################################################
# OSMstrassen.py
#
# Detlev Ahlgrimm, 2019
#
# 26.02.2019 erste Version

import sqlite3
import os
import sys
import time
import numpy as np

DATABASENAME=os.path.join("/2TB", "OSM_bigdata.sqlite")

connection=sqlite3.connect(DATABASENAME)


# ###########################################################
#
class StrasseExemplar():
def __init__(self):
self.is_merged=False
self.number=[] # eine fortlaufende Nummer für Debugging-Zwecke
self.way_ids=set() # die way_id's
self.coordinates=None # np.empty((0, 2), float)
self.data=None
self.minll=None
self.maxll=None

# ###########################################################
#
def calcRect(self):
self.minll=np.amin(self.coordinates, axis=0)
self.maxll=np.amax(self.coordinates, axis=0)


# ###########################################################
#
class Strasse():
def __init__(self, str_name):
self.str_name=str_name # der Name der Straße
self.exemplare=list()

strassen=self.GetWayIdByStreetName(str_name)
print("Anzahl Sätze:", len(strassen))
number=0
for strasse in strassen:
e=StrasseExemplar()
e.number=[number]
number+=1
e.way_ids.add(strasse)
e.coordinates, e.data=self.GetAllNodeCoordinatesForWayIds([strasse])
e.calcRect()
self.exemplare.append(e)
print(" %d \r"%(number), end="")
print()

cnt=0
merged=True
while merged:
cnt+=1
print("%d. Verschmelzung "%(cnt,))
merged=self.__merge()


# ###########################################################
#
def GetWayIdByStreetName(self, name_street):
c=connection.cursor()
c.execute('select distinct t1.way_id from tags t1, tags t2 where t1.way_id=t2.way_id and t1.way_id is not null and (t1.k="highway" or t1.v="highway") and t2.k="name" and t2.v=?', (name_street,))
rows=c.fetchall()
return [ids[0] for ids in rows]

# ###########################################################
#
def GetAllNodeCoordinatesForWayIds(self, way_ids):
c=connection.cursor()
sql='select distinct ref, lat, lon from nodes, nds where ref=nodes.id and way_id in ({seq})'.format(seq=','.join(['?']*len(way_ids)))
c.execute(sql, way_ids)
rows=c.fetchall()
c.close()

lldl=np.empty((len(rows), 2), float)
data=[]
for rowi in range(len(rows)):
wid, lat, lon=rows[rowi]
lldl[rowi]=[lat, lon]
data.append(wid)
return (lldl, data)

# ###########################################################
# https://codereview.stackexchange.com/a/31564
def __intersect(self, s1, s2, fix=0.001):
#fix=0.001
hoverlaps=(s1.minll[1]-fix <= s2.maxll[1]) and (s1.maxll[1]+fix >= s2.minll[1])
voverlaps=(s1.maxll[0]+fix >= s2.minll[0]) and (s1.minll[0]-fix <= s2.maxll[0])
return hoverlaps and voverlaps
#fix=0.001
if (s1.minll[0]-fix <= s2.minll[0] <= s1.maxll[0]+fix or s1.minll[0]-fix <= s2.maxll[0] <= s1.maxll[0]+fix) and \
(s1.minll[1]-fix <= s2.minll[1] <= s1.maxll[1]+fix or s1.minll[1]-fix <= s2.maxll[1] <= s1.maxll[1]+fix):
return True
return False

# ###########################################################
#
def __merge(self):
fix=0.001
del_cnt=0
for idxA in range(len(self.exemplare)-1):
if self.exemplare[idxA].is_merged:
continue
print(" %d ( %d ) \r"%(idxA, del_cnt), end="")
for idxI in range(idxA+1, len(self.exemplare)):
if self.exemplare[idxI].is_merged:
continue
if self.__intersect(self.exemplare[idxA], self.exemplare[idxI], fix):
self.exemplare[idxA].number.extend(self.exemplare[idxI].number)
self.exemplare[idxA].way_ids|=self.exemplare[idxI].way_ids
self.exemplare[idxA].minll=np.amin(np.append([self.exemplare[idxA].minll], [self.exemplare[idxI].minll], axis=0), axis=0)
self.exemplare[idxA].maxll=np.amax(np.append([self.exemplare[idxA].maxll], [self.exemplare[idxI].maxll], axis=0), axis=0)
self.exemplare[idxI].is_merged=True
del_cnt+=1
return del_cnt>0

# ###########################################################
#
def __merge2(self):
merged=False
del_idx=[]
# print("len(self.exemplare)=", len(self.exemplare))
for idxA in range(len(self.exemplare)-1):
if idxA in del_idx:
continue
print(" %d ( %d ) \r"%(idxA, len(del_idx)), end="")
for idxI in range(idxA+1, len(self.exemplare)):
if idxI in del_idx:
continue
if self.__intersect(self.exemplare[idxA], self.exemplare[idxI]):
merged=True
self.exemplare[idxA].number.extend(self.exemplare[idxI].number)
self.exemplare[idxA].way_ids|=self.exemplare[idxI].way_ids
self.exemplare[idxA].coordinates=np.append(self.exemplare[idxA].coordinates, self.exemplare[idxI].coordinates, axis=0)
self.exemplare[idxA].data.extend(self.exemplare[idxI].data)
self.exemplare[idxA].calcRect()
del_idx.append(idxI)
for di in sorted(del_idx, reverse=True):
del self.exemplare[di]
return merged



# ###########################################################
#
def AreaForWay(self, way_ids):
c=connection.cursor()
sql='select min(n.lat), min(n.lon), max(n.lat), max(n.lon) from nds x, nodes n where x.way_id in ({seq}) and x.ref=n.id'.format(seq=','.join(['?']*len(way_ids)))
print("sql=", sql)
c.execute(sql, way_ids)
return c.fetchone()


# ###########################################################
#
def writeGPX(npll, data, fn="tst.gpx"):
latm, lonm=np.mean(npll, axis=0)
#latmax, lonmax=np.amax(npll, axis=0)
with open(fn, "w") as fl:
fl.write('<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>\n'
'<gpx version="1.1" creator="RasPi Tracker" xmlns="http://www.topografix.com/GPX/1/1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.topografix.com/GPX/1/1 http://www.topografix.com/GPX/1/1/gpx.xsd">\n'
' <trk>\n'
' <trkseg>\n'
' <trkpt lat="%f" lon="%f"></trkpt>\n'
' <trkpt lat="%f" lon="%f"></trkpt>\n'
' </trkseg>\n'
' </trk>\n'%(latm-0.01, lonm-0.01, latm+0.01, lonm+0.01))
for idx in range(len(data)):
fl.write(' <wpt lat="{}" lon="{}"><desc>{}</desc></wpt>\n'.format(npll[idx][0], npll[idx][1], data[idx]))
fl.write('</gpx>\n')

t1=time.time()

#strasse=Strasse("Moorstraße") # 113 von 316
#strasse=Strasse("Kiefernweg") # 1162 von 1796
#strasse=Strasse("Rheinstraße") # 453 von 2191
#strasse=Strasse("Jägerstieg") # 33 von 62
#strasse=Strasse("Hauptstraße") # 6590 von 36347
#strasse=Strasse("Feldstraße") # 1791 von 3680
#strasse=Strasse("Talstraße") # 1367 von 4566
strasse=Strasse("Schulstraße") # 5151 von 11131

t2=time.time()

lldl=np.empty((0, 2), float)
data=[]
print()
ucnt=0
for e in strasse.exemplare:
#print(e.number)
#print(e.coordinates)
#print()
if not e.is_merged:
ucnt+=1
lldl=np.append(lldl, e.coordinates, axis=0)
data.extend(e.data)
#print(e.way_ids)
#print(e.coordinates)
#print(e.data)
#print(e.minll, e.maxll)
#print("-"*40)
print(lldl)
print(ucnt, len(strasse.exemplare))
fn="OSM_%s.gpx"%(strasse.str_name,)
print(fn)
print("Laufzeit:", t2-t1, "Sekunden")
writeGPX(lldl, data, fn)
sys.exit()


Ein Lauf für "Schulstraße" liefert folgende Terminal-Anzeige:
dede@i5:~> ./OSMstrassen.py
Anzahl Sätze: 11131
 11131      
1. Verschmelzung           
2. Verschmelzung           
3. Verschmelzung           
4. Verschmelzung           
     11101  ( 0 )    
[[ 48.3781091  10.786932 ]
 [ 48.3781539  10.7869173]
 [ 48.3781919  10.7869184]
 ...,
 [ 51.5723688  14.7153892]
 [ 51.5709397  14.7134043]
 [ 51.5704085  14.7126849]]
5151 11131
OSM_Schulstraße.gpx
Laufzeit: 318.4679944515228 Sekunden

...und erzeugt nach 318 Sekunden bzw. etwas über fünf Minuten eine GPX-Datei, bei der jede Koordinate jeder Schulstraße mit einem <wpt> dargestellt wird.
Durch die Verschmelzung wurden aus ursprünglich 11.131 Schulstraßen-Abschnitten final 5.151 Schulstraßen.
In wxOSM sieht das dann so aus:
Screenshot wxOSM - alle Straßen namens "Schulstraße"
Also: weitestgehende Gleichverteilung von Schulstraßen in Deutschland.  Nur nördlich von Berlin hat man es scheinbar nicht so mit Schulen ;-)
Aber wegen der Gleichverteilung eigentlich auch langweilig.
Spannender ist es bei der Talstraße:
Screenshot wxOSM - Talstraße
Eine eindeutige Häufung im Süden.....welch bergiges Wunder ;-)
Und anders herum sieht es bei der Moorstraße aus:
Screenshot wxOSM - Moorstraße
Offenbar gibt es im Norden mehr Moore.
In der Nähe von Süderbrarup gibt es sogar zwei Moorstraßen, die nur 2.6km auseinander liegen - aber deutlich nicht eine Straße darstellen:
Screenshot wxOSM - Moorstraße 2
Hoffentlich sind die beiden Straßen dann wenigstens per Postleitzahl unterscheidbar....sonst wird das sicher sehr lustig für den Postboten :-)


der Kurs ist um

Nur der Vollständigkeit halber sei noch erwähnt, dass der Kurs mittlerweile beendet ist. Aus Gründen der zur Verfügung stehenden Zeit und auch wegen der Vorgabe, das Endergebnis als Jupyter-Notebook abgeben zu müssen, habe ich mich gegen OSM und für eine Untersuchung der IMDB entschieden.
Das war zwar nur begrenzt spannend, passte allerdings deutlich besser zu den Werkzeugen, die wir in den letzten zwei Tagen vor Projektstart noch schnell kennengelernt hatten. Speziell Pandas natürlich.
Naja, mein Abschluss-Zertifikat hat dann auch bestätigt, dass das wohl die richtige Entscheidung war: ich habe 99 von 100 Punkten erreicht.
Wofür auch immer er mir den einen Punkt abgezogen haben mag.....

Wie eigentlich bereits am ersten Tag erwartet (unmittelbar nach der Vorstellungsrunde), war der Kurs für mich ein kompletter Reinfall.
22 Kursteilnehmer, von denen einige offenbar noch nie ein Terminal-Fenster auf ihrem Rechner geöffnet hatten. SQL, oder relationale Datenbanken im Allgemeinen, war für die meisten Leute Neuland. Mit Python sah es ähnlich aus. Sogar das Konstrukt Programm-Schleife war nicht allen geläufig....
Bei einem derartigen Delta der Vorkenntnisse konnte das nix werden. Auch hatte der Dozent vom Veranstalter die Pflichtvorgabe bekommen, definierte Kapitel in dem Buch "Einstieg in Python - Ideal für Programmiereinsteiger" abzuarbeiten, das an alle Teilnehmer ausgegeben worden war.
Als hätte da nicht ursprünglich unter Kursvoraussetzungen gestanden, dass Kenntnisse in SQL und Python erwartet würden. Man möchte brechen!

Immerhin habe ich den Kurs aus meinem Homeoffice absolvieren können. Hätte ich hingegen täglich in so ein Schulungszentrum fahren müssen, um dort acht Stunden lang dieses Erstsemester-Niveau über mich ergehen zu lassen, wäre ich wahrscheinlich sehr bald sehr unleidlich geworden.

So...und wer ist nun Schuld gewesen?
Sicherlich hauptsächlich der Veranstalter, der viel zu viele Teilnehmer mit viel zu unterschiedlichem Vorwissen in den Kurs gestopft hat. Für den waren das leicht verdiente 33.000€ - abzüglich des Salärs von vielleicht 5.000€ für den freiberuflichen Dozenten.
Der Dozent hat sich zwar mehrfach über die -für ihn- viel zu kurze Kurs-Vorbereitungszeit von drei Wochen beschwert, andererseits hätte er den Dozenten-Job ja auch nicht annehmen müssen. So richtig tief im Thema war er jedenfalls nicht.
Immerhin war sein gebrochenes Deutsch sehr viel erträglicher als das seiner Kollegin, die uns in der zweiten Woche die noSQL-Datenbanken näherbringen sollte. Bei ihr begann (gefühlt) jeder dritte Satz mit der Entschuldigung "ich nicht wisse richtig Wort in deutsch....du mich helfe könne?". Dieses Gestammel hat reichlich Zeit gekostet....speziell, wenn sie Fragen verstehen und darauf antworten sollte.
Auch hat sie sicher nicht drei Wochen für die Vorbereitung aufgewendet. Immer wieder hat sie ihr Präsentations-Dokument pausiert, um ad hoc zusätzliche Bilder aus dem Internet zu laden und zeigen. Der Praxis-Teil lief weitestgehend so ab, dass sie die URL zur jeweiligen Command-Reference gezeigt hat. Und dann hieß es: "Jeder sucht sich jetzt mal die Syntax für die Insert-, Select-, Update- und Delete-Statements raus und führt jedes Statement einmal aus."

Fazit: in 20 Tagen soviel gelernt, wie ich mir an zwei Tagen selbst bzw. alleine hätte anlesen können.

Mal schauen, ob ich meine Spielchen mit OSM irgendwann noch fortsetzen werde.