Mercurial > dive4elements > gnv-client

annotate geo-backend/src/test/ressources/QueryExecutorTestCase.properties @ 267:000e00592ba5

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added InnerJoin, Order By and Group By Support to Spatial-Queries geo-backend/trunk@217 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Tim Englich <tim.englich@intevation.de>
date Wed, 14 Oct 2009 09:28:18 +0000
parents 1530890b28c9
children db8920c5346d
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, \
000e00592ba5 Added InnerJoin, Order By and Group By Support to Spatial-Queries
Tim Englich <tim.englich@intevation.de>
parents: 266
diff changeset
37 m.ZLOCATION
http://dive4elements.wald.intevation.org