Mercurial > dive4elements > gnv-client

comparison gnv-artifacts/doc/conf/queries.properties @ 657:af3f56758f59

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