view gnv-artifacts/doc/conf/queries.properties @ 469:62fc63d0f71d

Added a new State in Product Verticalprofile in Timeseriespoints. Now it will be displayed the Years where measurements happened and than only the dates of the chosen Year will be fetched and displayed. gnv-artifacts/trunk@532 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Tim Englich <tim.englich@intevation.de>
date Tue, 12 Jan 2010 12:42:53 +0000
parents 363236fc462d
children 64e65daa65e9
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_timeseriespoint_with_area=SELECT \
        MEDIAN.TIMESERIESPOINT.FEATUREID KEY, \
        MEDIAN.TIMESERIESPOINT.NAME VALUE \
    FROM MEDIAN.TIMESERIESPOINT, \
         MEDIAN.MEASUREMENT MMT \
    WHERE MEDIAN.TIMESERIESPOINT.FEATUREID =  MMT.FEATUREID AND \
          MMT.SOURCEID = ? AND \
          INTERSECTS(SHAPE, \
                      (SELECT st_astext(SHAPE) \
                       FROM MEDIAN.FEATUREAREA \
                       WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \
                              FEATURECODE = ? )\
                      )\
    ORDER BY MEDIAN.TIMESERIESPOINT.NAME

timeseries_parameter=SELECT DISTINCT \
        p.PARAMETERID KEY, \
        p.GERMANNAME || '  ['|| p.UNIT ||']' VALUE, \
        p.GERMANNAME \
    FROM MEDIAN.PARAMETER P, \
         MEDIAN.TIMESERIES TS, \
         MEDIAN.TIMESERIESVALUE TSV, \
         MEDIAN.MEASUREMENT M, \
         MEDIAN.TIMESERIESPOINT TSP \
    WHERE M.FEATUREID = TSP.FEATUREID AND \
          M.MEASUREMENTID = TSV.MEASUREMENTID AND \
          TS.TIMESERIESID = TSV.TIMESERIESID AND \
          P.PARAMETERID = TS.PARAMETERID AND \
          TSP.FEATUREID = ? \
    ORDER BY P.GERMANNAME
    
timeseries_depth_height=SELECT DISTINCT \
        M.MEASUREMENTID KEY, \
        M.ZLOCATION VALUE \
    FROM MEDIAN.MEASUREMENT M, \
         MEDIAN.TIMESERIESVALUE TSV, \
         MEDIAN.TIMESERIES T, \
         MEDIAN.PARAMETER P \
    WHERE M.MEASUREMENTID = TSV.MEASUREMENTID AND \
          TSV.TIMESERIESID = T.TIMESERIESID AND \
          T.PARAMETERID = P.PARAMETERID AND \
          M.FEATUREID = ? 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, \
        t.TIMEINTERVAL GAPID \
    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
             
timeseries_odv_data = SELECT SI.NAME CRUISE, \
            MEDIAN.TIMESERIESPOINT.NAME STATION, \
            '*' TYPE, \
            ST_ASTEXT(SHAPE), \
            0 BOTDEPTH, \
            (M.ZLOCATION *-1) DEPTH, \
            TSV.TIMEVALUE, \
            TSV.DATAVALUE, \
            TS.PARAMETERID PARAMETER, \
            TSV.MEASUREMENTID, \
            TSV.TIMESERIESID \
     FROM MEDIAN.TIMESERIESPOINT, \
          MEDIAN.TIMESERIESVALUE TSV, \
          MEDIAN.TIMESERIES TS, \
          MEDIAN.MEASUREMENT M, \
          MEDIAN.SOURCEINFO SI \
     WHERE SI.SOURCEID = M.SOURCEID AND \
           M.FEATUREID = MEDIAN.TIMESERIESPOINT.FEATUREID AND \
           M.MEASUREMENTID = TSV.MEASUREMENTID AND \
           TSV.TIMESERIESID = TS.TIMESERIESID AND \
           TS.PARAMETERID IN ( ? ) AND \
           TSV.MEASUREMENTID IN ( ? ) AND \
           TSV.TIMEVALUE >= ? AND \
           TSV.TIMEVALUE <= ? \
     ORDER BY TSV.MEASUREMENTID, \
              TSV.TIMESERIESID, \
              TS.PARAMETERID, \
              TSV.TIMEVALUE

#############################################
#############################################
##########   Zeitserie Mesh    ##############
#############################################
#############################################

timeseries_mesh = SELECT OBJECTID KEY, \
        m.NAME VALUE \
    FROM MEDIAN.MESH m \
    WHERE SOURCEID IN (?) \
    ORDER BY m.NAME
timeseries_meshpoint = SELECT MEDIAN.MESHPOINT.FEATUREID, \
        ST_ASTEXT(SHAPE) \
    FROM MEDIAN.MESHPOINT, \
         MEDIAN.MESH M \
    WHERE MEDIAN.MESHPOINT.MESHID = M.MESHID AND \
         KPOSITION = 1 AND \
         M.OBJECTID = ? AND \
         INTERSECTS(SHAPE,"?")

timeseries_meshpoint_depth = SELECT MP.FEATUREID KEY, \
        'Layer ' || ML.KPOSITION || ': ' || -ML.UPPERZLOCATION || ' - '|| -ML.LOWERZLOCATION VALUE \
    from MEDIAN.MESHLAYER ML, \
         MEDIAN.MESHPOINT MP, \
         MEDIAN.MESH M \
    WHERE ML.KPOSITION = MP.KPOSITION AND \
          ML.MESHID = MP.MESHID AND \
          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 || '  ['|| p.UNIT ||']' VALUE, \
        p.GERMANNAME \
    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 AND \
          msc.PARAMETERID IN (?)

timeseries_mesh_chart_data=select /*+ parallel(timevalue,10) */ \
        msv.TIMEVALUE XORDINATE, \
        msv.DATAVALUE YORDINATE, \
        msv.PARAMETERID GROUP1, \
        msv.FEATUREID GROUP2, \
        mp.FEATUREID GROUP3, \
        -1 GAPID \
    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 = mp.FEATUREID AND \
          msv.PARAMETERID in ( ? ) AND \
          mp.FEATUREID in ( ? ) AND \
          msv.TIMEVALUE >= ? AND \
          msv.TIMEVALUE <= ? \
    order by mp.FEATUREID, \
             msv.FEATUREID, \
             msv.PARAMETERID, \
             msv.TIMEVALUE
             
timeseries_mesh_odv_data = select /*+ parallel(timevalue,10) */ \
        SI.NAME CRUISE, \
        m.MESHID || '-' || MEDIAN.MESHPOINT.IPOSITION || '-' || MEDIAN.MESHPOINT.JPOSITION STATION, \
        '*' TYPE, \
        ST_ASTEXT(SHAPE), \
        0 BOTDEPTH, \
        (((ML.UPPERZLOCATION + ML.LOWERZLOCATION) / 2)*-1) DEPTH, \
        msv.TIMEVALUE, \
        msv.DATAVALUE, \
        msv.PARAMETERID PARAMETER \
    from MEDIAN.MESHSCALARVALUE msv, \
         MEDIAN.MESHPOINT, \
         MEDIAN.MESH m, \
         MEDIAN.SOURCEINFO SI, \
         MEDIAN.MESHLAYER ML \
    where (m.OBJECTID = ? AND \
           msv.PARTID >= m.PARTIDMIN AND \
           msv.PARTID <= m.PARTIDMAX ) AND \
          m.SOURCEID = SI.SOURCEID AND \
          ML.MESHID = MEDIAN.MESHPOINT.MESHID AND \
          ML.KPOSITION = MEDIAN.MESHPOINT.KPOSITION AND \
          msv.FEATUREID = MEDIAN.MESHPOINT.FEATUREID AND \
          msv.PARAMETERID in (? ) AND \
          MEDIAN.MESHPOINT.FEATUREID in ( ? ) AND \
          msv.TIMEVALUE >= ? AND \
          msv.TIMEVALUE <= ? \
    order by MEDIAN.MESHPOINT.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 || '  ['|| p.UNIT ||']' VALUE, \
           p.GERMANNAME \
    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_minmaxdepth= SELECT min(M.ZLOCATION) MIN, \
           max(M.ZLOCATION) MAX \
    FROM MEDIAN.MEASUREMENT M, \
         MEDIAN.TIMESERIES TS, \
         MEDIAN.TIMESERIESVALUE TSV \
    WHERE TS.TIMESERIESID = TSV.TIMESERIESID and \
          M.MEASUREMENTID = TSV.MEASUREMENTID and \
          M.FEATUREID = ? and \
          TS.PARAMETERID IN ( ? )
          
          
verticalprofile_year=select distinct \
        to_char(tsv.TIMEVALUE,'YYYY') KEY, \
        to_char(tsv.TIMEVALUE,'YYYY') 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 to_char(tsv.TIMEVALUE,'YYYY')
          
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 ( ? ) and \
          to_char(tsv.TIMEVALUE,'YYYY') IN (?) \
    order by tsv.TIMEVALUE 

verticalprofile_chart_data= SELECT M.ZLOCATION XORDINATE, \
           TSV.DATAVALUE YORDINATE, \
           TS.PARAMETERID GROUP1, \
           TSV.TIMEVALUE GROUP2, \
           1 GROUP3, \
           1 DATAID \
    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 (?) AND \
          M.ZLOCATION >= ? AND \
          M.ZLOCATION <= ? \
    ORDER BY TSV.TIMEVALUE, \
             TSV.TIMESERIESID, \
             TS.PARAMETERID, \
             TSV.TIMEVALUE
             
verticalprofile_odv_data = SELECT SI.NAME CRUISE, \
            MEDIAN.TIMESERIESPOINT.NAME STATION, \
            '*' TYPE, \
            ST_ASTEXT(SHAPE), \
            0 BOTDEPTH, \
            (M.ZLOCATION *-1) DEPTH, \
            TSV.TIMEVALUE, \
            TSV.DATAVALUE, \
            TS.PARAMETERID PARAMETER, \
            TSV.MEASUREMENTID, \
            TSV.TIMESERIESID \
     FROM MEDIAN.TIMESERIESPOINT, \
          MEDIAN.TIMESERIESVALUE TSV, \
          MEDIAN.TIMESERIES TS, \
          MEDIAN.MEASUREMENT M, \
          MEDIAN.SOURCEINFO SI \
     WHERE SI.SOURCEID = M.SOURCEID AND \
           M.FEATUREID = MEDIAN.TIMESERIESPOINT.FEATUREID AND \
           M.MEASUREMENTID = TSV.MEASUREMENTID AND \
           TSV.TIMESERIESID = TS.TIMESERIESID AND \
           M.FEATUREID = ? AND \
           TS.PARAMETERID IN ( ? ) AND \
           TSV.TIMEVALUE IN (?) AND \
           M.ZLOCATION >= ? AND \
           M.ZLOCATION <= ? \
     ORDER BY TSV.MEASUREMENTID, \
              TSV.TIMESERIESID, \
              TS.PARAMETERID, \
              TSV.TIMEVALUE
             
#############################################
#############################################
########## Vertikalprofil Mesh ##############
#############################################
#############################################

verticalprofile_mesh = SELECT OBJECTID KEY, \
        m.NAME VALUE \
    FROM MEDIAN.MESH m \
    WHERE SOURCEID IN (?) \
    order by m.NAME
    
verticalprofile_mesh_with_area = SELECT M.OBJECTID KEY, \
         M.NAME VALUE \
     FROM MEDIAN.MESHPOINT, \
          MEDIAN.MESH M \
     WHERE M.SOURCEID IN (?) AND \
           M.MESHID = MEDIAN.MESHPOINT.MESHID AND \
           INTERSECTS(SHAPE, \
                      (SELECT st_astext(SHAPE) \
                       FROM MEDIAN.FEATUREAREA \
                       WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \
                              FEATURECODE = ? )\
                      ) \
     order by M.NAME
    
verticalprofile_mesh_point = SELECT FEATUREID, \
        ST_ASTEXT(SHAPE) \
    FROM MEDIAN.MESHPOINT \
    WHERE MESHID  in \
        (SELECT DISTINCT MESHID \
         FROM MEDIAN.MESH \
         WHERE OBJECTID = ?) AND \
         KPOSITION = 1 AND \
         INTERSECTS(SHAPE,"?")
          
verticalprofile_mesh_mindepth = SELECT MP.KPOSITION KEY, \
        'Layer ' || ML.KPOSITION || ': ' || -ML.UPPERZLOCATION || ' - '|| -ML.LOWERZLOCATION VALUE \
    from MEDIAN.MESHLAYER ML, \
         MEDIAN.MESHPOINT MP, \
         MEDIAN.MESH M \
    WHERE ML.KPOSITION = MP.KPOSITION AND \
          ML.MESHID = MP.MESHID AND \
          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

verticalprofile_mesh_maxdepth = SELECT MP.KPOSITION KEY, \
        'Layer ' || ML.KPOSITION || ': ' || -ML.UPPERZLOCATION || ' - '|| -ML.LOWERZLOCATION VALUE \
    from MEDIAN.MESHLAYER ML, \
         MEDIAN.MESHPOINT MP, \
         MEDIAN.MESH M \
    WHERE ML.KPOSITION = MP.KPOSITION AND \
          ML.MESHID = MP.MESHID AND \
          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 = ?) AND \
          MP.KPOSITION < ? \
    ORDER BY ML.UPPERZLOCATION DESC
    
verticalprofile_mesh_parameter=SELECT distinct \
        p.PARAMETERID KEY, \
        p.GERMANNAME || '  ['|| p.UNIT ||']' VALUE, \
        p.GERMANNAME \
    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 AND \
          msv.PARAMETERID in (?) \
    order by msv.TIMEVALUE

verticalprofile_mesh_chart_data=select ml.UPPERZLOCATION XORDINATE, \
           msv.DATAVALUE YORDINATE, \
           mp.KPOSITION KPOSITION, \
           msv.PARAMETERID GROUP1, \
           msv.TIMEVALUE GROUP2, \
           1 GROUP3, \
           2 DATAID \
    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 = ?)) AND \
          mp.KPOSITION <= ? AND \
          mp.KPOSITION >= ? \
   order by msv.PARAMETERID, \
            msv.TIMEVALUE, \
            ml.UPPERZLOCATION
            
verticalprofile_mesh_odv_data=select SI.NAME CRUISE, \
        m.MESHID || '-' || MEDIAN.MESHPOINT.IPOSITION || '-' || MEDIAN.MESHPOINT.JPOSITION STATION, \
        '*' TYPE, \
        ST_ASTEXT(SHAPE), \
        0 BOTDEPTH, \
        (((ML.UPPERZLOCATION + ML.LOWERZLOCATION) / 2)*-1) DEPTH, \
        msv.DATAVALUE, \
        msv.PARAMETERID PARAMETER, \
        msv.TIMEVALUE \
    from MEDIAN.MESHLAYER ml, \
         MEDIAN.MESHPOINT, \
         MEDIAN.MESH m, \
         MEDIAN.MESHSCALARVALUE msv, \
         MEDIAN.SOURCEINFO SI \
    where m.SOURCEID = SI.SOURCEID AND \
          msv.FEATUREID = MEDIAN.MESHPOINT. FEATUREID AND \
          ml.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \
          ml.MESHID = MEDIAN.MESHPOINT.MESHID and \
          m.MESHID = MEDIAN.MESHPOINT.MESHID AND \
          m.PARTIDMIN <= msv.PARTID AND \
          m.PARTIDMAX >= msv.PARTID AND \
          msv.PARAMETERID in (?) AND \
          msv.TIMEVALUE in (?) AND \
          m.OBJECTID = ? AND \
          MEDIAN.MESHPOINT.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 = ?)) AND \
          MEDIAN.MESHPOINT.KPOSITION <= ? AND \
          MEDIAN.MESHPOINT.KPOSITION >= ? \
    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_series_with_area = SELECT \
        S.SERIESID KEY, \
        S.DESCRIPTION VALUE \
    FROM MEDIAN.INSTANTANEOUSPOINT, \
         MEDIAN.SERIES S, \
         MEDIAN.MEASUREMENT M \
    WHERE S.SERIESID = MEDIAN.INSTANTANEOUSPOINT.SERIESID AND \
          MEDIAN.INSTANTANEOUSPOINT.POINTSPEC = 4 AND \
          MEDIAN.INSTANTANEOUSPOINT.FEATUREID = M.FEATUREID AND \
          M.SOURCEID= ? AND \
          INTERSECTS(SHAPE, \
                     (SELECT st_astext(SHAPE) \
                      FROM MEDIAN.FEATUREAREA \
                      WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \
                             FEATURECODE = ? )\
                     ) \
    ORDER BY S.DESCRIPTION
    
verticalprofile_instantaneouspoint_point = SELECT \
        MEDIAN.INSTANTANEOUSPOINT.FEATUREID, \
        to_char(MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE,'DD.MM.YYYY HH24:MI') VALUE, \
        MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE, \
         ST_ASTEXT(SHAPE) \
    FROM MEDIAN.INSTANTANEOUSPOINT, \
         MEDIAN.MEASUREMENT M \
    WHERE MEDIAN.INSTANTANEOUSPOINT.SERIESID = ? AND \
          MEDIAN.INSTANTANEOUSPOINT.POINTSPEC = 4 AND \
          MEDIAN.INSTANTANEOUSPOINT.FEATUREID = M.FEATUREID AND \
          M.SOURCEID = ? \
    ORDER BY MEDIAN.INSTANTANEOUSPOINT.FEATUREID, \
             MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE

verticalprofile_instantaneouspoint_parameter = SELECT DISTINCT \
        P.PARAMETERID KEY, \
        p.GERMANNAME || '  ['|| p.UNIT ||']' VALUE, \
        p.GERMANNAME \
    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_minmaxdepth = SELECT min(M.ZLOCATION) MIN, \
        max(M.ZLOCATION) MAX  \
    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 (?)
          
verticalprofile_instantaneouspoint_chart_data = SELECT M.ZLOCATION XORDINATE, \
        MSV.DATAVALUE  YORDINATE, \
        MSV.PARAMETERID GROUP1, \
        IP.TIMEVALUE GROUP2, \
        1 GROUP3, \
        3 DATAID \
    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 (?) AND \
          M.ZLOCATION >= ? AND \
          M.ZLOCATION <= ? \
    ORDER BY IP.TIMEVALUE, \
          MSV.PARAMETERID, \
          M.ZLOCATION
          
verticalprofile_instantaneouspoint_odv_data = SELECT S.DESCRIPTION CRUISE, \
        MEDIAN.INSTANTANEOUSPOINT.FEATUREID STATION, \
        '*' TYPE, \
        ST_ASTEXT(SHAPE), \
        0 BOTDEPTH, \
        (M.ZLOCATION * -1) DEPTH, \
        MSV.DATAVALUE, \
        MSV.PARAMETERID PARAMETER, \
        MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE \
    FROM MEDIAN.SERIES S, \
         MEDIAN.INSTANTANEOUSPOINT, \
         MEDIAN.MEASUREMENT M, \
         MEDIAN.MEASUREDSCALARVALUE MSV \
    WHERE S.SERIESID = MEDIAN.INSTANTANEOUSPOINT.SERIESID AND \
          MEDIAN.INSTANTANEOUSPOINT.FEATUREID = M.FEATUREID AND \
          M.MEASUREMENTID = MSV.MEASUREMENTID AND \
          MEDIAN.INSTANTANEOUSPOINT.FEATUREID = ? AND \
          MSV.PARAMETERID in (?) AND \
          M.ZLOCATION >= ? AND \
          M.ZLOCATION <= ? \
    ORDER BY MEDIAN.INSTANTANEOUSPOINT.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_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, \
        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.CRUISE C, \
         MEDIAN.VEHICLE V, \
         MEDIAN.TRACK T, \
         MEDIAN.SURVEYINFO S, \
         MEDIAN.INSTANTANEOUSPOINT I, \
         MEDIAN.MEASUREMENT M \
    WHERE C.VEHICLEID = V.VEHICLEID AND \
          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 = ? \
    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, \
        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, \
        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 || '  ['|| p.UNIT ||']' VALUE, \
        p.GERMANNAME \
    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 ST_ASTEXT(SHAPE), \
        MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE TIME, \
        MSV.DATAVALUE  YORDINATE, \
        MSV.PARAMETERID GROUP1, \
        ZLOCATION GROUP2, \
        1 GROUP3, \
        3 DATAID \
    FROM MEDIAN.INSTANTANEOUSPOINT, \
         MEDIAN.MEASUREMENT M, \
         MEDIAN.MEASUREDSCALARVALUE MSV \
    WHERE MEDIAN.INSTANTANEOUSPOINT.FEATUREID = M.FEATUREID AND \
          M.MEASUREMENTID = MSV.MEASUREMENTID AND \
          MEDIAN.INSTANTANEOUSPOINT.SURVEYID = ? AND \
          M.ZLOCATION IN (?) AND \
          MSV.PARAMETERID in (?) \
    ORDER BY MSV.PARAMETERID, \
             M.ZLOCATION, \
             MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE
horizontalprofile_instantaneouspoint_odv_data = SELECT V.NAME || ' ' || C.NAME  CRUISE, \
        MEDIAN.InstantaneousPoint.FEATUREID STATION, \
        '*' TYPE, \
        0 BOTDEPTH, \
        (M.ZLOCATION * -1) DEPTH, \
        ST_ASTEXT(SHAPE), \
        MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE, \
        MSV.DATAVALUE, \
        MSV.PARAMETERID PARAMETER \
    FROM MEDIAN.INSTANTANEOUSPOINT, \
         MEDIAN.VEHICLE V, \
         MEDIAN.CRUISE C, \
         (SELECT TRACKID,CRUISEID FROM MEDIAN.TRACK) T, \
         MEDIAN.SURVEYINFO S, \
         MEDIAN.MEASUREMENT M, \
         MEDIAN.MEASUREDSCALARVALUE MSV \
    WHERE V.VEHICLEID = C.VEHICLEID AND \
          C.CRUISEID = T.CRUISEID AND \
          T.TRACKID = S.TRACKID AND \
          S.SURVEYID = MEDIAN.INSTANTANEOUSPOINT.SURVEYID AND \
          MEDIAN.INSTANTANEOUSPOINT.FEATUREID = M.FEATUREID AND \
          M.MEASUREMENTID = MSV.MEASUREMENTID AND \
          MEDIAN.INSTANTANEOUSPOINT.SURVEYID = ? AND \
          M.ZLOCATION IN (?) AND \
          MSV.PARAMETERID in (?) \
    ORDER BY MSV.PARAMETERID, \
             M.ZLOCATION, \
             MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE
             
#############################################
#############################################
########### Horizontalprofil MESH ###########
#############################################
#############################################
horizontalprofile_meshpoint_depth = SELECT DISTINCT \
         mp.KPOSITION KEY, \
        'Layer ' || ml.KPOSITION || ': ' || -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 ST_ASTEXT(SHAPE), \
           msv.DATAVALUE YORDINATE, \
           msv.PARAMETERID GROUP1, \
           MEDIAN.MESHPOINT.KPOSITION GROUP2, \
           msv.TIMEVALUE GROUP3, \
           MEDIAN.MESHPOINT.JPOSITION, \
           MEDIAN.MESHPOINT.IPOSITION, \
           2 DATAID \
    from MEDIAN.MESHLAYER ml, \
         MEDIAN.MESHPOINT, \
         MEDIAN.MESH m, \
         MEDIAN.MESHSCALARVALUE msv \
    where msv.FEATUREID = MEDIAN.MESHPOINT. FEATUREID AND \
          ml.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \
          ml.MESHID = MEDIAN.MESHPOINT.MESHID and \
          m.MESHID = MEDIAN.MESHPOINT.MESHID AND \
          m.PARTIDMIN <= msv.PARTID AND \
          m.PARTIDMAX >= msv.PARTID AND \
          msv.PARAMETERID in (?) AND \
          msv.TIMEVALUE in (?) AND \
          m.OBJECTID = ? AND \
          MEDIAN.MESHPOINT.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 msv.TIMEVALUE, \
            MEDIAN.MESHPOINT.KPOSITION, \
            msv.PARAMETERID, \
            MEDIAN.MESHPOINT.JPOSITION, \
            MEDIAN.MESHPOINT.IPOSITION
            
horizontalprofile_mesh_odv_data =  SELECT SI.NAME CRUISE, \
          m.MESHID || '-' || MEDIAN.MESHPOINT.IPOSITION || '-' || MEDIAN.MESHPOINT.JPOSITION STATION, \
          '*' TYPE, \
          ST_ASTEXT(SHAPE), \
          0 BOTDEPTH, \
          (((ML.UPPERZLOCATION + ML.LOWERZLOCATION) / 2)*-1) DEPTH, \
          msv.DATAVALUE, \
          msv.PARAMETERID PARAMETER, \
          msv.TIMEVALUE, \
          MEDIAN.MESHPOINT.JPOSITION, \
          MEDIAN.MESHPOINT.IPOSITION \
    from MEDIAN.MESHLAYER ml, \
         MEDIAN.MESHPOINT, \
         MEDIAN.MESH m, \
         MEDIAN.MESHSCALARVALUE msv, \
         MEDIAN.SOURCEINFO SI \
    where msv.FEATUREID = MEDIAN.MESHPOINT. FEATUREID AND \
          ml.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \
          ml.MESHID = MEDIAN.MESHPOINT.MESHID and \
          m.MESHID = MEDIAN.MESHPOINT.MESHID AND \
          m.SOURCEID = SI.SOURCEID AND \
          m.PARTIDMIN <= msv.PARTID AND \
          m.PARTIDMAX >= msv.PARTID AND \
          msv.PARAMETERID in (?) AND \
          msv.TIMEVALUE in (?) AND \
          m.OBJECTID = ? AND \
          MEDIAN.MESHPOINT.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 msv.TIMEVALUE, \
            MEDIAN.MESHPOINT.KPOSITION, \
            msv.PARAMETERID, \
            MEDIAN.MESHPOINT.JPOSITION, \
            MEDIAN.MESHPOINT.IPOSITION
            
#############################################
#############################################
########### Horizontalprofil MESH ###########
###########     Schnittprofil     ###########
#############################################
#############################################
horizontalprofile_meshpoint_depth = SELECT DISTINCT \
         mp.KPOSITION KEY, \
        'Layer ' || ml.KPOSITION || ': ' || -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 )\
    order by mp.KPOSITION
            
horizontalprofile_meshpoint_cross_ij=SELECT MEDIAN.MESHFACE.JPOSITION, \
           MEDIAN.MESHFACE.IPOSITION \
    FROM MEDIAN.MESHFACE, \
         MEDIAN.MESH M \
    WHERE MEDIAN.MESHFACE.KPOSITION = 1 AND \
          M.MESHID = MEDIAN.MESHFACE.MESHID AND \
          M.OBJECTID = ? AND \
          INTERSECTS(SHAPE, "?")
horizontalprofile_mesh_cross_chart_data =  SELECT ST_ASTEXT(SHAPE), \
           msv.DATAVALUE YORDINATE, \
           msv.PARAMETERID GROUP1, \
           MEDIAN.MESHPOINT.KPOSITION GROUP2, \
           msv.TIMEVALUE GROUP3, \
           MEDIAN.MESHPOINT.JPOSITION, \
           MEDIAN.MESHPOINT.IPOSITION, \
           2 DATAID \
    from MEDIAN.MESHLAYER ml, \
         MEDIAN.MESHPOINT, \
         MEDIAN.MESH m, \
         MEDIAN.MESHSCALARVALUE msv \
    where msv.FEATUREID = MEDIAN.MESHPOINT.FEATUREID AND \
          ml.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \
          ml.MESHID = MEDIAN.MESHPOINT.MESHID and \
          m.MESHID = MEDIAN.MESHPOINT.MESHID AND \
          m.PARTIDMIN <= msv.PARTID AND \
          m.PARTIDMAX >= msv.PARTID AND \
          msv.PARAMETERID in (?) AND \
          msv.TIMEVALUE in (?) AND \
          m.OBJECTID = ? AND \
          ml.KPOSITION in (?) AND \
          MEDIAN.MESHPOINT.FEATUREID in \
              ( \
               SELECT distinct FEATUREID \
               FROM MEDIAN.MESHPOINT \
               WHERE ? \
               ) \
    order by msv.TIMEVALUE, \
            MEDIAN.MESHPOINT.KPOSITION, \
            msv.PARAMETERID, \
            MEDIAN.MESHPOINT.JPOSITION, \
            MEDIAN.MESHPOINT.IPOSITION
#############################################
#############################################
############# Profilschnitt MESH ############
#############################################
#############################################
verticalcrosssection_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 AND \
          msv.PARAMETERID = ? \
    order by msv.TIMEVALUE
verticalcrosssection_mesh_chart_data = SELECT ST_ASTEXT(SHAPE), \
           ((ml.UPPERZLOCATION  + ml.LOWERZLOCATION) / 2) Z, \
           msv.DATAVALUE YORDINATE, \
           msv.PARAMETERID GROUP1, \
           msv.TIMEVALUE GROUP2, \
           MEDIAN.MESHPOINT.JPOSITION, \
           MEDIAN.MESHPOINT.IPOSITION, \
           MEDIAN.MESHPOINT.KPOSITION \
    from MEDIAN.MESHLAYER ml, \
         MEDIAN.MESHPOINT, \
         MEDIAN.MESH m, \
         MEDIAN.MESHSCALARVALUE msv \
    where msv.FEATUREID = MEDIAN.MESHPOINT. FEATUREID AND \
          ml.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \
          ml.MESHID = MEDIAN.MESHPOINT.MESHID and \
          m.MESHID = MEDIAN.MESHPOINT.MESHID AND \
          m.PARTIDMIN <= msv.PARTID AND \
          m.PARTIDMAX >= msv.PARTID AND \
          msv.PARAMETERID = ? AND \
          msv.TIMEVALUE = ? AND \
          m.OBJECTID = ? AND \
          MEDIAN.MESHPOINT.FEATUREID in \
              ( \
               SELECT distinct FEATUREID \
               FROM MEDIAN.MESHPOINT \
               WHERE ? \
               ) \
    order by msv.TIMEVALUE, \
             msv.PARAMETERID, \
             MEDIAN.MESHPOINT.JPOSITION, \
             MEDIAN.MESHPOINT.IPOSITION, \
             MEDIAN.MESHPOINT.KPOSITION
             
verticalcrosssection_mesh_odv_data = SELECT SI.NAME CRUISE, \
            M.MESHID || '-' || MEDIAN.MESHPOINT.IPOSITION || '-' || MEDIAN.MESHPOINT.JPOSITION STATION, \
           '*' TYPE, \
           ST_ASTEXT(SHAPE), \
           0 BOTDEPTH, \
           (((ML.UPPERZLOCATION + ML.LOWERZLOCATION) / 2)*-1) DEPTH, \
           MSV.TIMEVALUE, \
           MSV.DATAVALUE, \
           MSV.PARAMETERID PARAMETER, \
           MEDIAN.MESHPOINT.JPOSITION, \
           MEDIAN.MESHPOINT.IPOSITION, \
           MEDIAN.MESHPOINT.KPOSITION, \
           2 DATAID \
    from MEDIAN.MESHLAYER ml, \
         MEDIAN.MESHPOINT, \
         MEDIAN.MESH m, \
         MEDIAN.SOURCEINFO SI, \
         MEDIAN.MESHSCALARVALUE msv \
    where msv.FEATUREID = MEDIAN.MESHPOINT. FEATUREID AND \
          ml.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \
          ml.MESHID = MEDIAN.MESHPOINT.MESHID and \
          m.MESHID = MEDIAN.MESHPOINT.MESHID AND \
          m.PARTIDMIN <= msv.PARTID AND \
          m.PARTIDMAX >= msv.PARTID AND \
          M.SOURCEID = SI.SOURCEID AND \
          msv.PARAMETERID = ? AND \
          msv.TIMEVALUE = ? AND \
          m.OBJECTID = ? AND \
          MEDIAN.MESHPOINT.FEATUREID in \
            ( select FEATUREID \
              from MEDIAN.MESHPOINT mp, \
                   MEDIAN.MESH m \
              where m.OBJECTID = ? AND \
                    mp.MESHID = m.MESHID  AND \
                    ? = (select ? from MEDIAN.MESHPOINT where FEATUREID = ?)) \
    order by msv.TIMEVALUE, \
             msv.PARAMETERID, \
             MEDIAN.MESHPOINT.JPOSITION, \
             MEDIAN.MESHPOINT.IPOSITION, \
             MEDIAN.MESHPOINT.KPOSITION
             
#############################################
#############################################
########## Horizontalschnitt MESH ###########
#############################################
#############################################
horizontalcrosssection_meshpoint_depth = SELECT DISTINCT MP.KPOSITION KEY, \
        'Layer ' || ML.KPOSITION || ': ' || -ML.UPPERZLOCATION || ' - '|| -ML.LOWERZLOCATION VALUE \
    from MEDIAN.MESHLAYER ML, \
         MEDIAN.MESHPOINT MP, \
         MEDIAN.MESH M \
    WHERE ML.KPOSITION = MP.KPOSITION AND \
          ML.MESHID = MP.MESHID AND \
          M.OBJECTID = ? AND \
          MP.MESHID = M.MESHID \
    ORDER BY MP.KPOSITION
    
horizontalcrosssection_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 AND \
          msv.PARAMETERID = ? \
    order by msv.TIMEVALUE
    
horizontalcrosssection_mesh_data = SELECT ST_ASTEXT(SHAPE), \
           MSV.DATAVALUE YORDINATE, \
           MEDIAN.MESHFACE.JPOSITION, \
           MEDIAN.MESHFACE.IPOSITION, \
           MEDIAN.MESHFACE.KPOSITION, \
           2 DATAID \
    from MEDIAN.MESHLAYER ML, \
         MEDIAN.MESHFACE, \
         MEDIAN.MESH M, \
         MEDIAN.MESHSCALARVALUE MSV \
    where MSV.FEATUREID = MEDIAN.MESHFACE.FEATUREID AND \
          ML.KPOSITION = MEDIAN.MESHFACE.KPOSITION and \
          ML.MESHID = MEDIAN.MESHFACE.MESHID and \
          M.MESHID = MEDIAN.MESHFACE.MESHID AND \
          M.PARTIDMIN <= MSV.PARTID AND \
          M.PARTIDMAX >= MSV.PARTID AND \
          MSV.PARAMETERID = ? AND \
          MSV.TIMEVALUE = ? AND \
          M.OBJECTID = ? AND \
          MEDIAN.MESHFACE.KPOSITION = ? \
    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, \
           '*' TYPE, \
           ST_ASTEXT(SHAPE), \
           0 BOTDEPTH, \
           (((ML.UPPERZLOCATION + ML.LOWERZLOCATION) / 2)*-1) DEPTH, \
           MSV.TIMEVALUE, \
           MSV.DATAVALUE, \
           MSV.PARAMETERID PARAMETER, \
           MEDIAN.MESHPOINT.JPOSITION, \
           MEDIAN.MESHPOINT.IPOSITION, \
           MEDIAN.MESHPOINT.KPOSITION \
    from MEDIAN.MESHLAYER ML, \
         MEDIAN.MESHPOINT, \
         MEDIAN.MESH M, \
         MEDIAN.SOURCEINFO SI, \
         MEDIAN.MESHSCALARVALUE MSV \
    where MSV.FEATUREID = MEDIAN.MESHPOINT.FEATUREID AND \
          ML.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \
          ML.MESHID = MEDIAN.MESHPOINT.MESHID and \
          M.MESHID = MEDIAN.MESHPOINT.MESHID AND \
          M.PARTIDMIN <= MSV.PARTID AND \
          M.PARTIDMAX >= MSV.PARTID AND \
          M.SOURCEID = SI.SOURCEID AND \
          MSV.PARAMETERID = ? AND \
          MSV.TIMEVALUE = ? AND \
          M.OBJECTID = ? AND \
          MEDIAN.MESHPOINT.KPOSITION = ? \
    order by MEDIAN.MESHPOINT.JPOSITION, \
             MEDIAN.MESHPOINT.IPOSITION
             
area_filter = SELECT DISTINCT \
       FEATUREID KEY ,\
       DESCRIPTION VALUE \
    FROM MEDIAN.FEATUREAREA \
    WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \
          FEATUREID > 0 \
    ORDER BY FEATUREID
    
subarea_filter = SELECT \
       FEATURECODE KEY, \
       NAME VALUE \
    FROM MEDIAN.FEATUREAREA \
    WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \
          FEATUREID = ? \
    ORDER BY NAME
    
rasterQuery = SELECT ST_ASTEXT(RASTER) \
    FROM MEDIAN.TOPO_WORLD_2MIN \
    WHERE INTERSECTS(RASTER, "?")

http://dive4elements.wald.intevation.org