X-Git-Url: https://git.toastfreeware.priv.at/philipp/winterrodeln/wrpylib.git/blobdiff_plain/2974e804f9035d062b48b2cb42bef2bb87809f02..02d36fe6e46df7d65bde90ec99d032546feb8893:/wrpylib/wrmwcache.py?ds=sidebyside diff --git a/wrpylib/wrmwcache.py b/wrpylib/wrmwcache.py index c197f94..4b3df61 100644 --- a/wrpylib/wrmwcache.py +++ b/wrpylib/wrmwcache.py @@ -1,10 +1,13 @@ -#!/usr/bin/python2.6 +#!/usr/bin/python2.7 # -*- coding: iso-8859-15 -*- # $Id$ # $HeadURL$ """Contains functions that maintain/update the cache tables.""" from sqlalchemy import schema from sqlalchemy.sql import select +from sqlalchemy.sql.expression import func as sqlfunc +import geoalchemy.functions as geofunc +from osgeo import ogr import formencode from wrpylib import mwdb, wrmwdb, mwmarkup, wrmwmarkup @@ -18,7 +21,7 @@ def update_wrsledruncache(connection): is raised. No other exception type should be raised under normal circumstances. >>> from sqlalchemy.engine import create_engine - >>> engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=0') + >>> engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1') >>> update_wrsledruncache(engine.connect()) """ metadata = schema.MetaData() @@ -60,7 +63,7 @@ def update_wrinncache(connection): is raised. No other exception type should be raised under normal circumstances. >>> from sqlalchemy.engine import create_engine - >>> engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=0') + >>> engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1') >>> update_wrinncache(engine.connect()) """ metadata = schema.MetaData() @@ -141,7 +144,8 @@ def update_wrmapcache(connection): is raised. No other exception type should be raised under normal circumstances. >>> from sqlalchemy.engine import create_engine - >>> engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=0') + >>> engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1') + >>> # or: engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1&passwd=XXXXX') >>> update_wrmapcache(engine.connect()) """ metadata = schema.MetaData() @@ -166,30 +170,76 @@ def update_wrmapcache(connection): # Refill wrmappointcache and wrmappathcache tables for sledrun_page in sledrun_pages: try: - result = mwmarkup.parse_googlemap(sledrun_page.old_text) - if not result is None: - center, zoom, coords, paths = result + start, content, endtag, end = mwmarkup.find_tag(sledrun_page.old_text, 'wrmap') + if content is None: + continue + geojson = wrmwmarkup.parse_wrmap(sledrun_page.old_text[start:end]) + + for feature in geojson['features']: + properties = feature['properties'] + coordinates = feature['geometry']['coordinates'] + # Points - for coord in coords: - lon, lat, point_type, label = coord - point_types = {u'Gasthaus': u'hut', u'Haltestelle': u'busstop', u'Parkplatz': u'carpark', u'Achtung': u'warning'} - if not point_type is None: - if not point_types.has_key(point_type): raise RuntimeError(u'Unknown point type {0}'.format(point_type)) - point_type = point_types[point_type] - sql = u'insert into wrmappointcache (page_id, type, point, label) values (%s, %s, POINT(%s, %s), %s)' - connection.execute(sql, (sledrun_page.page_id, point_type, lon, lat, label)) + if properties['type'] in wrmwmarkup.WRMAP_POINT_TYPES: + lon, lat = coordinates + label = properties.get('name') + point_types = {u'gasthaus': u'hut', u'haltestelle': u'busstop', u'parkplatz': u'carpark', u'achtung': u'warning', u'punkt': u'point'} + point_type = point_types[properties['type']] + sql = u'insert into wrmappointcache (page_id, type, point, label) values (%s, %s, POINT(%s, %s), %s)' + connection.execute(sql, (sledrun_page.page_id, point_type, lon, lat, label)) + # Paths - for path_type, coords in paths: - path_type = path_type.lower() - path_types = {u'6#ff014e9a': u'sledrun', u'6#ffe98401': u'walkup', u'6#ff7f7fff': u'alternative', u'3#ff000000': u'lift'} - if not path_types.has_key(path_type): raise RuntimeError(u'Unknown path type {0}'.format(path_type)) - path_type = path_types[path_type] - path = u", ".join(["{0} {1}".format(lon, lat) for lon, lat, symbol, title in coords]) + elif properties['type'] in wrmwmarkup.WRMAP_LINE_TYPES: + path_types = {u'rodelbahn': u'sledrun', u'gehweg': u'walkup', u'alternative': u'alternative', u'lift': u'lift', u'anfahrt': u'recommendedcarroute', u'linie': u'line'} + path_type = path_types[properties['type']] + path = u", ".join(["{0} {1}".format(lon, lat) for lon, lat in coordinates]) path = u'LineString({0})'.format(path) + if path_type == u'recommendedcarroute': continue sql = u'insert into wrmappathcache (path, page_id, type) values (GeomFromText(%s), %s, %s)' connection.execute(sql, (path, sledrun_page.page_id, path_type)) + + else: + raise RuntimeError(u'Unknown feature type {0}'.format(properties['type'])) except RuntimeError as e: error_msg = u"Error at sledrun '{0}': {1}".format(sledrun_page.page_title, unicode(e)) transaction.rollback() raise UpdateCacheError(error_msg, sledrun_page.page_title, e) transaction.commit() + + +def update_wrregioncache(connection): + """Updates the wrregioncache table from the wiki. + It relays on the table wrsledruncache to be up-to-date. + No exceptions should be raised under normal circumstances. + + >>> from sqlalchemy.engine import create_engine + >>> engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1') + >>> # or: engine = create_engine('mysql://philipp@localhost:3306/philipp_winterrodeln_wiki?charset=utf8&use_unicode=1&passwd=XXXXX') + >>> update_wrregioncache(engine.connect()) + """ + metadata = schema.MetaData() + wrregion = wrmwdb.wrregion_table(metadata) + wrsledruncache = wrmwdb.wrsledruncache_table(metadata) + wrregioncache = wrmwdb.wrregioncache_table(metadata) + + transaction = connection.begin() + + # Delete all existing entries in wrregioncache + # We rely on transactions MySQL InnoDB + connection.execute(wrregioncache.delete()) + + # Query all combinations of sledruns and regions + sel = select([wrregion.c.id.label('wrregion_id'), wrregion.c.border.label('border'), wrsledruncache.c.page_id, wrsledruncache.c.position_longitude, wrsledruncache.c.position_latitude], geofunc.gcontains(wrregion.c.border, sqlfunc.point(wrsledruncache.c.position_longitude, wrsledruncache.c.position_latitude))) + ins = wrregioncache.insert() + + # Refill wrregioncache + point = ogr.Geometry(ogr.wkbPoint) + result = connection.execute(sel) + for row in result: + point.SetPoint(0, row.position_longitude, row.position_latitude) + if point.Within(ogr.CreateGeometryFromWkb(row.border.geom_wkb)): + connection.execute(ins.values(wrregion_id=row.wrregion_id, page_id=row.page_id)) + + # commit + transaction.commit() +