Mercurial > dive4elements > gnv-client

#############################################
#############################################
##########     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, \
            '1' QF \
     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 TS.PARAMETERID, \
              TSV.MEASUREMENTID, \
              TSV.TIMESERIESID, \
              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, \
        '1' QF \
    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.TIMEVALUE, \
             msv.PARAMETERID


#############################################
#############################################
##########   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, \
           M.FEATUREID, \
           TS.TIMESERIESID \
    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, \
            '1' QF \
     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, \
              TSV.TIMEVALUE, \
              TS.PARAMETERID


#############################################
#############################################
########## 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, \
           MP.FEATUREID, \
           MP.MESHID \
    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 , \
        '1' QF \
    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.TIMEVALUE, \
             ml.UPPERZLOCATION, \
             msv.PARAMETERID

#############################################
#############################################
##### 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_series_with_wkt = 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, "?") \
    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_point_with_aera = 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 = ? AND \
          INTERSECTS(SHAPE, \
                    (SELECT st_astext(SHAPE) \
                     FROM MEDIAN.FEATUREAREA \
                     WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \
                            FEATURECODE = ? )\
                     ) \
    ORDER BY MEDIAN.INSTANTANEOUSPOINT.FEATUREID, \
             MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE

verticalprofile_instantaneouspoint_point_with_wkt = 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 = ? AND \
          INTERSECTS(SHAPE,"?") \
    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, \
        IP.FEATUREID, \
        IP.SERIESID \
    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 , \
        '1' QF \
    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, \
          M.ZLOCATION, \
          MSV.PARAMETERID

#############################################
#############################################
#### 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, \
        MEDIAN.INSTANTANEOUSPOINT.SURVEYID \
    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 , \
        '1' QF \
    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, \
           MEDIAN.MESHPOINT.MESHID \
    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 , \
          '1' QF \
    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, \
            MEDIAN.MESHPOINT.JPOSITION, \
            MEDIAN.MESHPOINT.IPOSITION, \
            msv.PARAMETERID

#############################################
#############################################
########### 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 , \
           '1' QF \
    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, \
             MEDIAN.MESHPOINT.JPOSITION, \
             MEDIAN.MESHPOINT.IPOSITION, \
             MEDIAN.MESHPOINT.KPOSITION, \
             msv.PARAMETERID

#############################################
#############################################
########## 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, \
             MSV.PARAMETERID

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, Nauthis  and Marinefeatures #
#############################################
#############################################

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 OWNER || '.' || TABLE_NAME || '.' || COLUMN_NAME \
    FROM SDE.COLUMN_REGISTRY \
    WHERE COLUMN_NAME NOT LIKE 'SHAPE' AND \
          OWNER = '?' AND \
          TABLE_NAME = '?'

geometry_type = select geometry_type \
    from sde.geometry_columns \
    where f_table_schema = '?' and \
          f_table_name = '?' and \
          f_geometry_column='SHAPE'
author	Ingo Weinzierl <ingo.weinzierl@intevation.de>
date	Mon, 12 Apr 2010 09:37:30 +0000
parents	feeaf5aec552
children	8065295ae255