Mercurial > dive4elements > gnv-client

comparison gnv-artifacts/src/test/ressources/queries.properties @ 127:f6f0e4ce4a35

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
merged gnv-artifacts/0.1
author Thomas Arendsen Hein <thomas@intevation.de>
date Fri, 28 Sep 2012 12:13:41 +0200
parents 5134266f00e7
children 7be22e76c270
comparison
equal deleted inserted replaced
49:94a07d1d9316 127:f6f0e4ce4a35
1 #############################################
2 #############################################
3 ########## Zeitserie ##############
4 #############################################
5 #############################################
6
7 timeseries_timeseriespoint=SELECT DISTINCT \
8 tsp.FEATUREID KEY, \
9 tsp.NAME VALUE \
10 FROM MEDIAN.TIMESERIESPOINT tsp, \
11 MEDIAN.MEASUREMENT mmt \
12 WHERE tsp.FEATUREID = mmt.FEATUREID AND \
13 mmt.SOURCEID = ? \
14 order by tsp.name
15
16 timeseries_parameter=SELECT DISTINCT \
17 p.PARAMETERID KEY, \
18 p.GERMANNAME VALUE \
19 from MEDIAN.PARAMETER p \
20 where p.PARAMETERID in \
21 (select distinct \
22 ts.PARAMETERID \
23 from MEDIAN.TIMESERIES ts \
24 where ts.TIMESERIESID in \
25 (select distinct \
26 tsv.TIMESERIESID \
27 from MEDIAN.TIMESERIESVALUE tsv \
28 where tsv.MEASUREMENTID in \
29 (select m.MEASUREMENTID \
30 from MEDIAN.MEASUREMENT m, \
31 MEDIAN.TIMESERIESPOINT tsp \
32 where m.FEATUREID = tsp.FEATUREID and \
33 tsp.FEATUREID IN ( ? )\
34 )\
35 )\
36 ) \
37 ORDER BY p.GERMANNAME
38
39 timeseries_depth_height=SELECT DISTINCT \
40 m.MEASUREMENTID KEY, \
41 m.ZLOCATION VALUE \
42 from MEDIAN.MEASUREMENT m \
43 where m.MEASUREMENTID in \
44 (SELECT DISTINCT \
45 t_v.MEASUREMENTID \
46 from MEDIAN.TIMESERIESVALUE t_v \
47 where t_v.TIMESERIESID in \
48 (SELECT DISTINCT \
49 t.TIMESERIESID \
50 from MEDIAN.TIMESERIES t \
51 where t.PARAMETERID in \
52 (SELECT DISTINCT \
53 p.PARAMETERID \
54 from MEDIAN.PARAMETER p \
55 where m.FEATUREID IN ( ? ) and \
56 p.PARAMETERID IN (?)\
57 )\
58 )\
59 )\
60 ORDER BY m.ZLOCATION DESC
61
62 timeseries_interval=select min(tv.TIMEVALUE) MIN, \
63 max(tv.TIMEVALUE) MAX \
64 from MEDIAN.TIMESERIES t , \
65 MEDIAN.TIMESERIESVALUE tv \
66 where tv.TIMESERIESID = t.TIMESERIESID AND \
67 t.PARAMETERID IN ( ? ) AND \
68 tv.MEASUREMENTID IN ( ? )
69
70 timeseries_chart_data=SELECT tv.TIMEVALUE XORDINATE, \
71 tv.DATAVALUE YORDINATE, \
72 t.PARAMETERID GROUP1, \
73 tv.MEASUREMENTID GROUP2, \
74 tv.TIMESERIESID GROUP3 \
75 FROM MEDIAN.TIMESERIESVALUE tv, \
76 MEDIAN.TIMESERIES t \
77 WHERE tv.TIMESERIESID = t.TIMESERIESID AND \
78 t.PARAMETERID IN ( ? ) AND \
79 tv.MEASUREMENTID IN ( ? ) AND \
80 tv.TIMEVALUE > ? AND \
81 tv.TIMEVALUE < ? \
82 ORDER BY tv.MEASUREMENTID , \
83 tv.TIMESERIESID , \
84 t.PARAMETERID , \
85 tv.TIMEVALUE
86
87 #############################################
88 #############################################
89 ########## Zeitserie Mesh ##############
90 #############################################
91 #############################################
92
93 timeseries_mesh = SELECT OBJECTID KEY, \
94 m.NAME VALUE \
95 FROM MEDIAN.MESH m \
96 order by m.NAME
97 timeseries_meshpoint = SELECT FEATUREID KEY, \
98 SHAPE VALUE \
99 FROM MEDIAN.MESHPOINT mp , \
100 MEDIAN.MESH m \
101 WHERE m.OBJECTID = ? AND \
102 mp.MESHID = m.MESHID AND \
103 KPOSITION = 1 AND \
104 rownum < 10
105
106 timeseries_meshpoint_depth = select mp.FEATUREID KEY, \
107 -ml.UPPERZLOCATION || ' - '|| -ml.LOWERZLOCATION as VALUE \
108 from MEDIAN.MESHLAYER ml, \
109 MEDIAN.MESHPOINT mp \
110 where ml.KPOSITION = mp.KPOSITION and \
111 ml.MESHID = mp.MESHID and \
112 mp.FEATUREID in \
113 ( select FEATUREID \
114 from MEDIAN.MESHPOINT mp, \
115 MEDIAN.MESH m \
116 where m.OBJECTID = ? AND \
117 mp.MESHID = m.MESHID AND \
118 IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) and \
119 JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?)) \
120 order by ml.UPPERZLOCATION desc
121
122 timeseries_mesh_parameter=SELECT distinct \
123 p.PARAMETERID KEY , \
124 p.GERMANNAME VALUE \
125 from MEDIAN.PARAMETER p, \
126 MEDIAN.MESHSCALARVALUE msc, \
127 MEDIAN.MESH m \
128 where m.OBJECTID = ? AND \
129 msc.PARTID = m.PARTIDMIN AND \
130 msc.PARAMETERID = p.PARAMETERID \
131 order by p.GERMANNAME
132 timeseries_mesh_interval=select /*+ parallel(TIMEVALUE,5) */ \
133 min(TIMEVALUE) MIN, \
134 max(TIMEVALUE) MAX \
135 from MEDIAN.MESHSCALARVALUE msc , \
136 MEDIAN.MESH m \
137 where m.OBJECTID = ? AND \
138 msc.PARTID >= m.PARTIDMIN AND \
139 msc.PARTID <= m.PARTIDMAX
140
141 timeseries_mesh_chart_data=select /*+ parallel(timevalue,10) */ \
142 msv.TIMEVALUE XORDINATE, \
143 msv.DATAVALUE YORDINATE, \
144 msv.PARAMETERID GROUP1, \
145 msv.FEATUREID GROUP2, \
146 mp.FEATUREID GROUP3 \
147 from MEDIAN.MESHSCALARVALUE msv , \
148 MEDIAN.MESHPOINT mp, \
149 MEDIAN.MESH m \
150 where (m.OBJECTID = ? AND \
151 msv.PARTID >= m.PARTIDMIN AND \
152 msv.PARTID <= m.PARTIDMAX ) AND \
153 msv.FEATUREID in ( ? ) AND \
154 msv.PARAMETERID in ( ? ) AND \
155 mp.FEATUREID in ( ? ) AND \
156 msv.TIMEVALUE >= ? AND \
157 msv.TIMEVALUE <= ? \
158 order by mp.FEATUREID, \
159 msv.FEATUREID, \
160 msv.PARAMETERID, \
161 msv.TIMEVALUE
162
163 #############################################
164 #############################################
165 ########## Vertikalprofil ##############
166 #############################################
167 #############################################
168
169 verticalprofile_point=SELECT DISTINCT \
170 tsp.FEATUREID KEY, \
171 tsp.NAME VALUE \
172 FROM MEDIAN.TIMESERIESPOINT tsp, \
173 MEDIAN.MEASUREMENT mmt \
174 WHERE tsp.FEATUREID = mmt.FEATUREID AND \
175 mmt.SOURCEID = ? \
176 order by tsp.name
177
178 verticalprofile_parameter=SELECT DISTINCT \
179 p.PARAMETERID KEY, \
180 p.GERMANNAME VALUE \
181 from MEDIAN.PARAMETER p, \
182 MEDIAN.TIMESERIES ts, \
183 MEDIAN.TIMESERIESVALUE tsv, \
184 MEDIAN.MEASUREMENT m \
185 where ts.PARAMETERID = p.PARAMETERID and \
186 ts.TIMESERIESID = tsv.TIMESERIESID and \
187 m.MEASUREMENTID = tsv.MEASUREMENTID and \
188 m.FEATUREID = ? \
189 ORDER BY p.GERMANNAME
190
191 verticalprofile_date=select distinct \
192 tsv.TIMEVALUE KEY, \
193 tsv.TIMEVALUE VALUE \
194 from MEDIAN.TIMESERIES ts , \
195 MEDIAN.TIMESERIESVALUE tsv, \
196 MEDIAN.MEASUREMENT m \
197 where ts.TIMESERIESID = tsv.TIMESERIESID and \
198 m.MEASUREMENTID = tsv.MEASUREMENTID and \
199 m.FEATUREID = ? and \
200 ts.PARAMETERID IN ( ? ) \
201 order by tsv.TIMEVALUE
202
203 verticalprofile_chart_data= SELECT m.ZLOCATION XORDINATE, \
204 tsv.DATAVALUE YORDINATE, \
205 ts.PARAMETERID GROUP1, \
206 tsv.TIMEVALUE GROUP2, \
207 1 GROUP3 \
208 from MEDIAN.TIMESERIES ts, \
209 MEDIAN.TIMESERIESVALUE tsv, \
210 MEDIAN.MEASUREMENT m \
211 where ts.TIMESERIESID = tsv.TIMESERIESID and \
212 m.MEASUREMENTID = tsv.MEASUREMENTID and \
213 m.FEATUREID = ? and \
214 ts.PARAMETERID IN ( ? ) AND \
215 tsv.TIMEVALUE IN (?) \
216 ORDER BY tsv.TIMEVALUE , \
217 tsv.TIMESERIESID , \
218 ts.PARAMETERID , \
219 tsv.TIMEVALUE
220
221 #############################################
222 #############################################
223 ########## Vertikalprofil Mesh ##############
224 #############################################
225 #############################################
226
227 verticalprofile_mesh = SELECT OBJECTID KEY, \
228 m.NAME VALUE \
229 FROM MEDIAN.MESH m \
230 order by m.NAME
231
232 verticalprofile_mesh_point = SELECT FEATUREID KEY, \
233 SHAPE VALUE \
234 FROM MEDIAN.MESHPOINT mp , \
235 MEDIAN.MESH m \
236 WHERE m.OBJECTID = ? AND \
237 m.MESHID = mp.MESHID AND \
238 mp.KPOSITION = 1 AND \
239 rownum < 10
240
241 verticalprofile_mesh_parameter=SELECT distinct \
242 p.PARAMETERID KEY , \
243 p.GERMANNAME VALUE \
244 from MEDIAN.PARAMETER p, \
245 MEDIAN.MESHSCALARVALUE msc, \
246 MEDIAN.MESH m \
247 where m.OBJECTID = ? AND \
248 m.PARTIDMIN = msc.PARTID AND \
249 msc.PARAMETERID = p.PARAMETERID \
250 order by p.GERMANNAME
251
252 verticalprofile_mesh_date= select distinct \
253 msv.TIMEVALUE KEY, \
254 msv.TIMEVALUE VALUE \
255 from MEDIAN.MESHSCALARVALUE msv , \
256 MEDIAN.MESH m \
257 where m.OBJECTID = ? AND \
258 msv.PARTID >= m.PARTIDMIN AND \
259 msv.PARTID <= m.PARTIDMAX \
260 order by msv.TIMEVALUE
261
262 verticalprofile_mesh_chart_data=select ml.UPPERZLOCATION XORDINATE, \
263 msv.DATAVALUE YORDINATE, \
264 msv.PARAMETERID GROUP1, \
265 msv.TIMEVALUE GROUP2, \
266 1 GROUP3 \
267 from MEDIAN.MESHLAYER ml, \
268 MEDIAN.MESHPOINT mp, \
269 MEDIAN.MESH m, \
270 MEDIAN.MESHSCALARVALUE msv \
271 where msv.FEATUREID = mp. FEATUREID AND \
272 ml.KPOSITION = mp.KPOSITION and \
273 ml.MESHID = mp.MESHID and \
274 m.MESHID = mp.MESHID AND \
275 m.PARTIDMIN <= msv.PARTID AND \
276 m.PARTIDMAX >= msv.PARTID AND \
277 msv.PARAMETERID in (?) AND \
278 msv.TIMEVALUE in (?) AND \
279 m.OBJECTID = ? AND \
280 mp.FEATUREID in \
281 ( select FEATUREID \
282 from MEDIAN.MESHPOINT mp, \
283 MEDIAN.MESH m \
284 where m.OBJECTID = ? AND \
285 mp.MESHID = m.MESHID AND \
286 IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) and \
287 JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?)) \
288 order by msv.PARAMETERID, \
289 msv.TIMEVALUE, \
290 ml.UPPERZLOCATION
291
292 #############################################
293 #############################################
294 ##### Vertikalprofil InstantaneousPoint #####
295 #############################################
296 #############################################
297
298 verticalprofile_instantaneouspoint_series = SELECT DISTINCT \
299 S.SERIESID KEY , \
300 S.DESCRIPTION VALUE \
301 FROM MEDIAN.SERIES S, \
302 MEDIAN.INSTANTANEOUSPOINT I, \
303 MEDIAN.MEASUREMENT M \
304 WHERE S.SERIESID = I.SERIESID AND \
305 I.POINTSPEC = 4 AND \
306 I.FEATUREID = M.FEATUREID AND \
307 M.SOURCEID= ? \
308 ORDER BY S.DESCRIPTION
309
310 verticalprofile_instantaneouspoint_point = SELECT DISTINCT \
311 I.FEATUREID KEY, \
312 to_char(I.TIMEVALUE,'DD.MM.YYYY HH24:MI') || ' - '|| I.SHAPE VALUE, \
313 I.TIMEVALUE \
314 FROM MEDIAN.INSTANTANEOUSPOINT I, \
315 MEDIAN.MEASUREMENT M \
316 WHERE I.SERIESID = ? AND \
317 I.POINTSPEC = 4 AND \
318 I.FEATUREID = M.FEATUREID AND \
319 M.SOURCEID = ? \
320 ORDER BY I.TIMEVALUE
321
322 verticalprofile_instantaneouspoint_parameter = SELECT DISTINCT \
323 P.PARAMETERID KEY , \
324 P.GERMANNAME VALUE \
325 FROM MEDIAN.PARAMETER P, \
326 MEDIAN.INSTANTANEOUSPOINT IP, \
327 MEDIAN.MEASUREMENT M, \
328 MEDIAN.MEASUREDSCALARVALUE MSV \
329 WHERE IP.FEATUREID = M.FEATUREID AND \
330 M.MEASUREMENTID = MSV.MEASUREMENTID AND \
331 MSV.PARAMETERID = P.PARAMETERID AND \
332 IP.FEATUREID = ? \
333 ORDER BY P.GERMANNAME
334
335 verticalprofile_instantaneouspoint_chart_data = SELECT M.ZLOCATION XORDINATE, \
336 MSV.DATAVALUE YORDINATE, \
337 MSV.PARAMETERID GROUP1, \
338 IP.TIMEVALUE GROUP2, \
339 1 GROUP3 \
340 FROM MEDIAN.INSTANTANEOUSPOINT IP, \
341 MEDIAN.MEASUREMENT M, \
342 MEDIAN.MEASUREDSCALARVALUE MSV \
343 WHERE IP.FEATUREID = M.FEATUREID AND \
344 M.MEASUREMENTID = MSV.MEASUREMENTID AND \
345 IP.FEATUREID = ? AND \
346 MSV.PARAMETERID in (?) \
347 ORDER BY IP.TIMEVALUE, \
348 MSV.PARAMETERID, \
349 M.ZLOCATION
350
351 #############################################
352 #############################################
353 #### Horizontalprofil InstantaneousPoint ####
354 #############################################
355 #############################################
356 horizontalprofile_instantaneouspoint_vehicle = SELECT DISTINCT \
357 V.VEHICLEID KEY, \
358 V.NAME VALUE \
359 FROM MEDIAN.VEHICLE V, \
360 MEDIAN.CRUISE C, \
361 MEDIAN.TRACK T, \
362 MEDIAN.SURVEYINFO S, \
363 MEDIAN.INSTANTANEOUSPOINT I, \
364 MEDIAN.MEASUREMENT M \
365 WHERE V.VEHICLEID = C.VEHICLEID AND \
366 C.CRUISEID = T.CRUISEID AND \
367 T.TRACKID = S.TRACKID AND \
368 S.SURVEYID = I.SURVEYID AND \
369 I.FEATUREID = M.FEATUREID AND \
370 M.SOURCEID = ? \
371 ORDER BY V.NAME
372 horizontalprofile_instantaneouspoint_cruise = SELECT DISTINCT \
373 C.CRUISEID KEY , \
374 C.NAME VALUE \
375 FROM MEDIAN.CRUISE C, \
376 MEDIAN.TRACK T, \
377 MEDIAN.SURVEYINFO S, \
378 MEDIAN.INSTANTANEOUSPOINT I, \
379 MEDIAN.MEASUREMENT M \
380 WHERE C.CRUISEID = T.CRUISEID AND \
381 T.TRACKID = S.TRACKID AND \
382 S.SURVEYID = I.SURVEYID AND \
383 I.FEATUREID = M.FEATUREID AND \
384 C.VEHICLEID = ? AND \
385 M.SOURCEID = ? AND \
386 C.NAME IS NOT NULL \
387 ORDER BY C.NAME
388 horizontalprofile_instantaneouspoint_track= SELECT \
389 T.TRACKID KEY , \
390 to_char(T.STARTDATE,'DD.MM.YYYY HH24:MI') || ' - '|| to_char(T.ENDDATE,'DD.MM.YYYY HH24:MI') || ' - '|| T.NAME VALUE \
391 FROM MEDIAN.TRACK T \
392 WHERE T.CRUISEID = ? \
393 ORDER BY T.STARTDATE, \
394 T.ENDDATE, \
395 T.NAME
396 horizontalprofile_instantaneouspoint_surveyinfo = SELECT \
397 SURVEYID KEY , \
398 to_char(STARTDATE,'DD.MM.YYYY HH24:MI') || ' - '|| to_char(ENDDATE,'DD.MM.YYYY HH24:MI') || ' - '|| DESCRIPTION VALUE \
399 FROM MEDIAN.SURVEYINFO \
400 WHERE TRACKID = ? \
401 ORDER BY STARTDATE, \
402 ENDDATE, \
403 DESCRIPTION
404 horizontalprofile_instantaneouspoint_parameter = SELECT DISTINCT \
405 P.PARAMETERID KEY , \
406 P.GERMANNAME VALUE \
407 FROM MEDIAN.PARAMETER P , \
408 MEDIAN.MEASUREMENT M , \
409 MEDIAN.INSTANTANEOUSPOINT IP , \
410 MEDIAN.MEASUREDSCALARVALUE MSV \
411 WHERE P.PARAMETERID = MSV.PARAMETERID AND \
412 MSV.MEASUREMENTID = M.MEASUREMENTID AND \
413 M.FEATUREID = IP.FEATUREID AND \
414 IP.SURVEYID = ? \
415 ORDER BY P.GERMANNAME
416 horizontalprofile_instantaneouspoint_depth = SELECT DISTINCT \
417 M.ZLOCATION KEY, \
418 M.ZLOCATION VALUE \
419 FROM MEDIAN.MEASUREMENT M , \
420 MEDIAN.INSTANTANEOUSPOINT IP \
421 WHERE M.FEATUREID = IP.FEATUREID AND \
422 IP.SURVEYID = ? \
423 ORDER BY M.ZLOCATION
424 horizontalprofile_instantaneouspoint_chart_data = SELECT MSV.OBJECTID XORDINATE_XCOORD , \
425 MSV.OBJECTID XORDINATE_YCOORD , \
426 MSV.DATAVALUE YORDINATE , \
427 MSV.PARAMETERID GROUP1 , \
428 ZLOCATION GROUP2 , \
429 1 GROUP3 \
430 FROM MEDIAN.INSTANTANEOUSPOINT IP, \
431 MEDIAN.MEASUREMENT M, \
432 MEDIAN.MEASUREDSCALARVALUE MSV \
433 WHERE IP.FEATUREID = M.FEATUREID AND \
434 M.MEASUREMENTID = MSV.MEASUREMENTID AND \
435 IP.SURVEYID = ? AND \
436 M.ZLOCATION IN (?) AND \
437 MSV.PARAMETERID in (?) \
438 ORDER BY MSV.PARAMETERID, \
439 M.ZLOCATION
440
441 #############################################
442 #############################################
443 ########### Horizontalprofil MESH ###########
444 #############################################
445 #############################################
446 horizontalprofile_meshpoint_depth = SELECT DISTINCT \
447 mp.KPOSITION KEY, \
448 -ml.UPPERZLOCATION || ' - '|| -ml.LOWERZLOCATION VALUE \
449 from MEDIAN.MESHLAYER ml, \
450 MEDIAN.MESHPOINT mp \
451 where ml.KPOSITION = mp.KPOSITION and \
452 ml.MESHID = mp.MESHID and \
453 mp.FEATUREID in \
454 ( select FEATUREID \
455 from MEDIAN.MESHPOINT mp, \
456 MEDIAN.MESH m \
457 where m.OBJECTID = ? AND \
458 mp.MESHID = m.MESHID AND \
459 IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) and \
460 JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?)) \
461 order by mp.KPOSITION
462
463 horizontalprofile_mesh_chart_data = select mp.IPOSITION XORDINATE_XCOORD, mp.JPOSITION XORDINATE_YCOORD, \
464 msv.DATAVALUE YORDINATE, \
465 msv.PARAMETERID GROUP1, \
466 msv.TIMEVALUE GROUP2, \
467 mp.KPOSITION GROUP3 \
468 from MEDIAN.MESHLAYER ml, \
469 MEDIAN.MESHPOINT mp, \
470 MEDIAN.MESH m, \
471 MEDIAN.MESHSCALARVALUE msv \
472 where msv.FEATUREID = mp. FEATUREID AND \
473 ml.KPOSITION = mp.KPOSITION and \
474 ml.MESHID = mp.MESHID and \
475 m.MESHID = mp.MESHID AND \
476 m.PARTIDMIN <= msv.PARTID AND \
477 m.PARTIDMAX >= msv.PARTID AND \
478 msv.PARAMETERID in (?) AND \
479 msv.TIMEVALUE in (?) AND \
480 m.OBJECTID = ? AND \
481 mp.FEATUREID in \
482 ( select FEATUREID \
483 from MEDIAN.MESHPOINT mp, \
484 MEDIAN.MESH m \
485 where m.OBJECTID = ? AND \
486 mp.MESHID = m.MESHID AND \
487 KPOSITION in ( ? ) and \
488 ? = (select ? from MEDIAN.MESHPOINT where FEATUREID = ?)) \
489 order by mp.KPOSITION, \
490 msv.TIMEVALUE, \
491 msv.PARAMETERID, \
492 mp.JPOSITION , \
493 mp.IPOSITION
494
http://dive4elements.wald.intevation.org