Mercurial > dive4elements > gnv-client

annotate geo-backend/src/test/ressources/QueryExecutorTestCase.properties @ 377:6a585e87c18b

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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
rev   line source
133
56655046194f Fix Bug setting the Filtervalues into the Statement.
Tim Englich <tim.englich@intevation.de>
parents: 132
diff changeset
1 mesh=Select MESHID, NAME from MEDIAN.MESH
139
9f2eaefe9dd4 Some Log Messages Added.
Tim Englich <tim.englich@intevation.de>
parents: 133
diff changeset
2 mesh_id=Select MESHID, NAME from MEDIAN.MESH where MESHID = ?
9f2eaefe9dd4 Some Log Messages Added.
Tim Englich <tim.englich@intevation.de>
parents: 133
diff changeset
3
9f2eaefe9dd4 Some Log Messages Added.
Tim Englich <tim.englich@intevation.de>
parents: 133
diff changeset
4
9f2eaefe9dd4 Some Log Messages Added.
Tim Englich <tim.englich@intevation.de>
parents: 133
diff changeset
5
9f2eaefe9dd4 Some Log Messages Added.
Tim Englich <tim.englich@intevation.de>
parents: 133
diff changeset
6 # Timeseries
9f2eaefe9dd4 Some Log Messages Added.
Tim Englich <tim.englich@intevation.de>
parents: 133
diff changeset
7 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
9f2eaefe9dd4 Some Log Messages Added.
Tim Englich <tim.englich@intevation.de>
parents: 133
diff changeset
8 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
9f2eaefe9dd4 Some Log Messages Added.
Tim Englich <tim.englich@intevation.de>
parents: 133
diff changeset
9 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 ( ? )
9f2eaefe9dd4 Some Log Messages Added.
Tim Englich <tim.englich@intevation.de>
parents: 133
diff changeset
10
9f2eaefe9dd4 Some Log Messages Added.
Tim Englich <tim.englich@intevation.de>
parents: 133
diff changeset
11
263
031ef9649cd1 Added SFS-Support to ArcSDE-Query-Part
Tim Englich <tim.englich@intevation.de>
parents: 139
diff changeset
12
031ef9649cd1 Added SFS-Support to ArcSDE-Query-Part
Tim Englich <tim.englich@intevation.de>
parents: 139
diff changeset
13 spatial_query=SELECT FEATUREID, \
031ef9649cd1 Added SFS-Support to ArcSDE-Query-Part
Tim Englich <tim.englich@intevation.de>
parents: 139
diff changeset
14 ST_ASTEXT(SHAPE) \
031ef9649cd1 Added SFS-Support to ArcSDE-Query-Part
Tim Englich <tim.englich@intevation.de>
parents: 139
diff changeset
15 FROM MEDIAN.MESHPOINT \
031ef9649cd1 Added SFS-Support to ArcSDE-Query-Part
Tim Englich <tim.englich@intevation.de>
parents: 139
diff changeset
16 WHERE MESHID in \
031ef9649cd1 Added SFS-Support to ArcSDE-Query-Part
Tim Englich <tim.englich@intevation.de>
parents: 139
diff changeset
17 (SELECT DISTINCT m.MESHID \
031ef9649cd1 Added SFS-Support to ArcSDE-Query-Part
Tim Englich <tim.englich@intevation.de>
parents: 139
diff changeset
18 FROM MEDIAN.MESH m \
031ef9649cd1 Added SFS-Support to ArcSDE-Query-Part
Tim Englich <tim.englich@intevation.de>
parents: 139
diff changeset
19 WHERE M.OBJECTID = M.OBJECTID) AND \
031ef9649cd1 Added SFS-Support to ArcSDE-Query-Part
Tim Englich <tim.englich@intevation.de>
parents: 139
diff changeset
20 KPOSITION = 1 AND \
266
1530890b28c9 Added SFS-Support to ArcSDE-Query-Part without any Spatial restriction in the where clause
Tim Englich <tim.englich@intevation.de>
parents: 263
diff changeset
21 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))")
1530890b28c9 Added SFS-Support to ArcSDE-Query-Part without any Spatial restriction in the where clause
Tim Englich <tim.englich@intevation.de>
parents: 263
diff changeset
22
267
000e00592ba5 Added InnerJoin, Order By and Group By Support to Spatial-Queries
Tim Englich <tim.englich@intevation.de>
parents: 266
diff changeset
23 spatial_query_without_intersects = SELECT ST_ASTEXT(SHAPE), \
000e00592ba5 Added InnerJoin, Order By and Group By Support to Spatial-Queries
Tim Englich <tim.englich@intevation.de>
parents: 266
diff changeset
24 MSV.DATAVALUE YORDINATE , \
000e00592ba5 Added InnerJoin, Order By and Group By Support to Spatial-Queries
Tim Englich <tim.englich@intevation.de>
parents: 266
diff changeset
25 MSV.PARAMETERID GROUP1 , \
000e00592ba5 Added InnerJoin, Order By and Group By Support to Spatial-Queries
Tim Englich <tim.englich@intevation.de>
parents: 266
diff changeset
26 M.ZLOCATION GROUP2, \
000e00592ba5 Added InnerJoin, Order By and Group By Support to Spatial-Queries
Tim Englich <tim.englich@intevation.de>
parents: 266
diff changeset
27 1 GROUP3 \
000e00592ba5 Added InnerJoin, Order By and Group By Support to Spatial-Queries
Tim Englich <tim.englich@intevation.de>
parents: 266
diff changeset
28 FROM MEDIAN.INSTANTANEOUSPOINT, \
000e00592ba5 Added InnerJoin, Order By and Group By Support to Spatial-Queries
Tim Englich <tim.englich@intevation.de>
parents: 266
diff changeset
29 MEDIAN.MEASUREMENT M, \
000e00592ba5 Added InnerJoin, Order By and Group By Support to Spatial-Queries
Tim Englich <tim.englich@intevation.de>
parents: 266
diff changeset
30 MEDIAN.MEASUREDSCALARVALUE MSV \
000e00592ba5 Added InnerJoin, Order By and Group By Support to Spatial-Queries
Tim Englich <tim.englich@intevation.de>
parents: 266
diff changeset
31 WHERE MEDIAN.INSTANTANEOUSPOINT.FEATUREID =m.FEATUREID AND \
000e00592ba5 Added InnerJoin, Order By and Group By Support to Spatial-Queries
Tim Englich <tim.englich@intevation.de>
parents: 266
diff changeset
32 m.MEASUREMENTID = MSV.MEASUREMENTID AND \
000e00592ba5 Added InnerJoin, Order By and Group By Support to Spatial-Queries
Tim Englich <tim.englich@intevation.de>
parents: 266
diff changeset
33 MEDIAN.INSTANTANEOUSPOINT.SURVEYID = 1895 AND \
000e00592ba5 Added InnerJoin, Order By and Group By Support to Spatial-Queries
Tim Englich <tim.englich@intevation.de>
parents: 266
diff changeset
34 m.ZLOCATION IN (-16.843 , -15.852) AND \
000e00592ba5 Added InnerJoin, Order By and Group By Support to Spatial-Queries
Tim Englich <tim.englich@intevation.de>
parents: 266
diff changeset
35 MSV.PARAMETERID in (2 , 1) \
000e00592ba5 Added InnerJoin, Order By and Group By Support to Spatial-Queries
Tim Englich <tim.englich@intevation.de>
parents: 266
diff changeset
36 ORDER BY MSV.PARAMETERID, \
269
db8920c5346d Added further Queries for Testing Spatial-Query-Support
Tim Englich <tim.englich@intevation.de>
parents: 267
diff changeset
37 m.ZLOCATION
db8920c5346d Added further Queries for Testing Spatial-Query-Support
Tim Englich <tim.englich@intevation.de>
parents: 267
diff changeset
38
db8920c5346d Added further Queries for Testing Spatial-Query-Support
Tim Englich <tim.englich@intevation.de>
parents: 267
diff changeset
39 spatial_query_without_intersects2 = SELECT ST_ASTEXT(SHAPE), \
db8920c5346d Added further Queries for Testing Spatial-Query-Support
Tim Englich <tim.englich@intevation.de>
parents: 267
diff changeset
40 msv.DATAVALUE YORDINATE, \
db8920c5346d Added further Queries for Testing Spatial-Query-Support
Tim Englich <tim.englich@intevation.de>
parents: 267
diff changeset
41 msv.PARAMETERID GROUP1, \
db8920c5346d Added further Queries for Testing Spatial-Query-Support
Tim Englich <tim.englich@intevation.de>
parents: 267
diff changeset
42 msv.TIMEVALUE GROUP2, \
db8920c5346d Added further Queries for Testing Spatial-Query-Support
Tim Englich <tim.englich@intevation.de>
parents: 267
diff changeset
43 MEDIAN.MESHPOINT.KPOSITION GROUP3, \
db8920c5346d Added further Queries for Testing Spatial-Query-Support
Tim Englich <tim.englich@intevation.de>
parents: 267
diff changeset
44 MEDIAN.MESHPOINT.JPOSITION , \
db8920c5346d Added further Queries for Testing Spatial-Query-Support
Tim Englich <tim.englich@intevation.de>
parents: 267
diff changeset
45 MEDIAN.MESHPOINT.IPOSITION \
db8920c5346d Added further Queries for Testing Spatial-Query-Support
Tim Englich <tim.englich@intevation.de>
parents: 267
diff changeset
46 from MEDIAN.MESHLAYER ml, \
db8920c5346d Added further Queries for Testing Spatial-Query-Support
Tim Englich <tim.englich@intevation.de>
parents: 267
diff changeset
47 MEDIAN.MESHPOINT , \
db8920c5346d Added further Queries for Testing Spatial-Query-Support
Tim Englich <tim.englich@intevation.de>
parents: 267
diff changeset
48 MEDIAN.MESH m, \
db8920c5346d Added further Queries for Testing Spatial-Query-Support
Tim Englich <tim.englich@intevation.de>
parents: 267
diff changeset
49 MEDIAN.MESHSCALARVALUE msv \
db8920c5346d Added further Queries for Testing Spatial-Query-Support
Tim Englich <tim.englich@intevation.de>
parents: 267
diff changeset
50 where msv.FEATUREID = MEDIAN.MESHPOINT.FEATUREID AND \
db8920c5346d Added further Queries for Testing Spatial-Query-Support
Tim Englich <tim.englich@intevation.de>
parents: 267
diff changeset
51 ml.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \
db8920c5346d Added further Queries for Testing Spatial-Query-Support
Tim Englich <tim.englich@intevation.de>
parents: 267
diff changeset
52 ml.MESHID = MEDIAN.MESHPOINT.MESHID and \
db8920c5346d Added further Queries for Testing Spatial-Query-Support
Tim Englich <tim.englich@intevation.de>
parents: 267
diff changeset
53 m.MESHID = MEDIAN.MESHPOINT.MESHID AND \
db8920c5346d Added further Queries for Testing Spatial-Query-Support
Tim Englich <tim.englich@intevation.de>
parents: 267
diff changeset
54 m.PARTIDMIN <= msv.PARTID AND \
db8920c5346d Added further Queries for Testing Spatial-Query-Support
Tim Englich <tim.englich@intevation.de>
parents: 267
diff changeset
55 m.PARTIDMAX >= msv.PARTID AND \
db8920c5346d Added further Queries for Testing Spatial-Query-Support
Tim Englich <tim.englich@intevation.de>
parents: 267
diff changeset
56 msv.PARAMETERID in (2,1) AND \
db8920c5346d Added further Queries for Testing Spatial-Query-Support
Tim Englich <tim.englich@intevation.de>
parents: 267
diff changeset
57 msv.TIMEVALUE in (to_date('2009.10.15 23:00:00', 'YYYY.MM.DD HH24:MI:SS')) AND \
db8920c5346d Added further Queries for Testing Spatial-Query-Support
Tim Englich <tim.englich@intevation.de>
parents: 267
diff changeset
58 m.OBJECTID = 3 AND \
db8920c5346d Added further Queries for Testing Spatial-Query-Support
Tim Englich <tim.englich@intevation.de>
parents: 267
diff changeset
59 MEDIAN.MESHPOINT.FEATUREID in \
db8920c5346d Added further Queries for Testing Spatial-Query-Support
Tim Englich <tim.englich@intevation.de>
parents: 267
diff changeset
60 ( select FEATUREID \
db8920c5346d Added further Queries for Testing Spatial-Query-Support
Tim Englich <tim.englich@intevation.de>
parents: 267
diff changeset
61 from MEDIAN.MESHPOINT mp, \
db8920c5346d Added further Queries for Testing Spatial-Query-Support
Tim Englich <tim.englich@intevation.de>
parents: 267
diff changeset
62 MEDIAN.MESH m \
db8920c5346d Added further Queries for Testing Spatial-Query-Support
Tim Englich <tim.englich@intevation.de>
parents: 267
diff changeset
63 where m.OBJECTID = 3 AND \
db8920c5346d Added further Queries for Testing Spatial-Query-Support
Tim Englich <tim.englich@intevation.de>
parents: 267
diff changeset
64 mp.MESHID = m.MESHID AND \
db8920c5346d Added further Queries for Testing Spatial-Query-Support
Tim Englich <tim.englich@intevation.de>
parents: 267
diff changeset
65 KPOSITION in ( 1 ) and \
db8920c5346d Added further Queries for Testing Spatial-Query-Support
Tim Englich <tim.englich@intevation.de>
parents: 267
diff changeset
66 JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = 2003771)) \
db8920c5346d Added further Queries for Testing Spatial-Query-Support
Tim Englich <tim.englich@intevation.de>
parents: 267
diff changeset
67 order by MEDIAN.MESHPOINT.KPOSITION, \
db8920c5346d Added further Queries for Testing Spatial-Query-Support
Tim Englich <tim.englich@intevation.de>
parents: 267
diff changeset
68 msv.TIMEVALUE, \
db8920c5346d Added further Queries for Testing Spatial-Query-Support
Tim Englich <tim.englich@intevation.de>
parents: 267
diff changeset
69 msv.PARAMETERID, \
db8920c5346d Added further Queries for Testing Spatial-Query-Support
Tim Englich <tim.englich@intevation.de>
parents: 267
diff changeset
70 MEDIAN.MESHPOINT.JPOSITION , \
274
ff1b7967e6b9 General CodecCleanup:
Tim Englich <tim.englich@intevation.de>
parents: 269
diff changeset
71 MEDIAN.MESHPOINT.IPOSITION
ff1b7967e6b9 General CodecCleanup:
Tim Englich <tim.englich@intevation.de>
parents: 269
diff changeset
72
ff1b7967e6b9 General CodecCleanup:
Tim Englich <tim.englich@intevation.de>
parents: 269
diff changeset
73
ff1b7967e6b9 General CodecCleanup:
Tim Englich <tim.englich@intevation.de>
parents: 269
diff changeset
74 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
ff1b7967e6b9 General CodecCleanup:
Tim Englich <tim.englich@intevation.de>
parents: 269
diff changeset
75 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
ff1b7967e6b9 General CodecCleanup:
Tim Englich <tim.englich@intevation.de>
parents: 269
diff changeset
76 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
275
9063c5fcebf7 Added the possibility to use Innerselects in SpatialQueries
Tim Englich <tim.englich@intevation.de>
parents: 274
diff changeset
77 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
9063c5fcebf7 Added the possibility to use Innerselects in SpatialQueries
Tim Englich <tim.englich@intevation.de>
parents: 274
diff changeset
78
9063c5fcebf7 Added the possibility to use Innerselects in SpatialQueries
Tim Englich <tim.englich@intevation.de>
parents: 274
diff changeset
79
9063c5fcebf7 Added the possibility to use Innerselects in SpatialQueries
Tim Englich <tim.englich@intevation.de>
parents: 274
diff changeset
80
377
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
81 choose_fis_query = select sourceid \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
82 from median.meshpoint, \
275
9063c5fcebf7 Added the possibility to use Innerselects in SpatialQueries
Tim Englich <tim.englich@intevation.de>
parents: 274
diff changeset
83 median.mesh \
9063c5fcebf7 Added the possibility to use Innerselects in SpatialQueries
Tim Englich <tim.englich@intevation.de>
parents: 274
diff changeset
84 where median.meshpoint.meshid = median.mesh.meshid and \
9063c5fcebf7 Added the possibility to use Innerselects in SpatialQueries
Tim Englich <tim.englich@intevation.de>
parents: 274
diff changeset
85 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))") \
9063c5fcebf7 Added the possibility to use Innerselects in SpatialQueries
Tim Englich <tim.englich@intevation.de>
parents: 274
diff changeset
86 group by sourceid
9063c5fcebf7 Added the possibility to use Innerselects in SpatialQueries
Tim Englich <tim.englich@intevation.de>
parents: 274
diff changeset
87
9063c5fcebf7 Added the possibility to use Innerselects in SpatialQueries
Tim Englich <tim.englich@intevation.de>
parents: 274
diff changeset
88
377
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
89 choose_fis_query_2 = select sourceid \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
90 from median.meshpoint, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
91 median.mesh \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
92 where median.meshpoint.meshid = median.mesh.meshid and \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
93 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))")
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
94
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
95 choose_fis_query_3 = select sourceid \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
96 from median.meshpoint, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
97 median.mesh \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
98 where median.meshpoint.meshid = median.mesh.meshid \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
99 group by sourceid
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
100
275
9063c5fcebf7 Added the possibility to use Innerselects in SpatialQueries
Tim Englich <tim.englich@intevation.de>
parents: 274
diff changeset
101 spatial_query_with_innerselect = SELECT M.OBJECTID KEY, \
9063c5fcebf7 Added the possibility to use Innerselects in SpatialQueries
Tim Englich <tim.englich@intevation.de>
parents: 274
diff changeset
102 M.NAME VALUE \
9063c5fcebf7 Added the possibility to use Innerselects in SpatialQueries
Tim Englich <tim.englich@intevation.de>
parents: 274
diff changeset
103 FROM MEDIAN.MESHPOINT, \
9063c5fcebf7 Added the possibility to use Innerselects in SpatialQueries
Tim Englich <tim.englich@intevation.de>
parents: 274
diff changeset
104 MEDIAN.MESH M \
9063c5fcebf7 Added the possibility to use Innerselects in SpatialQueries
Tim Englich <tim.englich@intevation.de>
parents: 274
diff changeset
105 WHERE M.SOURCEID IN (2) AND \
9063c5fcebf7 Added the possibility to use Innerselects in SpatialQueries
Tim Englich <tim.englich@intevation.de>
parents: 274
diff changeset
106 M.MESHID = MEDIAN.MESHPOINT.MESHID AND \
9063c5fcebf7 Added the possibility to use Innerselects in SpatialQueries
Tim Englich <tim.englich@intevation.de>
parents: 274
diff changeset
107 INTERSECTS(SHAPE, \
9063c5fcebf7 Added the possibility to use Innerselects in SpatialQueries
Tim Englich <tim.englich@intevation.de>
parents: 274
diff changeset
108 (SELECT st_astext(SHAPE) FROM MEDIAN.FEATUREAREA WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND FEATURECODE = 41 )\
9063c5fcebf7 Added the possibility to use Innerselects in SpatialQueries
Tim Englich <tim.englich@intevation.de>
parents: 274
diff changeset
109 ) \
9063c5fcebf7 Added the possibility to use Innerselects in SpatialQueries
Tim Englich <tim.englich@intevation.de>
parents: 274
diff changeset
110 order by M.NAME
377
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
111
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
112
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
113 query_odv_data_timeseriespoint = SELECT SI.NAME CRUISE, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
114 MEDIAN.TIMESERIESPOINT.NAME STATION, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
115 '*' TYPE, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
116 ST_ASTEXT(SHAPE), \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
117 0 BOTDEPTH, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
118 (M.ZLOCATION *-1) DEPTH, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
119 TSV.TIMEVALUE XORDINATE, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
120 TSV.DATAVALUE YORDINATE, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
121 TS.PARAMETERID GROUP1, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
122 TSV.MEASUREMENTID GROUP2, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
123 TSV.TIMESERIESID GROUP3 \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
124 FROM MEDIAN.TIMESERIESPOINT , \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
125 MEDIAN.TIMESERIESVALUE TSV, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
126 MEDIAN.TIMESERIES TS, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
127 MEDIAN.MEASUREMENT M, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
128 MEDIAN.SOURCEINFO SI \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
129 WHERE SI.SOURCEID = M.SOURCEID AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
130 M.FEATUREID = MEDIAN.TIMESERIESPOINT.FEATUREID AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
131 M.MEASUREMENTID = TSV.MEASUREMENTID AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
132 TSV.TIMESERIESID = TS.TIMESERIESID AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
133 TS.PARAMETERID IN ( 2 ) AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
134 TSV.MEASUREMENTID IN ( 500061 ) AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
135 TSV.TIMEVALUE >= to_date('2002.09.27 11:00:00', 'YYYY.MM.DD HH24:MI:SS') AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
136 TSV.TIMEVALUE <= to_date('2008.12.09 03:00:00', 'YYYY.MM.DD HH24:MI:SS') \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
137 ORDER BY TSV.MEASUREMENTID , \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
138 TSV.TIMESERIESID , \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
139 TS.PARAMETERID , \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
140 TSV.TIMEVALUE
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
141 query_odv_data_vp_timeseriespoint = SELECT SI.NAME CRUISE, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
142 MEDIAN.TIMESERIESPOINT.NAME STATION, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
143 '*' TYPE, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
144 ST_ASTEXT(SHAPE), \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
145 0 BOTDEPTH, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
146 (M.ZLOCATION *-1) DEPTH, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
147 TSV.TIMEVALUE, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
148 TSV.DATAVALUE, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
149 TS.PARAMETERID PARAMETER, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
150 TSV.MEASUREMENTID, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
151 TSV.TIMESERIESID \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
152 FROM MEDIAN.TIMESERIESPOINT , \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
153 MEDIAN.TIMESERIESVALUE TSV, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
154 MEDIAN.TIMESERIES TS, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
155 MEDIAN.MEASUREMENT M, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
156 MEDIAN.SOURCEINFO SI \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
157 WHERE SI.SOURCEID = M.SOURCEID AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
158 M.FEATUREID = MEDIAN.TIMESERIESPOINT.FEATUREID AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
159 M.MEASUREMENTID = TSV.MEASUREMENTID AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
160 TSV.TIMESERIESID = TS.TIMESERIESID AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
161 M.FEATUREID = 100011 AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
162 TS.PARAMETERID IN ( 2 , 31 ) AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
163 TSV.TIMEVALUE IN (to_date('2008.12.09 00:00:00', 'YYYY.MM.DD HH24:MI:SS')) AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
164 M.ZLOCATION >= -40 AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
165 M.ZLOCATION <= -7 \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
166 ORDER BY TSV.MEASUREMENTID , \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
167 TSV.TIMESERIESID , \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
168 TS.PARAMETERID , \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
169 TSV.TIMEVALUE
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
170
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
171
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
172 query_odv_data_mesh = select /*+ parallel(timevalue,10) */ \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
173 SI.NAME CRUISE, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
174 m.MESHID || '-' || MEDIAN.MESHPOINT.IPOSITION || '-' || MEDIAN.MESHPOINT.JPOSITION STATION, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
175 '*' TYPE, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
176 ST_ASTEXT(SHAPE), \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
177 0 BOTDEPTH, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
178 (((ML.UPPERZLOCATION + ML.LOWERZLOCATION) / 2)*-1) DEPTH, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
179 msv.TIMEVALUE, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
180 msv.DATAVALUE , \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
181 msv.PARAMETERID PARAMETER \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
182 from MEDIAN.MESHSCALARVALUE msv , \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
183 MEDIAN.MESHPOINT, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
184 MEDIAN.MESH m, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
185 MEDIAN.SOURCEINFO SI, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
186 MEDIAN.MESHLAYER ML \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
187 where (m.OBJECTID = 3 AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
188 msv.PARTID >= m.PARTIDMIN AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
189 msv.PARTID <= m.PARTIDMAX ) AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
190 m.SOURCEID = SI.SOURCEID AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
191 ML.MESHID = MEDIAN.MESHPOINT.MESHID AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
192 ML.KPOSITION = MEDIAN.MESHPOINT.KPOSITION AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
193 msv.FEATUREID in ( 2003771 ) AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
194 msv.PARAMETERID in ( 2 , 1 ) AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
195 MEDIAN.MESHPOINT.FEATUREID in ( 2003771 , 2012198 ) AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
196 msv.TIMEVALUE >= to_date('2009.10.30 01:00:00', 'YYYY.MM.DD HH24:MI:SS') AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
197 msv.TIMEVALUE <= to_date('2009.11.09 00:00:00', 'YYYY.MM.DD HH24:MI:SS') \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
198 order by MEDIAN.MESHPOINT.FEATUREID, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
199 msv.FEATUREID, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
200 msv.PARAMETERID, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
201 msv.TIMEVALUE
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
202
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
203
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
204 query_odv_data_vp_mesh = select \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
205 SI.NAME CRUISE, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
206 m.MESHID || '-' || MEDIAN.MESHPOINT.IPOSITION || '-' || MEDIAN.MESHPOINT.JPOSITION STATION, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
207 '*' TYPE, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
208 ST_ASTEXT(SHAPE), \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
209 0 BOTDEPTH, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
210 (((ML.UPPERZLOCATION + ML.LOWERZLOCATION) / 2)*-1) DEPTH, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
211 msv.DATAVALUE , \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
212 msv.PARAMETERID PARAMETER, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
213 msv.TIMEVALUE , \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
214 from MEDIAN.MESHLAYER ml, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
215 MEDIAN.MESHPOINT, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
216 MEDIAN.MESH m, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
217 MEDIAN.MESHSCALARVALUE msv, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
218 MEDIAN.SOURCEINFO SI, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
219 where m.SOURCEID = SI.SOURCEID AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
220 msv.FEATUREID = MEDIAN.MESHPOINT. FEATUREID AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
221 ml.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
222 ml.MESHID = MEDIAN.MESHPOINT.MESHID and \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
223 m.MESHID = MEDIAN.MESHPOINT.MESHID AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
224 m.PARTIDMIN <= msv.PARTID AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
225 m.PARTIDMAX >= msv.PARTID AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
226 msv.PARAMETERID in (1 , 2) AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
227 msv.TIMEVALUE in (to_date('2009.11.09 00:00:00', 'YYYY.MM.DD HH24:MI:SS')) AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
228 m.OBJECTID = 3 AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
229 MEDIAN.MESHPOINT.FEATUREID in ( select FEATUREID \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
230 from MEDIAN.MESHPOINT mp, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
231 MEDIAN.MESH m \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
232 where m.OBJECTID = 3 AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
233 mp.MESHID = m.MESHID AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
234 IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = 2003771) and \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
235 JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = 2003771)) AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
236 MEDIAN.MESHPOINT.KPOSITION <= 4 AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
237 MEDIAN.MESHPOINT.KPOSITION >= 1 \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
238 order by msv.PARAMETERID, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
239 msv.TIMEVALUE, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
240 ml.UPPERZLOCATION
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
241
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
242 test_vehicle_orig = SELECT DISTINCT V.VEHICLEID KEY, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
243 V.NAME VALUE \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
244 FROM MEDIAN.VEHICLE V, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
245 MEDIAN.CRUISE C, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
246 MEDIAN.TRACK T, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
247 MEDIAN.SURVEYINFO S, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
248 MEDIAN.INSTANTANEOUSPOINT I, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
249 MEDIAN.MEASUREMENT M \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
250 WHERE V.VEHICLEID = C.VEHICLEID AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
251 C.CRUISEID = T.CRUISEID AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
252 T.TRACKID = S.TRACKID AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
253 S.SURVEYID = I.SURVEYID AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
254 I.FEATUREID = M.FEATUREID AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
255 M.SOURCEID = 34 \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
256 ORDER BY V.NAME
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
257
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
258
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
259 test_vehicle_area_ip = SELECT V.VEHICLEID KEY, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
260 V.NAME VALUE \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
261 FROM MEDIAN.INSTANTANEOUSPOINT, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
262 MEDIAN.TRACK T , \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
263 MEDIAN.VEHICLE V, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
264 MEDIAN.CRUISE C, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
265 MEDIAN.SURVEYINFO S, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
266 MEDIAN.MEASUREMENT M \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
267 WHERE V.VEHICLEID = C.VEHICLEID AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
268 C.CRUISEID = T.CRUISEID AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
269 T.TRACKID = S.TRACKID AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
270 S.SURVEYID = MEDIAN.INSTANTANEOUSPOINT.SURVEYID AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
271 MEDIAN.INSTANTANEOUSPOINT.FEATUREID = M.FEATUREID AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
272 M.SOURCEID = 34 AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
273 INTERSECTS(SHAPE, (SELECT st_astext(SHAPE) FROM MEDIAN.FEATUREAREA WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND FEATURECODE = 24 )) \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
274 ORDER BY V.NAME
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
275
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
276 test_vehicle_area = SELECT V.VEHICLEID KEY, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
277 V.NAME VALUE \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
278 FROM MEDIAN.TRACK , \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
279 MEDIAN.VEHICLE V, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
280 MEDIAN.CRUISE C \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
281 WHERE V.VEHICLEID = C.VEHICLEID AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
282 C.CRUISEID = MEDIAN.TRACK.CRUISEID AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
283 MEDIAN.TRACK.TRACKID IN \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
284 (SELECT DISTINCT S.TRACKID \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
285 FROM MEDIAN.MEASUREMENT M ,\
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
286 MEDIAN.INSTANTANEOUSPOINT I, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
287 MEDIAN.SURVEYINFO S \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
288 WHERE M.SOURCEID = 34 AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
289 I.FEATUREID = M.FEATUREID AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
290 S.SURVEYID = I.SURVEYID)AND \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
291 INTERSECTS(SHAPE, (SELECT st_astext(SHAPE) FROM MEDIAN.FEATUREAREA WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND FEATURECODE = 24 )) \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
292 ORDER BY V.NAME
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
293 test_track_orig = SELECT \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
294 ST_ASTEXT(SHAPE), \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
295 TRACKID KEY \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
296 FROM MEDIAN.TRACK \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
297 WHERE CRUISEID = 2 \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
298 ORDER BY STARTDATE, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
299 ENDDATE, \
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
300 NAME
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
301
6a585e87c18b Added RegionFilter-Support to HorizontalProfile InstantaneousPoint
Tim Englich <tim.englich@intevation.de>
parents: 275
diff changeset
302 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
http://dive4elements.wald.intevation.org