Mercurial > dive4elements > gnv-client

comparison gnv-artifacts/doc/conf/queries.properties @ 1119:7c4f81f74c47

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
merged gnv-artifacts
author Thomas Arendsen Hein <thomas@intevation.de>
date Fri, 28 Sep 2012 12:14:00 +0200
parents 19813e72ec06
children
comparison
equal deleted inserted replaced
1027:fca4b5eb8d2f 1119:7c4f81f74c47
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_timeseriespoint_with_wkt=SELECT \
32 MEDIAN.TIMESERIESPOINT.FEATUREID KEY, \
33 MEDIAN.TIMESERIESPOINT.NAME VALUE \
34 FROM MEDIAN.TIMESERIESPOINT, \
35 MEDIAN.MEASUREMENT MMT \
36 WHERE MEDIAN.TIMESERIESPOINT.FEATUREID = MMT.FEATUREID AND \
37 MMT.SOURCEID = ? AND \
38 INTERSECTS(SHAPE,"?")\
39 ORDER BY MEDIAN.TIMESERIESPOINT.NAME
40
41 timeseries_parameter=SELECT DISTINCT \
42 p.PARAMETERID KEY, \
43 p.GERMANNAME || ' ['|| p.UNIT ||']' VALUE, \
44 p.GERMANNAME \
45 FROM MEDIAN.PARAMETER P, \
46 MEDIAN.TIMESERIES TS, \
47 MEDIAN.TIMESERIESVALUE TSV, \
48 MEDIAN.MEASUREMENT M, \
49 MEDIAN.TIMESERIESPOINT TSP \
50 WHERE M.FEATUREID = TSP.FEATUREID AND \
51 M.MEASUREMENTID = TSV.MEASUREMENTID AND \
52 TS.TIMESERIESID = TSV.TIMESERIESID AND \
53 P.PARAMETERID = TS.PARAMETERID AND \
54 TSP.FEATUREID = ? \
55 ORDER BY P.GERMANNAME
56
57 timeseries_depth_height=SELECT DISTINCT \
58 M.MEASUREMENTID KEY, \
59 M.ZLOCATION VALUE, \
60 P.PARAMETERID PARAMETERID \
61 FROM MEDIAN.MEASUREMENT M, \
62 MEDIAN.TIMESERIESVALUE TSV, \
63 MEDIAN.TIMESERIES T, \
64 MEDIAN.PARAMETER P \
65 WHERE M.MEASUREMENTID = TSV.MEASUREMENTID AND \
66 TSV.TIMESERIESID = T.TIMESERIESID AND \
67 T.PARAMETERID = P.PARAMETERID AND \
68 M.FEATUREID = ? AND \
69 P.PARAMETERID IN (?)\
70 ORDER BY m.ZLOCATION DESC
71
72 timeseries_interval=select min(tv.TIMEVALUE) MIN, \
73 max(tv.TIMEVALUE) MAX \
74 from MEDIAN.TIMESERIES t, \
75 MEDIAN.TIMESERIESVALUE tv \
76 where tv.TIMESERIESID = t.TIMESERIESID AND \
77 t.PARAMETERID IN ( ? ) AND \
78 tv.MEASUREMENTID IN ( ? )
79
80 timeseries_chart_data=SELECT tv.TIMEVALUE XORDINATE, \
81 tv.DATAVALUE YORDINATE, \
82 t.PARAMETERID GROUP1, \
83 tv.MEASUREMENTID GROUP2, \
84 tv.TIMESERIESID GROUP3, \
85 t.TIMEINTERVAL GAPID \
86 FROM MEDIAN.TIMESERIESVALUE tv, \
87 MEDIAN.TIMESERIES t \
88 WHERE tv.TIMESERIESID = t.TIMESERIESID AND \
89 t.PARAMETERID IN ( ? ) AND \
90 tv.MEASUREMENTID IN ( ? ) AND \
91 tv.TIMEVALUE >= ? AND \
92 tv.TIMEVALUE <= ? \
93 ORDER BY tv.MEASUREMENTID, \
94 tv.TIMESERIESID, \
95 t.PARAMETERID, \
96 tv.TIMEVALUE
97
98 timeseries_odv_data = SELECT SI.NAME CRUISE, \
99 MEDIAN.TIMESERIESPOINT.NAME STATION, \
100 '*' TYPE, \
101 ST_ASTEXT(SHAPE), \
102 0 BOTDEPTH, \
103 (M.ZLOCATION *-1) DEPTH, \
104 TSV.TIMEVALUE, \
105 TSV.DATAVALUE, \
106 TS.PARAMETERID PARAMETER, \
107 TSV.MEASUREMENTID, \
108 TSV.TIMESERIESID, \
109 '1' QF \
110 FROM MEDIAN.TIMESERIESPOINT, \
111 MEDIAN.TIMESERIESVALUE TSV, \
112 MEDIAN.TIMESERIES TS, \
113 MEDIAN.MEASUREMENT M, \
114 MEDIAN.SOURCEINFO SI \
115 WHERE SI.SOURCEID = M.SOURCEID AND \
116 M.FEATUREID = MEDIAN.TIMESERIESPOINT.FEATUREID AND \
117 M.MEASUREMENTID = TSV.MEASUREMENTID AND \
118 TSV.TIMESERIESID = TS.TIMESERIESID AND \
119 TS.PARAMETERID IN ( ? ) AND \
120 TSV.MEASUREMENTID IN ( ? ) AND \
121 TSV.TIMEVALUE >= ? AND \
122 TSV.TIMEVALUE <= ? \
123 ORDER BY TS.PARAMETERID, \
124 TSV.MEASUREMENTID, \
125 TSV.TIMESERIESID, \
126 TSV.TIMEVALUE
127
128 #############################################
129 #############################################
130 ########## Zeitserie ##############
131 ########## Vektordaten ###############
132 #############################################
133 #############################################
134
135 timeseries_parameter_vector=SELECT DISTINCT \
136 p.PARAMETERID KEY, \
137 p.GERMANNAME || ' ['|| p.UNIT ||']' VALUE, \
138 p.GERMANNAME \
139 FROM MEDIAN.PARAMETER P, \
140 MEDIAN.TIMESERIES TS, \
141 MEDIAN.TIMESERIESVECTORVALUE TSV, \
142 MEDIAN.MEASUREMENT M, \
143 MEDIAN.TIMESERIESPOINT TSP \
144 WHERE M.FEATUREID = TSP.FEATUREID AND \
145 M.MEASUREMENTID = TSV.MEASUREMENTID AND \
146 TS.TIMESERIESID = TSV.TIMESERIESID AND \
147 P.PARAMETERID = TS.PARAMETERID AND \
148 TSP.FEATUREID = ? \
149 ORDER BY P.GERMANNAME
150
151 timeseries_depth_height_vector=SELECT DISTINCT \
152 M.MEASUREMENTID KEY, \
153 M.ZLOCATION VALUE, \
154 P.PARAMETERID PARAMETERID \
155 FROM MEDIAN.MEASUREMENT M, \
156 MEDIAN.TIMESERIESVECTORVALUE TSV, \
157 MEDIAN.TIMESERIES T, \
158 MEDIAN.PARAMETER P \
159 WHERE M.MEASUREMENTID = TSV.MEASUREMENTID AND \
160 TSV.TIMESERIESID = T.TIMESERIESID AND \
161 T.PARAMETERID = P.PARAMETERID AND \
162 M.FEATUREID = ? AND \
163 P.PARAMETERID = ? \
164 ORDER BY m.ZLOCATION DESC
165
166 timeseries_interval_vector=select min(tsv.TIMEVALUE) MIN, \
167 max(tsv.TIMEVALUE) MAX \
168 from MEDIAN.TIMESERIES t, \
169 MEDIAN.TIMESERIESVECTORVALUE tsv \
170 where tsv.TIMESERIESID = t.TIMESERIESID AND \
171 t.PARAMETERID = ? AND \
172 tsv.MEASUREMENTID = ?
173
174 timeseries_chart_data_vector=SELECT tsv.TIMEVALUE XORDINATE, \
175 tsv.XCOMPONENT, \
176 tsv.YCOMPONENT, \
177 tsv.ZCOMPONENT, \
178 tsv.SPEED, \
179 tsv.DIRECTION, \
180 t.PARAMETERID GROUP1, \
181 tsv.MEASUREMENTID GROUP2, \
182 tsv.TIMESERIESID GROUP3, \
183 t.TIMEINTERVAL GAPID \
184 FROM MEDIAN.TIMESERIESFULLVECTORVALUE tsv, \
185 MEDIAN.TIMESERIES t \
186 WHERE tsv.TIMESERIESID = t.TIMESERIESID AND \
187 t.PARAMETERID = ? AND \
188 tsv.MEASUREMENTID = ? AND \
189 tsv.TIMEVALUE >= ? AND \
190 tsv.TIMEVALUE <= ? \
191 ORDER BY tsv.MEASUREMENTID, \
192 tsv.TIMESERIESID, \
193 t.PARAMETERID, \
194 tsv.TIMEVALUE
195
196 timeseries_odv_data_vector = SELECT SI.NAME CRUISE, \
197 MEDIAN.TIMESERIESPOINT.NAME STATION, \
198 '*' TYPE, \
199 ST_ASTEXT(SHAPE), \
200 0 BOTDEPTH, \
201 (M.ZLOCATION *-1) DEPTH, \
202 TSV.TIMEVALUE, \
203 TSV.XCOMPONENT, \
204 TSV.YCOMPONENT, \
205 TSV.ZCOMPONENT, \
206 TSV.SPEED, \
207 TSV.DIRECTION, \
208 TS.PARAMETERID PARAMETER, \
209 TSV.MEASUREMENTID, \
210 TSV.TIMESERIESID, \
211 '1' QF \
212 FROM MEDIAN.TIMESERIESPOINT, \
213 MEDIAN.TIMESERIESFULLVECTORVALUE TSV, \
214 MEDIAN.TIMESERIES TS, \
215 MEDIAN.MEASUREMENT M, \
216 MEDIAN.SOURCEINFO SI \
217 WHERE SI.SOURCEID = M.SOURCEID AND \
218 M.FEATUREID = MEDIAN.TIMESERIESPOINT.FEATUREID AND \
219 M.MEASUREMENTID = TSV.MEASUREMENTID AND \
220 TSV.TIMESERIESID = TS.TIMESERIESID AND \
221 TS.PARAMETERID = ? AND \
222 TSV.MEASUREMENTID = ? AND \
223 TSV.TIMEVALUE >= ? AND \
224 TSV.TIMEVALUE <= ? \
225 ORDER BY TS.PARAMETERID, \
226 TSV.MEASUREMENTID, \
227 TSV.TIMESERIESID, \
228 TSV.TIMEVALUE
229
230 ############################################
231 #############################################
232 ########## Zeitserie Mesh ##############
233 #############################################
234 #############################################
235
236 timeseries_mesh = SELECT OBJECTID KEY, \
237 m.NAME VALUE \
238 FROM MEDIAN.MESH m \
239 WHERE SOURCEID IN (?) \
240 ORDER BY m.NAME
241 timeseries_meshpoint = SELECT MEDIAN.MESHPOINT.FEATUREID, \
242 ST_ASTEXT(SHAPE) \
243 FROM MEDIAN.MESHPOINT, \
244 MEDIAN.MESH M \
245 WHERE MEDIAN.MESHPOINT.MESHID = M.MESHID AND \
246 KPOSITION = 1 AND \
247 M.OBJECTID = ? AND \
248 INTERSECTS(SHAPE,"?")
249
250 timeseries_meshpoint_depth = SELECT MP.FEATUREID KEY, \
251 'Layer ' || ML.KPOSITION || ': ' || -ML.UPPERZLOCATION || ' - '|| -ML.LOWERZLOCATION VALUE \
252 from MEDIAN.MESHLAYER ML, \
253 MEDIAN.MESHPOINT MP, \
254 MEDIAN.MESH M \
255 WHERE ML.KPOSITION = MP.KPOSITION AND \
256 ML.MESHID = MP.MESHID AND \
257 M.OBJECTID = ? AND \
258 MP.MESHID = M.MESHID AND \
259 IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) AND \
260 JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) \
261 ORDER BY ML.UPPERZLOCATION DESC
262
263 timeseries_mesh_parameter=SELECT distinct \
264 p.PARAMETERID KEY, \
265 p.GERMANNAME || ' ['|| p.UNIT ||']' VALUE, \
266 p.GERMANNAME \
267 from MEDIAN.PARAMETER p, \
268 MEDIAN.MESHSCALARVALUE msc, \
269 MEDIAN.MESH m \
270 where m.OBJECTID = ? AND \
271 msc.PARTID >= m.PARTIDMIN AND \
272 msc.PARTID <= m.PARTIDMAX AND \
273 msc.PARAMETERID = p.PARAMETERID \
274 order by p.GERMANNAME
275 timeseries_mesh_interval=select /*+ parallel(TIMEVALUE,5) */ \
276 min(TIMEVALUE) MIN, \
277 max(TIMEVALUE) MAX \
278 from MEDIAN.MESHSCALARVALUE msc, \
279 MEDIAN.MESH m \
280 where m.OBJECTID = ? AND \
281 msc.PARTID >= m.PARTIDMIN AND \
282 msc.PARTID <= m.PARTIDMAX AND \
283 msc.PARAMETERID IN (?)
284
285 timeseries_mesh_chart_data=select /*+ parallel(timevalue,10) */ \
286 msv.TIMEVALUE XORDINATE, \
287 msv.DATAVALUE YORDINATE, \
288 msv.PARAMETERID GROUP1, \
289 msv.FEATUREID GROUP2, \
290 mp.FEATUREID GROUP3, \
291 -1 GAPID \
292 from MEDIAN.MESHSCALARVALUE msv, \
293 MEDIAN.MESHPOINT mp, \
294 MEDIAN.MESH m \
295 where (m.OBJECTID = ? AND \
296 msv.PARTID >= m.PARTIDMIN AND \
297 msv.PARTID <= m.PARTIDMAX ) AND \
298 msv.FEATUREID = mp.FEATUREID AND \
299 msv.PARAMETERID in ( ? ) AND \
300 mp.FEATUREID in ( ? ) AND \
301 msv.TIMEVALUE >= ? AND \
302 msv.TIMEVALUE <= ? \
303 order by mp.FEATUREID, \
304 msv.FEATUREID, \
305 msv.PARAMETERID, \
306 msv.TIMEVALUE
307
308 timeseries_mesh_odv_data = select /*+ parallel(timevalue,10) */ \
309 SI.NAME CRUISE, \
310 m.MESHID || '-' || MEDIAN.MESHPOINT.IPOSITION || '-' || MEDIAN.MESHPOINT.JPOSITION STATION, \
311 '*' TYPE, \
312 ST_ASTEXT(SHAPE), \
313 0 BOTDEPTH, \
314 (((ML.UPPERZLOCATION + ML.LOWERZLOCATION) / 2)*-1) DEPTH, \
315 msv.TIMEVALUE, \
316 msv.DATAVALUE, \
317 msv.PARAMETERID PARAMETER, \
318 '1' QF \
319 from MEDIAN.MESHSCALARVALUE msv, \
320 MEDIAN.MESHPOINT, \
321 MEDIAN.MESH m, \
322 MEDIAN.SOURCEINFO SI, \
323 MEDIAN.MESHLAYER ML \
324 where (m.OBJECTID = ? AND \
325 msv.PARTID >= m.PARTIDMIN AND \
326 msv.PARTID <= m.PARTIDMAX ) AND \
327 m.SOURCEID = SI.SOURCEID AND \
328 ML.MESHID = MEDIAN.MESHPOINT.MESHID AND \
329 ML.KPOSITION = MEDIAN.MESHPOINT.KPOSITION AND \
330 msv.FEATUREID = MEDIAN.MESHPOINT.FEATUREID AND \
331 msv.PARAMETERID in (? ) AND \
332 MEDIAN.MESHPOINT.FEATUREID in ( ? ) AND \
333 msv.TIMEVALUE >= ? AND \
334 msv.TIMEVALUE <= ? \
335 order by MEDIAN.MESHPOINT.FEATUREID, \
336 msv.FEATUREID, \
337 msv.TIMEVALUE, \
338 msv.PARAMETERID
339
340 #############################################
341 #############################################
342 ########## Zeitserie Mesh ##############
343 ########## Vektordaten ##############
344 #############################################
345 #############################################
346
347 timeseries_mesh_parameter_vector=SELECT DISTINCT \
348 P.PARAMETERID KEY, \
349 P.GERMANNAME || ' ['|| P.UNIT ||']' VALUE, \
350 P.GERMANNAME \
351 FROM MEDIAN.PARAMETER P, \
352 MEDIAN.MESHVECTORVALUE MVV, \
353 MEDIAN.MESH M \
354 WHERE M.OBJECTID = ? AND \
355 MVV.PARTID >= M.PARTIDMIN AND \
356 MVV.PARTID <= M.PARTIDMAX AND \
357 MVV.PARAMETERID = P.PARAMETERID \
358 ORDER BY P.GERMANNAME
359
360 timeseries_mesh_interval_vector=SELECT /*+ parallel(TIMEVALUE,5) */ \
361 MIN(TIMEVALUE) MIN, \
362 MAX(TIMEVALUE) MAX \
363 FROM MEDIAN.MESHVECTORVALUE MVV, \
364 MEDIAN.MESH M \
365 WHERE M.OBJECTID = ? AND \
366 MVV.PARTID >= M.PARTIDMIN AND \
367 MVV.PARTID <= M.PARTIDMAX AND \
368 MVV.PARAMETERID IN (?)
369
370 timeseries_mesh_chart_data_vector =SELECT /*+ parallel(TIMEVALUE,10) */ \
371 MVV.TIMEVALUE XORDINATE, \
372 MVV.XCOMPONENT, \
373 MVV.YCOMPONENT, \
374 MVV.ZCOMPONENT, \
375 MVV.SPEED, \
376 MVV.DIRECTION, \
377 MVV.PARAMETERID GROUP1, \
378 MVV.FEATUREID GROUP2, \
379 MP.FEATUREID GROUP3, \
380 -1 GAPID \
381 FROM MEDIAN.MESHCURRENTVALUE MVV, \
382 MEDIAN.MESHPOINT MP, \
383 MEDIAN.MESH M \
384 WHERE (M.OBJECTID = ? AND \
385 MVV.PARTID >= M.PARTIDMIN AND \
386 MVV.PARTID <= M.PARTIDMAX ) AND \
387 MVV.FEATUREID = MP.FEATUREID AND \
388 MVV.PARAMETERID in ( ? ) AND \
389 MP.FEATUREID in ( ? ) AND \
390 MVV.TIMEVALUE >= ? AND \
391 MVV.TIMEVALUE <= ? \
392 ORDER BY MP.FEATUREID, \
393 MVV.FEATUREID, \
394 MVV.PARAMETERID, \
395 MVV.TIMEVALUE
396
397 timeseries_mesh_odv_data_vector = SELECT /*+ parallel(timevalue,10) */ \
398 SI.NAME CRUISE, \
399 M.MESHID || '-' || MEDIAN.MESHPOINT.IPOSITION || '-' || MEDIAN.MESHPOINT.JPOSITION STATION, \
400 '*' TYPE, \
401 ST_ASTEXT(SHAPE), \
402 0 BOTDEPTH, \
403 (((ML.UPPERZLOCATION + ML.LOWERZLOCATION) / 2)*-1) DEPTH, \
404 MVV.TIMEVALUE, \
405 MVV.XCOMPONENT, \
406 MVV.YCOMPONENT, \
407 MVV.ZCOMPONENT, \
408 MVV.SPEED, \
409 MVV.DIRECTION, \
410 MVV.PARAMETERID PARAMETER, \
411 '1' QF \
412 FROM MEDIAN.MESHCURRENTVALUE MVV, \
413 MEDIAN.MESHPOINT, \
414 MEDIAN.MESH M, \
415 MEDIAN.SOURCEINFO SI, \
416 MEDIAN.MESHLAYER ML \
417 WHERE (M.OBJECTID = ? AND \
418 MVV.PARTID >= M.PARTIDMIN AND \
419 MVV.PARTID <= M.PARTIDMAX ) AND \
420 M.SOURCEID = SI.SOURCEID AND \
421 ML.MESHID = MEDIAN.MESHPOINT.MESHID AND \
422 ML.KPOSITION = MEDIAN.MESHPOINT.KPOSITION AND \
423 MVV.FEATUREID = MEDIAN.MESHPOINT.FEATUREID AND \
424 MVV.PARAMETERID in (? ) AND \
425 MEDIAN.MESHPOINT.FEATUREID in ( ? ) AND \
426 MVV.TIMEVALUE >= ? AND \
427 MVV.TIMEVALUE <= ? \
428 ORDER BY MEDIAN.MESHPOINT.FEATUREID, \
429 MVV.FEATUREID, \
430 MVV.TIMEVALUE, \
431 MVV.PARAMETERID
432
433 #############################################
434 #############################################
435 ########## Vertikalprofil ##############
436 #############################################
437 #############################################
438
439 verticalprofile_point=SELECT DISTINCT \
440 tsp.FEATUREID KEY, \
441 tsp.NAME VALUE \
442 FROM MEDIAN.TIMESERIESPOINT tsp, \
443 MEDIAN.MEASUREMENT mmt \
444 WHERE tsp.FEATUREID = mmt.FEATUREID AND \
445 mmt.SOURCEID = ? \
446 order by tsp.name
447
448 verticalprofile_parameter=SELECT DISTINCT \
449 p.PARAMETERID KEY, \
450 p.GERMANNAME || ' ['|| p.UNIT ||']' VALUE, \
451 p.GERMANNAME \
452 from MEDIAN.PARAMETER p, \
453 MEDIAN.TIMESERIES ts, \
454 MEDIAN.TIMESERIESVALUE tsv, \
455 MEDIAN.MEASUREMENT m \
456 where ts.PARAMETERID = p.PARAMETERID and \
457 ts.TIMESERIESID = tsv.TIMESERIESID and \
458 m.MEASUREMENTID = tsv.MEASUREMENTID and \
459 m.FEATUREID = ? \
460 ORDER BY p.GERMANNAME
461
462 verticalprofile_minmaxdepth= SELECT min(M.ZLOCATION) MIN, \
463 max(M.ZLOCATION) MAX \
464 FROM MEDIAN.MEASUREMENT M, \
465 MEDIAN.TIMESERIES TS, \
466 MEDIAN.TIMESERIESVALUE TSV \
467 WHERE TS.TIMESERIESID = TSV.TIMESERIESID and \
468 M.MEASUREMENTID = TSV.MEASUREMENTID and \
469 M.FEATUREID = ? and \
470 TS.PARAMETERID IN ( ? )
471
472
473 verticalprofile_year=select distinct \
474 to_char(tsv.TIMEVALUE,'YYYY') KEY, \
475 to_char(tsv.TIMEVALUE,'YYYY') VALUE \
476 from MEDIAN.TIMESERIES ts, \
477 MEDIAN.TIMESERIESVALUE tsv, \
478 MEDIAN.MEASUREMENT m \
479 where ts.TIMESERIESID = tsv.TIMESERIESID and \
480 m.MEASUREMENTID = tsv.MEASUREMENTID and \
481 m.FEATUREID = ? and \
482 ts.PARAMETERID IN ( ? ) \
483 order by to_char(tsv.TIMEVALUE,'YYYY')
484
485 verticalprofile_date=select distinct \
486 tsv.TIMEVALUE KEY, \
487 tsv.TIMEVALUE VALUE \
488 from MEDIAN.TIMESERIES ts, \
489 MEDIAN.TIMESERIESVALUE tsv, \
490 MEDIAN.MEASUREMENT m \
491 where ts.TIMESERIESID = tsv.TIMESERIESID and \
492 m.MEASUREMENTID = tsv.MEASUREMENTID and \
493 m.FEATUREID = ? and \
494 ts.PARAMETERID IN ( ? ) and \
495 to_char(tsv.TIMEVALUE,'YYYY') IN (?) \
496 order by tsv.TIMEVALUE
497
498 verticalprofile_chart_data= SELECT M.ZLOCATION XORDINATE, \
499 TSV.DATAVALUE YORDINATE, \
500 TS.PARAMETERID GROUP1, \
501 TSV.TIMEVALUE GROUP2, \
502 1 GROUP3, \
503 1 DATAID, \
504 M.FEATUREID, \
505 TS.TIMESERIESID \
506 from MEDIAN.TIMESERIES TS, \
507 MEDIAN.TIMESERIESVALUE TSV, \
508 MEDIAN.MEASUREMENT M \
509 where TS.TIMESERIESID = TSV.TIMESERIESID AND \
510 M.MEASUREMENTID = TSV.MEASUREMENTID AND \
511 M.FEATUREID = ? AND \
512 TS.PARAMETERID IN ( ? ) AND \
513 TSV.TIMEVALUE IN (?) AND \
514 M.ZLOCATION >= ? AND \
515 M.ZLOCATION <= ? \
516 ORDER BY TSV.TIMEVALUE, \
517 TSV.TIMESERIESID, \
518 TS.PARAMETERID, \
519 TSV.TIMEVALUE
520
521 verticalprofile_odv_data = SELECT SI.NAME CRUISE, \
522 MEDIAN.TIMESERIESPOINT.NAME STATION, \
523 '*' TYPE, \
524 ST_ASTEXT(SHAPE), \
525 0 BOTDEPTH, \
526 (M.ZLOCATION) DEPTH, \
527 TSV.TIMEVALUE, \
528 TSV.DATAVALUE, \
529 TS.PARAMETERID PARAMETER, \
530 TSV.MEASUREMENTID, \
531 TSV.TIMESERIESID, \
532 '1' QF \
533 FROM MEDIAN.TIMESERIESPOINT, \
534 MEDIAN.TIMESERIESVALUE TSV, \
535 MEDIAN.TIMESERIES TS, \
536 MEDIAN.MEASUREMENT M, \
537 MEDIAN.SOURCEINFO SI \
538 WHERE SI.SOURCEID = M.SOURCEID AND \
539 M.FEATUREID = MEDIAN.TIMESERIESPOINT.FEATUREID AND \
540 M.MEASUREMENTID = TSV.MEASUREMENTID AND \
541 TSV.TIMESERIESID = TS.TIMESERIESID AND \
542 M.FEATUREID = ? AND \
543 TS.PARAMETERID IN ( ? ) AND \
544 TSV.TIMEVALUE IN (?) AND \
545 M.ZLOCATION >= ? AND \
546 M.ZLOCATION <= ? \
547 ORDER BY TSV.MEASUREMENTID, \
548 TSV.TIMESERIESID, \
549 TSV.TIMEVALUE, \
550 TS.PARAMETERID
551
552 #############################################
553 #############################################
554 ########## Vertikalprofil ##############
555 ########## Vektordaten ##############
556 #############################################
557 #############################################
558
559 verticalprofile_parameter_vector=SELECT DISTINCT \
560 P.PARAMETERID KEY, \
561 P.GERMANNAME || ' ['|| P.UNIT ||']' VALUE, \
562 P.GERMANNAME \
563 from MEDIAN.PARAMETER P, \
564 MEDIAN.TIMESERIES TS, \
565 MEDIAN.TIMESERIESVECTORVALUE TVV, \
566 MEDIAN.MEASUREMENT M \
567 where TS.PARAMETERID = P.PARAMETERID AND \
568 TS.TIMESERIESID = TVV.TIMESERIESID AND \
569 M.MEASUREMENTID = TVV.MEASUREMENTID AND \
570 M.FEATUREID = ? \
571 ORDER BY P.GERMANNAME
572
573 verticalprofile_minmaxdepth_vector= SELECT MIN(M.ZLOCATION) MIN, \
574 MAX(M.ZLOCATION) MAX \
575 FROM MEDIAN.MEASUREMENT M, \
576 MEDIAN.TIMESERIES TS, \
577 MEDIAN.TIMESERIESVECTORVALUE TVV \
578 WHERE TS.TIMESERIESID = TVV.TIMESERIESID AND \
579 M.MEASUREMENTID = TVV.MEASUREMENTID AND \
580 M.FEATUREID = ? AND \
581 TS.PARAMETERID = ?
582
583 verticalprofile_year_vector=SELECT DISTINCT \
584 TO_CHAR(TVV.TIMEVALUE,'YYYY') KEY, \
585 TO_CHAR(TVV.TIMEVALUE,'YYYY') VALUE \
586 FROM MEDIAN.TIMESERIES TS, \
587 MEDIAN.TIMESERIESVECTORVALUE TVV, \
588 MEDIAN.MEASUREMENT M \
589 WHERE TS.TIMESERIESID = TVV.TIMESERIESID AND \
590 M.MEASUREMENTID = TVV.MEASUREMENTID AND \
591 M.FEATUREID = ? AND \
592 TS.PARAMETERID = ? \
593 ORDER BY to_char(TVV.TIMEVALUE,'YYYY')
594
595 verticalprofile_date_vector=SELECT DISTINCT \
596 TVV.TIMEVALUE KEY, \
597 TVV.TIMEVALUE VALUE \
598 from MEDIAN.TIMESERIES TS, \
599 MEDIAN.TIMESERIESVECTORVALUE TVV, \
600 MEDIAN.MEASUREMENT M \
601 WHERE TS.TIMESERIESID = TVV.TIMESERIESID AND \
602 M.MEASUREMENTID = TVV.MEASUREMENTID AND \
603 M.FEATUREID = ? AND \
604 TS.PARAMETERID = ? AND \
605 TO_CHAR(TVV.TIMEVALUE,'YYYY') IN (?) \
606 ORDER BY TVV.TIMEVALUE
607
608 verticalprofile_chart_data_vector= SELECT M.ZLOCATION XORDINATE, \
609 TS.PARAMETERID GROUP1, \
610 TVV.TIMEVALUE GROUP2, \
611 TVV.XCOMPONENT, \
612 TVV.YCOMPONENT, \
613 TVV.ZCOMPONENT, \
614 TVV.SPEED, \
615 TVV.DIRECTION, \
616 1 GROUP3, \
617 1 DATAID, \
618 M.FEATUREID, \
619 TS.TIMESERIESID \
620 FROM MEDIAN.TIMESERIES TS, \
621 MEDIAN.TIMESERIESFULLVECTORVALUE TVV, \
622 MEDIAN.MEASUREMENT M \
623 WHERE TS.TIMESERIESID = TVV.TIMESERIESID AND \
624 M.MEASUREMENTID = TVV.MEASUREMENTID AND \
625 M.FEATUREID = ? AND \
626 TS.PARAMETERID = ? AND \
627 TVV.TIMEVALUE = ? AND \
628 M.ZLOCATION >= ? AND \
629 M.ZLOCATION <= ? \
630 ORDER BY TVV.TIMEVALUE, \
631 TVV.TIMESERIESID, \
632 TS.PARAMETERID, \
633 TVV.TIMEVALUE
634
635 verticalprofile_odv_data_vector = SELECT SI.NAME CRUISE, \
636 MEDIAN.TIMESERIESPOINT.NAME STATION, \
637 '*' TYPE, \
638 ST_ASTEXT(SHAPE), \
639 0 BOTDEPTH, \
640 (M.ZLOCATION) DEPTH, \
641 TVV.TIMEVALUE, \
642 TVV.XCOMPONENT, \
643 TVV.YCOMPONENT, \
644 TVV.ZCOMPONENT, \
645 TVV.SPEED, \
646 TVV.DIRECTION, \
647 TS.PARAMETERID PARAMETER, \
648 TVV.MEASUREMENTID, \
649 TVV.TIMESERIESID, \
650 '1' QF \
651 FROM MEDIAN.TIMESERIESPOINT, \
652 MEDIAN.TIMESERIESFULLVECTORVALUE TVV, \
653 MEDIAN.TIMESERIES TS, \
654 MEDIAN.MEASUREMENT M, \
655 MEDIAN.SOURCEINFO SI \
656 WHERE SI.SOURCEID = M.SOURCEID AND \
657 M.FEATUREID = MEDIAN.TIMESERIESPOINT.FEATUREID AND \
658 M.MEASUREMENTID = TVV.MEASUREMENTID AND \
659 TVV.TIMESERIESID = TS.TIMESERIESID AND \
660 M.FEATUREID = ? AND \
661 TS.PARAMETERID = ? AND \
662 TVV.TIMEVALUE = ? AND \
663 M.ZLOCATION >= ? AND \
664 M.ZLOCATION <= ? \
665 ORDER BY TVV.MEASUREMENTID, \
666 TVV.TIMESERIESID, \
667 TVV.TIMEVALUE, \
668 TS.PARAMETERID
669
670 #############################################
671 #############################################
672 ########## Vertikalprofil Mesh ##############
673 #############################################
674 #############################################
675
676 verticalprofile_mesh = SELECT OBJECTID KEY, \
677 m.NAME VALUE \
678 FROM MEDIAN.MESH m \
679 WHERE SOURCEID IN (?) \
680 order by m.NAME
681
682 verticalprofile_mesh_with_area = SELECT M.OBJECTID KEY, \
683 M.NAME VALUE \
684 FROM MEDIAN.MESHPOINT, \
685 MEDIAN.MESH M \
686 WHERE M.SOURCEID IN (?) AND \
687 M.MESHID = MEDIAN.MESHPOINT.MESHID AND \
688 INTERSECTS(SHAPE, \
689 (SELECT st_astext(SHAPE) \
690 FROM MEDIAN.FEATUREAREA \
691 WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \
692 FEATURECODE = ? )\
693 ) \
694 order by M.NAME
695
696 verticalprofile_mesh_with_wkt = SELECT M.OBJECTID KEY, \
697 M.NAME VALUE \
698 FROM MEDIAN.MESHPOINT, \
699 MEDIAN.MESH M \
700 WHERE M.SOURCEID IN (?) AND \
701 M.MESHID = MEDIAN.MESHPOINT.MESHID AND \
702 INTERSECTS(SHAPE, "?") \
703 order by M.NAME
704
705 verticalprofile_mesh_point = SELECT FEATUREID, \
706 ST_ASTEXT(SHAPE) \
707 FROM MEDIAN.MESHPOINT \
708 WHERE MESHID in \
709 (SELECT DISTINCT MESHID \
710 FROM MEDIAN.MESH \
711 WHERE OBJECTID = ?) AND \
712 KPOSITION = 1 AND \
713 INTERSECTS(SHAPE,"?")
714
715 verticalprofile_mesh_mindepth = SELECT MP.KPOSITION KEY, \
716 'Layer ' || ML.KPOSITION || ': ' || -ML.UPPERZLOCATION || ' - '|| -ML.LOWERZLOCATION VALUE \
717 from MEDIAN.MESHLAYER ML, \
718 MEDIAN.MESHPOINT MP, \
719 MEDIAN.MESH M \
720 WHERE ML.KPOSITION = MP.KPOSITION AND \
721 ML.MESHID = MP.MESHID AND \
722 M.OBJECTID = ? AND \
723 MP.MESHID = M.MESHID AND \
724 IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) AND \
725 JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) \
726 ORDER BY ML.UPPERZLOCATION DESC
727
728 verticalprofile_mesh_maxdepth = SELECT MP.KPOSITION KEY, \
729 'Layer ' || ML.KPOSITION || ': ' || -ML.UPPERZLOCATION || ' - '|| -ML.LOWERZLOCATION VALUE \
730 from MEDIAN.MESHLAYER ML, \
731 MEDIAN.MESHPOINT MP, \
732 MEDIAN.MESH M \
733 WHERE ML.KPOSITION = MP.KPOSITION AND \
734 ML.MESHID = MP.MESHID AND \
735 M.OBJECTID = ? AND \
736 MP.MESHID = M.MESHID AND \
737 IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) AND \
738 JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) AND \
739 MP.KPOSITION < ? \
740 ORDER BY ML.UPPERZLOCATION DESC
741
742 verticalprofile_mesh_parameter=SELECT distinct \
743 p.PARAMETERID KEY, \
744 p.GERMANNAME || ' ['|| p.UNIT ||']' VALUE, \
745 p.GERMANNAME \
746 from MEDIAN.PARAMETER p, \
747 MEDIAN.MESHSCALARVALUE msc, \
748 MEDIAN.MESH m \
749 where m.OBJECTID = ? AND \
750 msc.PARTID >= m.PARTIDMIN AND \
751 msc.PARTID <= m.PARTIDMAX AND \
752 msc.PARAMETERID = p.PARAMETERID \
753 order by p.GERMANNAME
754
755 verticalprofile_mesh_year= select distinct \
756 to_char(msv.TIMEVALUE,'YYYY') KEY, \
757 to_char(msv.TIMEVALUE,'YYYY') VALUE \
758 from MEDIAN.MESHSCALARVALUE msv, \
759 MEDIAN.MESH m \
760 where m.OBJECTID = ? AND \
761 msv.PARTID >= m.PARTIDMIN AND \
762 msv.PARTID <= m.PARTIDMAX AND \
763 msv.PARAMETERID in (?) \
764 order by to_char(msv.TIMEVALUE, 'YYYY')
765
766 verticalprofile_mesh_date= select distinct \
767 msv.TIMEVALUE KEY, \
768 msv.TIMEVALUE VALUE \
769 from MEDIAN.MESHSCALARVALUE msv, \
770 MEDIAN.MESH m \
771 where m.OBJECTID = ? AND \
772 msv.PARTID >= m.PARTIDMIN AND \
773 msv.PARTID <= m.PARTIDMAX AND \
774 msv.PARAMETERID in (?) AND \
775 to_char(msv.TIMEVALUE,'YYYY') in (?) \
776 order by msv.TIMEVALUE
777
778 verticalprofile_mesh_chart_data=select ml.UPPERZLOCATION XORDINATE, \
779 msv.DATAVALUE YORDINATE, \
780 mp.KPOSITION KPOSITION, \
781 msv.PARAMETERID GROUP1, \
782 msv.TIMEVALUE GROUP2, \
783 1 GROUP3, \
784 2 DATAID, \
785 MP.FEATUREID, \
786 MP.MESHID \
787 from MEDIAN.MESHLAYER ml, \
788 MEDIAN.MESHPOINT mp, \
789 MEDIAN.MESH m, \
790 MEDIAN.MESHSCALARVALUE msv \
791 where msv.FEATUREID = mp. FEATUREID AND \
792 ml.KPOSITION = mp.KPOSITION and \
793 ml.MESHID = mp.MESHID and \
794 m.MESHID = mp.MESHID AND \
795 m.PARTIDMIN <= msv.PARTID AND \
796 m.PARTIDMAX >= msv.PARTID AND \
797 msv.PARAMETERID in (?) AND \
798 msv.TIMEVALUE in (?) AND \
799 m.OBJECTID = ? AND \
800 mp.FEATUREID in \
801 ( select FEATUREID \
802 from MEDIAN.MESHPOINT mp, \
803 MEDIAN.MESH m \
804 where m.OBJECTID = ? AND \
805 mp.MESHID = m.MESHID AND \
806 IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) and \
807 JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?)) AND \
808 mp.KPOSITION <= ? AND \
809 mp.KPOSITION >= ? \
810 order by msv.PARAMETERID, \
811 msv.TIMEVALUE, \
812 ml.UPPERZLOCATION
813
814 verticalprofile_mesh_odv_data=select SI.NAME CRUISE, \
815 m.MESHID || '-' || MEDIAN.MESHPOINT.IPOSITION || '-' || MEDIAN.MESHPOINT.JPOSITION STATION, \
816 '*' TYPE, \
817 ST_ASTEXT(SHAPE), \
818 0 BOTDEPTH, \
819 (((ML.UPPERZLOCATION + ML.LOWERZLOCATION) / 2)) DEPTH, \
820 msv.DATAVALUE, \
821 msv.PARAMETERID PARAMETER, \
822 msv.TIMEVALUE , \
823 '1' QF \
824 from MEDIAN.MESHLAYER ml, \
825 MEDIAN.MESHPOINT, \
826 MEDIAN.MESH m, \
827 MEDIAN.MESHSCALARVALUE msv, \
828 MEDIAN.SOURCEINFO SI \
829 where m.SOURCEID = SI.SOURCEID AND \
830 msv.FEATUREID = MEDIAN.MESHPOINT. FEATUREID AND \
831 ml.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \
832 ml.MESHID = MEDIAN.MESHPOINT.MESHID and \
833 m.MESHID = MEDIAN.MESHPOINT.MESHID AND \
834 m.PARTIDMIN <= msv.PARTID AND \
835 m.PARTIDMAX >= msv.PARTID AND \
836 msv.PARAMETERID in (?) AND \
837 msv.TIMEVALUE in (?) AND \
838 m.OBJECTID = ? AND \
839 MEDIAN.MESHPOINT.FEATUREID in \
840 ( select FEATUREID \
841 from MEDIAN.MESHPOINT mp, \
842 MEDIAN.MESH m \
843 where m.OBJECTID = ? AND \
844 mp.MESHID = m.MESHID AND \
845 IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) and \
846 JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?)) AND \
847 MEDIAN.MESHPOINT.KPOSITION <= ? AND \
848 MEDIAN.MESHPOINT.KPOSITION >= ? \
849 order by msv.TIMEVALUE, \
850 ml.UPPERZLOCATION, \
851 msv.PARAMETERID
852
853 #############################################
854 #############################################
855 ########## Vertikalprofil Mesh ##############
856 ########## Vektordaten ##############
857 #############################################
858 #############################################
859
860 verticalprofile_mesh_parameter_vector=SELECT DISTINCT \
861 P.PARAMETERID KEY, \
862 P.GERMANNAME || ' ['|| P.UNIT ||']' VALUE, \
863 P.GERMANNAME \
864 FROM MEDIAN.PARAMETER P, \
865 MEDIAN.MESHVECTORVALUE MVV, \
866 MEDIAN.MESH M \
867 WHERE M.OBJECTID = ? AND \
868 MVV.PARTID >= M.PARTIDMIN AND \
869 MVV.PARTID <= M.PARTIDMAX AND \
870 MVV.PARAMETERID = P.PARAMETERID \
871 ORDER BY P.GERMANNAME
872
873 verticalprofile_mesh_year_vector= SELECT DISTINCT \
874 to_char(MVV.TIMEVALUE,'YYYY') KEY, \
875 to_char(MVV.TIMEVALUE,'YYYY') VALUE \
876 FROM MEDIAN.MESHVECTORVALUE MVV, \
877 MEDIAN.MESH M \
878 WHERE M.OBJECTID = ? AND \
879 MVV.PARTID >= M.PARTIDMIN AND \
880 MVV.PARTID <= M.PARTIDMAX AND \
881 MVV.PARAMETERID in (?) \
882 ORDER BY to_char(MVV.TIMEVALUE, 'YYYY')
883
884 verticalprofile_mesh_date_vector= SELECT DISTINCT \
885 MVV.TIMEVALUE KEY, \
886 MVV.TIMEVALUE VALUE \
887 FROM MEDIAN.MESHVECTORVALUE MVV, \
888 MEDIAN.MESH M \
889 WHERE M.OBJECTID = ? AND \
890 MVV.PARTID >= M.PARTIDMIN AND \
891 MVV.PARTID <= M.PARTIDMAX AND \
892 MVV.PARAMETERID in (?) AND \
893 to_char(MVV.TIMEVALUE,'YYYY') in (?) \
894 ORDER BY MVV.TIMEVALUE
895
896 verticalprofile_mesh_chart_data_vector=SELECT ML.UPPERZLOCATION XORDINATE, \
897 MP.KPOSITION KPOSITION, \
898 MVV.XCOMPONENT, \
899 MVV.YCOMPONENT, \
900 MVV.ZCOMPONENT, \
901 MVV.SPEED, \
902 MVV.DIRECTION, \
903 MVV.PARAMETERID GROUP1, \
904 MVV.TIMEVALUE GROUP2, \
905 1 GROUP3, \
906 2 DATAID, \
907 MP.FEATUREID, \
908 MP.MESHID \
909 from MEDIAN.MESHLAYER ML, \
910 MEDIAN.MESHPOINT MP, \
911 MEDIAN.MESH M, \
912 MEDIAN.MESHCURRENTVALUE MVV \
913 where MVV.FEATUREID = MP. FEATUREID AND \
914 ML.KPOSITION = MP.KPOSITION AND \
915 ML.MESHID = MP.MESHID AND \
916 M.MESHID = MP.MESHID AND \
917 M.PARTIDMIN <= MVV.PARTID AND \
918 M.PARTIDMAX >= MVV.PARTID AND \
919 MVV.PARAMETERID IN (?) AND \
920 MVV.TIMEVALUE IN (?) AND \
921 M.OBJECTID = ? AND \
922 MP.FEATUREID IN \
923 ( SELECT FEATUREID \
924 FROM MEDIAN.MESHPOINT MP, \
925 MEDIAN.MESH M \
926 WHERE M.OBJECTID = ? AND \
927 MP.MESHID = M.MESHID AND \
928 IPOSITION = (SELECT IPOSITION FROM MEDIAN.MESHPOINT WHERE FEATUREID = ?) AND \
929 JPOSITION = (SELECT JPOSITION FROM MEDIAN.MESHPOINT WHERE FEATUREID = ?)) AND \
930 MP.KPOSITION <= ? AND \
931 MP.KPOSITION >= ? \
932 ORDER BY MVV.PARAMETERID, \
933 MVV.TIMEVALUE, \
934 ML.UPPERZLOCATION
935
936 verticalprofile_mesh_odv_data_vector=SELECT SI.NAME CRUISE, \
937 M.MESHID || '-' || MEDIAN.MESHPOINT.IPOSITION || '-' || MEDIAN.MESHPOINT.JPOSITION STATION, \
938 '*' TYPE, \
939 ST_ASTEXT(SHAPE), \
940 0 BOTDEPTH, \
941 (((ML.UPPERZLOCATION + ML.LOWERZLOCATION) / 2)) DEPTH, \
942 MVV.XCOMPONENT, \
943 MVV.YCOMPONENT, \
944 MVV.ZCOMPONENT, \
945 MVV.SPEED, \
946 MVV.DIRECTION, \
947 MVV.PARAMETERID PARAMETER, \
948 MVV.TIMEVALUE , \
949 '1' QF \
950 FROM MEDIAN.MESHLAYER ML, \
951 MEDIAN.MESHPOINT, \
952 MEDIAN.MESH M, \
953 MEDIAN.MESHCURRENTVALUE MVV, \
954 MEDIAN.SOURCEINFO SI \
955 WHERE M.SOURCEID = SI.SOURCEID AND \
956 MVV.FEATUREID = MEDIAN.MESHPOINT. FEATUREID AND \
957 ML.KPOSITION = MEDIAN.MESHPOINT.KPOSITION AND \
958 ML.MESHID = MEDIAN.MESHPOINT.MESHID AND \
959 M.MESHID = MEDIAN.MESHPOINT.MESHID AND \
960 M.PARTIDMIN <= MVV.PARTID AND \
961 M.PARTIDMAX >= MVV.PARTID AND \
962 MVV.PARAMETERID IN (?) AND \
963 MVV.TIMEVALUE IN (?) AND \
964 M.OBJECTID = ? AND \
965 MEDIAN.MESHPOINT.FEATUREID IN \
966 ( SELECT FEATUREID \
967 FROM MEDIAN.MESHPOINT MP, \
968 MEDIAN.MESH M \
969 WHERE M.OBJECTID = ? AND \
970 MP.MESHID = M.MESHID AND \
971 IPOSITION = (SELECT IPOSITION FROM MEDIAN.MESHPOINT WHERE FEATUREID = ?) AND \
972 JPOSITION = (SELECT JPOSITION FROM MEDIAN.MESHPOINT WHERE FEATUREID = ?)) AND \
973 MEDIAN.MESHPOINT.KPOSITION <= ? AND \
974 MEDIAN.MESHPOINT.KPOSITION >= ? \
975 ORDER BY MVV.TIMEVALUE, \
976 ML.UPPERZLOCATION, \
977 MVV.PARAMETERID
978
979 #############################################
980 #############################################
981 ##### Vertikalprofil InstantaneousPoint #####
982 #############################################
983 #############################################
984
985 verticalprofile_instantaneouspoint_series = SELECT DISTINCT \
986 S.SERIESID KEY, \
987 S.DESCRIPTION VALUE \
988 FROM MEDIAN.SERIES S, \
989 MEDIAN.INSTANTANEOUSPOINT I, \
990 MEDIAN.MEASUREMENT M \
991 WHERE S.SERIESID = I.SERIESID AND \
992 I.POINTSPEC = 4 AND \
993 I.FEATUREID = M.FEATUREID AND \
994 M.SOURCEID= ? \
995 ORDER BY S.DESCRIPTION
996
997 verticalprofile_instantaneouspoint_series_with_area = SELECT \
998 S.SERIESID KEY, \
999 S.DESCRIPTION VALUE \
1000 FROM MEDIAN.INSTANTANEOUSPOINT, \
1001 MEDIAN.SERIES S, \
1002 MEDIAN.MEASUREMENT M \
1003 WHERE S.SERIESID = MEDIAN.INSTANTANEOUSPOINT.SERIESID AND \
1004 MEDIAN.INSTANTANEOUSPOINT.POINTSPEC = 4 AND \
1005 MEDIAN.INSTANTANEOUSPOINT.FEATUREID = M.FEATUREID AND \
1006 M.SOURCEID= ? AND \
1007 INTERSECTS(SHAPE, \
1008 (SELECT st_astext(SHAPE) \
1009 FROM MEDIAN.FEATUREAREA \
1010 WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \
1011 FEATURECODE = ? )\
1012 ) \
1013 ORDER BY S.DESCRIPTION
1014 verticalprofile_instantaneouspoint_series_with_wkt = SELECT \
1015 S.SERIESID KEY, \
1016 S.DESCRIPTION VALUE \
1017 FROM MEDIAN.INSTANTANEOUSPOINT, \
1018 MEDIAN.SERIES S, \
1019 MEDIAN.MEASUREMENT M \
1020 WHERE S.SERIESID = MEDIAN.INSTANTANEOUSPOINT.SERIESID AND \
1021 MEDIAN.INSTANTANEOUSPOINT.POINTSPEC = 4 AND \
1022 MEDIAN.INSTANTANEOUSPOINT.FEATUREID = M.FEATUREID AND \
1023 M.SOURCEID= ? AND \
1024 INTERSECTS(SHAPE, "?") \
1025 ORDER BY S.DESCRIPTION
1026
1027 verticalprofile_instantaneouspoint_point = SELECT \
1028 MEDIAN.INSTANTANEOUSPOINT.FEATUREID, \
1029 to_char(MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE,'DD.MM.YYYY HH24:MI') VALUE, \
1030 MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE, \
1031 ST_ASTEXT(SHAPE) \
1032 FROM MEDIAN.INSTANTANEOUSPOINT, \
1033 MEDIAN.MEASUREMENT M \
1034 WHERE MEDIAN.INSTANTANEOUSPOINT.SERIESID = ? AND \
1035 MEDIAN.INSTANTANEOUSPOINT.POINTSPEC = 4 AND \
1036 MEDIAN.INSTANTANEOUSPOINT.FEATUREID = M.FEATUREID AND \
1037 M.SOURCEID = ? \
1038 ORDER BY MEDIAN.INSTANTANEOUSPOINT.FEATUREID, \
1039 MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE
1040
1041 verticalprofile_instantaneouspoint_point_with_aera = SELECT \
1042 MEDIAN.INSTANTANEOUSPOINT.FEATUREID, \
1043 to_char(MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE,'DD.MM.YYYY HH24:MI') VALUE, \
1044 MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE, \
1045 ST_ASTEXT(SHAPE) \
1046 FROM MEDIAN.INSTANTANEOUSPOINT, \
1047 MEDIAN.MEASUREMENT M \
1048 WHERE MEDIAN.INSTANTANEOUSPOINT.SERIESID = ? AND \
1049 MEDIAN.INSTANTANEOUSPOINT.POINTSPEC = 4 AND \
1050 MEDIAN.INSTANTANEOUSPOINT.FEATUREID = M.FEATUREID AND \
1051 M.SOURCEID = ? AND \
1052 INTERSECTS(SHAPE, \
1053 (SELECT st_astext(SHAPE) \
1054 FROM MEDIAN.FEATUREAREA \
1055 WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \
1056 FEATURECODE = ? )\
1057 ) \
1058 ORDER BY MEDIAN.INSTANTANEOUSPOINT.FEATUREID, \
1059 MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE
1060
1061 verticalprofile_instantaneouspoint_point_with_wkt = SELECT \
1062 MEDIAN.INSTANTANEOUSPOINT.FEATUREID, \
1063 to_char(MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE,'DD.MM.YYYY HH24:MI') VALUE, \
1064 MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE, \
1065 ST_ASTEXT(SHAPE) \
1066 FROM MEDIAN.INSTANTANEOUSPOINT, \
1067 MEDIAN.MEASUREMENT M \
1068 WHERE MEDIAN.INSTANTANEOUSPOINT.SERIESID = ? AND \
1069 MEDIAN.INSTANTANEOUSPOINT.POINTSPEC = 4 AND \
1070 MEDIAN.INSTANTANEOUSPOINT.FEATUREID = M.FEATUREID AND \
1071 M.SOURCEID = ? AND \
1072 INTERSECTS(SHAPE,"?") \
1073 ORDER BY MEDIAN.INSTANTANEOUSPOINT.FEATUREID, \
1074 MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE
1075
1076 verticalprofile_instantaneouspoint_parameter = SELECT DISTINCT \
1077 P.PARAMETERID KEY, \
1078 p.GERMANNAME || ' ['|| p.UNIT ||']' VALUE, \
1079 p.GERMANNAME \
1080 FROM MEDIAN.PARAMETER P, \
1081 MEDIAN.INSTANTANEOUSPOINT IP, \
1082 MEDIAN.MEASUREMENT M, \
1083 MEDIAN.MEASUREDSCALARVALUE MSV \
1084 WHERE IP.FEATUREID = M.FEATUREID AND \
1085 M.MEASUREMENTID = MSV.MEASUREMENTID AND \
1086 MSV.PARAMETERID = P.PARAMETERID AND \
1087 IP.FEATUREID = ? \
1088 ORDER BY P.GERMANNAME
1089
1090 verticalprofile_instantaneouspoint_minmaxdepth = SELECT min(M.ZLOCATION) MIN, \
1091 max(M.ZLOCATION) MAX \
1092 FROM MEDIAN.INSTANTANEOUSPOINT IP, \
1093 MEDIAN.MEASUREMENT M, \
1094 MEDIAN.MEASUREDSCALARVALUE MSV \
1095 WHERE IP.FEATUREID = M.FEATUREID AND \
1096 M.MEASUREMENTID = MSV.MEASUREMENTID AND \
1097 IP.FEATUREID = ? AND \
1098 MSV.PARAMETERID in (?)
1099
1100 verticalprofile_instantaneouspoint_chart_data = SELECT M.ZLOCATION XORDINATE, \
1101 MSV.DATAVALUE YORDINATE, \
1102 MSV.PARAMETERID GROUP1, \
1103 IP.TIMEVALUE GROUP2, \
1104 1 GROUP3, \
1105 3 DATAID, \
1106 IP.FEATUREID, \
1107 IP.SERIESID \
1108 FROM MEDIAN.INSTANTANEOUSPOINT IP, \
1109 MEDIAN.MEASUREMENT M, \
1110 MEDIAN.MEASUREDSCALARVALUE MSV \
1111 WHERE IP.FEATUREID = M.FEATUREID AND \
1112 M.MEASUREMENTID = MSV.MEASUREMENTID AND \
1113 IP.FEATUREID = ? AND \
1114 MSV.PARAMETERID in (?) AND \
1115 M.ZLOCATION >= ? AND \
1116 M.ZLOCATION <= ? \
1117 ORDER BY IP.TIMEVALUE, \
1118 MSV.PARAMETERID, \
1119 M.ZLOCATION
1120
1121 verticalprofile_instantaneouspoint_odv_data = SELECT S.DESCRIPTION CRUISE, \
1122 MEDIAN.INSTANTANEOUSPOINT.FEATUREID STATION, \
1123 '*' TYPE, \
1124 ST_ASTEXT(SHAPE), \
1125 0 BOTDEPTH, \
1126 (M.ZLOCATION) DEPTH, \
1127 MSV.DATAVALUE, \
1128 MSV.PARAMETERID PARAMETER, \
1129 MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE , \
1130 '1' QF \
1131 FROM MEDIAN.SERIES S, \
1132 MEDIAN.INSTANTANEOUSPOINT, \
1133 MEDIAN.MEASUREMENT M, \
1134 MEDIAN.MEASUREDSCALARVALUE MSV \
1135 WHERE S.SERIESID = MEDIAN.INSTANTANEOUSPOINT.SERIESID AND \
1136 MEDIAN.INSTANTANEOUSPOINT.FEATUREID = M.FEATUREID AND \
1137 M.MEASUREMENTID = MSV.MEASUREMENTID AND \
1138 MEDIAN.INSTANTANEOUSPOINT.FEATUREID = ? AND \
1139 MSV.PARAMETERID in (?) AND \
1140 M.ZLOCATION >= ? AND \
1141 M.ZLOCATION <= ? \
1142 ORDER BY MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE, \
1143 M.ZLOCATION, \
1144 MSV.PARAMETERID
1145
1146 #############################################
1147 #############################################
1148 #### Horizontalprofil InstantaneousPoint ####
1149 #############################################
1150 #############################################
1151 horizontalprofile_instantaneouspoint_vehicle = SELECT DISTINCT \
1152 V.VEHICLEID KEY, \
1153 V.NAME VALUE \
1154 FROM MEDIAN.VEHICLE V, \
1155 MEDIAN.CRUISE C, \
1156 MEDIAN.TRACK T, \
1157 MEDIAN.SURVEYINFO S, \
1158 MEDIAN.INSTANTANEOUSPOINT I, \
1159 MEDIAN.MEASUREMENT M \
1160 WHERE V.VEHICLEID = C.VEHICLEID AND \
1161 C.CRUISEID = T.CRUISEID AND \
1162 T.TRACKID = S.TRACKID AND \
1163 S.SURVEYID = I.SURVEYID AND \
1164 I.FEATUREID = M.FEATUREID AND \
1165 M.SOURCEID = ? \
1166 ORDER BY V.NAME
1167
1168 horizontalprofile_instantaneouspoint_vehicle_with_area = SELECT V.VEHICLEID KEY, \
1169 V.NAME VALUE \
1170 FROM MEDIAN.TRACK, \
1171 MEDIAN.VEHICLE V, \
1172 MEDIAN.CRUISE C \
1173 WHERE V.VEHICLEID = C.VEHICLEID AND \
1174 C.CRUISEID = MEDIAN.TRACK.CRUISEID AND \
1175 MEDIAN.TRACK.TRACKID IN \
1176 (SELECT DISTINCT S.TRACKID \
1177 FROM MEDIAN.MEASUREMENT M ,\
1178 MEDIAN.INSTANTANEOUSPOINT I, \
1179 MEDIAN.SURVEYINFO S \
1180 WHERE M.SOURCEID = ? AND \
1181 I.FEATUREID = M.FEATUREID AND \
1182 S.SURVEYID = I.SURVEYID)AND \
1183 INTERSECTS(SHAPE, \
1184 (SELECT st_astext(SHAPE) \
1185 FROM MEDIAN.FEATUREAREA \
1186 WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \
1187 FEATURECODE = ? )\
1188 ) \
1189 ORDER BY V.NAME
1190
1191 horizontalprofile_instantaneouspoint_vehicle_with_wkt = SELECT V.VEHICLEID KEY, \
1192 V.NAME VALUE \
1193 FROM MEDIAN.TRACK, \
1194 MEDIAN.VEHICLE V, \
1195 MEDIAN.CRUISE C \
1196 WHERE V.VEHICLEID = C.VEHICLEID AND \
1197 C.CRUISEID = MEDIAN.TRACK.CRUISEID AND \
1198 MEDIAN.TRACK.TRACKID IN \
1199 (SELECT DISTINCT S.TRACKID \
1200 FROM MEDIAN.MEASUREMENT M ,\
1201 MEDIAN.INSTANTANEOUSPOINT I, \
1202 MEDIAN.SURVEYINFO S \
1203 WHERE M.SOURCEID = ? AND \
1204 I.FEATUREID = M.FEATUREID AND \
1205 S.SURVEYID = I.SURVEYID)AND \
1206 INTERSECTS(SHAPE,"?") \
1207 ORDER BY V.NAME
1208
1209 horizontalprofile_instantaneouspoint_cruise = SELECT DISTINCT \
1210 C.CRUISEID KEY, \
1211 V.NAME || ' ' || \
1212 C.NAME || ' ' || \
1213 TO_CHAR(C.STARTDATE,'DD.MM.YYYY') || ' - ' || \
1214 TO_CHAR(C.ENDDATE,'DD.MM.YYYY') VALUE, \
1215 V.NAME, \
1216 C.NAME \
1217 FROM MEDIAN.CRUISE C, \
1218 MEDIAN.VEHICLE V, \
1219 MEDIAN.TRACK T, \
1220 MEDIAN.SURVEYINFO S, \
1221 MEDIAN.INSTANTANEOUSPOINT I, \
1222 MEDIAN.MEASUREMENT M \
1223 WHERE C.VEHICLEID = V.VEHICLEID AND \
1224 C.CRUISEID = T.CRUISEID AND \
1225 T.TRACKID = S.TRACKID AND \
1226 S.SURVEYID = I.SURVEYID AND \
1227 I.FEATUREID = M.FEATUREID AND \
1228 C.VEHICLEID = ? AND \
1229 M.SOURCEID = ? \
1230 ORDER BY V.NAME, C.NAME
1231
1232 horizontalprofile_instantaneouspoint_cruise_with_area = SELECT C.CRUISEID KEY, \
1233 V.NAME || ' ' || \
1234 C.NAME || ' ' || \
1235 TO_CHAR(C.STARTDATE,'DD.MM.YYYY') || ' - ' || \
1236 TO_CHAR(C.ENDDATE,'DD.MM.YYYY') VALUE, \
1237 V.NAME, \
1238 C.NAME \
1239 FROM MEDIAN.TRACK, \
1240 MEDIAN.VEHICLE V, \
1241 MEDIAN.CRUISE C \
1242 WHERE V.VEHICLEID = C.VEHICLEID AND \
1243 C.CRUISEID = MEDIAN.TRACK.CRUISEID AND \
1244 MEDIAN.TRACK.TRACKID IN \
1245 (SELECT DISTINCT S.TRACKID \
1246 FROM MEDIAN.MEASUREMENT M ,\
1247 MEDIAN.INSTANTANEOUSPOINT I, \
1248 MEDIAN.SURVEYINFO S \
1249 WHERE M.SOURCEID = ? AND \
1250 C.VEHICLEID = ? AND \
1251 I.FEATUREID = M.FEATUREID AND \
1252 S.SURVEYID = I.SURVEYID)AND \
1253 INTERSECTS(SHAPE, \
1254 (SELECT st_astext(SHAPE) \
1255 FROM MEDIAN.FEATUREAREA \
1256 WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \
1257 FEATURECODE = ? )\
1258 ) \
1259 ORDER BY V.NAME
1260
1261 horizontalprofile_instantaneouspoint_cruise_with_wkt = SELECT C.CRUISEID KEY, \
1262 V.NAME || ' ' || \
1263 C.NAME || ' ' || \
1264 TO_CHAR(C.STARTDATE,'DD.MM.YYYY') || ' - ' || \
1265 TO_CHAR(C.ENDDATE,'DD.MM.YYYY') VALUE, \
1266 V.NAME, \
1267 C.NAME \
1268 FROM MEDIAN.TRACK, \
1269 MEDIAN.VEHICLE V, \
1270 MEDIAN.CRUISE C \
1271 WHERE V.VEHICLEID = C.VEHICLEID AND \
1272 C.CRUISEID = MEDIAN.TRACK.CRUISEID AND \
1273 MEDIAN.TRACK.TRACKID IN \
1274 (SELECT DISTINCT S.TRACKID \
1275 FROM MEDIAN.MEASUREMENT M ,\
1276 MEDIAN.INSTANTANEOUSPOINT I, \
1277 MEDIAN.SURVEYINFO S \
1278 WHERE M.SOURCEID = ? AND \
1279 C.VEHICLEID = ? AND \
1280 I.FEATUREID = M.FEATUREID AND \
1281 S.SURVEYID = I.SURVEYID)AND \
1282 INTERSECTS(SHAPE,"?") \
1283 ORDER BY V.NAME
1284
1285 horizontalprofile_instantaneouspoint_track= SELECT \
1286 T.TRACKID KEY, \
1287 to_char(T.STARTDATE,'DD.MM.YYYY HH24:MI') || ' - '|| to_char(T.ENDDATE,'DD.MM.YYYY HH24:MI') || ' - '|| T.NAME VALUE \
1288 FROM MEDIAN.TRACK T \
1289 WHERE T.CRUISEID = ? \
1290 ORDER BY T.STARTDATE, \
1291 T.ENDDATE, \
1292 T.NAME
1293
1294 horizontalprofile_instantaneouspoint_track_with_area = SELECT \
1295 MEDIAN.TRACK.TRACKID KEY, \
1296 to_char(STARTDATE,'DD-MM-YYYY HH24:MI') || ' - '|| to_char(ENDDATE,'DD-MM-YYYY HH24:MI') || ' - '|| NAME VALUE, \
1297 NAME \
1298 FROM MEDIAN.TRACK \
1299 WHERE MEDIAN.TRACK.CRUISEID = ? AND \
1300 INTERSECTS(SHAPE, \
1301 (SELECT st_astext(SHAPE) \
1302 FROM MEDIAN.FEATUREAREA \
1303 WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \
1304 FEATURECODE = ? )\
1305 ) \
1306 ORDER BY STARTDATE, \
1307 ENDDATE, \
1308 NAME
1309
1310 horizontalprofile_instantaneouspoint_track_with_wkt = SELECT \
1311 MEDIAN.TRACK.TRACKID KEY, \
1312 to_char(STARTDATE,'DD-MM-YYYY HH24:MI') || ' - '|| to_char(ENDDATE,'DD-MM-YYYY HH24:MI') || ' - '|| NAME VALUE, \
1313 NAME \
1314 FROM MEDIAN.TRACK \
1315 WHERE MEDIAN.TRACK.CRUISEID = ? AND \
1316 INTERSECTS(SHAPE, "?") \
1317 ORDER BY STARTDATE, \
1318 ENDDATE, \
1319 NAME
1320
1321 horizontalprofile_instantaneouspoint_surveyinfo = SELECT \
1322 SURVEYID KEY, \
1323 to_char(STARTDATE,'DD.MM.YYYY HH24:MI') || ' - '|| to_char(ENDDATE,'DD.MM.YYYY HH24:MI') || ' - '|| DESCRIPTION VALUE \
1324 FROM MEDIAN.SURVEYINFO \
1325 WHERE TRACKID = ? \
1326 ORDER BY STARTDATE, \
1327 ENDDATE, \
1328 DESCRIPTION
1329 horizontalprofile_instantaneouspoint_parameter = SELECT DISTINCT \
1330 P.PARAMETERID KEY, \
1331 p.GERMANNAME || ' ['|| p.UNIT ||']' VALUE, \
1332 p.GERMANNAME \
1333 FROM MEDIAN.PARAMETER P, \
1334 MEDIAN.MEASUREMENT M, \
1335 MEDIAN.INSTANTANEOUSPOINT IP, \
1336 MEDIAN.MEASUREDSCALARVALUE MSV \
1337 WHERE P.PARAMETERID = MSV.PARAMETERID AND \
1338 MSV.MEASUREMENTID = M.MEASUREMENTID AND \
1339 M.FEATUREID = IP.FEATUREID AND \
1340 IP.SURVEYID = ? \
1341 ORDER BY P.GERMANNAME
1342 horizontalprofile_instantaneouspoint_depth = SELECT DISTINCT \
1343 M.ZLOCATION KEY, \
1344 M.ZLOCATION VALUE \
1345 FROM MEDIAN.MEASUREMENT M, \
1346 MEDIAN.INSTANTANEOUSPOINT IP \
1347 WHERE M.FEATUREID = IP.FEATUREID AND \
1348 IP.SURVEYID = ? \
1349 ORDER BY M.ZLOCATION
1350 horizontalprofile_instantaneouspoint_chart_data = SELECT ST_ASTEXT(SHAPE), \
1351 MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE TIME, \
1352 MSV.DATAVALUE YORDINATE, \
1353 MSV.PARAMETERID GROUP1, \
1354 ZLOCATION GROUP2, \
1355 1 GROUP3, \
1356 3 DATAID, \
1357 MEDIAN.INSTANTANEOUSPOINT.SURVEYID \
1358 FROM MEDIAN.INSTANTANEOUSPOINT, \
1359 MEDIAN.MEASUREMENT M, \
1360 MEDIAN.MEASUREDSCALARVALUE MSV \
1361 WHERE MEDIAN.INSTANTANEOUSPOINT.FEATUREID = M.FEATUREID AND \
1362 M.MEASUREMENTID = MSV.MEASUREMENTID AND \
1363 MEDIAN.INSTANTANEOUSPOINT.SURVEYID = ? AND \
1364 M.ZLOCATION IN (?) AND \
1365 MSV.PARAMETERID in (?) \
1366 ORDER BY MSV.PARAMETERID, \
1367 M.ZLOCATION, \
1368 MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE
1369 horizontalprofile_instantaneouspoint_odv_data = SELECT V.NAME || ' ' || C.NAME CRUISE, \
1370 MEDIAN.InstantaneousPoint.FEATUREID STATION, \
1371 '*' TYPE, \
1372 0 BOTDEPTH, \
1373 (M.ZLOCATION * -1) DEPTH, \
1374 ST_ASTEXT(SHAPE), \
1375 MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE, \
1376 MSV.DATAVALUE, \
1377 MSV.PARAMETERID PARAMETER , \
1378 '1' QF \
1379 FROM MEDIAN.INSTANTANEOUSPOINT, \
1380 MEDIAN.VEHICLE V, \
1381 MEDIAN.CRUISE C, \
1382 (SELECT TRACKID,CRUISEID FROM MEDIAN.TRACK) T, \
1383 MEDIAN.SURVEYINFO S, \
1384 MEDIAN.MEASUREMENT M, \
1385 MEDIAN.MEASUREDSCALARVALUE MSV \
1386 WHERE V.VEHICLEID = C.VEHICLEID AND \
1387 C.CRUISEID = T.CRUISEID AND \
1388 T.TRACKID = S.TRACKID AND \
1389 S.SURVEYID = MEDIAN.INSTANTANEOUSPOINT.SURVEYID AND \
1390 MEDIAN.INSTANTANEOUSPOINT.FEATUREID = M.FEATUREID AND \
1391 M.MEASUREMENTID = MSV.MEASUREMENTID AND \
1392 MEDIAN.INSTANTANEOUSPOINT.SURVEYID = ? AND \
1393 M.ZLOCATION IN (?) AND \
1394 MSV.PARAMETERID in (?) \
1395 ORDER BY MSV.PARAMETERID, \
1396 M.ZLOCATION, \
1397 MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE
1398
1399 #############################################
1400 #############################################
1401 ########### Horizontalprofil MESH ###########
1402 #############################################
1403 #############################################
1404 horizontalprofile_meshpoint_depth = SELECT DISTINCT \
1405 mp.KPOSITION KEY, \
1406 'Layer ' || ml.KPOSITION || ': ' || -ml.UPPERZLOCATION || ' - '|| -ml.LOWERZLOCATION VALUE \
1407 from MEDIAN.MESHLAYER ml, \
1408 MEDIAN.MESHPOINT mp \
1409 where ml.KPOSITION = mp.KPOSITION and \
1410 ml.MESHID = mp.MESHID and \
1411 mp.FEATUREID in \
1412 ( select FEATUREID \
1413 from MEDIAN.MESHPOINT mp, \
1414 MEDIAN.MESH m \
1415 where m.OBJECTID = ? AND \
1416 mp.MESHID = m.MESHID AND \
1417 IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) and \
1418 JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?)) \
1419 order by mp.KPOSITION
1420
1421 horizontalprofile_mesh_chart_data = SELECT ST_ASTEXT(SHAPE), \
1422 msv.DATAVALUE YORDINATE, \
1423 msv.PARAMETERID GROUP1, \
1424 MEDIAN.MESHPOINT.KPOSITION GROUP2, \
1425 msv.TIMEVALUE GROUP3, \
1426 MEDIAN.MESHPOINT.JPOSITION, \
1427 MEDIAN.MESHPOINT.IPOSITION, \
1428 2 DATAID, \
1429 MEDIAN.MESHPOINT.MESHID \
1430 from MEDIAN.MESHLAYER ml, \
1431 MEDIAN.MESHPOINT, \
1432 MEDIAN.MESH m, \
1433 MEDIAN.MESHSCALARVALUE msv \
1434 where msv.FEATUREID = MEDIAN.MESHPOINT. FEATUREID AND \
1435 ml.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \
1436 ml.MESHID = MEDIAN.MESHPOINT.MESHID and \
1437 m.MESHID = MEDIAN.MESHPOINT.MESHID AND \
1438 m.PARTIDMIN <= msv.PARTID AND \
1439 m.PARTIDMAX >= msv.PARTID AND \
1440 msv.PARAMETERID in (?) AND \
1441 msv.TIMEVALUE in (?) AND \
1442 m.OBJECTID = ? AND \
1443 MEDIAN.MESHPOINT.FEATUREID in \
1444 ( select FEATUREID \
1445 from MEDIAN.MESHPOINT mp, \
1446 MEDIAN.MESH m \
1447 where m.OBJECTID = ? AND \
1448 mp.MESHID = m.MESHID AND \
1449 KPOSITION in ( ? ) and \
1450 ? = (select ? from MEDIAN.MESHPOINT where FEATUREID = ?)) \
1451 order by msv.TIMEVALUE, \
1452 MEDIAN.MESHPOINT.KPOSITION, \
1453 msv.PARAMETERID, \
1454 MEDIAN.MESHPOINT.JPOSITION, \
1455 MEDIAN.MESHPOINT.IPOSITION
1456
1457 horizontalprofile_mesh_odv_data = SELECT SI.NAME CRUISE, \
1458 m.MESHID || '-' || MEDIAN.MESHPOINT.IPOSITION || '-' || MEDIAN.MESHPOINT.JPOSITION STATION, \
1459 '*' TYPE, \
1460 ST_ASTEXT(SHAPE), \
1461 0 BOTDEPTH, \
1462 (((ML.UPPERZLOCATION + ML.LOWERZLOCATION) / 2)*-1) DEPTH, \
1463 msv.DATAVALUE, \
1464 msv.PARAMETERID PARAMETER, \
1465 msv.TIMEVALUE, \
1466 MEDIAN.MESHPOINT.JPOSITION, \
1467 MEDIAN.MESHPOINT.IPOSITION , \
1468 '1' QF \
1469 from MEDIAN.MESHLAYER ml, \
1470 MEDIAN.MESHPOINT, \
1471 MEDIAN.MESH m, \
1472 MEDIAN.MESHSCALARVALUE msv, \
1473 MEDIAN.SOURCEINFO SI \
1474 where msv.FEATUREID = MEDIAN.MESHPOINT. FEATUREID AND \
1475 ml.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \
1476 ml.MESHID = MEDIAN.MESHPOINT.MESHID and \
1477 m.MESHID = MEDIAN.MESHPOINT.MESHID AND \
1478 m.SOURCEID = SI.SOURCEID AND \
1479 m.PARTIDMIN <= msv.PARTID AND \
1480 m.PARTIDMAX >= msv.PARTID AND \
1481 msv.PARAMETERID in (?) AND \
1482 msv.TIMEVALUE in (?) AND \
1483 m.OBJECTID = ? AND \
1484 MEDIAN.MESHPOINT.FEATUREID in \
1485 ( select FEATUREID \
1486 from MEDIAN.MESHPOINT mp, \
1487 MEDIAN.MESH m \
1488 where m.OBJECTID = ? AND \
1489 mp.MESHID = m.MESHID AND \
1490 KPOSITION in ( ? ) and \
1491 ? = (select ? from MEDIAN.MESHPOINT where FEATUREID = ?)) \
1492 order by msv.TIMEVALUE, \
1493 MEDIAN.MESHPOINT.KPOSITION, \
1494 MEDIAN.MESHPOINT.JPOSITION, \
1495 MEDIAN.MESHPOINT.IPOSITION, \
1496 msv.PARAMETERID
1497
1498 #############################################
1499 #############################################
1500 ########### Horizontalprofil MESH ###########
1501 ########### Vektordaten ###########
1502 #############################################
1503 #############################################
1504
1505 horizontalprofile_mesh_chart_data_vector= SELECT ST_ASTEXT(SHAPE), \
1506 MVV.XCOMPONENT, \
1507 MVV.YCOMPONENT, \
1508 MVV.ZCOMPONENT, \
1509 MVV.SPEED, \
1510 MVV.DIRECTION, \
1511 MVV.PARAMETERID GROUP1, \
1512 MEDIAN.MESHPOINT.KPOSITION GROUP2, \
1513 MVV.TIMEVALUE GROUP3, \
1514 MEDIAN.MESHPOINT.JPOSITION, \
1515 MEDIAN.MESHPOINT.IPOSITION, \
1516 2 DATAID, \
1517 MEDIAN.MESHPOINT.MESHID \
1518 FROM MEDIAN.MESHLAYER ML, \
1519 MEDIAN.MESHPOINT, \
1520 MEDIAN.MESH M, \
1521 MEDIAN.MESHCURRENTVALUE MVV \
1522 WHERE MVV.FEATUREID = MEDIAN.MESHPOINT. FEATUREID AND \
1523 ML.KPOSITION = MEDIAN.MESHPOINT.KPOSITION AND \
1524 ML.MESHID = MEDIAN.MESHPOINT.MESHID AND \
1525 M.MESHID = MEDIAN.MESHPOINT.MESHID AND \
1526 M.PARTIDMIN <= MVV.PARTID AND \
1527 M.PARTIDMAX >= MVV.PARTID AND \
1528 MVV.PARAMETERID = ? AND \
1529 MVV.TIMEVALUE = ? AND \
1530 M.OBJECTID = ? AND \
1531 MEDIAN.MESHPOINT.FEATUREID IN \
1532 ( SELECT FEATUREID \
1533 from MEDIAN.MESHPOINT MP, \
1534 MEDIAN.MESH M \
1535 where M.OBJECTID = ? AND \
1536 MP.MESHID = M.MESHID AND \
1537 KPOSITION IN ( ? ) AND \
1538 ? = (SELECT ? FROM MEDIAN.MESHPOINT WHERE FEATUREID = ?)) \
1539 ORDER BY MVV.TIMEVALUE, \
1540 MEDIAN.MESHPOINT.KPOSITION, \
1541 MVV.PARAMETERID, \
1542 MEDIAN.MESHPOINT.JPOSITION, \
1543 MEDIAN.MESHPOINT.IPOSITION
1544
1545 horizontalprofile_mesh_odv_data_vector= SELECT SI.NAME CRUISE, \
1546 M.MESHID || '-' || MEDIAN.MESHPOINT.IPOSITION || '-' || MEDIAN.MESHPOINT.JPOSITION STATION, \
1547 '*' TYPE, \
1548 ST_ASTEXT(SHAPE), \
1549 0 BOTDEPTH, \
1550 (((ML.UPPERZLOCATION + ML.LOWERZLOCATION) / 2)*-1) DEPTH, \
1551 MVV.XCOMPONENT, \
1552 MVV.YCOMPONENT, \
1553 MVV.ZCOMPONENT, \
1554 MVV.SPEED, \
1555 MVV.DIRECTION, \
1556 MVV.PARAMETERID PARAMETER, \
1557 MVV.TIMEVALUE, \
1558 MEDIAN.MESHPOINT.JPOSITION, \
1559 MEDIAN.MESHPOINT.IPOSITION , \
1560 '1' QF \
1561 from MEDIAN.MESHLAYER ML, \
1562 MEDIAN.MESHPOINT, \
1563 MEDIAN.MESH M, \
1564 MEDIAN.MESHCURRENTVALUE MVV, \
1565 MEDIAN.SOURCEINFO SI \
1566 where MVV.FEATUREID = MEDIAN.MESHPOINT. FEATUREID AND \
1567 ML.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \
1568 ML.MESHID = MEDIAN.MESHPOINT.MESHID and \
1569 M.MESHID = MEDIAN.MESHPOINT.MESHID AND \
1570 M.SOURCEID = SI.SOURCEID AND \
1571 M.PARTIDMIN <= MVV.PARTID AND \
1572 M.PARTIDMAX >= MVV.PARTID AND \
1573 MVV.PARAMETERID = ? AND \
1574 MVV.TIMEVALUE = ? AND \
1575 M.OBJECTID = ? AND \
1576 MEDIAN.MESHPOINT.FEATUREID IN \
1577 ( SELECT FEATUREID \
1578 FROM MEDIAN.MESHPOINT MP, \
1579 MEDIAN.MESH M \
1580 WHERE M.OBJECTID = ? AND \
1581 MP.MESHID = M.MESHID AND \
1582 KPOSITION IN ( ? ) AND \
1583 ? = (SELECT ? FROM MEDIAN.MESHPOINT WHERE FEATUREID = ?)) \
1584 ORDER BY MVV.TIMEVALUE, \
1585 MEDIAN.MESHPOINT.KPOSITION, \
1586 MEDIAN.MESHPOINT.JPOSITION, \
1587 MEDIAN.MESHPOINT.IPOSITION, \
1588 MVV.PARAMETERID
1589
1590 #############################################
1591 #############################################
1592 ########### Horizontalprofil MESH ###########
1593 ########### Schnittprofil ###########
1594 #############################################
1595 #############################################
1596 horizontalprofile_meshpoint_depth = SELECT DISTINCT \
1597 mp.KPOSITION KEY, \
1598 'Layer ' || ml.KPOSITION || ': ' || -ml.UPPERZLOCATION || ' - '|| -ml.LOWERZLOCATION VALUE \
1599 from MEDIAN.MESHLAYER ml, \
1600 MEDIAN.MESHPOINT mp \
1601 where ml.KPOSITION = mp.KPOSITION and \
1602 ml.MESHID = mp.MESHID and \
1603 mp.FEATUREID in \
1604 ( select FEATUREID \
1605 from MEDIAN.MESHPOINT mp, \
1606 MEDIAN.MESH m \
1607 where m.OBJECTID = ? AND \
1608 mp.MESHID = m.MESHID )\
1609 order by mp.KPOSITION
1610
1611 horizontalprofile_meshpoint_cross_ij=SELECT MEDIAN.MESHFACE.JPOSITION, \
1612 MEDIAN.MESHFACE.IPOSITION \
1613 FROM MEDIAN.MESHFACE, \
1614 MEDIAN.MESH M \
1615 WHERE MEDIAN.MESHFACE.KPOSITION = 1 AND \
1616 M.MESHID = MEDIAN.MESHFACE.MESHID AND \
1617 M.OBJECTID = ? AND \
1618 INTERSECTS(SHAPE, "?")
1619 horizontalprofile_mesh_cross_chart_data = SELECT ST_ASTEXT(SHAPE), \
1620 msv.DATAVALUE YORDINATE, \
1621 msv.PARAMETERID GROUP1, \
1622 MEDIAN.MESHPOINT.KPOSITION GROUP2, \
1623 msv.TIMEVALUE GROUP3, \
1624 MEDIAN.MESHPOINT.JPOSITION, \
1625 MEDIAN.MESHPOINT.IPOSITION, \
1626 MEDIAN.MESHPOINT.MESHID MESHID, \
1627 2 DATAID \
1628 from MEDIAN.MESHLAYER ml, \
1629 MEDIAN.MESHPOINT, \
1630 MEDIAN.MESH m, \
1631 MEDIAN.MESHSCALARVALUE msv \
1632 where msv.FEATUREID = MEDIAN.MESHPOINT.FEATUREID AND \
1633 ml.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \
1634 ml.MESHID = MEDIAN.MESHPOINT.MESHID and \
1635 m.MESHID = MEDIAN.MESHPOINT.MESHID AND \
1636 m.PARTIDMIN <= msv.PARTID AND \
1637 m.PARTIDMAX >= msv.PARTID AND \
1638 msv.PARAMETERID in (?) AND \
1639 msv.TIMEVALUE in (?) AND \
1640 m.OBJECTID = ? AND \
1641 ml.KPOSITION in (?) AND \
1642 MEDIAN.MESHPOINT.FEATUREID in \
1643 ( \
1644 SELECT distinct FEATUREID \
1645 FROM MEDIAN.MESHPOINT \
1646 WHERE ? \
1647 ) \
1648 order by msv.TIMEVALUE, \
1649 MEDIAN.MESHPOINT.KPOSITION, \
1650 msv.PARAMETERID, \
1651 MEDIAN.MESHPOINT.JPOSITION, \
1652 MEDIAN.MESHPOINT.IPOSITION
1653
1654 #############################################
1655 #############################################
1656 ########### Horizontalprofil MESH ###########
1657 ########### Schnittprofil ###########
1658 ########### Vektordaten ###########
1659 #############################################
1660 #############################################
1661
1662 horizontalprofile_mesh_cross_chart_data_vector = SELECT ST_ASTEXT(SHAPE), \
1663 MVV.XCOMPONENT, \
1664 MVV.YCOMPONENT, \
1665 MVV.ZCOMPONENT, \
1666 MVV.SPEED, \
1667 MVV.DIRECTION, \
1668 MVV.PARAMETERID GROUP1, \
1669 MEDIAN.MESHPOINT.KPOSITION GROUP2, \
1670 MVV.TIMEVALUE GROUP3, \
1671 MEDIAN.MESHPOINT.JPOSITION, \
1672 MEDIAN.MESHPOINT.IPOSITION, \
1673 2 DATAID \
1674 FROM MEDIAN.MESHLAYER ML, \
1675 MEDIAN.MESHPOINT, \
1676 MEDIAN.MESH M, \
1677 MEDIAN.MESHCURRENTVALUE MVV \
1678 WHERE MVV.FEATUREID = MEDIAN.MESHPOINT.FEATUREID AND \
1679 ML.KPOSITION = MEDIAN.MESHPOINT.KPOSITION AND \
1680 ML.MESHID = MEDIAN.MESHPOINT.MESHID AND \
1681 M.MESHID = MEDIAN.MESHPOINT.MESHID AND \
1682 M.PARTIDMIN <= MVV.PARTID AND \
1683 M.PARTIDMAX >= MVV.PARTID AND \
1684 MVV.PARAMETERID = ? AND \
1685 MVV.TIMEVALUE = ? AND \
1686 M.OBJECTID = ? AND \
1687 ML.KPOSITION in (?) AND \
1688 MEDIAN.MESHPOINT.FEATUREID in \
1689 ( \
1690 SELECT DISTINCT FEATUREID \
1691 FROM MEDIAN.MESHPOINT \
1692 WHERE ? \
1693 ) \
1694 ORDER BY MVV.TIMEVALUE, \
1695 MEDIAN.MESHPOINT.KPOSITION, \
1696 MVV.PARAMETERID, \
1697 MEDIAN.MESHPOINT.JPOSITION, \
1698 MEDIAN.MESHPOINT.IPOSITION
1699
1700 #############################################
1701 #############################################
1702 ############# Profilschnitt MESH ############
1703 #############################################
1704 #############################################
1705 verticalcrosssection_mesh_year = select distinct \
1706 to_char(msv.TIMEVALUE,'YYYY') KEY, \
1707 to_char(msv.TIMEVALUE,'YYYY') VALUE \
1708 from MEDIAN.MESHSCALARVALUE msv, \
1709 MEDIAN.MESH m \
1710 where m.OBJECTID = ? AND \
1711 msv.PARTID >= m.PARTIDMIN AND \
1712 msv.PARTID <= m.PARTIDMAX AND \
1713 msv.PARAMETERID = ? \
1714 order by to_char(msv.TIMEVALUE,'YYYY')
1715 verticalcrosssection_mesh_date = select distinct \
1716 msv.TIMEVALUE KEY, \
1717 msv.TIMEVALUE VALUE \
1718 from MEDIAN.MESHSCALARVALUE msv, \
1719 MEDIAN.MESH m \
1720 where m.OBJECTID = ? AND \
1721 msv.PARTID >= m.PARTIDMIN AND \
1722 msv.PARTID <= m.PARTIDMAX AND \
1723 msv.PARAMETERID = ? AND \
1724 to_char(msv.TIMEVALUE,'YYYY') in (?) \
1725 order by msv.TIMEVALUE
1726 verticalcrosssection_mesh_chart_data = SELECT ST_ASTEXT(SHAPE), \
1727 ((ml.UPPERZLOCATION + ml.LOWERZLOCATION) / 2) Z, \
1728 msv.DATAVALUE YORDINATE, \
1729 msv.PARAMETERID GROUP1, \
1730 msv.TIMEVALUE GROUP2, \
1731 MEDIAN.MESHPOINT.JPOSITION, \
1732 MEDIAN.MESHPOINT.IPOSITION, \
1733 MEDIAN.MESHPOINT.KPOSITION \
1734 from MEDIAN.MESHLAYER ml, \
1735 MEDIAN.MESHPOINT, \
1736 MEDIAN.MESH m, \
1737 MEDIAN.MESHSCALARVALUE msv \
1738 where msv.FEATUREID = MEDIAN.MESHPOINT. FEATUREID AND \
1739 ml.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \
1740 ml.MESHID = MEDIAN.MESHPOINT.MESHID and \
1741 m.MESHID = MEDIAN.MESHPOINT.MESHID AND \
1742 m.PARTIDMIN <= msv.PARTID AND \
1743 m.PARTIDMAX >= msv.PARTID AND \
1744 msv.PARAMETERID = ? AND \
1745 msv.TIMEVALUE = ? AND \
1746 m.OBJECTID = ? AND \
1747 MEDIAN.MESHPOINT.FEATUREID in \
1748 ( \
1749 SELECT distinct FEATUREID \
1750 FROM MEDIAN.MESHPOINT \
1751 WHERE ? \
1752 ) \
1753 order by msv.TIMEVALUE, \
1754 msv.PARAMETERID, \
1755 MEDIAN.MESHPOINT.JPOSITION, \
1756 MEDIAN.MESHPOINT.IPOSITION, \
1757 MEDIAN.MESHPOINT.KPOSITION
1758
1759 verticalcrosssection_mesh_odv_data = SELECT SI.NAME CRUISE, \
1760 M.MESHID || '-' || MEDIAN.MESHPOINT.IPOSITION || '-' || MEDIAN.MESHPOINT.JPOSITION STATION, \
1761 '*' TYPE, \
1762 ST_ASTEXT(SHAPE), \
1763 0 BOTDEPTH, \
1764 (((ML.UPPERZLOCATION + ML.LOWERZLOCATION) / 2)*-1) DEPTH, \
1765 MSV.TIMEVALUE, \
1766 MSV.DATAVALUE, \
1767 MSV.PARAMETERID PARAMETER, \
1768 MEDIAN.MESHPOINT.JPOSITION, \
1769 MEDIAN.MESHPOINT.IPOSITION, \
1770 MEDIAN.MESHPOINT.KPOSITION, \
1771 2 DATAID , \
1772 '1' QF \
1773 from MEDIAN.MESHLAYER ml, \
1774 MEDIAN.MESHPOINT, \
1775 MEDIAN.MESH m, \
1776 MEDIAN.SOURCEINFO SI, \
1777 MEDIAN.MESHSCALARVALUE msv \
1778 where msv.FEATUREID = MEDIAN.MESHPOINT. FEATUREID AND \
1779 ml.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \
1780 ml.MESHID = MEDIAN.MESHPOINT.MESHID and \
1781 m.MESHID = MEDIAN.MESHPOINT.MESHID AND \
1782 m.PARTIDMIN <= msv.PARTID AND \
1783 m.PARTIDMAX >= msv.PARTID AND \
1784 M.SOURCEID = SI.SOURCEID AND \
1785 msv.PARAMETERID = ? AND \
1786 msv.TIMEVALUE = ? AND \
1787 m.OBJECTID = ? AND \
1788 MEDIAN.MESHPOINT.FEATUREID in \
1789 ( select FEATUREID \
1790 from MEDIAN.MESHPOINT mp, \
1791 MEDIAN.MESH m \
1792 where m.OBJECTID = ? AND \
1793 mp.MESHID = m.MESHID AND \
1794 ? = (select ? from MEDIAN.MESHPOINT where FEATUREID = ?)) \
1795 order by msv.TIMEVALUE, \
1796 MEDIAN.MESHPOINT.JPOSITION, \
1797 MEDIAN.MESHPOINT.IPOSITION, \
1798 MEDIAN.MESHPOINT.KPOSITION, \
1799 msv.PARAMETERID
1800
1801 ##############################################
1802 #### Special case of VerticalCrossSection ####
1803 ##############################################
1804 verticalcrosssection_mesh_parallel_linestring = SELECT ST_ASTEXT(SHAPE), \
1805 IPOSITION, \
1806 JPOSITION \
1807 from MEDIAN.MESHLAYER ml, \
1808 MEDIAN.MESHPOINT, \
1809 MEDIAN.MESH m, \
1810 MEDIAN.MESHSCALARVALUE msv \
1811 where msv.FEATUREID = MEDIAN.MESHPOINT.FEATUREID AND \
1812 ml.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \
1813 ml.MESHID = MEDIAN.MESHPOINT.MESHID and \
1814 m.MESHID = MEDIAN.MESHPOINT.MESHID AND \
1815 m.PARTIDMIN <= msv.PARTID AND \
1816 m.PARTIDMAX >= msv.PARTID AND \
1817 msv.PARAMETERID in (?) AND \
1818 msv.TIMEVALUE in (?) AND \
1819 m.OBJECTID = ? AND \
1820 MEDIAN.MESHPOINT.FEATUREID in \
1821 ( select FEATUREID \
1822 from MEDIAN.MESHPOINT mp, \
1823 MEDIAN.MESH m \
1824 where m.OBJECTID = ? AND \
1825 mp.MESHID = m.MESHID AND \
1826 KPOSITION in ( 1 ) and \
1827 ? = (select ? from MEDIAN.MESHPOINT where FEATUREID = ?)) \
1828 order by MEDIAN.MESHPOINT.JPOSITION, \
1829 MEDIAN.MESHPOINT.IPOSITION
1830
1831 #############################################
1832 #############################################
1833 ########## Horizontalschnitt MESH ###########
1834 #############################################
1835 #############################################
1836 horizontalcrosssection_meshpoint_depth = SELECT DISTINCT MP.KPOSITION KEY, \
1837 'Layer ' || ML.KPOSITION || ': ' || -ML.UPPERZLOCATION || ' - '|| -ML.LOWERZLOCATION VALUE \
1838 from MEDIAN.MESHLAYER ML, \
1839 MEDIAN.MESHPOINT MP, \
1840 MEDIAN.MESH M \
1841 WHERE ML.KPOSITION = MP.KPOSITION AND \
1842 ML.MESHID = MP.MESHID AND \
1843 M.OBJECTID = ? AND \
1844 MP.MESHID = M.MESHID \
1845 ORDER BY MP.KPOSITION
1846
1847 horizontalcrosssection_mesh_year = select distinct \
1848 to_char(msv.TIMEVALUE,'YYYY') KEY, \
1849 to_char(msv.TIMEVALUE,'YYYY') VALUE \
1850 from MEDIAN.MESHSCALARVALUE msv, \
1851 MEDIAN.MESH m \
1852 where m.OBJECTID = ? AND \
1853 msv.PARTID >= m.PARTIDMIN AND \
1854 msv.PARTID <= m.PARTIDMAX AND \
1855 msv.PARAMETERID = ? \
1856 order by to_char(msv.TIMEVALUE,'YYYY')
1857 horizontalcrosssection_mesh_date = select distinct \
1858 msv.TIMEVALUE KEY, \
1859 msv.TIMEVALUE VALUE \
1860 from MEDIAN.MESHSCALARVALUE msv, \
1861 MEDIAN.MESH m \
1862 where m.OBJECTID = ? AND \
1863 msv.PARTID >= m.PARTIDMIN AND \
1864 msv.PARTID <= m.PARTIDMAX AND \
1865 msv.PARAMETERID = ? AND \
1866 to_char(msv.TIMEVALUE,'YYYY') in (?) \
1867 order by msv.TIMEVALUE
1868
1869 horizontalcrosssection_mesh_data = SELECT ST_ASTEXT(SHAPE), \
1870 MSV.DATAVALUE YORDINATE, \
1871 MEDIAN.MESHPOINT.JPOSITION, \
1872 MEDIAN.MESHPOINT.IPOSITION, \
1873 MEDIAN.MESHPOINT.KPOSITION, \
1874 MSV.PARAMETERID, \
1875 MSV.TIMEVALUE, \
1876 2 DATAID \
1877 from MEDIAN.MESHLAYER ML, \
1878 MEDIAN.MESHPOINT, \
1879 MEDIAN.MESH M, \
1880 MEDIAN.MESHSCALARVALUE MSV \
1881 where MSV.FEATUREID = MEDIAN.MESHPOINT.FEATUREID AND \
1882 ML.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \
1883 ML.MESHID = MEDIAN.MESHPOINT.MESHID and \
1884 M.MESHID = MEDIAN.MESHPOINT.MESHID AND \
1885 M.PARTIDMIN <= MSV.PARTID AND \
1886 M.PARTIDMAX >= MSV.PARTID AND \
1887 MSV.PARAMETERID = ? AND \
1888 MSV.TIMEVALUE = ? AND \
1889 M.OBJECTID = ? AND \
1890 MEDIAN.MESHPOINT.KPOSITION = ? \
1891 order by MEDIAN.MESHPOINT.JPOSITION, \
1892 MEDIAN.MESHPOINT.IPOSITION
1893 horizontalcrosssection_mesh_odv_data = SELECT SI.NAME CRUISE, \
1894 M.MESHID || '-' || MEDIAN.MESHPOINT.IPOSITION || '-' || MEDIAN.MESHPOINT.JPOSITION STATION, \
1895 '*' TYPE, \
1896 ST_ASTEXT(SHAPE), \
1897 0 BOTDEPTH, \
1898 (((ML.UPPERZLOCATION + ML.LOWERZLOCATION) / 2)*-1) DEPTH, \
1899 MSV.TIMEVALUE, \
1900 MSV.DATAVALUE, \
1901 MSV.PARAMETERID PARAMETER, \
1902 MEDIAN.MESHPOINT.JPOSITION, \
1903 MEDIAN.MESHPOINT.IPOSITION, \
1904 MEDIAN.MESHPOINT.KPOSITION \
1905 from MEDIAN.MESHLAYER ML, \
1906 MEDIAN.MESHPOINT, \
1907 MEDIAN.MESH M, \
1908 MEDIAN.SOURCEINFO SI, \
1909 MEDIAN.MESHSCALARVALUE MSV \
1910 where MSV.FEATUREID = MEDIAN.MESHPOINT.FEATUREID AND \
1911 ML.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \
1912 ML.MESHID = MEDIAN.MESHPOINT.MESHID and \
1913 M.MESHID = MEDIAN.MESHPOINT.MESHID AND \
1914 M.PARTIDMIN <= MSV.PARTID AND \
1915 M.PARTIDMAX >= MSV.PARTID AND \
1916 M.SOURCEID = SI.SOURCEID AND \
1917 MSV.PARAMETERID = ? AND \
1918 MSV.TIMEVALUE = ? AND \
1919 M.OBJECTID = ? AND \
1920 MEDIAN.MESHPOINT.KPOSITION = ? \
1921 order by MEDIAN.MESHPOINT.JPOSITION, \
1922 MEDIAN.MESHPOINT.IPOSITION, \
1923 MSV.PARAMETERID
1924
1925 area_filter = SELECT DISTINCT \
1926 FEATUREID KEY ,\
1927 DESCRIPTION VALUE \
1928 FROM MEDIAN.FEATUREAREA \
1929 WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \
1930 FEATUREID > 0 \
1931 ORDER BY FEATUREID
1932
1933 subarea_filter = SELECT \
1934 FEATURECODE KEY, \
1935 NAME VALUE \
1936 FROM MEDIAN.FEATUREAREA \
1937 WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \
1938 FEATUREID = ? \
1939 ORDER BY NAME
1940
1941 rasterQuery = SELECT ST_ASTEXT(RASTER) \
1942 FROM MEDIAN.TOPO_WORLD_2MIN \
1943 WHERE INTERSECTS(RASTER, "?")
1944
1945 mapviewer_interface_fis_region = SELECT ID_FIS \
1946 FROM MEDIAN.FEATUREAREA, \
1947 MEDIAN.FIS_HAS_REGION FHR \
1948 WHERE FHR.FEATUREID = MEDIAN.FEATUREAREA.FEATUREID AND \
1949 FHR.FEATURETYPE = MEDIAN.FEATUREAREA.FEATURETYPE AND \
1950 FHR.FEATURECODE = MEDIAN.FEATUREAREA.FEATURECODE AND \
1951 INTERSECTS(SHAPE,"?")
1952
1953 mapviewer_interface_mapservices_has_fis = SELECT DISTINCT ID_FIS, \
1954 ID_MAPSERVICE \
1955 FROM MEDIAN.FIS_HAS_MAPSERVICE \
1956 WHERE ID_MAPSERVICE IN (?)
1957
1958 mapviewer_interface_mapservices_has_parameter = SELECT DISTINCT ID_PARAMETER \
1959 FROM MEDIAN.MAPSERVICE_HAS_PARAMETER \
1960 WHERE ID_MAPSERVICE = ?
1961
1962 mapviewer_interface_mapservices_has_parameter_using_layer = SELECT DISTINCT ID_PARAMETER \
1963 FROM MEDIAN.LAYER_HAS_PARAMETER \
1964 WHERE ID_MAPSERVICE = ? AND \
1965 ID_LAYER IN (?)
1966
1967 #############################################
1968 #############################################
1969 # Layer Contis, Nauthis and Marinefeatures #
1970 #############################################
1971 #############################################
1972
1973 layer = SELECT ROW_ID KEY, \
1974 TITLE || '-' || LAYER_NAME || '-' || BAND VALUE \
1975 FROM MEDIAN.LAYER_HAS_SUBTYPES \
1976 WHERE ID_FEATURECLASS LIKE ? AND \
1977 ITEMS > 0 \
1978 ORDER BY LAYER_NAME
1979
1980 layer_request_data = SELECT ID_FEATURECLASS, \
1981 QUERY_STRING, \
1982 ID_MAPSERVICE || '_' ||ID_LAYER, \
1983 TITLE || '-' || LAYER_NAME || '-' || BAND VALUE \
1984 FROM MEDIAN.LAYER_HAS_SUBTYPES \
1985 WHERE ROW_ID IN (?)
1986
1987 layer_data = SELECT ST_ASTEXT(SHAPE), ? \
1988 FROM ? \
1989 WHERE ?
1990
1991 layer_data_with_geom = SELECT ST_ASTEXT(SHAPE), ? \
1992 FROM ? \
1993 WHERE ? AND \
1994 INTERSECTS(SHAPE,"?")
1995
1996 geometry_for_subareafilter=SELECT st_astext(SHAPE) \
1997 FROM MEDIAN.FEATUREAREA \
1998 WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \
1999 FEATURECODE = ?
2000
2001 layer_colums= SELECT OWNER || '.' || TABLE_NAME || '.' || COLUMN_NAME \
2002 FROM SDE.COLUMN_REGISTRY \
2003 WHERE COLUMN_NAME NOT LIKE 'SHAPE' AND \
2004 OWNER = '?' AND \
2005 TABLE_NAME = '?'
2006
2007 geometry_type = select geometry_type \
2008 from sde.geometry_columns \
2009 where f_table_schema = '?' and \
2010 f_table_name = '?' and \
2011 f_geometry_column='SHAPE'
2012
2013
2014 #############################################
2015 #############################################
2016 ###### Sync of cache after DB updates #######
2017 #############################################
2018 #############################################
2019 updated_tables = SELECT FULLTABLENAME \
2020 FROM MEDIAN.LASTUPDATED \
2021 WHERE LASTUPDATE >= to_date('?', 'YYYY.MM.DD HH24:MI:SS')
http://dive4elements.wald.intevation.org