def find_multiroute(self):
"""
Find route between all points, according to given params.
"""
start = datetime.now()
for point in self.route_points:
if point.status == 'notfound':
self.status = 'notfound'
if self.status != 'notfound':
for i in range(len(self.route_points)-1):
start_point = self.route_points[i]
end_point = self.route_points[i+1]
route = Route(start_point, end_point, self.params)
route.find_best_route()
if route.status == 'notfound':
self.status = route.status
self.length += route.length
self.cost += route.cost
self.geojson_features += route.geojson()
for point in self.route_points:
point.delete_temp_ways()
if self.status == 'init':
self.status = 'success'
end = datetime.now()
print 'Find MultiRoute duration:', total_seconds(end - start)
return self.status
def _add_routes_attributes():
"""
Copy useful attributes from planet_osm_routes2, this time only osmc
"""
start = datetime.now()
cursor = connections[MAP_DB].cursor()
routes_query = """SELECT osm_id, "mtb:scale", osmcsymbol0 FROM planet_osm_routes2
WHERE (osmcsymbol0<>'mtb:white:mtb_mtb' AND osmcsymbol0 IS NOT NULL)
OR "mtb:scale" IS NOT NULL;"""
cursor.execute(routes_query)
evaluated = 0
updated = 0
while True:
routes_rows = cursor.fetchmany(100)
if not routes_rows:
break
for row in routes_rows:
osm_id = row[0]
mtbscale = None
if row[1]:
mtbscale = re.sub(r'[^0-9]', '', row[1])
if mtbscale == '':
mtbscale = None
osmc = row[2]
if osmc != 'mtb:white:mtb_mtb':
updated += Way.objects.filter(osm_id=osm_id).update(osmc=1, mtbscale=mtbscale)
else:
updated += Way.objects.filter(osm_id=osm_id).update(mtbscale=mtbscale)
evaluated += 100
if not evaluated % 1000:
logger.info('%i evaluated records...' % evaluated)
logger.info('%i osmc attrs updated successfully.' % updated)
logger.info('Time: %s' % total_seconds(datetime.now()-start))
def _add_routes_attributes():
'''
Copy useful attributes from planet_osm_routes2, this time only osmc
'''
start = datetime.now()
cursor = connections[MAP_DB].cursor()
routes_query = '''SELECT osm_id, "mtb:scale", osmcsymbol0 FROM planet_osm_routes2 WHERE (osmcsymbol0<>'mtb:white:mtb_mtb' and osmcsymbol0 is not null) or "mtb:scale" is not null;'''
cursor.execute(routes_query)
evaluated = 0
updated = 0
while True:
routes_rows = cursor.fetchmany(100)
if not routes_rows:
break
for row in routes_rows:
osm_id = row[0]
mtbscale = None
if row[1]:
mtbscale = re.sub(r'[^0-9]', '', row[1])
osmc = row[2]
if osmc != 'mtb:white:mtb_mtb':
updated += Way.objects.filter(osm_id=osm_id).update(osmc=1, mtbscale=mtbscale)
else:
updated += Way.objects.filter(osm_id=osm_id).update(mtbscale=mtbscale)
evaluated += 100
if not evaluated % 1000:
print evaluated, 'evaluated records:'
print updated, 'osmc attrs updated successfully'
print 'Time:', total_seconds(datetime.now()-start)
def _add_polygon_attributes():
"""
Copy useful attributes from planet_osm_polygon for routable areas
"""
start = datetime.now()
cursor = connections[MAP_DB].cursor()
evaluated = 0
updated = 0
# highway areas are still without attributes
null_ways_osm_ids = Way.objects.filter(highway__isnull=True).distinct('osm_id').values_list('osm_id', flat=True)
logger.info('%i IDs to be evaluated...' % len(null_ways_osm_ids))
for osm_id in null_ways_osm_ids:
query = """SELECT osm_id, tracktype, oneway, access, bicycle, foot, incline, width, surface, smoothness,
maxspeed, "mtb:scale" as mtbscale, "mtb:scale:uphill" as mtbscaleuphill, sac_scale, network, highway
FROM planet_osm_polygon WHERE osm_id=%s; """ % osm_id
cursor.execute(query)
description = cursor.description
rows = [dict(zip([col[0] for col in description], row)) for row in cursor.fetchall()]
for row in rows:
update_args = _row_to_arguments(row)
updated += Way.objects.filter(osm_id=osm_id).update(**update_args)
evaluated += 1
if not evaluated % 1000:
logger.info('%i evaluated ways...' % evaluated)
cursor.close()
logger.info('%i ways (areas) updated successfully.' % updated)
logger.info('Time: %s' % total_seconds(datetime.now()-start))
def _add_routes_attributes():
"""
Copy useful attributes from planet_osm_routes2, this time only osmc
"""
start = datetime.now()
cursor = connections[MAP_DB].cursor()
routes_query = """SELECT osm_id, "mtb:scale", osmcsymbol0 FROM planet_osm_routes2
WHERE (osmcsymbol0<>'mtb:white:mtb_mtb' AND osmcsymbol0 IS NOT NULL)
OR "mtb:scale" IS NOT NULL;"""
cursor.execute(routes_query)
evaluated = 0
updated = 0
while True:
routes_rows = cursor.fetchmany(100)
if not routes_rows:
break
for row in routes_rows:
osm_id = row[0]
mtbscale = None
if row[1]:
mtbscale = re.sub(r'[^0-9]', '', row[1])
if mtbscale == '':
mtbscale = None
osmc = row[2]
if osmc != 'mtb:white:mtb_mtb':
updated += Way.objects.filter(osm_id=osm_id).update(
osmc=1, mtbscale=mtbscale)
else:
updated += Way.objects.filter(osm_id=osm_id).update(
mtbscale=mtbscale)
evaluated += 100
if not evaluated % 1000:
logger.info('%i evaluated records...' % evaluated)
logger.info('%i osmc attrs updated successfully.' % updated)
logger.info('Time: %s' % total_seconds(datetime.now() - start))
def _add_polygon_attributes():
"""
Copy useful attributes from planet_osm_polygon for routable areas
"""
start = datetime.now()
cursor = connections[MAP_DB].cursor()
evaluated = 0
updated = 0
# highway areas are still without attributes
null_ways_osm_ids = Way.objects.filter(
highway__isnull=True).distinct('osm_id').values_list('osm_id',
flat=True)
logger.info('%i IDs to be evaluated...' % len(null_ways_osm_ids))
for osm_id in null_ways_osm_ids:
query = """SELECT osm_id, tracktype, oneway, access, bicycle, foot, incline, width, surface, smoothness,
maxspeed, "mtb:scale" as mtbscale, "mtb:scale:uphill" as mtbscaleuphill, sac_scale, network, highway
FROM planet_osm_polygon WHERE osm_id=%s; """ % osm_id
cursor.execute(query)
description = cursor.description
rows = [
dict(zip([col[0] for col in description], row))
for row in cursor.fetchall()
]
for row in rows:
update_args = _row_to_arguments(row)
updated += Way.objects.filter(osm_id=osm_id).update(**update_args)
evaluated += 1
if not evaluated % 1000:
logger.info('%i evaluated ways...' % evaluated)
cursor.close()
logger.info('%i ways (areas) updated successfully.' % updated)
logger.info('Time: %s' % total_seconds(datetime.now() - start))
def add_attributes():
'''
Add attributes from osm2pgsql created database to our Way objects
'''
start = datetime.now()
_update_reverse_cost()
_add_line_attributes()
_add_polygon_attributes()
_add_routes_attributes()
print 'All attributes updated successfully.'
print 'Total time:', total_seconds(datetime.now()-start)
print 'Run python vacuum_full.py again'
def add_attributes():
"""
Add attributes from osm2pgsql created database to our Way objects
"""
start = datetime.now()
_update_reverse_cost()
_add_line_attributes()
_add_polygon_attributes()
_add_routes_attributes()
logger.info('All attributes updated successfully.')
logger.info('Total time: %s' % total_seconds(datetime.now()-start))
# TODO should enable running VACUUM automatically
logger.info('Run VACUUM FULL to speed up routing.')
def add_attributes():
"""
Add attributes from osm2pgsql created database to our Way objects
"""
start = datetime.now()
_update_reverse_cost()
_add_line_attributes()
_add_polygon_attributes()
_add_routes_attributes()
logger.info('All attributes updated successfully.')
logger.info('Total time: %s' % total_seconds(datetime.now() - start))
# TODO should enable running VACUUM automatically
logger.info('Run VACUUM FULL to speed up routing.')
def copy_ways():
'''
copy data generated with osm2po to routing_way table
'''
start = datetime.now()
cursor = connections[MAP_DB].cursor()
cursor.execute('DELETE FROM routing_way')
insert = """
insert into routing_way (class_id, length, name, x1, y1, x2, y2, reverse_cost, osm_id, source, target, the_geom)
select clazz, km, osm_name, x1, y1, x2, y2, reverse_cost, osm_id, source, target, geom_way
from osm_2po_4pgr
"""
cursor.execute(insert)
transaction.commit_unless_managed(using=MAP_DB)
count = Way.objects.all().count()
print count, " ways inserted successfully"
print 'Total time:', total_seconds(datetime.now()-start)
print 'Run python vacuum_full.py now'
def _add_line_attributes():
"""
Copy useful attributes from planet_osm_line
"""
start = datetime.now()
cursor = connections[MAP_DB].cursor()
column_bicycle = """CASE WHEN (cycleway IN ('opposite','opposite_lane')
OR "oneway:bicycle"='no')
AND oneway IN ('yes','-1')
THEN 'opposite'
ELSE bicycle
END AS bicycle"""
column_class_bicycle = """CASE WHEN ("class:bicycle:touring" IS NOT NULL) THEN "class:bicycle:touring"
ELSE "class:bicycle"
END AS class_bicycle"""
query = """SELECT osm_id, tracktype, oneway, access, %s, foot, incline,
width, surface, smoothness, maxspeed,
"mtb:scale:uphill" as mtbscaleuphill, sac_scale, network,
replace(highway, '_link', '') AS highway, %s, "class:bicycle:mtb" as class_mtb,
"class:bicycle:mtb:technical" as class_mtb_technical
FROM planet_osm_line
WHERE osm_id>0; """ % (column_bicycle, column_class_bicycle)
cursor.execute(query)
description = cursor.description
evaluated = 0
updated = 0
while True:
lines_rows = [
dict(zip([col[0] for col in description], row))
for row in cursor.fetchmany(100)
]
if not lines_rows:
break
for row in lines_rows:
osm_id = row['osm_id']
update_args = _row_to_arguments(row)
updated += Way.objects.filter(osm_id=osm_id).update(**update_args)
evaluated += 100
if not evaluated % 1000:
logger.info('%i evaluated ways...' % evaluated)
cursor.close()
logger.info('%i ways (lines) updated successfully, %i lines evaluated.' %
(updated, evaluated))
logger.info('Time: %s' % total_seconds(datetime.now() - start))
def _add_line_attributes():
"""
Copy useful attributes from planet_osm_line
"""
start = datetime.now()
cursor = connections[MAP_DB].cursor()
column_bicycle = """CASE WHEN (cycleway IN ('opposite','opposite_lane')
OR "oneway:bicycle"='no')
AND oneway IN ('yes','-1')
THEN 'opposite'
ELSE bicycle
END AS bicycle"""
column_class_bicycle = """CASE WHEN ("class:bicycle:touring" IS NOT NULL) THEN "class:bicycle:touring"
ELSE "class:bicycle"
END AS class_bicycle"""
query = """SELECT osm_id, tracktype, oneway, access, %s, foot, incline,
width, surface, smoothness, maxspeed,
"mtb:scale:uphill" as mtbscaleuphill, sac_scale, network,
replace(highway, '_link', '') AS highway, %s, "class:bicycle:mtb" as class_mtb,
"class:bicycle:mtb:technical" as class_mtb_technical
FROM planet_osm_line
WHERE osm_id>0; """ % (column_bicycle, column_class_bicycle)
cursor.execute(query)
description = cursor.description
evaluated = 0
updated = 0
while True:
lines_rows = [dict(zip([col[0] for col in description], row)) for row in cursor.fetchmany(100)]
if not lines_rows:
break
for row in lines_rows:
osm_id = row['osm_id']
update_args = _row_to_arguments(row)
updated += Way.objects.filter(osm_id=osm_id).update(**update_args)
evaluated += 100
if not evaluated % 1000:
logger.info('%i evaluated ways...' % evaluated)
cursor.close()
logger.info('%i ways (lines) updated successfully, %i lines evaluated.' % (updated, evaluated))
logger.info('Time: %s' % total_seconds(datetime.now()-start))
def copy_ways():
"""
copy data generated with osm2po to routing_way table
"""
start = datetime.now()
cursor = connections[MAP_DB].cursor()
cursor.execute('DELETE FROM routing_way')
insert = """
insert into routing_way (class_id, length, name, x1, y1, x2, y2, reverse_cost, osm_id, source, target, the_geom)
select clazz, km,
case when osm_name is null then '' else osm_name end as osm_name,
x1, y1, x2, y2, reverse_cost, osm_id, source, target, geom_way
from osm_2po_4pgr
"""
cursor.execute(insert)
transaction.commit_unless_managed(using=MAP_DB)
count = Way.objects.all().count()
logger.info('%i ways inserted successfully' % count)
logger.info('Total time: %s' % total_seconds(datetime.now()-start))
# TODO should enable running VACUUM automatically
logger.info('Run VACUUM FULL to speed up other processing.')
def copy_ways():
"""
copy data generated with osm2po to routing_way table
"""
start = datetime.now()
cursor = connections[MAP_DB].cursor()
cursor.execute('DELETE FROM routing_way')
insert = """
insert into routing_way (class_id, length, name, x1, y1, x2, y2, reverse_cost, osm_id, source, target, the_geom)
select clazz, km,
case when osm_name is null then '' else osm_name end as osm_name,
x1, y1, x2, y2, reverse_cost, osm_id, source, target, geom_way
from osm_2po_4pgr
"""
cursor.execute(insert)
transaction.commit_unless_managed(using=MAP_DB)
count = Way.objects.all().count()
logger.info('%i ways inserted successfully' % count)
logger.info('Total time: %s' % total_seconds(datetime.now() - start))
# TODO should enable running VACUUM automatically
logger.info('Run VACUUM FULL to speed up other processing.')