Mercurial > dive4elements > gnv-client
view gnv-artifacts/src/test/ressources/queries.properties @ 81:9b41f3688610
Added Support for TimeSeriesMesh
gnv-artifacts/trunk@106 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author | Tim Englich <tim.englich@intevation.de> |
---|---|
date | Mon, 21 Sep 2009 11:50:47 +0000 |
parents | f24c825144bc |
children | 5eb62df21f9a |
line wrap: on
line source
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_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 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 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, tv.DATAVALUE, t.PARAMETERID, tv.MEASUREMENTID, tv.TIMESERIESID 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 # Zeitserie Mesh timeseries_mesh = SELECT OBJECTID KEY, m.NAME VALUE FROM MEDIAN.MESH m order by m.NAME 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 timeseries_meshpoint_depth = select mp.FEATUREID KEY, -ml.UPPERZLOCATION || ' - '|| -ml.LOWERZLOCATION as 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 ml.UPPERZLOCATION desc 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 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 timeseries_mesh_chart_data=select /*+ parallel(timevalue,10) */ msv.TIMEVALUE, msv.DATAVALUE, msv.PARAMETERID, msv.FEATUREID MEASUREMENTID, mp.FEATUREID TIMESERIESID 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