Mercurial > dive4elements > gnv-client

comparison geo-backend/src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEStatement.java @ 543:fac02bf1c685

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added Rasterdatasupport to the ARCSDE-Statement geo-backend/trunk@504 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Tim Englich <tim.englich@intevation.de>
date Mon, 04 Jan 2010 15:22:09 +0000
parents 0b813ae17173
children 33f93898cbbf
comparison
equal deleted inserted replaced
542:f0b6d0e2a0f6 543:fac02bf1c685
6 import java.sql.Connection; 6 import java.sql.Connection;
7 import java.sql.ResultSet; 7 import java.sql.ResultSet;
8 import java.sql.SQLException; 8 import java.sql.SQLException;
9 import java.sql.SQLWarning; 9 import java.sql.SQLWarning;
10 import java.sql.Statement; 10 import java.sql.Statement;
11 import java.util.HashMap;
12 import java.util.Vector;
11 13
12 import org.apache.log4j.Logger; 14 import org.apache.log4j.Logger;
13 15
14 import com.esri.sde.sdk.client.SDEPoint; 16 import com.esri.sde.sdk.client.SDEPoint;
15 import com.esri.sde.sdk.client.SeColumnDefinition; 17 import com.esri.sde.sdk.client.SeColumnDefinition;
16 import com.esri.sde.sdk.client.SeConnection; 18 import com.esri.sde.sdk.client.SeConnection;
17 import com.esri.sde.sdk.client.SeException; 19 import com.esri.sde.sdk.client.SeException;
20 import com.esri.sde.sdk.client.SeExtent;
18 import com.esri.sde.sdk.client.SeFilter; 21 import com.esri.sde.sdk.client.SeFilter;
19 import com.esri.sde.sdk.client.SeLayer; 22 import com.esri.sde.sdk.client.SeLayer;
23 import com.esri.sde.sdk.client.SeObjectId;
20 import com.esri.sde.sdk.client.SeQuery; 24 import com.esri.sde.sdk.client.SeQuery;
21 import com.esri.sde.sdk.client.SeQueryInfo; 25 import com.esri.sde.sdk.client.SeQueryInfo;
26 import com.esri.sde.sdk.client.SeRaster;
27 import com.esri.sde.sdk.client.SeRasterAttr;
28 import com.esri.sde.sdk.client.SeRasterBand;
29 import com.esri.sde.sdk.client.SeRasterColumn;
30 import com.esri.sde.sdk.client.SeRasterConstraint;
31 import com.esri.sde.sdk.client.SeRasterTile;
22 import com.esri.sde.sdk.client.SeRow; 32 import com.esri.sde.sdk.client.SeRow;
23 import com.esri.sde.sdk.client.SeShape; 33 import com.esri.sde.sdk.client.SeShape;
24 import com.esri.sde.sdk.client.SeShapeFilter; 34 import com.esri.sde.sdk.client.SeShapeFilter;
25 import com.esri.sde.sdk.client.SeSqlConstruct; 35 import com.esri.sde.sdk.client.SeSqlConstruct;
26 import com.vividsolutions.jts.geom.Geometry; 36 import com.vividsolutions.jts.geom.Geometry;
37 47
38 /** 48 /**
39 * the logger, used to log exceptions and additonaly information 49 * the logger, used to log exceptions and additonaly information
40 */ 50 */
41 private static Logger log = Logger.getLogger(ArcSDEStatement.class); 51 private static Logger log = Logger.getLogger(ArcSDEStatement.class);
52
53 private static HashMap<String, double[]> tiles = new HashMap<String, double[]>();
54
55 private static SeRasterBand rasterBand = null;
42 56
43 private ArcSDEConnection connection = null; 57 private ArcSDEConnection connection = null;
44 58
45 /** 59 /**
46 * Constructor 60 * Constructor
157 tableNames[i] = tableNames[i].trim(); 171 tableNames[i] = tableNames[i].trim();
158 } 172 }
159 173
160 Geometry g = null; 174 Geometry g = null;
161 int pos = where.indexOf("intersects"); 175 int pos = where.indexOf("intersects");
162 if (pos > 0 ){ 176 if (pos >= 0 ){
163 String substr = where.substring(pos); 177 String substr = where.substring(pos);
164 where = where.substring(0,where.lastIndexOf("intersects")); // TODO auch or unterstützen 178 where = where.substring(0,where.lastIndexOf("intersects")); // TODO auch or unterstützen
165 where = where.substring(0,where.lastIndexOf("and")); // TODO auch or unterstützen 179 int andPos = where.lastIndexOf("and");
180 if (andPos < 0){
181 andPos = 0;
182 }
183 where = where.substring(0,andPos); // TODO auch or unterstützen
166 String intersectsStmt = substr.substring(0, substr.lastIndexOf(")")); // Annahme räumliches Stmt steht am Ende 184 String intersectsStmt = substr.substring(0, substr.lastIndexOf(")")); // Annahme räumliches Stmt steht am Ende
167 String wkt = null; 185 String wkt = null;
168 if (intersectsStmt.contains("select")){ 186 if (intersectsStmt.contains("select")){
169 // Anstelle eines WKT ist ein InnerSelect zum Bestimmen der Comparatorgeometrie gegeben. 187 // Anstelle eines WKT ist ein InnerSelect zum Bestimmen der Comparatorgeometrie gegeben.
170 String stmt = intersectsStmt.substring(intersectsStmt.indexOf("select"),intersectsStmt.lastIndexOf(")")); 188 String stmt = intersectsStmt.substring(intersectsStmt.indexOf("select"),intersectsStmt.lastIndexOf(")"));
181 if (geometryColumnName == null){ 199 if (geometryColumnName == null){
182 geometryColumnName = "SHAPE"; // TODO dynamisch aus Intersects auslesen. 200 geometryColumnName = "SHAPE"; // TODO dynamisch aus Intersects auslesen.
183 } 201 }
184 202
185 } 203 }
204
186 return this.executeQuery(this.connection.getSeConnection(), tableNames, geometryColumnName, where, g, returnFields,byClause); 205 return this.executeQuery(this.connection.getSeConnection(), tableNames, geometryColumnName, where, g, returnFields,byClause);
187 }else{ 206 }else{
188 query = new SeQuery(this.connection.getSeConnection()); 207 query = new SeQuery(this.connection.getSeConnection());
189 query.prepareSql(statement); 208 query.prepareSql(statement);
190 query.execute(); 209 query.execute();
191 return this.handleResultSet(query); 210 return this.handleResultSet(query,false,null);
192 } 211 }
193 212
194 } catch (Exception e) { 213 } catch (Exception e) {
195 log.error(e,e); 214 log.error(e,e);
196 throw new SQLException(e.getMessage()); 215 throw new SQLException(e.getMessage());
204 Geometry g, String[] pReturnFields, String byClause) 223 Geometry g, String[] pReturnFields, String byClause)
205 throws SQLException { 224 throws SQLException {
206 log.debug("executeQuery()"); 225 log.debug("executeQuery()");
207 try { 226 try {
208 // get the layer for querying 227 // get the layer for querying
209 228 boolean isRaster = pSpatialColumnName.equalsIgnoreCase("raster");
229
210 SeShapeFilter[] filters = null; 230 SeShapeFilter[] filters = null;
211 if (g != null){ 231 if (g != null){
212 SeLayer lLayer = new SeLayer(con, pLayername[0], pSpatialColumnName); 232 if (!isRaster){
213 SeShape shape = new SeShape(); 233 SeLayer lLayer = new SeLayer(con, pLayername[0], pSpatialColumnName);
214 shape.setCoordRef(lLayer.getCoordRef()); 234 SeShape shape = new SeShape();
215 235 shape.setCoordRef(lLayer.getCoordRef());
216 SDEPoint[] lPoints = new ArcSDEUtils().createPoints(g); 236
217 237 SDEPoint[] lPoints = new ArcSDEUtils().createPoints(g);
218 int searchMode = SeFilter.METHOD_AI; 238
219 if (g instanceof Polygon){ 239 int searchMode = SeFilter.METHOD_AI;
220 shape.generatePolygon(lPoints.length, 1, null, lPoints); 240 if (g instanceof Polygon){
221 }else if (g instanceof Point){ 241 shape.generatePolygon(lPoints.length, 1, null, lPoints);
222 shape.generatePoint(1, lPoints); 242 }else if (g instanceof Point){
223 searchMode = SeFilter.METHOD_PC; 243 shape.generatePoint(1, lPoints);
244 searchMode = SeFilter.METHOD_PC;
245 }
246
247 SeShapeFilter filter = new SeShapeFilter(pLayername[0],
248 pSpatialColumnName, shape,searchMode);
249 filters = new SeShapeFilter[1];
250 filters[0] = filter;
224 } 251 }
225
226 SeShapeFilter filter = new SeShapeFilter(pLayername[0],
227 pSpatialColumnName, shape,searchMode);
228 filters = new SeShapeFilter[1];
229 filters[0] = filter;
230 } 252 }
231 253
232 SeQuery spatialQuery = null; 254 SeQuery spatialQuery = null;
233 SeSqlConstruct sqlCons = new SeSqlConstruct(pLayername, pWhere); 255 SeSqlConstruct sqlCons = new SeSqlConstruct(pLayername, pWhere);
234 spatialQuery = new SeQuery(con);//, pReturnFields, sqlCons); 256 spatialQuery = new SeQuery(con);//, pReturnFields, sqlCons);
248 */ 270 */
249 if (filters != null){ 271 if (filters != null){
250 spatialQuery.setSpatialConstraints(SeQuery.SE_OPTIMIZE, false, 272 spatialQuery.setSpatialConstraints(SeQuery.SE_OPTIMIZE, false,
251 filters); 273 filters);
252 } 274 }
253 spatialQuery.execute(); 275
254 276 if (!isRaster){
255 return this.handleResultSet(spatialQuery); 277 spatialQuery.execute();
278 }
279
280 return this.handleResultSet(spatialQuery,isRaster,g);
256 281
257 } catch (Exception e) { 282 } catch (Exception e) {
258 if (e instanceof SeException){ 283 if (e instanceof SeException){
259 ArcSDEUtils.printError((SeException)e); 284 ArcSDEUtils.printError((SeException)e);
260 }else{ 285 }else{
462 * Copied from de.intevation.gnv.geobackend.sde.datasources.SDEQuery 487 * Copied from de.intevation.gnv.geobackend.sde.datasources.SDEQuery
463 * @param pSeQuery 488 * @param pSeQuery
464 * @return 489 * @return
465 * @throws SeException 490 * @throws SeException
466 */ 491 */
467 private ResultSet handleResultSet(SeQuery pSeQuery) throws SeException { 492 private ResultSet handleResultSet(SeQuery pSeQuery, boolean isRaster, Geometry geometry) throws SeException {
468 log.debug("ArcSDEStatement,handleResultSet()"); 493 log.debug("ArcSDEStatement,handleResultSet()");
469 SDEResultSet lSet = new SDEResultSet(); 494 SDEResultSet lSet = new SDEResultSet();
470 SeRow row; 495 SeRow row = null;;
471 int lCount; 496 int lCount;
472 for (lCount = 0; (row = pSeQuery.fetch()) != null; lCount++) { 497 if (!isRaster){
473 // one time execution 498 for (lCount = 0; (row = pSeQuery.fetch()) != null; lCount++) {
474 if (lCount == 0) { 499 // one time execution
475 // analyze cols of result set 500 if (lCount == 0) {
476 SeColumnDefinition[] lCols = row.getColumns(); 501 // analyze cols of result set
477 for (SeColumnDefinition lCol : lCols) { 502 SeColumnDefinition[] lCols = row.getColumns();
478 lSet.addCol(new ColDefinition(lCol.getName(), lCol.getType()));// notice: esri-types have been copied into colDefinition class! 503 for (SeColumnDefinition lCol : lCols) {
504 lSet.addCol(new ColDefinition(lCol.getName(), lCol.getType()));// notice: esri-types have been copied into colDefinition class!
505 }
479 } 506 }
507 short lNumCols = row.getNumColumns();
508 Row lBackingRow = new Row(lNumCols);
509 for (int i = 0; i < lNumCols; i++) {
510 lBackingRow.addObject(row.getObject(i), i);
511 }
512 lSet.addRow(lBackingRow);
480 } 513 }
481 short lNumCols = row.getNumColumns(); 514 }else{
482 Row lBackingRow = new Row(lNumCols); 515 try {
483 for (int i = 0; i < lNumCols; i++) { 516
484 lBackingRow.addObject(row.getObject(i), i); 517 if (rasterBand == null){
518 pSeQuery.execute();
519 row = pSeQuery.fetch();
520 SeRasterAttr attr = row.getRaster(0);
521 SeRaster raster = attr.getRasterInfo();
522 SeRasterBand[] bands = raster.getBands();
523 rasterBand = bands[0];
524 }
525
526 int height = rasterBand.getBandHeight();
527 int width = rasterBand.getBandWidth();
528
529 SeExtent extent = rasterBand.getExtent();
530 log.debug("Extent: "+ extent.getMinX() +" "+extent.getMinY()+" "+extent.getMaxX()+ " "+extent.getMaxY() );
531 log.debug("Querygeometry: "+geometry.toText());
532
533 double x = ((Point)geometry).getX();
534 double y = ((Point)geometry).getY();
535 boolean isPointInRaster = false;
536 if ((x >= extent.getMinX() && x <= extent.getMaxX()) && (y >= extent.getMinY() && y <= extent.getMaxY())){
537 isPointInRaster = true;
538 }
539
540 double dxNature = x - extent.getMinX();
541 double dyNature = extent.getMaxY() - y;//y-extent.getMinY();
542 double widthNature = extent.getMaxX()-extent.getMinX();
543 double heightNature = extent.getMaxY()-extent.getMinY();
544
545 int pixelX = (int)Math.round(dxNature * width / widthNature);
546 int pixelY = (int)Math.round(dyNature * height/ heightNature);
547
548 log.debug("Rasterdimesion (Pixel) " + width + " / " + height);
549 log.debug ("Required Pixel: " + pixelX + " / "+ pixelY);
550 int bandNumber = rasterBand.getBandNumber();
551 log.debug("BAND-ID "+rasterBand.getId().longValue());
552 int maxLevel = 0;
553 log.debug("Maxlevel: "+maxLevel);
554
555 if (isPointInRaster){
556 int columnIndex = pixelX / 128;
557 int rowIndex = pixelY / 128;
558 String tileKey = columnIndex + "/"+ rowIndex;
559
560 double[] tileValues = tiles.get(tileKey);
561 if (tileValues == null){
562 if (row == null){
563 pSeQuery.execute();
564 row = pSeQuery.fetch();
565 }
566 SeRasterConstraint constraint = new SeRasterConstraint();
567 constraint.setLevel(maxLevel);
568 constraint.setBands(bandNumber);
569 constraint.setEnvelope(pixelX / 128, pixelY / 128, pixelX / 128, pixelY / 128);
570 pSeQuery.queryRasterTile(constraint);
571 SeRasterTile tile = row.getRasterTile();
572
573 if(tile != null){
574 log.debug("BAND-ID Tile "+tile.getBandId().longValue());
575 log.debug("Pixel "+ tile.getNumPixels());
576 log.debug("Column / Row "+tile.getColumnIndex()+" / "+tile.getRowIndex());
577 int localPixelX = pixelX - (tile.getColumnIndex()*128);
578 int localPixelY = pixelY - (tile.getRowIndex()*128);
579 int pos = localPixelY*128 +localPixelX;
580 if (pos < tile.getNumPixels()){
581 tileValues = new double[tile.getNumPixels()];
582 tileValues = tile.getPixels(tileValues);
583 this.tiles.put(tileKey, tileValues);
584
585 }
586 }
587 }else{
588 log.debug("Tile kommt aus dem Cache.");
589 }
590
591 int localPixelX = pixelX - (columnIndex*128);
592 int localPixelY = pixelY - (rowIndex*128);
593 int pos = localPixelY*128 +localPixelX;
594 lSet.addCol(new ColDefinition("height",ColDefinition.FLOAT64));
595 Row lBackingRow = new Row(1);
596 lBackingRow.addObject(tileValues[pos], 0);
597 lSet.addRow(lBackingRow);
598 }else{
599 log.debug("The Query doesn't deliver any Information because the Point is located outside the Raster.");
600 }
601 } catch (Exception e) {
602 log.error(e,e);
485 } 603 }
486 lSet.addRow(lBackingRow);
487 } 604 }
488 pSeQuery.close(); 605 pSeQuery.close();
489 return lSet; 606 return lSet;
490 } 607 }
491 608
http://dive4elements.wald.intevation.org