Mercurial > dive4elements > gnv-client

comparison gnv-artifacts/src/test/ressources/queries.properties @ 83:5c8e54726a58

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added Support for Vertical Profiles Mesh gnv-artifacts/trunk@113 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Tim Englich <tim.englich@intevation.de>
date Wed, 23 Sep 2009 11:57:08 +0000
parents 5eb62df21f9a
children dbd141c6bb97
comparison
equal deleted inserted replaced
82:5eb62df21f9a 83:5c8e54726a58
1 #############################################
2 #############################################
3 ########## Zeitserie ##############
4 #############################################
5 #############################################
6
1 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 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
2 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 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
3 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 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
4 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 ( ? ) 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 ( ? )
5 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 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
6 12
7 13 #############################################
8 # Zeitserie Mesh 14 #############################################
15 ########## Zeitserie Mesh ##############
16 #############################################
17 #############################################
9 18
10 timeseries_mesh = SELECT OBJECTID KEY, m.NAME VALUE FROM MEDIAN.MESH m order by m.NAME 19 timeseries_mesh = SELECT OBJECTID KEY, m.NAME VALUE FROM MEDIAN.MESH m order by m.NAME
11 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 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
12 timeseries_meshpoint_depth = select mp.FEATUREID KEY, -ml.UPPERZLOCATION || ' - '|| -ml.LOWERZLOCATION as VALUE from MEDIAN.MESHLAYER ml, MEDIAN.MESHPOINT mp where ml.KPOSITION = mp.KPOSITION and ml.MESHID = mp.MESHID and mp.FEATUREID in ( select FEATUREID from MEDIAN.MESHPOINT mp, MEDIAN.MESH m where m.OBJECTID = ? AND mp.MESHID = m.MESHID AND IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) and JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?)) order by ml.UPPERZLOCATION desc 21 timeseries_meshpoint_depth = select mp.FEATUREID KEY, \
22 -ml.UPPERZLOCATION || ' - '|| -ml.LOWERZLOCATION as VALUE \
23 from MEDIAN.MESHLAYER ml, \
24 MEDIAN.MESHPOINT mp \
25 where ml.KPOSITION = mp.KPOSITION and \
26 ml.MESHID = mp.MESHID and \
27 mp.FEATUREID in \
28 ( select FEATUREID \
29 from MEDIAN.MESHPOINT mp, \
30 MEDIAN.MESH m \
31 where m.OBJECTID = ? AND \
32 mp.MESHID = m.MESHID AND \
33 IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) and \
34 JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?)) \
35 order by ml.UPPERZLOCATION desc
13 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 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
14 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 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
15 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 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
16 39
17 # Vertikalprofil 40 #############################################
41 #############################################
42 ########## Vertikalprofil ##############
43 #############################################
44 #############################################
18 45
19 verticalprofile_point=SELECT DISTINCT \ 46 verticalprofile_point=SELECT DISTINCT \
20 tsp.FEATUREID KEY, \ 47 tsp.FEATUREID KEY, \
21 tsp.NAME VALUE \ 48 tsp.NAME VALUE \
22 FROM MEDIAN.TIMESERIESPOINT tsp, \ 49 FROM MEDIAN.TIMESERIESPOINT tsp, \
60 MEDIAN.MEASUREMENT m \ 87 MEDIAN.MEASUREMENT m \
61 where ts.TIMESERIESID = tsv.TIMESERIESID and \ 88 where ts.TIMESERIESID = tsv.TIMESERIESID and \
62 m.MEASUREMENTID = tsv.MEASUREMENTID and \ 89 m.MEASUREMENTID = tsv.MEASUREMENTID and \
63 m.FEATUREID = ? and \ 90 m.FEATUREID = ? and \
64 ts.PARAMETERID IN ( ? ) AND \ 91 ts.PARAMETERID IN ( ? ) AND \
65 tsv.TIMEVALUE IN (TO_DATE ('?', 'YYYY.MM.DD HH24:MI:SS')) \ 92 tsv.TIMEVALUE IN (?) \
66 ORDER BY tsv.TIMEVALUE , \ 93 ORDER BY tsv.TIMEVALUE , \
67 tsv.TIMESERIESID , \ 94 tsv.TIMESERIESID , \
68 ts.PARAMETERID , \ 95 ts.PARAMETERID , \
69 tsv.TIMEVALUE 96 tsv.TIMEVALUE
97
98 #############################################
99 #############################################
100 ########## Vertikalprofil Mesh ##############
101 #############################################
102 #############################################
103
104 verticalprofile_mesh = SELECT OBJECTID KEY, \
105 m.NAME VALUE \
106 FROM MEDIAN.MESH m \
107 order by m.NAME
108
109 verticalprofile_mesh_point = SELECT FEATUREID KEY, \
110 SHAPE VALUE \
111 FROM MEDIAN.MESHPOINT mp , \
112 MEDIAN.MESH m \
113 WHERE m.OBJECTID = ? AND \
114 m.MESHID = mp.MESHID AND \
115 mp.KPOSITION = 1 AND \
116 rownum < 10
117
118 verticalprofile_mesh_parameter=SELECT distinct \
119 p.PARAMETERID KEY , \
120 p.GERMANNAME VALUE \
121 from MEDIAN.PARAMETER p, \
122 MEDIAN.MESHSCALARVALUE msc, \
123 MEDIAN.MESH m \
124 where m.OBJECTID = ? AND \
125 m.PARTIDMIN = msc.PARTID AND \
126 msc.PARAMETERID = p.PARAMETERID \
127 order by p.GERMANNAME
128
129 verticalprofile_mesh_date= select distinct \
130 msv.TIMEVALUE KEY, \
131 msv.TIMEVALUE VALUE \
132 from MEDIAN.MESHSCALARVALUE msv , \
133 MEDIAN.MESH m \
134 where m.OBJECTID = ? AND \
135 msv.PARTID >= m.PARTIDMIN AND \
136 msv.PARTID <= m.PARTIDMAX \
137 order by msv.TIMEVALUE
138
139 verticalprofile_mesh_chart_data=select ml.UPPERZLOCATION XORDINATE, \
140 msv.DATAVALUE YORDINATE, \
141 msv.PARAMETERID GROUP1, \
142 msv.TIMEVALUE GROUP2, \
143 1 GROUP3 \
144 from MEDIAN.MESHLAYER ml, \
145 MEDIAN.MESHPOINT mp, \
146 MEDIAN.MESH m, \
147 MEDIAN.MESHSCALARVALUE msv \
148 where msv.FEATUREID = mp. FEATUREID AND \
149 ml.KPOSITION = mp.KPOSITION and \
150 ml.MESHID = mp.MESHID and \
151 m.MESHID = mp.MESHID AND \
152 m.PARTIDMIN <= msv.PARTID AND \
153 m.PARTIDMAX >= msv.PARTID AND \
154 msv.PARAMETERID in (?) AND \
155 msv.TIMEVALUE in (?) AND \
156 m.OBJECTID = ? AND \
157 mp.FEATUREID in \
158 ( select FEATUREID \
159 from MEDIAN.MESHPOINT mp, \
160 MEDIAN.MESH m \
161 where m.OBJECTID = ? AND \
162 mp.MESHID = m.MESHID AND \
163 IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) and \
164 JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?)) \
165 order by msv.PARAMETERID, \
166 msv.TIMEVALUE, \
167 ml.UPPERZLOCATION
168
169
http://dive4elements.wald.intevation.org