Mercurial > dive4elements > gnv-client

comparison 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
comparison
equal deleted inserted replaced
279:3c38ef20d622 377:6a585e87c18b
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 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
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 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
78 78
79 79
80 80
81 choose_fis_query = select m.sourceid, \ 81 choose_fis_query = select sourceid \
82 from median.meshpoint ,\ 82 from median.meshpoint, \
83 median.mesh \ 83 median.mesh \
84 where median.meshpoint.meshid = median.mesh.meshid and \ 84 where median.meshpoint.meshid = median.mesh.meshid and \
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))") \ 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))") \
86 group by sourceid 86 group by sourceid
87 87
88
89 choose_fis_query_2 = select sourceid \
90 from median.meshpoint, \
91 median.mesh \
92 where median.meshpoint.meshid = median.mesh.meshid and \
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))")
94
95 choose_fis_query_3 = select sourceid \
96 from median.meshpoint, \
97 median.mesh \
98 where median.meshpoint.meshid = median.mesh.meshid \
99 group by sourceid
88 100
89 spatial_query_with_innerselect = SELECT M.OBJECTID KEY, \ 101 spatial_query_with_innerselect = SELECT M.OBJECTID KEY, \
90 M.NAME VALUE \ 102 M.NAME VALUE \
91 FROM MEDIAN.MESHPOINT, \ 103 FROM MEDIAN.MESHPOINT, \
92 MEDIAN.MESH M \ 104 MEDIAN.MESH M \
94 M.MESHID = MEDIAN.MESHPOINT.MESHID AND \ 106 M.MESHID = MEDIAN.MESHPOINT.MESHID AND \
95 INTERSECTS(SHAPE, \ 107 INTERSECTS(SHAPE, \
96 (SELECT st_astext(SHAPE) FROM MEDIAN.FEATUREAREA WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND FEATURECODE = 41 )\ 108 (SELECT st_astext(SHAPE) FROM MEDIAN.FEATUREAREA WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND FEATURECODE = 41 )\
97 ) \ 109 ) \
98 order by M.NAME 110 order by M.NAME
111
112
113 query_odv_data_timeseriespoint = SELECT SI.NAME CRUISE, \
114 MEDIAN.TIMESERIESPOINT.NAME STATION, \
115 '*' TYPE, \
116 ST_ASTEXT(SHAPE), \
117 0 BOTDEPTH, \
118 (M.ZLOCATION *-1) DEPTH, \
119 TSV.TIMEVALUE XORDINATE, \
120 TSV.DATAVALUE YORDINATE, \
121 TS.PARAMETERID GROUP1, \
122 TSV.MEASUREMENTID GROUP2, \
123 TSV.TIMESERIESID GROUP3 \
124 FROM MEDIAN.TIMESERIESPOINT , \
125 MEDIAN.TIMESERIESVALUE TSV, \
126 MEDIAN.TIMESERIES TS, \
127 MEDIAN.MEASUREMENT M, \
128 MEDIAN.SOURCEINFO SI \
129 WHERE SI.SOURCEID = M.SOURCEID AND \
130 M.FEATUREID = MEDIAN.TIMESERIESPOINT.FEATUREID AND \
131 M.MEASUREMENTID = TSV.MEASUREMENTID AND \
132 TSV.TIMESERIESID = TS.TIMESERIESID AND \
133 TS.PARAMETERID IN ( 2 ) AND \
134 TSV.MEASUREMENTID IN ( 500061 ) AND \
135 TSV.TIMEVALUE >= to_date('2002.09.27 11:00:00', 'YYYY.MM.DD HH24:MI:SS') AND \
136 TSV.TIMEVALUE <= to_date('2008.12.09 03:00:00', 'YYYY.MM.DD HH24:MI:SS') \
137 ORDER BY TSV.MEASUREMENTID , \
138 TSV.TIMESERIESID , \
139 TS.PARAMETERID , \
140 TSV.TIMEVALUE
141 query_odv_data_vp_timeseriespoint = SELECT SI.NAME CRUISE, \
142 MEDIAN.TIMESERIESPOINT.NAME STATION, \
143 '*' TYPE, \
144 ST_ASTEXT(SHAPE), \
145 0 BOTDEPTH, \
146 (M.ZLOCATION *-1) DEPTH, \
147 TSV.TIMEVALUE, \
148 TSV.DATAVALUE, \
149 TS.PARAMETERID PARAMETER, \
150 TSV.MEASUREMENTID, \
151 TSV.TIMESERIESID \
152 FROM MEDIAN.TIMESERIESPOINT , \
153 MEDIAN.TIMESERIESVALUE TSV, \
154 MEDIAN.TIMESERIES TS, \
155 MEDIAN.MEASUREMENT M, \
156 MEDIAN.SOURCEINFO SI \
157 WHERE SI.SOURCEID = M.SOURCEID AND \
158 M.FEATUREID = MEDIAN.TIMESERIESPOINT.FEATUREID AND \
159 M.MEASUREMENTID = TSV.MEASUREMENTID AND \
160 TSV.TIMESERIESID = TS.TIMESERIESID AND \
161 M.FEATUREID = 100011 AND \
162 TS.PARAMETERID IN ( 2 , 31 ) AND \
163 TSV.TIMEVALUE IN (to_date('2008.12.09 00:00:00', 'YYYY.MM.DD HH24:MI:SS')) AND \
164 M.ZLOCATION >= -40 AND \
165 M.ZLOCATION <= -7 \
166 ORDER BY TSV.MEASUREMENTID , \
167 TSV.TIMESERIESID , \
168 TS.PARAMETERID , \
169 TSV.TIMEVALUE
170
171
172 query_odv_data_mesh = select /*+ parallel(timevalue,10) */ \
173 SI.NAME CRUISE, \
174 m.MESHID || '-' || MEDIAN.MESHPOINT.IPOSITION || '-' || MEDIAN.MESHPOINT.JPOSITION STATION, \
175 '*' TYPE, \
176 ST_ASTEXT(SHAPE), \
177 0 BOTDEPTH, \
178 (((ML.UPPERZLOCATION + ML.LOWERZLOCATION) / 2)*-1) DEPTH, \
179 msv.TIMEVALUE, \
180 msv.DATAVALUE , \
181 msv.PARAMETERID PARAMETER \
182 from MEDIAN.MESHSCALARVALUE msv , \
183 MEDIAN.MESHPOINT, \
184 MEDIAN.MESH m, \
185 MEDIAN.SOURCEINFO SI, \
186 MEDIAN.MESHLAYER ML \
187 where (m.OBJECTID = 3 AND \
188 msv.PARTID >= m.PARTIDMIN AND \
189 msv.PARTID <= m.PARTIDMAX ) AND \
190 m.SOURCEID = SI.SOURCEID AND \
191 ML.MESHID = MEDIAN.MESHPOINT.MESHID AND \
192 ML.KPOSITION = MEDIAN.MESHPOINT.KPOSITION AND \
193 msv.FEATUREID in ( 2003771 ) AND \
194 msv.PARAMETERID in ( 2 , 1 ) AND \
195 MEDIAN.MESHPOINT.FEATUREID in ( 2003771 , 2012198 ) AND \
196 msv.TIMEVALUE >= to_date('2009.10.30 01:00:00', 'YYYY.MM.DD HH24:MI:SS') AND \
197 msv.TIMEVALUE <= to_date('2009.11.09 00:00:00', 'YYYY.MM.DD HH24:MI:SS') \
198 order by MEDIAN.MESHPOINT.FEATUREID, \
199 msv.FEATUREID, \
200 msv.PARAMETERID, \
201 msv.TIMEVALUE
202
203
204 query_odv_data_vp_mesh = select \
205 SI.NAME CRUISE, \
206 m.MESHID || '-' || MEDIAN.MESHPOINT.IPOSITION || '-' || MEDIAN.MESHPOINT.JPOSITION STATION, \
207 '*' TYPE, \
208 ST_ASTEXT(SHAPE), \
209 0 BOTDEPTH, \
210 (((ML.UPPERZLOCATION + ML.LOWERZLOCATION) / 2)*-1) DEPTH, \
211 msv.DATAVALUE , \
212 msv.PARAMETERID PARAMETER, \
213 msv.TIMEVALUE , \
214 from MEDIAN.MESHLAYER ml, \
215 MEDIAN.MESHPOINT, \
216 MEDIAN.MESH m, \
217 MEDIAN.MESHSCALARVALUE msv, \
218 MEDIAN.SOURCEINFO SI, \
219 where m.SOURCEID = SI.SOURCEID AND \
220 msv.FEATUREID = MEDIAN.MESHPOINT. FEATUREID AND \
221 ml.KPOSITION = MEDIAN.MESHPOINT.KPOSITION and \
222 ml.MESHID = MEDIAN.MESHPOINT.MESHID and \
223 m.MESHID = MEDIAN.MESHPOINT.MESHID AND \
224 m.PARTIDMIN <= msv.PARTID AND \
225 m.PARTIDMAX >= msv.PARTID AND \
226 msv.PARAMETERID in (1 , 2) AND \
227 msv.TIMEVALUE in (to_date('2009.11.09 00:00:00', 'YYYY.MM.DD HH24:MI:SS')) AND \
228 m.OBJECTID = 3 AND \
229 MEDIAN.MESHPOINT.FEATUREID in ( select FEATUREID \
230 from MEDIAN.MESHPOINT mp, \
231 MEDIAN.MESH m \
232 where m.OBJECTID = 3 AND \
233 mp.MESHID = m.MESHID AND \
234 IPOSITION = (select IPOSITION from MEDIAN.MESHPOINT where FEATUREID = 2003771) and \
235 JPOSITION = (select JPOSITION from MEDIAN.MESHPOINT where FEATUREID = 2003771)) AND \
236 MEDIAN.MESHPOINT.KPOSITION <= 4 AND \
237 MEDIAN.MESHPOINT.KPOSITION >= 1 \
238 order by msv.PARAMETERID, \
239 msv.TIMEVALUE, \
240 ml.UPPERZLOCATION
241
242 test_vehicle_orig = SELECT DISTINCT V.VEHICLEID KEY, \
243 V.NAME VALUE \
244 FROM MEDIAN.VEHICLE V, \
245 MEDIAN.CRUISE C, \
246 MEDIAN.TRACK T, \
247 MEDIAN.SURVEYINFO S, \
248 MEDIAN.INSTANTANEOUSPOINT I, \
249 MEDIAN.MEASUREMENT M \
250 WHERE V.VEHICLEID = C.VEHICLEID AND \
251 C.CRUISEID = T.CRUISEID AND \
252 T.TRACKID = S.TRACKID AND \
253 S.SURVEYID = I.SURVEYID AND \
254 I.FEATUREID = M.FEATUREID AND \
255 M.SOURCEID = 34 \
256 ORDER BY V.NAME
257
258
259 test_vehicle_area_ip = SELECT V.VEHICLEID KEY, \
260 V.NAME VALUE \
261 FROM MEDIAN.INSTANTANEOUSPOINT, \
262 MEDIAN.TRACK T , \
263 MEDIAN.VEHICLE V, \
264 MEDIAN.CRUISE C, \
265 MEDIAN.SURVEYINFO S, \
266 MEDIAN.MEASUREMENT M \
267 WHERE V.VEHICLEID = C.VEHICLEID AND \
268 C.CRUISEID = T.CRUISEID AND \
269 T.TRACKID = S.TRACKID AND \
270 S.SURVEYID = MEDIAN.INSTANTANEOUSPOINT.SURVEYID AND \
271 MEDIAN.INSTANTANEOUSPOINT.FEATUREID = M.FEATUREID AND \
272 M.SOURCEID = 34 AND \
273 INTERSECTS(SHAPE, (SELECT st_astext(SHAPE) FROM MEDIAN.FEATUREAREA WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND FEATURECODE = 24 )) \
274 ORDER BY V.NAME
275
276 test_vehicle_area = SELECT V.VEHICLEID KEY, \
277 V.NAME VALUE \
278 FROM MEDIAN.TRACK , \
279 MEDIAN.VEHICLE V, \
280 MEDIAN.CRUISE C \
281 WHERE V.VEHICLEID = C.VEHICLEID AND \
282 C.CRUISEID = MEDIAN.TRACK.CRUISEID AND \
283 MEDIAN.TRACK.TRACKID IN \
284 (SELECT DISTINCT S.TRACKID \
285 FROM MEDIAN.MEASUREMENT M ,\
286 MEDIAN.INSTANTANEOUSPOINT I, \
287 MEDIAN.SURVEYINFO S \
288 WHERE M.SOURCEID = 34 AND \
289 I.FEATUREID = M.FEATUREID AND \
290 S.SURVEYID = I.SURVEYID)AND \
291 INTERSECTS(SHAPE, (SELECT st_astext(SHAPE) FROM MEDIAN.FEATUREAREA WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND FEATURECODE = 24 )) \
292 ORDER BY V.NAME
293 test_track_orig = SELECT \
294 ST_ASTEXT(SHAPE), \
295 TRACKID KEY \
296 FROM MEDIAN.TRACK \
297 WHERE CRUISEID = 2 \
298 ORDER BY STARTDATE, \
299 ENDDATE, \
300 NAME
301
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