Mercurial > dive4elements > gnv-client

annotate gnv-artifacts/src/test/ressources/queries.properties @ 85:dbd141c6bb97

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added Support for VerticalProfiles for InstantaneousPoints gnv-artifacts/trunk@115 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Tim Englich <tim.englich@intevation.de>
date Wed, 23 Sep 2009 14:32:55 +0000
parents 5c8e54726a58
children ce398a7a99fc
rev   line source
83
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
1 #############################################
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
2 #############################################
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
3 ########## Zeitserie ##############
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
4 #############################################
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
5 #############################################
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
6
62
9f57053d0384 Testcases Modified
Tim Englich <tim.englich@intevation.de>
parents: 60
diff changeset
7 timeseries_timeseriespoint=SELECT DISTINCT tsp.FEATUREID KEY, tsp.NAME VALUE FROM MEDIAN.TIMESERIESPOINT tsp, MEDIAN.MEASUREMENT mmt WHERE tsp.FEATUREID = mmt.FEATUREID AND mmt.SOURCEID = ? order by tsp.name
9f57053d0384 Testcases Modified
Tim Englich <tim.englich@intevation.de>
parents: 60
diff changeset
8 timeseries_parameter=SELECT DISTINCT p.PARAMETERID KEY, p.GERMANNAME VALUE from MEDIAN.PARAMETER p where p.PARAMETERID in (select distinct ts.PARAMETERID from MEDIAN.TIMESERIES ts where ts.TIMESERIESID in (select distinct tsv.TIMESERIESID from MEDIAN.TIMESERIESVALUE tsv where tsv.MEASUREMENTID in (select m.MEASUREMENTID from MEDIAN.MEASUREMENT m, MEDIAN.TIMESERIESPOINT tsp where m.FEATUREID = tsp.FEATUREID and tsp.FEATUREID IN ( ? )))) ORDER BY p.GERMANNAME
9f57053d0384 Testcases Modified
Tim Englich <tim.englich@intevation.de>
parents: 60
diff changeset
9 timeseries_depth_height=SELECT DISTINCT m.MEASUREMENTID KEY, m.ZLOCATION VALUE from MEDIAN.MEASUREMENT m where m.MEASUREMENTID in (SELECT DISTINCT t_v.MEASUREMENTID from MEDIAN.TIMESERIESVALUE t_v where t_v.TIMESERIESID in (SELECT DISTINCT t.TIMESERIESID from MEDIAN.TIMESERIES t where t.PARAMETERID in (SELECT DISTINCT p.PARAMETERID from MEDIAN.PARAMETER p where m.FEATUREID IN ( ? ) and p.PARAMETERID IN (?))))ORDER BY m.ZLOCATION DESC
69
f24c825144bc TestCase Refactored to the new Folder-Based Configuration.
Tim Englich <tim.englich@intevation.de>
parents: 62
diff changeset
10 timeseries_interval=select min(tv.TIMEVALUE) MIN, max(tv.TIMEVALUE) MAX from MEDIAN.TIMESERIES t , MEDIAN.TIMESERIESVALUE tv where tv.TIMESERIESID = t.TIMESERIESID AND t.PARAMETERID IN ( ? ) AND tv.MEASUREMENTID IN ( ? )
83
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
11 timeseries_chart_data=SELECT tv.TIMEVALUE XORDINATE, tv.DATAVALUE YORDINATE, t.PARAMETERID GROUP1, tv.MEASUREMENTID GROUP2, tv.TIMESERIESID GROUP3 FROM MEDIAN.TIMESERIESVALUE tv, MEDIAN.TIMESERIES t WHERE tv.TIMESERIESID = t.TIMESERIESID AND t.PARAMETERID IN ( ? ) AND tv.MEASUREMENTID IN ( ? ) AND tv.TIMEVALUE > TO_DATE (?, 'YYYY.MM.DD HH24:MI:SS') AND tv.TIMEVALUE < TO_DATE (?, 'YYYY.MM.DD HH24:MI:SS') ORDER BY tv.MEASUREMENTID ,tv.TIMESERIESID ,t.PARAMETERID ,tv.TIMEVALUE
81
9b41f3688610 Added Support for TimeSeriesMesh
Tim Englich <tim.englich@intevation.de>
parents: 69
diff changeset
12
83
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
13 #############################################
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
14 #############################################
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
15 ########## Zeitserie Mesh ##############
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
16 #############################################
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
17 #############################################
81
9b41f3688610 Added Support for TimeSeriesMesh
Tim Englich <tim.englich@intevation.de>
parents: 69
diff changeset
18
9b41f3688610 Added Support for TimeSeriesMesh
Tim Englich <tim.englich@intevation.de>
parents: 69
diff changeset
19 timeseries_mesh = SELECT OBJECTID KEY, m.NAME VALUE FROM MEDIAN.MESH m order by m.NAME
9b41f3688610 Added Support for TimeSeriesMesh
Tim Englich <tim.englich@intevation.de>
parents: 69
diff changeset
20 timeseries_meshpoint = SELECT FEATUREID KEY, SHAPE VALUE FROM MEDIAN.MESHPOINT mp , MEDIAN.MESH m WHERE m.OBJECTID = ? AND mp.MESHID = m.MESHID AND KPOSITION = 1 AND rownum < 10
83
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
21 timeseries_meshpoint_depth = select mp.FEATUREID KEY, \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
22 -ml.UPPERZLOCATION || ' - '|| -ml.LOWERZLOCATION as VALUE \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
23 from MEDIAN.MESHLAYER ml, \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
24 MEDIAN.MESHPOINT mp \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
25 where ml.KPOSITION = mp.KPOSITION and \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
26 ml.MESHID = mp.MESHID and \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
27 mp.FEATUREID in \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
28 ( select FEATUREID \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
29 from MEDIAN.MESHPOINT mp, \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
30 MEDIAN.MESH m \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
31 where m.OBJECTID = ? AND \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
32 mp.MESHID = m.MESHID AND \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
33 IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) and \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
34 JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?)) \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
35 order by ml.UPPERZLOCATION desc
81
9b41f3688610 Added Support for TimeSeriesMesh
Tim Englich <tim.englich@intevation.de>
parents: 69
diff changeset
36 timeseries_mesh_parameter=SELECT distinct p.PARAMETERID KEY , p.GERMANNAME VALUE from MEDIAN.PARAMETER p, MEDIAN.MESHSCALARVALUE msc, MEDIAN.MESH m where m.OBJECTID = ? AND msc.PARTID = m.PARTIDMIN AND msc.PARAMETERID = p.PARAMETERID
9b41f3688610 Added Support for TimeSeriesMesh
Tim Englich <tim.englich@intevation.de>
parents: 69
diff changeset
37 timeseries_mesh_interval=select /*+ parallel(TIMEVALUE,5) */ min(TIMEVALUE) MIN, max(TIMEVALUE) MAX from MEDIAN.MESHSCALARVALUE msc , MEDIAN.MESH m where m.OBJECTID = ? AND msc.PARTID >= m.PARTIDMIN AND msc.PARTID <= m.PARTIDMAX
83
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
38 timeseries_mesh_chart_data=select /*+ parallel(timevalue,10) */ msv.TIMEVALUE XORDINATE, msv.DATAVALUE YORDINATE, msv.PARAMETERID GROUP1, msv.FEATUREID GROUP2, mp.FEATUREID GROUP3 from MEDIAN.MESHSCALARVALUE msv , MEDIAN.MESHPOINT mp, MEDIAN.MESH m where (m.OBJECTID = ? AND msv.PARTID >= m.PARTIDMIN AND msv.PARTID <= m.PARTIDMAX ) AND msv.FEATUREID in ( ? ) and msv.PARAMETERID in ( ? ) AND mp.FEATUREID = ? and TIMEVALUE >= to_date(?, 'YYYY.MM.DD HH24:MI:SS') and TIMEVALUE <= to_date(?, 'YYYY.MM.DD HH24:MI:SS') order by msv.FEATUREID, msv.PARAMETERID, msv.TIMEVALUE
81
9b41f3688610 Added Support for TimeSeriesMesh
Tim Englich <tim.englich@intevation.de>
parents: 69
diff changeset
39
83
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
40 #############################################
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
41 #############################################
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
42 ########## Vertikalprofil ##############
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
43 #############################################
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
44 #############################################
82
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
45
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
46 verticalprofile_point=SELECT DISTINCT \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
47 tsp.FEATUREID KEY, \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
48 tsp.NAME VALUE \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
49 FROM MEDIAN.TIMESERIESPOINT tsp, \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
50 MEDIAN.MEASUREMENT mmt \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
51 WHERE tsp.FEATUREID = mmt.FEATUREID AND \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
52 mmt.SOURCEID = ? \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
53 order by tsp.name
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
54
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
55 verticalprofile_parameter=SELECT DISTINCT \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
56 p.PARAMETERID KEY, \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
57 p.GERMANNAME VALUE \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
58 from MEDIAN.PARAMETER p, \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
59 MEDIAN.TIMESERIES ts, \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
60 MEDIAN.TIMESERIESVALUE tsv, \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
61 MEDIAN.MEASUREMENT m \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
62 where ts.PARAMETERID = p.PARAMETERID and \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
63 ts.TIMESERIESID = tsv.TIMESERIESID and \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
64 m.MEASUREMENTID = tsv.MEASUREMENTID and \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
65 m.FEATUREID = ? \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
66 ORDER BY p.GERMANNAME
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
67
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
68 verticalprofile_date=select distinct \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
69 tsv.TIMEVALUE KEY, \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
70 tsv.TIMEVALUE VALUE \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
71 from MEDIAN.TIMESERIES ts , \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
72 MEDIAN.TIMESERIESVALUE tsv, \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
73 MEDIAN.MEASUREMENT m \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
74 where ts.TIMESERIESID = tsv.TIMESERIESID and \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
75 m.MEASUREMENTID = tsv.MEASUREMENTID and \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
76 m.FEATUREID = ? and \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
77 ts.PARAMETERID IN ( ? ) \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
78 order by tsv.TIMEVALUE
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
79
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
80 verticalprofile_chart_data= SELECT m.ZLOCATION XORDINATE, \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
81 tsv.DATAVALUE YORDINATE, \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
82 ts.PARAMETERID GROUP1, \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
83 tsv.TIMEVALUE GROUP2, \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
84 1 GROUP3 \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
85 from MEDIAN.TIMESERIES ts, \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
86 MEDIAN.TIMESERIESVALUE tsv, \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
87 MEDIAN.MEASUREMENT m \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
88 where ts.TIMESERIESID = tsv.TIMESERIESID and \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
89 m.MEASUREMENTID = tsv.MEASUREMENTID and \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
90 m.FEATUREID = ? and \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
91 ts.PARAMETERID IN ( ? ) AND \
83
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
92 tsv.TIMEVALUE IN (?) \
82
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
93 ORDER BY tsv.TIMEVALUE , \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
94 tsv.TIMESERIESID , \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
95 ts.PARAMETERID , \
5eb62df21f9a Added Support for Vertical Profiles Marnet, STAUN, IMIS
Tim Englich <tim.englich@intevation.de>
parents: 81
diff changeset
96 tsv.TIMEVALUE
83
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
97
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
98 #############################################
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
99 #############################################
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
100 ########## Vertikalprofil Mesh ##############
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
101 #############################################
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
102 #############################################
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
103
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
104 verticalprofile_mesh = SELECT OBJECTID KEY, \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
105 m.NAME VALUE \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
106 FROM MEDIAN.MESH m \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
107 order by m.NAME
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
108
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
109 verticalprofile_mesh_point = SELECT FEATUREID KEY, \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
110 SHAPE VALUE \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
111 FROM MEDIAN.MESHPOINT mp , \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
112 MEDIAN.MESH m \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
113 WHERE m.OBJECTID = ? AND \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
114 m.MESHID = mp.MESHID AND \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
115 mp.KPOSITION = 1 AND \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
116 rownum < 10
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
117
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
118 verticalprofile_mesh_parameter=SELECT distinct \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
119 p.PARAMETERID KEY , \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
120 p.GERMANNAME VALUE \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
121 from MEDIAN.PARAMETER p, \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
122 MEDIAN.MESHSCALARVALUE msc, \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
123 MEDIAN.MESH m \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
124 where m.OBJECTID = ? AND \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
125 m.PARTIDMIN = msc.PARTID AND \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
126 msc.PARAMETERID = p.PARAMETERID \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
127 order by p.GERMANNAME
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
128
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
129 verticalprofile_mesh_date= select distinct \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
130 msv.TIMEVALUE KEY, \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
131 msv.TIMEVALUE VALUE \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
132 from MEDIAN.MESHSCALARVALUE msv , \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
133 MEDIAN.MESH m \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
134 where m.OBJECTID = ? AND \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
135 msv.PARTID >= m.PARTIDMIN AND \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
136 msv.PARTID <= m.PARTIDMAX \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
137 order by msv.TIMEVALUE
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
138
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
139 verticalprofile_mesh_chart_data=select ml.UPPERZLOCATION XORDINATE, \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
140 msv.DATAVALUE YORDINATE, \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
141 msv.PARAMETERID GROUP1, \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
142 msv.TIMEVALUE GROUP2, \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
143 1 GROUP3 \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
144 from MEDIAN.MESHLAYER ml, \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
145 MEDIAN.MESHPOINT mp, \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
146 MEDIAN.MESH m, \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
147 MEDIAN.MESHSCALARVALUE msv \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
148 where msv.FEATUREID = mp. FEATUREID AND \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
149 ml.KPOSITION = mp.KPOSITION and \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
150 ml.MESHID = mp.MESHID and \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
151 m.MESHID = mp.MESHID AND \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
152 m.PARTIDMIN <= msv.PARTID AND \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
153 m.PARTIDMAX >= msv.PARTID AND \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
154 msv.PARAMETERID in (?) AND \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
155 msv.TIMEVALUE in (?) AND \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
156 m.OBJECTID = ? AND \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
157 mp.FEATUREID in \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
158 ( select FEATUREID \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
159 from MEDIAN.MESHPOINT mp, \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
160 MEDIAN.MESH m \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
161 where m.OBJECTID = ? AND \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
162 mp.MESHID = m.MESHID AND \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
163 IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) and \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
164 JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?)) \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
165 order by msv.PARAMETERID, \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
166 msv.TIMEVALUE, \
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
167 ml.UPPERZLOCATION
5c8e54726a58 Added Support for Vertical Profiles Mesh
Tim Englich <tim.englich@intevation.de>
parents: 82
diff changeset
168
85
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
169 #############################################
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
170 #############################################
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
171 ##### Vertikalprofil InstantaneousPoint #####
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
172 #############################################
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
173 #############################################
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
174
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
175 verticalprofile_instantaneouspoint_series = SELECT S.SERIESID KEY, \
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
176 S.DESCRIPTION VALUE \
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
177 FROM MEDIAN.SERIES S
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
178
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
179 verticalprofile_instantaneouspoint_point = SELECT IP.FEATUREID KEY, \
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
180 IP.SHAPE VALUE \
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
181 FROM MEDIAN.INSTANTANEOUSPOINT IP \
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
182 WHERE IP.POINTSPEC = 4 AND \
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
183 IP.SERIESID = ?
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
184
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
185 verticalprofile_instantaneouspoint_parameter = SELECT DISTINCT \
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
186 P.PARAMETERID KEY , \
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
187 P.GERMANNAME VALUE \
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
188 FROM MEDIAN.PARAMETER P, \
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
189 MEDIAN.INSTANTANEOUSPOINT IP, \
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
190 MEDIAN.MEASUREMENT M, \
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
191 MEDIAN.MEASUREDSCALARVALUE MSV \
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
192 WHERE IP.FEATUREID = M.FEATUREID AND \
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
193 M.MEASUREMENTID = MSV.MEASUREMENTID AND \
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
194 MSV.PARAMETERID = P.PARAMETERID AND \
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
195 IP.FEATUREID = ? \
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
196 ORDER BY P.GERMANNAME
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
197
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
198 verticalprofile_instantaneouspoint_chart_data = SELECT M.ZLOCATION XORDINATE, \
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
199 MSV.DATAVALUE YORDINATE, \
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
200 MSV.PARAMETERID GROUP1, \
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
201 IP.TIMEVALUE GROUP2, \
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
202 1 GROUP3 \
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
203 FROM MEDIAN.INSTANTANEOUSPOINT IP, \
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
204 MEDIAN.MEASUREMENT M, \
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
205 MEDIAN.MEASUREDSCALARVALUE MSV \
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
206 WHERE IP.FEATUREID = M.FEATUREID AND \
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
207 M.MEASUREMENTID = MSV.MEASUREMENTID AND \
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
208 IP.FEATUREID = ? AND \
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
209 MSV.PARAMETERID in (?) \
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
210 ORDER BY IP.TIMEVALUE, \
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
211 MSV.PARAMETERID, \
dbd141c6bb97 Added Support for VerticalProfiles for InstantaneousPoints
Tim Englich <tim.englich@intevation.de>
parents: 83
diff changeset
212 M.ZLOCATION
http://dive4elements.wald.intevation.org