Mercurial > dive4elements > gnv-client

comparison geo-backend/src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEStatement.java @ 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 ff1b7967e6b9
comparison
equal deleted inserted replaced
266:1530890b28c9 267:000e00592ba5
16 import com.esri.sde.sdk.client.SeConnection; 16 import com.esri.sde.sdk.client.SeConnection;
17 import com.esri.sde.sdk.client.SeException; 17 import com.esri.sde.sdk.client.SeException;
18 import com.esri.sde.sdk.client.SeFilter; 18 import com.esri.sde.sdk.client.SeFilter;
19 import com.esri.sde.sdk.client.SeLayer; 19 import com.esri.sde.sdk.client.SeLayer;
20 import com.esri.sde.sdk.client.SeQuery; 20 import com.esri.sde.sdk.client.SeQuery;
21 import com.esri.sde.sdk.client.SeQueryInfo;
21 import com.esri.sde.sdk.client.SeRow; 22 import com.esri.sde.sdk.client.SeRow;
22 import com.esri.sde.sdk.client.SeShape; 23 import com.esri.sde.sdk.client.SeShape;
23 import com.esri.sde.sdk.client.SeShapeFilter; 24 import com.esri.sde.sdk.client.SeShapeFilter;
24 import com.esri.sde.sdk.client.SeSqlConstruct; 25 import com.esri.sde.sdk.client.SeSqlConstruct;
25 import com.vividsolutions.jts.geom.Geometry; 26 import com.vividsolutions.jts.geom.Geometry;
125 if (statement.toLowerCase().contains("st_astext")){ 126 if (statement.toLowerCase().contains("st_astext")){
126 127
127 String[] values = statement.toLowerCase().split("where", 2); 128 String[] values = statement.toLowerCase().split("where", 2);
128 String where = values.length > 1 ? values[1].trim() : ""; 129 String where = values.length > 1 ? values[1].trim() : "";
129 values = values[0].split("from", 2); 130 values = values[0].split("from", 2);
130 String layerName = values[1].toUpperCase().trim(); 131 String[] tableNames = values[1].toUpperCase().trim().split(",");
131 String[] returnFields = values[0].replaceAll("select", "").trim().split(","); 132 String[] returnFields = values[0].replaceAll("select", "").trim().split(",");
132 String geometryColumnName = "N/N"; 133 String geometryColumnName = "N/N";
134 String byClause = null;
135 int byClausePos = where.indexOf("group by"); // TODO: Reihenfolge signifikant?
136 if (byClausePos < 0){
137 byClausePos = where.indexOf("order by");
138 }
139 if (byClausePos > 0){
140 byClause = where.substring(byClausePos);
141 where = where.substring(0,byClausePos);
142 }
143
133 for (int i = 0; i < returnFields.length; i++){ 144 for (int i = 0; i < returnFields.length; i++){
134 returnFields[i] = returnFields[i].trim(); 145 returnFields[i] = returnFields[i].trim();
135 if (returnFields[i].startsWith("st_astext(")){ 146 if (returnFields[i].startsWith("st_astext(")){
136 returnFields[i] = returnFields[i].replaceAll("st_astext", ""); 147 returnFields[i] = returnFields[i].replaceAll("st_astext", "");
137 returnFields[i] = returnFields[i].substring(1, returnFields[i].length()-1); 148 returnFields[i] = returnFields[i].substring(1, returnFields[i].length()-1);
138 geometryColumnName = returnFields[i]; 149 geometryColumnName = returnFields[i];
139 } 150 }
140 } 151 }
152
153 for (int i = 0; i < tableNames.length; i++){
154 tableNames[i] = tableNames[i].trim();
155 }
156
141 Geometry g = null; 157 Geometry g = null;
142 int pos = where.indexOf("intersects"); 158 int pos = where.indexOf("intersects");
143 if (pos > 0 ){ 159 if (pos > 0 ){
144 String substr = where.substring(pos); 160 String substr = where.substring(pos);
145 where = where.substring(0,where.lastIndexOf("and")); // TODO auch or unterstützen 161 where = where.substring(0,where.lastIndexOf("and")); // TODO auch or unterstützen
146 String intersectsStmt = substr.substring(0, substr.lastIndexOf(")")); // Annahme räumliches Stmt steht am Ende 162 String intersectsStmt = substr.substring(0, substr.lastIndexOf(")")); // Annahme räumliches Stmt steht am Ende
147 String wkt = intersectsStmt.substring(intersectsStmt.indexOf("\""), intersectsStmt.lastIndexOf("\"")).replace("\"", "").trim(); 163 String wkt = intersectsStmt.substring(intersectsStmt.indexOf("\""), intersectsStmt.lastIndexOf("\"")).replace("\"", "").trim();
148 g = new WKTReader().read(wkt); 164 g = new WKTReader().read(wkt);
149 165
150 } 166 }
151 return this.executeQuery(this.connection.getSeConnection(), layerName, geometryColumnName, where, g, returnFields); 167 return this.executeQuery(this.connection.getSeConnection(), tableNames, geometryColumnName, where, g, returnFields,byClause);
152 }else{ 168 }else{
153 query = new SeQuery(this.connection.getSeConnection()); 169 query = new SeQuery(this.connection.getSeConnection());
154 query.prepareSql(statement); 170 query.prepareSql(statement);
155 query.execute(); 171 query.execute();
156 return this.handleResultSet(query); 172 return this.handleResultSet(query);
162 } 178 }
163 179
164 180
165 } 181 }
166 182
167 private ResultSet executeQuery(SeConnection con, String pLayername, 183 private ResultSet executeQuery(SeConnection con, String[] pLayername,
168 String pSpatialColumnName, String pWhere, 184 String pSpatialColumnName, String pWhere,
169 Geometry g, String[] pReturnFields) 185 Geometry g, String[] pReturnFields, String byClause)
170 throws SQLException { 186 throws SQLException {
171 log.debug("executeQuery()"); 187 log.debug("executeQuery()");
172 try { 188 try {
173 // get the layer for querying 189 // get the layer for querying
174 SeLayer lLayer = new SeLayer(con, pLayername, pSpatialColumnName); 190
175 SeShapeFilter[] filters = null; 191 SeShapeFilter[] filters = null;
176 if (g != null){ 192 if (g != null){
193 SeLayer lLayer = new SeLayer(con, pLayername[0], pSpatialColumnName);
177 SeShape shape = new SeShape(); 194 SeShape shape = new SeShape();
178 shape.setCoordRef(lLayer.getCoordRef()); 195 shape.setCoordRef(lLayer.getCoordRef());
179 196
180 SDEPoint[] lPoints = new ArcSDEUtils().createPoints(g); 197 SDEPoint[] lPoints = new ArcSDEUtils().createPoints(g);
181 198
182 shape.generatePolygon(lPoints.length, 1, null, lPoints); 199 shape.generatePolygon(lPoints.length, 1, null, lPoints);
183 SeShapeFilter filter = new SeShapeFilter(pLayername, 200 SeShapeFilter filter = new SeShapeFilter(pLayername[0],
184 pSpatialColumnName, shape, SeFilter.METHOD_AI); 201 pSpatialColumnName, shape, SeFilter.METHOD_AI);
185 filters = new SeShapeFilter[1]; 202 filters = new SeShapeFilter[1];
186 filters[0] = filter; 203 filters[0] = filter;
187 } 204 }
188 205
189 SeQuery spatialQuery = null; 206 SeQuery spatialQuery = null;
190 SeSqlConstruct sqlCons = new SeSqlConstruct(pLayername, pWhere); 207 SeSqlConstruct sqlCons = new SeSqlConstruct(pLayername, pWhere);
191 spatialQuery = new SeQuery(con, pReturnFields, sqlCons); 208 spatialQuery = new SeQuery(con);//, pReturnFields, sqlCons);
192 spatialQuery.prepareQuery(); 209
210 SeQueryInfo queryInfo = new SeQueryInfo();
211 queryInfo.setColumns(pReturnFields);
212
213 if (byClause != null){
214 queryInfo.setByClause(byClause);
215 }
216
217 queryInfo.setConstruct(sqlCons);
218 spatialQuery.prepareQueryInfo(queryInfo);
219
193 /* 220 /*
194 * Set spatial constraints 221 * Set spatial constraints
195 */ 222 */
196 if (filters != null){ 223 if (filters != null){
197 spatialQuery.setSpatialConstraints(SeQuery.SE_OPTIMIZE, false, 224 spatialQuery.setSpatialConstraints(SeQuery.SE_OPTIMIZE, false,
200 spatialQuery.execute(); 227 spatialQuery.execute();
201 228
202 return this.handleResultSet(spatialQuery); 229 return this.handleResultSet(spatialQuery);
203 230
204 } catch (Exception e) { 231 } catch (Exception e) {
205 log.error(e.getMessage(), e); 232 if (e instanceof SeException){
233 ArcSDEUtils.printError((SeException)e);
234 }else{
235 log.error(e.getMessage(), e);
236 }
237
206 throw new SQLException(e.getMessage()); 238 throw new SQLException(e.getMessage());
207 } 239 }
208 } 240 }
209 241
210 /** 242 /**
http://dive4elements.wald.intevation.org