Mercurial > dive4elements > gnv-client

annotate gnv-artifacts/doc/conf/queries.properties @ 557:05fcb3c553fd 0.4

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