source: osmose-frontend/api_0_1.py

Last change on this file was e2036a5, checked in by Jocelyn Jaubert <jocelyn.jaubert@…>, 7 years ago

Store lat/lon in database as numeric type instead of integer

This also fixes a precision isse on lat/lon, which is missing one digit
compared to OSM database.

  • Property mode set to 100644
File size: 6.2 KB
Line 
1#!/usr/bin/env python
2# -*- coding: utf-8 -*-
3
4#
5# Copyright 2009 Christoph Böhme, Mitja Kleider - part of Openstreetbugs.
6# Copyright 2011-2012 Jocelyn Jaubert
7# Copyright 2012 Frederic Rodrigo
8#
9# Openstreetbugs is free software: you can redistribute it and/or modify
10# it under the terms of the GNU General Public License as published by
11# the Free Software Foundation, either version 3 of the License, or
12# (at your option) any later version.
13#
14# Openstreetbugs is distributed in the hope that it will be useful,
15# but WITHOUT ANY WARRANTY; without even the implied warranty of
16# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
17# GNU General Public License for more details.
18#
19# You should have received a copy of the GNU General Public License
20# along with Openstreetbugs. If not, see <http://www.gnu.org/licenses/>.
21#
22
23from bottle import route, response, request
24from tools import utils, osmose_common
25
26
27@route('/api/0.1/closePOIexec', method=['GET', 'POST'])
28@route('/api/0.1.1/closePOIexec/<id:int>')
29def closePOIexec(id = None):
30    response.content_type = 'text/plain; Charset=UTF-8'
31    id = id or request.params.get('id', type=int)
32    if not id:
33        return "FAIL"
34    elif osmose_common.remove_bug(id, "done") == 0:
35        return "OK"
36    else:
37        return "FAIL"
38
39
40def bboxRequest2Clause():
41    minlat = request.params.get("b", type=float, default=-90)
42    maxlat = request.params.get("t", type=float, default=90)
43    minlon = request.params.get("l", type=float, default=-180)
44    maxlon = request.params.get("r", type=float, default=180)
45    lat = int((minlat + maxlat) / 2)
46    lon = int((minlon + maxlon) / 2)
47    clause = []
48    clause.append("marker.lat BETWEEN %s AND %s" % (int(minlat), int(maxlat)))
49    clause.append("marker.lon BETWEEN %s AND %s" % (int(minlon), int(maxlon)))
50    order = "ABS(lat - %s) + ABS(lon - %s) ASC" % (lat, lon)
51    return [clause, order]
52
53def list2Clause(l, clause, item):
54    if not l:
55        return
56    err_id = l.split(",")
57    err_id = ",".join([str(int(x)) for x in err_id if x])
58    if err_id:
59        clause.append("%s IN (%s)" % (item, err_id))
60
61def query(db, sql, args, callback):
62    db.execute(sql, args)
63    res = db.fetchone()
64    content = ''
65    while res:
66        content += callback(res)
67        res = db.fetchone()
68
69    return content
70
71
72@route('/api/0.1/getBugs')
73def getBugs(db, lang):
74    clause, order = bboxRequest2Clause()
75    list2Clause(request.params.get('item'), clause, 'marker.item')
76
77    sqlbase  = """
78SELECT marker.id,
79    marker.item,
80    marker.lat,
81    marker.lon,
82    dynpoi_class.title as title,
83    marker.subtitle as subtitle
84FROM marker
85INNER JOIN dynpoi_class ON
86    marker.source=dynpoi_class.source AND
87    marker.class=dynpoi_class.class
88INNER JOIN dynpoi_update_last ON
89    marker.source = dynpoi_update_last.source
90WHERE
91    %s AND
92    dynpoi_update_last.timestamp > (now() - interval '3 months')
93ORDER BY
94    %s
95LIMIT 100
96"""
97
98    translate = utils.translator(lang)
99
100    def each(res):
101        lat       = res["lat"]
102        lon       = res["lon"]
103        error_id  = res["id"]
104        title     = translate.select(res["title"])
105        subtitle  = translate.select(res["subtitle"])
106        item      = res["item"] or 0
107
108        text      = title
109        if subtitle:
110            text += " - <br>" + subtitle
111        return u"putAJAXMarker('%s', %f, %f, '%s', '%s');\n" % (error_id, lon, lat, text, item)
112
113    response.content_type = 'text/plain; Charset=UTF-8'
114    return query(db, sqlbase % (' AND '.join(clause), order), None, each)
115
116
117@route('/api/0.1/getBugsByUser')
118@route('/api/0.1.1/getBugs/<user>')
119def getBugsByUser(db, lang, user=None):
120    user = user or request.params.get('user')
121    clause, order = bboxRequest2Clause()
122    sql_arg = {}
123    limit = ''
124    if user:
125        clause.append("u.username = %(username)s")
126        sql_arg['username'] = user
127    else:
128        num_points = request.params.get('points', type=int, default=100)
129        if num_points != "all":
130            limit = "LIMIT %d" % int(num_points)
131    list2Clause(request.params.get('class'), clause, 'm.class')
132    list2Clause(request.params.item, clause, 'm.item')
133    list2Clause(request.params.not_item, clause, 'm.item NOT')
134
135    sqlbase  = """
136SELECT marker.id,
137       marker.item,
138       marker.source,
139       marker.class,
140       marker.elems,
141       marker.subclass,
142       marker.lat,
143       marker.lon,
144       dynpoi_class.title as title,
145       marker.subtitle as subtitle,
146       dynpoi_update_last.timestamp,
147       u.username
148FROM marker
149INNER JOIN dynpoi_class ON
150    marker.source=dynpoi_class.source AND
151    marker.class=dynpoi_class.class
152INNER JOIN dynpoi_update_last ON
153    marker.source = dynpoi_update_last.source
154LEFT JOIN marker_elem u ON
155    marker.id = u.marker_id
156WHERE
157    %s
158ORDER BY
159    dynpoi_update_last.timestamp DESC
160%s
161"""
162
163    translate = utils.translator(lang)
164
165    def each(res):
166        lat       = res["lat"]
167        lon       = res["lon"]
168        error_id  = res["id"]
169        title     = translate.select(res["title"])
170        subtitle  = translate.select(res["subtitle"])
171        item      = res["item"] or 0
172        return u'"%s", "%s", "%s", %f, %f, "%s", "%s", "%s"\n' % (res["timestamp"], res["username"].decode('utf-8'), error_id, lon, lat, title, subtitle, item)
173
174    response.content_type = 'text/plain; Charset=UTF-8'
175    content = "# timestamp, username, error_id, lon, lat, title, subtitle, item\n"
176    return content + query(db, sqlbase % (' AND '.join(clause), limit), sql_arg, each)
177
178
179@route('/api/0.1/getUsers')
180def getUsers(db):
181    user = request.params.get('user')
182    clause, order = bboxRequest2Clause()
183    sql_arg = {}
184    if user:
185        clause.append("u.username = %(username)s")
186        sql_arg['username'] = user
187
188    list2Clause(request.params.get('item'), clause, 'm.item')
189    list2Clause(request.params.get('not_item'), clause, 'm.item NOT')
190
191    sqlbase  = """
192SELECT
193    u.username,
194    count(*) AS count
195FROM marker_elem u
196JOIN marker ON
197    marker.id = u.marker_id
198WHERE
199    %s
200GROUP BY
201    u.username
202ORDER BY
203    u.username
204"""
205
206    response.content_type = 'text/plain; Charset=UTF-8'
207    content = "# username, count\n"
208    content += query(db, sqlbase % ' AND '.join(clause), sql_arg, lambda res:
209        '"%s", %d"\n' % (res["username"], int(res["count"]))
210    )
211    return content
Note: See TracBrowser for help on using the repository browser.