tim@199: ############################################# tim@199: ############################################# tim@199: ########## Zeitserie ############## tim@199: ############################################# tim@199: ############################################# tim@199: tim@199: timeseries_timeseriespoint=SELECT DISTINCT \ tim@199: tsp.FEATUREID KEY, \ tim@199: tsp.NAME VALUE \ tim@199: FROM MEDIAN.TIMESERIESPOINT tsp, \ tim@199: MEDIAN.MEASUREMENT mmt \ tim@199: WHERE tsp.FEATUREID = mmt.FEATUREID AND \ tim@199: mmt.SOURCEID = ? \ tim@199: order by tsp.name tim@199: tim@224: timeseries_timeseriespoint_with_area=SELECT \ tim@224: MEDIAN.TIMESERIESPOINT.FEATUREID KEY, \ tim@224: MEDIAN.TIMESERIESPOINT.NAME VALUE \ tim@224: FROM MEDIAN.TIMESERIESPOINT, \ tim@224: MEDIAN.MEASUREMENT MMT \ tim@224: WHERE MEDIAN.TIMESERIESPOINT.FEATUREID = MMT.FEATUREID AND \ tim@224: MMT.SOURCEID = ? AND \ tim@224: INTERSECTS(SHAPE, \ tim@224: (SELECT st_astext(SHAPE) \ tim@224: FROM MEDIAN.FEATUREAREA \ tim@224: WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \ tim@224: FEATURECODE = ? )\ tim@224: )\ tim@224: ORDER BY MEDIAN.TIMESERIESPOINT.NAME tim@224: tim@738: timeseries_timeseriespoint_with_wkt=SELECT \ tim@738: MEDIAN.TIMESERIESPOINT.FEATUREID KEY, \ tim@738: MEDIAN.TIMESERIESPOINT.NAME VALUE \ tim@738: FROM MEDIAN.TIMESERIESPOINT, \ tim@738: MEDIAN.MEASUREMENT MMT \ tim@738: WHERE MEDIAN.TIMESERIESPOINT.FEATUREID = MMT.FEATUREID AND \ tim@738: MMT.SOURCEID = ? AND \ tim@738: INTERSECTS(SHAPE,"?")\ tim@738: ORDER BY MEDIAN.TIMESERIESPOINT.NAME tim@738: tim@199: timeseries_parameter=SELECT DISTINCT \ tim@199: p.PARAMETERID KEY, \ tim@338: p.GERMANNAME || ' ['|| p.UNIT ||']' VALUE, \ tim@338: p.GERMANNAME \ tim@209: FROM MEDIAN.PARAMETER P, \ tim@209: MEDIAN.TIMESERIES TS, \ tim@209: MEDIAN.TIMESERIESVALUE TSV, \ tim@209: MEDIAN.MEASUREMENT M, \ tim@209: MEDIAN.TIMESERIESPOINT TSP \ tim@209: WHERE M.FEATUREID = TSP.FEATUREID AND \ tim@209: M.MEASUREMENTID = TSV.MEASUREMENTID AND \ tim@209: TS.TIMESERIESID = TSV.TIMESERIESID AND \ tim@209: P.PARAMETERID = TS.PARAMETERID AND \ tim@209: TSP.FEATUREID = ? \ tim@209: ORDER BY P.GERMANNAME tim@209: tim@199: timeseries_depth_height=SELECT DISTINCT \ tim@209: M.MEASUREMENTID KEY, \ ingo@634: M.ZLOCATION VALUE, \ ingo@634: P.PARAMETERID PARAMETERID \ tim@209: FROM MEDIAN.MEASUREMENT M, \ tim@209: MEDIAN.TIMESERIESVALUE TSV, \ tim@209: MEDIAN.TIMESERIES T, \ tim@209: MEDIAN.PARAMETER P \ tim@209: WHERE M.MEASUREMENTID = TSV.MEASUREMENTID AND \ tim@209: TSV.TIMESERIESID = T.TIMESERIESID AND \ tim@209: T.PARAMETERID = P.PARAMETERID AND \ tim@209: M.FEATUREID = ? AND \ tim@209: P.PARAMETERID IN (?)\ tim@199: ORDER BY m.ZLOCATION DESC tim@199: tim@199: timeseries_interval=select min(tv.TIMEVALUE) MIN, \ tim@199: max(tv.TIMEVALUE) MAX \ tim@319: from MEDIAN.TIMESERIES t, \ tim@199: MEDIAN.TIMESERIESVALUE tv \ tim@199: where tv.TIMESERIESID = t.TIMESERIESID AND \ tim@199: t.PARAMETERID IN ( ? ) AND \ tim@199: tv.MEASUREMENTID IN ( ? ) tim@199: tim@199: timeseries_chart_data=SELECT tv.TIMEVALUE XORDINATE, \ tim@199: tv.DATAVALUE YORDINATE, \ tim@199: t.PARAMETERID GROUP1, \ tim@199: tv.MEASUREMENTID GROUP2, \ tim@217: tv.TIMESERIESID GROUP3, \ tim@217: t.TIMEINTERVAL GAPID \ tim@199: FROM MEDIAN.TIMESERIESVALUE tv, \ tim@199: MEDIAN.TIMESERIES t \ tim@199: WHERE tv.TIMESERIESID = t.TIMESERIESID AND \ tim@199: t.PARAMETERID IN ( ? ) AND \ tim@199: tv.MEASUREMENTID IN ( ? ) AND \ tim@199: tv.TIMEVALUE >= ? AND \ tim@199: tv.TIMEVALUE <= ? \ tim@319: ORDER BY tv.MEASUREMENTID, \ tim@319: tv.TIMESERIESID, \ tim@319: t.PARAMETERID, \ tim@199: tv.TIMEVALUE tim@232: tim@232: timeseries_odv_data = SELECT SI.NAME CRUISE, \ tim@232: MEDIAN.TIMESERIESPOINT.NAME STATION, \ tim@232: '*' TYPE, \ tim@232: ST_ASTEXT(SHAPE), \ tim@232: 0 BOTDEPTH, \ tim@232: (M.ZLOCATION *-1) DEPTH, \ tim@232: TSV.TIMEVALUE, \ tim@232: TSV.DATAVALUE, \ tim@232: TS.PARAMETERID PARAMETER, \ tim@232: TSV.MEASUREMENTID, \ tim@765: TSV.TIMESERIESID, \ tim@765: '1' QF \ tim@319: FROM MEDIAN.TIMESERIESPOINT, \ tim@232: MEDIAN.TIMESERIESVALUE TSV, \ tim@232: MEDIAN.TIMESERIES TS, \ tim@232: MEDIAN.MEASUREMENT M, \ tim@232: MEDIAN.SOURCEINFO SI \ tim@232: WHERE SI.SOURCEID = M.SOURCEID AND \ tim@232: M.FEATUREID = MEDIAN.TIMESERIESPOINT.FEATUREID AND \ tim@232: M.MEASUREMENTID = TSV.MEASUREMENTID AND \ tim@232: TSV.TIMESERIESID = TS.TIMESERIESID AND \ tim@232: TS.PARAMETERID IN ( ? ) AND \ tim@232: TSV.MEASUREMENTID IN ( ? ) AND \ tim@232: TSV.TIMEVALUE >= ? AND \ tim@232: TSV.TIMEVALUE <= ? \ tim@765: ORDER BY TS.PARAMETERID, \ tim@765: TSV.MEASUREMENTID, \ tim@319: TSV.TIMESERIESID, \ tim@232: TSV.TIMEVALUE tim@765: tim@1037: ############################################# tim@1037: ############################################# tim@1037: ########## Zeitserie ############## tim@1037: ########## Vektordaten ############### tim@1037: ############################################# tim@1037: ############################################# tim@199: tim@1037: timeseries_parameter_vector=SELECT DISTINCT \ tim@1037: p.PARAMETERID KEY, \ tim@1037: p.GERMANNAME || ' ['|| p.UNIT ||']' VALUE, \ tim@1037: p.GERMANNAME \ tim@1037: FROM MEDIAN.PARAMETER P, \ tim@1037: MEDIAN.TIMESERIES TS, \ tim@1037: MEDIAN.TIMESERIESVECTORVALUE TSV, \ tim@1037: MEDIAN.MEASUREMENT M, \ tim@1037: MEDIAN.TIMESERIESPOINT TSP \ tim@1037: WHERE M.FEATUREID = TSP.FEATUREID AND \ tim@1037: M.MEASUREMENTID = TSV.MEASUREMENTID AND \ tim@1037: TS.TIMESERIESID = TSV.TIMESERIESID AND \ tim@1037: P.PARAMETERID = TS.PARAMETERID AND \ tim@1037: TSP.FEATUREID = ? \ tim@1037: ORDER BY P.GERMANNAME tim@1037: tim@1037: timeseries_depth_height_vector=SELECT DISTINCT \ tim@1037: M.MEASUREMENTID KEY, \ tim@1037: M.ZLOCATION VALUE, \ tim@1037: P.PARAMETERID PARAMETERID \ tim@1037: FROM MEDIAN.MEASUREMENT M, \ tim@1037: MEDIAN.TIMESERIESVECTORVALUE TSV, \ tim@1037: MEDIAN.TIMESERIES T, \ tim@1037: MEDIAN.PARAMETER P \ tim@1037: WHERE M.MEASUREMENTID = TSV.MEASUREMENTID AND \ tim@1037: TSV.TIMESERIESID = T.TIMESERIESID AND \ tim@1037: T.PARAMETERID = P.PARAMETERID AND \ tim@1037: M.FEATUREID = ? AND \ tim@1037: P.PARAMETERID = ? \ tim@1037: ORDER BY m.ZLOCATION DESC tim@1037: tim@1037: timeseries_interval_vector=select min(tsv.TIMEVALUE) MIN, \ tim@1037: max(tsv.TIMEVALUE) MAX \ tim@1037: from MEDIAN.TIMESERIES t, \ tim@1037: MEDIAN.TIMESERIESVECTORVALUE tsv \ tim@1037: where tsv.TIMESERIESID = t.TIMESERIESID AND \ tim@1037: t.PARAMETERID = ? AND \ tim@1037: tsv.MEASUREMENTID = ? tim@1037: tim@1037: timeseries_chart_data_vector=SELECT tsv.TIMEVALUE XORDINATE, \ tim@1037: tsv.XCOMPONENT, \ tim@1037: tsv.YCOMPONENT, \ tim@1037: tsv.ZCOMPONENT, \ tim@1037: 1 SPEED, \ tim@1037: 1 DIRECTION, \ tim@1037: t.PARAMETERID GROUP1, \ tim@1037: tsv.MEASUREMENTID GROUP2, \ tim@1037: tsv.TIMESERIESID GROUP3, \ tim@1037: t.TIMEINTERVAL GAPID \ tim@1037: FROM MEDIAN.TIMESERIESVECTORVALUE tsv, \ tim@1037: MEDIAN.TIMESERIES t \ tim@1037: WHERE tsv.TIMESERIESID = t.TIMESERIESID AND \ tim@1037: t.PARAMETERID = ? AND \ tim@1037: tsv.MEASUREMENTID = ? AND \ tim@1037: tsv.TIMEVALUE >= ? AND \ tim@1037: tsv.TIMEVALUE <= ? \ tim@1037: ORDER BY tsv.MEASUREMENTID, \ tim@1037: tsv.TIMESERIESID, \ tim@1037: t.PARAMETERID, \ tim@1037: tsv.TIMEVALUE tim@1037: tim@1037: timeseries_odv_data_vector = SELECT SI.NAME CRUISE, \ tim@1037: MEDIAN.TIMESERIESPOINT.NAME STATION, \ tim@1037: '*' TYPE, \ tim@1037: ST_ASTEXT(SHAPE), \ tim@1037: 0 BOTDEPTH, \ tim@1037: (M.ZLOCATION *-1) DEPTH, \ tim@1037: TSV.TIMEVALUE, \ tim@1037: TSV.XCOMPONENT, \ tim@1037: TSV.YCOMPONENT, \ tim@1037: TSV.ZCOMPONENT, \ tim@1037: 1 SPEED, \ tim@1037: 1 DIRECTION, \ tim@1037: TS.PARAMETERID PARAMETER, \ tim@1037: TSV.MEASUREMENTID, \ tim@1037: TSV.TIMESERIESID, \ tim@1037: '1' QF \ tim@1037: FROM MEDIAN.TIMESERIESPOINT, \ tim@1037: MEDIAN.TIMESERIESVECTORVALUE TSV, \ tim@1037: MEDIAN.TIMESERIES TS, \ tim@1037: MEDIAN.MEASUREMENT M, \ tim@1037: MEDIAN.SOURCEINFO SI \ tim@1037: WHERE SI.SOURCEID = M.SOURCEID AND \ tim@1037: M.FEATUREID = MEDIAN.TIMESERIESPOINT.FEATUREID AND \ tim@1037: M.MEASUREMENTID = TSV.MEASUREMENTID AND \ tim@1037: TSV.TIMESERIESID = TS.TIMESERIESID AND \ tim@1037: TS.PARAMETERID = ? AND \ tim@1037: TSV.MEASUREMENTID = ? AND \ tim@1037: TSV.TIMEVALUE >= ? AND \ tim@1037: TSV.TIMEVALUE <= ? \ tim@1037: ORDER BY TS.PARAMETERID, \ tim@1037: TSV.MEASUREMENTID, \ tim@1037: TSV.TIMESERIESID, \ tim@1037: TSV.TIMEVALUE tim@1037: tim@1037: ############################################ tim@199: ############################################# tim@199: ########## Zeitserie Mesh ############## tim@199: ############################################# tim@199: ############################################# tim@199: tim@199: timeseries_mesh = SELECT OBJECTID KEY, \ tim@199: m.NAME VALUE \ tim@199: FROM MEDIAN.MESH m \ tim@199: WHERE SOURCEID IN (?) \ tim@199: ORDER BY m.NAME tim@209: timeseries_meshpoint = SELECT MEDIAN.MESHPOINT.FEATUREID, \ tim@199: ST_ASTEXT(SHAPE) \ tim@209: FROM MEDIAN.MESHPOINT, \ tim@209: MEDIAN.MESH M \ tim@209: WHERE MEDIAN.MESHPOINT.MESHID = M.MESHID AND \ tim@199: KPOSITION = 1 AND \ tim@209: M.OBJECTID = ? AND \ tim@199: INTERSECTS(SHAPE,"?") tim@199: tim@209: timeseries_meshpoint_depth = SELECT MP.FEATUREID KEY, \ tim@209: 'Layer ' || ML.KPOSITION || ': ' || -ML.UPPERZLOCATION || ' - '|| -ML.LOWERZLOCATION VALUE \ tim@209: from MEDIAN.MESHLAYER ML, \ tim@209: MEDIAN.MESHPOINT MP, \ tim@209: MEDIAN.MESH M \ tim@209: WHERE ML.KPOSITION = MP.KPOSITION AND \ tim@209: ML.MESHID = MP.MESHID AND \ tim@209: M.OBJECTID = ? AND \ tim@209: MP.MESHID = M.MESHID AND \ tim@209: IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) AND \ tim@209: JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) \ tim@209: ORDER BY ML.UPPERZLOCATION DESC tim@199: tim@199: timeseries_mesh_parameter=SELECT distinct \ tim@319: p.PARAMETERID KEY, \ tim@356: p.GERMANNAME || ' ['|| p.UNIT ||']' VALUE, \ tim@356: p.GERMANNAME \ tim@199: from MEDIAN.PARAMETER p, \ tim@199: MEDIAN.MESHSCALARVALUE msc, \ tim@199: MEDIAN.MESH m \ tim@749: where m.OBJECTID = ? AND \ tim@737: msc.PARTID >= m.PARTIDMIN AND \ tim@737: msc.PARTID <= m.PARTIDMAX AND \ tim@199: msc.PARAMETERID = p.PARAMETERID \ tim@199: order by p.GERMANNAME tim@199: timeseries_mesh_interval=select /*+ parallel(TIMEVALUE,5) */ \ tim@199: min(TIMEVALUE) MIN, \ tim@199: max(TIMEVALUE) MAX \ tim@319: from MEDIAN.MESHSCALARVALUE msc, \ tim@199: MEDIAN.MESH m \ tim@199: where m.OBJECTID = ? AND \ tim@199: msc.PARTID >= m.PARTIDMIN AND \ tim@199: msc.PARTID <= m.PARTIDMAX AND \ tim@199: msc.PARAMETERID IN (?) tim@199: tim@199: timeseries_mesh_chart_data=select /*+ parallel(timevalue,10) */ \ tim@199: msv.TIMEVALUE XORDINATE, \ tim@199: msv.DATAVALUE YORDINATE, \ tim@199: msv.PARAMETERID GROUP1, \ tim@199: msv.FEATUREID GROUP2, \ tim@217: mp.FEATUREID GROUP3, \ tim@217: -1 GAPID \ tim@319: from MEDIAN.MESHSCALARVALUE msv, \ tim@199: MEDIAN.MESHPOINT mp, \ tim@199: MEDIAN.MESH m \ tim@199: where (m.OBJECTID = ? AND \ tim@199: msv.PARTID >= m.PARTIDMIN AND \ tim@199: msv.PARTID <= m.PARTIDMAX ) AND \ tim@306: msv.FEATUREID = mp.FEATUREID AND \ tim@199: msv.PARAMETERID in ( ? ) AND \ tim@199: mp.FEATUREID in ( ? ) AND \ tim@199: msv.TIMEVALUE >= ? AND \ tim@199: msv.TIMEVALUE <= ? \ tim@199: order by mp.FEATUREID, \ tim@199: msv.FEATUREID, \ tim@199: msv.PARAMETERID, \ tim@199: msv.TIMEVALUE tim@240: tim@240: timeseries_mesh_odv_data = select /*+ parallel(timevalue,10) */ \ tim@240: SI.NAME CRUISE, \ tim@240: m.MESHID || '-' || MEDIAN.MESHPOINT.IPOSITION || '-' || MEDIAN.MESHPOINT.JPOSITION STATION, \ tim@240: '*' TYPE, \ tim@240: ST_ASTEXT(SHAPE), \ tim@240: 0 BOTDEPTH, \ tim@240: (((ML.UPPERZLOCATION + ML.LOWERZLOCATION) / 2)*-1) DEPTH, \ tim@240: msv.TIMEVALUE, \ tim@319: msv.DATAVALUE, \ tim@765: msv.PARAMETERID PARAMETER, \ tim@765: '1' QF \ tim@319: from MEDIAN.MESHSCALARVALUE msv, \ tim@240: MEDIAN.MESHPOINT, \ tim@240: MEDIAN.MESH m, \ tim@240: MEDIAN.SOURCEINFO SI, \ tim@240: MEDIAN.MESHLAYER ML \ tim@240: where (m.OBJECTID = ? AND \ tim@240: msv.PARTID >= m.PARTIDMIN AND \ tim@240: msv.PARTID <= m.PARTIDMAX ) AND \ tim@240: m.SOURCEID = SI.SOURCEID AND \ tim@240: ML.MESHID = MEDIAN.MESHPOINT.MESHID AND \ tim@240: ML.KPOSITION = MEDIAN.MESHPOINT.KPOSITION AND \ tim@306: msv.FEATUREID = MEDIAN.MESHPOINT.FEATUREID AND \ tim@240: msv.PARAMETERID in (? ) AND \ tim@240: MEDIAN.MESHPOINT.FEATUREID in ( ? ) AND \ tim@240: msv.TIMEVALUE >= ? AND \ tim@240: msv.TIMEVALUE <= ? \ tim@240: order by MEDIAN.MESHPOINT.FEATUREID, \ tim@240: msv.FEATUREID, \ tim@765: msv.TIMEVALUE, \ tim@765: msv.PARAMETERID tim@240: tim@199: tim@199: ############################################# tim@199: ############################################# tim@199: ########## Vertikalprofil ############## tim@199: ############################################# tim@199: ############################################# tim@199: tim@199: verticalprofile_point=SELECT DISTINCT \ tim@199: tsp.FEATUREID KEY, \ tim@199: tsp.NAME VALUE \ tim@199: FROM MEDIAN.TIMESERIESPOINT tsp, \ tim@199: MEDIAN.MEASUREMENT mmt \ tim@199: WHERE tsp.FEATUREID = mmt.FEATUREID AND \ tim@199: mmt.SOURCEID = ? \ tim@199: order by tsp.name tim@199: tim@199: verticalprofile_parameter=SELECT DISTINCT \ tim@199: p.PARAMETERID KEY, \ tim@356: p.GERMANNAME || ' ['|| p.UNIT ||']' VALUE, \ tim@356: p.GERMANNAME \ tim@199: from MEDIAN.PARAMETER p, \ tim@199: MEDIAN.TIMESERIES ts, \ tim@199: MEDIAN.TIMESERIESVALUE tsv, \ tim@199: MEDIAN.MEASUREMENT m \ tim@199: where ts.PARAMETERID = p.PARAMETERID and \ tim@199: ts.TIMESERIESID = tsv.TIMESERIESID and \ tim@199: m.MEASUREMENTID = tsv.MEASUREMENTID and \ tim@199: m.FEATUREID = ? \ tim@199: ORDER BY p.GERMANNAME tim@211: tim@211: verticalprofile_minmaxdepth= SELECT min(M.ZLOCATION) MIN, \ tim@211: max(M.ZLOCATION) MAX \ tim@211: FROM MEDIAN.MEASUREMENT M, \ tim@211: MEDIAN.TIMESERIES TS, \ tim@211: MEDIAN.TIMESERIESVALUE TSV \ tim@211: WHERE TS.TIMESERIESID = TSV.TIMESERIESID and \ tim@211: M.MEASUREMENTID = TSV.MEASUREMENTID and \ tim@211: M.FEATUREID = ? and \ tim@211: TS.PARAMETERID IN ( ? ) tim@211: tim@469: tim@469: verticalprofile_year=select distinct \ tim@469: to_char(tsv.TIMEVALUE,'YYYY') KEY, \ tim@469: to_char(tsv.TIMEVALUE,'YYYY') VALUE \ tim@469: from MEDIAN.TIMESERIES ts, \ tim@469: MEDIAN.TIMESERIESVALUE tsv, \ tim@469: MEDIAN.MEASUREMENT m \ tim@469: where ts.TIMESERIESID = tsv.TIMESERIESID and \ tim@469: m.MEASUREMENTID = tsv.MEASUREMENTID and \ tim@469: m.FEATUREID = ? and \ tim@469: ts.PARAMETERID IN ( ? ) \ tim@469: order by to_char(tsv.TIMEVALUE,'YYYY') tim@469: tim@199: verticalprofile_date=select distinct \ tim@199: tsv.TIMEVALUE KEY, \ tim@199: tsv.TIMEVALUE VALUE \ tim@319: from MEDIAN.TIMESERIES ts, \ tim@199: MEDIAN.TIMESERIESVALUE tsv, \ tim@199: MEDIAN.MEASUREMENT m \ tim@199: where ts.TIMESERIESID = tsv.TIMESERIESID and \ tim@199: m.MEASUREMENTID = tsv.MEASUREMENTID and \ tim@199: m.FEATUREID = ? and \ tim@469: ts.PARAMETERID IN ( ? ) and \ tim@469: to_char(tsv.TIMEVALUE,'YYYY') IN (?) \ tim@199: order by tsv.TIMEVALUE tim@199: tim@211: verticalprofile_chart_data= SELECT M.ZLOCATION XORDINATE, \ tim@211: TSV.DATAVALUE YORDINATE, \ tim@211: TS.PARAMETERID GROUP1, \ tim@211: TSV.TIMEVALUE GROUP2, \ ingo@336: 1 GROUP3, \ tim@762: 1 DATAID, \ tim@762: M.FEATUREID, \ tim@762: TS.TIMESERIESID \ tim@211: from MEDIAN.TIMESERIES TS, \ tim@211: MEDIAN.TIMESERIESVALUE TSV, \ tim@211: MEDIAN.MEASUREMENT M \ tim@211: where TS.TIMESERIESID = TSV.TIMESERIESID AND \ tim@211: M.MEASUREMENTID = TSV.MEASUREMENTID AND \ tim@211: M.FEATUREID = ? AND \ tim@211: TS.PARAMETERID IN ( ? ) AND \ tim@211: TSV.TIMEVALUE IN (?) AND \ tim@211: M.ZLOCATION >= ? AND \ tim@211: M.ZLOCATION <= ? \ tim@319: ORDER BY TSV.TIMEVALUE, \ tim@319: TSV.TIMESERIESID, \ tim@319: TS.PARAMETERID, \ tim@211: TSV.TIMEVALUE tim@199: tim@237: verticalprofile_odv_data = SELECT SI.NAME CRUISE, \ tim@237: MEDIAN.TIMESERIESPOINT.NAME STATION, \ tim@237: '*' TYPE, \ tim@237: ST_ASTEXT(SHAPE), \ tim@237: 0 BOTDEPTH, \ tim@237: (M.ZLOCATION *-1) DEPTH, \ tim@237: TSV.TIMEVALUE, \ tim@237: TSV.DATAVALUE, \ tim@237: TS.PARAMETERID PARAMETER, \ tim@237: TSV.MEASUREMENTID, \ tim@765: TSV.TIMESERIESID, \ tim@765: '1' QF \ tim@319: FROM MEDIAN.TIMESERIESPOINT, \ tim@237: MEDIAN.TIMESERIESVALUE TSV, \ tim@237: MEDIAN.TIMESERIES TS, \ tim@237: MEDIAN.MEASUREMENT M, \ tim@237: MEDIAN.SOURCEINFO SI \ tim@237: WHERE SI.SOURCEID = M.SOURCEID AND \ tim@237: M.FEATUREID = MEDIAN.TIMESERIESPOINT.FEATUREID AND \ tim@237: M.MEASUREMENTID = TSV.MEASUREMENTID AND \ tim@237: TSV.TIMESERIESID = TS.TIMESERIESID AND \ tim@237: M.FEATUREID = ? AND \ tim@237: TS.PARAMETERID IN ( ? ) AND \ tim@237: TSV.TIMEVALUE IN (?) AND \ tim@237: M.ZLOCATION >= ? AND \ tim@237: M.ZLOCATION <= ? \ tim@319: ORDER BY TSV.MEASUREMENTID, \ tim@319: TSV.TIMESERIESID, \ tim@765: TSV.TIMEVALUE, \ tim@765: TS.PARAMETERID tim@765: tim@237: tim@199: ############################################# tim@199: ############################################# tim@199: ########## Vertikalprofil Mesh ############## tim@199: ############################################# tim@199: ############################################# tim@199: tim@199: verticalprofile_mesh = SELECT OBJECTID KEY, \ tim@199: m.NAME VALUE \ tim@199: FROM MEDIAN.MESH m \ tim@199: WHERE SOURCEID IN (?) \ tim@199: order by m.NAME tim@199: tim@223: verticalprofile_mesh_with_area = SELECT M.OBJECTID KEY, \ tim@223: M.NAME VALUE \ tim@223: FROM MEDIAN.MESHPOINT, \ tim@223: MEDIAN.MESH M \ tim@223: WHERE M.SOURCEID IN (?) AND \ tim@223: M.MESHID = MEDIAN.MESHPOINT.MESHID AND \ tim@223: INTERSECTS(SHAPE, \ tim@223: (SELECT st_astext(SHAPE) \ tim@223: FROM MEDIAN.FEATUREAREA \ tim@223: WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \ tim@223: FEATURECODE = ? )\ tim@223: ) \ tim@223: order by M.NAME tim@737: tim@737: verticalprofile_mesh_with_wkt = SELECT M.OBJECTID KEY, \ tim@737: M.NAME VALUE \ tim@737: FROM MEDIAN.MESHPOINT, \ tim@737: MEDIAN.MESH M \ tim@737: WHERE M.SOURCEID IN (?) AND \ tim@737: M.MESHID = MEDIAN.MESHPOINT.MESHID AND \ tim@737: INTERSECTS(SHAPE, "?") \ tim@737: order by M.NAME tim@223: tim@199: verticalprofile_mesh_point = SELECT FEATUREID, \ tim@199: ST_ASTEXT(SHAPE) \ tim@199: FROM MEDIAN.MESHPOINT \ tim@199: WHERE MESHID in \ tim@199: (SELECT DISTINCT MESHID \ tim@199: FROM MEDIAN.MESH \ tim@199: WHERE OBJECTID = ?) AND \ tim@199: KPOSITION = 1 AND \ tim@199: INTERSECTS(SHAPE,"?") tim@199: tim@213: verticalprofile_mesh_mindepth = SELECT MP.KPOSITION KEY, \ tim@213: 'Layer ' || ML.KPOSITION || ': ' || -ML.UPPERZLOCATION || ' - '|| -ML.LOWERZLOCATION VALUE \ tim@213: from MEDIAN.MESHLAYER ML, \ tim@213: MEDIAN.MESHPOINT MP, \ tim@213: MEDIAN.MESH M \ tim@213: WHERE ML.KPOSITION = MP.KPOSITION AND \ tim@213: ML.MESHID = MP.MESHID AND \ tim@213: M.OBJECTID = ? AND \ tim@213: MP.MESHID = M.MESHID AND \ tim@213: IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) AND \ tim@213: JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) \ tim@213: ORDER BY ML.UPPERZLOCATION DESC tim@213: tim@213: verticalprofile_mesh_maxdepth = SELECT MP.KPOSITION KEY, \ tim@213: 'Layer ' || ML.KPOSITION || ': ' || -ML.UPPERZLOCATION || ' - '|| -ML.LOWERZLOCATION VALUE \ tim@213: from MEDIAN.MESHLAYER ML, \ tim@213: MEDIAN.MESHPOINT MP, \ tim@213: MEDIAN.MESH M \ tim@213: WHERE ML.KPOSITION = MP.KPOSITION AND \ tim@213: ML.MESHID = MP.MESHID AND \ tim@213: M.OBJECTID = ? AND \ tim@213: MP.MESHID = M.MESHID AND \ tim@213: IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) AND \ tim@213: JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) AND \ tim@213: MP.KPOSITION < ? \ tim@213: ORDER BY ML.UPPERZLOCATION DESC tim@213: tim@199: verticalprofile_mesh_parameter=SELECT distinct \ tim@319: p.PARAMETERID KEY, \ tim@356: p.GERMANNAME || ' ['|| p.UNIT ||']' VALUE, \ tim@356: p.GERMANNAME \ tim@199: from MEDIAN.PARAMETER p, \ tim@199: MEDIAN.MESHSCALARVALUE msc, \ tim@199: MEDIAN.MESH m \ tim@199: where m.OBJECTID = ? AND \ tim@749: msc.PARTID >= m.PARTIDMIN AND \ tim@749: msc.PARTID <= m.PARTIDMAX AND \ tim@199: msc.PARAMETERID = p.PARAMETERID \ tim@199: order by p.GERMANNAME ingo@523: ingo@523: verticalprofile_mesh_year= select distinct \ ingo@523: to_char(msv.TIMEVALUE,'YYYY') KEY, \ ingo@523: to_char(msv.TIMEVALUE,'YYYY') VALUE \ ingo@523: from MEDIAN.MESHSCALARVALUE msv, \ ingo@523: MEDIAN.MESH m \ ingo@523: where m.OBJECTID = ? AND \ ingo@523: msv.PARTID >= m.PARTIDMIN AND \ ingo@523: msv.PARTID <= m.PARTIDMAX AND \ ingo@523: msv.PARAMETERID in (?) \ ingo@523: order by to_char(msv.TIMEVALUE, 'YYYY') tim@199: tim@199: verticalprofile_mesh_date= select distinct \ tim@199: msv.TIMEVALUE KEY, \ tim@199: msv.TIMEVALUE VALUE \ tim@319: from MEDIAN.MESHSCALARVALUE msv, \ tim@199: MEDIAN.MESH m \ tim@199: where m.OBJECTID = ? AND \ tim@199: msv.PARTID >= m.PARTIDMIN AND \ tim@199: msv.PARTID <= m.PARTIDMAX AND \ ingo@523: msv.PARAMETERID in (?) AND \ ingo@523: to_char(msv.TIMEVALUE,'YYYY') in (?) \ tim@199: order by msv.TIMEVALUE tim@199: tim@199: verticalprofile_mesh_chart_data=select ml.UPPERZLOCATION XORDINATE, \ tim@199: msv.DATAVALUE YORDINATE, \ ingo@370: mp.KPOSITION KPOSITION, \ tim@199: msv.PARAMETERID GROUP1, \ tim@199: msv.TIMEVALUE GROUP2, \ ingo@336: 1 GROUP3, \ tim@762: 2 DATAID, \ tim@762: MP.FEATUREID, \ tim@762: MP.MESHID \ tim@199: from MEDIAN.MESHLAYER ml, \ tim@199: MEDIAN.MESHPOINT mp, \ tim@199: MEDIAN.MESH m, \ tim@199: MEDIAN.MESHSCALARVALUE msv \ tim@199: where msv.FEATUREID = mp. FEATUREID AND \ tim@199: ml.KPOSITION = mp.KPOSITION and \ tim@199: ml.MESHID = mp.MESHID and \ tim@199: m.MESHID = mp.MESHID AND \ tim@199: m.PARTIDMIN <= msv.PARTID AND \ tim@199: m.PARTIDMAX >= msv.PARTID AND \ tim@199: msv.PARAMETERID in (?) AND \ tim@199: msv.TIMEVALUE in (?) AND \ tim@199: m.OBJECTID = ? AND \ tim@199: mp.FEATUREID in \ tim@199: ( select FEATUREID \ tim@199: from MEDIAN.MESHPOINT mp, \ tim@199: MEDIAN.MESH m \ tim@199: where m.OBJECTID = ? AND \ tim@199: mp.MESHID = m.MESHID AND \ tim@199: IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) and \ tim@213: JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?)) AND \ tim@213: mp.KPOSITION <= ? AND \ tim@213: mp.KPOSITION >= ? \ tim@199: order by msv.PARAMETERID, \ tim@199: msv.TIMEVALUE, \ tim@199: ml.UPPERZLOCATION tim@199: tim@241: verticalprofile_mesh_odv_data=select SI.NAME CRUISE, \ tim@241: m.MESHID || '-' || MEDIAN.MESHPOINT.IPOSITION || '-' || MEDIAN.MESHPOINT.JPOSITION STATION, \ tim@241: '*' TYPE, \ tim@241: ST_ASTEXT(SHAPE), \ tim@241: 0 BOTDEPTH, \ tim@241: (((ML.UPPERZLOCATION + ML.LOWERZLOCATION) / 2)*-1) DEPTH, \ tim@319: msv.DATAVALUE, \ tim@241: msv.PARAMETERID PARAMETER, \ tim@765: msv.TIMEVALUE , \ tim@765: '1' QF \ tim@241: from MEDIAN.MESHLAYER ml, \ tim@241: MEDIAN.MESHPOINT, \ tim@241: MEDIAN.MESH m, \ tim@241: MEDIAN.MESHSCALARVALUE msv, \ tim@242: MEDIAN.SOURCEINFO SI \ tim@241: where m.SOURCEID = SI.SOURCEID AND \ tim@241: msv.FEATUREID = MEDIAN.MESHPOINT. FEATUREID AND \ tim@241: ml.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \ tim@241: ml.MESHID = MEDIAN.MESHPOINT.MESHID and \ tim@241: m.MESHID = MEDIAN.MESHPOINT.MESHID AND \ tim@241: m.PARTIDMIN <= msv.PARTID AND \ tim@241: m.PARTIDMAX >= msv.PARTID AND \ tim@241: msv.PARAMETERID in (?) AND \ tim@241: msv.TIMEVALUE in (?) AND \ tim@241: m.OBJECTID = ? AND \ tim@241: MEDIAN.MESHPOINT.FEATUREID in \ tim@241: ( select FEATUREID \ tim@241: from MEDIAN.MESHPOINT mp, \ tim@241: MEDIAN.MESH m \ tim@241: where m.OBJECTID = ? AND \ tim@241: mp.MESHID = m.MESHID AND \ tim@241: IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) and \ tim@241: JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?)) AND \ tim@241: MEDIAN.MESHPOINT.KPOSITION <= ? AND \ tim@241: MEDIAN.MESHPOINT.KPOSITION >= ? \ tim@765: order by msv.TIMEVALUE, \ tim@765: ml.UPPERZLOCATION, \ tim@765: msv.PARAMETERID tim@241: tim@199: ############################################# tim@199: ############################################# tim@199: ##### Vertikalprofil InstantaneousPoint ##### tim@199: ############################################# tim@199: ############################################# tim@199: tim@199: verticalprofile_instantaneouspoint_series = SELECT DISTINCT \ tim@319: S.SERIESID KEY, \ tim@199: S.DESCRIPTION VALUE \ tim@199: FROM MEDIAN.SERIES S, \ tim@199: MEDIAN.INSTANTANEOUSPOINT I, \ tim@199: MEDIAN.MEASUREMENT M \ tim@199: WHERE S.SERIESID = I.SERIESID AND \ tim@199: I.POINTSPEC = 4 AND \ tim@199: I.FEATUREID = M.FEATUREID AND \ tim@199: M.SOURCEID= ? \ tim@199: ORDER BY S.DESCRIPTION tim@199: tim@325: verticalprofile_instantaneouspoint_series_with_area = SELECT \ tim@325: S.SERIESID KEY, \ tim@325: S.DESCRIPTION VALUE \ tim@325: FROM MEDIAN.INSTANTANEOUSPOINT, \ tim@325: MEDIAN.SERIES S, \ tim@325: MEDIAN.MEASUREMENT M \ tim@325: WHERE S.SERIESID = MEDIAN.INSTANTANEOUSPOINT.SERIESID AND \ tim@325: MEDIAN.INSTANTANEOUSPOINT.POINTSPEC = 4 AND \ tim@325: MEDIAN.INSTANTANEOUSPOINT.FEATUREID = M.FEATUREID AND \ tim@325: M.SOURCEID= ? AND \ tim@325: INTERSECTS(SHAPE, \ tim@776: (SELECT st_astext(SHAPE) \ tim@776: FROM MEDIAN.FEATUREAREA \ tim@776: WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \ tim@776: FEATURECODE = ? )\ tim@325: ) \ tim@325: ORDER BY S.DESCRIPTION tim@776: verticalprofile_instantaneouspoint_series_with_wkt = SELECT \ tim@776: S.SERIESID KEY, \ tim@776: S.DESCRIPTION VALUE \ tim@776: FROM MEDIAN.INSTANTANEOUSPOINT, \ tim@776: MEDIAN.SERIES S, \ tim@776: MEDIAN.MEASUREMENT M \ tim@776: WHERE S.SERIESID = MEDIAN.INSTANTANEOUSPOINT.SERIESID AND \ tim@776: MEDIAN.INSTANTANEOUSPOINT.POINTSPEC = 4 AND \ tim@776: MEDIAN.INSTANTANEOUSPOINT.FEATUREID = M.FEATUREID AND \ tim@776: M.SOURCEID= ? AND \ tim@776: INTERSECTS(SHAPE, "?") \ tim@776: ORDER BY S.DESCRIPTION tim@325: tim@205: verticalprofile_instantaneouspoint_point = SELECT \ tim@205: MEDIAN.INSTANTANEOUSPOINT.FEATUREID, \ tim@205: to_char(MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE,'DD.MM.YYYY HH24:MI') VALUE, \ tim@205: MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE, \ tim@205: ST_ASTEXT(SHAPE) \ tim@205: FROM MEDIAN.INSTANTANEOUSPOINT, \ tim@199: MEDIAN.MEASUREMENT M \ tim@205: WHERE MEDIAN.INSTANTANEOUSPOINT.SERIESID = ? AND \ tim@205: MEDIAN.INSTANTANEOUSPOINT.POINTSPEC = 4 AND \ tim@205: MEDIAN.INSTANTANEOUSPOINT.FEATUREID = M.FEATUREID AND \ tim@199: M.SOURCEID = ? \ tim@205: ORDER BY MEDIAN.INSTANTANEOUSPOINT.FEATUREID, \ tim@205: MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE tim@199: tim@776: verticalprofile_instantaneouspoint_point_with_aera = SELECT \ tim@776: MEDIAN.INSTANTANEOUSPOINT.FEATUREID, \ tim@776: to_char(MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE,'DD.MM.YYYY HH24:MI') VALUE, \ tim@776: MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE, \ tim@776: ST_ASTEXT(SHAPE) \ tim@776: FROM MEDIAN.INSTANTANEOUSPOINT, \ tim@776: MEDIAN.MEASUREMENT M \ tim@776: WHERE MEDIAN.INSTANTANEOUSPOINT.SERIESID = ? AND \ tim@776: MEDIAN.INSTANTANEOUSPOINT.POINTSPEC = 4 AND \ tim@776: MEDIAN.INSTANTANEOUSPOINT.FEATUREID = M.FEATUREID AND \ tim@776: M.SOURCEID = ? AND \ tim@776: INTERSECTS(SHAPE, \ tim@776: (SELECT st_astext(SHAPE) \ tim@776: FROM MEDIAN.FEATUREAREA \ tim@776: WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \ tim@776: FEATURECODE = ? )\ tim@776: ) \ tim@776: ORDER BY MEDIAN.INSTANTANEOUSPOINT.FEATUREID, \ tim@776: MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE tim@776: tim@776: verticalprofile_instantaneouspoint_point_with_wkt = SELECT \ tim@776: MEDIAN.INSTANTANEOUSPOINT.FEATUREID, \ tim@776: to_char(MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE,'DD.MM.YYYY HH24:MI') VALUE, \ tim@776: MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE, \ tim@776: ST_ASTEXT(SHAPE) \ tim@776: FROM MEDIAN.INSTANTANEOUSPOINT, \ tim@776: MEDIAN.MEASUREMENT M \ tim@776: WHERE MEDIAN.INSTANTANEOUSPOINT.SERIESID = ? AND \ tim@776: MEDIAN.INSTANTANEOUSPOINT.POINTSPEC = 4 AND \ tim@776: MEDIAN.INSTANTANEOUSPOINT.FEATUREID = M.FEATUREID AND \ tim@776: M.SOURCEID = ? AND \ tim@776: INTERSECTS(SHAPE,"?") \ tim@776: ORDER BY MEDIAN.INSTANTANEOUSPOINT.FEATUREID, \ tim@776: MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE tim@776: tim@199: verticalprofile_instantaneouspoint_parameter = SELECT DISTINCT \ tim@319: P.PARAMETERID KEY, \ tim@356: p.GERMANNAME || ' ['|| p.UNIT ||']' VALUE, \ tim@356: p.GERMANNAME \ tim@199: FROM MEDIAN.PARAMETER P, \ tim@199: MEDIAN.INSTANTANEOUSPOINT IP, \ tim@199: MEDIAN.MEASUREMENT M, \ tim@199: MEDIAN.MEASUREDSCALARVALUE MSV \ tim@199: WHERE IP.FEATUREID = M.FEATUREID AND \ tim@199: M.MEASUREMENTID = MSV.MEASUREMENTID AND \ tim@199: MSV.PARAMETERID = P.PARAMETERID AND \ tim@199: IP.FEATUREID = ? \ tim@199: ORDER BY P.GERMANNAME tim@211: tim@211: verticalprofile_instantaneouspoint_minmaxdepth = SELECT min(M.ZLOCATION) MIN, \ tim@211: max(M.ZLOCATION) MAX \ tim@211: FROM MEDIAN.INSTANTANEOUSPOINT IP, \ tim@211: MEDIAN.MEASUREMENT M, \ tim@211: MEDIAN.MEASUREDSCALARVALUE MSV \ tim@211: WHERE IP.FEATUREID = M.FEATUREID AND \ tim@211: M.MEASUREMENTID = MSV.MEASUREMENTID AND \ tim@211: IP.FEATUREID = ? AND \ tim@211: MSV.PARAMETERID in (?) tim@211: tim@199: verticalprofile_instantaneouspoint_chart_data = SELECT M.ZLOCATION XORDINATE, \ tim@199: MSV.DATAVALUE YORDINATE, \ tim@199: MSV.PARAMETERID GROUP1, \ tim@199: IP.TIMEVALUE GROUP2, \ ingo@336: 1 GROUP3, \ tim@762: 3 DATAID, \ tim@762: IP.FEATUREID, \ tim@762: IP.SERIESID \ tim@199: FROM MEDIAN.INSTANTANEOUSPOINT IP, \ tim@199: MEDIAN.MEASUREMENT M, \ tim@199: MEDIAN.MEASUREDSCALARVALUE MSV \ tim@199: WHERE IP.FEATUREID = M.FEATUREID AND \ tim@199: M.MEASUREMENTID = MSV.MEASUREMENTID AND \ tim@199: IP.FEATUREID = ? AND \ tim@211: MSV.PARAMETERID in (?) AND \ tim@211: M.ZLOCATION >= ? AND \ tim@211: M.ZLOCATION <= ? \ tim@199: ORDER BY IP.TIMEVALUE, \ tim@199: MSV.PARAMETERID, \ tim@199: M.ZLOCATION tim@243: tim@243: verticalprofile_instantaneouspoint_odv_data = SELECT S.DESCRIPTION CRUISE, \ tim@243: MEDIAN.INSTANTANEOUSPOINT.FEATUREID STATION, \ tim@243: '*' TYPE, \ tim@243: ST_ASTEXT(SHAPE), \ tim@243: 0 BOTDEPTH, \ tim@243: (M.ZLOCATION * -1) DEPTH, \ tim@243: MSV.DATAVALUE, \ tim@243: MSV.PARAMETERID PARAMETER, \ tim@765: MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE , \ tim@765: '1' QF \ tim@243: FROM MEDIAN.SERIES S, \ tim@243: MEDIAN.INSTANTANEOUSPOINT, \ tim@243: MEDIAN.MEASUREMENT M, \ tim@243: MEDIAN.MEASUREDSCALARVALUE MSV \ tim@243: WHERE S.SERIESID = MEDIAN.INSTANTANEOUSPOINT.SERIESID AND \ tim@243: MEDIAN.INSTANTANEOUSPOINT.FEATUREID = M.FEATUREID AND \ tim@243: M.MEASUREMENTID = MSV.MEASUREMENTID AND \ tim@243: MEDIAN.INSTANTANEOUSPOINT.FEATUREID = ? AND \ tim@243: MSV.PARAMETERID in (?) AND \ tim@243: M.ZLOCATION >= ? AND \ tim@243: M.ZLOCATION <= ? \ tim@243: ORDER BY MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE, \ tim@765: M.ZLOCATION, \ tim@765: MSV.PARAMETERID tim@243: tim@199: ############################################# tim@199: ############################################# tim@199: #### Horizontalprofil InstantaneousPoint #### tim@199: ############################################# tim@199: ############################################# tim@199: horizontalprofile_instantaneouspoint_vehicle = SELECT DISTINCT \ tim@199: V.VEHICLEID KEY, \ tim@199: V.NAME VALUE \ tim@199: FROM MEDIAN.VEHICLE V, \ tim@199: MEDIAN.CRUISE C, \ tim@199: MEDIAN.TRACK T, \ tim@199: MEDIAN.SURVEYINFO S, \ tim@199: MEDIAN.INSTANTANEOUSPOINT I, \ tim@199: MEDIAN.MEASUREMENT M \ tim@199: WHERE V.VEHICLEID = C.VEHICLEID AND \ tim@199: C.CRUISEID = T.CRUISEID AND \ tim@199: T.TRACKID = S.TRACKID AND \ tim@199: S.SURVEYID = I.SURVEYID AND \ tim@199: I.FEATUREID = M.FEATUREID AND \ tim@199: M.SOURCEID = ? \ tim@199: ORDER BY V.NAME tim@319: tim@319: horizontalprofile_instantaneouspoint_vehicle_with_area = SELECT V.VEHICLEID KEY, \ tim@319: V.NAME VALUE \ tim@319: FROM MEDIAN.TRACK, \ tim@319: MEDIAN.VEHICLE V, \ tim@319: MEDIAN.CRUISE C \ tim@319: WHERE V.VEHICLEID = C.VEHICLEID AND \ tim@319: C.CRUISEID = MEDIAN.TRACK.CRUISEID AND \ tim@319: MEDIAN.TRACK.TRACKID IN \ tim@319: (SELECT DISTINCT S.TRACKID \ tim@319: FROM MEDIAN.MEASUREMENT M ,\ tim@319: MEDIAN.INSTANTANEOUSPOINT I, \ tim@319: MEDIAN.SURVEYINFO S \ tim@319: WHERE M.SOURCEID = ? AND \ tim@319: I.FEATUREID = M.FEATUREID AND \ tim@319: S.SURVEYID = I.SURVEYID)AND \ tim@319: INTERSECTS(SHAPE, \ tim@319: (SELECT st_astext(SHAPE) \ tim@319: FROM MEDIAN.FEATUREAREA \ tim@319: WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \ tim@319: FEATURECODE = ? )\ tim@319: ) \ tim@319: ORDER BY V.NAME tim@741: tim@741: horizontalprofile_instantaneouspoint_vehicle_with_wkt = SELECT V.VEHICLEID KEY, \ tim@741: V.NAME VALUE \ tim@741: FROM MEDIAN.TRACK, \ tim@741: MEDIAN.VEHICLE V, \ tim@741: MEDIAN.CRUISE C \ tim@741: WHERE V.VEHICLEID = C.VEHICLEID AND \ tim@741: C.CRUISEID = MEDIAN.TRACK.CRUISEID AND \ tim@741: MEDIAN.TRACK.TRACKID IN \ tim@741: (SELECT DISTINCT S.TRACKID \ tim@741: FROM MEDIAN.MEASUREMENT M ,\ tim@741: MEDIAN.INSTANTANEOUSPOINT I, \ tim@741: MEDIAN.SURVEYINFO S \ tim@741: WHERE M.SOURCEID = ? AND \ tim@741: I.FEATUREID = M.FEATUREID AND \ tim@741: S.SURVEYID = I.SURVEYID)AND \ tim@741: INTERSECTS(SHAPE,"?") \ tim@741: ORDER BY V.NAME tim@741: tim@199: horizontalprofile_instantaneouspoint_cruise = SELECT DISTINCT \ tim@319: C.CRUISEID KEY, \ tim@206: V.NAME || ' ' || \ tim@206: C.NAME || ' ' || \ tim@206: TO_CHAR(C.STARTDATE,'DD.MM.YYYY') || ' - ' || \ tim@319: TO_CHAR(C.ENDDATE,'DD.MM.YYYY') VALUE, \ tim@319: V.NAME, \ tim@206: C.NAME \ tim@199: FROM MEDIAN.CRUISE C, \ tim@206: MEDIAN.VEHICLE V, \ tim@199: MEDIAN.TRACK T, \ tim@199: MEDIAN.SURVEYINFO S, \ tim@199: MEDIAN.INSTANTANEOUSPOINT I, \ tim@199: MEDIAN.MEASUREMENT M \ tim@206: WHERE C.VEHICLEID = V.VEHICLEID AND \ tim@206: C.CRUISEID = T.CRUISEID AND \ tim@199: T.TRACKID = S.TRACKID AND \ tim@199: S.SURVEYID = I.SURVEYID AND \ tim@199: I.FEATUREID = M.FEATUREID AND \ tim@199: C.VEHICLEID = ? AND \ tim@205: M.SOURCEID = ? \ tim@206: ORDER BY V.NAME, C.NAME tim@319: tim@319: horizontalprofile_instantaneouspoint_cruise_with_area = SELECT C.CRUISEID KEY, \ tim@319: V.NAME || ' ' || \ tim@319: C.NAME || ' ' || \ tim@319: TO_CHAR(C.STARTDATE,'DD.MM.YYYY') || ' - ' || \ tim@319: TO_CHAR(C.ENDDATE,'DD.MM.YYYY') VALUE, \ tim@319: V.NAME, \ tim@319: C.NAME \ tim@319: FROM MEDIAN.TRACK, \ tim@319: MEDIAN.VEHICLE V, \ tim@319: MEDIAN.CRUISE C \ tim@319: WHERE V.VEHICLEID = C.VEHICLEID AND \ tim@319: C.CRUISEID = MEDIAN.TRACK.CRUISEID AND \ tim@319: MEDIAN.TRACK.TRACKID IN \ tim@319: (SELECT DISTINCT S.TRACKID \ tim@319: FROM MEDIAN.MEASUREMENT M ,\ tim@319: MEDIAN.INSTANTANEOUSPOINT I, \ tim@319: MEDIAN.SURVEYINFO S \ tim@319: WHERE M.SOURCEID = ? AND \ tim@319: C.VEHICLEID = ? AND \ tim@319: I.FEATUREID = M.FEATUREID AND \ tim@319: S.SURVEYID = I.SURVEYID)AND \ tim@319: INTERSECTS(SHAPE, \ tim@319: (SELECT st_astext(SHAPE) \ tim@319: FROM MEDIAN.FEATUREAREA \ tim@319: WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \ tim@319: FEATURECODE = ? )\ tim@319: ) \ tim@319: ORDER BY V.NAME tim@741: tim@741: horizontalprofile_instantaneouspoint_cruise_with_wkt = SELECT C.CRUISEID KEY, \ tim@741: V.NAME || ' ' || \ tim@741: C.NAME || ' ' || \ tim@741: TO_CHAR(C.STARTDATE,'DD.MM.YYYY') || ' - ' || \ tim@741: TO_CHAR(C.ENDDATE,'DD.MM.YYYY') VALUE, \ tim@741: V.NAME, \ tim@741: C.NAME \ tim@741: FROM MEDIAN.TRACK, \ tim@741: MEDIAN.VEHICLE V, \ tim@741: MEDIAN.CRUISE C \ tim@741: WHERE V.VEHICLEID = C.VEHICLEID AND \ tim@741: C.CRUISEID = MEDIAN.TRACK.CRUISEID AND \ tim@741: MEDIAN.TRACK.TRACKID IN \ tim@741: (SELECT DISTINCT S.TRACKID \ tim@741: FROM MEDIAN.MEASUREMENT M ,\ tim@741: MEDIAN.INSTANTANEOUSPOINT I, \ tim@741: MEDIAN.SURVEYINFO S \ tim@741: WHERE M.SOURCEID = ? AND \ tim@741: C.VEHICLEID = ? AND \ tim@741: I.FEATUREID = M.FEATUREID AND \ tim@741: S.SURVEYID = I.SURVEYID)AND \ tim@741: INTERSECTS(SHAPE,"?") \ tim@741: ORDER BY V.NAME tim@741: tim@199: horizontalprofile_instantaneouspoint_track= SELECT \ tim@319: T.TRACKID KEY, \ tim@199: to_char(T.STARTDATE,'DD.MM.YYYY HH24:MI') || ' - '|| to_char(T.ENDDATE,'DD.MM.YYYY HH24:MI') || ' - '|| T.NAME VALUE \ tim@199: FROM MEDIAN.TRACK T \ tim@199: WHERE T.CRUISEID = ? \ tim@199: ORDER BY T.STARTDATE, \ tim@199: T.ENDDATE, \ tim@199: T.NAME tim@319: tim@319: horizontalprofile_instantaneouspoint_track_with_area = SELECT \ tim@319: MEDIAN.TRACK.TRACKID KEY, \ tim@741: to_char(STARTDATE,'DD-MM-YYYY HH24:MI') || ' - '|| to_char(ENDDATE,'DD-MM-YYYY HH24:MI') || ' - '|| NAME VALUE, \ tim@319: NAME \ tim@319: FROM MEDIAN.TRACK \ tim@319: WHERE MEDIAN.TRACK.CRUISEID = ? AND \ tim@319: INTERSECTS(SHAPE, \ tim@319: (SELECT st_astext(SHAPE) \ tim@319: FROM MEDIAN.FEATUREAREA \ tim@319: WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \ tim@319: FEATURECODE = ? )\ tim@319: ) \ tim@319: ORDER BY STARTDATE, \ tim@319: ENDDATE, \ tim@319: NAME tim@741: tim@741: horizontalprofile_instantaneouspoint_track_with_wkt = SELECT \ tim@741: MEDIAN.TRACK.TRACKID KEY, \ tim@741: to_char(STARTDATE,'DD-MM-YYYY HH24:MI') || ' - '|| to_char(ENDDATE,'DD-MM-YYYY HH24:MI') || ' - '|| NAME VALUE, \ tim@741: NAME \ tim@741: FROM MEDIAN.TRACK \ tim@741: WHERE MEDIAN.TRACK.CRUISEID = ? AND \ tim@741: INTERSECTS(SHAPE, "?") \ tim@741: ORDER BY STARTDATE, \ tim@741: ENDDATE, \ tim@741: NAME tim@741: tim@199: horizontalprofile_instantaneouspoint_surveyinfo = SELECT \ tim@319: SURVEYID KEY, \ tim@199: to_char(STARTDATE,'DD.MM.YYYY HH24:MI') || ' - '|| to_char(ENDDATE,'DD.MM.YYYY HH24:MI') || ' - '|| DESCRIPTION VALUE \ tim@199: FROM MEDIAN.SURVEYINFO \ tim@199: WHERE TRACKID = ? \ tim@199: ORDER BY STARTDATE, \ tim@199: ENDDATE, \ tim@199: DESCRIPTION tim@199: horizontalprofile_instantaneouspoint_parameter = SELECT DISTINCT \ tim@319: P.PARAMETERID KEY, \ tim@356: p.GERMANNAME || ' ['|| p.UNIT ||']' VALUE, \ tim@356: p.GERMANNAME \ tim@319: FROM MEDIAN.PARAMETER P, \ tim@319: MEDIAN.MEASUREMENT M, \ tim@319: MEDIAN.INSTANTANEOUSPOINT IP, \ tim@199: MEDIAN.MEASUREDSCALARVALUE MSV \ tim@199: WHERE P.PARAMETERID = MSV.PARAMETERID AND \ tim@199: MSV.MEASUREMENTID = M.MEASUREMENTID AND \ tim@199: M.FEATUREID = IP.FEATUREID AND \ tim@199: IP.SURVEYID = ? \ tim@199: ORDER BY P.GERMANNAME tim@199: horizontalprofile_instantaneouspoint_depth = SELECT DISTINCT \ tim@199: M.ZLOCATION KEY, \ tim@199: M.ZLOCATION VALUE \ tim@319: FROM MEDIAN.MEASUREMENT M, \ tim@199: MEDIAN.INSTANTANEOUSPOINT IP \ tim@199: WHERE M.FEATUREID = IP.FEATUREID AND \ tim@199: IP.SURVEYID = ? \ tim@199: ORDER BY M.ZLOCATION tim@319: horizontalprofile_instantaneouspoint_chart_data = SELECT ST_ASTEXT(SHAPE), \ tim@319: MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE TIME, \ tim@319: MSV.DATAVALUE YORDINATE, \ tim@319: MSV.PARAMETERID GROUP1, \ tim@319: ZLOCATION GROUP2, \ ingo@336: 1 GROUP3, \ tim@762: 3 DATAID, \ tim@762: MEDIAN.INSTANTANEOUSPOINT.SURVEYID \ tim@199: FROM MEDIAN.INSTANTANEOUSPOINT, \ tim@199: MEDIAN.MEASUREMENT M, \ tim@199: MEDIAN.MEASUREDSCALARVALUE MSV \ tim@199: WHERE MEDIAN.INSTANTANEOUSPOINT.FEATUREID = M.FEATUREID AND \ tim@199: M.MEASUREMENTID = MSV.MEASUREMENTID AND \ tim@199: MEDIAN.INSTANTANEOUSPOINT.SURVEYID = ? AND \ tim@199: M.ZLOCATION IN (?) AND \ tim@199: MSV.PARAMETERID in (?) \ tim@199: ORDER BY MSV.PARAMETERID, \ tim@199: M.ZLOCATION, \ tim@199: MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE tim@244: horizontalprofile_instantaneouspoint_odv_data = SELECT V.NAME || ' ' || C.NAME CRUISE, \ tim@244: MEDIAN.InstantaneousPoint.FEATUREID STATION, \ tim@244: '*' TYPE, \ tim@244: 0 BOTDEPTH, \ tim@319: (M.ZLOCATION * -1) DEPTH, \ tim@319: ST_ASTEXT(SHAPE), \ tim@244: MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE, \ tim@244: MSV.DATAVALUE, \ tim@765: MSV.PARAMETERID PARAMETER , \ tim@765: '1' QF \ tim@244: FROM MEDIAN.INSTANTANEOUSPOINT, \ tim@244: MEDIAN.VEHICLE V, \ tim@244: MEDIAN.CRUISE C, \ tim@244: (SELECT TRACKID,CRUISEID FROM MEDIAN.TRACK) T, \ tim@244: MEDIAN.SURVEYINFO S, \ tim@244: MEDIAN.MEASUREMENT M, \ tim@244: MEDIAN.MEASUREDSCALARVALUE MSV \ tim@244: WHERE V.VEHICLEID = C.VEHICLEID AND \ tim@244: C.CRUISEID = T.CRUISEID AND \ tim@244: T.TRACKID = S.TRACKID AND \ tim@244: S.SURVEYID = MEDIAN.INSTANTANEOUSPOINT.SURVEYID AND \ tim@244: MEDIAN.INSTANTANEOUSPOINT.FEATUREID = M.FEATUREID AND \ tim@244: M.MEASUREMENTID = MSV.MEASUREMENTID AND \ tim@244: MEDIAN.INSTANTANEOUSPOINT.SURVEYID = ? AND \ tim@244: M.ZLOCATION IN (?) AND \ tim@244: MSV.PARAMETERID in (?) \ tim@244: ORDER BY MSV.PARAMETERID, \ tim@244: M.ZLOCATION, \ tim@244: MEDIAN.INSTANTANEOUSPOINT.TIMEVALUE tim@199: tim@199: ############################################# tim@199: ############################################# tim@199: ########### Horizontalprofil MESH ########### tim@199: ############################################# tim@199: ############################################# tim@199: horizontalprofile_meshpoint_depth = SELECT DISTINCT \ tim@199: mp.KPOSITION KEY, \ tim@199: 'Layer ' || ml.KPOSITION || ': ' || -ml.UPPERZLOCATION || ' - '|| -ml.LOWERZLOCATION VALUE \ tim@199: from MEDIAN.MESHLAYER ml, \ tim@199: MEDIAN.MESHPOINT mp \ tim@199: where ml.KPOSITION = mp.KPOSITION and \ tim@199: ml.MESHID = mp.MESHID and \ tim@199: mp.FEATUREID in \ tim@199: ( select FEATUREID \ tim@199: from MEDIAN.MESHPOINT mp, \ tim@199: MEDIAN.MESH m \ tim@199: where m.OBJECTID = ? AND \ tim@199: mp.MESHID = m.MESHID AND \ tim@199: IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) and \ tim@199: JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?)) \ tim@199: order by mp.KPOSITION tim@199: tim@199: horizontalprofile_mesh_chart_data = SELECT ST_ASTEXT(SHAPE), \ tim@199: msv.DATAVALUE YORDINATE, \ tim@199: msv.PARAMETERID GROUP1, \ tim@199: MEDIAN.MESHPOINT.KPOSITION GROUP2, \ tim@199: msv.TIMEVALUE GROUP3, \ tim@319: MEDIAN.MESHPOINT.JPOSITION, \ ingo@336: MEDIAN.MESHPOINT.IPOSITION, \ tim@762: 2 DATAID, \ tim@762: MEDIAN.MESHPOINT.MESHID \ tim@199: from MEDIAN.MESHLAYER ml, \ tim@319: MEDIAN.MESHPOINT, \ tim@199: MEDIAN.MESH m, \ tim@199: MEDIAN.MESHSCALARVALUE msv \ tim@199: where msv.FEATUREID = MEDIAN.MESHPOINT. FEATUREID AND \ tim@199: ml.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \ tim@199: ml.MESHID = MEDIAN.MESHPOINT.MESHID and \ tim@199: m.MESHID = MEDIAN.MESHPOINT.MESHID AND \ tim@199: m.PARTIDMIN <= msv.PARTID AND \ tim@199: m.PARTIDMAX >= msv.PARTID AND \ tim@199: msv.PARAMETERID in (?) AND \ tim@199: msv.TIMEVALUE in (?) AND \ tim@199: m.OBJECTID = ? AND \ tim@199: MEDIAN.MESHPOINT.FEATUREID in \ tim@199: ( select FEATUREID \ tim@199: from MEDIAN.MESHPOINT mp, \ tim@199: MEDIAN.MESH m \ tim@199: where m.OBJECTID = ? AND \ tim@199: mp.MESHID = m.MESHID AND \ tim@199: KPOSITION in ( ? ) and \ tim@199: ? = (select ? from MEDIAN.MESHPOINT where FEATUREID = ?)) \ tim@199: order by msv.TIMEVALUE, \ tim@199: MEDIAN.MESHPOINT.KPOSITION, \ tim@199: msv.PARAMETERID, \ tim@319: MEDIAN.MESHPOINT.JPOSITION, \ tim@199: MEDIAN.MESHPOINT.IPOSITION tim@242: tim@242: horizontalprofile_mesh_odv_data = SELECT SI.NAME CRUISE, \ tim@242: m.MESHID || '-' || MEDIAN.MESHPOINT.IPOSITION || '-' || MEDIAN.MESHPOINT.JPOSITION STATION, \ tim@242: '*' TYPE, \ tim@242: ST_ASTEXT(SHAPE), \ tim@242: 0 BOTDEPTH, \ tim@242: (((ML.UPPERZLOCATION + ML.LOWERZLOCATION) / 2)*-1) DEPTH, \ tim@319: msv.DATAVALUE, \ tim@247: msv.PARAMETERID PARAMETER, \ tim@242: msv.TIMEVALUE, \ tim@319: MEDIAN.MESHPOINT.JPOSITION, \ tim@765: MEDIAN.MESHPOINT.IPOSITION , \ tim@765: '1' QF \ tim@242: from MEDIAN.MESHLAYER ml, \ tim@319: MEDIAN.MESHPOINT, \ tim@242: MEDIAN.MESH m, \ tim@242: MEDIAN.MESHSCALARVALUE msv, \ tim@242: MEDIAN.SOURCEINFO SI \ tim@242: where msv.FEATUREID = MEDIAN.MESHPOINT. FEATUREID AND \ tim@242: ml.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \ tim@242: ml.MESHID = MEDIAN.MESHPOINT.MESHID and \ tim@242: m.MESHID = MEDIAN.MESHPOINT.MESHID AND \ tim@242: m.SOURCEID = SI.SOURCEID AND \ tim@242: m.PARTIDMIN <= msv.PARTID AND \ tim@242: m.PARTIDMAX >= msv.PARTID AND \ tim@242: msv.PARAMETERID in (?) AND \ tim@242: msv.TIMEVALUE in (?) AND \ tim@242: m.OBJECTID = ? AND \ tim@242: MEDIAN.MESHPOINT.FEATUREID in \ tim@242: ( select FEATUREID \ tim@242: from MEDIAN.MESHPOINT mp, \ tim@242: MEDIAN.MESH m \ tim@242: where m.OBJECTID = ? AND \ tim@242: mp.MESHID = m.MESHID AND \ tim@242: KPOSITION in ( ? ) and \ tim@242: ? = (select ? from MEDIAN.MESHPOINT where FEATUREID = ?)) \ tim@242: order by msv.TIMEVALUE, \ tim@242: MEDIAN.MESHPOINT.KPOSITION, \ tim@319: MEDIAN.MESHPOINT.JPOSITION, \ tim@765: MEDIAN.MESHPOINT.IPOSITION, \ tim@765: msv.PARAMETERID tim@352: tim@352: ############################################# tim@352: ############################################# tim@352: ########### Horizontalprofil MESH ########### tim@352: ########### Schnittprofil ########### tim@352: ############################################# tim@352: ############################################# tim@352: horizontalprofile_meshpoint_depth = SELECT DISTINCT \ tim@352: mp.KPOSITION KEY, \ tim@352: 'Layer ' || ml.KPOSITION || ': ' || -ml.UPPERZLOCATION || ' - '|| -ml.LOWERZLOCATION VALUE \ tim@352: from MEDIAN.MESHLAYER ml, \ tim@352: MEDIAN.MESHPOINT mp \ tim@352: where ml.KPOSITION = mp.KPOSITION and \ tim@352: ml.MESHID = mp.MESHID and \ tim@352: mp.FEATUREID in \ tim@352: ( select FEATUREID \ tim@352: from MEDIAN.MESHPOINT mp, \ tim@352: MEDIAN.MESH m \ tim@352: where m.OBJECTID = ? AND \ tim@352: mp.MESHID = m.MESHID )\ tim@352: order by mp.KPOSITION tim@352: tim@352: horizontalprofile_meshpoint_cross_ij=SELECT MEDIAN.MESHFACE.JPOSITION, \ tim@352: MEDIAN.MESHFACE.IPOSITION \ tim@352: FROM MEDIAN.MESHFACE, \ tim@352: MEDIAN.MESH M \ tim@352: WHERE MEDIAN.MESHFACE.KPOSITION = 1 AND \ tim@352: M.MESHID = MEDIAN.MESHFACE.MESHID AND \ tim@352: M.OBJECTID = ? AND \ tim@352: INTERSECTS(SHAPE, "?") tim@352: horizontalprofile_mesh_cross_chart_data = SELECT ST_ASTEXT(SHAPE), \ tim@352: msv.DATAVALUE YORDINATE, \ tim@352: msv.PARAMETERID GROUP1, \ tim@352: MEDIAN.MESHPOINT.KPOSITION GROUP2, \ tim@352: msv.TIMEVALUE GROUP3, \ tim@352: MEDIAN.MESHPOINT.JPOSITION, \ tim@352: MEDIAN.MESHPOINT.IPOSITION, \ tim@352: 2 DATAID \ tim@352: from MEDIAN.MESHLAYER ml, \ tim@352: MEDIAN.MESHPOINT, \ tim@352: MEDIAN.MESH m, \ tim@352: MEDIAN.MESHSCALARVALUE msv \ tim@352: where msv.FEATUREID = MEDIAN.MESHPOINT.FEATUREID AND \ tim@352: ml.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \ tim@352: ml.MESHID = MEDIAN.MESHPOINT.MESHID and \ tim@352: m.MESHID = MEDIAN.MESHPOINT.MESHID AND \ tim@352: m.PARTIDMIN <= msv.PARTID AND \ tim@352: m.PARTIDMAX >= msv.PARTID AND \ tim@352: msv.PARAMETERID in (?) AND \ tim@352: msv.TIMEVALUE in (?) AND \ tim@352: m.OBJECTID = ? AND \ tim@352: ml.KPOSITION in (?) AND \ tim@352: MEDIAN.MESHPOINT.FEATUREID in \ tim@352: ( \ tim@352: SELECT distinct FEATUREID \ tim@352: FROM MEDIAN.MESHPOINT \ tim@352: WHERE ? \ tim@352: ) \ tim@352: order by msv.TIMEVALUE, \ tim@352: MEDIAN.MESHPOINT.KPOSITION, \ tim@352: msv.PARAMETERID, \ tim@352: MEDIAN.MESHPOINT.JPOSITION, \ tim@352: MEDIAN.MESHPOINT.IPOSITION tim@199: ############################################# tim@199: ############################################# tim@216: ############# Profilschnitt MESH ############ tim@199: ############################################# tim@199: ############################################# ingo@524: verticalcrosssection_mesh_year = select distinct \ ingo@524: to_char(msv.TIMEVALUE,'YYYY') KEY, \ ingo@524: to_char(msv.TIMEVALUE,'YYYY') VALUE \ ingo@524: from MEDIAN.MESHSCALARVALUE msv, \ ingo@524: MEDIAN.MESH m \ ingo@524: where m.OBJECTID = ? AND \ ingo@524: msv.PARTID >= m.PARTIDMIN AND \ ingo@524: msv.PARTID <= m.PARTIDMAX AND \ ingo@524: msv.PARAMETERID = ? \ ingo@524: order by to_char(msv.TIMEVALUE,'YYYY') tim@199: verticalcrosssection_mesh_date = select distinct \ tim@199: msv.TIMEVALUE KEY, \ tim@199: msv.TIMEVALUE VALUE \ tim@319: from MEDIAN.MESHSCALARVALUE msv, \ tim@199: MEDIAN.MESH m \ tim@199: where m.OBJECTID = ? AND \ tim@199: msv.PARTID >= m.PARTIDMIN AND \ tim@199: msv.PARTID <= m.PARTIDMAX AND \ ingo@524: msv.PARAMETERID = ? AND \ ingo@524: to_char(msv.TIMEVALUE,'YYYY') in (?) \ tim@199: order by msv.TIMEVALUE tim@199: verticalcrosssection_mesh_chart_data = SELECT ST_ASTEXT(SHAPE), \ tim@346: ((ml.UPPERZLOCATION + ml.LOWERZLOCATION) / 2) Z, \ tim@199: msv.DATAVALUE YORDINATE, \ tim@199: msv.PARAMETERID GROUP1, \ tim@199: msv.TIMEVALUE GROUP2, \ tim@199: MEDIAN.MESHPOINT.JPOSITION, \ tim@199: MEDIAN.MESHPOINT.IPOSITION, \ tim@199: MEDIAN.MESHPOINT.KPOSITION \ tim@199: from MEDIAN.MESHLAYER ml, \ tim@319: MEDIAN.MESHPOINT, \ tim@199: MEDIAN.MESH m, \ tim@199: MEDIAN.MESHSCALARVALUE msv \ tim@199: where msv.FEATUREID = MEDIAN.MESHPOINT. FEATUREID AND \ tim@199: ml.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \ tim@199: ml.MESHID = MEDIAN.MESHPOINT.MESHID and \ tim@199: m.MESHID = MEDIAN.MESHPOINT.MESHID AND \ tim@199: m.PARTIDMIN <= msv.PARTID AND \ tim@199: m.PARTIDMAX >= msv.PARTID AND \ tim@199: msv.PARAMETERID = ? AND \ tim@199: msv.TIMEVALUE = ? AND \ tim@199: m.OBJECTID = ? AND \ tim@199: MEDIAN.MESHPOINT.FEATUREID in \ ingo@428: ( \ ingo@428: SELECT distinct FEATUREID \ ingo@428: FROM MEDIAN.MESHPOINT \ ingo@428: WHERE ? \ ingo@428: ) \ tim@205: order by msv.TIMEVALUE, \ tim@205: msv.PARAMETERID, \ tim@319: MEDIAN.MESHPOINT.JPOSITION, \ tim@205: MEDIAN.MESHPOINT.IPOSITION, \ tim@216: MEDIAN.MESHPOINT.KPOSITION tim@216: tim@246: verticalcrosssection_mesh_odv_data = SELECT SI.NAME CRUISE, \ tim@246: M.MESHID || '-' || MEDIAN.MESHPOINT.IPOSITION || '-' || MEDIAN.MESHPOINT.JPOSITION STATION, \ tim@246: '*' TYPE, \ tim@246: ST_ASTEXT(SHAPE), \ tim@246: 0 BOTDEPTH, \ tim@246: (((ML.UPPERZLOCATION + ML.LOWERZLOCATION) / 2)*-1) DEPTH, \ tim@246: MSV.TIMEVALUE, \ tim@319: MSV.DATAVALUE, \ tim@246: MSV.PARAMETERID PARAMETER, \ tim@246: MEDIAN.MESHPOINT.JPOSITION, \ tim@246: MEDIAN.MESHPOINT.IPOSITION, \ ingo@336: MEDIAN.MESHPOINT.KPOSITION, \ tim@765: 2 DATAID , \ tim@765: '1' QF \ tim@246: from MEDIAN.MESHLAYER ml, \ tim@319: MEDIAN.MESHPOINT, \ tim@246: MEDIAN.MESH m, \ tim@246: MEDIAN.SOURCEINFO SI, \ tim@246: MEDIAN.MESHSCALARVALUE msv \ tim@246: where msv.FEATUREID = MEDIAN.MESHPOINT. FEATUREID AND \ tim@246: ml.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \ tim@246: ml.MESHID = MEDIAN.MESHPOINT.MESHID and \ tim@246: m.MESHID = MEDIAN.MESHPOINT.MESHID AND \ tim@246: m.PARTIDMIN <= msv.PARTID AND \ tim@246: m.PARTIDMAX >= msv.PARTID AND \ tim@246: M.SOURCEID = SI.SOURCEID AND \ tim@246: msv.PARAMETERID = ? AND \ tim@246: msv.TIMEVALUE = ? AND \ tim@246: m.OBJECTID = ? AND \ tim@246: MEDIAN.MESHPOINT.FEATUREID in \ tim@246: ( select FEATUREID \ tim@246: from MEDIAN.MESHPOINT mp, \ tim@246: MEDIAN.MESH m \ tim@246: where m.OBJECTID = ? AND \ tim@246: mp.MESHID = m.MESHID AND \ tim@246: ? = (select ? from MEDIAN.MESHPOINT where FEATUREID = ?)) \ tim@246: order by msv.TIMEVALUE, \ tim@319: MEDIAN.MESHPOINT.JPOSITION, \ tim@246: MEDIAN.MESHPOINT.IPOSITION, \ tim@765: MEDIAN.MESHPOINT.KPOSITION, \ tim@765: msv.PARAMETERID tim@246: tim@216: ############################################# tim@216: ############################################# tim@216: ########## Horizontalschnitt MESH ########### tim@216: ############################################# tim@216: ############################################# tim@216: horizontalcrosssection_meshpoint_depth = SELECT DISTINCT MP.KPOSITION KEY, \ tim@216: 'Layer ' || ML.KPOSITION || ': ' || -ML.UPPERZLOCATION || ' - '|| -ML.LOWERZLOCATION VALUE \ tim@216: from MEDIAN.MESHLAYER ML, \ tim@216: MEDIAN.MESHPOINT MP, \ tim@216: MEDIAN.MESH M \ tim@216: WHERE ML.KPOSITION = MP.KPOSITION AND \ tim@216: ML.MESHID = MP.MESHID AND \ tim@216: M.OBJECTID = ? AND \ tim@216: MP.MESHID = M.MESHID \ tim@216: ORDER BY MP.KPOSITION tim@216: ingo@532: horizontalcrosssection_mesh_year = select distinct \ ingo@532: to_char(msv.TIMEVALUE,'YYYY') KEY, \ ingo@532: to_char(msv.TIMEVALUE,'YYYY') VALUE \ ingo@532: from MEDIAN.MESHSCALARVALUE msv, \ ingo@532: MEDIAN.MESH m \ ingo@532: where m.OBJECTID = ? AND \ ingo@532: msv.PARTID >= m.PARTIDMIN AND \ ingo@532: msv.PARTID <= m.PARTIDMAX AND \ ingo@532: msv.PARAMETERID = ? \ ingo@532: order by to_char(msv.TIMEVALUE,'YYYY') tim@216: horizontalcrosssection_mesh_date = select distinct \ tim@216: msv.TIMEVALUE KEY, \ tim@216: msv.TIMEVALUE VALUE \ tim@319: from MEDIAN.MESHSCALARVALUE msv, \ tim@216: MEDIAN.MESH m \ tim@216: where m.OBJECTID = ? AND \ tim@216: msv.PARTID >= m.PARTIDMIN AND \ tim@216: msv.PARTID <= m.PARTIDMAX AND \ ingo@532: msv.PARAMETERID = ? AND \ ingo@532: to_char(msv.TIMEVALUE,'YYYY') in (?) \ tim@216: order by msv.TIMEVALUE tim@216: tim@218: horizontalcrosssection_mesh_data = SELECT ST_ASTEXT(SHAPE), \ tim@218: MSV.DATAVALUE YORDINATE, \ sascha@482: MEDIAN.MESHPOINT.JPOSITION, \ sascha@482: MEDIAN.MESHPOINT.IPOSITION, \ sascha@482: MEDIAN.MESHPOINT.KPOSITION, \ sascha@482: MSV.PARAMETERID, \ sascha@482: MSV.TIMEVALUE, \ ingo@336: 2 DATAID \ tim@218: from MEDIAN.MESHLAYER ML, \ sascha@482: MEDIAN.MESHPOINT, \ tim@218: MEDIAN.MESH M, \ tim@218: MEDIAN.MESHSCALARVALUE MSV \ sascha@482: where MSV.FEATUREID = MEDIAN.MESHPOINT.FEATUREID AND \ sascha@482: ML.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \ sascha@482: ML.MESHID = MEDIAN.MESHPOINT.MESHID and \ sascha@482: M.MESHID = MEDIAN.MESHPOINT.MESHID AND \ tim@218: M.PARTIDMIN <= MSV.PARTID AND \ tim@218: M.PARTIDMAX >= MSV.PARTID AND \ tim@218: MSV.PARAMETERID = ? AND \ tim@218: MSV.TIMEVALUE = ? AND \ tim@218: M.OBJECTID = ? AND \ sascha@482: MEDIAN.MESHPOINT.KPOSITION = ? \ sascha@482: order by MEDIAN.MESHPOINT.JPOSITION, \ sascha@482: MEDIAN.MESHPOINT.IPOSITION tim@245: horizontalcrosssection_mesh_odv_data = SELECT SI.NAME CRUISE, \ tim@245: M.MESHID || '-' || MEDIAN.MESHPOINT.IPOSITION || '-' || MEDIAN.MESHPOINT.JPOSITION STATION, \ tim@245: '*' TYPE, \ tim@245: ST_ASTEXT(SHAPE), \ tim@245: 0 BOTDEPTH, \ tim@245: (((ML.UPPERZLOCATION + ML.LOWERZLOCATION) / 2)*-1) DEPTH, \ tim@245: MSV.TIMEVALUE, \ tim@319: MSV.DATAVALUE, \ tim@245: MSV.PARAMETERID PARAMETER, \ tim@245: MEDIAN.MESHPOINT.JPOSITION, \ tim@245: MEDIAN.MESHPOINT.IPOSITION, \ tim@245: MEDIAN.MESHPOINT.KPOSITION \ tim@245: from MEDIAN.MESHLAYER ML, \ tim@245: MEDIAN.MESHPOINT, \ tim@245: MEDIAN.MESH M, \ tim@245: MEDIAN.SOURCEINFO SI, \ tim@245: MEDIAN.MESHSCALARVALUE MSV \ tim@245: where MSV.FEATUREID = MEDIAN.MESHPOINT.FEATUREID AND \ tim@245: ML.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \ tim@245: ML.MESHID = MEDIAN.MESHPOINT.MESHID and \ tim@245: M.MESHID = MEDIAN.MESHPOINT.MESHID AND \ tim@245: M.PARTIDMIN <= MSV.PARTID AND \ tim@245: M.PARTIDMAX >= MSV.PARTID AND \ tim@245: M.SOURCEID = SI.SOURCEID AND \ tim@245: MSV.PARAMETERID = ? AND \ tim@245: MSV.TIMEVALUE = ? AND \ tim@245: M.OBJECTID = ? AND \ tim@245: MEDIAN.MESHPOINT.KPOSITION = ? \ tim@319: order by MEDIAN.MESHPOINT.JPOSITION, \ tim@765: MEDIAN.MESHPOINT.IPOSITION, \ tim@765: MSV.PARAMETERID tim@223: tim@223: area_filter = SELECT DISTINCT \ tim@223: FEATUREID KEY ,\ tim@223: DESCRIPTION VALUE \ tim@223: FROM MEDIAN.FEATUREAREA \ tim@223: WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \ tim@223: FEATUREID > 0 \ tim@223: ORDER BY FEATUREID tim@223: tim@223: subarea_filter = SELECT \ tim@223: FEATURECODE KEY, \ tim@223: NAME VALUE \ tim@223: FROM MEDIAN.FEATUREAREA \ tim@223: WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \ tim@223: FEATUREID = ? \ ingo@336: ORDER BY NAME tim@455: tim@455: rasterQuery = SELECT ST_ASTEXT(RASTER) \ tim@455: FROM MEDIAN.TOPO_WORLD_2MIN \ tim@455: WHERE INTERSECTS(RASTER, "?") tim@604: tim@604: mapviewer_interface_fis_region = SELECT ID_FIS \ tim@604: FROM MEDIAN.FEATUREAREA, \ tim@604: MEDIAN.FIS_HAS_REGION FHR \ tim@604: WHERE FHR.FEATUREID = MEDIAN.FEATUREAREA.FEATUREID AND \ tim@604: FHR.FEATURETYPE = MEDIAN.FEATUREAREA.FEATURETYPE AND \ tim@604: FHR.FEATURECODE = MEDIAN.FEATUREAREA.FEATURECODE AND \ tim@604: INTERSECTS(SHAPE,"?") tim@604: tim@604: mapviewer_interface_mapservices_has_fis = SELECT DISTINCT ID_FIS, \ tim@604: ID_MAPSERVICE \ tim@604: FROM MEDIAN.FIS_HAS_MAPSERVICE \ tim@604: WHERE ID_MAPSERVICE IN (?) tim@604: tim@604: mapviewer_interface_mapservices_has_parameter = SELECT DISTINCT ID_PARAMETER \ tim@604: FROM MEDIAN.MAPSERVICE_HAS_PARAMETER \ tim@604: WHERE ID_MAPSERVICE = ? tim@604: tim@604: mapviewer_interface_mapservices_has_parameter_using_layer = SELECT DISTINCT ID_PARAMETER \ tim@604: FROM MEDIAN.LAYER_HAS_PARAMETER \ tim@604: WHERE ID_MAPSERVICE = ? AND \ tim@616: ID_LAYER IN (?) tim@616: tim@616: ############################################# tim@616: ############################################# tim@799: # Layer Contis, Nauthis and Marinefeatures # tim@616: ############################################# tim@616: ############################################# tim@616: tim@649: layer = SELECT ROW_ID KEY, \ tim@754: TITLE || '-' || LAYER_NAME || '-' || BAND VALUE \ tim@616: FROM MEDIAN.LAYER_HAS_SUBTYPES \ tim@833: WHERE ID_FEATURECLASS LIKE ? AND \ tim@833: ITEMS > 0 \ tim@616: ORDER BY LAYER_NAME tim@616: tim@616: layer_request_data = SELECT ID_FEATURECLASS, \ tim@616: QUERY_STRING, \ tim@724: ID_MAPSERVICE || '_' ||ID_LAYER \ tim@616: FROM MEDIAN.LAYER_HAS_SUBTYPES \ tim@859: WHERE ROW_ID IN (?) tim@616: tim@728: layer_data = SELECT ST_ASTEXT(SHAPE), ? \ tim@616: FROM ? \ ingo@634: WHERE ? tim@649: tim@728: layer_data_with_geom = SELECT ST_ASTEXT(SHAPE), ? \ tim@649: FROM ? \ tim@649: WHERE ? AND \ tim@649: INTERSECTS(SHAPE,"?") tim@649: tim@649: geometry_for_subareafilter=SELECT st_astext(SHAPE) \ tim@649: FROM MEDIAN.FEATUREAREA \ tim@649: WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \ tim@649: FEATURECODE = ? tim@728: tim@756: layer_colums= SELECT OWNER || '.' || TABLE_NAME || '.' || COLUMN_NAME \ tim@728: FROM SDE.COLUMN_REGISTRY \ tim@728: WHERE COLUMN_NAME NOT LIKE 'SHAPE' AND \ tim@728: OWNER = '?' AND \ tim@728: TABLE_NAME = '?' tim@799: tim@799: geometry_type = select geometry_type \ tim@799: from sde.geometry_columns \ tim@799: where f_table_schema = '?' and \ tim@799: f_table_name = '?' and \ tim@844: f_geometry_column='SHAPE' hans@1033: hans@1033: hans@1033: ############################################# hans@1033: ############################################# hans@1033: ###### Sync of cache after DB updates ####### hans@1033: ############################################# hans@1033: ############################################# tim@844: updated_tables = SELECT FULLTABLENAME \ tim@844: FROM MEDIAN.LASTUPDATED \ hans@1033: WHERE LASTUPDATE >= to_date('?', 'YYYY.MM.DD HH24:MI:SS')