|
1
|
|
|
# Copyright 2013 Netherlands eScience Center |
|
2
|
|
|
# |
|
3
|
|
|
# Licensed under the Apache License, Version 2.0 (the "License"); |
|
4
|
|
|
# you may not use this file except in compliance with the License. |
|
5
|
|
|
# You may obtain a copy of the License at |
|
6
|
|
|
# |
|
7
|
|
|
# http://www.apache.org/licenses/LICENSE-2.0 |
|
8
|
|
|
# |
|
9
|
|
|
# Unless required by applicable law or agreed to in writing, software |
|
10
|
|
|
# distributed under the License is distributed on an "AS IS" BASIS, |
|
11
|
|
|
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
|
12
|
|
|
# See the License for the specific language governing permissions and |
|
13
|
|
|
# limitations under the License. |
|
14
|
|
|
|
|
15
|
1 |
|
import logging |
|
16
|
1 |
|
from iso8601 import parse_date |
|
17
|
1 |
|
from pyramid.httpexceptions import HTTPFound |
|
18
|
1 |
|
from pyramid.response import Response |
|
19
|
1 |
|
from pyramid.view import view_config |
|
20
|
1 |
|
import simplejson |
|
21
|
|
|
|
|
22
|
1 |
|
logger = logging.getLogger(__package__) |
|
|
|
|
|
|
23
|
|
|
|
|
24
|
|
|
|
|
25
|
1 |
|
class UploadViews(object): |
|
26
|
|
|
""" |
|
27
|
|
|
Serves annotations from a table with following structure:: |
|
28
|
|
|
|
|
29
|
|
|
CREATE TABLE classification.annotation_538_movement |
|
30
|
|
|
( |
|
31
|
|
|
device_info_serial integer NOT NULL, |
|
32
|
|
|
date_time timestamp without time zone NOT NULL, |
|
33
|
|
|
first_index integer NOT NULL, |
|
34
|
|
|
class_id integer, |
|
35
|
|
|
class_name character varying(255), |
|
36
|
|
|
class_red double precision, |
|
37
|
|
|
class_green double precision, |
|
38
|
|
|
class_blue double precision, |
|
39
|
|
|
CONSTRAINT annotation_538_movement_pkey PRIMARY KEY (device_info_serial, date_time, first_index) |
|
|
|
|
|
|
40
|
|
|
); |
|
41
|
|
|
""" |
|
42
|
|
|
|
|
43
|
1 |
|
def __init__(self, request): |
|
44
|
1 |
|
self.request = request |
|
45
|
1 |
|
self.db = request.db |
|
|
|
|
|
|
46
|
1 |
|
self.table = request.matchdict.get('table', '') |
|
47
|
|
|
|
|
48
|
1 |
|
def fetch_classes(self): |
|
49
|
1 |
|
cursor = self.db.cursor() |
|
50
|
1 |
|
sql_template = "SELECT DISTINCT class_id AS id, class_name AS label, 'rgb(' || floor(class_red*255) ||',' || floor(class_green*255) || ',' || floor(class_blue*255) || ')' AS color FROM {table} ORDER BY class_id" |
|
|
|
|
|
|
51
|
1 |
|
sql = sql_template.format(table=self.table) |
|
52
|
1 |
|
cursor.execute(sql) |
|
53
|
1 |
|
return cursor.fetchall() |
|
54
|
|
|
|
|
55
|
1 |
|
def fetch_trackers(self): |
|
56
|
1 |
|
cursor = self.db.cursor() |
|
57
|
1 |
|
sql_template = """SELECT |
|
58
|
|
|
device_info_serial AS id, |
|
59
|
|
|
TIMEZONE('zulu', MIN(date_time)) AS start, |
|
60
|
|
|
TIMEZONE('zulu', MAX(date_time)) AS end, |
|
61
|
|
|
COUNT(*) AS count |
|
62
|
|
|
FROM "{table}" |
|
63
|
|
|
GROUP BY device_info_serial |
|
64
|
|
|
ORDER BY device_info_serial |
|
65
|
|
|
""" |
|
66
|
1 |
|
sql = sql_template.format(table=self.table.replace('.', '"."')) |
|
67
|
1 |
|
cursor.execute(sql) |
|
68
|
1 |
|
trackers = cursor.fetchall() |
|
69
|
1 |
|
for tracker in trackers: |
|
70
|
1 |
|
tracker['size'] = self.track_size(tracker['id'], tracker['start'], tracker['end']) |
|
|
|
|
|
|
71
|
1 |
|
tracker['page_size'] = 500 |
|
72
|
1 |
|
tracker['first_page'] = self.ts_track_after(tracker['id'], tracker['start'], tracker['end'], tracker['page_size']) |
|
|
|
|
|
|
73
|
1 |
|
tracker['last_page'] = self.ts_track_before(tracker['id'], tracker['start'], tracker['end'], tracker['page_size']) |
|
|
|
|
|
|
74
|
1 |
|
return trackers |
|
75
|
|
|
|
|
76
|
1 |
|
def fetch_annotations_as_csv(self, tracker_id, start, end): |
|
77
|
1 |
|
cursor = self.db.cursor() |
|
78
|
1 |
|
sql_template = '''SELECT |
|
79
|
|
|
device_info_serial, |
|
80
|
|
|
date_time, |
|
81
|
|
|
class_id |
|
82
|
|
|
FROM "{table}" |
|
83
|
|
|
WHERE |
|
84
|
|
|
device_info_serial=%(tracker)s |
|
85
|
|
|
AND |
|
86
|
|
|
date_time BETWEEN %(start)s AND %(end)s |
|
87
|
|
|
ORDER BY |
|
88
|
|
|
device_info_serial, date_time |
|
89
|
|
|
''' |
|
90
|
1 |
|
logger.debug('Fetching annotations for id:{0}, start:{1}, end:{2}'.format(tracker_id, start, end)) |
|
|
|
|
|
|
91
|
1 |
|
sql = sql_template.format(table=self.table.replace('.', '"."')) |
|
92
|
1 |
|
cursor.execute(sql, {'tracker': tracker_id, |
|
93
|
|
|
'start': start, |
|
94
|
|
|
'end': end, |
|
95
|
|
|
}) |
|
96
|
1 |
|
annotations = ['device_info_serial,date_time,class_id'] |
|
97
|
1 |
|
for a in cursor.fetchall(): |
|
|
|
|
|
|
98
|
1 |
|
annotations.append(str(a['device_info_serial']) + ',' + a['date_time'].isoformat() + 'Z,' + str(a['class_id'])) |
|
|
|
|
|
|
99
|
1 |
|
return "\n".join(annotations) + "\n" |
|
100
|
|
|
|
|
101
|
1 |
|
def track_size(self, tracker_id, start, end): |
|
102
|
1 |
|
cursor = self.db.cursor() |
|
103
|
1 |
|
sql = '''SELECT |
|
104
|
|
|
COUNT(*) AS count |
|
105
|
|
|
FROM gps.ee_tracking_speed_limited |
|
106
|
|
|
WHERE |
|
107
|
|
|
device_info_serial=%(tracker)s |
|
108
|
|
|
AND |
|
109
|
|
|
date_time BETWEEN %(start)s AND %(end)s |
|
110
|
|
|
''' |
|
111
|
1 |
|
cursor.execute(sql, {'tracker': tracker_id, |
|
112
|
|
|
'start': start, |
|
113
|
|
|
'end': end, |
|
114
|
|
|
}) |
|
115
|
1 |
|
result = cursor.fetchone() |
|
116
|
1 |
|
return result['count'] |
|
117
|
|
|
|
|
118
|
1 |
|
def ts_track_after(self, tracker_id, start, end, count): |
|
119
|
1 |
|
cursor = self.db.cursor() |
|
120
|
1 |
|
sql = '''SELECT |
|
121
|
|
|
date_time |
|
122
|
|
|
FROM gps.ee_tracking_speed_limited |
|
123
|
|
|
WHERE |
|
124
|
|
|
device_info_serial=%(tracker)s |
|
125
|
|
|
AND |
|
126
|
|
|
date_time BETWEEN %(start)s AND %(end)s |
|
127
|
|
|
ORDER BY date_time |
|
128
|
|
|
LIMIT 1 |
|
129
|
|
|
OFFSET %(count)s |
|
130
|
|
|
''' |
|
131
|
1 |
|
cursor.execute(sql, {'tracker': tracker_id, |
|
132
|
|
|
'start': start, |
|
133
|
|
|
'end': end, |
|
134
|
|
|
'count': count, |
|
135
|
|
|
}) |
|
136
|
|
|
|
|
137
|
1 |
|
result = cursor.fetchone() |
|
138
|
1 |
|
if result: |
|
139
|
1 |
|
return result['date_time'] |
|
140
|
|
|
else: |
|
141
|
1 |
|
return end |
|
142
|
|
|
|
|
143
|
1 |
|
def ts_track_before(self, tracker_id, start, end, count): |
|
144
|
1 |
|
cursor = self.db.cursor() |
|
145
|
1 |
|
sql = '''SELECT |
|
146
|
|
|
date_time |
|
147
|
|
|
FROM gps.ee_tracking_speed_limited |
|
148
|
|
|
WHERE |
|
149
|
|
|
device_info_serial=%(tracker)s |
|
150
|
|
|
AND |
|
151
|
|
|
date_time BETWEEN %(start)s AND %(end)s |
|
152
|
|
|
ORDER BY date_time DESC |
|
153
|
|
|
LIMIT 1 |
|
154
|
|
|
OFFSET %(count)s |
|
155
|
|
|
''' |
|
156
|
1 |
|
cursor.execute(sql, {'tracker': tracker_id, |
|
157
|
|
|
'start': start, |
|
158
|
|
|
'end': end, |
|
159
|
|
|
'count': count, |
|
160
|
|
|
}) |
|
161
|
1 |
|
result = cursor.fetchone() |
|
162
|
1 |
|
if result: |
|
163
|
1 |
|
return result['date_time'] |
|
164
|
|
|
else: |
|
165
|
1 |
|
return start |
|
166
|
|
|
|
|
167
|
1 |
|
@view_config(route_name='uploads.html', renderer='uploads.mako') |
|
168
|
|
|
def uploads(self): |
|
169
|
|
|
''' |
|
170
|
|
|
Page with form to select table with annotations. |
|
171
|
|
|
|
|
172
|
|
|
Query parameters: |
|
173
|
|
|
- table: Postgresql schema and table seperated by a dot, eg myschema.mytable |
|
|
|
|
|
|
174
|
|
|
''' |
|
175
|
1 |
|
table = self.request.params.get('table', '') |
|
176
|
1 |
|
if table == '': |
|
177
|
1 |
|
return {'trackers': [], 'table': ''} |
|
178
|
|
|
else: |
|
179
|
1 |
|
self.table = table |
|
180
|
|
|
|
|
181
|
1 |
|
trackers = self.fetch_trackers() |
|
182
|
|
|
|
|
183
|
1 |
|
return {'trackers': trackers, 'table': self.table} |
|
184
|
|
|
|
|
185
|
1 |
|
@view_config(route_name='annotations.html', renderer='upload.mako') |
|
186
|
|
|
def upload(self): |
|
187
|
|
|
''' |
|
188
|
|
|
Page with eEcology Annotation UI with annotations loaded from databases. |
|
|
|
|
|
|
189
|
|
|
|
|
190
|
|
|
Query parameters: |
|
191
|
|
|
- id: tracker id, |
|
192
|
|
|
- start: start of time range in ISO8601 format |
|
193
|
|
|
- end: end of time range in ISO8601 format |
|
194
|
|
|
''' |
|
195
|
1 |
|
if not set(['id', 'start', 'end']).issubset(self.request.params.keys()): |
|
|
|
|
|
|
196
|
1 |
|
return HTTPFound(self.request.route_path('uploads.html', _query={'table': self.table})) |
|
|
|
|
|
|
197
|
1 |
|
tracker_id = int(self.request.params.get('id', 0)) |
|
198
|
1 |
|
start = parse_date(self.request.params['start']).isoformat() |
|
199
|
1 |
|
end = parse_date(self.request.params['end']).isoformat() |
|
200
|
1 |
|
classes = self.fetch_classes() |
|
201
|
1 |
|
annotations_url = self.request.route_path('annotations.csv', |
|
202
|
|
|
table=self.table, |
|
203
|
|
|
) |
|
204
|
1 |
|
return {'tracker_id': tracker_id, |
|
205
|
|
|
'start': start, |
|
206
|
|
|
'end': end, |
|
207
|
|
|
'classes': simplejson.dumps(classes), |
|
208
|
|
|
'annotations_url': annotations_url, |
|
209
|
|
|
} |
|
210
|
|
|
|
|
211
|
1 |
|
@view_config(route_name='annotations.csv') |
|
212
|
|
|
def annotations_as_csv(self): |
|
213
|
|
|
''' |
|
214
|
|
|
Returns annotations from a table with a tracker and time range selection. |
|
|
|
|
|
|
215
|
|
|
|
|
216
|
|
|
Query parameters: |
|
217
|
|
|
- id: tracker id, |
|
218
|
|
|
- start: start of time range in ISO8601 format |
|
219
|
|
|
- end: end of time range in ISO8601 format |
|
220
|
|
|
''' |
|
221
|
1 |
|
tracker_id = self.request.params.get('id', 0) |
|
222
|
1 |
|
start = parse_date(self.request.params['start']).isoformat() |
|
223
|
1 |
|
end = parse_date(self.request.params['end']).isoformat() |
|
224
|
1 |
|
csv = self.fetch_annotations_as_csv(tracker_id, start, end) |
|
225
|
|
|
return Response(csv, content_type='text/csv') |
|
226
|
|
|
|
NLeSC /
eEcology-Annotation-WS
Loading history...
This check looks for invalid names for a range of different identifiers.
You can set regular expressions to which the identifiers must conform if the defaults do not match your requirements.
If your project includes a Pylint configuration file, the settings contained in that file take precedence.
To find out more about Pylint, please refer to their site.