Mercurial > dive4elements > gnv-client

view gnv-artifacts/src/test/ressources/queries.properties @ 120:5134266f00e7

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Resolved gnv/issue21 No NPE will be thrown. Add Dummydata for Calculating the distance between two MeasurmentPoints. gnv-artifacts/trunk@190 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Tim Englich <tim.englich@intevation.de>
date Tue, 06 Oct 2009 10:58:47 +0000
parents 4841808819d9
children 7be22e76c270
line wrap: on
line source
#############################################
#############################################
##########     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 > ? AND \
          tv.TIMEVALUE < ? \
    ORDER BY tv.MEASUREMENTID , \
             tv.TIMESERIESID , \
             t.PARAMETERID , \
             tv.TIMEVALUE

#############################################
#############################################
##########   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_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 \
    order by p.GERMANNAME
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 in ( ? ) AND \
          msv.TIMEVALUE >= ? AND \
          msv.TIMEVALUE <= ? \
    order by mp.FEATUREID, \
             msv.FEATUREID, \
             msv.PARAMETERID, \
             msv.TIMEVALUE

#############################################
#############################################
##########   Vertikalprofil    ##############
#############################################
#############################################

verticalprofile_point=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

verticalprofile_parameter=SELECT DISTINCT \
           p.PARAMETERID KEY, \
           p.GERMANNAME VALUE \
    from MEDIAN.PARAMETER p, \
           MEDIAN.TIMESERIES ts, \
           MEDIAN.TIMESERIESVALUE tsv, \
           MEDIAN.MEASUREMENT m \
    where ts.PARAMETERID = p.PARAMETERID and \
          ts.TIMESERIESID = tsv.TIMESERIESID and \
          m.MEASUREMENTID = tsv.MEASUREMENTID and \
          m.FEATUREID = ? \
    ORDER BY p.GERMANNAME

verticalprofile_date=select distinct \
           tsv.TIMEVALUE KEY, \
           tsv.TIMEVALUE VALUE \
    from MEDIAN.TIMESERIES ts , \
         MEDIAN.TIMESERIESVALUE tsv, \
         MEDIAN.MEASUREMENT m \
    where ts.TIMESERIESID = tsv.TIMESERIESID and \
          m.MEASUREMENTID = tsv.MEASUREMENTID and \
          m.FEATUREID = ? and \
          ts.PARAMETERID IN ( ? ) \
    order by tsv.TIMEVALUE 

verticalprofile_chart_data= SELECT m.ZLOCATION XORDINATE, \
           tsv.DATAVALUE YORDINATE, \
           ts.PARAMETERID GROUP1, \
           tsv.TIMEVALUE GROUP2, \
           1 GROUP3 \
    from MEDIAN.TIMESERIES ts, \
         MEDIAN.TIMESERIESVALUE tsv, \
         MEDIAN.MEASUREMENT m \
    where ts.TIMESERIESID = tsv.TIMESERIESID and \
          m.MEASUREMENTID = tsv.MEASUREMENTID and \
          m.FEATUREID = ? and \
          ts.PARAMETERID IN ( ? ) AND \
          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
            
#############################################
#############################################
##### Vertikalprofil InstantaneousPoint #####
#############################################
#############################################

verticalprofile_instantaneouspoint_series = SELECT DISTINCT \
        S.SERIESID KEY , \
        S.DESCRIPTION VALUE \
    FROM MEDIAN.SERIES S, \
         MEDIAN.INSTANTANEOUSPOINT I, \
         MEDIAN.MEASUREMENT M \
    WHERE S.SERIESID = I.SERIESID AND \
          I.POINTSPEC = 4 AND \
          I.FEATUREID = M.FEATUREID AND \
          M.SOURCEID= ? \
    ORDER BY S.DESCRIPTION
    
verticalprofile_instantaneouspoint_point = SELECT DISTINCT \
        I.FEATUREID KEY, \
        to_char(I.TIMEVALUE,'DD.MM.YYYY HH24:MI') || ' - '|| I.SHAPE VALUE, \
        I.TIMEVALUE \
    FROM MEDIAN.INSTANTANEOUSPOINT I, \
         MEDIAN.MEASUREMENT M \
    WHERE I.SERIESID = ? AND \
          I.POINTSPEC = 4 AND \
          I.FEATUREID = M.FEATUREID AND \
          M.SOURCEID = ? \
    ORDER BY I.TIMEVALUE

verticalprofile_instantaneouspoint_parameter = SELECT DISTINCT \
        P.PARAMETERID KEY , \
        P.GERMANNAME VALUE \
    FROM MEDIAN.PARAMETER P, \
         MEDIAN.INSTANTANEOUSPOINT IP, \
         MEDIAN.MEASUREMENT M, \
         MEDIAN.MEASUREDSCALARVALUE MSV \
    WHERE IP.FEATUREID = M.FEATUREID AND \
          M.MEASUREMENTID = MSV.MEASUREMENTID AND \
          MSV.PARAMETERID = P.PARAMETERID AND \
          IP.FEATUREID = ? \
    ORDER BY P.GERMANNAME

verticalprofile_instantaneouspoint_chart_data = SELECT M.ZLOCATION XORDINATE, \
        MSV.DATAVALUE  YORDINATE, \
        MSV.PARAMETERID GROUP1, \
        IP.TIMEVALUE GROUP2, \
        1 GROUP3 \
    FROM MEDIAN.INSTANTANEOUSPOINT IP, \
         MEDIAN.MEASUREMENT M, \
         MEDIAN.MEASUREDSCALARVALUE MSV \
    WHERE IP.FEATUREID = M.FEATUREID AND \
          M.MEASUREMENTID = MSV.MEASUREMENTID AND \
          IP.FEATUREID = ? AND \
          MSV.PARAMETERID in (?) \
    ORDER BY IP.TIMEVALUE, \
          MSV.PARAMETERID, \
          M.ZLOCATION

#############################################
#############################################
#### Horizontalprofil InstantaneousPoint ####
#############################################
#############################################
horizontalprofile_instantaneouspoint_vehicle = SELECT DISTINCT \
        V.VEHICLEID KEY, \
        V.NAME VALUE \
    FROM MEDIAN.VEHICLE V, \
         MEDIAN.CRUISE C, \
         MEDIAN.TRACK T, \
         MEDIAN.SURVEYINFO S, \
         MEDIAN.INSTANTANEOUSPOINT I, \
         MEDIAN.MEASUREMENT M \
    WHERE V.VEHICLEID = C.VEHICLEID AND \
          C.CRUISEID = T.CRUISEID AND \
          T.TRACKID = S.TRACKID AND \
          S.SURVEYID  = I.SURVEYID AND \
          I.FEATUREID =  M.FEATUREID AND \
          M.SOURCEID = ? \
    ORDER BY V.NAME
horizontalprofile_instantaneouspoint_cruise = SELECT DISTINCT \
        C.CRUISEID KEY , \
        C.NAME VALUE \
     FROM MEDIAN.CRUISE C, \
         MEDIAN.TRACK T, \
         MEDIAN.SURVEYINFO S, \
         MEDIAN.INSTANTANEOUSPOINT I, \
         MEDIAN.MEASUREMENT M \
    WHERE C.CRUISEID = T.CRUISEID AND \
          T.TRACKID = S.TRACKID AND \
          S.SURVEYID  = I.SURVEYID AND \
          I.FEATUREID =  M.FEATUREID AND \
          C.VEHICLEID  = ? AND \
          M.SOURCEID = ? AND \
          C.NAME IS NOT NULL \
    ORDER BY C.NAME
horizontalprofile_instantaneouspoint_track= SELECT \
        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_surveyinfo = SELECT \
        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 = ? \
    ORDER BY STARTDATE, \
             ENDDATE, \
             DESCRIPTION
horizontalprofile_instantaneouspoint_parameter = SELECT DISTINCT \
        P.PARAMETERID KEY , \
        P.GERMANNAME VALUE \
    FROM MEDIAN.PARAMETER P , \
         MEDIAN.MEASUREMENT M , \
         MEDIAN.INSTANTANEOUSPOINT IP , \
         MEDIAN.MEASUREDSCALARVALUE MSV \
    WHERE P.PARAMETERID = MSV.PARAMETERID AND \
          MSV.MEASUREMENTID = M.MEASUREMENTID AND \
          M.FEATUREID = IP.FEATUREID AND \
          IP.SURVEYID = ? \
    ORDER BY P.GERMANNAME
horizontalprofile_instantaneouspoint_depth = SELECT DISTINCT \
        M.ZLOCATION KEY, \
        M.ZLOCATION VALUE \
    FROM MEDIAN.MEASUREMENT M , \
         MEDIAN.INSTANTANEOUSPOINT IP \
    WHERE M.FEATUREID = IP.FEATUREID AND \
          IP.SURVEYID = ? \
    ORDER BY M.ZLOCATION
horizontalprofile_instantaneouspoint_chart_data = SELECT MSV.OBJECTID XORDINATE_XCOORD , \
        MSV.OBJECTID XORDINATE_YCOORD , \
        MSV.DATAVALUE  YORDINATE , \
        MSV.PARAMETERID GROUP1 , \
        ZLOCATION GROUP2 , \
        1 GROUP3 \
    FROM MEDIAN.INSTANTANEOUSPOINT IP, \
         MEDIAN.MEASUREMENT M, \
         MEDIAN.MEASUREDSCALARVALUE MSV \
    WHERE IP.FEATUREID = M.FEATUREID AND \
          M.MEASUREMENTID = MSV.MEASUREMENTID AND \
          IP.SURVEYID = ? AND \
          M.ZLOCATION IN (?) AND \
          MSV.PARAMETERID in (?) \
    ORDER BY MSV.PARAMETERID, \
             M.ZLOCATION
             
#############################################
#############################################
########### Horizontalprofil MESH ###########
#############################################
#############################################
horizontalprofile_meshpoint_depth = SELECT DISTINCT \
         mp.KPOSITION KEY, \
        -ml.UPPERZLOCATION || ' - '|| -ml.LOWERZLOCATION 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 mp.KPOSITION
    
horizontalprofile_mesh_chart_data = select mp.IPOSITION XORDINATE_XCOORD, mp.JPOSITION XORDINATE_YCOORD, \
           msv.DATAVALUE YORDINATE, \
           msv.PARAMETERID GROUP1, \
           msv.TIMEVALUE GROUP2, \
           mp.KPOSITION 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 \
                    KPOSITION in ( ? ) and \
                    ? = (select ? from MEDIAN.MESHPOINT where FEATUREID = ?)) \
   order by mp.KPOSITION, \
            msv.TIMEVALUE, \
            msv.PARAMETERID, \
            mp.JPOSITION , \
            mp.IPOSITION
http://dive4elements.wald.intevation.org