tim@83: ############################################# tim@83: ############################################# tim@83: ########## Zeitserie ############## tim@83: ############################################# tim@83: ############################################# tim@83: tim@62: 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 tim@62: timeseries_parameter=SELECT DISTINCT p.PARAMETERID KEY, p.GERMANNAME VALUE from MEDIAN.PARAMETER p where p.PARAMETERID in (select distinct ts.PARAMETERID from MEDIAN.TIMESERIES ts where ts.TIMESERIESID in (select distinct tsv.TIMESERIESID from MEDIAN.TIMESERIESVALUE tsv where tsv.MEASUREMENTID in (select m.MEASUREMENTID from MEDIAN.MEASUREMENT m, MEDIAN.TIMESERIESPOINT tsp where m.FEATUREID = tsp.FEATUREID and tsp.FEATUREID IN ( ? )))) ORDER BY p.GERMANNAME tim@62: timeseries_depth_height=SELECT DISTINCT m.MEASUREMENTID KEY, m.ZLOCATION VALUE from MEDIAN.MEASUREMENT m where m.MEASUREMENTID in (SELECT DISTINCT t_v.MEASUREMENTID from MEDIAN.TIMESERIESVALUE t_v where t_v.TIMESERIESID in (SELECT DISTINCT t.TIMESERIESID from MEDIAN.TIMESERIES t where t.PARAMETERID in (SELECT DISTINCT p.PARAMETERID from MEDIAN.PARAMETER p where m.FEATUREID IN ( ? ) and p.PARAMETERID IN (?))))ORDER BY m.ZLOCATION DESC tim@69: 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 ( ? ) tim@83: timeseries_chart_data=SELECT tv.TIMEVALUE XORDINATE, tv.DATAVALUE YORDINATE, t.PARAMETERID GROUP1, tv.MEASUREMENTID GROUP2, tv.TIMESERIESID GROUP3 FROM MEDIAN.TIMESERIESVALUE tv, MEDIAN.TIMESERIES t WHERE tv.TIMESERIESID = t.TIMESERIESID AND t.PARAMETERID IN ( ? ) AND tv.MEASUREMENTID IN ( ? ) AND tv.TIMEVALUE > TO_DATE (?, 'YYYY.MM.DD HH24:MI:SS') AND tv.TIMEVALUE < TO_DATE (?, 'YYYY.MM.DD HH24:MI:SS') ORDER BY tv.MEASUREMENTID ,tv.TIMESERIESID ,t.PARAMETERID ,tv.TIMEVALUE tim@81: tim@83: ############################################# tim@83: ############################################# tim@83: ########## Zeitserie Mesh ############## tim@83: ############################################# tim@83: ############################################# tim@81: tim@81: timeseries_mesh = SELECT OBJECTID KEY, m.NAME VALUE FROM MEDIAN.MESH m order by m.NAME tim@81: timeseries_meshpoint = SELECT FEATUREID KEY, SHAPE VALUE FROM MEDIAN.MESHPOINT mp , MEDIAN.MESH m WHERE m.OBJECTID = ? AND mp.MESHID = m.MESHID AND KPOSITION = 1 AND rownum < 10 tim@83: timeseries_meshpoint_depth = select mp.FEATUREID KEY, \ tim@83: -ml.UPPERZLOCATION || ' - '|| -ml.LOWERZLOCATION as VALUE \ tim@83: from MEDIAN.MESHLAYER ml, \ tim@83: MEDIAN.MESHPOINT mp \ tim@83: where ml.KPOSITION = mp.KPOSITION and \ tim@83: ml.MESHID = mp.MESHID and \ tim@83: mp.FEATUREID in \ tim@83: ( select FEATUREID \ tim@83: from MEDIAN.MESHPOINT mp, \ tim@83: MEDIAN.MESH m \ tim@83: where m.OBJECTID = ? AND \ tim@83: mp.MESHID = m.MESHID AND \ tim@83: IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) and \ tim@83: JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?)) \ tim@83: order by ml.UPPERZLOCATION desc tim@81: timeseries_mesh_parameter=SELECT distinct p.PARAMETERID KEY , p.GERMANNAME VALUE from MEDIAN.PARAMETER p, MEDIAN.MESHSCALARVALUE msc, MEDIAN.MESH m where m.OBJECTID = ? AND msc.PARTID = m.PARTIDMIN AND msc.PARAMETERID = p.PARAMETERID tim@81: 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 tim@83: timeseries_mesh_chart_data=select /*+ parallel(timevalue,10) */ msv.TIMEVALUE XORDINATE, msv.DATAVALUE YORDINATE, msv.PARAMETERID GROUP1, msv.FEATUREID GROUP2, mp.FEATUREID GROUP3 from MEDIAN.MESHSCALARVALUE msv , MEDIAN.MESHPOINT mp, MEDIAN.MESH m where (m.OBJECTID = ? AND msv.PARTID >= m.PARTIDMIN AND msv.PARTID <= m.PARTIDMAX ) AND msv.FEATUREID in ( ? ) and msv.PARAMETERID in ( ? ) AND mp.FEATUREID = ? and TIMEVALUE >= to_date(?, 'YYYY.MM.DD HH24:MI:SS') and TIMEVALUE <= to_date(?, 'YYYY.MM.DD HH24:MI:SS') order by msv.FEATUREID, msv.PARAMETERID, msv.TIMEVALUE tim@81: tim@83: ############################################# tim@83: ############################################# tim@83: ########## Vertikalprofil ############## tim@83: ############################################# tim@83: ############################################# tim@82: tim@82: verticalprofile_point=SELECT DISTINCT \ tim@82: tsp.FEATUREID KEY, \ tim@82: tsp.NAME VALUE \ tim@82: FROM MEDIAN.TIMESERIESPOINT tsp, \ tim@82: MEDIAN.MEASUREMENT mmt \ tim@82: WHERE tsp.FEATUREID = mmt.FEATUREID AND \ tim@82: mmt.SOURCEID = ? \ tim@82: order by tsp.name tim@82: tim@82: verticalprofile_parameter=SELECT DISTINCT \ tim@82: p.PARAMETERID KEY, \ tim@82: p.GERMANNAME VALUE \ tim@82: from MEDIAN.PARAMETER p, \ tim@82: MEDIAN.TIMESERIES ts, \ tim@82: MEDIAN.TIMESERIESVALUE tsv, \ tim@82: MEDIAN.MEASUREMENT m \ tim@82: where ts.PARAMETERID = p.PARAMETERID and \ tim@82: ts.TIMESERIESID = tsv.TIMESERIESID and \ tim@82: m.MEASUREMENTID = tsv.MEASUREMENTID and \ tim@82: m.FEATUREID = ? \ tim@82: ORDER BY p.GERMANNAME tim@82: tim@82: verticalprofile_date=select distinct \ tim@82: tsv.TIMEVALUE KEY, \ tim@82: tsv.TIMEVALUE VALUE \ tim@82: from MEDIAN.TIMESERIES ts , \ tim@82: MEDIAN.TIMESERIESVALUE tsv, \ tim@82: MEDIAN.MEASUREMENT m \ tim@82: where ts.TIMESERIESID = tsv.TIMESERIESID and \ tim@82: m.MEASUREMENTID = tsv.MEASUREMENTID and \ tim@82: m.FEATUREID = ? and \ tim@82: ts.PARAMETERID IN ( ? ) \ tim@82: order by tsv.TIMEVALUE tim@82: tim@82: verticalprofile_chart_data= SELECT m.ZLOCATION XORDINATE, \ tim@82: tsv.DATAVALUE YORDINATE, \ tim@82: ts.PARAMETERID GROUP1, \ tim@82: tsv.TIMEVALUE GROUP2, \ tim@82: 1 GROUP3 \ tim@82: from MEDIAN.TIMESERIES ts, \ tim@82: MEDIAN.TIMESERIESVALUE tsv, \ tim@82: MEDIAN.MEASUREMENT m \ tim@82: where ts.TIMESERIESID = tsv.TIMESERIESID and \ tim@82: m.MEASUREMENTID = tsv.MEASUREMENTID and \ tim@82: m.FEATUREID = ? and \ tim@82: ts.PARAMETERID IN ( ? ) AND \ tim@83: tsv.TIMEVALUE IN (?) \ tim@82: ORDER BY tsv.TIMEVALUE , \ tim@82: tsv.TIMESERIESID , \ tim@82: ts.PARAMETERID , \ tim@82: tsv.TIMEVALUE tim@83: tim@83: ############################################# tim@83: ############################################# tim@83: ########## Vertikalprofil Mesh ############## tim@83: ############################################# tim@83: ############################################# tim@83: tim@83: verticalprofile_mesh = SELECT OBJECTID KEY, \ tim@83: m.NAME VALUE \ tim@83: FROM MEDIAN.MESH m \ tim@83: order by m.NAME tim@83: tim@83: verticalprofile_mesh_point = SELECT FEATUREID KEY, \ tim@83: SHAPE VALUE \ tim@83: FROM MEDIAN.MESHPOINT mp , \ tim@83: MEDIAN.MESH m \ tim@83: WHERE m.OBJECTID = ? AND \ tim@83: m.MESHID = mp.MESHID AND \ tim@83: mp.KPOSITION = 1 AND \ tim@83: rownum < 10 tim@83: tim@83: verticalprofile_mesh_parameter=SELECT distinct \ tim@83: p.PARAMETERID KEY , \ tim@83: p.GERMANNAME VALUE \ tim@83: from MEDIAN.PARAMETER p, \ tim@83: MEDIAN.MESHSCALARVALUE msc, \ tim@83: MEDIAN.MESH m \ tim@83: where m.OBJECTID = ? AND \ tim@83: m.PARTIDMIN = msc.PARTID AND \ tim@83: msc.PARAMETERID = p.PARAMETERID \ tim@83: order by p.GERMANNAME tim@83: tim@83: verticalprofile_mesh_date= select distinct \ tim@83: msv.TIMEVALUE KEY, \ tim@83: msv.TIMEVALUE VALUE \ tim@83: from MEDIAN.MESHSCALARVALUE msv , \ tim@83: MEDIAN.MESH m \ tim@83: where m.OBJECTID = ? AND \ tim@83: msv.PARTID >= m.PARTIDMIN AND \ tim@83: msv.PARTID <= m.PARTIDMAX \ tim@83: order by msv.TIMEVALUE tim@83: tim@83: verticalprofile_mesh_chart_data=select ml.UPPERZLOCATION XORDINATE, \ tim@83: msv.DATAVALUE YORDINATE, \ tim@83: msv.PARAMETERID GROUP1, \ tim@83: msv.TIMEVALUE GROUP2, \ tim@83: 1 GROUP3 \ tim@83: from MEDIAN.MESHLAYER ml, \ tim@83: MEDIAN.MESHPOINT mp, \ tim@83: MEDIAN.MESH m, \ tim@83: MEDIAN.MESHSCALARVALUE msv \ tim@83: where msv.FEATUREID = mp. FEATUREID AND \ tim@83: ml.KPOSITION = mp.KPOSITION and \ tim@83: ml.MESHID = mp.MESHID and \ tim@83: m.MESHID = mp.MESHID AND \ tim@83: m.PARTIDMIN <= msv.PARTID AND \ tim@83: m.PARTIDMAX >= msv.PARTID AND \ tim@83: msv.PARAMETERID in (?) AND \ tim@83: msv.TIMEVALUE in (?) AND \ tim@83: m.OBJECTID = ? AND \ tim@83: mp.FEATUREID in \ tim@83: ( select FEATUREID \ tim@83: from MEDIAN.MESHPOINT mp, \ tim@83: MEDIAN.MESH m \ tim@83: where m.OBJECTID = ? AND \ tim@83: mp.MESHID = m.MESHID AND \ tim@83: IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?) and \ tim@83: JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = ?)) \ tim@83: order by msv.PARAMETERID, \ tim@83: msv.TIMEVALUE, \ tim@83: ml.UPPERZLOCATION tim@83: tim@83: