view gnv-artifacts/doc/conf/queries.properties @ 754:5d45357dbc6d

ISSUE92: Integrated Column BAND for Query of Layers which belong to a FIS gnv-artifacts/trunk@800 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Tim Englich <tim.englich@intevation.de>
date Fri, 19 Mar 2010 10:02:15 +0000
parents 686eecfafbbf
children 1614b27dcbfa
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_timeseriespoint_with_wkt=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,"?")\
    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, \
        P.PARAMETERID PARAMETERID \
    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.PARTID <= m.PARTIDMAX 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_with_wkt = 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, "?") \
     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 \
          msc.PARTID >= m.PARTIDMIN AND \
          msc.PARTID <= m.PARTIDMAX AND \
          msc.PARAMETERID = p.PARAMETERID \
    order by p.GERMANNAME

verticalprofile_mesh_year= select  distinct \
        to_char(msv.TIMEVALUE,'YYYY') KEY, \
        to_char(msv.TIMEVALUE,'YYYY') 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 to_char(msv.TIMEVALUE, 'YYYY')
          
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 (?) AND \
          to_char(msv.TIMEVALUE,'YYYY') 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_vehicle_with_wkt = 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,"?") \
    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_cruise_with_wkt = 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,"?") \
    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_track_with_wkt = 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, "?") \
    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_year = select  distinct \
        to_char(msv.TIMEVALUE,'YYYY') KEY, \
        to_char(msv.TIMEVALUE,'YYYY') 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 to_char(msv.TIMEVALUE,'YYYY')
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 = ? AND \
          to_char(msv.TIMEVALUE,'YYYY') in (?) \
    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_year = select  distinct \
        to_char(msv.TIMEVALUE,'YYYY') KEY, \
        to_char(msv.TIMEVALUE,'YYYY') 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 to_char(msv.TIMEVALUE,'YYYY')
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 = ? AND \
          to_char(msv.TIMEVALUE,'YYYY') in (?) \
    order by msv.TIMEVALUE
    
horizontalcrosssection_mesh_data = SELECT ST_ASTEXT(SHAPE), \
           MSV.DATAVALUE YORDINATE, \
           MEDIAN.MESHPOINT.JPOSITION, \
           MEDIAN.MESHPOINT.IPOSITION, \
           MEDIAN.MESHPOINT.KPOSITION, \
           MSV.PARAMETERID, \
           MSV.TIMEVALUE, \
           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 = ? AND \
          MSV.TIMEVALUE = ? AND \
          M.OBJECTID = ? AND \
          MEDIAN.MESHPOINT.KPOSITION = ? \
    order by MEDIAN.MESHPOINT.JPOSITION, \
             MEDIAN.MESHPOINT.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, "?")
    
mapviewer_interface_fis_region = SELECT ID_FIS \
    FROM MEDIAN.FEATUREAREA, \
         MEDIAN.FIS_HAS_REGION FHR \
    WHERE FHR.FEATUREID = MEDIAN.FEATUREAREA.FEATUREID AND \
          FHR.FEATURETYPE = MEDIAN.FEATUREAREA.FEATURETYPE AND \
          FHR.FEATURECODE = MEDIAN.FEATUREAREA.FEATURECODE AND \
          INTERSECTS(SHAPE,"?")
          
mapviewer_interface_mapservices_has_fis = SELECT DISTINCT ID_FIS, \
         ID_MAPSERVICE \
    FROM MEDIAN.FIS_HAS_MAPSERVICE \
    WHERE ID_MAPSERVICE IN (?)

mapviewer_interface_mapservices_has_parameter = SELECT DISTINCT ID_PARAMETER \
    FROM MEDIAN.MAPSERVICE_HAS_PARAMETER \
    WHERE ID_MAPSERVICE = ?

mapviewer_interface_mapservices_has_parameter_using_layer = SELECT DISTINCT ID_PARAMETER \
   FROM MEDIAN.LAYER_HAS_PARAMETER \
   WHERE ID_MAPSERVICE = ? AND \
         ID_LAYER IN (?)
         
#############################################
#############################################
######### Layer Contis und Nauthis ##########
#############################################
#############################################

layer = SELECT ROW_ID KEY, \
        TITLE || '-' || LAYER_NAME || '-' || BAND VALUE \
    FROM MEDIAN.LAYER_HAS_SUBTYPES \
    WHERE ID_FEATURECLASS LIKE ? \
    ORDER BY LAYER_NAME
    
layer_request_data = SELECT ID_FEATURECLASS, \
        QUERY_STRING, \
        ID_MAPSERVICE || '_' ||ID_LAYER \
    FROM MEDIAN.LAYER_HAS_SUBTYPES \
    WHERE ROW_ID = ? 

layer_data = SELECT ST_ASTEXT(SHAPE), ? \
    FROM ? \
    WHERE ?
    
layer_data_with_geom = SELECT ST_ASTEXT(SHAPE), ? \
    FROM ? \
    WHERE ? AND \
          INTERSECTS(SHAPE,"?")
    
geometry_for_subareafilter=SELECT st_astext(SHAPE) \
    FROM MEDIAN.FEATUREAREA \
    WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \
           FEATURECODE = ?
           
layer_colums= SELECT COLUMN_NAME \
    FROM SDE.COLUMN_REGISTRY \
    WHERE COLUMN_NAME NOT LIKE 'SHAPE' AND \
          OWNER = '?' AND \
          TABLE_NAME = '?'

http://dive4elements.wald.intevation.org