home       Inhaltsverzeichnis
erste Version am 21.02.2019
letzte Änderung am 25.02.2019

BigData-Spielchen, Seite 2


ERM

Damit ich bzgl. Hierarchie und Spalten-Namen zukünftig nicht weiterhin an zwei Stellen nachsehen muss, habe ich eben erstmal ein ERM gezeichnet:
ERM

Mindestens die 1-n-Beziehung an users stimmt derzeit noch nicht - die Spalte users.uid ist ja noch non-unique befüllt.
Weiterhin fehlen einige Beziehungen. Mindestens an members.ref.

Um die nds.ref-nodes.id-Beziehung abzuschließen, habe ich den Query:
cursor.execute('select count(ref) from nds where ref not in (select id from nodes)')
laufen lassen. Er liefert:
(0,)
Somit gibt es für jeden Wert in nds.ref einen identischen Wert in nodes.id - bzw. wird mit nds.ref ausschließlich die Spalte nodes.id referenziert.
Hingegen liefert:
cursor.execute('select count(id) from nodes where id not in (select ref from nds)')
den Wert:
(7.626.723,)
Will heißen: 7.6 Millionen von knapp 300 Millionen nodes-Sätze haben keine Referenz in nds.
Das ist also die Anzahl der nodes, die zu keinem way gehören.
Allerdings liefert auch der Query
cursor.execute('select * from nodes where not exists (select * from nds where ref=nodes.id) and not exists (select * from tags where node_id=nodes.id) and not exists (select * from members where ref=nodes.id)')
diverse Sätze. Und das wären nach meinem derzeitigen Verständnis alles nodes, die weder einem way, noch einer relation zugeordnet sind und an denen auch keine tags hängen. Welchen Sinn auch immer ein derartiger node haben mag....

Ganz speziell sieht es bei members.ref aus. Die Spalte members.type enthält ausschließlich die drei Werte: 'way', 'node', 'relation'.
Die folgende Tabelle gibt die Anzahl der existenten bzw. nicht existenten Satzbeziehungen wider:

members.ref in <type>.id
members.ref not in <type>.id
type="way" 10.226.180
503.573
type="node"
1.412.017
21.819
type="relation"
73.967
16.386
Summe
11.712.164 541.778
Die Summe der sechs Werte stimmt mit der Gesamtzahl der Sätze in members überein:
10.226.180 + 503.573 + 1.412.017 + 21.819 + 73.967 + 16.386 = 12.253.942
Es stellt sich allerdings die Frage, was der Wert members.ref aussagt, wenn er nicht ID in der Ziel-Tabelle ist? Oder sind das vielleicht einfach Leichen!?
Der Query:
cursor.execute('select count(id) from members where ref in (select id from nodes) or ref in (select id from relations) or ref in (select id from ways)')
liefert:
(11.733.510,)
Folglich gibt es 11.733.510-11.712.164=21.346 Sätze, die einen Partner in einer anderen Tabelle, als in members.type angegeben, haben.
Wobei das natürlich auch zufällig identische IDs sein können. Zumindest in den drei Haupttabellen (nodes, relations und ways) sind die IDs nur pro Tabelle unique.


Objekte finden

Nun möchte ich zu einer Koordinate (in der Tabelle nodes) die zugehörigen Koordinaten ermitteln.
Ich nehme mal wieder mein Haus. Jedoch will ich diesmal kein Rechteck übergeben. Ein einzelner Punkt muss reichen.
Leider kennt SQL keinen Operator wie NEAREST ... aber dank stackoverfow.com hatte ich trotzdem zeitnah eine Lösung:
# ###########################################################
# based on: https://stackoverflow.com/a/7261601/3588613
def findNearestNode(lat, lon, r=0.0001):
while r<1:
cursor.execute('select id, lat, lon from nodes'
' where lat between ? and ? and lon between ? and ?'
' order by ((?-lat)*(?-lat) + (?-lon)*(?-lon)) limit 1',
(lat-r, lat+r, lon-r, lon+r, lat, lat, lon, lon))
row=cursor.fetchall()
if len(row)>0:
return row
r*=2
return ""

connection=sqlite3.connect(DATABASENAME)
cursor=connection.cursor()

lat, lon=54.805026042962, 9.524888992310
for ln in findNearestNode(lat, lon):
print(ln)


Die Funktion liefert ihr Ergebnis in einem Sekundenbruchteil:
(4807612353, 54.8050964, 9.5249838)

Und auch der Query, der die restlichen Ecken des Objektes zusammensucht, ist blitzschnell fertig:
cursor.execute('select lat, lon, id from nodes where id in (select ref from nds where way_id in (select way_id from nds where ref=?))', (nodes_id,))
Liefert dieses Bild:
Screenshot wxOSM - Ecken des Objekts
Für Straßen funktioniert es ebenso:
Screenshot wxOSM - eine Straße
Oder auch für einen See:
Screenshot wxOSM - unser See in der Zone

Das zugehörige Script sieht so aus:
connection=sqlite3.connect(DATABASENAME)
cursor=connection.cursor()

lat, lon=53.273990546129, 13.420143127441
data=findNearestNode(lat, lon, 0.001)[0]
nid, *junk=data

cursor.execute('select lat, lon, id from nodes where id in (select ref from nds where way_id in (select way_id from nds where ref=?))', (nid,))
row=cursor.fetchone()

print('<?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'
' </trkseg>\n'
' </trk>'%(lat, lon))
while row:
print(' <wpt lat="{}" lon="{}"><desc>{}</desc></wpt>'.format(row[0], row[1], row[2]))
row=cursor.fetchone()
print('</gpx>\n')

Und wird aufgerufen als:
dede@i5:~> ./testDB_OSM.py >tst.gpx

Die Fortsetzung folgt auf der nächsten Seite.