def symbols(self, **params):
from db.common.symbols import ShieldFactory
factory = ShieldFactory(
'SwissMobile',
'JelRef',
'KCTRef',
'ItalianHikingRefs',
'OSMCSymbol',
'Nordic',
'Slopes',
'ShieldImage',
'TextColorBelow',
'ColorBox',
'TextSymbol',
)
sym = factory.create(TagStore(params), params.get('_network', ''), 10)
if sym is None:
raise cherrypy.NotFound()
with NamedTemporaryFile() as f:
sym.write_image(f.name)
factory._mangle_svg(f.name)
with open(f.name, 'rb') as f:
res = f.read()
cherrypy.response.headers['Content-Type'] = 'image/svg+xml'
return res
def symbols(self, **params):
from db.common.symbols import ShieldFactory
factory = ShieldFactory(
'SwissMobile',
'JelRef',
'KCTRef',
'ItalianHikingRefs',
'OSMCSymbol',
'Nordic',
'Slopes',
'ShieldImage',
'TextColorBelow',
'ColorBox',
'TextSymbol',
)
sym = factory.create(TagStore(params), params.get('_network', ''), 10)
if sym is None:
raise cherrypy.NotFound()
with NamedTemporaryFile() as f:
sym.write_image(f.name)
factory._mangle_svg(f.name)
with open(f.name, 'rb') as f:
res = f.read()
cherrypy.response.headers['Content-Type'] = 'image/svg+xml'
return res
def __init__(self, meta, name, relations, ways, hierarchy, countries):
table = sa.Table(name, meta,
sa.Column('id', sa.BigInteger,
primary_key=True, autoincrement=False),
sa.Column('name', sa.String),
sa.Column('intnames', JSONB),
sa.Column('ref', sa.String),
sa.Column('itinary', JSONB),
sa.Column('symbol', sa.String),
sa.Column('country', sa.String(length=3)),
sa.Column('network', sa.String(length=3)),
sa.Column('level', sa.SmallInteger),
sa.Column('top', sa.Boolean),
sa.Column('geom', Geometry('GEOMETRY', srid=ways.srid)))
super().__init__(table, relations.change)
self.rels = relations
self.ways = ways
self.rtree = hierarchy
self.countries = countries
self.symbols = ShieldFactory(*ROUTE_CONF.symbols)
self.numthreads = meta.info.get('num_threads', 1)
class PisteRouteInfo(Routes):
def __init__(self, segments, hierarchy, countries):
super().__init__(CONF.route_table_name, segments, hiertable=hierarchy)
self.symbols = ShieldFactory(*CONF.symbols)
def columns(self):
cols = _create_piste_columns(CONF.route_table_name)
cols.append(Column('top', Boolean))
cols.append(Column('geom', Geometry('GEOMETRY',
srid=self.segment_table.data.c.geom.type.srid)))
return cols
def transform_tags(self, osmid, tags):
outtags, difficulty = _basic_tag_transform(osmid, tags)
# we don't support hierarchy at the moment
outtags['top'] = True
# find all relation parts
h = self.hierarchy_table.data
parts = select([h.c.child]).where(h.c.parent == osmid)
# get the geometry
s = self.segment_table.data
sel = select([func.st_linemerge(func.st_collect(s.c.geom))])\
.where(s.c.rels.op('&& ARRAY')(parts))
outtags['geom'] = self.thread.conn.scalar(sel)
outtags['symbol'] = self.symbols.create_write(tags, '', difficulty)
return outtags
class PisteWayInfo(Ways):
def __init__(self, meta, osmdata, subset=None, geom_change=None):
super().__init__(meta, CONF.way_table_name, osmdata,
subset=subset, geom_change=geom_change)
self.symbols = ShieldFactory(*CONF.symbols)
def columns(self):
return _create_piste_columns(CONF.way_table_name)
def transform_tags(self, osmid, tags):
outtags, difficulty = _basic_tag_transform(osmid, tags)
outtags['symbol'] = self.symbols.create_write(tags, '', difficulty)
return outtags
class Routes(ThreadableDBObject, TableSource):
""" Table that creates information about the routes. This includes
general information as well as the geometry.
"""
def __init__(self, meta, name, relations, ways, hierarchy, countries):
table = sa.Table(name, meta,
sa.Column('id', sa.BigInteger,
primary_key=True, autoincrement=False),
sa.Column('name', sa.String),
sa.Column('intnames', JSONB),
sa.Column('ref', sa.String),
sa.Column('itinary', JSONB),
sa.Column('symbol', sa.String),
sa.Column('country', sa.String(length=3)),
sa.Column('network', sa.String(length=3)),
sa.Column('level', sa.SmallInteger),
sa.Column('top', sa.Boolean),
sa.Column('geom', Geometry('GEOMETRY', srid=ways.srid)))
super().__init__(table, relations.change)
self.rels = relations
self.ways = ways
self.rtree = hierarchy
self.countries = countries
self.symbols = ShieldFactory(*ROUTE_CONF.symbols)
self.numthreads = meta.info.get('num_threads', 1)
def _insert_objects(self, conn, subsel=None):
h = self.rtree.data
max_depth = conn.scalar(sa.select([saf.max(h.c.depth)]))
subtab = sa.select([h.c.child, saf.max(h.c.depth).label("lvl")])\
.group_by(h.c.child).alias()
# Process relations by hierarchy, starting with the highest depth.
# This guarantees that the geometry of member relations is already
# available for processing the relation geometry.
if max_depth is not None:
for level in range(max_depth, 1, -1):
subset = self.rels.data.select()\
.where(subtab.c.lvl == level)\
.where(self.rels.c.id == subtab.c.child)
if subsel is not None:
subset = subset.where(subsel)
self.insert_objects(conn, subset)
# Lastly, process all routes that are nobody's child.
subset = self.rels.data.select()\
.where(self.rels.c.id.notin_(
sa.select([h.c.child], distinct=True).as_scalar()))
if subsel is not None:
subset = subset.where(subsel)
self.insert_objects(conn, subset)
def construct(self, engine):
h = self.rtree.data
idx = sa.Index(self.data.name + '_iname_idx', sa.func.upper(self.data.c.name))
with engine.begin() as conn:
conn.execute(DropIndexIfExists(idx))
self.truncate(conn)
max_depth = conn.scalar(sa.select([saf.max(h.c.depth)]))
subtab = sa.select([h.c.child, saf.max(h.c.depth).label("lvl")])\
.group_by(h.c.child).alias()
# Process relations by hierarchy, starting with the highest depth.
# This guarantees that the geometry of member relations is already
# available for processing the relation geometry.
if max_depth is not None:
for level in range(max_depth, 1, -1):
subset = self.rels.data.select()\
.where(subtab.c.lvl == level)\
.where(self.rels.c.id == subtab.c.child)
self.insert_objects(engine, subset)
# Lastly, process all routes that are nobody's child.
subset = self.rels.data.select()\
.where(self.rels.c.id.notin_(
sa.select([h.c.child], distinct=True).as_scalar()))
self.insert_objects(engine, subset)
with engine.begin() as conn:
idx.create(conn)
def update(self, engine):
with engine.begin() as conn:
# delete removed relations
conn.execute(self.delete(self.rels.select_delete()))
# collect all changed relations in a temporary table
# 1. relations added or modified
sels = [sa.select([self.rels.cc.id])]
# 2. relations with modified geometries
w = self.ways
sels.append(sa.select([saf.func.unnest(w.c.rels).label('id')], distinct=True)
.where(w.c.id.in_(w.select_add_modify())))
conn.execute('DROP TABLE IF EXISTS __tmp_osgende_routes_updaterels')
conn.execute(CreateTableAs('__tmp_osgende_routes_updaterels',
sa.union(*sels), temporary=False))
tmp_rels = sa.Table('__tmp_osgende_routes_updaterels',
sa.MetaData(), autoload_with=conn)
# 3. parent relation of all of them
conn.execute(tmp_rels.insert().from_select(tmp_rels.c,
sa.select([self.rtree.c.parent], distinct=True)
.where(self.rtree.c.child.in_(sa.select([tmp_rels.c.id])))))
# and insert/update all
self._insert_objects(conn, self.rels.c.id.in_(tmp_rels.select()))
tmp_rels.drop(conn)
def insert_objects(self, engine, subset):
res = engine.execution_options(stream_results=True).execute(subset)
workers = self.create_worker_queue(engine, self._process_construct_next)
for obj in res:
workers.add_task(obj)
workers.finish()
def _process_construct_next(self, obj):
cols = self._construct_row(obj, self.thread.conn)
if cols is not None:
self.thread.conn.execute(self.upsert_data().values(cols))
else:
self.thread.conn.execute(self.data.delete().where(self.c.id == obj['id']))
def _construct_row(self, obj, conn):
tags = TagStore(obj['tags'])
outtags = RouteRow(obj['id'])
# determine name and level
for k, v in tags.items():
if k in ('name', 'ref'):
outtags[k] = v
elif k.startswith('name:'):
outtags.intnames[k[5:]] = v
elif k == 'network':
outtags.level = _compute_route_level(v)
if tags.get('network:type') == 'node_network':
outtags.level = Network.LOC.min()
# child relations
relids = [ r['id'] for r in obj['members'] if r['type'] == 'R']
members = obj['members']
if len(relids) > 0:
# Is this relation part of a cycle? Then drop the relation members
# to not get us in trouble with geometry building.
h1 = self.rtree.data.alias()
h2 = self.rtree.data.alias()
sql = sa.select([h1.c.parent])\
.where(h1.c.parent == obj['id'])\
.where(h1.c.child == h2.c.parent)\
.where(h2.c.child == obj['id'])
if (self.thread.conn.execute(sql).rowcount > 0):
members = [ m for m in obj['members'] if m['type'] == 'W' ]
relids = []
# geometry
geom = build_route_geometry(conn, members, self.ways, self.data)
if geom is None:
return None
if geom.geom_type not in ('MultiLineString', 'LineString'):
raise RuntimeError("Bad geometry %s for %d" % (geom.geom_type, obj['id']))
# if the route is unsorted but linear, sort it
if geom.geom_type == 'MultiLineString':
fixed_geom = linemerge(geom)
if fixed_geom.geom_type == 'LineString':
geom = fixed_geom
outtags.geom = from_shape(geom, srid=self.data.c.geom.type.srid)
# find the country
if len(relids) > 0:
sel = sa.select([self.c.country], distinct=True)\
.where(self.c.id.in_(relids))
else:
c = self.countries
sel = sa.select([c.column_cc()], distinct=True)\
.where(c.column_geom().ST_Intersects(outtags.geom))
cur = self.thread.conn.execute(sel)
# should be counting when rowcount > 1
if cur.rowcount >= 1:
cntry = cur.scalar()
else:
cntry = None
outtags.country = cntry
outtags.symbol = self.symbols.create_write(tags, cntry, outtags.level)
# custom filter callback
if ROUTE_CONF.tag_filter is not None:
ROUTE_CONF.tag_filter(outtags, tags)
if outtags.network is None:
if tags.get('network:type') == 'node_network':
outtags.network = 'NDS'
if outtags.top is None:
if 'network' in tags and tags.get('network:type') != 'node_network':
h = self.rtree.data
r = self.rels.data
sel = sa.select([sa.text("'a'")]).where(h.c.child == obj['id'])\
.where(r.c.id == h.c.parent)\
.where(h.c.depth == 2)\
.where(r.c.tags['network'].astext == tags['network'])\
.limit(1)
top = self.thread.conn.scalar(sel)
outtags.top = (top is None)
else:
outtags.top = True
return outtags
def __init__(self, segments, hierarchy, countries):
super().__init__(ROUTE_CONF.table_name, segments, hiertable=hierarchy)
self.country_table = countries
self.symbols = ShieldFactory(*ROUTE_CONF.symbols)
class RouteInfo(Routes):
def __init__(self, segments, hierarchy, countries):
super().__init__(ROUTE_CONF.table_name, segments, hiertable=hierarchy)
self.country_table = countries
self.symbols = ShieldFactory(*ROUTE_CONF.symbols)
def columns(self):
return (Column('name', String),
Column('intnames', HSTORE),
Column('symbol', String),
Column('country', String(length=3)),
Column('network', String(length=2)),
Column('level', SmallInteger),
Column('top', Boolean),
Column('geom', Geometry('GEOMETRY',
srid=self.segment_table.data.c.geom.type.srid)),
Index('idx_%s_iname' % ROUTE_CONF.table_name, text('upper(name)'))
)
def transform_tags(self, osmid, tags):
outtags = { 'intnames' : {},
'level' : 35,
'network' : '',
'top' : None,
'geom' : None}
# determine name and level
for (k,v) in tags.items():
if k == 'name':
outtags[k] = v
elif k.startswith('name:'):
outtags['intnames'][k[5:]] = v
elif k == 'ref':
if 'name' not in outtags:
outtags['name'] = '[%s]' % v
elif k == 'network':
outtags['level'] = ROUTE_CONF.network_map.get(v, 35)
if 'name'not in outtags:
outtags['name'] = '(%s)' % osmid
# geometry
geom = self.build_geometry(osmid)
if geom is None:
return None
# if the route is unsorted but linear, sort it
if geom.geom_type == 'MultiLineString':
fixed_geom = linemerge(geom)
if fixed_geom.geom_type == 'LineString':
geom = fixed_geom
outtags['geom'] = from_shape(geom, srid=self.data.c.geom.type.srid)
# find the country
c = self.country_table
sel = select([c.column_cc()], distinct=True)\
.where(c.column_geom().ST_Intersects(outtags['geom']))
cur = self.thread.conn.execute(sel)
if cur.rowcount == 1:
cntry = cur.scalar()
elif cur.rowcount > 1:
# XXX should be counting here
cntry = cur.scalar()
else:
cntry = None
outtags['country'] = cntry
outtags['symbol'] = self.symbols.create_write(tags, cntry, outtags['level'])
# custom filter callback
if ROUTE_CONF.tag_filter is not None:
ROUTE_CONF.tag_filter(outtags, tags)
if outtags['top'] is None:
if 'network' in tags:
h = self.hierarchy_table.data
r = self.src.data
sel = select([text("'a'")]).where(h.c.child == osmid)\
.where(r.c.id == h.c.parent)\
.where(h.c.depth == 2)\
.where(r.c.tags['network'] == tags['network'])\
.limit(1)
top = self.thread.conn.scalar(sel)
outtags['top'] = (top is None)
else:
outtags['top'] = True
return outtags
def _process_next(self, obj):
tags = self.transform_tags(obj['id'], TagStore(obj['tags']))
if tags is not None:
tags['id'] = obj['id']
self.thread.conn.execute(self.data.insert().values(**tags))
def __init__(self, segments, hierarchy, countries):
super().__init__(CONF.route_table_name, segments, hiertable=hierarchy)
self.symbols = ShieldFactory(*CONF.symbols)
def __init__(self, meta, osmdata, subset=None, geom_change=None):
super().__init__(meta, CONF.way_table_name, osmdata,
subset=subset, geom_change=geom_change)
self.symbols = ShieldFactory(*CONF.symbols)
class RouteInfo(Routes):
def __init__(self, segments, hierarchy, countries):
super().__init__(ROUTE_CONF.table_name, segments, hiertable=hierarchy)
self.country_table = countries
self.symbols = ShieldFactory(*ROUTE_CONF.symbols)
def columns(self):
return (Column('name', String),
Column('intnames', HSTORE),
Column('symbol', String),
Column('country', String(length=3)),
Column('network', String(length=2)),
Column('level', SmallInteger),
Column('top', Boolean),
Column('geom', Geometry('GEOMETRY',
srid=self.segment_table.data.c.geom.type.srid)),
Index('idx_%s_iname' % ROUTE_CONF.table_name, text('upper(name)'))
)
def build_geometry(self, osmid):
# find all relation parts
h = self.hierarchy_table.data
parts = select([h.c.child]).where(h.c.parent == osmid)
# stick them together
s = self.segment_table.data
sel = select([func.st_linemerge(func.st_collect(s.c.geom))])\
.where(s.c.rels.op('&& ARRAY')(parts))
return self.thread.conn.scalar(sel)
def transform_tags(self, osmid, tags):
outtags = { 'intnames' : {},
'level' : 35,
'network' : '',
'top' : None,
'geom' : None}
# determine name and level
for (k,v) in tags.items():
if k == 'name':
outtags[k] = v
elif k.startswith('name:'):
outtags['intnames'][k[5:]] = v
elif k == 'ref':
if 'name' not in outtags:
outtags['name'] = '[%s]' % v
elif k == 'network':
outtags['level'] = ROUTE_CONF.network_map.get(v, 35)
if 'name'not in outtags:
outtags['name'] = '(%s)' % osmid
# geometry
outtags['geom'] = self.build_geometry(osmid)
if outtags['geom'] is None:
return None
# find the country
c = self.country_table
sel = select([c.column_cc()], distinct=True)\
.where(c.column_geom().ST_Intersects(outtags['geom']))
cur = self.thread.conn.execute(sel)
if cur.rowcount == 1:
cntry = cur.scalar()
elif cur.rowcount > 1:
# XXX should be counting here
cntry = cur.scalar()
else:
cntry = None
outtags['country'] = cntry
outtags['symbol'] = self.symbols.create_write(tags, cntry, outtags['level'])
# custom filter callback
if ROUTE_CONF.tag_filter is not None:
ROUTE_CONF.tag_filter(outtags, tags)
if outtags['top'] is None:
if 'network' in tags:
h = self.hierarchy_table.data
r = self.src.data
sel = select([text("'a'")]).where(h.c.child == osmid)\
.where(r.c.id == h.c.parent)\
.where(h.c.depth == 2)\
.where(r.c.tags['network'] == tags['network'])\
.limit(1)
top = self.thread.conn.scalar(sel)
outtags['top'] = (top is None)
else:
outtags['top'] = True
return outtags
from osgende.lines import PlainWayTable
import sqlalchemy as sa
from sqlalchemy.sql import functions as saf
from sqlalchemy.dialects.postgresql import JSONB
from geoalchemy2 import Geometry
from geoalchemy2.shape import to_shape, from_shape
from shapely.ops import linemerge
from db.common.symbols import ShieldFactory
from db.configs import PisteTableConfig, RouteTableConfig
from db import conf
CONF = conf.get('PISTE', PisteTableConfig)
shield_fab = ShieldFactory(*CONF.symbols)
def _add_piste_columns(table, name):
table.append_column(sa.Column('name', sa.String))
table.append_column(sa.Column('intnames', JSONB))
table.append_column(sa.Column('symbol', sa.String))
table.append_column(sa.Column('difficulty', sa.SmallInteger))
table.append_column(sa.Column('piste', sa.SmallInteger))
table.append_column(sa.Index('idx_%s_iname' % name,
sa.text('upper(name)')))
def _basic_tag_transform(osmid, tags):
outtags = {'intnames': {}}
def __init__(self, segments, hierarchy, countries):
super().__init__(ROUTE_CONF.table_name, segments, hiertable=hierarchy)
self.country_table = countries
self.symbols = ShieldFactory(*ROUTE_CONF.symbols)
class RouteInfo(Routes):
def __init__(self, segments, hierarchy, countries):
super().__init__(ROUTE_CONF.table_name, segments, hiertable=hierarchy)
self.country_table = countries
self.symbols = ShieldFactory(*ROUTE_CONF.symbols)
def columns(self):
return (Column('name', String), Column('intnames', HSTORE),
Column('symbol', String), Column('country', String(length=3)),
Column('network', String(length=2)),
Column('level', SmallInteger), Column('top', Boolean),
Column(
'geom',
Geometry('GEOMETRY',
srid=self.segment_table.data.c.geom.type.srid)),
Index('idx_%s_iname' % ROUTE_CONF.table_name,
text('upper(name)')))
def transform_tags(self, osmid, tags):
outtags = {
'intnames': {},
'level': 35,
'network': '',
'top': None,
'geom': None
}
# determine name and level
for (k, v) in tags.items():
if k == 'name':
outtags[k] = v
elif k.startswith('name:'):
outtags['intnames'][k[5:]] = v
elif k == 'ref':
if 'name' not in outtags:
outtags['name'] = '[%s]' % v
elif k == 'network':
outtags['level'] = ROUTE_CONF.network_map.get(v, 35)
if 'name' not in outtags:
outtags['name'] = '(%s)' % osmid
# geometry
geom = self.build_geometry(osmid)
if geom is None:
return None
# if the route is unsorted but linear, sort it
if geom.geom_type == 'MultiLineString':
fixed_geom = linemerge(geom)
if fixed_geom.geom_type == 'LineString':
geom = fixed_geom
outtags['geom'] = from_shape(geom, srid=self.data.c.geom.type.srid)
# find the country
c = self.country_table
sel = select([c.column_cc()], distinct=True)\
.where(c.column_geom().ST_Intersects(outtags['geom']))
cur = self.thread.conn.execute(sel)
if cur.rowcount == 1:
cntry = cur.scalar()
elif cur.rowcount > 1:
# XXX should be counting here
cntry = cur.scalar()
else:
cntry = None
outtags['country'] = cntry
outtags['symbol'] = self.symbols.create_write(tags, cntry,
outtags['level'])
# custom filter callback
if ROUTE_CONF.tag_filter is not None:
ROUTE_CONF.tag_filter(outtags, tags)
if outtags['top'] is None:
if 'network' in tags:
h = self.hierarchy_table.data
r = self.src.data
sel = select([text("'a'")]).where(h.c.child == osmid)\
.where(r.c.id == h.c.parent)\
.where(h.c.depth == 2)\
.where(r.c.tags['network'] == tags['network'])\
.limit(1)
top = self.thread.conn.scalar(sel)
outtags['top'] = (top is None)
else:
outtags['top'] = True
return outtags
def _process_next(self, obj):
tags = self.transform_tags(obj['id'], TagStore(obj['tags']))
if tags is not None:
tags['id'] = obj['id']
self.thread.conn.execute(self.data.insert().values(**tags))