Mercurial > dive4elements > gnv-client

diff 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
line wrap: on
line diff
--- a/gnv-artifacts/src/test/ressources/queries.properties	Tue Sep 22 13:20:30 2009 +0000
+++ b/gnv-artifacts/src/test/ressources/queries.properties	Wed Sep 23 11:57:08 2009 +0000
@@ -1,20 +1,47 @@
+#############################################
+#############################################
+##########     Zeitserie       ##############
+#############################################
+#############################################
+
 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
 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
 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
 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 ( ? )
-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
+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
 
-
-# Zeitserie Mesh
+#############################################
+#############################################
+##########   Zeitserie Mesh    ##############
+#############################################
+#############################################
 
 timeseries_mesh = SELECT OBJECTID KEY, m.NAME VALUE FROM MEDIAN.MESH m order by m.NAME
 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 
-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
+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
 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
 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
-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
+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
 
-# Vertikalprofil
+#############################################
+#############################################
+##########   Vertikalprofil    ##############
+#############################################
+#############################################
 
 verticalprofile_point=SELECT DISTINCT \
            tsp.FEATUREID KEY, \
@@ -62,8 +89,81 @@
           m.MEASUREMENTID = tsv.MEASUREMENTID and \
           m.FEATUREID = ? and \
           ts.PARAMETERID IN ( ? ) AND \
-          tsv.TIMEVALUE IN (TO_DATE ('?', 'YYYY.MM.DD HH24:MI:SS')) \
+          tsv.TIMEVALUE IN (?) \
     ORDER BY tsv.TIMEVALUE , \
              tsv.TIMESERIESID , \
              ts.PARAMETERID , \
              tsv.TIMEVALUE
+             
+#############################################
+#############################################
+########## Vertikalprofil Mesh ##############
+#############################################
+#############################################
+
+verticalprofile_mesh = SELECT OBJECTID KEY, \
+        m.NAME VALUE \
+    FROM MEDIAN.MESH m \
+    order by m.NAME
+    
+verticalprofile_mesh_point = SELECT FEATUREID KEY, \
+        SHAPE VALUE \
+    FROM MEDIAN.MESHPOINT mp , \
+         MEDIAN.MESH m \
+    WHERE m.OBJECTID = ? AND \
+          m.MESHID = mp.MESHID AND \
+          mp.KPOSITION = 1 AND \
+          rownum < 10
+          
+verticalprofile_mesh_parameter=SELECT distinct \
+        p.PARAMETERID KEY , \
+        p.GERMANNAME VALUE \
+    from MEDIAN.PARAMETER p, \
+         MEDIAN.MESHSCALARVALUE msc, \
+         MEDIAN.MESH m  \
+    where m.OBJECTID = ? AND \
+          m.PARTIDMIN = msc.PARTID AND \
+          msc.PARAMETERID = p.PARAMETERID \
+    order by p.GERMANNAME
+          
+verticalprofile_mesh_date= select  distinct \
+        msv.TIMEVALUE KEY, \
+        msv.TIMEVALUE VALUE \
+    from MEDIAN.MESHSCALARVALUE msv , \
+         MEDIAN.MESH m \
+    where m.OBJECTID = ?  AND \
+          msv.PARTID >= m.PARTIDMIN AND \
+          msv.PARTID <= m.PARTIDMAX \
+    order by msv.TIMEVALUE
+
+verticalprofile_mesh_chart_data=select ml.UPPERZLOCATION XORDINATE, \
+           msv.DATAVALUE YORDINATE, \
+           msv.PARAMETERID GROUP1, \
+           msv.TIMEVALUE GROUP2, \
+           1 GROUP3 \
+    from MEDIAN.MESHLAYER ml, \
+         MEDIAN.MESHPOINT mp, \
+         MEDIAN.MESH m, \
+         MEDIAN.MESHSCALARVALUE msv \
+    where msv.FEATUREID = mp. FEATUREID AND \
+          ml.KPOSITION = mp.KPOSITION and \
+          ml.MESHID = mp.MESHID and \
+          m.MESHID = mp.MESHID AND \
+          m.PARTIDMIN <= msv.PARTID AND \
+          m.PARTIDMAX >= msv.PARTID AND \
+          msv.PARAMETERID in (?) AND \
+          msv.TIMEVALUE in (?) AND \
+          m.OBJECTID = ? 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 msv.PARAMETERID, \
+            msv.TIMEVALUE, \
+            ml.UPPERZLOCATION
+            
+  
\ No newline at end of file
http://dive4elements.wald.intevation.org