Mercurial > dive4elements > gnv-client

diff gnv-artifacts/doc/conf/queries.properties @ 319:251f16a083f8

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added RegionFilter-Support to HorizontalProfile InstantaneousPoint gnv-artifacts/trunk@378 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Tim Englich <tim.englich@intevation.de>
date Fri, 27 Nov 2009 13:00:00 +0000
parents e7f1c79bf9cd
children 3eff9241ea1e
line wrap: on
line diff
--- a/gnv-artifacts/doc/conf/queries.properties	Thu Nov 26 16:10:47 2009 +0000
+++ b/gnv-artifacts/doc/conf/queries.properties	Fri Nov 27 13:00:00 2009 +0000
@@ -59,7 +59,7 @@
 
 timeseries_interval=select min(tv.TIMEVALUE) MIN, \
         max(tv.TIMEVALUE) MAX \
-    from MEDIAN.TIMESERIES t , \
+    from MEDIAN.TIMESERIES t, \
          MEDIAN.TIMESERIESVALUE tv \
     where tv.TIMESERIESID = t.TIMESERIESID AND \
           t.PARAMETERID IN ( ? ) AND \
@@ -78,9 +78,9 @@
           tv.MEASUREMENTID IN ( ? ) AND \
           tv.TIMEVALUE >= ? AND \
           tv.TIMEVALUE <= ? \
-    ORDER BY tv.MEASUREMENTID , \
-             tv.TIMESERIESID , \
-             t.PARAMETERID , \
+    ORDER BY tv.MEASUREMENTID, \
+             tv.TIMESERIESID, \
+             t.PARAMETERID, \
              tv.TIMEVALUE
              
 timeseries_odv_data = SELECT SI.NAME CRUISE, \
@@ -94,7 +94,7 @@
             TS.PARAMETERID PARAMETER, \
             TSV.MEASUREMENTID, \
             TSV.TIMESERIESID \
-     FROM MEDIAN.TIMESERIESPOINT , \
+     FROM MEDIAN.TIMESERIESPOINT, \
           MEDIAN.TIMESERIESVALUE TSV, \
           MEDIAN.TIMESERIES TS, \
           MEDIAN.MEASUREMENT M, \
@@ -107,9 +107,9 @@
            TSV.MEASUREMENTID IN ( ? ) AND \
            TSV.TIMEVALUE >= ? AND \
            TSV.TIMEVALUE <= ? \
-     ORDER BY TSV.MEASUREMENTID , \
-              TSV.TIMESERIESID , \
-              TS.PARAMETERID , \
+     ORDER BY TSV.MEASUREMENTID, \
+              TSV.TIMESERIESID, \
+              TS.PARAMETERID, \
               TSV.TIMEVALUE
 
 #############################################
@@ -146,7 +146,7 @@
     ORDER BY ML.UPPERZLOCATION DESC
 
 timeseries_mesh_parameter=SELECT distinct \
-        p.PARAMETERID KEY , \
+        p.PARAMETERID KEY, \
         p.GERMANNAME VALUE \
     from MEDIAN.PARAMETER p, \
          MEDIAN.MESHSCALARVALUE msc, \
@@ -158,7 +158,7 @@
 timeseries_mesh_interval=select /*+ parallel(TIMEVALUE,5) */ \
         min(TIMEVALUE) MIN, \
         max(TIMEVALUE) MAX \
-    from MEDIAN.MESHSCALARVALUE msc , \
+    from MEDIAN.MESHSCALARVALUE msc, \
          MEDIAN.MESH m \
     where m.OBJECTID = ? AND \
           msc.PARTID >= m.PARTIDMIN AND \
@@ -172,7 +172,7 @@
         msv.FEATUREID GROUP2, \
         mp.FEATUREID GROUP3, \
         -1 GAPID \
-    from MEDIAN.MESHSCALARVALUE msv , \
+    from MEDIAN.MESHSCALARVALUE msv, \
          MEDIAN.MESHPOINT mp, \
          MEDIAN.MESH m \
     where (m.OBJECTID = ? AND \
@@ -196,9 +196,9 @@
         0 BOTDEPTH, \
         (((ML.UPPERZLOCATION + ML.LOWERZLOCATION) / 2)*-1) DEPTH, \
         msv.TIMEVALUE, \
-        msv.DATAVALUE , \
+        msv.DATAVALUE, \
         msv.PARAMETERID PARAMETER \
-    from MEDIAN.MESHSCALARVALUE msv , \
+    from MEDIAN.MESHSCALARVALUE msv, \
          MEDIAN.MESHPOINT, \
          MEDIAN.MESH m, \
          MEDIAN.SOURCEINFO SI, \
@@ -261,7 +261,7 @@
 verticalprofile_date=select distinct \
            tsv.TIMEVALUE KEY, \
            tsv.TIMEVALUE VALUE \
-    from MEDIAN.TIMESERIES ts , \
+    from MEDIAN.TIMESERIES ts, \
          MEDIAN.TIMESERIESVALUE tsv, \
          MEDIAN.MEASUREMENT m \
     where ts.TIMESERIESID = tsv.TIMESERIESID and \
@@ -285,9 +285,9 @@
           TSV.TIMEVALUE IN (?) AND \
           M.ZLOCATION >= ? AND \
           M.ZLOCATION <= ? \
-    ORDER BY TSV.TIMEVALUE , \
-             TSV.TIMESERIESID , \
-             TS.PARAMETERID , \
+    ORDER BY TSV.TIMEVALUE, \
+             TSV.TIMESERIESID, \
+             TS.PARAMETERID, \
              TSV.TIMEVALUE
              
 verticalprofile_odv_data = SELECT SI.NAME CRUISE, \
@@ -301,7 +301,7 @@
             TS.PARAMETERID PARAMETER, \
             TSV.MEASUREMENTID, \
             TSV.TIMESERIESID \
-     FROM MEDIAN.TIMESERIESPOINT , \
+     FROM MEDIAN.TIMESERIESPOINT, \
           MEDIAN.TIMESERIESVALUE TSV, \
           MEDIAN.TIMESERIES TS, \
           MEDIAN.MEASUREMENT M, \
@@ -315,9 +315,9 @@
            TSV.TIMEVALUE IN (?) AND \
            M.ZLOCATION >= ? AND \
            M.ZLOCATION <= ? \
-     ORDER BY TSV.MEASUREMENTID , \
-              TSV.TIMESERIESID , \
-              TS.PARAMETERID , \
+     ORDER BY TSV.MEASUREMENTID, \
+              TSV.TIMESERIESID, \
+              TS.PARAMETERID, \
               TSV.TIMEVALUE
              
 #############################################
@@ -384,7 +384,7 @@
     ORDER BY ML.UPPERZLOCATION DESC
     
 verticalprofile_mesh_parameter=SELECT distinct \
-        p.PARAMETERID KEY , \
+        p.PARAMETERID KEY, \
         p.GERMANNAME VALUE \
     from MEDIAN.PARAMETER p, \
          MEDIAN.MESHSCALARVALUE msc, \
@@ -397,7 +397,7 @@
 verticalprofile_mesh_date= select  distinct \
         msv.TIMEVALUE KEY, \
         msv.TIMEVALUE VALUE \
-    from MEDIAN.MESHSCALARVALUE msv , \
+    from MEDIAN.MESHSCALARVALUE msv, \
          MEDIAN.MESH m \
     where m.OBJECTID = ?  AND \
           msv.PARTID >= m.PARTIDMIN AND \
@@ -443,9 +443,9 @@
         ST_ASTEXT(SHAPE), \
         0 BOTDEPTH, \
         (((ML.UPPERZLOCATION + ML.LOWERZLOCATION) / 2)*-1) DEPTH, \
-        msv.DATAVALUE , \
+        msv.DATAVALUE, \
         msv.PARAMETERID PARAMETER, \
-        msv.TIMEVALUE , \
+        msv.TIMEVALUE \
     from MEDIAN.MESHLAYER ml, \
          MEDIAN.MESHPOINT, \
          MEDIAN.MESH m, \
@@ -482,7 +482,7 @@
 #############################################
 
 verticalprofile_instantaneouspoint_series = SELECT DISTINCT \
-        S.SERIESID KEY , \
+        S.SERIESID KEY, \
         S.DESCRIPTION VALUE \
     FROM MEDIAN.SERIES S, \
          MEDIAN.INSTANTANEOUSPOINT I, \
@@ -508,7 +508,7 @@
              MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE
 
 verticalprofile_instantaneouspoint_parameter = SELECT DISTINCT \
-        P.PARAMETERID KEY , \
+        P.PARAMETERID KEY, \
         P.GERMANNAME VALUE \
     FROM MEDIAN.PARAMETER P, \
          MEDIAN.INSTANTANEOUSPOINT IP, \
@@ -593,13 +593,36 @@
           I.FEATUREID =  M.FEATUREID AND \
           M.SOURCEID = ? \
     ORDER BY V.NAME
+
+horizontalprofile_instantaneouspoint_vehicle_with_area = SELECT V.VEHICLEID KEY, \
+                V.NAME VALUE \
+    FROM MEDIAN.TRACK, \
+         MEDIAN.VEHICLE V, \
+         MEDIAN.CRUISE C \
+    WHERE V.VEHICLEID = C.VEHICLEID AND \
+          C.CRUISEID = MEDIAN.TRACK.CRUISEID AND \
+          MEDIAN.TRACK.TRACKID IN \
+                      (SELECT DISTINCT S.TRACKID \
+                       FROM MEDIAN.MEASUREMENT M ,\
+                            MEDIAN.INSTANTANEOUSPOINT I, \
+                            MEDIAN.SURVEYINFO S \
+                       WHERE M.SOURCEID = ? AND \
+                             I.FEATUREID = M.FEATUREID AND \
+                             S.SURVEYID = I.SURVEYID)AND \
+                             INTERSECTS(SHAPE, \
+                                        (SELECT st_astext(SHAPE) \
+                                         FROM MEDIAN.FEATUREAREA \
+                                         WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \
+                                                FEATURECODE = ? )\
+                                         ) \
+    ORDER BY V.NAME
 horizontalprofile_instantaneouspoint_cruise = SELECT DISTINCT \
-        C.CRUISEID KEY , \
+        C.CRUISEID KEY, \
         V.NAME || ' ' || \
         C.NAME || ' ' || \
         TO_CHAR(C.STARTDATE,'DD.MM.YYYY') || ' - ' || \
-        TO_CHAR(C.ENDDATE,'DD.MM.YYYY') VALUE , \
-        V.NAME , \
+        TO_CHAR(C.ENDDATE,'DD.MM.YYYY') VALUE, \
+        V.NAME, \
         C.NAME \
      FROM MEDIAN.CRUISE C, \
          MEDIAN.VEHICLE V, \
@@ -615,16 +638,61 @@
           C.VEHICLEID  = ? AND \
           M.SOURCEID = ? \
     ORDER BY V.NAME, C.NAME
+
+horizontalprofile_instantaneouspoint_cruise_with_area = SELECT C.CRUISEID KEY, \
+        V.NAME || ' ' || \
+        C.NAME || ' ' || \
+        TO_CHAR(C.STARTDATE,'DD.MM.YYYY') || ' - ' || \
+        TO_CHAR(C.ENDDATE,'DD.MM.YYYY') VALUE, \
+        V.NAME, \
+        C.NAME \
+    FROM MEDIAN.TRACK, \
+         MEDIAN.VEHICLE V, \
+         MEDIAN.CRUISE C \
+    WHERE V.VEHICLEID = C.VEHICLEID AND \
+          C.CRUISEID = MEDIAN.TRACK.CRUISEID AND \
+          MEDIAN.TRACK.TRACKID IN \
+                      (SELECT DISTINCT S.TRACKID \
+                       FROM MEDIAN.MEASUREMENT M ,\
+                            MEDIAN.INSTANTANEOUSPOINT I, \
+                            MEDIAN.SURVEYINFO S \
+                       WHERE M.SOURCEID = ? AND \
+                             C.VEHICLEID  = ? AND \
+                             I.FEATUREID = M.FEATUREID AND \
+                             S.SURVEYID = I.SURVEYID)AND \
+                             INTERSECTS(SHAPE, \
+                                        (SELECT st_astext(SHAPE) \
+                                         FROM MEDIAN.FEATUREAREA \
+                                         WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \
+                                                FEATURECODE = ? )\
+                                         ) \
+    ORDER BY V.NAME
 horizontalprofile_instantaneouspoint_track= SELECT \
-        T.TRACKID KEY , \
+        T.TRACKID KEY, \
         to_char(T.STARTDATE,'DD.MM.YYYY HH24:MI') || ' - '|| to_char(T.ENDDATE,'DD.MM.YYYY HH24:MI') || ' - '|| T.NAME VALUE \
     FROM MEDIAN.TRACK T \
     WHERE T.CRUISEID = ? \
     ORDER BY T.STARTDATE, \
              T.ENDDATE, \
              T.NAME
+             
+horizontalprofile_instantaneouspoint_track_with_area = SELECT \
+        MEDIAN.TRACK.TRACKID KEY, \
+        to_char(STARTDATE,'DD.MM.YYYY HH24:MI') || ' - '|| to_char(ENDDATE,'DD.MM.YYYY HH24:MI') || ' - '|| NAME VALUE, \
+        NAME \
+    FROM MEDIAN.TRACK \
+    WHERE MEDIAN.TRACK.CRUISEID = ? AND \
+          INTERSECTS(SHAPE, \
+                    (SELECT st_astext(SHAPE) \
+                     FROM MEDIAN.FEATUREAREA \
+                     WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \
+                            FEATURECODE = ? )\
+                     ) \
+    ORDER BY STARTDATE, \
+             ENDDATE, \
+             NAME
 horizontalprofile_instantaneouspoint_surveyinfo = SELECT \
-        SURVEYID KEY , \
+        SURVEYID KEY, \
         to_char(STARTDATE,'DD.MM.YYYY HH24:MI') || ' - '|| to_char(ENDDATE,'DD.MM.YYYY HH24:MI') || ' - '|| DESCRIPTION VALUE \
     FROM MEDIAN.SURVEYINFO \
     WHERE TRACKID = ? \
@@ -632,11 +700,11 @@
              ENDDATE, \
              DESCRIPTION
 horizontalprofile_instantaneouspoint_parameter = SELECT DISTINCT \
-        P.PARAMETERID KEY , \
+        P.PARAMETERID KEY, \
         P.GERMANNAME VALUE \
-    FROM MEDIAN.PARAMETER P , \
-         MEDIAN.MEASUREMENT M , \
-         MEDIAN.INSTANTANEOUSPOINT IP , \
+    FROM MEDIAN.PARAMETER P, \
+         MEDIAN.MEASUREMENT M, \
+         MEDIAN.INSTANTANEOUSPOINT IP, \
          MEDIAN.MEASUREDSCALARVALUE MSV \
     WHERE P.PARAMETERID = MSV.PARAMETERID AND \
           MSV.MEASUREMENTID = M.MEASUREMENTID AND \
@@ -646,16 +714,16 @@
 horizontalprofile_instantaneouspoint_depth = SELECT DISTINCT \
         M.ZLOCATION KEY, \
         M.ZLOCATION VALUE \
-    FROM MEDIAN.MEASUREMENT M , \
+    FROM MEDIAN.MEASUREMENT M, \
          MEDIAN.INSTANTANEOUSPOINT IP \
     WHERE M.FEATUREID = IP.FEATUREID AND \
           IP.SURVEYID = ? \
     ORDER BY M.ZLOCATION
-horizontalprofile_instantaneouspoint_chart_data = SELECT ST_ASTEXT(SHAPE) , \
-        MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE TIME , \
-        MSV.DATAVALUE  YORDINATE , \
-        MSV.PARAMETERID GROUP1 , \
-        ZLOCATION GROUP2 , \
+horizontalprofile_instantaneouspoint_chart_data = SELECT ST_ASTEXT(SHAPE), \
+        MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE TIME, \
+        MSV.DATAVALUE  YORDINATE, \
+        MSV.PARAMETERID GROUP1, \
+        ZLOCATION GROUP2, \
         1 GROUP3 \
     FROM MEDIAN.INSTANTANEOUSPOINT, \
          MEDIAN.MEASUREMENT M, \
@@ -672,8 +740,8 @@
         MEDIAN.InstantaneousPoint.FEATUREID STATION, \
         '*' TYPE, \
         0 BOTDEPTH, \
-        (M.ZLOCATION * -1) DEPTH , \
-        ST_ASTEXT(SHAPE) , \
+        (M.ZLOCATION * -1) DEPTH, \
+        ST_ASTEXT(SHAPE), \
         MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE, \
         MSV.DATAVALUE, \
         MSV.PARAMETERID PARAMETER \
@@ -724,10 +792,10 @@
            msv.PARAMETERID GROUP1, \
            MEDIAN.MESHPOINT.KPOSITION GROUP2, \
            msv.TIMEVALUE GROUP3, \
-           MEDIAN.MESHPOINT.JPOSITION , \
+           MEDIAN.MESHPOINT.JPOSITION, \
            MEDIAN.MESHPOINT.IPOSITION \
     from MEDIAN.MESHLAYER ml, \
-         MEDIAN.MESHPOINT , \
+         MEDIAN.MESHPOINT, \
          MEDIAN.MESH m, \
          MEDIAN.MESHSCALARVALUE msv \
     where msv.FEATUREID = MEDIAN.MESHPOINT. FEATUREID AND \
@@ -750,7 +818,7 @@
    order by msv.TIMEVALUE, \
             MEDIAN.MESHPOINT.KPOSITION, \
             msv.PARAMETERID, \
-            MEDIAN.MESHPOINT.JPOSITION , \
+            MEDIAN.MESHPOINT.JPOSITION, \
             MEDIAN.MESHPOINT.IPOSITION
             
 horizontalprofile_mesh_odv_data =  SELECT SI.NAME CRUISE, \
@@ -759,13 +827,13 @@
           ST_ASTEXT(SHAPE), \
           0 BOTDEPTH, \
           (((ML.UPPERZLOCATION + ML.LOWERZLOCATION) / 2)*-1) DEPTH, \
-          msv.DATAVALUE , \
+          msv.DATAVALUE, \
           msv.PARAMETERID PARAMETER, \
           msv.TIMEVALUE, \
-          MEDIAN.MESHPOINT.JPOSITION , \
+          MEDIAN.MESHPOINT.JPOSITION, \
           MEDIAN.MESHPOINT.IPOSITION \
     from MEDIAN.MESHLAYER ml, \
-         MEDIAN.MESHPOINT , \
+         MEDIAN.MESHPOINT, \
          MEDIAN.MESH m, \
          MEDIAN.MESHSCALARVALUE msv, \
          MEDIAN.SOURCEINFO SI \
@@ -790,7 +858,7 @@
    order by msv.TIMEVALUE, \
             MEDIAN.MESHPOINT.KPOSITION, \
             msv.PARAMETERID, \
-            MEDIAN.MESHPOINT.JPOSITION , \
+            MEDIAN.MESHPOINT.JPOSITION, \
             MEDIAN.MESHPOINT.IPOSITION
              
              
@@ -802,7 +870,7 @@
 verticalcrosssection_mesh_date = select  distinct \
         msv.TIMEVALUE KEY, \
         msv.TIMEVALUE VALUE \
-    from MEDIAN.MESHSCALARVALUE msv , \
+    from MEDIAN.MESHSCALARVALUE msv, \
          MEDIAN.MESH m \
     where m.OBJECTID = ?  AND \
           msv.PARTID >= m.PARTIDMIN AND \
@@ -817,7 +885,7 @@
            MEDIAN.MESHPOINT.IPOSITION, \
            MEDIAN.MESHPOINT.KPOSITION \
     from MEDIAN.MESHLAYER ml, \
-         MEDIAN.MESHPOINT , \
+         MEDIAN.MESHPOINT, \
          MEDIAN.MESH m, \
          MEDIAN.MESHSCALARVALUE msv \
     where msv.FEATUREID = MEDIAN.MESHPOINT. FEATUREID AND \
@@ -838,7 +906,7 @@
                     ? = (select ? from MEDIAN.MESHPOINT where FEATUREID = ?)) \
     order by msv.TIMEVALUE, \
              msv.PARAMETERID, \
-             MEDIAN.MESHPOINT.JPOSITION , \
+             MEDIAN.MESHPOINT.JPOSITION, \
              MEDIAN.MESHPOINT.IPOSITION, \
              MEDIAN.MESHPOINT.KPOSITION
              
@@ -849,13 +917,13 @@
            0 BOTDEPTH, \
            (((ML.UPPERZLOCATION + ML.LOWERZLOCATION) / 2)*-1) DEPTH, \
            MSV.TIMEVALUE, \
-           MSV.DATAVALUE , \
+           MSV.DATAVALUE, \
            MSV.PARAMETERID PARAMETER, \
            MEDIAN.MESHPOINT.JPOSITION, \
            MEDIAN.MESHPOINT.IPOSITION, \
            MEDIAN.MESHPOINT.KPOSITION \
     from MEDIAN.MESHLAYER ml, \
-         MEDIAN.MESHPOINT , \
+         MEDIAN.MESHPOINT, \
          MEDIAN.MESH m, \
          MEDIAN.SOURCEINFO SI, \
          MEDIAN.MESHSCALARVALUE msv \
@@ -878,7 +946,7 @@
                     ? = (select ? from MEDIAN.MESHPOINT where FEATUREID = ?)) \
     order by msv.TIMEVALUE, \
              msv.PARAMETERID, \
-             MEDIAN.MESHPOINT.JPOSITION , \
+             MEDIAN.MESHPOINT.JPOSITION, \
              MEDIAN.MESHPOINT.IPOSITION, \
              MEDIAN.MESHPOINT.KPOSITION
              
@@ -901,7 +969,7 @@
 horizontalcrosssection_mesh_date = select  distinct \
         msv.TIMEVALUE KEY, \
         msv.TIMEVALUE VALUE \
-    from MEDIAN.MESHSCALARVALUE msv , \
+    from MEDIAN.MESHSCALARVALUE msv, \
          MEDIAN.MESH m \
     where m.OBJECTID = ?  AND \
           msv.PARTID >= m.PARTIDMIN AND \
@@ -928,7 +996,7 @@
           MSV.TIMEVALUE = ? AND \
           M.OBJECTID = ? AND \
           MEDIAN.MESHFACE.KPOSITION = ? \
-    order by MEDIAN.MESHFACE.JPOSITION , \
+    order by MEDIAN.MESHFACE.JPOSITION, \
              MEDIAN.MESHFACE.IPOSITION
 horizontalcrosssection_mesh_odv_data = SELECT SI.NAME CRUISE, \
             M.MESHID || '-' || MEDIAN.MESHPOINT.IPOSITION || '-' || MEDIAN.MESHPOINT.JPOSITION STATION, \
@@ -937,7 +1005,7 @@
            0 BOTDEPTH, \
            (((ML.UPPERZLOCATION + ML.LOWERZLOCATION) / 2)*-1) DEPTH, \
            MSV.TIMEVALUE, \
-           MSV.DATAVALUE , \
+           MSV.DATAVALUE, \
            MSV.PARAMETERID PARAMETER, \
            MEDIAN.MESHPOINT.JPOSITION, \
            MEDIAN.MESHPOINT.IPOSITION, \
@@ -958,7 +1026,7 @@
           MSV.TIMEVALUE = ? AND \
           M.OBJECTID = ? AND \
           MEDIAN.MESHPOINT.KPOSITION = ? \
-    order by MEDIAN.MESHPOINT.JPOSITION , \
+    order by MEDIAN.MESHPOINT.JPOSITION, \
              MEDIAN.MESHPOINT.IPOSITION
              
 area_filter = SELECT DISTINCT \
http://dive4elements.wald.intevation.org