Mercurial > dive4elements > gnv-client
view geo-backend/src/test/ressources/QueryExecutorTestCase.properties @ 377:6a585e87c18b
Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
geo-backend/trunk@377 c6561f87-3c4e-4783-a992-168aeb5c3f6f
| author | Tim Englich <tim.englich@intevation.de> |
|---|---|
| date | Fri, 27 Nov 2009 12:59:04 +0000 |
| parents | 9063c5fcebf7 |
| children | 31595c0a1a33 |
line wrap: on
line source
mesh=Select MESHID, NAME from MEDIAN.MESH mesh_id=Select MESHID, NAME from MEDIAN.MESH where MESHID = ? # Timeseries timeseries_stations=SELECT tsp.FEATUREID, tsp.NAME FROM MEDIAN.TIMESERIESPOINT tsp WHERE tsp.FEATUREID IN (SELECT DISTINCT mmt.FEATUREID FROM MEDIAN.MEASUREMENT mmt WHERE mmt.SOURCEID IN ( 4 )) order by tsp.name timeseries_stations_op=SELECT DISTINCT tsp.FEATUREID, tsp.NAME FROM MEDIAN.TIMESERIESPOINT tsp, MEDIAN.MEASUREMENT mmt WHERE tsp.FEATUREID = mmt.FEATUREID AND mmt.SOURCEID IN ( 4 ) order by tsp.name timeseries_interval=select min(tv.TIMEVALUE) MIN, max(tv.TIMEVALUE) MAX from MEDIAN.TIMESERIES t , MEDIAN.TIMESERIESVALUE tv where tv.TIMESERIESID = t.TIMESERIESID and tv.MEASUREMENTID IN ( ? ) AND t.PARAMETERID IN ( ? ) spatial_query=SELECT FEATUREID, \ ST_ASTEXT(SHAPE) \ FROM MEDIAN.MESHPOINT \ WHERE MESHID in \ (SELECT DISTINCT m.MESHID \ FROM MEDIAN.MESH m \ WHERE M.OBJECTID = M.OBJECTID) AND \ KPOSITION = 1 AND \ INTERSECTS(SHAPE,"POLYGON((6.483333296817733 56.50000002211891,6.483012102287745 56.50981053688071,6.48205002744038 56.51957897448855,6.480451095589132 56.52926359306454,6.478222179894658 56.538822818368885,6.475372835726546 56.54821591435217,6.471915300663454 56.557402480241024,6.46786415921693 56.56634328872852,6.463237181021697 56.57500011833619,6.458053812093169 56.583335585775785,6.452336348293812 56.591314319415915,6.446109264780915 56.59890195345352,6.439399383644684 56.60606613374233,6.432235203355873 56.61277601487856,6.424647569318296 56.61900309839146,6.416668835678138 56.62472056219082,6.408333368238544 56.629903931119344,6.399676538630871 56.63453090931458,6.390735730143376 56.6385820507611,6.381549164254523 56.64203958582419,6.372156068271238 56.644888929992305,6.362596842966894 56.64711784568678,6.352912224390906 56.64871677753803,6.34314378678306 56.64967885238539,6.333333272021264 56.65000004691538,6.323522757259468 56.64967885238539,6.31375431965165 56.64871677753803,6.304069701075662 56.64711784568678,6.29451047577129 56.644888929992305,6.285117379788005 56.64203958582419,6.27593081389918 56.6385820507611,6.266990005411657 56.63453090931458,6.258333343442075 56.629903931119344,6.24999770836439 56.62472056219082,6.24201897472426 56.61900309839146,6.234431340686655 56.61277601487856,6.227267160397872 56.60606613374233,6.220557279261612 56.59890195345352,6.214330195748715 56.591314319415915,6.2086127319493585 56.583335585775785,6.203429363020831 56.57500011833619,6.198802384825626 56.56634328872852,6.194751243379102 56.557402480241024,6.19129370831601 56.54821591435217,6.188444364147898 56.538822818368885,6.186215448453396 56.52926359306454,6.184616516602176 56.51957897448855,6.183654441754783 56.50981053688071,6.183333247224795 56.50000002211891,6.183654441754783 56.490189507357115,6.184616516602176 56.4804210697493,6.186215448453396 56.47073645117331,6.188444364147898 56.46117722586894,6.19129370831601 56.45178412988565,6.194751243379102 56.44259756399683,6.198802384825626 56.433656755509304,6.203429363020831 56.42500009353972,6.2086127319493585 56.41666445846204,6.214330195748715 56.40868572482191,6.220557279261612 56.4010980907843,6.227267160397872 56.39393391049552,6.234431340686655 56.38722402935926,6.24201897472426 56.38099694584636,6.24999770836439 56.375279482047006,6.258333343442075 56.37009611311848,6.266990005411657 56.36546913492327,6.27593081389918 56.36141799347675,6.285117379788005 56.35796045841366,6.29451047577129 56.355111114245545,6.304069701075662 56.35288219855104,6.31375431965165 56.351283266699795,6.323522757259468 56.35032119185243,6.333333272021264 56.34999999732244,6.34314378678306 56.35032119185243,6.352912224390906 56.351283266699795,6.362596842966894 56.35288219855104,6.372156068271238 56.355111114245545,6.381549164254523 56.35796045841366,6.390735730143376 56.36141799347675,6.399676538630871 56.36546913492327,6.408333368238544 56.37009611311848,6.416668835678138 56.375279482047006,6.424647569318296 56.38099694584636,6.432235203355873 56.38722402935926,6.439399383644684 56.39393391049552,6.446109264780915 56.4010980907843,6.452336348293812 56.40868572482191,6.458053812093169 56.41666445846204,6.463237181021697 56.42500009353972,6.46786415921693 56.433656755509304,6.471915300663454 56.44259756399683,6.475372835726546 56.45178412988565,6.478222179894658 56.46117722586894,6.480451095589132 56.47073645117331,6.48205002744038 56.4804210697493,6.483012102287745 56.490189507357115,6.483333296817733 56.50000002211891))") spatial_query_without_intersects = SELECT ST_ASTEXT(SHAPE), \ MSV.DATAVALUE YORDINATE , \ MSV.PARAMETERID GROUP1 , \ M.ZLOCATION GROUP2, \ 1 GROUP3 \ FROM MEDIAN.INSTANTANEOUSPOINT, \ MEDIAN.MEASUREMENT M, \ MEDIAN.MEASUREDSCALARVALUE MSV \ WHERE MEDIAN.INSTANTANEOUSPOINT.FEATUREID =m.FEATUREID AND \ m.MEASUREMENTID = MSV.MEASUREMENTID AND \ MEDIAN.INSTANTANEOUSPOINT.SURVEYID = 1895 AND \ m.ZLOCATION IN (-16.843 , -15.852) AND \ MSV.PARAMETERID in (2 , 1) \ ORDER BY MSV.PARAMETERID, \ m.ZLOCATION spatial_query_without_intersects2 = SELECT ST_ASTEXT(SHAPE), \ msv.DATAVALUE YORDINATE, \ msv.PARAMETERID GROUP1, \ msv.TIMEVALUE GROUP2, \ MEDIAN.MESHPOINT.KPOSITION GROUP3, \ MEDIAN.MESHPOINT.JPOSITION , \ MEDIAN.MESHPOINT.IPOSITION \ 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 (2,1) AND \ msv.TIMEVALUE in (to_date('2009.10.15 23:00:00', 'YYYY.MM.DD HH24:MI:SS')) AND \ m.OBJECTID = 3 AND \ MEDIAN.MESHPOINT.FEATUREID in \ ( select FEATUREID \ from MEDIAN.MESHPOINT mp, \ MEDIAN.MESH m \ where m.OBJECTID = 3 AND \ mp.MESHID = m.MESHID AND \ KPOSITION in ( 1 ) and \ JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = 2003771)) \ order by MEDIAN.MESHPOINT.KPOSITION, \ msv.TIMEVALUE, \ msv.PARAMETERID, \ MEDIAN.MESHPOINT.JPOSITION , \ MEDIAN.MESHPOINT.IPOSITION spatial_query_without_intersects3 = SELECT ST_ASTEXT(SHAPE), MSV.DATAVALUE YORDINATE, MEDIAN.MESHFACE.JPOSITION, MEDIAN.MESHFACE.IPOSITION, MEDIAN.MESHFACE.KPOSITION from MEDIAN.MESHLAYER ML, MEDIAN.MESHFACE, MEDIAN.MESH M, MEDIAN.MESHSCALARVALUE MSV where MSV.FEATUREID = MEDIAN.MESHFACE.FEATUREID AND ML.KPOSITION = MEDIAN.MESHFACE.KPOSITION and ML.MESHID = MEDIAN.MESHFACE.MESHID and M.MESHID = MEDIAN.MESHFACE.MESHID AND M.PARTIDMIN <= MSV.PARTID AND M.PARTIDMAX >= MSV.PARTID AND MSV.PARAMETERID = 1 AND MSV.TIMEVALUE = to_date('2007.01.03 00:00:00', 'YYYY.MM.DD HH24:MI:SS') AND M.OBJECTID = 2 AND MEDIAN.MESHFACE.KPOSITION = 1 order by MEDIAN.MESHFACE.JPOSITION , MEDIAN.MESHFACE.IPOSITION spatial_query_without_intersects4 = SELECT ST_ASTEXT(SHAPE), MSV.DATAVALUE YORDINATE, MEDIAN.MESHFACE.JPOSITION, MEDIAN.MESHFACE.IPOSITION, MEDIAN.MESHFACE.KPOSITION from MEDIAN.MESHLAYER ML, MEDIAN.MESHFACE, MEDIAN.MESH M, MEDIAN.MESHSCALARVALUE MSV where MSV.FEATUREID = MEDIAN.MESHFACE.FEATUREID AND ML.KPOSITION = MEDIAN.MESHFACE.KPOSITION and ML.MESHID = MEDIAN.MESHFACE.MESHID and M.MESHID = MEDIAN.MESHFACE.MESHID AND M.PARTIDMIN <= MSV.PARTID AND M.PARTIDMAX >= MSV.PARTID AND MSV.PARAMETERID = 1 AND MSV.TIMEVALUE = to_date('2008.01.15 12:00:00', 'YYYY.MM.DD HH24:MI:SS') AND M.OBJECTID = 321 AND MEDIAN.MESHFACE.KPOSITION = 1 order by MEDIAN.MESHFACE.JPOSITION , MEDIAN.MESHFACE.IPOSITION spatial_query_without_intersects5 = SELECT ST_ASTEXT(SHAPE), MSV.DATAVALUE YORDINATE, MEDIAN.MESHFACE.JPOSITION, MEDIAN.MESHFACE.IPOSITION, MEDIAN.MESHFACE.KPOSITION from MEDIAN.MESHLAYER ML, MEDIAN.MESHFACE, MEDIAN.MESH M, MEDIAN.MESHSCALARVALUE MSV where MSV.FEATUREID = MEDIAN.MESHFACE.FEATUREID AND ML.KPOSITION = MEDIAN.MESHFACE.KPOSITION and ML.MESHID = MEDIAN.MESHFACE.MESHID and M.MESHID = MEDIAN.MESHFACE.MESHID AND M.PARTIDMIN <= MSV.PARTID AND M.PARTIDMAX >= MSV.PARTID AND MSV.PARAMETERID = 2 AND MSV.TIMEVALUE = to_date('2009.10.23 01:00:00', 'YYYY.MM.DD HH24:MI:SS') AND M.OBJECTID = 1 AND MEDIAN.MESHFACE.KPOSITION = 1 order by MEDIAN.MESHFACE.JPOSITION , MEDIAN.MESHFACE.IPOSITION spatial_query_without_intersects6 = SELECT ST_ASTEXT(SHAPE), MSV.DATAVALUE YORDINATE, MEDIAN.MESHFACE.JPOSITION, MEDIAN.MESHFACE.IPOSITION, MEDIAN.MESHFACE.KPOSITION from MEDIAN.MESHLAYER ML, MEDIAN.MESHFACE, MEDIAN.MESH M, MEDIAN.MESHSCALARVALUE MSV where MSV.FEATUREID = MEDIAN.MESHFACE.FEATUREID AND ML.KPOSITION = MEDIAN.MESHFACE.KPOSITION and ML.MESHID = MEDIAN.MESHFACE.MESHID and M.MESHID = MEDIAN.MESHFACE.MESHID AND M.PARTIDMIN <= MSV.PARTID AND M.PARTIDMAX >= MSV.PARTID AND MSV.PARAMETERID = 2 AND MSV.TIMEVALUE = to_date('2009.10.23 01:00:00', 'YYYY.MM.DD HH24:MI:SS') AND M.OBJECTID = 3 AND MEDIAN.MESHFACE.KPOSITION = 1 order by MEDIAN.MESHFACE.JPOSITION , MEDIAN.MESHFACE.IPOSITION choose_fis_query = select sourceid \ from median.meshpoint, \ median.mesh \ where median.meshpoint.meshid = median.mesh.meshid and \ INTERSECTS(SHAPE,"POLYGON((6.483333296817733 56.50000002211891,6.483012102287745 56.50981053688071,6.48205002744038 56.51957897448855,6.480451095589132 56.52926359306454,6.478222179894658 56.538822818368885,6.475372835726546 56.54821591435217,6.471915300663454 56.557402480241024,6.46786415921693 56.56634328872852,6.463237181021697 56.57500011833619,6.458053812093169 56.583335585775785,6.452336348293812 56.591314319415915,6.446109264780915 56.59890195345352,6.439399383644684 56.60606613374233,6.432235203355873 56.61277601487856,6.424647569318296 56.61900309839146,6.416668835678138 56.62472056219082,6.408333368238544 56.629903931119344,6.399676538630871 56.63453090931458,6.390735730143376 56.6385820507611,6.381549164254523 56.64203958582419,6.372156068271238 56.644888929992305,6.362596842966894 56.64711784568678,6.352912224390906 56.64871677753803,6.34314378678306 56.64967885238539,6.333333272021264 56.65000004691538,6.323522757259468 56.64967885238539,6.31375431965165 56.64871677753803,6.304069701075662 56.64711784568678,6.29451047577129 56.644888929992305,6.285117379788005 56.64203958582419,6.27593081389918 56.6385820507611,6.266990005411657 56.63453090931458,6.258333343442075 56.629903931119344,6.24999770836439 56.62472056219082,6.24201897472426 56.61900309839146,6.234431340686655 56.61277601487856,6.227267160397872 56.60606613374233,6.220557279261612 56.59890195345352,6.214330195748715 56.591314319415915,6.2086127319493585 56.583335585775785,6.203429363020831 56.57500011833619,6.198802384825626 56.56634328872852,6.194751243379102 56.557402480241024,6.19129370831601 56.54821591435217,6.188444364147898 56.538822818368885,6.186215448453396 56.52926359306454,6.184616516602176 56.51957897448855,6.183654441754783 56.50981053688071,6.183333247224795 56.50000002211891,6.183654441754783 56.490189507357115,6.184616516602176 56.4804210697493,6.186215448453396 56.47073645117331,6.188444364147898 56.46117722586894,6.19129370831601 56.45178412988565,6.194751243379102 56.44259756399683,6.198802384825626 56.433656755509304,6.203429363020831 56.42500009353972,6.2086127319493585 56.41666445846204,6.214330195748715 56.40868572482191,6.220557279261612 56.4010980907843,6.227267160397872 56.39393391049552,6.234431340686655 56.38722402935926,6.24201897472426 56.38099694584636,6.24999770836439 56.375279482047006,6.258333343442075 56.37009611311848,6.266990005411657 56.36546913492327,6.27593081389918 56.36141799347675,6.285117379788005 56.35796045841366,6.29451047577129 56.355111114245545,6.304069701075662 56.35288219855104,6.31375431965165 56.351283266699795,6.323522757259468 56.35032119185243,6.333333272021264 56.34999999732244,6.34314378678306 56.35032119185243,6.352912224390906 56.351283266699795,6.362596842966894 56.35288219855104,6.372156068271238 56.355111114245545,6.381549164254523 56.35796045841366,6.390735730143376 56.36141799347675,6.399676538630871 56.36546913492327,6.408333368238544 56.37009611311848,6.416668835678138 56.375279482047006,6.424647569318296 56.38099694584636,6.432235203355873 56.38722402935926,6.439399383644684 56.39393391049552,6.446109264780915 56.4010980907843,6.452336348293812 56.40868572482191,6.458053812093169 56.41666445846204,6.463237181021697 56.42500009353972,6.46786415921693 56.433656755509304,6.471915300663454 56.44259756399683,6.475372835726546 56.45178412988565,6.478222179894658 56.46117722586894,6.480451095589132 56.47073645117331,6.48205002744038 56.4804210697493,6.483012102287745 56.490189507357115,6.483333296817733 56.50000002211891))") \ group by sourceid choose_fis_query_2 = select sourceid \ from median.meshpoint, \ median.mesh \ where median.meshpoint.meshid = median.mesh.meshid and \ INTERSECTS(SHAPE,"POLYGON((6.483333296817733 56.50000002211891,6.483012102287745 56.50981053688071,6.48205002744038 56.51957897448855,6.480451095589132 56.52926359306454,6.478222179894658 56.538822818368885,6.475372835726546 56.54821591435217,6.471915300663454 56.557402480241024,6.46786415921693 56.56634328872852,6.463237181021697 56.57500011833619,6.458053812093169 56.583335585775785,6.452336348293812 56.591314319415915,6.446109264780915 56.59890195345352,6.439399383644684 56.60606613374233,6.432235203355873 56.61277601487856,6.424647569318296 56.61900309839146,6.416668835678138 56.62472056219082,6.408333368238544 56.629903931119344,6.399676538630871 56.63453090931458,6.390735730143376 56.6385820507611,6.381549164254523 56.64203958582419,6.372156068271238 56.644888929992305,6.362596842966894 56.64711784568678,6.352912224390906 56.64871677753803,6.34314378678306 56.64967885238539,6.333333272021264 56.65000004691538,6.323522757259468 56.64967885238539,6.31375431965165 56.64871677753803,6.304069701075662 56.64711784568678,6.29451047577129 56.644888929992305,6.285117379788005 56.64203958582419,6.27593081389918 56.6385820507611,6.266990005411657 56.63453090931458,6.258333343442075 56.629903931119344,6.24999770836439 56.62472056219082,6.24201897472426 56.61900309839146,6.234431340686655 56.61277601487856,6.227267160397872 56.60606613374233,6.220557279261612 56.59890195345352,6.214330195748715 56.591314319415915,6.2086127319493585 56.583335585775785,6.203429363020831 56.57500011833619,6.198802384825626 56.56634328872852,6.194751243379102 56.557402480241024,6.19129370831601 56.54821591435217,6.188444364147898 56.538822818368885,6.186215448453396 56.52926359306454,6.184616516602176 56.51957897448855,6.183654441754783 56.50981053688071,6.183333247224795 56.50000002211891,6.183654441754783 56.490189507357115,6.184616516602176 56.4804210697493,6.186215448453396 56.47073645117331,6.188444364147898 56.46117722586894,6.19129370831601 56.45178412988565,6.194751243379102 56.44259756399683,6.198802384825626 56.433656755509304,6.203429363020831 56.42500009353972,6.2086127319493585 56.41666445846204,6.214330195748715 56.40868572482191,6.220557279261612 56.4010980907843,6.227267160397872 56.39393391049552,6.234431340686655 56.38722402935926,6.24201897472426 56.38099694584636,6.24999770836439 56.375279482047006,6.258333343442075 56.37009611311848,6.266990005411657 56.36546913492327,6.27593081389918 56.36141799347675,6.285117379788005 56.35796045841366,6.29451047577129 56.355111114245545,6.304069701075662 56.35288219855104,6.31375431965165 56.351283266699795,6.323522757259468 56.35032119185243,6.333333272021264 56.34999999732244,6.34314378678306 56.35032119185243,6.352912224390906 56.351283266699795,6.362596842966894 56.35288219855104,6.372156068271238 56.355111114245545,6.381549164254523 56.35796045841366,6.390735730143376 56.36141799347675,6.399676538630871 56.36546913492327,6.408333368238544 56.37009611311848,6.416668835678138 56.375279482047006,6.424647569318296 56.38099694584636,6.432235203355873 56.38722402935926,6.439399383644684 56.39393391049552,6.446109264780915 56.4010980907843,6.452336348293812 56.40868572482191,6.458053812093169 56.41666445846204,6.463237181021697 56.42500009353972,6.46786415921693 56.433656755509304,6.471915300663454 56.44259756399683,6.475372835726546 56.45178412988565,6.478222179894658 56.46117722586894,6.480451095589132 56.47073645117331,6.48205002744038 56.4804210697493,6.483012102287745 56.490189507357115,6.483333296817733 56.50000002211891))") choose_fis_query_3 = select sourceid \ from median.meshpoint, \ median.mesh \ where median.meshpoint.meshid = median.mesh.meshid \ group by sourceid spatial_query_with_innerselect = SELECT M.OBJECTID KEY, \ M.NAME VALUE \ FROM MEDIAN.MESHPOINT, \ MEDIAN.MESH M \ WHERE M.SOURCEID IN (2) AND \ M.MESHID = MEDIAN.MESHPOINT.MESHID AND \ INTERSECTS(SHAPE, \ (SELECT st_astext(SHAPE) FROM MEDIAN.FEATUREAREA WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND FEATURECODE = 41 )\ ) \ order by M.NAME query_odv_data_timeseriespoint = SELECT SI.NAME CRUISE, \ MEDIAN.TIMESERIESPOINT.NAME STATION, \ '*' TYPE, \ ST_ASTEXT(SHAPE), \ 0 BOTDEPTH, \ (M.ZLOCATION *-1) DEPTH, \ TSV.TIMEVALUE XORDINATE, \ TSV.DATAVALUE YORDINATE, \ TS.PARAMETERID GROUP1, \ TSV.MEASUREMENTID GROUP2, \ TSV.TIMESERIESID GROUP3 \ 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 ( 2 ) AND \ TSV.MEASUREMENTID IN ( 500061 ) AND \ TSV.TIMEVALUE >= to_date('2002.09.27 11:00:00', 'YYYY.MM.DD HH24:MI:SS') AND \ TSV.TIMEVALUE <= to_date('2008.12.09 03:00:00', 'YYYY.MM.DD HH24:MI:SS') \ ORDER BY TSV.MEASUREMENTID , \ TSV.TIMESERIESID , \ TS.PARAMETERID , \ TSV.TIMEVALUE query_odv_data_vp_timeseriespoint = 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 = 100011 AND \ TS.PARAMETERID IN ( 2 , 31 ) AND \ TSV.TIMEVALUE IN (to_date('2008.12.09 00:00:00', 'YYYY.MM.DD HH24:MI:SS')) AND \ M.ZLOCATION >= -40 AND \ M.ZLOCATION <= -7 \ ORDER BY TSV.MEASUREMENTID , \ TSV.TIMESERIESID , \ TS.PARAMETERID , \ TSV.TIMEVALUE query_odv_data_mesh = 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 = 3 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 in ( 2003771 ) AND \ msv.PARAMETERID in ( 2 , 1 ) AND \ MEDIAN.MESHPOINT.FEATUREID in ( 2003771 , 2012198 ) AND \ msv.TIMEVALUE >= to_date('2009.10.30 01:00:00', 'YYYY.MM.DD HH24:MI:SS') AND \ msv.TIMEVALUE <= to_date('2009.11.09 00:00:00', 'YYYY.MM.DD HH24:MI:SS') \ order by MEDIAN.MESHPOINT.FEATUREID, \ msv.FEATUREID, \ msv.PARAMETERID, \ msv.TIMEVALUE query_odv_data_vp_mesh = 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 (1 , 2) AND \ msv.TIMEVALUE in (to_date('2009.11.09 00:00:00', 'YYYY.MM.DD HH24:MI:SS')) AND \ m.OBJECTID = 3 AND \ MEDIAN.MESHPOINT.FEATUREID in ( select FEATUREID \ from MEDIAN.MESHPOINT mp, \ MEDIAN.MESH m \ where m.OBJECTID = 3 AND \ mp.MESHID = m.MESHID AND \ IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = 2003771) and \ JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = 2003771)) AND \ MEDIAN.MESHPOINT.KPOSITION <= 4 AND \ MEDIAN.MESHPOINT.KPOSITION >= 1 \ order by msv.PARAMETERID, \ msv.TIMEVALUE, \ ml.UPPERZLOCATION test_vehicle_orig = 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 = 34 \ ORDER BY V.NAME test_vehicle_area_ip = SELECT V.VEHICLEID KEY, \ V.NAME VALUE \ FROM MEDIAN.INSTANTANEOUSPOINT, \ MEDIAN.TRACK T , \ MEDIAN.VEHICLE V, \ MEDIAN.CRUISE C, \ MEDIAN.SURVEYINFO S, \ MEDIAN.MEASUREMENT M \ 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.SOURCEID = 34 AND \ INTERSECTS(SHAPE, (SELECT st_astext(SHAPE) FROM MEDIAN.FEATUREAREA WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND FEATURECODE = 24 )) \ ORDER BY V.NAME test_vehicle_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 = 34 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 = 24 )) \ ORDER BY V.NAME test_track_orig = SELECT \ ST_ASTEXT(SHAPE), \ TRACKID KEY \ FROM MEDIAN.TRACK \ WHERE CRUISEID = 2 \ ORDER BY STARTDATE, \ ENDDATE, \ NAME test_track_area = SELECT ST_ASTEXT(SHAPE), 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 = 2 AND msv.TIMEVALUE = to_date('2009.11.27 00:00:00', 'YYYY.MM.DD HH24:MI:SS') , to_date('2009.11.26 00:00:00', 'YYYY.MM.DD HH24:MI:SS') AND m.OBJECTID = 3 AND MEDIAN.MESHPOINT.FEATUREID in ( select FEATUREID from MEDIAN.MESHPOINT mp, MEDIAN.MESH m where m.OBJECTID = 3 AND mp.MESHID = m.MESHID AND IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = 2003771)) order by msv.TIMEVALUE, msv.PARAMETERID, MEDIAN.MESHPOINT.JPOSITION, MEDIAN.MESHPOINT.IPOSITION, MEDIAN.MESHPOINT.KPOSITION