Mercurial > dive4elements > gnv-client
view gnv-artifacts/doc/conf/queries.properties @ 1037:da4306683186
Added queries for TimeSeriesPoints in vectorvalues for TimeSeries
gnv-artifacts/trunk@1109 c6561f87-3c4e-4783-a992-168aeb5c3f6f
| author | Tim Englich <tim.englich@intevation.de> |
|---|---|
| date | Tue, 18 May 2010 14:27:46 +0000 |
| parents | 35c442410738 |
| children | 07b829c06981 |
line wrap: on
line source
############################################# ############################################# ########## Zeitserie ############## ############################################# ############################################# timeseries_timeseriespoint=SELECT DISTINCT \ tsp.FEATUREID KEY, \ tsp.NAME VALUE \ FROM MEDIAN.TIMESERIESPOINT tsp, \ MEDIAN.MEASUREMENT mmt \ WHERE tsp.FEATUREID = mmt.FEATUREID AND \ mmt.SOURCEID = ? \ order by tsp.name timeseries_timeseriespoint_with_area=SELECT \ MEDIAN.TIMESERIESPOINT.FEATUREID KEY, \ MEDIAN.TIMESERIESPOINT.NAME VALUE \ FROM MEDIAN.TIMESERIESPOINT, \ MEDIAN.MEASUREMENT MMT \ WHERE MEDIAN.TIMESERIESPOINT.FEATUREID = MMT.FEATUREID AND \ MMT.SOURCEID = ? AND \ INTERSECTS(SHAPE, \ (SELECT st_astext(SHAPE) \ FROM MEDIAN.FEATUREAREA \ WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND \ FEATURECODE = ? )\ )\ ORDER BY MEDIAN.TIMESERIESPOINT.NAME timeseries_timeseriespoint_with_wkt=SELECT \ MEDIAN.TIMESERIESPOINT.FEATUREID KEY, \ MEDIAN.TIMESERIESPOINT.NAME VALUE \ FROM MEDIAN.TIMESERIESPOINT, \ MEDIAN.MEASUREMENT MMT \ WHERE MEDIAN.TIMESERIESPOINT.FEATUREID = MMT.FEATUREID AND \ MMT.SOURCEID = ? AND \ INTERSECTS(SHAPE,"?")\ ORDER BY MEDIAN.TIMESERIESPOINT.NAME timeseries_parameter=SELECT DISTINCT \ p.PARAMETERID KEY, \ p.GERMANNAME || ' ['|| p.UNIT ||']' VALUE, \ p.GERMANNAME \ FROM MEDIAN.PARAMETER P, \ MEDIAN.TIMESERIES TS, \ MEDIAN.TIMESERIESVALUE TSV, \ MEDIAN.MEASUREMENT M, \ MEDIAN.TIMESERIESPOINT TSP \ WHERE M.FEATUREID = TSP.FEATUREID AND \ M.MEASUREMENTID = TSV.MEASUREMENTID AND \ TS.TIMESERIESID = TSV.TIMESERIESID AND \ P.PARAMETERID = TS.PARAMETERID AND \ TSP.FEATUREID = ? \ ORDER BY P.GERMANNAME timeseries_depth_height=SELECT DISTINCT \ M.MEASUREMENTID KEY, \ M.ZLOCATION VALUE, \ P.PARAMETERID PARAMETERID \ FROM MEDIAN.MEASUREMENT M, \ MEDIAN.TIMESERIESVALUE TSV, \ MEDIAN.TIMESERIES T, \ MEDIAN.PARAMETER P \ WHERE M.MEASUREMENTID = TSV.MEASUREMENTID AND \ TSV.TIMESERIESID = T.TIMESERIESID AND \ T.PARAMETERID = P.PARAMETERID AND \ M.FEATUREID = ? AND \ P.PARAMETERID IN (?)\ ORDER BY m.ZLOCATION DESC timeseries_interval=select min(tv.TIMEVALUE) MIN, \ max(tv.TIMEVALUE) MAX \ from MEDIAN.TIMESERIES t, \ MEDIAN.TIMESERIESVALUE tv \ where tv.TIMESERIESID = t.TIMESERIESID AND \ t.PARAMETERID IN ( ? ) AND \ tv.MEASUREMENTID IN ( ? ) timeseries_chart_data=SELECT tv.TIMEVALUE XORDINATE, \ tv.DATAVALUE YORDINATE, \ t.PARAMETERID GROUP1, \ tv.MEASUREMENTID GROUP2, \ tv.TIMESERIESID GROUP3, \ t.TIMEINTERVAL GAPID \ FROM MEDIAN.TIMESERIESVALUE tv, \ MEDIAN.TIMESERIES t \ WHERE tv.TIMESERIESID = t.TIMESERIESID AND \ t.PARAMETERID IN ( ? ) AND \ tv.MEASUREMENTID IN ( ? ) AND \ tv.TIMEVALUE >= ? AND \ tv.TIMEVALUE <= ? \ ORDER BY tv.MEASUREMENTID, \ tv.TIMESERIESID, \ t.PARAMETERID, \ tv.TIMEVALUE timeseries_odv_data = SELECT SI.NAME CRUISE, \ MEDIAN.TIMESERIESPOINT.NAME STATION, \ '*' TYPE, \ ST_ASTEXT(SHAPE), \ 0 BOTDEPTH, \ (M.ZLOCATION *-1) DEPTH, \ TSV.TIMEVALUE, \ TSV.DATAVALUE, \ TS.PARAMETERID PARAMETER, \ TSV.MEASUREMENTID, \ TSV.TIMESERIESID, \ '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 ############## ########## Vektordaten ############### ############################################# ############################################# timeseries_parameter_vector=SELECT DISTINCT \ p.PARAMETERID KEY, \ p.GERMANNAME || ' ['|| p.UNIT ||']' VALUE, \ p.GERMANNAME \ FROM MEDIAN.PARAMETER P, \ MEDIAN.TIMESERIES TS, \ MEDIAN.TIMESERIESVECTORVALUE 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_vector=SELECT DISTINCT \ M.MEASUREMENTID KEY, \ M.ZLOCATION VALUE, \ P.PARAMETERID PARAMETERID \ FROM MEDIAN.MEASUREMENT M, \ MEDIAN.TIMESERIESVECTORVALUE 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 = ? \ ORDER BY m.ZLOCATION DESC timeseries_interval_vector=select min(tsv.TIMEVALUE) MIN, \ max(tsv.TIMEVALUE) MAX \ from MEDIAN.TIMESERIES t, \ MEDIAN.TIMESERIESVECTORVALUE tsv \ where tsv.TIMESERIESID = t.TIMESERIESID AND \ t.PARAMETERID = ? AND \ tsv.MEASUREMENTID = ? timeseries_chart_data_vector=SELECT tsv.TIMEVALUE XORDINATE, \ tsv.XCOMPONENT, \ tsv.YCOMPONENT, \ tsv.ZCOMPONENT, \ 1 SPEED, \ 1 DIRECTION, \ t.PARAMETERID GROUP1, \ tsv.MEASUREMENTID GROUP2, \ tsv.TIMESERIESID GROUP3, \ t.TIMEINTERVAL GAPID \ FROM MEDIAN.TIMESERIESVECTORVALUE tsv, \ MEDIAN.TIMESERIES t \ WHERE tsv.TIMESERIESID = t.TIMESERIESID AND \ t.PARAMETERID = ? AND \ tsv.MEASUREMENTID = ? AND \ tsv.TIMEVALUE >= ? AND \ tsv.TIMEVALUE <= ? \ ORDER BY tsv.MEASUREMENTID, \ tsv.TIMESERIESID, \ t.PARAMETERID, \ tsv.TIMEVALUE timeseries_odv_data_vector = SELECT SI.NAME CRUISE, \ MEDIAN.TIMESERIESPOINT.NAME STATION, \ '*' TYPE, \ ST_ASTEXT(SHAPE), \ 0 BOTDEPTH, \ (M.ZLOCATION *-1) DEPTH, \ TSV.TIMEVALUE, \ TSV.XCOMPONENT, \ TSV.YCOMPONENT, \ TSV.ZCOMPONENT, \ 1 SPEED, \ 1 DIRECTION, \ TS.PARAMETERID PARAMETER, \ TSV.MEASUREMENTID, \ TSV.TIMESERIESID, \ '1' QF \ FROM MEDIAN.TIMESERIESPOINT, \ MEDIAN.TIMESERIESVECTORVALUE 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 = ? AND \ TSV.MEASUREMENTID = ? 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 ? AND \ ITEMS > 0 \ ORDER BY LAYER_NAME layer_request_data = SELECT ID_FEATURECLASS, \ QUERY_STRING, \ ID_MAPSERVICE || '_' ||ID_LAYER \ FROM MEDIAN.LAYER_HAS_SUBTYPES \ WHERE ROW_ID IN (?) 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' ############################################# ############################################# ###### Sync of cache after DB updates ####### ############################################# ############################################# updated_tables = SELECT FULLTABLENAME \ FROM MEDIAN.LASTUPDATED \ WHERE LASTUPDATE >= to_date('?', 'YYYY.MM.DD HH24:MI:SS')