# HG changeset patch # User Tim Englich # Date 1255512498 0 # Node ID 000e00592ba58b5983e0347f8803f6bf0183912b # Parent 1530890b28c923e6df8a3dc04f2cd8cc1a57405c Added InnerJoin, Order By and Group By Support to Spatial-Queries geo-backend/trunk@217 c6561f87-3c4e-4783-a992-168aeb5c3f6f diff -r 1530890b28c9 -r 000e00592ba5 geo-backend/ChangeLog --- a/geo-backend/ChangeLog Tue Oct 13 14:30:52 2009 +0000 +++ b/geo-backend/ChangeLog Wed Oct 14 09:28:18 2009 +0000 @@ -1,3 +1,12 @@ +2009-10-14 Tim Englich + + * src/test/ressources/QueryExecutorTestCase.properties: + Changed Test-Query-Statement for Spatial-Queries with INNERJOINS and ORDER BY + * src/test/java/de/intevation/gnv/geobackend/base/query/QueryExecutorTestCase.java (testSpatialQueryWithoutIntersects): + Changed expected Result-Size because of Changes in the QueryStatement which is used. + * src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEStatement.java (executeQuery): + Added INNERJOIN, ORDER BY and GROUP BY support to Spatial Queries + 2009-10-13 Tim Englich * src/test/java/de/intevation/gnv/geobackend/base/query/QueryExecutorTestCase.java (testSpatialQueryWithoutIntersects): diff -r 1530890b28c9 -r 000e00592ba5 geo-backend/src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEStatement.java --- a/geo-backend/src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEStatement.java Tue Oct 13 14:30:52 2009 +0000 +++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEStatement.java Wed Oct 14 09:28:18 2009 +0000 @@ -18,6 +18,7 @@ import com.esri.sde.sdk.client.SeFilter; import com.esri.sde.sdk.client.SeLayer; import com.esri.sde.sdk.client.SeQuery; +import com.esri.sde.sdk.client.SeQueryInfo; import com.esri.sde.sdk.client.SeRow; import com.esri.sde.sdk.client.SeShape; import com.esri.sde.sdk.client.SeShapeFilter; @@ -127,9 +128,19 @@ String[] values = statement.toLowerCase().split("where", 2); String where = values.length > 1 ? values[1].trim() : ""; values = values[0].split("from", 2); - String layerName = values[1].toUpperCase().trim(); + String[] tableNames = values[1].toUpperCase().trim().split(","); String[] returnFields = values[0].replaceAll("select", "").trim().split(","); String geometryColumnName = "N/N"; + String byClause = null; + int byClausePos = where.indexOf("group by"); // TODO: Reihenfolge signifikant? + if (byClausePos < 0){ + byClausePos = where.indexOf("order by"); + } + if (byClausePos > 0){ + byClause = where.substring(byClausePos); + where = where.substring(0,byClausePos); + } + for (int i = 0; i < returnFields.length; i++){ returnFields[i] = returnFields[i].trim(); if (returnFields[i].startsWith("st_astext(")){ @@ -138,6 +149,11 @@ geometryColumnName = returnFields[i]; } } + + for (int i = 0; i < tableNames.length; i++){ + tableNames[i] = tableNames[i].trim(); + } + Geometry g = null; int pos = where.indexOf("intersects"); if (pos > 0 ){ @@ -148,7 +164,7 @@ g = new WKTReader().read(wkt); } - return this.executeQuery(this.connection.getSeConnection(), layerName, geometryColumnName, where, g, returnFields); + return this.executeQuery(this.connection.getSeConnection(), tableNames, geometryColumnName, where, g, returnFields,byClause); }else{ query = new SeQuery(this.connection.getSeConnection()); query.prepareSql(statement); @@ -164,23 +180,24 @@ } - private ResultSet executeQuery(SeConnection con, String pLayername, + private ResultSet executeQuery(SeConnection con, String[] pLayername, String pSpatialColumnName, String pWhere, - Geometry g, String[] pReturnFields) + Geometry g, String[] pReturnFields, String byClause) throws SQLException { log.debug("executeQuery()"); try { // get the layer for querying - SeLayer lLayer = new SeLayer(con, pLayername, pSpatialColumnName); + SeShapeFilter[] filters = null; if (g != null){ + SeLayer lLayer = new SeLayer(con, pLayername[0], pSpatialColumnName); SeShape shape = new SeShape(); shape.setCoordRef(lLayer.getCoordRef()); SDEPoint[] lPoints = new ArcSDEUtils().createPoints(g); shape.generatePolygon(lPoints.length, 1, null, lPoints); - SeShapeFilter filter = new SeShapeFilter(pLayername, + SeShapeFilter filter = new SeShapeFilter(pLayername[0], pSpatialColumnName, shape, SeFilter.METHOD_AI); filters = new SeShapeFilter[1]; filters[0] = filter; @@ -188,8 +205,18 @@ SeQuery spatialQuery = null; SeSqlConstruct sqlCons = new SeSqlConstruct(pLayername, pWhere); - spatialQuery = new SeQuery(con, pReturnFields, sqlCons); - spatialQuery.prepareQuery(); + spatialQuery = new SeQuery(con);//, pReturnFields, sqlCons); + + SeQueryInfo queryInfo = new SeQueryInfo(); + queryInfo.setColumns(pReturnFields); + + if (byClause != null){ + queryInfo.setByClause(byClause); + } + + queryInfo.setConstruct(sqlCons); + spatialQuery.prepareQueryInfo(queryInfo); + /* * Set spatial constraints */ @@ -202,7 +229,12 @@ return this.handleResultSet(spatialQuery); } catch (Exception e) { - log.error(e.getMessage(), e); + if (e instanceof SeException){ + ArcSDEUtils.printError((SeException)e); + }else{ + log.error(e.getMessage(), e); + } + throw new SQLException(e.getMessage()); } } diff -r 1530890b28c9 -r 000e00592ba5 geo-backend/src/test/java/de/intevation/gnv/geobackend/base/query/QueryExecutorTestCase.java --- a/geo-backend/src/test/java/de/intevation/gnv/geobackend/base/query/QueryExecutorTestCase.java Tue Oct 13 14:30:52 2009 +0000 +++ b/geo-backend/src/test/java/de/intevation/gnv/geobackend/base/query/QueryExecutorTestCase.java Wed Oct 14 09:28:18 2009 +0000 @@ -58,7 +58,7 @@ public void testSpatialQueryWithoutIntersects(){ try { - this.testQuery(1332, "spatial_query_without_intersects", null); + this.testQuery(286, "spatial_query_without_intersects", null); } catch (QueryException e) { log.error(e,e); fail(); diff -r 1530890b28c9 -r 000e00592ba5 geo-backend/src/test/ressources/QueryExecutorTestCase.properties --- a/geo-backend/src/test/ressources/QueryExecutorTestCase.properties Tue Oct 13 14:30:52 2009 +0000 +++ b/geo-backend/src/test/ressources/QueryExecutorTestCase.properties Wed Oct 14 09:28:18 2009 +0000 @@ -20,4 +20,18 @@ KPOSITION = 1 AND \ INTERSECTS(SHAPE,"POLYGON((6.483333296817733 56.50000002211891,6.483012102287745 56.50981053688071,6.48205002744038 56.51957897448855,6.480451095589132 56.52926359306454,6.478222179894658 56.538822818368885,6.475372835726546 56.54821591435217,6.471915300663454 56.557402480241024,6.46786415921693 56.56634328872852,6.463237181021697 56.57500011833619,6.458053812093169 56.583335585775785,6.452336348293812 56.591314319415915,6.446109264780915 56.59890195345352,6.439399383644684 56.60606613374233,6.432235203355873 56.61277601487856,6.424647569318296 56.61900309839146,6.416668835678138 56.62472056219082,6.408333368238544 56.629903931119344,6.399676538630871 56.63453090931458,6.390735730143376 56.6385820507611,6.381549164254523 56.64203958582419,6.372156068271238 56.644888929992305,6.362596842966894 56.64711784568678,6.352912224390906 56.64871677753803,6.34314378678306 56.64967885238539,6.333333272021264 56.65000004691538,6.323522757259468 56.64967885238539,6.31375431965165 56.64871677753803,6.304069701075662 56.64711784568678,6.29451047577129 56.644888929992305,6.285117379788005 56.64203958582419,6.27593081389918 56.6385820507611,6.266990005411657 56.63453090931458,6.258333343442075 56.629903931119344,6.24999770836439 56.62472056219082,6.24201897472426 56.61900309839146,6.234431340686655 56.61277601487856,6.227267160397872 56.60606613374233,6.220557279261612 56.59890195345352,6.214330195748715 56.591314319415915,6.2086127319493585 56.583335585775785,6.203429363020831 56.57500011833619,6.198802384825626 56.56634328872852,6.194751243379102 56.557402480241024,6.19129370831601 56.54821591435217,6.188444364147898 56.538822818368885,6.186215448453396 56.52926359306454,6.184616516602176 56.51957897448855,6.183654441754783 56.50981053688071,6.183333247224795 56.50000002211891,6.183654441754783 56.490189507357115,6.184616516602176 56.4804210697493,6.186215448453396 56.47073645117331,6.188444364147898 56.46117722586894,6.19129370831601 56.45178412988565,6.194751243379102 56.44259756399683,6.198802384825626 56.433656755509304,6.203429363020831 56.42500009353972,6.2086127319493585 56.41666445846204,6.214330195748715 56.40868572482191,6.220557279261612 56.4010980907843,6.227267160397872 56.39393391049552,6.234431340686655 56.38722402935926,6.24201897472426 56.38099694584636,6.24999770836439 56.375279482047006,6.258333343442075 56.37009611311848,6.266990005411657 56.36546913492327,6.27593081389918 56.36141799347675,6.285117379788005 56.35796045841366,6.29451047577129 56.355111114245545,6.304069701075662 56.35288219855104,6.31375431965165 56.351283266699795,6.323522757259468 56.35032119185243,6.333333272021264 56.34999999732244,6.34314378678306 56.35032119185243,6.352912224390906 56.351283266699795,6.362596842966894 56.35288219855104,6.372156068271238 56.355111114245545,6.381549164254523 56.35796045841366,6.390735730143376 56.36141799347675,6.399676538630871 56.36546913492327,6.408333368238544 56.37009611311848,6.416668835678138 56.375279482047006,6.424647569318296 56.38099694584636,6.432235203355873 56.38722402935926,6.439399383644684 56.39393391049552,6.446109264780915 56.4010980907843,6.452336348293812 56.40868572482191,6.458053812093169 56.41666445846204,6.463237181021697 56.42500009353972,6.46786415921693 56.433656755509304,6.471915300663454 56.44259756399683,6.475372835726546 56.45178412988565,6.478222179894658 56.46117722586894,6.480451095589132 56.47073645117331,6.48205002744038 56.4804210697493,6.483012102287745 56.490189507357115,6.483333296817733 56.50000002211891))") -spatial_query_without_intersects = SELECT ST_ASTEXT(SHAPE) FROM MEDIAN.INSTANTANEOUSPOINT where SURVEYID = 1895 +spatial_query_without_intersects = SELECT ST_ASTEXT(SHAPE), \ + MSV.DATAVALUE YORDINATE , \ + MSV.PARAMETERID GROUP1 , \ + M.ZLOCATION GROUP2, \ + 1 GROUP3 \ + FROM MEDIAN.INSTANTANEOUSPOINT, \ + MEDIAN.MEASUREMENT M, \ + MEDIAN.MEASUREDSCALARVALUE MSV \ + WHERE MEDIAN.INSTANTANEOUSPOINT.FEATUREID =m.FEATUREID AND \ + m.MEASUREMENTID = MSV.MEASUREMENTID AND \ + MEDIAN.INSTANTANEOUSPOINT.SURVEYID = 1895 AND \ + m.ZLOCATION IN (-16.843 , -15.852) AND \ + MSV.PARAMETERID in (2 , 1) \ + ORDER BY MSV.PARAMETERID, \ + m.ZLOCATION \ No newline at end of file