X-Git-Url: https://git.toastfreeware.priv.at/philipp/winterrodeln/wrfeed.git/blobdiff_plain/c255639edef3f89546418218f3b6d59fd59ffdcc..888330ce77e064b6ba0d7d5811f41ae303552985:/wrfeed/controllers/berichte.py diff --git a/wrfeed/controllers/berichte.py b/wrfeed/controllers/berichte.py index 0815056..878a83c 100644 --- a/wrfeed/controllers/berichte.py +++ b/wrfeed/controllers/berichte.py @@ -1,4 +1,4 @@ -#!/usr/bin/python2.6 +#!/usr/bin/python2.7 # -*- coding: UTF-8 -*- """Creates an Atom-Feed for single or multiple winterrodeln sled reports. @@ -6,6 +6,7 @@ Format: http://www.winterrodeln.org/feed/berichte/alle http://www.winterrodeln.org/feed/berichte/bahn/kemater_alm http://www.winterrodeln.org/feed/berichte/bahnen/5+280+251 + http://www.winterrodeln.org/feed/berichte/region/osttirol See: http://www.atompub.org/ http://effbot.org/zone/element.htm @@ -16,9 +17,11 @@ import datetime from xml.etree.ElementTree import Element, SubElement, tostring from sqlalchemy.engine import create_engine +from osgeo import ogr import logging +import webob.exc from pylons import request, response, session, config, tmpl_context as c, url from pylons.controllers.util import abort, redirect @@ -27,36 +30,57 @@ from wrfeed.lib.base import BaseController, render log = logging.getLogger(__name__) -def create_feed(page_title=None, page_ids=None): +def create_feed(page_title=None, page_ids=None, region_name=None): """If a page_title is given, only the reports for the given sledrun are shown. If a list of page_ids is given, only the reports for the selected pages are shown. + If a region name (lower case) is given, the reports just for this region are shown. Otherwise, all reports are shown.""" engine = create_engine(config['sqlalchemy.url']) limit = int(config['feedentrylimit']) conn = engine.connect() - select = "select wrreport.page_id, wrreport.page_title, wrreport.id, date_report, date_entry, `condition`, description, author_name, author_userid, author_username, position_longitude, position_latitude from wrreport left outer join wrsledruncache on wrreport.page_id=wrsledruncache.page_id " + select = 'select wrreport.page_id, wrreport.page_title, wrreport.id, date_report, date_entry, `condition`, description, author_name, author_userid, author_username, position_longitude, position_latitude from wrreport left outer join wrsledruncache on wrreport.page_id=wrsledruncache.page_id' + where = 'where date_invalid > now() and delete_date is null' + order = 'order by id desc' + limit = 'limit {0}'.format(limit) + params = [] + if not page_title is None: # page_title is given page_title = page_title.replace('_', ' ') - sql = select + "where lcase(wrreport.page_title) = lcase(%s) and date_invalid > now() and delete_date is null order by id desc limit {0}".format(limit) - result = conn.execute(sql, page_title) + where += ' and lcase(wrreport.page_title) = lcase(%s)' + params += [page_title] elif not page_ids is None: # a list of page_ids is given - sql = [select + "where "] - if len(page_ids) > 0: - sql += '(' - sql += " or ".join(['wrreport.page_id=%s' for page_id in page_ids]) - sql += ') ' - sql += 'and date_invalid > now() and delete_date is null order by id desc limit {0}'.format(limit) - page_ids_str = [str(page_id) for page_id in page_ids] - result = conn.execute("".join(sql), *page_ids_str) + if len(page_ids) == 0: + raise webob.exc.HTTPBadRequest() + where += ' and (' + where += " or ".join(['wrreport.page_id=%s' for page_id in page_ids]) + where += ')' + params += map(str, page_ids) + elif not region_name is None: + # a name of a region is given + # (1) find out whether the region exists + subselect = 'select aswkb(border) as border_wkb from wrregion where name=lcase(%s)' + subresult = conn.execute(subselect, region_name) + if subresult.rowcount == 0: + # no region with such a name + raise webob.exc.HTTPNotFound() + assert subresult.rowcount == 1 + row = subresult.fetchone() + # (2) now we have the border + border_wkb = row['border_wkb'] # border as WKB + where += ' and contains(geomfromwkb(%s), point(position_longitude, position_latitude))' + params += [border_wkb] + # the following variables are needed for the additional filtering below + border = ogr.CreateGeometryFromWkb(border_wkb) + point = ogr.Geometry(ogr.wkbPoint) else: # user wants to have all reports - sql = select + "where date_invalid > now() and delete_date is null order by id desc limit {0}".format(limit) - result = conn.execute(sql) - + pass + sql = ' '.join([select, where, order, limit]) + result = conn.execute(sql, *params) feed = Element("feed", xmlns="http://www.w3.org/2005/Atom", attrib={'xmlns:georss': 'http://www.georss.org/georss', 'xmlns:wr': 'http://www.winterrodeln.org/schema/wrreport'}) feed_title = SubElement(feed, "title") @@ -65,7 +89,9 @@ def create_feed(page_title=None, page_ids=None): if not page_title is None: feed_id.text = url(qualified=True, controller='berichte', action='bahn', id=page_title) elif not page_ids is None: - feed_id.text = url(qualified=True, controller='berichte', action='bahnen', id="+".join(page_ids_str)) + feed_id.text = url(qualified=True, controller='berichte', action='bahnen', id="+".join(map(str, page_ids))) + elif not region_name is None: + feed_id.text = url(qualified=True, controller='berichte', action='region', id=region_name) else: feed_id.text = url(qualified=True, controller='berichte', action='alle') feed_updated = SubElement(feed, "updated") @@ -74,6 +100,15 @@ def create_feed(page_title=None, page_ids=None): last_updated = None for row in result: page_id, page_title, report_id, date_report, date_entry, condition, description, author_name, author_userid, author_username, lon, lat = row + + if not region_name is None: + # mysql 5.5 doesn't have specification conform geospacial functions. + # http://dev.mysql.com/doc/refman/5.5/en/functions-for-testing-spatial-relations-between-geometric-objects.html + # therefore we have to do further filtering here. + point.SetPoint(0, lon, lat) + if not point.Within(border): + continue + page_title_url = page_title.replace(u' ', u'_') entry = SubElement(feed, "entry") entry_title = SubElement(entry, "title") @@ -158,3 +193,11 @@ class BerichteController(BaseController): abort(400) # bad request response.content_type = 'application/atom+xml' return create_feed(page_ids=page_ids) + + def region(self, id): + """Handles URLs like + http://www.winterrodeln.org/feed/berichte/region/osttirol + """ + response.content_type = 'application/atom+xml' + return create_feed(region_name=id) +