Mercurial > dive4elements > gnv-client

changeset 275:9063c5fcebf7

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added the possibility to use Innerselects in SpatialQueries geo-backend/trunk@286 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Tim Englich <tim.englich@intevation.de>
date Thu, 05 Nov 2009 15:03:51 +0000
parents ff1b7967e6b9
children 6503bc864017
files geo-backend/ChangeLog geo-backend/src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEStatement.java geo-backend/src/main/java/de/intevation/gnv/geobackend/sde/datasources/Row.java geo-backend/src/test/java/de/intevation/gnv/geobackend/base/query/QueryExecutorTestCase.java geo-backend/src/test/ressources/QueryExecutorTestCase.properties
diffstat 5 files changed, 104 insertions(+), 29 deletions(-) [+]
line wrap: on
line diff
--- a/geo-backend/ChangeLog	Mon Nov 02 10:53:06 2009 +0000
+++ b/geo-backend/ChangeLog	Thu Nov 05 15:03:51 2009 +0000
@@ -1,3 +1,14 @@
+2009-11-05  Tim Englich  <tim.englich@intevation.de>
+
+	* src/test/ressources/QueryExecutorTestCase.properties,
+	  src/test/java/de/intevation/gnv/geobackend/base/query/QueryExecutorTestCase.java (testSpatialQueryWithInnerSelect):
+	  Added an Tescase to test the InnerSelect Statments in Spatial-Queries.
+	* src/main/java/de/intevation/gnv/geobackend/sde/datasources/Row.java (getPosValue): 
+	  Bugfix. An Point could not have an Z-Coordinate-Value
+	* src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEStatement.java (executeQuery): 
+	  Added the possibility to execute Spatial-Queries using InnerStatements instead
+	  of WKT in Intersects-where-clauses.
+
 2009-11-02  Tim Englich  <tim.englich@intevation.de>
 
 	* src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEConnection.java (createStatement): 
--- a/geo-backend/src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEStatement.java	Mon Nov 02 10:53:06 2009 +0000
+++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEStatement.java	Thu Nov 05 15:03:51 2009 +0000
@@ -123,14 +123,14 @@
 	public ResultSet executeQuery(String statement) throws SQLException {
 		try {
 		    SeQuery query = null;
-	        if (statement.toLowerCase().contains("st_astext")){
+	        if (statement.toLowerCase().contains("st_astext")  || statement.toLowerCase().contains("intersects")){
 	            
 	            String[] values =  statement.toLowerCase().split("where", 2);
 	            String where = values.length > 1 ? values[1].trim() : "";
 	            values = values[0].split("from", 2);
 	            String[] tableNames = values[1].toUpperCase().trim().split(",");
 	            String[] returnFields  = values[0].replaceAll("select", "").trim().split(",");
-	            String geometryColumnName = "N/N";
+	            String geometryColumnName = null;
 	            String byClause = null;
 	            int byClausePos = where.indexOf("group by");
 	            if (byClausePos < 0){
@@ -158,11 +158,27 @@
 	            int pos = where.indexOf("intersects");
 	            if (pos > 0 ){
 	                String substr =  where.substring(pos);
+	                where = where.substring(0,where.lastIndexOf("intersects")); // TODO auch or unterstützen
 	                where = where.substring(0,where.lastIndexOf("and")); // TODO auch or unterstützen
 	                String intersectsStmt = substr.substring(0, substr.lastIndexOf(")")); // Annahme räumliches Stmt steht am Ende
-	                String wkt = intersectsStmt.substring(intersectsStmt.indexOf("\""), intersectsStmt.lastIndexOf("\"")).replace("\"", "").trim();
+	                String wkt = null;
+	                if (intersectsStmt.contains("select")){
+	                    // Anstelle eines WKT ist ein InnerSelect zum Bestimmen der Comparatorgeometrie gegeben.
+	                    String stmt = intersectsStmt.substring(intersectsStmt.indexOf("select"),intersectsStmt.lastIndexOf(")"));
+	                    ResultSet resultSet = this.executeQuery(stmt);
+	                    if (resultSet != null && resultSet.next()){
+	                        // TODO Selektion von mehreren Geometrien unterstützen.
+	                        wkt = resultSet.getString("SHAPE");
+	                    }
+	                }else{
+	                    wkt = intersectsStmt.substring(intersectsStmt.indexOf("\""), intersectsStmt.lastIndexOf("\"")).replace("\"", "").trim();
+	                }
 	                g = new WKTReader().read(wkt);
 	                
+	                if (geometryColumnName == null){
+	                    geometryColumnName = "SHAPE"; // TODO dynamisch aus Intersects auslesen.
+	                }
+	                
 	            }
 	            return this.executeQuery(this.connection.getSeConnection(), tableNames, geometryColumnName, where, g, returnFields,byClause);
 	        }else{
--- a/geo-backend/src/main/java/de/intevation/gnv/geobackend/sde/datasources/Row.java	Mon Nov 02 10:53:06 2009 +0000
+++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/sde/datasources/Row.java	Thu Nov 05 15:03:51 2009 +0000
@@ -247,18 +247,22 @@
                     returnValue.append("POINT(")
                                .append(mPoint[0].getX())
                                .append(" ")
-                               .append(mPoint[0].getY())
-                               .append(" ")
-                               .append(mPoint[0].getZ())
-                               .append(")");
+                               .append(mPoint[0].getY());
+                                if (mPoint[0].is3D()){
+                                   returnValue.append(" ")
+                                   .append(mPoint[0].getZ());
+                               }
+                                returnValue.append(")");
                 }else if (val.isLine()){
                     returnValue.append("LINESTRING(");
                     for (int i = 0; i< mPoint.length;i++){
                         returnValue.append(mPoint[i].getX())
                                    .append(" ")
-                                   .append(mPoint[i].getY())
-                                   .append(" ")
-                                   .append(mPoint[i].getZ());
+                                   .append(mPoint[i].getY());
+                                    if (mPoint[i].is3D()){
+                                        returnValue.append(" ")
+                                        .append(mPoint[i].getZ());
+                                    }
                         if (i < mPoint.length-1){
                             returnValue.append(" , ");
                         }
@@ -266,15 +270,19 @@
                     returnValue.append(")");
                 } else if (val.isPolygon()){
                     returnValue.append("POLYGON((");
-                    for (int i = 0; i< mPoint.length;i++){
-                        returnValue.append(mPoint[i].getX())
+                    int length = mPoint.length;
+                    for (int i = 0; i< length ;i++){
+                       SDEPoint p = mPoint[i];
+                       returnValue.append(p.getX())
                                    .append(" ")
-                                   .append(mPoint[i].getY())
-                                   .append(" ")
-                                   .append(mPoint[i].getZ());
-                        if (i < mPoint.length-1){
-                            returnValue.append(" , ");
-                        }
+                                   .append(p.getY());
+                       if (p.is3D()){
+                           returnValue.append(" ")
+                                      .append(p.getZ());
+                       }
+                       if (i < length-1){
+                           returnValue.append(" , ");
+                       }
                     }
                     returnValue.append(")");
                     // TODO Wie werden innere Ringe unterstützt.
--- a/geo-backend/src/test/java/de/intevation/gnv/geobackend/base/query/QueryExecutorTestCase.java	Mon Nov 02 10:53:06 2009 +0000
+++ b/geo-backend/src/test/java/de/intevation/gnv/geobackend/base/query/QueryExecutorTestCase.java	Thu Nov 05 15:03:51 2009 +0000
@@ -70,6 +70,25 @@
         }
     }
     
+    public void testSpatialQueryWithInnerSelect(){
+        try {
+            this.testQuery(1998, "spatial_query_with_innerselect", null);
+       } catch (QueryException e) {
+            log.error(e,e);
+            fail();
+        }
+    }
+    
+    
+    public void  testChooseFis(){
+        try {
+            this.testQuery(1, "choose_fis_query", null);
+        } catch (QueryException e) {
+            log.error(e, e);
+            fail();
+        }
+    }
+    
     
     /**
      * Tests if the Databaseconnection can be established and
@@ -127,16 +146,16 @@
             assertEquals(resultsize, results.size());
             log.debug(resultsize+" Datensätze erhalten");
             Iterator<Result> it = results.iterator();
-//            while (it.hasNext()){
-//                Result tmpResult = it.next();
-//                ResultDescriptor resultDescriptor = tmpResult.getResultDescriptor();
-//                int columns = resultDescriptor.getColumnCount();
-//                for (int i = 0; i < columns; i++){
-//                    String columnName = resultDescriptor.getColumnName(i);
-//                    Object value = tmpResult.getString(columnName);
-//                    log.debug(columnName + " ==> "+value.toString());
-//                }
-//            }
+            while (it.hasNext()){
+                Result tmpResult = it.next();
+                ResultDescriptor resultDescriptor = tmpResult.getResultDescriptor();
+                int columns = resultDescriptor.getColumnCount();
+                for (int i = 0; i < columns; i++){
+                    String columnName = resultDescriptor.getColumnName(i);
+                    Object value = tmpResult.getString(columnName);
+                    log.debug(columnName + " ==> "+value.toString());
+                }
+            }
 //        }
         log.debug("Query dauerte: "+(System.currentTimeMillis()-start) +"ms");
     }
--- a/geo-backend/src/test/ressources/QueryExecutorTestCase.properties	Mon Nov 02 10:53:06 2009 +0000
+++ b/geo-backend/src/test/ressources/QueryExecutorTestCase.properties	Thu Nov 05 15:03:51 2009 +0000
@@ -74,4 +74,25 @@
 spatial_query_without_intersects3 = SELECT ST_ASTEXT(SHAPE), MSV.DATAVALUE YORDINATE, MEDIAN.MESHFACE.JPOSITION, MEDIAN.MESHFACE.IPOSITION, MEDIAN.MESHFACE.KPOSITION from MEDIAN.MESHLAYER ML, MEDIAN.MESHFACE, MEDIAN.MESH M, MEDIAN.MESHSCALARVALUE MSV where MSV.FEATUREID = MEDIAN.MESHFACE.FEATUREID AND ML.KPOSITION = MEDIAN.MESHFACE.KPOSITION and ML.MESHID = MEDIAN.MESHFACE.MESHID and M.MESHID = MEDIAN.MESHFACE.MESHID AND M.PARTIDMIN <= MSV.PARTID AND M.PARTIDMAX >= MSV.PARTID AND MSV.PARAMETERID = 1 AND MSV.TIMEVALUE = to_date('2007.01.03 00:00:00', 'YYYY.MM.DD HH24:MI:SS') AND M.OBJECTID = 2 AND MEDIAN.MESHFACE.KPOSITION = 1 order by MEDIAN.MESHFACE.JPOSITION , MEDIAN.MESHFACE.IPOSITION
 spatial_query_without_intersects4 = SELECT ST_ASTEXT(SHAPE), MSV.DATAVALUE YORDINATE, MEDIAN.MESHFACE.JPOSITION, MEDIAN.MESHFACE.IPOSITION, MEDIAN.MESHFACE.KPOSITION from MEDIAN.MESHLAYER ML, MEDIAN.MESHFACE, MEDIAN.MESH M, MEDIAN.MESHSCALARVALUE MSV where MSV.FEATUREID = MEDIAN.MESHFACE.FEATUREID AND ML.KPOSITION = MEDIAN.MESHFACE.KPOSITION and ML.MESHID = MEDIAN.MESHFACE.MESHID and M.MESHID = MEDIAN.MESHFACE.MESHID AND M.PARTIDMIN <= MSV.PARTID AND M.PARTIDMAX >= MSV.PARTID AND MSV.PARAMETERID = 1 AND MSV.TIMEVALUE = to_date('2008.01.15 12:00:00', 'YYYY.MM.DD HH24:MI:SS') AND M.OBJECTID = 321 AND MEDIAN.MESHFACE.KPOSITION = 1 order by MEDIAN.MESHFACE.JPOSITION , MEDIAN.MESHFACE.IPOSITION
 spatial_query_without_intersects5 = SELECT ST_ASTEXT(SHAPE), MSV.DATAVALUE YORDINATE, MEDIAN.MESHFACE.JPOSITION, MEDIAN.MESHFACE.IPOSITION, MEDIAN.MESHFACE.KPOSITION from MEDIAN.MESHLAYER ML, MEDIAN.MESHFACE, MEDIAN.MESH M, MEDIAN.MESHSCALARVALUE MSV where MSV.FEATUREID = MEDIAN.MESHFACE.FEATUREID AND ML.KPOSITION = MEDIAN.MESHFACE.KPOSITION and ML.MESHID = MEDIAN.MESHFACE.MESHID and M.MESHID = MEDIAN.MESHFACE.MESHID AND M.PARTIDMIN <= MSV.PARTID AND M.PARTIDMAX >= MSV.PARTID AND MSV.PARAMETERID = 2 AND MSV.TIMEVALUE = to_date('2009.10.23 01:00:00', 'YYYY.MM.DD HH24:MI:SS') AND M.OBJECTID = 1 AND MEDIAN.MESHFACE.KPOSITION = 1 order by MEDIAN.MESHFACE.JPOSITION , MEDIAN.MESHFACE.IPOSITION
-spatial_query_without_intersects6 = SELECT ST_ASTEXT(SHAPE), MSV.DATAVALUE YORDINATE, MEDIAN.MESHFACE.JPOSITION, MEDIAN.MESHFACE.IPOSITION, MEDIAN.MESHFACE.KPOSITION from MEDIAN.MESHLAYER ML, MEDIAN.MESHFACE, MEDIAN.MESH M, MEDIAN.MESHSCALARVALUE MSV where MSV.FEATUREID = MEDIAN.MESHFACE.FEATUREID AND ML.KPOSITION = MEDIAN.MESHFACE.KPOSITION and ML.MESHID = MEDIAN.MESHFACE.MESHID and M.MESHID = MEDIAN.MESHFACE.MESHID AND M.PARTIDMIN <= MSV.PARTID AND M.PARTIDMAX >= MSV.PARTID AND MSV.PARAMETERID = 2 AND MSV.TIMEVALUE = to_date('2009.10.23 01:00:00', 'YYYY.MM.DD HH24:MI:SS') AND M.OBJECTID = 3 AND MEDIAN.MESHFACE.KPOSITION = 1 order by MEDIAN.MESHFACE.JPOSITION , MEDIAN.MESHFACE.IPOSITION
\ No newline at end of file
+spatial_query_without_intersects6 = SELECT ST_ASTEXT(SHAPE), MSV.DATAVALUE YORDINATE, MEDIAN.MESHFACE.JPOSITION, MEDIAN.MESHFACE.IPOSITION, MEDIAN.MESHFACE.KPOSITION from MEDIAN.MESHLAYER ML, MEDIAN.MESHFACE, MEDIAN.MESH M, MEDIAN.MESHSCALARVALUE MSV where MSV.FEATUREID = MEDIAN.MESHFACE.FEATUREID AND ML.KPOSITION = MEDIAN.MESHFACE.KPOSITION and ML.MESHID = MEDIAN.MESHFACE.MESHID and M.MESHID = MEDIAN.MESHFACE.MESHID AND M.PARTIDMIN <= MSV.PARTID AND M.PARTIDMAX >= MSV.PARTID AND MSV.PARAMETERID = 2 AND MSV.TIMEVALUE = to_date('2009.10.23 01:00:00', 'YYYY.MM.DD HH24:MI:SS') AND M.OBJECTID = 3 AND MEDIAN.MESHFACE.KPOSITION = 1 order by MEDIAN.MESHFACE.JPOSITION , MEDIAN.MESHFACE.IPOSITION
+
+
+
+choose_fis_query = select m.sourceid, \
+    from median.meshpoint ,\
+         median.mesh \
+    where median.meshpoint.meshid = median.mesh.meshid and \
+          INTERSECTS(SHAPE,"POLYGON((6.483333296817733 56.50000002211891,6.483012102287745 56.50981053688071,6.48205002744038 56.51957897448855,6.480451095589132 56.52926359306454,6.478222179894658 56.538822818368885,6.475372835726546 56.54821591435217,6.471915300663454 56.557402480241024,6.46786415921693 56.56634328872852,6.463237181021697 56.57500011833619,6.458053812093169 56.583335585775785,6.452336348293812 56.591314319415915,6.446109264780915 56.59890195345352,6.439399383644684 56.60606613374233,6.432235203355873 56.61277601487856,6.424647569318296 56.61900309839146,6.416668835678138 56.62472056219082,6.408333368238544 56.629903931119344,6.399676538630871 56.63453090931458,6.390735730143376 56.6385820507611,6.381549164254523 56.64203958582419,6.372156068271238 56.644888929992305,6.362596842966894 56.64711784568678,6.352912224390906 56.64871677753803,6.34314378678306 56.64967885238539,6.333333272021264 56.65000004691538,6.323522757259468 56.64967885238539,6.31375431965165 56.64871677753803,6.304069701075662 56.64711784568678,6.29451047577129 56.644888929992305,6.285117379788005 56.64203958582419,6.27593081389918 56.6385820507611,6.266990005411657 56.63453090931458,6.258333343442075 56.629903931119344,6.24999770836439 56.62472056219082,6.24201897472426 56.61900309839146,6.234431340686655 56.61277601487856,6.227267160397872 56.60606613374233,6.220557279261612 56.59890195345352,6.214330195748715 56.591314319415915,6.2086127319493585 56.583335585775785,6.203429363020831 56.57500011833619,6.198802384825626 56.56634328872852,6.194751243379102 56.557402480241024,6.19129370831601 56.54821591435217,6.188444364147898 56.538822818368885,6.186215448453396 56.52926359306454,6.184616516602176 56.51957897448855,6.183654441754783 56.50981053688071,6.183333247224795 56.50000002211891,6.183654441754783 56.490189507357115,6.184616516602176 56.4804210697493,6.186215448453396 56.47073645117331,6.188444364147898 56.46117722586894,6.19129370831601 56.45178412988565,6.194751243379102 56.44259756399683,6.198802384825626 56.433656755509304,6.203429363020831 56.42500009353972,6.2086127319493585 56.41666445846204,6.214330195748715 56.40868572482191,6.220557279261612 56.4010980907843,6.227267160397872 56.39393391049552,6.234431340686655 56.38722402935926,6.24201897472426 56.38099694584636,6.24999770836439 56.375279482047006,6.258333343442075 56.37009611311848,6.266990005411657 56.36546913492327,6.27593081389918 56.36141799347675,6.285117379788005 56.35796045841366,6.29451047577129 56.355111114245545,6.304069701075662 56.35288219855104,6.31375431965165 56.351283266699795,6.323522757259468 56.35032119185243,6.333333272021264 56.34999999732244,6.34314378678306 56.35032119185243,6.352912224390906 56.351283266699795,6.362596842966894 56.35288219855104,6.372156068271238 56.355111114245545,6.381549164254523 56.35796045841366,6.390735730143376 56.36141799347675,6.399676538630871 56.36546913492327,6.408333368238544 56.37009611311848,6.416668835678138 56.375279482047006,6.424647569318296 56.38099694584636,6.432235203355873 56.38722402935926,6.439399383644684 56.39393391049552,6.446109264780915 56.4010980907843,6.452336348293812 56.40868572482191,6.458053812093169 56.41666445846204,6.463237181021697 56.42500009353972,6.46786415921693 56.433656755509304,6.471915300663454 56.44259756399683,6.475372835726546 56.45178412988565,6.478222179894658 56.46117722586894,6.480451095589132 56.47073645117331,6.48205002744038 56.4804210697493,6.483012102287745 56.490189507357115,6.483333296817733 56.50000002211891))") \
+    group by sourceid
+    
+    
+spatial_query_with_innerselect = SELECT M.OBJECTID KEY, \
+         M.NAME VALUE \
+     FROM MEDIAN.MESHPOINT, \
+          MEDIAN.MESH M \
+     WHERE M.SOURCEID IN (2) AND \
+           M.MESHID = MEDIAN.MESHPOINT.MESHID AND \
+           INTERSECTS(SHAPE, \
+                      (SELECT st_astext(SHAPE) FROM MEDIAN.FEATUREAREA WHERE (FEATURETYPE = 7 OR FEATURETYPE = 8) AND FEATURECODE = 41 )\
+                      ) \
+     order by M.NAME
http://dive4elements.wald.intevation.org