Mercurial > dive4elements > gnv-client
diff gnv-artifacts/doc/conf/queries.properties @ 540:80630520e25a
merged gnv-artifacts/0.4
| author | Thomas Arendsen Hein <thomas@intevation.de> |
|---|---|
| date | Fri, 28 Sep 2012 12:13:49 +0200 |
| parents | d2f97cb2ac32 |
| children | 938ce81a6bd0 |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/gnv-artifacts/doc/conf/queries.properties Fri Sep 28 12:13:49 2012 +0200 @@ -0,0 +1,1193 @@ +############################################# +############################################# +########## 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_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 \ + 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 \ + 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 TSV.MEASUREMENTID, \ + TSV.TIMESERIESID, \ + TS.PARAMETERID, \ + 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.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 \ + 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.PARAMETERID, \ + msv.TIMEVALUE + + +############################################# +############################################# +########## 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 \ + 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 \ + 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, \ + TS.PARAMETERID, \ + TSV.TIMEVALUE + +############################################# +############################################# +########## 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_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 \ + m.PARTIDMIN = msc.PARTID 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 \ + 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 \ + 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.PARAMETERID, \ + msv.TIMEVALUE, \ + ml.UPPERZLOCATION + +############################################# +############################################# +##### 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_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_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 \ + 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 \ + 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, \ + MSV.PARAMETERID, \ + M.ZLOCATION + +############################################# +############################################# +#### 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_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_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_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 \ + 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 \ + 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 \ + 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 \ + 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, \ + msv.PARAMETERID, \ + MEDIAN.MESHPOINT.JPOSITION, \ + MEDIAN.MESHPOINT.IPOSITION + +############################################# +############################################# +########### 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 \ + 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, \ + msv.PARAMETERID, \ + MEDIAN.MESHPOINT.JPOSITION, \ + MEDIAN.MESHPOINT.IPOSITION, \ + MEDIAN.MESHPOINT.KPOSITION + +############################################# +############################################# +########## 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 + +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, "?")