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:
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:
Für Straßen funktioniert es ebenso:
Oder auch für einen See:
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.