Mercurial > dive4elements > gnv-client

comparison gnv-artifacts/doc/conf/queries.properties @ 540:80630520e25a

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
merged gnv-artifacts/0.4
author Thomas Arendsen Hein <thomas@intevation.de>
date Fri, 28 Sep 2012 12:13:49 +0200
parents d2f97cb2ac32
children 938ce81a6bd0
comparison
equal deleted inserted replaced
415:9f4a0b990d27 540:80630520e25a
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
265 verticalprofile_year=select distinct \
266 to_char(tsv.TIMEVALUE,'YYYY') KEY, \
267 to_char(tsv.TIMEVALUE,'YYYY') VALUE \
268 from MEDIAN.TIMESERIES ts, \
269 MEDIAN.TIMESERIESVALUE tsv, \
270 MEDIAN.MEASUREMENT m \
271 where ts.TIMESERIESID = tsv.TIMESERIESID and \
272 m.MEASUREMENTID = tsv.MEASUREMENTID and \
273 m.FEATUREID = ? and \
274 ts.PARAMETERID IN ( ? ) \
275 order by to_char(tsv.TIMEVALUE,'YYYY')
276
277 verticalprofile_date=select distinct \
278 tsv.TIMEVALUE KEY, \
279 tsv.TIMEVALUE VALUE \
280 from MEDIAN.TIMESERIES ts, \
281 MEDIAN.TIMESERIESVALUE tsv, \
282 MEDIAN.MEASUREMENT m \
283 where ts.TIMESERIESID = tsv.TIMESERIESID and \
284 m.MEASUREMENTID = tsv.MEASUREMENTID and \
285 m.FEATUREID = ? and \
286 ts.PARAMETERID IN ( ? ) and \
287 to_char(tsv.TIMEVALUE,'YYYY') IN (?) \
288 order by tsv.TIMEVALUE
289
290 verticalprofile_chart_data= SELECT M.ZLOCATION XORDINATE, \
291 TSV.DATAVALUE YORDINATE, \
292 TS.PARAMETERID GROUP1, \
293 TSV.TIMEVALUE GROUP2, \
294 1 GROUP3, \
295 1 DATAID \
296 from MEDIAN.TIMESERIES TS, \
297 MEDIAN.TIMESERIESVALUE TSV, \
298 MEDIAN.MEASUREMENT M \
299 where TS.TIMESERIESID = TSV.TIMESERIESID AND \
300 M.MEASUREMENTID = TSV.MEASUREMENTID AND \
301 M.FEATUREID = ? AND \
302 TS.PARAMETERID IN ( ? ) AND \
303 TSV.TIMEVALUE IN (?) AND \
304 M.ZLOCATION >= ? AND \
305 M.ZLOCATION <= ? \
306 ORDER BY TSV.TIMEVALUE, \
307 TSV.TIMESERIESID, \
308 TS.PARAMETERID, \
309 TSV.TIMEVALUE
310
311 verticalprofile_odv_data = SELECT SI.NAME CRUISE, \
312 MEDIAN.TIMESERIESPOINT.NAME STATION, \
313 '*' TYPE, \
314 ST_ASTEXT(SHAPE), \
315 0 BOTDEPTH, \
316 (M.ZLOCATION *-1) DEPTH, \
317 TSV.TIMEVALUE, \
318 TSV.DATAVALUE, \
319 TS.PARAMETERID PARAMETER, \
320 TSV.MEASUREMENTID, \
321 TSV.TIMESERIESID \
322 FROM MEDIAN.TIMESERIESPOINT, \
323 MEDIAN.TIMESERIESVALUE TSV, \
324 MEDIAN.TIMESERIES TS, \
325 MEDIAN.MEASUREMENT M, \
326 MEDIAN.SOURCEINFO SI \
327 WHERE SI.SOURCEID = M.SOURCEID AND \
328 M.FEATUREID = MEDIAN.TIMESERIESPOINT.FEATUREID AND \
329 M.MEASUREMENTID = TSV.MEASUREMENTID AND \
330 TSV.TIMESERIESID = TS.TIMESERIESID AND \
331 M.FEATUREID = ? AND \
332 TS.PARAMETERID IN ( ? ) AND \
333 TSV.TIMEVALUE IN (?) AND \
334 M.ZLOCATION >= ? AND \
335 M.ZLOCATION <= ? \
336 ORDER BY TSV.MEASUREMENTID, \
337 TSV.TIMESERIESID, \
338 TS.PARAMETERID, \
339 TSV.TIMEVALUE
340
341 #############################################
342 #############################################
343 ########## Vertikalprofil Mesh ##############
344 #############################################
345 #############################################
346
347 verticalprofile_mesh = SELECT OBJECTID KEY, \
348 m.NAME VALUE \
349 FROM MEDIAN.MESH m \
350 WHERE SOURCEID IN (?) \
351 order by m.NAME
352
353 verticalprofile_mesh_with_area = SELECT M.OBJECTID KEY, \
354 M.NAME VALUE \
355 FROM MEDIAN.MESHPOINT, \
356 MEDIAN.MESH M \
357 WHERE M.SOURCEID IN (?) AND \
358 M.MESHID = MEDIAN.MESHPOINT.MESHID AND \
359 INTERSECTS(SHAPE, \
360 (SELECT st_astext(SHAPE) \
361 FROM MEDIAN.FEATUREAREA \
362 WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \
363 FEATURECODE = ? )\
364 ) \
365 order by M.NAME
366
367 verticalprofile_mesh_point = SELECT FEATUREID, \
368 ST_ASTEXT(SHAPE) \
369 FROM MEDIAN.MESHPOINT \
370 WHERE MESHID in \
371 (SELECT DISTINCT MESHID \
372 FROM MEDIAN.MESH \
373 WHERE OBJECTID = ?) AND \
374 KPOSITION = 1 AND \
375 INTERSECTS(SHAPE,"?")
376
377 verticalprofile_mesh_mindepth = SELECT MP.KPOSITION KEY, \
378 'Layer ' || ML.KPOSITION || ': ' || -ML.UPPERZLOCATION || ' - '|| -ML.LOWERZLOCATION VALUE \
379 from MEDIAN.MESHLAYER ML, \
380 MEDIAN.MESHPOINT MP, \
381 MEDIAN.MESH M \
382 WHERE ML.KPOSITION = MP.KPOSITION AND \
383 ML.MESHID = MP.MESHID AND \
384 M.OBJECTID = ? AND \
385 MP.MESHID = M.MESHID AND \
386 IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) AND \
387 JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) \
388 ORDER BY ML.UPPERZLOCATION DESC
389
390 verticalprofile_mesh_maxdepth = SELECT MP.KPOSITION KEY, \
391 'Layer ' || ML.KPOSITION || ': ' || -ML.UPPERZLOCATION || ' - '|| -ML.LOWERZLOCATION VALUE \
392 from MEDIAN.MESHLAYER ML, \
393 MEDIAN.MESHPOINT MP, \
394 MEDIAN.MESH M \
395 WHERE ML.KPOSITION = MP.KPOSITION AND \
396 ML.MESHID = MP.MESHID AND \
397 M.OBJECTID = ? AND \
398 MP.MESHID = M.MESHID AND \
399 IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) AND \
400 JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) AND \
401 MP.KPOSITION < ? \
402 ORDER BY ML.UPPERZLOCATION DESC
403
404 verticalprofile_mesh_parameter=SELECT distinct \
405 p.PARAMETERID KEY, \
406 p.GERMANNAME || ' ['|| p.UNIT ||']' VALUE, \
407 p.GERMANNAME \
408 from MEDIAN.PARAMETER p, \
409 MEDIAN.MESHSCALARVALUE msc, \
410 MEDIAN.MESH m \
411 where m.OBJECTID = ? AND \
412 m.PARTIDMIN = msc.PARTID AND \
413 msc.PARAMETERID = p.PARAMETERID \
414 order by p.GERMANNAME
415
416 verticalprofile_mesh_year= select distinct \
417 to_char(msv.TIMEVALUE,'YYYY') KEY, \
418 to_char(msv.TIMEVALUE,'YYYY') VALUE \
419 from MEDIAN.MESHSCALARVALUE msv, \
420 MEDIAN.MESH m \
421 where m.OBJECTID = ? AND \
422 msv.PARTID >= m.PARTIDMIN AND \
423 msv.PARTID <= m.PARTIDMAX AND \
424 msv.PARAMETERID in (?) \
425 order by to_char(msv.TIMEVALUE, 'YYYY')
426
427 verticalprofile_mesh_date= select distinct \
428 msv.TIMEVALUE KEY, \
429 msv.TIMEVALUE VALUE \
430 from MEDIAN.MESHSCALARVALUE msv, \
431 MEDIAN.MESH m \
432 where m.OBJECTID = ? AND \
433 msv.PARTID >= m.PARTIDMIN AND \
434 msv.PARTID <= m.PARTIDMAX AND \
435 msv.PARAMETERID in (?) AND \
436 to_char(msv.TIMEVALUE,'YYYY') in (?) \
437 order by msv.TIMEVALUE
438
439 verticalprofile_mesh_chart_data=select ml.UPPERZLOCATION XORDINATE, \
440 msv.DATAVALUE YORDINATE, \
441 mp.KPOSITION KPOSITION, \
442 msv.PARAMETERID GROUP1, \
443 msv.TIMEVALUE GROUP2, \
444 1 GROUP3, \
445 2 DATAID \
446 from MEDIAN.MESHLAYER ml, \
447 MEDIAN.MESHPOINT mp, \
448 MEDIAN.MESH m, \
449 MEDIAN.MESHSCALARVALUE msv \
450 where msv.FEATUREID = mp. FEATUREID AND \
451 ml.KPOSITION = mp.KPOSITION and \
452 ml.MESHID = mp.MESHID and \
453 m.MESHID = mp.MESHID AND \
454 m.PARTIDMIN <= msv.PARTID AND \
455 m.PARTIDMAX >= msv.PARTID AND \
456 msv.PARAMETERID in (?) AND \
457 msv.TIMEVALUE in (?) AND \
458 m.OBJECTID = ? AND \
459 mp.FEATUREID in \
460 ( select FEATUREID \
461 from MEDIAN.MESHPOINT mp, \
462 MEDIAN.MESH m \
463 where m.OBJECTID = ? AND \
464 mp.MESHID = m.MESHID AND \
465 IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) and \
466 JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?)) AND \
467 mp.KPOSITION <= ? AND \
468 mp.KPOSITION >= ? \
469 order by msv.PARAMETERID, \
470 msv.TIMEVALUE, \
471 ml.UPPERZLOCATION
472
473 verticalprofile_mesh_odv_data=select SI.NAME CRUISE, \
474 m.MESHID || '-' || MEDIAN.MESHPOINT.IPOSITION || '-' || MEDIAN.MESHPOINT.JPOSITION STATION, \
475 '*' TYPE, \
476 ST_ASTEXT(SHAPE), \
477 0 BOTDEPTH, \
478 (((ML.UPPERZLOCATION + ML.LOWERZLOCATION) / 2)*-1) DEPTH, \
479 msv.DATAVALUE, \
480 msv.PARAMETERID PARAMETER, \
481 msv.TIMEVALUE \
482 from MEDIAN.MESHLAYER ml, \
483 MEDIAN.MESHPOINT, \
484 MEDIAN.MESH m, \
485 MEDIAN.MESHSCALARVALUE msv, \
486 MEDIAN.SOURCEINFO SI \
487 where m.SOURCEID = SI.SOURCEID AND \
488 msv.FEATUREID = MEDIAN.MESHPOINT. FEATUREID AND \
489 ml.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \
490 ml.MESHID = MEDIAN.MESHPOINT.MESHID and \
491 m.MESHID = MEDIAN.MESHPOINT.MESHID AND \
492 m.PARTIDMIN <= msv.PARTID AND \
493 m.PARTIDMAX >= msv.PARTID AND \
494 msv.PARAMETERID in (?) AND \
495 msv.TIMEVALUE in (?) AND \
496 m.OBJECTID = ? AND \
497 MEDIAN.MESHPOINT.FEATUREID in \
498 ( select FEATUREID \
499 from MEDIAN.MESHPOINT mp, \
500 MEDIAN.MESH m \
501 where m.OBJECTID = ? AND \
502 mp.MESHID = m.MESHID AND \
503 IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) and \
504 JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?)) AND \
505 MEDIAN.MESHPOINT.KPOSITION <= ? AND \
506 MEDIAN.MESHPOINT.KPOSITION >= ? \
507 order by msv.PARAMETERID, \
508 msv.TIMEVALUE, \
509 ml.UPPERZLOCATION
510
511 #############################################
512 #############################################
513 ##### Vertikalprofil InstantaneousPoint #####
514 #############################################
515 #############################################
516
517 verticalprofile_instantaneouspoint_series = SELECT DISTINCT \
518 S.SERIESID KEY, \
519 S.DESCRIPTION VALUE \
520 FROM MEDIAN.SERIES S, \
521 MEDIAN.INSTANTANEOUSPOINT I, \
522 MEDIAN.MEASUREMENT M \
523 WHERE S.SERIESID = I.SERIESID AND \
524 I.POINTSPEC = 4 AND \
525 I.FEATUREID = M.FEATUREID AND \
526 M.SOURCEID= ? \
527 ORDER BY S.DESCRIPTION
528
529 verticalprofile_instantaneouspoint_series_with_area = SELECT \
530 S.SERIESID KEY, \
531 S.DESCRIPTION VALUE \
532 FROM MEDIAN.INSTANTANEOUSPOINT, \
533 MEDIAN.SERIES S, \
534 MEDIAN.MEASUREMENT M \
535 WHERE S.SERIESID = MEDIAN.INSTANTANEOUSPOINT.SERIESID AND \
536 MEDIAN.INSTANTANEOUSPOINT.POINTSPEC = 4 AND \
537 MEDIAN.INSTANTANEOUSPOINT.FEATUREID = M.FEATUREID AND \
538 M.SOURCEID= ? AND \
539 INTERSECTS(SHAPE, \
540 (SELECT st_astext(SHAPE) \
541 FROM MEDIAN.FEATUREAREA \
542 WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \
543 FEATURECODE = ? )\
544 ) \
545 ORDER BY S.DESCRIPTION
546
547 verticalprofile_instantaneouspoint_point = SELECT \
548 MEDIAN.INSTANTANEOUSPOINT.FEATUREID, \
549 to_char(MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE,'DD.MM.YYYY HH24:MI') VALUE, \
550 MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE, \
551 ST_ASTEXT(SHAPE) \
552 FROM MEDIAN.INSTANTANEOUSPOINT, \
553 MEDIAN.MEASUREMENT M \
554 WHERE MEDIAN.INSTANTANEOUSPOINT.SERIESID = ? AND \
555 MEDIAN.INSTANTANEOUSPOINT.POINTSPEC = 4 AND \
556 MEDIAN.INSTANTANEOUSPOINT.FEATUREID = M.FEATUREID AND \
557 M.SOURCEID = ? \
558 ORDER BY MEDIAN.INSTANTANEOUSPOINT.FEATUREID, \
559 MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE
560
561 verticalprofile_instantaneouspoint_parameter = SELECT DISTINCT \
562 P.PARAMETERID KEY, \
563 p.GERMANNAME || ' ['|| p.UNIT ||']' VALUE, \
564 p.GERMANNAME \
565 FROM MEDIAN.PARAMETER P, \
566 MEDIAN.INSTANTANEOUSPOINT IP, \
567 MEDIAN.MEASUREMENT M, \
568 MEDIAN.MEASUREDSCALARVALUE MSV \
569 WHERE IP.FEATUREID = M.FEATUREID AND \
570 M.MEASUREMENTID = MSV.MEASUREMENTID AND \
571 MSV.PARAMETERID = P.PARAMETERID AND \
572 IP.FEATUREID = ? \
573 ORDER BY P.GERMANNAME
574
575 verticalprofile_instantaneouspoint_minmaxdepth = SELECT min(M.ZLOCATION) MIN, \
576 max(M.ZLOCATION) MAX \
577 FROM MEDIAN.INSTANTANEOUSPOINT IP, \
578 MEDIAN.MEASUREMENT M, \
579 MEDIAN.MEASUREDSCALARVALUE MSV \
580 WHERE IP.FEATUREID = M.FEATUREID AND \
581 M.MEASUREMENTID = MSV.MEASUREMENTID AND \
582 IP.FEATUREID = ? AND \
583 MSV.PARAMETERID in (?)
584
585 verticalprofile_instantaneouspoint_chart_data = SELECT M.ZLOCATION XORDINATE, \
586 MSV.DATAVALUE YORDINATE, \
587 MSV.PARAMETERID GROUP1, \
588 IP.TIMEVALUE GROUP2, \
589 1 GROUP3, \
590 3 DATAID \
591 FROM MEDIAN.INSTANTANEOUSPOINT IP, \
592 MEDIAN.MEASUREMENT M, \
593 MEDIAN.MEASUREDSCALARVALUE MSV \
594 WHERE IP.FEATUREID = M.FEATUREID AND \
595 M.MEASUREMENTID = MSV.MEASUREMENTID AND \
596 IP.FEATUREID = ? AND \
597 MSV.PARAMETERID in (?) AND \
598 M.ZLOCATION >= ? AND \
599 M.ZLOCATION <= ? \
600 ORDER BY IP.TIMEVALUE, \
601 MSV.PARAMETERID, \
602 M.ZLOCATION
603
604 verticalprofile_instantaneouspoint_odv_data = SELECT S.DESCRIPTION CRUISE, \
605 MEDIAN.INSTANTANEOUSPOINT.FEATUREID STATION, \
606 '*' TYPE, \
607 ST_ASTEXT(SHAPE), \
608 0 BOTDEPTH, \
609 (M.ZLOCATION * -1) DEPTH, \
610 MSV.DATAVALUE, \
611 MSV.PARAMETERID PARAMETER, \
612 MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE \
613 FROM MEDIAN.SERIES S, \
614 MEDIAN.INSTANTANEOUSPOINT, \
615 MEDIAN.MEASUREMENT M, \
616 MEDIAN.MEASUREDSCALARVALUE MSV \
617 WHERE S.SERIESID = MEDIAN.INSTANTANEOUSPOINT.SERIESID AND \
618 MEDIAN.INSTANTANEOUSPOINT.FEATUREID = M.FEATUREID AND \
619 M.MEASUREMENTID = MSV.MEASUREMENTID AND \
620 MEDIAN.INSTANTANEOUSPOINT.FEATUREID = ? AND \
621 MSV.PARAMETERID in (?) AND \
622 M.ZLOCATION >= ? AND \
623 M.ZLOCATION <= ? \
624 ORDER BY MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE, \
625 MSV.PARAMETERID, \
626 M.ZLOCATION
627
628 #############################################
629 #############################################
630 #### Horizontalprofil InstantaneousPoint ####
631 #############################################
632 #############################################
633 horizontalprofile_instantaneouspoint_vehicle = SELECT DISTINCT \
634 V.VEHICLEID KEY, \
635 V.NAME VALUE \
636 FROM MEDIAN.VEHICLE V, \
637 MEDIAN.CRUISE C, \
638 MEDIAN.TRACK T, \
639 MEDIAN.SURVEYINFO S, \
640 MEDIAN.INSTANTANEOUSPOINT I, \
641 MEDIAN.MEASUREMENT M \
642 WHERE V.VEHICLEID = C.VEHICLEID AND \
643 C.CRUISEID = T.CRUISEID AND \
644 T.TRACKID = S.TRACKID AND \
645 S.SURVEYID = I.SURVEYID AND \
646 I.FEATUREID = M.FEATUREID AND \
647 M.SOURCEID = ? \
648 ORDER BY V.NAME
649
650 horizontalprofile_instantaneouspoint_vehicle_with_area = SELECT V.VEHICLEID KEY, \
651 V.NAME VALUE \
652 FROM MEDIAN.TRACK, \
653 MEDIAN.VEHICLE V, \
654 MEDIAN.CRUISE C \
655 WHERE V.VEHICLEID = C.VEHICLEID AND \
656 C.CRUISEID = MEDIAN.TRACK.CRUISEID AND \
657 MEDIAN.TRACK.TRACKID IN \
658 (SELECT DISTINCT S.TRACKID \
659 FROM MEDIAN.MEASUREMENT M ,\
660 MEDIAN.INSTANTANEOUSPOINT I, \
661 MEDIAN.SURVEYINFO S \
662 WHERE M.SOURCEID = ? AND \
663 I.FEATUREID = M.FEATUREID AND \
664 S.SURVEYID = I.SURVEYID)AND \
665 INTERSECTS(SHAPE, \
666 (SELECT st_astext(SHAPE) \
667 FROM MEDIAN.FEATUREAREA \
668 WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \
669 FEATURECODE = ? )\
670 ) \
671 ORDER BY V.NAME
672 horizontalprofile_instantaneouspoint_cruise = SELECT DISTINCT \
673 C.CRUISEID KEY, \
674 V.NAME || ' ' || \
675 C.NAME || ' ' || \
676 TO_CHAR(C.STARTDATE,'DD.MM.YYYY') || ' - ' || \
677 TO_CHAR(C.ENDDATE,'DD.MM.YYYY') VALUE, \
678 V.NAME, \
679 C.NAME \
680 FROM MEDIAN.CRUISE C, \
681 MEDIAN.VEHICLE V, \
682 MEDIAN.TRACK T, \
683 MEDIAN.SURVEYINFO S, \
684 MEDIAN.INSTANTANEOUSPOINT I, \
685 MEDIAN.MEASUREMENT M \
686 WHERE C.VEHICLEID = V.VEHICLEID AND \
687 C.CRUISEID = T.CRUISEID AND \
688 T.TRACKID = S.TRACKID AND \
689 S.SURVEYID = I.SURVEYID AND \
690 I.FEATUREID = M.FEATUREID AND \
691 C.VEHICLEID = ? AND \
692 M.SOURCEID = ? \
693 ORDER BY V.NAME, C.NAME
694
695 horizontalprofile_instantaneouspoint_cruise_with_area = SELECT C.CRUISEID KEY, \
696 V.NAME || ' ' || \
697 C.NAME || ' ' || \
698 TO_CHAR(C.STARTDATE,'DD.MM.YYYY') || ' - ' || \
699 TO_CHAR(C.ENDDATE,'DD.MM.YYYY') VALUE, \
700 V.NAME, \
701 C.NAME \
702 FROM MEDIAN.TRACK, \
703 MEDIAN.VEHICLE V, \
704 MEDIAN.CRUISE C \
705 WHERE V.VEHICLEID = C.VEHICLEID AND \
706 C.CRUISEID = MEDIAN.TRACK.CRUISEID AND \
707 MEDIAN.TRACK.TRACKID IN \
708 (SELECT DISTINCT S.TRACKID \
709 FROM MEDIAN.MEASUREMENT M ,\
710 MEDIAN.INSTANTANEOUSPOINT I, \
711 MEDIAN.SURVEYINFO S \
712 WHERE M.SOURCEID = ? AND \
713 C.VEHICLEID = ? AND \
714 I.FEATUREID = M.FEATUREID AND \
715 S.SURVEYID = I.SURVEYID)AND \
716 INTERSECTS(SHAPE, \
717 (SELECT st_astext(SHAPE) \
718 FROM MEDIAN.FEATUREAREA \
719 WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \
720 FEATURECODE = ? )\
721 ) \
722 ORDER BY V.NAME
723 horizontalprofile_instantaneouspoint_track= SELECT \
724 T.TRACKID KEY, \
725 to_char(T.STARTDATE,'DD.MM.YYYY HH24:MI') || ' - '|| to_char(T.ENDDATE,'DD.MM.YYYY HH24:MI') || ' - '|| T.NAME VALUE \
726 FROM MEDIAN.TRACK T \
727 WHERE T.CRUISEID = ? \
728 ORDER BY T.STARTDATE, \
729 T.ENDDATE, \
730 T.NAME
731
732 horizontalprofile_instantaneouspoint_track_with_area = SELECT \
733 MEDIAN.TRACK.TRACKID KEY, \
734 to_char(STARTDATE,'DD.MM.YYYY HH24:MI') || ' - '|| to_char(ENDDATE,'DD.MM.YYYY HH24:MI') || ' - '|| NAME VALUE, \
735 NAME \
736 FROM MEDIAN.TRACK \
737 WHERE MEDIAN.TRACK.CRUISEID = ? AND \
738 INTERSECTS(SHAPE, \
739 (SELECT st_astext(SHAPE) \
740 FROM MEDIAN.FEATUREAREA \
741 WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \
742 FEATURECODE = ? )\
743 ) \
744 ORDER BY STARTDATE, \
745 ENDDATE, \
746 NAME
747 horizontalprofile_instantaneouspoint_surveyinfo = SELECT \
748 SURVEYID KEY, \
749 to_char(STARTDATE,'DD.MM.YYYY HH24:MI') || ' - '|| to_char(ENDDATE,'DD.MM.YYYY HH24:MI') || ' - '|| DESCRIPTION VALUE \
750 FROM MEDIAN.SURVEYINFO \
751 WHERE TRACKID = ? \
752 ORDER BY STARTDATE, \
753 ENDDATE, \
754 DESCRIPTION
755 horizontalprofile_instantaneouspoint_parameter = SELECT DISTINCT \
756 P.PARAMETERID KEY, \
757 p.GERMANNAME || ' ['|| p.UNIT ||']' VALUE, \
758 p.GERMANNAME \
759 FROM MEDIAN.PARAMETER P, \
760 MEDIAN.MEASUREMENT M, \
761 MEDIAN.INSTANTANEOUSPOINT IP, \
762 MEDIAN.MEASUREDSCALARVALUE MSV \
763 WHERE P.PARAMETERID = MSV.PARAMETERID AND \
764 MSV.MEASUREMENTID = M.MEASUREMENTID AND \
765 M.FEATUREID = IP.FEATUREID AND \
766 IP.SURVEYID = ? \
767 ORDER BY P.GERMANNAME
768 horizontalprofile_instantaneouspoint_depth = SELECT DISTINCT \
769 M.ZLOCATION KEY, \
770 M.ZLOCATION VALUE \
771 FROM MEDIAN.MEASUREMENT M, \
772 MEDIAN.INSTANTANEOUSPOINT IP \
773 WHERE M.FEATUREID = IP.FEATUREID AND \
774 IP.SURVEYID = ? \
775 ORDER BY M.ZLOCATION
776 horizontalprofile_instantaneouspoint_chart_data = SELECT ST_ASTEXT(SHAPE), \
777 MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE TIME, \
778 MSV.DATAVALUE YORDINATE, \
779 MSV.PARAMETERID GROUP1, \
780 ZLOCATION GROUP2, \
781 1 GROUP3, \
782 3 DATAID \
783 FROM MEDIAN.INSTANTANEOUSPOINT, \
784 MEDIAN.MEASUREMENT M, \
785 MEDIAN.MEASUREDSCALARVALUE MSV \
786 WHERE MEDIAN.INSTANTANEOUSPOINT.FEATUREID = M.FEATUREID AND \
787 M.MEASUREMENTID = MSV.MEASUREMENTID AND \
788 MEDIAN.INSTANTANEOUSPOINT.SURVEYID = ? AND \
789 M.ZLOCATION IN (?) AND \
790 MSV.PARAMETERID in (?) \
791 ORDER BY MSV.PARAMETERID, \
792 M.ZLOCATION, \
793 MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE
794 horizontalprofile_instantaneouspoint_odv_data = SELECT V.NAME || ' ' || C.NAME CRUISE, \
795 MEDIAN.InstantaneousPoint.FEATUREID STATION, \
796 '*' TYPE, \
797 0 BOTDEPTH, \
798 (M.ZLOCATION * -1) DEPTH, \
799 ST_ASTEXT(SHAPE), \
800 MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE, \
801 MSV.DATAVALUE, \
802 MSV.PARAMETERID PARAMETER \
803 FROM MEDIAN.INSTANTANEOUSPOINT, \
804 MEDIAN.VEHICLE V, \
805 MEDIAN.CRUISE C, \
806 (SELECT TRACKID,CRUISEID FROM MEDIAN.TRACK) T, \
807 MEDIAN.SURVEYINFO S, \
808 MEDIAN.MEASUREMENT M, \
809 MEDIAN.MEASUREDSCALARVALUE MSV \
810 WHERE V.VEHICLEID = C.VEHICLEID AND \
811 C.CRUISEID = T.CRUISEID AND \
812 T.TRACKID = S.TRACKID AND \
813 S.SURVEYID = MEDIAN.INSTANTANEOUSPOINT.SURVEYID AND \
814 MEDIAN.INSTANTANEOUSPOINT.FEATUREID = M.FEATUREID AND \
815 M.MEASUREMENTID = MSV.MEASUREMENTID AND \
816 MEDIAN.INSTANTANEOUSPOINT.SURVEYID = ? AND \
817 M.ZLOCATION IN (?) AND \
818 MSV.PARAMETERID in (?) \
819 ORDER BY MSV.PARAMETERID, \
820 M.ZLOCATION, \
821 MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE
822
823 #############################################
824 #############################################
825 ########### Horizontalprofil MESH ###########
826 #############################################
827 #############################################
828 horizontalprofile_meshpoint_depth = SELECT DISTINCT \
829 mp.KPOSITION KEY, \
830 'Layer ' || ml.KPOSITION || ': ' || -ml.UPPERZLOCATION || ' - '|| -ml.LOWERZLOCATION VALUE \
831 from MEDIAN.MESHLAYER ml, \
832 MEDIAN.MESHPOINT mp \
833 where ml.KPOSITION = mp.KPOSITION and \
834 ml.MESHID = mp.MESHID and \
835 mp.FEATUREID in \
836 ( select FEATUREID \
837 from MEDIAN.MESHPOINT mp, \
838 MEDIAN.MESH m \
839 where m.OBJECTID = ? AND \
840 mp.MESHID = m.MESHID AND \
841 IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) and \
842 JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?)) \
843 order by mp.KPOSITION
844
845 horizontalprofile_mesh_chart_data = SELECT ST_ASTEXT(SHAPE), \
846 msv.DATAVALUE YORDINATE, \
847 msv.PARAMETERID GROUP1, \
848 MEDIAN.MESHPOINT.KPOSITION GROUP2, \
849 msv.TIMEVALUE GROUP3, \
850 MEDIAN.MESHPOINT.JPOSITION, \
851 MEDIAN.MESHPOINT.IPOSITION, \
852 2 DATAID \
853 from MEDIAN.MESHLAYER ml, \
854 MEDIAN.MESHPOINT, \
855 MEDIAN.MESH m, \
856 MEDIAN.MESHSCALARVALUE msv \
857 where msv.FEATUREID = MEDIAN.MESHPOINT. FEATUREID AND \
858 ml.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \
859 ml.MESHID = MEDIAN.MESHPOINT.MESHID and \
860 m.MESHID = MEDIAN.MESHPOINT.MESHID AND \
861 m.PARTIDMIN <= msv.PARTID AND \
862 m.PARTIDMAX >= msv.PARTID AND \
863 msv.PARAMETERID in (?) AND \
864 msv.TIMEVALUE in (?) AND \
865 m.OBJECTID = ? AND \
866 MEDIAN.MESHPOINT.FEATUREID in \
867 ( select FEATUREID \
868 from MEDIAN.MESHPOINT mp, \
869 MEDIAN.MESH m \
870 where m.OBJECTID = ? AND \
871 mp.MESHID = m.MESHID AND \
872 KPOSITION in ( ? ) and \
873 ? = (select ? from MEDIAN.MESHPOINT where FEATUREID = ?)) \
874 order by msv.TIMEVALUE, \
875 MEDIAN.MESHPOINT.KPOSITION, \
876 msv.PARAMETERID, \
877 MEDIAN.MESHPOINT.JPOSITION, \
878 MEDIAN.MESHPOINT.IPOSITION
879
880 horizontalprofile_mesh_odv_data = SELECT SI.NAME CRUISE, \
881 m.MESHID || '-' || MEDIAN.MESHPOINT.IPOSITION || '-' || MEDIAN.MESHPOINT.JPOSITION STATION, \
882 '*' TYPE, \
883 ST_ASTEXT(SHAPE), \
884 0 BOTDEPTH, \
885 (((ML.UPPERZLOCATION + ML.LOWERZLOCATION) / 2)*-1) DEPTH, \
886 msv.DATAVALUE, \
887 msv.PARAMETERID PARAMETER, \
888 msv.TIMEVALUE, \
889 MEDIAN.MESHPOINT.JPOSITION, \
890 MEDIAN.MESHPOINT.IPOSITION \
891 from MEDIAN.MESHLAYER ml, \
892 MEDIAN.MESHPOINT, \
893 MEDIAN.MESH m, \
894 MEDIAN.MESHSCALARVALUE msv, \
895 MEDIAN.SOURCEINFO SI \
896 where msv.FEATUREID = MEDIAN.MESHPOINT. FEATUREID AND \
897 ml.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \
898 ml.MESHID = MEDIAN.MESHPOINT.MESHID and \
899 m.MESHID = MEDIAN.MESHPOINT.MESHID AND \
900 m.SOURCEID = SI.SOURCEID AND \
901 m.PARTIDMIN <= msv.PARTID AND \
902 m.PARTIDMAX >= msv.PARTID AND \
903 msv.PARAMETERID in (?) AND \
904 msv.TIMEVALUE in (?) AND \
905 m.OBJECTID = ? AND \
906 MEDIAN.MESHPOINT.FEATUREID in \
907 ( select FEATUREID \
908 from MEDIAN.MESHPOINT mp, \
909 MEDIAN.MESH m \
910 where m.OBJECTID = ? AND \
911 mp.MESHID = m.MESHID AND \
912 KPOSITION in ( ? ) and \
913 ? = (select ? from MEDIAN.MESHPOINT where FEATUREID = ?)) \
914 order by msv.TIMEVALUE, \
915 MEDIAN.MESHPOINT.KPOSITION, \
916 msv.PARAMETERID, \
917 MEDIAN.MESHPOINT.JPOSITION, \
918 MEDIAN.MESHPOINT.IPOSITION
919
920 #############################################
921 #############################################
922 ########### Horizontalprofil MESH ###########
923 ########### Schnittprofil ###########
924 #############################################
925 #############################################
926 horizontalprofile_meshpoint_depth = SELECT DISTINCT \
927 mp.KPOSITION KEY, \
928 'Layer ' || ml.KPOSITION || ': ' || -ml.UPPERZLOCATION || ' - '|| -ml.LOWERZLOCATION VALUE \
929 from MEDIAN.MESHLAYER ml, \
930 MEDIAN.MESHPOINT mp \
931 where ml.KPOSITION = mp.KPOSITION and \
932 ml.MESHID = mp.MESHID and \
933 mp.FEATUREID in \
934 ( select FEATUREID \
935 from MEDIAN.MESHPOINT mp, \
936 MEDIAN.MESH m \
937 where m.OBJECTID = ? AND \
938 mp.MESHID = m.MESHID )\
939 order by mp.KPOSITION
940
941 horizontalprofile_meshpoint_cross_ij=SELECT MEDIAN.MESHFACE.JPOSITION, \
942 MEDIAN.MESHFACE.IPOSITION \
943 FROM MEDIAN.MESHFACE, \
944 MEDIAN.MESH M \
945 WHERE MEDIAN.MESHFACE.KPOSITION = 1 AND \
946 M.MESHID = MEDIAN.MESHFACE.MESHID AND \
947 M.OBJECTID = ? AND \
948 INTERSECTS(SHAPE, "?")
949 horizontalprofile_mesh_cross_chart_data = SELECT ST_ASTEXT(SHAPE), \
950 msv.DATAVALUE YORDINATE, \
951 msv.PARAMETERID GROUP1, \
952 MEDIAN.MESHPOINT.KPOSITION GROUP2, \
953 msv.TIMEVALUE GROUP3, \
954 MEDIAN.MESHPOINT.JPOSITION, \
955 MEDIAN.MESHPOINT.IPOSITION, \
956 2 DATAID \
957 from MEDIAN.MESHLAYER ml, \
958 MEDIAN.MESHPOINT, \
959 MEDIAN.MESH m, \
960 MEDIAN.MESHSCALARVALUE msv \
961 where msv.FEATUREID = MEDIAN.MESHPOINT.FEATUREID AND \
962 ml.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \
963 ml.MESHID = MEDIAN.MESHPOINT.MESHID and \
964 m.MESHID = MEDIAN.MESHPOINT.MESHID AND \
965 m.PARTIDMIN <= msv.PARTID AND \
966 m.PARTIDMAX >= msv.PARTID AND \
967 msv.PARAMETERID in (?) AND \
968 msv.TIMEVALUE in (?) AND \
969 m.OBJECTID = ? AND \
970 ml.KPOSITION in (?) AND \
971 MEDIAN.MESHPOINT.FEATUREID in \
972 ( \
973 SELECT distinct FEATUREID \
974 FROM MEDIAN.MESHPOINT \
975 WHERE ? \
976 ) \
977 order by msv.TIMEVALUE, \
978 MEDIAN.MESHPOINT.KPOSITION, \
979 msv.PARAMETERID, \
980 MEDIAN.MESHPOINT.JPOSITION, \
981 MEDIAN.MESHPOINT.IPOSITION
982 #############################################
983 #############################################
984 ############# Profilschnitt MESH ############
985 #############################################
986 #############################################
987 verticalcrosssection_mesh_year = select distinct \
988 to_char(msv.TIMEVALUE,'YYYY') KEY, \
989 to_char(msv.TIMEVALUE,'YYYY') VALUE \
990 from MEDIAN.MESHSCALARVALUE msv, \
991 MEDIAN.MESH m \
992 where m.OBJECTID = ? AND \
993 msv.PARTID >= m.PARTIDMIN AND \
994 msv.PARTID <= m.PARTIDMAX AND \
995 msv.PARAMETERID = ? \
996 order by to_char(msv.TIMEVALUE,'YYYY')
997 verticalcrosssection_mesh_date = select distinct \
998 msv.TIMEVALUE KEY, \
999 msv.TIMEVALUE VALUE \
1000 from MEDIAN.MESHSCALARVALUE msv, \
1001 MEDIAN.MESH m \
1002 where m.OBJECTID = ? AND \
1003 msv.PARTID >= m.PARTIDMIN AND \
1004 msv.PARTID <= m.PARTIDMAX AND \
1005 msv.PARAMETERID = ? AND \
1006 to_char(msv.TIMEVALUE,'YYYY') in (?) \
1007 order by msv.TIMEVALUE
1008 verticalcrosssection_mesh_chart_data = SELECT ST_ASTEXT(SHAPE), \
1009 ((ml.UPPERZLOCATION + ml.LOWERZLOCATION) / 2) Z, \
1010 msv.DATAVALUE YORDINATE, \
1011 msv.PARAMETERID GROUP1, \
1012 msv.TIMEVALUE GROUP2, \
1013 MEDIAN.MESHPOINT.JPOSITION, \
1014 MEDIAN.MESHPOINT.IPOSITION, \
1015 MEDIAN.MESHPOINT.KPOSITION \
1016 from MEDIAN.MESHLAYER ml, \
1017 MEDIAN.MESHPOINT, \
1018 MEDIAN.MESH m, \
1019 MEDIAN.MESHSCALARVALUE msv \
1020 where msv.FEATUREID = MEDIAN.MESHPOINT. FEATUREID AND \
1021 ml.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \
1022 ml.MESHID = MEDIAN.MESHPOINT.MESHID and \
1023 m.MESHID = MEDIAN.MESHPOINT.MESHID AND \
1024 m.PARTIDMIN <= msv.PARTID AND \
1025 m.PARTIDMAX >= msv.PARTID AND \
1026 msv.PARAMETERID = ? AND \
1027 msv.TIMEVALUE = ? AND \
1028 m.OBJECTID = ? AND \
1029 MEDIAN.MESHPOINT.FEATUREID in \
1030 ( \
1031 SELECT distinct FEATUREID \
1032 FROM MEDIAN.MESHPOINT \
1033 WHERE ? \
1034 ) \
1035 order by msv.TIMEVALUE, \
1036 msv.PARAMETERID, \
1037 MEDIAN.MESHPOINT.JPOSITION, \
1038 MEDIAN.MESHPOINT.IPOSITION, \
1039 MEDIAN.MESHPOINT.KPOSITION
1040
1041 verticalcrosssection_mesh_odv_data = SELECT SI.NAME CRUISE, \
1042 M.MESHID || '-' || MEDIAN.MESHPOINT.IPOSITION || '-' || MEDIAN.MESHPOINT.JPOSITION STATION, \
1043 '*' TYPE, \
1044 ST_ASTEXT(SHAPE), \
1045 0 BOTDEPTH, \
1046 (((ML.UPPERZLOCATION + ML.LOWERZLOCATION) / 2)*-1) DEPTH, \
1047 MSV.TIMEVALUE, \
1048 MSV.DATAVALUE, \
1049 MSV.PARAMETERID PARAMETER, \
1050 MEDIAN.MESHPOINT.JPOSITION, \
1051 MEDIAN.MESHPOINT.IPOSITION, \
1052 MEDIAN.MESHPOINT.KPOSITION, \
1053 2 DATAID \
1054 from MEDIAN.MESHLAYER ml, \
1055 MEDIAN.MESHPOINT, \
1056 MEDIAN.MESH m, \
1057 MEDIAN.SOURCEINFO SI, \
1058 MEDIAN.MESHSCALARVALUE msv \
1059 where msv.FEATUREID = MEDIAN.MESHPOINT. FEATUREID AND \
1060 ml.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \
1061 ml.MESHID = MEDIAN.MESHPOINT.MESHID and \
1062 m.MESHID = MEDIAN.MESHPOINT.MESHID AND \
1063 m.PARTIDMIN <= msv.PARTID AND \
1064 m.PARTIDMAX >= msv.PARTID AND \
1065 M.SOURCEID = SI.SOURCEID AND \
1066 msv.PARAMETERID = ? AND \
1067 msv.TIMEVALUE = ? AND \
1068 m.OBJECTID = ? AND \
1069 MEDIAN.MESHPOINT.FEATUREID in \
1070 ( select FEATUREID \
1071 from MEDIAN.MESHPOINT mp, \
1072 MEDIAN.MESH m \
1073 where m.OBJECTID = ? AND \
1074 mp.MESHID = m.MESHID AND \
1075 ? = (select ? from MEDIAN.MESHPOINT where FEATUREID = ?)) \
1076 order by msv.TIMEVALUE, \
1077 msv.PARAMETERID, \
1078 MEDIAN.MESHPOINT.JPOSITION, \
1079 MEDIAN.MESHPOINT.IPOSITION, \
1080 MEDIAN.MESHPOINT.KPOSITION
1081
1082 #############################################
1083 #############################################
1084 ########## Horizontalschnitt MESH ###########
1085 #############################################
1086 #############################################
1087 horizontalcrosssection_meshpoint_depth = SELECT DISTINCT MP.KPOSITION KEY, \
1088 'Layer ' || ML.KPOSITION || ': ' || -ML.UPPERZLOCATION || ' - '|| -ML.LOWERZLOCATION VALUE \
1089 from MEDIAN.MESHLAYER ML, \
1090 MEDIAN.MESHPOINT MP, \
1091 MEDIAN.MESH M \
1092 WHERE ML.KPOSITION = MP.KPOSITION AND \
1093 ML.MESHID = MP.MESHID AND \
1094 M.OBJECTID = ? AND \
1095 MP.MESHID = M.MESHID \
1096 ORDER BY MP.KPOSITION
1097
1098 horizontalcrosssection_mesh_year = select distinct \
1099 to_char(msv.TIMEVALUE,'YYYY') KEY, \
1100 to_char(msv.TIMEVALUE,'YYYY') VALUE \
1101 from MEDIAN.MESHSCALARVALUE msv, \
1102 MEDIAN.MESH m \
1103 where m.OBJECTID = ? AND \
1104 msv.PARTID >= m.PARTIDMIN AND \
1105 msv.PARTID <= m.PARTIDMAX AND \
1106 msv.PARAMETERID = ? \
1107 order by to_char(msv.TIMEVALUE,'YYYY')
1108 horizontalcrosssection_mesh_date = select distinct \
1109 msv.TIMEVALUE KEY, \
1110 msv.TIMEVALUE VALUE \
1111 from MEDIAN.MESHSCALARVALUE msv, \
1112 MEDIAN.MESH m \
1113 where m.OBJECTID = ? AND \
1114 msv.PARTID >= m.PARTIDMIN AND \
1115 msv.PARTID <= m.PARTIDMAX AND \
1116 msv.PARAMETERID = ? AND \
1117 to_char(msv.TIMEVALUE,'YYYY') in (?) \
1118 order by msv.TIMEVALUE
1119
1120 horizontalcrosssection_mesh_data = SELECT ST_ASTEXT(SHAPE), \
1121 MSV.DATAVALUE YORDINATE, \
1122 MEDIAN.MESHPOINT.JPOSITION, \
1123 MEDIAN.MESHPOINT.IPOSITION, \
1124 MEDIAN.MESHPOINT.KPOSITION, \
1125 MSV.PARAMETERID, \
1126 MSV.TIMEVALUE, \
1127 2 DATAID \
1128 from MEDIAN.MESHLAYER ML, \
1129 MEDIAN.MESHPOINT, \
1130 MEDIAN.MESH M, \
1131 MEDIAN.MESHSCALARVALUE MSV \
1132 where MSV.FEATUREID = MEDIAN.MESHPOINT.FEATUREID AND \
1133 ML.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \
1134 ML.MESHID = MEDIAN.MESHPOINT.MESHID and \
1135 M.MESHID = MEDIAN.MESHPOINT.MESHID AND \
1136 M.PARTIDMIN <= MSV.PARTID AND \
1137 M.PARTIDMAX >= MSV.PARTID AND \
1138 MSV.PARAMETERID = ? AND \
1139 MSV.TIMEVALUE = ? AND \
1140 M.OBJECTID = ? AND \
1141 MEDIAN.MESHPOINT.KPOSITION = ? \
1142 order by MEDIAN.MESHPOINT.JPOSITION, \
1143 MEDIAN.MESHPOINT.IPOSITION
1144 horizontalcrosssection_mesh_odv_data = SELECT SI.NAME CRUISE, \
1145 M.MESHID || '-' || MEDIAN.MESHPOINT.IPOSITION || '-' || MEDIAN.MESHPOINT.JPOSITION STATION, \
1146 '*' TYPE, \
1147 ST_ASTEXT(SHAPE), \
1148 0 BOTDEPTH, \
1149 (((ML.UPPERZLOCATION + ML.LOWERZLOCATION) / 2)*-1) DEPTH, \
1150 MSV.TIMEVALUE, \
1151 MSV.DATAVALUE, \
1152 MSV.PARAMETERID PARAMETER, \
1153 MEDIAN.MESHPOINT.JPOSITION, \
1154 MEDIAN.MESHPOINT.IPOSITION, \
1155 MEDIAN.MESHPOINT.KPOSITION \
1156 from MEDIAN.MESHLAYER ML, \
1157 MEDIAN.MESHPOINT, \
1158 MEDIAN.MESH M, \
1159 MEDIAN.SOURCEINFO SI, \
1160 MEDIAN.MESHSCALARVALUE MSV \
1161 where MSV.FEATUREID = MEDIAN.MESHPOINT.FEATUREID AND \
1162 ML.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \
1163 ML.MESHID = MEDIAN.MESHPOINT.MESHID and \
1164 M.MESHID = MEDIAN.MESHPOINT.MESHID AND \
1165 M.PARTIDMIN <= MSV.PARTID AND \
1166 M.PARTIDMAX >= MSV.PARTID AND \
1167 M.SOURCEID = SI.SOURCEID AND \
1168 MSV.PARAMETERID = ? AND \
1169 MSV.TIMEVALUE = ? AND \
1170 M.OBJECTID = ? AND \
1171 MEDIAN.MESHPOINT.KPOSITION = ? \
1172 order by MEDIAN.MESHPOINT.JPOSITION, \
1173 MEDIAN.MESHPOINT.IPOSITION
1174
1175 area_filter = SELECT DISTINCT \
1176 FEATUREID KEY ,\
1177 DESCRIPTION VALUE \
1178 FROM MEDIAN.FEATUREAREA \
1179 WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \
1180 FEATUREID > 0 \
1181 ORDER BY FEATUREID
1182
1183 subarea_filter = SELECT \
1184 FEATURECODE KEY, \
1185 NAME VALUE \
1186 FROM MEDIAN.FEATUREAREA \
1187 WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \
1188 FEATUREID = ? \
1189 ORDER BY NAME
1190
1191 rasterQuery = SELECT ST_ASTEXT(RASTER) \
1192 FROM MEDIAN.TOPO_WORLD_2MIN \
1193 WHERE INTERSECTS(RASTER, "?")
http://dive4elements.wald.intevation.org