Mercurial > dive4elements > gnv-client

comparison gnv-artifacts/doc/conf/queries.properties @ 376:d8f3ef441bf2

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
merged gnv-artifacts/0.3
author Thomas Arendsen Hein <thomas@intevation.de>
date Fri, 28 Sep 2012 12:13:47 +0200
parents 061355435075
children 88cd37c3b5e4
comparison
equal deleted inserted replaced
293:6b0ef2324d02 376:d8f3ef441bf2
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_timeseriespoint_with_area=SELECT \
17 MEDIAN.TIMESERIESPOINT.FEATUREID KEY, \
18 MEDIAN.TIMESERIESPOINT.NAME VALUE \
19 FROM MEDIAN.TIMESERIESPOINT, \
20 MEDIAN.MEASUREMENT MMT \
21 WHERE MEDIAN.TIMESERIESPOINT.FEATUREID = MMT.FEATUREID AND \
22 MMT.SOURCEID = ? AND \
23 INTERSECTS(SHAPE, \
24 (SELECT st_astext(SHAPE) \
25 FROM MEDIAN.FEATUREAREA \
26 WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \
27 FEATURECODE = ? )\
28 )\
29 ORDER BY MEDIAN.TIMESERIESPOINT.NAME
30
31 timeseries_parameter=SELECT DISTINCT \
32 p.PARAMETERID KEY, \
33 p.GERMANNAME || ' ['|| p.UNIT ||']' VALUE, \
34 p.GERMANNAME \
35 FROM MEDIAN.PARAMETER P, \
36 MEDIAN.TIMESERIES TS, \
37 MEDIAN.TIMESERIESVALUE TSV, \
38 MEDIAN.MEASUREMENT M, \
39 MEDIAN.TIMESERIESPOINT TSP \
40 WHERE M.FEATUREID = TSP.FEATUREID AND \
41 M.MEASUREMENTID = TSV.MEASUREMENTID AND \
42 TS.TIMESERIESID = TSV.TIMESERIESID AND \
43 P.PARAMETERID = TS.PARAMETERID AND \
44 TSP.FEATUREID = ? \
45 ORDER BY P.GERMANNAME
46
47 timeseries_depth_height=SELECT DISTINCT \
48 M.MEASUREMENTID KEY, \
49 M.ZLOCATION VALUE \
50 FROM MEDIAN.MEASUREMENT M, \
51 MEDIAN.TIMESERIESVALUE TSV, \
52 MEDIAN.TIMESERIES T, \
53 MEDIAN.PARAMETER P \
54 WHERE M.MEASUREMENTID = TSV.MEASUREMENTID AND \
55 TSV.TIMESERIESID = T.TIMESERIESID AND \
56 T.PARAMETERID = P.PARAMETERID AND \
57 M.FEATUREID = ? AND \
58 P.PARAMETERID IN (?)\
59 ORDER BY m.ZLOCATION DESC
60
61 timeseries_interval=select min(tv.TIMEVALUE) MIN, \
62 max(tv.TIMEVALUE) MAX \
63 from MEDIAN.TIMESERIES t, \
64 MEDIAN.TIMESERIESVALUE tv \
65 where tv.TIMESERIESID = t.TIMESERIESID AND \
66 t.PARAMETERID IN ( ? ) AND \
67 tv.MEASUREMENTID IN ( ? )
68
69 timeseries_chart_data=SELECT tv.TIMEVALUE XORDINATE, \
70 tv.DATAVALUE YORDINATE, \
71 t.PARAMETERID GROUP1, \
72 tv.MEASUREMENTID GROUP2, \
73 tv.TIMESERIESID GROUP3, \
74 t.TIMEINTERVAL GAPID \
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 timeseries_odv_data = SELECT SI.NAME CRUISE, \
88 MEDIAN.TIMESERIESPOINT.NAME STATION, \
89 '*' TYPE, \
90 ST_ASTEXT(SHAPE), \
91 0 BOTDEPTH, \
92 (M.ZLOCATION *-1) DEPTH, \
93 TSV.TIMEVALUE, \
94 TSV.DATAVALUE, \
95 TS.PARAMETERID PARAMETER, \
96 TSV.MEASUREMENTID, \
97 TSV.TIMESERIESID \
98 FROM MEDIAN.TIMESERIESPOINT, \
99 MEDIAN.TIMESERIESVALUE TSV, \
100 MEDIAN.TIMESERIES TS, \
101 MEDIAN.MEASUREMENT M, \
102 MEDIAN.SOURCEINFO SI \
103 WHERE SI.SOURCEID = M.SOURCEID AND \
104 M.FEATUREID = MEDIAN.TIMESERIESPOINT.FEATUREID AND \
105 M.MEASUREMENTID = TSV.MEASUREMENTID AND \
106 TSV.TIMESERIESID = TS.TIMESERIESID AND \
107 TS.PARAMETERID IN ( ? ) AND \
108 TSV.MEASUREMENTID IN ( ? ) AND \
109 TSV.TIMEVALUE >= ? AND \
110 TSV.TIMEVALUE <= ? \
111 ORDER BY TSV.MEASUREMENTID, \
112 TSV.TIMESERIESID, \
113 TS.PARAMETERID, \
114 TSV.TIMEVALUE
115
116 #############################################
117 #############################################
118 ########## Zeitserie Mesh ##############
119 #############################################
120 #############################################
121
122 timeseries_mesh = SELECT OBJECTID KEY, \
123 m.NAME VALUE \
124 FROM MEDIAN.MESH m \
125 WHERE SOURCEID IN (?) \
126 ORDER BY m.NAME
127 timeseries_meshpoint = SELECT MEDIAN.MESHPOINT.FEATUREID, \
128 ST_ASTEXT(SHAPE) \
129 FROM MEDIAN.MESHPOINT, \
130 MEDIAN.MESH M \
131 WHERE MEDIAN.MESHPOINT.MESHID = M.MESHID AND \
132 KPOSITION = 1 AND \
133 M.OBJECTID = ? AND \
134 INTERSECTS(SHAPE,"?")
135
136 timeseries_meshpoint_depth = SELECT MP.FEATUREID KEY, \
137 'Layer ' || ML.KPOSITION || ': ' || -ML.UPPERZLOCATION || ' - '|| -ML.LOWERZLOCATION VALUE \
138 from MEDIAN.MESHLAYER ML, \
139 MEDIAN.MESHPOINT MP, \
140 MEDIAN.MESH M \
141 WHERE ML.KPOSITION = MP.KPOSITION AND \
142 ML.MESHID = MP.MESHID AND \
143 M.OBJECTID = ? AND \
144 MP.MESHID = M.MESHID AND \
145 IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) AND \
146 JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) \
147 ORDER BY ML.UPPERZLOCATION DESC
148
149 timeseries_mesh_parameter=SELECT distinct \
150 p.PARAMETERID KEY, \
151 p.GERMANNAME || ' ['|| p.UNIT ||']' VALUE, \
152 p.GERMANNAME \
153 from MEDIAN.PARAMETER p, \
154 MEDIAN.MESHSCALARVALUE msc, \
155 MEDIAN.MESH m \
156 where m.OBJECTID = ? AND \
157 msc.PARTID = m.PARTIDMIN AND \
158 msc.PARAMETERID = p.PARAMETERID \
159 order by p.GERMANNAME
160 timeseries_mesh_interval=select /*+ parallel(TIMEVALUE,5) */ \
161 min(TIMEVALUE) MIN, \
162 max(TIMEVALUE) MAX \
163 from MEDIAN.MESHSCALARVALUE msc, \
164 MEDIAN.MESH m \
165 where m.OBJECTID = ? AND \
166 msc.PARTID >= m.PARTIDMIN AND \
167 msc.PARTID <= m.PARTIDMAX AND \
168 msc.PARAMETERID IN (?)
169
170 timeseries_mesh_chart_data=select /*+ parallel(timevalue,10) */ \
171 msv.TIMEVALUE XORDINATE, \
172 msv.DATAVALUE YORDINATE, \
173 msv.PARAMETERID GROUP1, \
174 msv.FEATUREID GROUP2, \
175 mp.FEATUREID GROUP3, \
176 -1 GAPID \
177 from MEDIAN.MESHSCALARVALUE msv, \
178 MEDIAN.MESHPOINT mp, \
179 MEDIAN.MESH m \
180 where (m.OBJECTID = ? AND \
181 msv.PARTID >= m.PARTIDMIN AND \
182 msv.PARTID <= m.PARTIDMAX ) AND \
183 msv.FEATUREID = mp.FEATUREID AND \
184 msv.PARAMETERID in ( ? ) AND \
185 mp.FEATUREID in ( ? ) AND \
186 msv.TIMEVALUE >= ? AND \
187 msv.TIMEVALUE <= ? \
188 order by mp.FEATUREID, \
189 msv.FEATUREID, \
190 msv.PARAMETERID, \
191 msv.TIMEVALUE
192
193 timeseries_mesh_odv_data = select /*+ parallel(timevalue,10) */ \
194 SI.NAME CRUISE, \
195 m.MESHID || '-' || MEDIAN.MESHPOINT.IPOSITION || '-' || MEDIAN.MESHPOINT.JPOSITION STATION, \
196 '*' TYPE, \
197 ST_ASTEXT(SHAPE), \
198 0 BOTDEPTH, \
199 (((ML.UPPERZLOCATION + ML.LOWERZLOCATION) / 2)*-1) DEPTH, \
200 msv.TIMEVALUE, \
201 msv.DATAVALUE, \
202 msv.PARAMETERID PARAMETER \
203 from MEDIAN.MESHSCALARVALUE msv, \
204 MEDIAN.MESHPOINT, \
205 MEDIAN.MESH m, \
206 MEDIAN.SOURCEINFO SI, \
207 MEDIAN.MESHLAYER ML \
208 where (m.OBJECTID = ? AND \
209 msv.PARTID >= m.PARTIDMIN AND \
210 msv.PARTID <= m.PARTIDMAX ) AND \
211 m.SOURCEID = SI.SOURCEID AND \
212 ML.MESHID = MEDIAN.MESHPOINT.MESHID AND \
213 ML.KPOSITION = MEDIAN.MESHPOINT.KPOSITION AND \
214 msv.FEATUREID = MEDIAN.MESHPOINT.FEATUREID AND \
215 msv.PARAMETERID in (? ) AND \
216 MEDIAN.MESHPOINT.FEATUREID in ( ? ) AND \
217 msv.TIMEVALUE >= ? AND \
218 msv.TIMEVALUE <= ? \
219 order by MEDIAN.MESHPOINT.FEATUREID, \
220 msv.FEATUREID, \
221 msv.PARAMETERID, \
222 msv.TIMEVALUE
223
224
225 #############################################
226 #############################################
227 ########## Vertikalprofil ##############
228 #############################################
229 #############################################
230
231 verticalprofile_point=SELECT DISTINCT \
232 tsp.FEATUREID KEY, \
233 tsp.NAME VALUE \
234 FROM MEDIAN.TIMESERIESPOINT tsp, \
235 MEDIAN.MEASUREMENT mmt \
236 WHERE tsp.FEATUREID = mmt.FEATUREID AND \
237 mmt.SOURCEID = ? \
238 order by tsp.name
239
240 verticalprofile_parameter=SELECT DISTINCT \
241 p.PARAMETERID KEY, \
242 p.GERMANNAME || ' ['|| p.UNIT ||']' VALUE, \
243 p.GERMANNAME \
244 from MEDIAN.PARAMETER p, \
245 MEDIAN.TIMESERIES ts, \
246 MEDIAN.TIMESERIESVALUE tsv, \
247 MEDIAN.MEASUREMENT m \
248 where ts.PARAMETERID = p.PARAMETERID and \
249 ts.TIMESERIESID = tsv.TIMESERIESID and \
250 m.MEASUREMENTID = tsv.MEASUREMENTID and \
251 m.FEATUREID = ? \
252 ORDER BY p.GERMANNAME
253
254 verticalprofile_minmaxdepth= SELECT min(M.ZLOCATION) MIN, \
255 max(M.ZLOCATION) MAX \
256 FROM MEDIAN.MEASUREMENT M, \
257 MEDIAN.TIMESERIES TS, \
258 MEDIAN.TIMESERIESVALUE TSV \
259 WHERE TS.TIMESERIESID = TSV.TIMESERIESID and \
260 M.MEASUREMENTID = TSV.MEASUREMENTID and \
261 M.FEATUREID = ? and \
262 TS.PARAMETERID IN ( ? )
263
264 verticalprofile_date=select distinct \
265 tsv.TIMEVALUE KEY, \
266 tsv.TIMEVALUE VALUE \
267 from MEDIAN.TIMESERIES ts, \
268 MEDIAN.TIMESERIESVALUE tsv, \
269 MEDIAN.MEASUREMENT m \
270 where ts.TIMESERIESID = tsv.TIMESERIESID and \
271 m.MEASUREMENTID = tsv.MEASUREMENTID and \
272 m.FEATUREID = ? and \
273 ts.PARAMETERID IN ( ? ) \
274 order by tsv.TIMEVALUE
275
276 verticalprofile_chart_data= SELECT M.ZLOCATION XORDINATE, \
277 TSV.DATAVALUE YORDINATE, \
278 TS.PARAMETERID GROUP1, \
279 TSV.TIMEVALUE GROUP2, \
280 1 GROUP3, \
281 1 DATAID \
282 from MEDIAN.TIMESERIES TS, \
283 MEDIAN.TIMESERIESVALUE TSV, \
284 MEDIAN.MEASUREMENT M \
285 where TS.TIMESERIESID = TSV.TIMESERIESID AND \
286 M.MEASUREMENTID = TSV.MEASUREMENTID AND \
287 M.FEATUREID = ? AND \
288 TS.PARAMETERID IN ( ? ) AND \
289 TSV.TIMEVALUE IN (?) AND \
290 M.ZLOCATION >= ? AND \
291 M.ZLOCATION <= ? \
292 ORDER BY TSV.TIMEVALUE, \
293 TSV.TIMESERIESID, \
294 TS.PARAMETERID, \
295 TSV.TIMEVALUE
296
297 verticalprofile_odv_data = SELECT SI.NAME CRUISE, \
298 MEDIAN.TIMESERIESPOINT.NAME STATION, \
299 '*' TYPE, \
300 ST_ASTEXT(SHAPE), \
301 0 BOTDEPTH, \
302 (M.ZLOCATION *-1) DEPTH, \
303 TSV.TIMEVALUE, \
304 TSV.DATAVALUE, \
305 TS.PARAMETERID PARAMETER, \
306 TSV.MEASUREMENTID, \
307 TSV.TIMESERIESID \
308 FROM MEDIAN.TIMESERIESPOINT, \
309 MEDIAN.TIMESERIESVALUE TSV, \
310 MEDIAN.TIMESERIES TS, \
311 MEDIAN.MEASUREMENT M, \
312 MEDIAN.SOURCEINFO SI \
313 WHERE SI.SOURCEID = M.SOURCEID AND \
314 M.FEATUREID = MEDIAN.TIMESERIESPOINT.FEATUREID AND \
315 M.MEASUREMENTID = TSV.MEASUREMENTID AND \
316 TSV.TIMESERIESID = TS.TIMESERIESID AND \
317 M.FEATUREID = ? AND \
318 TS.PARAMETERID IN ( ? ) AND \
319 TSV.TIMEVALUE IN (?) AND \
320 M.ZLOCATION >= ? AND \
321 M.ZLOCATION <= ? \
322 ORDER BY TSV.MEASUREMENTID, \
323 TSV.TIMESERIESID, \
324 TS.PARAMETERID, \
325 TSV.TIMEVALUE
326
327 #############################################
328 #############################################
329 ########## Vertikalprofil Mesh ##############
330 #############################################
331 #############################################
332
333 verticalprofile_mesh = SELECT OBJECTID KEY, \
334 m.NAME VALUE \
335 FROM MEDIAN.MESH m \
336 WHERE SOURCEID IN (?) \
337 order by m.NAME
338
339 verticalprofile_mesh_with_area = SELECT M.OBJECTID KEY, \
340 M.NAME VALUE \
341 FROM MEDIAN.MESHPOINT, \
342 MEDIAN.MESH M \
343 WHERE M.SOURCEID IN (?) AND \
344 M.MESHID = MEDIAN.MESHPOINT.MESHID AND \
345 INTERSECTS(SHAPE, \
346 (SELECT st_astext(SHAPE) \
347 FROM MEDIAN.FEATUREAREA \
348 WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \
349 FEATURECODE = ? )\
350 ) \
351 order by M.NAME
352
353 verticalprofile_mesh_point = SELECT FEATUREID, \
354 ST_ASTEXT(SHAPE) \
355 FROM MEDIAN.MESHPOINT \
356 WHERE MESHID in \
357 (SELECT DISTINCT MESHID \
358 FROM MEDIAN.MESH \
359 WHERE OBJECTID = ?) AND \
360 KPOSITION = 1 AND \
361 INTERSECTS(SHAPE,"?")
362
363 verticalprofile_mesh_mindepth = SELECT MP.KPOSITION KEY, \
364 'Layer ' || ML.KPOSITION || ': ' || -ML.UPPERZLOCATION || ' - '|| -ML.LOWERZLOCATION VALUE \
365 from MEDIAN.MESHLAYER ML, \
366 MEDIAN.MESHPOINT MP, \
367 MEDIAN.MESH M \
368 WHERE ML.KPOSITION = MP.KPOSITION AND \
369 ML.MESHID = MP.MESHID AND \
370 M.OBJECTID = ? AND \
371 MP.MESHID = M.MESHID AND \
372 IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) AND \
373 JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) \
374 ORDER BY ML.UPPERZLOCATION DESC
375
376 verticalprofile_mesh_maxdepth = SELECT MP.KPOSITION KEY, \
377 'Layer ' || ML.KPOSITION || ': ' || -ML.UPPERZLOCATION || ' - '|| -ML.LOWERZLOCATION VALUE \
378 from MEDIAN.MESHLAYER ML, \
379 MEDIAN.MESHPOINT MP, \
380 MEDIAN.MESH M \
381 WHERE ML.KPOSITION = MP.KPOSITION AND \
382 ML.MESHID = MP.MESHID AND \
383 M.OBJECTID = ? AND \
384 MP.MESHID = M.MESHID AND \
385 IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) AND \
386 JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) AND \
387 MP.KPOSITION < ? \
388 ORDER BY ML.UPPERZLOCATION DESC
389
390 verticalprofile_mesh_parameter=SELECT distinct \
391 p.PARAMETERID KEY, \
392 p.GERMANNAME || ' ['|| p.UNIT ||']' VALUE, \
393 p.GERMANNAME \
394 from MEDIAN.PARAMETER p, \
395 MEDIAN.MESHSCALARVALUE msc, \
396 MEDIAN.MESH m \
397 where m.OBJECTID = ? AND \
398 m.PARTIDMIN = msc.PARTID AND \
399 msc.PARAMETERID = p.PARAMETERID \
400 order by p.GERMANNAME
401
402 verticalprofile_mesh_date= select distinct \
403 msv.TIMEVALUE KEY, \
404 msv.TIMEVALUE VALUE \
405 from MEDIAN.MESHSCALARVALUE msv, \
406 MEDIAN.MESH m \
407 where m.OBJECTID = ? AND \
408 msv.PARTID >= m.PARTIDMIN AND \
409 msv.PARTID <= m.PARTIDMAX AND \
410 msv.PARAMETERID in (?) \
411 order by msv.TIMEVALUE
412
413 verticalprofile_mesh_chart_data=select ml.UPPERZLOCATION XORDINATE, \
414 msv.DATAVALUE YORDINATE, \
415 mp.KPOSITION KPOSITION, \
416 msv.PARAMETERID GROUP1, \
417 msv.TIMEVALUE GROUP2, \
418 1 GROUP3, \
419 2 DATAID \
420 from MEDIAN.MESHLAYER ml, \
421 MEDIAN.MESHPOINT mp, \
422 MEDIAN.MESH m, \
423 MEDIAN.MESHSCALARVALUE msv \
424 where msv.FEATUREID = mp. FEATUREID AND \
425 ml.KPOSITION = mp.KPOSITION and \
426 ml.MESHID = mp.MESHID and \
427 m.MESHID = mp.MESHID AND \
428 m.PARTIDMIN <= msv.PARTID AND \
429 m.PARTIDMAX >= msv.PARTID AND \
430 msv.PARAMETERID in (?) AND \
431 msv.TIMEVALUE in (?) AND \
432 m.OBJECTID = ? AND \
433 mp.FEATUREID in \
434 ( select FEATUREID \
435 from MEDIAN.MESHPOINT mp, \
436 MEDIAN.MESH m \
437 where m.OBJECTID = ? AND \
438 mp.MESHID = m.MESHID AND \
439 IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) and \
440 JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?)) AND \
441 mp.KPOSITION <= ? AND \
442 mp.KPOSITION >= ? \
443 order by msv.PARAMETERID, \
444 msv.TIMEVALUE, \
445 ml.UPPERZLOCATION
446
447 verticalprofile_mesh_odv_data=select SI.NAME CRUISE, \
448 m.MESHID || '-' || MEDIAN.MESHPOINT.IPOSITION || '-' || MEDIAN.MESHPOINT.JPOSITION STATION, \
449 '*' TYPE, \
450 ST_ASTEXT(SHAPE), \
451 0 BOTDEPTH, \
452 (((ML.UPPERZLOCATION + ML.LOWERZLOCATION) / 2)*-1) DEPTH, \
453 msv.DATAVALUE, \
454 msv.PARAMETERID PARAMETER, \
455 msv.TIMEVALUE \
456 from MEDIAN.MESHLAYER ml, \
457 MEDIAN.MESHPOINT, \
458 MEDIAN.MESH m, \
459 MEDIAN.MESHSCALARVALUE msv, \
460 MEDIAN.SOURCEINFO SI \
461 where m.SOURCEID = SI.SOURCEID AND \
462 msv.FEATUREID = MEDIAN.MESHPOINT. FEATUREID AND \
463 ml.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \
464 ml.MESHID = MEDIAN.MESHPOINT.MESHID and \
465 m.MESHID = MEDIAN.MESHPOINT.MESHID AND \
466 m.PARTIDMIN <= msv.PARTID AND \
467 m.PARTIDMAX >= msv.PARTID AND \
468 msv.PARAMETERID in (?) AND \
469 msv.TIMEVALUE in (?) AND \
470 m.OBJECTID = ? AND \
471 MEDIAN.MESHPOINT.FEATUREID in \
472 ( select FEATUREID \
473 from MEDIAN.MESHPOINT mp, \
474 MEDIAN.MESH m \
475 where m.OBJECTID = ? AND \
476 mp.MESHID = m.MESHID AND \
477 IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) and \
478 JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?)) AND \
479 MEDIAN.MESHPOINT.KPOSITION <= ? AND \
480 MEDIAN.MESHPOINT.KPOSITION >= ? \
481 order by msv.PARAMETERID, \
482 msv.TIMEVALUE, \
483 ml.UPPERZLOCATION
484
485 #############################################
486 #############################################
487 ##### Vertikalprofil InstantaneousPoint #####
488 #############################################
489 #############################################
490
491 verticalprofile_instantaneouspoint_series = SELECT DISTINCT \
492 S.SERIESID KEY, \
493 S.DESCRIPTION VALUE \
494 FROM MEDIAN.SERIES S, \
495 MEDIAN.INSTANTANEOUSPOINT I, \
496 MEDIAN.MEASUREMENT M \
497 WHERE S.SERIESID = I.SERIESID AND \
498 I.POINTSPEC = 4 AND \
499 I.FEATUREID = M.FEATUREID AND \
500 M.SOURCEID= ? \
501 ORDER BY S.DESCRIPTION
502
503 verticalprofile_instantaneouspoint_series_with_area = SELECT \
504 S.SERIESID KEY, \
505 S.DESCRIPTION VALUE \
506 FROM MEDIAN.INSTANTANEOUSPOINT, \
507 MEDIAN.SERIES S, \
508 MEDIAN.MEASUREMENT M \
509 WHERE S.SERIESID = MEDIAN.INSTANTANEOUSPOINT.SERIESID AND \
510 MEDIAN.INSTANTANEOUSPOINT.POINTSPEC = 4 AND \
511 MEDIAN.INSTANTANEOUSPOINT.FEATUREID = M.FEATUREID AND \
512 M.SOURCEID= ? AND \
513 INTERSECTS(SHAPE, \
514 (SELECT st_astext(SHAPE) \
515 FROM MEDIAN.FEATUREAREA \
516 WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \
517 FEATURECODE = ? )\
518 ) \
519 ORDER BY S.DESCRIPTION
520
521 verticalprofile_instantaneouspoint_point = SELECT \
522 MEDIAN.INSTANTANEOUSPOINT.FEATUREID, \
523 to_char(MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE,'DD.MM.YYYY HH24:MI') VALUE, \
524 MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE, \
525 ST_ASTEXT(SHAPE) \
526 FROM MEDIAN.INSTANTANEOUSPOINT, \
527 MEDIAN.MEASUREMENT M \
528 WHERE MEDIAN.INSTANTANEOUSPOINT.SERIESID = ? AND \
529 MEDIAN.INSTANTANEOUSPOINT.POINTSPEC = 4 AND \
530 MEDIAN.INSTANTANEOUSPOINT.FEATUREID = M.FEATUREID AND \
531 M.SOURCEID = ? \
532 ORDER BY MEDIAN.INSTANTANEOUSPOINT.FEATUREID, \
533 MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE
534
535 verticalprofile_instantaneouspoint_parameter = SELECT DISTINCT \
536 P.PARAMETERID KEY, \
537 p.GERMANNAME || ' ['|| p.UNIT ||']' VALUE, \
538 p.GERMANNAME \
539 FROM MEDIAN.PARAMETER P, \
540 MEDIAN.INSTANTANEOUSPOINT IP, \
541 MEDIAN.MEASUREMENT M, \
542 MEDIAN.MEASUREDSCALARVALUE MSV \
543 WHERE IP.FEATUREID = M.FEATUREID AND \
544 M.MEASUREMENTID = MSV.MEASUREMENTID AND \
545 MSV.PARAMETERID = P.PARAMETERID AND \
546 IP.FEATUREID = ? \
547 ORDER BY P.GERMANNAME
548
549 verticalprofile_instantaneouspoint_minmaxdepth = SELECT min(M.ZLOCATION) MIN, \
550 max(M.ZLOCATION) MAX \
551 FROM MEDIAN.INSTANTANEOUSPOINT IP, \
552 MEDIAN.MEASUREMENT M, \
553 MEDIAN.MEASUREDSCALARVALUE MSV \
554 WHERE IP.FEATUREID = M.FEATUREID AND \
555 M.MEASUREMENTID = MSV.MEASUREMENTID AND \
556 IP.FEATUREID = ? AND \
557 MSV.PARAMETERID in (?)
558
559 verticalprofile_instantaneouspoint_chart_data = SELECT M.ZLOCATION XORDINATE, \
560 MSV.DATAVALUE YORDINATE, \
561 MSV.PARAMETERID GROUP1, \
562 IP.TIMEVALUE GROUP2, \
563 1 GROUP3, \
564 3 DATAID \
565 FROM MEDIAN.INSTANTANEOUSPOINT IP, \
566 MEDIAN.MEASUREMENT M, \
567 MEDIAN.MEASUREDSCALARVALUE MSV \
568 WHERE IP.FEATUREID = M.FEATUREID AND \
569 M.MEASUREMENTID = MSV.MEASUREMENTID AND \
570 IP.FEATUREID = ? AND \
571 MSV.PARAMETERID in (?) AND \
572 M.ZLOCATION >= ? AND \
573 M.ZLOCATION <= ? \
574 ORDER BY IP.TIMEVALUE, \
575 MSV.PARAMETERID, \
576 M.ZLOCATION
577
578 verticalprofile_instantaneouspoint_odv_data = SELECT S.DESCRIPTION CRUISE, \
579 MEDIAN.INSTANTANEOUSPOINT.FEATUREID STATION, \
580 '*' TYPE, \
581 ST_ASTEXT(SHAPE), \
582 0 BOTDEPTH, \
583 (M.ZLOCATION * -1) DEPTH, \
584 MSV.DATAVALUE, \
585 MSV.PARAMETERID PARAMETER, \
586 MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE \
587 FROM MEDIAN.SERIES S, \
588 MEDIAN.INSTANTANEOUSPOINT, \
589 MEDIAN.MEASUREMENT M, \
590 MEDIAN.MEASUREDSCALARVALUE MSV \
591 WHERE S.SERIESID = MEDIAN.INSTANTANEOUSPOINT.SERIESID AND \
592 MEDIAN.INSTANTANEOUSPOINT.FEATUREID = M.FEATUREID AND \
593 M.MEASUREMENTID = MSV.MEASUREMENTID AND \
594 MEDIAN.INSTANTANEOUSPOINT.FEATUREID = ? AND \
595 MSV.PARAMETERID in (?) AND \
596 M.ZLOCATION >= ? AND \
597 M.ZLOCATION <= ? \
598 ORDER BY MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE, \
599 MSV.PARAMETERID, \
600 M.ZLOCATION
601
602 #############################################
603 #############################################
604 #### Horizontalprofil InstantaneousPoint ####
605 #############################################
606 #############################################
607 horizontalprofile_instantaneouspoint_vehicle = SELECT DISTINCT \
608 V.VEHICLEID KEY, \
609 V.NAME VALUE \
610 FROM MEDIAN.VEHICLE V, \
611 MEDIAN.CRUISE C, \
612 MEDIAN.TRACK T, \
613 MEDIAN.SURVEYINFO S, \
614 MEDIAN.INSTANTANEOUSPOINT I, \
615 MEDIAN.MEASUREMENT M \
616 WHERE V.VEHICLEID = C.VEHICLEID AND \
617 C.CRUISEID = T.CRUISEID AND \
618 T.TRACKID = S.TRACKID AND \
619 S.SURVEYID = I.SURVEYID AND \
620 I.FEATUREID = M.FEATUREID AND \
621 M.SOURCEID = ? \
622 ORDER BY V.NAME
623
624 horizontalprofile_instantaneouspoint_vehicle_with_area = SELECT V.VEHICLEID KEY, \
625 V.NAME VALUE \
626 FROM MEDIAN.TRACK, \
627 MEDIAN.VEHICLE V, \
628 MEDIAN.CRUISE C \
629 WHERE V.VEHICLEID = C.VEHICLEID AND \
630 C.CRUISEID = MEDIAN.TRACK.CRUISEID AND \
631 MEDIAN.TRACK.TRACKID IN \
632 (SELECT DISTINCT S.TRACKID \
633 FROM MEDIAN.MEASUREMENT M ,\
634 MEDIAN.INSTANTANEOUSPOINT I, \
635 MEDIAN.SURVEYINFO S \
636 WHERE M.SOURCEID = ? AND \
637 I.FEATUREID = M.FEATUREID AND \
638 S.SURVEYID = I.SURVEYID)AND \
639 INTERSECTS(SHAPE, \
640 (SELECT st_astext(SHAPE) \
641 FROM MEDIAN.FEATUREAREA \
642 WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \
643 FEATURECODE = ? )\
644 ) \
645 ORDER BY V.NAME
646 horizontalprofile_instantaneouspoint_cruise = SELECT DISTINCT \
647 C.CRUISEID KEY, \
648 V.NAME || ' ' || \
649 C.NAME || ' ' || \
650 TO_CHAR(C.STARTDATE,'DD.MM.YYYY') || ' - ' || \
651 TO_CHAR(C.ENDDATE,'DD.MM.YYYY') VALUE, \
652 V.NAME, \
653 C.NAME \
654 FROM MEDIAN.CRUISE C, \
655 MEDIAN.VEHICLE V, \
656 MEDIAN.TRACK T, \
657 MEDIAN.SURVEYINFO S, \
658 MEDIAN.INSTANTANEOUSPOINT I, \
659 MEDIAN.MEASUREMENT M \
660 WHERE C.VEHICLEID = V.VEHICLEID AND \
661 C.CRUISEID = T.CRUISEID AND \
662 T.TRACKID = S.TRACKID AND \
663 S.SURVEYID = I.SURVEYID AND \
664 I.FEATUREID = M.FEATUREID AND \
665 C.VEHICLEID = ? AND \
666 M.SOURCEID = ? \
667 ORDER BY V.NAME, C.NAME
668
669 horizontalprofile_instantaneouspoint_cruise_with_area = SELECT C.CRUISEID KEY, \
670 V.NAME || ' ' || \
671 C.NAME || ' ' || \
672 TO_CHAR(C.STARTDATE,'DD.MM.YYYY') || ' - ' || \
673 TO_CHAR(C.ENDDATE,'DD.MM.YYYY') VALUE, \
674 V.NAME, \
675 C.NAME \
676 FROM MEDIAN.TRACK, \
677 MEDIAN.VEHICLE V, \
678 MEDIAN.CRUISE C \
679 WHERE V.VEHICLEID = C.VEHICLEID AND \
680 C.CRUISEID = MEDIAN.TRACK.CRUISEID AND \
681 MEDIAN.TRACK.TRACKID IN \
682 (SELECT DISTINCT S.TRACKID \
683 FROM MEDIAN.MEASUREMENT M ,\
684 MEDIAN.INSTANTANEOUSPOINT I, \
685 MEDIAN.SURVEYINFO S \
686 WHERE M.SOURCEID = ? AND \
687 C.VEHICLEID = ? AND \
688 I.FEATUREID = M.FEATUREID AND \
689 S.SURVEYID = I.SURVEYID)AND \
690 INTERSECTS(SHAPE, \
691 (SELECT st_astext(SHAPE) \
692 FROM MEDIAN.FEATUREAREA \
693 WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \
694 FEATURECODE = ? )\
695 ) \
696 ORDER BY V.NAME
697 horizontalprofile_instantaneouspoint_track= SELECT \
698 T.TRACKID KEY, \
699 to_char(T.STARTDATE,'DD.MM.YYYY HH24:MI') || ' - '|| to_char(T.ENDDATE,'DD.MM.YYYY HH24:MI') || ' - '|| T.NAME VALUE \
700 FROM MEDIAN.TRACK T \
701 WHERE T.CRUISEID = ? \
702 ORDER BY T.STARTDATE, \
703 T.ENDDATE, \
704 T.NAME
705
706 horizontalprofile_instantaneouspoint_track_with_area = SELECT \
707 MEDIAN.TRACK.TRACKID KEY, \
708 to_char(STARTDATE,'DD.MM.YYYY HH24:MI') || ' - '|| to_char(ENDDATE,'DD.MM.YYYY HH24:MI') || ' - '|| NAME VALUE, \
709 NAME \
710 FROM MEDIAN.TRACK \
711 WHERE MEDIAN.TRACK.CRUISEID = ? AND \
712 INTERSECTS(SHAPE, \
713 (SELECT st_astext(SHAPE) \
714 FROM MEDIAN.FEATUREAREA \
715 WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \
716 FEATURECODE = ? )\
717 ) \
718 ORDER BY STARTDATE, \
719 ENDDATE, \
720 NAME
721 horizontalprofile_instantaneouspoint_surveyinfo = SELECT \
722 SURVEYID KEY, \
723 to_char(STARTDATE,'DD.MM.YYYY HH24:MI') || ' - '|| to_char(ENDDATE,'DD.MM.YYYY HH24:MI') || ' - '|| DESCRIPTION VALUE \
724 FROM MEDIAN.SURVEYINFO \
725 WHERE TRACKID = ? \
726 ORDER BY STARTDATE, \
727 ENDDATE, \
728 DESCRIPTION
729 horizontalprofile_instantaneouspoint_parameter = SELECT DISTINCT \
730 P.PARAMETERID KEY, \
731 p.GERMANNAME || ' ['|| p.UNIT ||']' VALUE, \
732 p.GERMANNAME \
733 FROM MEDIAN.PARAMETER P, \
734 MEDIAN.MEASUREMENT M, \
735 MEDIAN.INSTANTANEOUSPOINT IP, \
736 MEDIAN.MEASUREDSCALARVALUE MSV \
737 WHERE P.PARAMETERID = MSV.PARAMETERID AND \
738 MSV.MEASUREMENTID = M.MEASUREMENTID AND \
739 M.FEATUREID = IP.FEATUREID AND \
740 IP.SURVEYID = ? \
741 ORDER BY P.GERMANNAME
742 horizontalprofile_instantaneouspoint_depth = SELECT DISTINCT \
743 M.ZLOCATION KEY, \
744 M.ZLOCATION VALUE \
745 FROM MEDIAN.MEASUREMENT M, \
746 MEDIAN.INSTANTANEOUSPOINT IP \
747 WHERE M.FEATUREID = IP.FEATUREID AND \
748 IP.SURVEYID = ? \
749 ORDER BY M.ZLOCATION
750 horizontalprofile_instantaneouspoint_chart_data = SELECT ST_ASTEXT(SHAPE), \
751 MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE TIME, \
752 MSV.DATAVALUE YORDINATE, \
753 MSV.PARAMETERID GROUP1, \
754 ZLOCATION GROUP2, \
755 1 GROUP3, \
756 3 DATAID \
757 FROM MEDIAN.INSTANTANEOUSPOINT, \
758 MEDIAN.MEASUREMENT M, \
759 MEDIAN.MEASUREDSCALARVALUE MSV \
760 WHERE MEDIAN.INSTANTANEOUSPOINT.FEATUREID = M.FEATUREID AND \
761 M.MEASUREMENTID = MSV.MEASUREMENTID AND \
762 MEDIAN.INSTANTANEOUSPOINT.SURVEYID = ? AND \
763 M.ZLOCATION IN (?) AND \
764 MSV.PARAMETERID in (?) \
765 ORDER BY MSV.PARAMETERID, \
766 M.ZLOCATION, \
767 MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE
768 horizontalprofile_instantaneouspoint_odv_data = SELECT V.NAME || ' ' || C.NAME CRUISE, \
769 MEDIAN.InstantaneousPoint.FEATUREID STATION, \
770 '*' TYPE, \
771 0 BOTDEPTH, \
772 (M.ZLOCATION * -1) DEPTH, \
773 ST_ASTEXT(SHAPE), \
774 MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE, \
775 MSV.DATAVALUE, \
776 MSV.PARAMETERID PARAMETER \
777 FROM MEDIAN.INSTANTANEOUSPOINT, \
778 MEDIAN.VEHICLE V, \
779 MEDIAN.CRUISE C, \
780 (SELECT TRACKID,CRUISEID FROM MEDIAN.TRACK) T, \
781 MEDIAN.SURVEYINFO S, \
782 MEDIAN.MEASUREMENT M, \
783 MEDIAN.MEASUREDSCALARVALUE MSV \
784 WHERE V.VEHICLEID = C.VEHICLEID AND \
785 C.CRUISEID = T.CRUISEID AND \
786 T.TRACKID = S.TRACKID AND \
787 S.SURVEYID = MEDIAN.INSTANTANEOUSPOINT.SURVEYID AND \
788 MEDIAN.INSTANTANEOUSPOINT.FEATUREID = M.FEATUREID AND \
789 M.MEASUREMENTID = MSV.MEASUREMENTID AND \
790 MEDIAN.INSTANTANEOUSPOINT.SURVEYID = ? AND \
791 M.ZLOCATION IN (?) AND \
792 MSV.PARAMETERID in (?) \
793 ORDER BY MSV.PARAMETERID, \
794 M.ZLOCATION, \
795 MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE
796
797 #############################################
798 #############################################
799 ########### Horizontalprofil MESH ###########
800 #############################################
801 #############################################
802 horizontalprofile_meshpoint_depth = SELECT DISTINCT \
803 mp.KPOSITION KEY, \
804 'Layer ' || ml.KPOSITION || ': ' || -ml.UPPERZLOCATION || ' - '|| -ml.LOWERZLOCATION VALUE \
805 from MEDIAN.MESHLAYER ml, \
806 MEDIAN.MESHPOINT mp \
807 where ml.KPOSITION = mp.KPOSITION and \
808 ml.MESHID = mp.MESHID and \
809 mp.FEATUREID in \
810 ( select FEATUREID \
811 from MEDIAN.MESHPOINT mp, \
812 MEDIAN.MESH m \
813 where m.OBJECTID = ? AND \
814 mp.MESHID = m.MESHID AND \
815 IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) and \
816 JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?)) \
817 order by mp.KPOSITION
818
819 horizontalprofile_mesh_chart_data = SELECT ST_ASTEXT(SHAPE), \
820 msv.DATAVALUE YORDINATE, \
821 msv.PARAMETERID GROUP1, \
822 MEDIAN.MESHPOINT.KPOSITION GROUP2, \
823 msv.TIMEVALUE GROUP3, \
824 MEDIAN.MESHPOINT.JPOSITION, \
825 MEDIAN.MESHPOINT.IPOSITION, \
826 2 DATAID \
827 from MEDIAN.MESHLAYER ml, \
828 MEDIAN.MESHPOINT, \
829 MEDIAN.MESH m, \
830 MEDIAN.MESHSCALARVALUE msv \
831 where msv.FEATUREID = MEDIAN.MESHPOINT. FEATUREID AND \
832 ml.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \
833 ml.MESHID = MEDIAN.MESHPOINT.MESHID and \
834 m.MESHID = MEDIAN.MESHPOINT.MESHID AND \
835 m.PARTIDMIN <= msv.PARTID AND \
836 m.PARTIDMAX >= msv.PARTID AND \
837 msv.PARAMETERID in (?) AND \
838 msv.TIMEVALUE in (?) AND \
839 m.OBJECTID = ? AND \
840 MEDIAN.MESHPOINT.FEATUREID in \
841 ( select FEATUREID \
842 from MEDIAN.MESHPOINT mp, \
843 MEDIAN.MESH m \
844 where m.OBJECTID = ? AND \
845 mp.MESHID = m.MESHID AND \
846 KPOSITION in ( ? ) and \
847 ? = (select ? from MEDIAN.MESHPOINT where FEATUREID = ?)) \
848 order by msv.TIMEVALUE, \
849 MEDIAN.MESHPOINT.KPOSITION, \
850 msv.PARAMETERID, \
851 MEDIAN.MESHPOINT.JPOSITION, \
852 MEDIAN.MESHPOINT.IPOSITION
853
854 horizontalprofile_mesh_odv_data = SELECT SI.NAME CRUISE, \
855 m.MESHID || '-' || MEDIAN.MESHPOINT.IPOSITION || '-' || MEDIAN.MESHPOINT.JPOSITION STATION, \
856 '*' TYPE, \
857 ST_ASTEXT(SHAPE), \
858 0 BOTDEPTH, \
859 (((ML.UPPERZLOCATION + ML.LOWERZLOCATION) / 2)*-1) DEPTH, \
860 msv.DATAVALUE, \
861 msv.PARAMETERID PARAMETER, \
862 msv.TIMEVALUE, \
863 MEDIAN.MESHPOINT.JPOSITION, \
864 MEDIAN.MESHPOINT.IPOSITION \
865 from MEDIAN.MESHLAYER ml, \
866 MEDIAN.MESHPOINT, \
867 MEDIAN.MESH m, \
868 MEDIAN.MESHSCALARVALUE msv, \
869 MEDIAN.SOURCEINFO SI \
870 where msv.FEATUREID = MEDIAN.MESHPOINT. FEATUREID AND \
871 ml.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \
872 ml.MESHID = MEDIAN.MESHPOINT.MESHID and \
873 m.MESHID = MEDIAN.MESHPOINT.MESHID AND \
874 m.SOURCEID = SI.SOURCEID AND \
875 m.PARTIDMIN <= msv.PARTID AND \
876 m.PARTIDMAX >= msv.PARTID AND \
877 msv.PARAMETERID in (?) AND \
878 msv.TIMEVALUE in (?) AND \
879 m.OBJECTID = ? AND \
880 MEDIAN.MESHPOINT.FEATUREID in \
881 ( select FEATUREID \
882 from MEDIAN.MESHPOINT mp, \
883 MEDIAN.MESH m \
884 where m.OBJECTID = ? AND \
885 mp.MESHID = m.MESHID AND \
886 KPOSITION in ( ? ) and \
887 ? = (select ? from MEDIAN.MESHPOINT where FEATUREID = ?)) \
888 order by msv.TIMEVALUE, \
889 MEDIAN.MESHPOINT.KPOSITION, \
890 msv.PARAMETERID, \
891 MEDIAN.MESHPOINT.JPOSITION, \
892 MEDIAN.MESHPOINT.IPOSITION
893
894 #############################################
895 #############################################
896 ########### Horizontalprofil MESH ###########
897 ########### Schnittprofil ###########
898 #############################################
899 #############################################
900 horizontalprofile_meshpoint_depth = SELECT DISTINCT \
901 mp.KPOSITION KEY, \
902 'Layer ' || ml.KPOSITION || ': ' || -ml.UPPERZLOCATION || ' - '|| -ml.LOWERZLOCATION VALUE \
903 from MEDIAN.MESHLAYER ml, \
904 MEDIAN.MESHPOINT mp \
905 where ml.KPOSITION = mp.KPOSITION and \
906 ml.MESHID = mp.MESHID and \
907 mp.FEATUREID in \
908 ( select FEATUREID \
909 from MEDIAN.MESHPOINT mp, \
910 MEDIAN.MESH m \
911 where m.OBJECTID = ? AND \
912 mp.MESHID = m.MESHID )\
913 order by mp.KPOSITION
914
915 horizontalprofile_meshpoint_cross_ij=SELECT MEDIAN.MESHFACE.JPOSITION, \
916 MEDIAN.MESHFACE.IPOSITION \
917 FROM MEDIAN.MESHFACE, \
918 MEDIAN.MESH M \
919 WHERE MEDIAN.MESHFACE.KPOSITION = 1 AND \
920 M.MESHID = MEDIAN.MESHFACE.MESHID AND \
921 M.OBJECTID = ? AND \
922 INTERSECTS(SHAPE, "?")
923 horizontalprofile_mesh_cross_chart_data = SELECT ST_ASTEXT(SHAPE), \
924 msv.DATAVALUE YORDINATE, \
925 msv.PARAMETERID GROUP1, \
926 MEDIAN.MESHPOINT.KPOSITION GROUP2, \
927 msv.TIMEVALUE GROUP3, \
928 MEDIAN.MESHPOINT.JPOSITION, \
929 MEDIAN.MESHPOINT.IPOSITION, \
930 2 DATAID \
931 from MEDIAN.MESHLAYER ml, \
932 MEDIAN.MESHPOINT, \
933 MEDIAN.MESH m, \
934 MEDIAN.MESHSCALARVALUE msv \
935 where msv.FEATUREID = MEDIAN.MESHPOINT.FEATUREID AND \
936 ml.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \
937 ml.MESHID = MEDIAN.MESHPOINT.MESHID and \
938 m.MESHID = MEDIAN.MESHPOINT.MESHID AND \
939 m.PARTIDMIN <= msv.PARTID AND \
940 m.PARTIDMAX >= msv.PARTID AND \
941 msv.PARAMETERID in (?) AND \
942 msv.TIMEVALUE in (?) AND \
943 m.OBJECTID = ? AND \
944 ml.KPOSITION in (?) AND \
945 MEDIAN.MESHPOINT.FEATUREID in \
946 ( \
947 SELECT distinct FEATUREID \
948 FROM MEDIAN.MESHPOINT \
949 WHERE ? \
950 ) \
951 order by msv.TIMEVALUE, \
952 MEDIAN.MESHPOINT.KPOSITION, \
953 msv.PARAMETERID, \
954 MEDIAN.MESHPOINT.JPOSITION, \
955 MEDIAN.MESHPOINT.IPOSITION
956 #############################################
957 #############################################
958 ############# Profilschnitt MESH ############
959 #############################################
960 #############################################
961 verticalcrosssection_mesh_date = select distinct \
962 msv.TIMEVALUE KEY, \
963 msv.TIMEVALUE VALUE \
964 from MEDIAN.MESHSCALARVALUE msv, \
965 MEDIAN.MESH m \
966 where m.OBJECTID = ? AND \
967 msv.PARTID >= m.PARTIDMIN AND \
968 msv.PARTID <= m.PARTIDMAX AND \
969 msv.PARAMETERID = ? \
970 order by msv.TIMEVALUE
971 verticalcrosssection_mesh_chart_data = SELECT ST_ASTEXT(SHAPE), \
972 ((ml.UPPERZLOCATION + ml.LOWERZLOCATION) / 2) Z, \
973 msv.DATAVALUE YORDINATE, \
974 msv.PARAMETERID GROUP1, \
975 msv.TIMEVALUE GROUP2, \
976 MEDIAN.MESHPOINT.JPOSITION, \
977 MEDIAN.MESHPOINT.IPOSITION, \
978 MEDIAN.MESHPOINT.KPOSITION \
979 from MEDIAN.MESHLAYER ml, \
980 MEDIAN.MESHPOINT, \
981 MEDIAN.MESH m, \
982 MEDIAN.MESHSCALARVALUE msv \
983 where msv.FEATUREID = MEDIAN.MESHPOINT. FEATUREID AND \
984 ml.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \
985 ml.MESHID = MEDIAN.MESHPOINT.MESHID and \
986 m.MESHID = MEDIAN.MESHPOINT.MESHID AND \
987 m.PARTIDMIN <= msv.PARTID AND \
988 m.PARTIDMAX >= msv.PARTID AND \
989 msv.PARAMETERID = ? AND \
990 msv.TIMEVALUE = ? AND \
991 m.OBJECTID = ? AND \
992 MEDIAN.MESHPOINT.FEATUREID in \
993 ( select FEATUREID \
994 from MEDIAN.MESHPOINT mp, \
995 MEDIAN.MESH m \
996 where m.OBJECTID = ? AND \
997 mp.MESHID = m.MESHID AND \
998 ? = (select ? from MEDIAN.MESHPOINT where FEATUREID = ?)) \
999 order by msv.TIMEVALUE, \
1000 msv.PARAMETERID, \
1001 MEDIAN.MESHPOINT.JPOSITION, \
1002 MEDIAN.MESHPOINT.IPOSITION, \
1003 MEDIAN.MESHPOINT.KPOSITION
1004
1005 verticalcrosssection_mesh_odv_data = SELECT SI.NAME CRUISE, \
1006 M.MESHID || '-' || MEDIAN.MESHPOINT.IPOSITION || '-' || MEDIAN.MESHPOINT.JPOSITION STATION, \
1007 '*' TYPE, \
1008 ST_ASTEXT(SHAPE), \
1009 0 BOTDEPTH, \
1010 (((ML.UPPERZLOCATION + ML.LOWERZLOCATION) / 2)*-1) DEPTH, \
1011 MSV.TIMEVALUE, \
1012 MSV.DATAVALUE, \
1013 MSV.PARAMETERID PARAMETER, \
1014 MEDIAN.MESHPOINT.JPOSITION, \
1015 MEDIAN.MESHPOINT.IPOSITION, \
1016 MEDIAN.MESHPOINT.KPOSITION, \
1017 2 DATAID \
1018 from MEDIAN.MESHLAYER ml, \
1019 MEDIAN.MESHPOINT, \
1020 MEDIAN.MESH m, \
1021 MEDIAN.SOURCEINFO SI, \
1022 MEDIAN.MESHSCALARVALUE msv \
1023 where msv.FEATUREID = MEDIAN.MESHPOINT. FEATUREID AND \
1024 ml.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \
1025 ml.MESHID = MEDIAN.MESHPOINT.MESHID and \
1026 m.MESHID = MEDIAN.MESHPOINT.MESHID AND \
1027 m.PARTIDMIN <= msv.PARTID AND \
1028 m.PARTIDMAX >= msv.PARTID AND \
1029 M.SOURCEID = SI.SOURCEID AND \
1030 msv.PARAMETERID = ? AND \
1031 msv.TIMEVALUE = ? AND \
1032 m.OBJECTID = ? AND \
1033 MEDIAN.MESHPOINT.FEATUREID in \
1034 ( select FEATUREID \
1035 from MEDIAN.MESHPOINT mp, \
1036 MEDIAN.MESH m \
1037 where m.OBJECTID = ? AND \
1038 mp.MESHID = m.MESHID AND \
1039 ? = (select ? from MEDIAN.MESHPOINT where FEATUREID = ?)) \
1040 order by msv.TIMEVALUE, \
1041 msv.PARAMETERID, \
1042 MEDIAN.MESHPOINT.JPOSITION, \
1043 MEDIAN.MESHPOINT.IPOSITION, \
1044 MEDIAN.MESHPOINT.KPOSITION
1045
1046 #############################################
1047 #############################################
1048 ########## Horizontalschnitt MESH ###########
1049 #############################################
1050 #############################################
1051 horizontalcrosssection_meshpoint_depth = SELECT DISTINCT MP.KPOSITION KEY, \
1052 'Layer ' || ML.KPOSITION || ': ' || -ML.UPPERZLOCATION || ' - '|| -ML.LOWERZLOCATION VALUE \
1053 from MEDIAN.MESHLAYER ML, \
1054 MEDIAN.MESHPOINT MP, \
1055 MEDIAN.MESH M \
1056 WHERE ML.KPOSITION = MP.KPOSITION AND \
1057 ML.MESHID = MP.MESHID AND \
1058 M.OBJECTID = ? AND \
1059 MP.MESHID = M.MESHID \
1060 ORDER BY MP.KPOSITION
1061
1062 horizontalcrosssection_mesh_date = select distinct \
1063 msv.TIMEVALUE KEY, \
1064 msv.TIMEVALUE VALUE \
1065 from MEDIAN.MESHSCALARVALUE msv, \
1066 MEDIAN.MESH m \
1067 where m.OBJECTID = ? AND \
1068 msv.PARTID >= m.PARTIDMIN AND \
1069 msv.PARTID <= m.PARTIDMAX AND \
1070 msv.PARAMETERID = ? \
1071 order by msv.TIMEVALUE
1072
1073 horizontalcrosssection_mesh_data = SELECT ST_ASTEXT(SHAPE), \
1074 MSV.DATAVALUE YORDINATE, \
1075 MEDIAN.MESHFACE.JPOSITION, \
1076 MEDIAN.MESHFACE.IPOSITION, \
1077 MEDIAN.MESHFACE.KPOSITION, \
1078 2 DATAID \
1079 from MEDIAN.MESHLAYER ML, \
1080 MEDIAN.MESHFACE, \
1081 MEDIAN.MESH M, \
1082 MEDIAN.MESHSCALARVALUE MSV \
1083 where MSV.FEATUREID = MEDIAN.MESHFACE.FEATUREID AND \
1084 ML.KPOSITION = MEDIAN.MESHFACE.KPOSITION and \
1085 ML.MESHID = MEDIAN.MESHFACE.MESHID and \
1086 M.MESHID = MEDIAN.MESHFACE.MESHID AND \
1087 M.PARTIDMIN <= MSV.PARTID AND \
1088 M.PARTIDMAX >= MSV.PARTID AND \
1089 MSV.PARAMETERID = ? AND \
1090 MSV.TIMEVALUE = ? AND \
1091 M.OBJECTID = ? AND \
1092 MEDIAN.MESHFACE.KPOSITION = ? \
1093 order by MEDIAN.MESHFACE.JPOSITION, \
1094 MEDIAN.MESHFACE.IPOSITION
1095 horizontalcrosssection_mesh_odv_data = SELECT SI.NAME CRUISE, \
1096 M.MESHID || '-' || MEDIAN.MESHPOINT.IPOSITION || '-' || MEDIAN.MESHPOINT.JPOSITION STATION, \
1097 '*' TYPE, \
1098 ST_ASTEXT(SHAPE), \
1099 0 BOTDEPTH, \
1100 (((ML.UPPERZLOCATION + ML.LOWERZLOCATION) / 2)*-1) DEPTH, \
1101 MSV.TIMEVALUE, \
1102 MSV.DATAVALUE, \
1103 MSV.PARAMETERID PARAMETER, \
1104 MEDIAN.MESHPOINT.JPOSITION, \
1105 MEDIAN.MESHPOINT.IPOSITION, \
1106 MEDIAN.MESHPOINT.KPOSITION \
1107 from MEDIAN.MESHLAYER ML, \
1108 MEDIAN.MESHPOINT, \
1109 MEDIAN.MESH M, \
1110 MEDIAN.SOURCEINFO SI, \
1111 MEDIAN.MESHSCALARVALUE MSV \
1112 where MSV.FEATUREID = MEDIAN.MESHPOINT.FEATUREID AND \
1113 ML.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \
1114 ML.MESHID = MEDIAN.MESHPOINT.MESHID and \
1115 M.MESHID = MEDIAN.MESHPOINT.MESHID AND \
1116 M.PARTIDMIN <= MSV.PARTID AND \
1117 M.PARTIDMAX >= MSV.PARTID AND \
1118 M.SOURCEID = SI.SOURCEID AND \
1119 MSV.PARAMETERID = ? AND \
1120 MSV.TIMEVALUE = ? AND \
1121 M.OBJECTID = ? AND \
1122 MEDIAN.MESHPOINT.KPOSITION = ? \
1123 order by MEDIAN.MESHPOINT.JPOSITION, \
1124 MEDIAN.MESHPOINT.IPOSITION
1125
1126 area_filter = SELECT DISTINCT \
1127 FEATUREID KEY ,\
1128 DESCRIPTION VALUE \
1129 FROM MEDIAN.FEATUREAREA \
1130 WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \
1131 FEATUREID > 0 \
1132 ORDER BY FEATUREID
1133
1134 subarea_filter = SELECT \
1135 FEATURECODE KEY, \
1136 NAME VALUE \
1137 FROM MEDIAN.FEATUREAREA \
1138 WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \
1139 FEATUREID = ? \
1140 ORDER BY NAME
http://dive4elements.wald.intevation.org