#!/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()
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 |