# HG changeset patch # User Tim Englich # Date 1262618529 0 # Node ID fac02bf1c68580f0dbf1d0440cf471400b703dac # Parent f0b6d0e2a0f69cbd52501e04d72292216819743e Added Rasterdatasupport to the ARCSDE-Statement geo-backend/trunk@504 c6561f87-3c4e-4783-a992-168aeb5c3f6f diff -r f0b6d0e2a0f6 -r fac02bf1c685 geo-backend/ChangeLog --- a/geo-backend/ChangeLog Sat Dec 19 14:45:21 2009 +0000 +++ b/geo-backend/ChangeLog Mon Jan 04 15:22:09 2010 +0000 @@ -1,3 +1,10 @@ +2010-01-04 Tim Englich + + * src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEStatement.java (handleResultSet): + Added Rasterdatasupport to the ARCSDESTATEMENT. + Now it is possible to Query the Value of an Pixel like that: + SELECT ST_ASTEXT(RASTER) FROM MEDIAN.TOPO_BS_D_6X10SEC where INTERSECTS(RASTER, "POINT( 12.084721594184593 54.35583351483295)") + 2009-12-19 Sascha L. Teichmann * contrib/sql-cache.xml: Configured disk storage to survive restarts. diff -r f0b6d0e2a0f6 -r fac02bf1c685 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 Sat Dec 19 14:45:21 2009 +0000 +++ b/geo-backend/src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEStatement.java Mon Jan 04 15:22:09 2010 +0000 @@ -8,6 +8,8 @@ import java.sql.SQLException; import java.sql.SQLWarning; import java.sql.Statement; +import java.util.HashMap; +import java.util.Vector; import org.apache.log4j.Logger; @@ -15,10 +17,18 @@ import com.esri.sde.sdk.client.SeColumnDefinition; import com.esri.sde.sdk.client.SeConnection; import com.esri.sde.sdk.client.SeException; +import com.esri.sde.sdk.client.SeExtent; import com.esri.sde.sdk.client.SeFilter; import com.esri.sde.sdk.client.SeLayer; +import com.esri.sde.sdk.client.SeObjectId; import com.esri.sde.sdk.client.SeQuery; import com.esri.sde.sdk.client.SeQueryInfo; +import com.esri.sde.sdk.client.SeRaster; +import com.esri.sde.sdk.client.SeRasterAttr; +import com.esri.sde.sdk.client.SeRasterBand; +import com.esri.sde.sdk.client.SeRasterColumn; +import com.esri.sde.sdk.client.SeRasterConstraint; +import com.esri.sde.sdk.client.SeRasterTile; import com.esri.sde.sdk.client.SeRow; import com.esri.sde.sdk.client.SeShape; import com.esri.sde.sdk.client.SeShapeFilter; @@ -40,6 +50,10 @@ */ private static Logger log = Logger.getLogger(ArcSDEStatement.class); + private static HashMap tiles = new HashMap(); + + private static SeRasterBand rasterBand = null; + private ArcSDEConnection connection = null; /** @@ -159,10 +173,14 @@ Geometry g = null; int pos = where.indexOf("intersects"); - if (pos > 0 ){ + 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 + int andPos = where.lastIndexOf("and"); + if (andPos < 0){ + andPos = 0; + } + where = where.substring(0,andPos); // TODO auch or unterstützen String intersectsStmt = substr.substring(0, substr.lastIndexOf(")")); // Annahme räumliches Stmt steht am Ende String wkt = null; if (intersectsStmt.contains("select")){ @@ -183,12 +201,13 @@ } } + return this.executeQuery(this.connection.getSeConnection(), tableNames, geometryColumnName, where, g, returnFields,byClause); }else{ query = new SeQuery(this.connection.getSeConnection()); query.prepareSql(statement); query.execute(); - return this.handleResultSet(query); + return this.handleResultSet(query,false,null); } } catch (Exception e) { @@ -206,27 +225,30 @@ log.debug("executeQuery()"); try { // get the layer for querying - + boolean isRaster = pSpatialColumnName.equalsIgnoreCase("raster"); + 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); - - int searchMode = SeFilter.METHOD_AI; - if (g instanceof Polygon){ - shape.generatePolygon(lPoints.length, 1, null, lPoints); - }else if (g instanceof Point){ - shape.generatePoint(1, lPoints); - searchMode = SeFilter.METHOD_PC; + if (!isRaster){ + SeLayer lLayer = new SeLayer(con, pLayername[0], pSpatialColumnName); + SeShape shape = new SeShape(); + shape.setCoordRef(lLayer.getCoordRef()); + + SDEPoint[] lPoints = new ArcSDEUtils().createPoints(g); + + int searchMode = SeFilter.METHOD_AI; + if (g instanceof Polygon){ + shape.generatePolygon(lPoints.length, 1, null, lPoints); + }else if (g instanceof Point){ + shape.generatePoint(1, lPoints); + searchMode = SeFilter.METHOD_PC; + } + + SeShapeFilter filter = new SeShapeFilter(pLayername[0], + pSpatialColumnName, shape,searchMode); + filters = new SeShapeFilter[1]; + filters[0] = filter; } - - SeShapeFilter filter = new SeShapeFilter(pLayername[0], - pSpatialColumnName, shape,searchMode); - filters = new SeShapeFilter[1]; - filters[0] = filter; } SeQuery spatialQuery = null; @@ -250,9 +272,12 @@ spatialQuery.setSpatialConstraints(SeQuery.SE_OPTIMIZE, false, filters); } - spatialQuery.execute(); - - return this.handleResultSet(spatialQuery); + + if (!isRaster){ + spatialQuery.execute(); + } + + return this.handleResultSet(spatialQuery,isRaster,g); } catch (Exception e) { if (e instanceof SeException){ @@ -464,26 +489,118 @@ * @return * @throws SeException */ - private ResultSet handleResultSet(SeQuery pSeQuery) throws SeException { + private ResultSet handleResultSet(SeQuery pSeQuery, boolean isRaster, Geometry geometry) throws SeException { log.debug("ArcSDEStatement,handleResultSet()"); SDEResultSet lSet = new SDEResultSet(); - SeRow row; + SeRow row = null;; int lCount; - for (lCount = 0; (row = pSeQuery.fetch()) != null; lCount++) { - // one time execution - if (lCount == 0) { - // analyze cols of result set - SeColumnDefinition[] lCols = row.getColumns(); - for (SeColumnDefinition lCol : lCols) { - lSet.addCol(new ColDefinition(lCol.getName(), lCol.getType()));// notice: esri-types have been copied into colDefinition class! + if (!isRaster){ + for (lCount = 0; (row = pSeQuery.fetch()) != null; lCount++) { + // one time execution + if (lCount == 0) { + // analyze cols of result set + SeColumnDefinition[] lCols = row.getColumns(); + for (SeColumnDefinition lCol : lCols) { + lSet.addCol(new ColDefinition(lCol.getName(), lCol.getType()));// notice: esri-types have been copied into colDefinition class! + } } + short lNumCols = row.getNumColumns(); + Row lBackingRow = new Row(lNumCols); + for (int i = 0; i < lNumCols; i++) { + lBackingRow.addObject(row.getObject(i), i); + } + lSet.addRow(lBackingRow); } - short lNumCols = row.getNumColumns(); - Row lBackingRow = new Row(lNumCols); - for (int i = 0; i < lNumCols; i++) { - lBackingRow.addObject(row.getObject(i), i); + }else{ + try { + + if (rasterBand == null){ + pSeQuery.execute(); + row = pSeQuery.fetch(); + SeRasterAttr attr = row.getRaster(0); + SeRaster raster = attr.getRasterInfo(); + SeRasterBand[] bands = raster.getBands(); + rasterBand = bands[0]; + } + + int height = rasterBand.getBandHeight(); + int width = rasterBand.getBandWidth(); + + SeExtent extent = rasterBand.getExtent(); + log.debug("Extent: "+ extent.getMinX() +" "+extent.getMinY()+" "+extent.getMaxX()+ " "+extent.getMaxY() ); + log.debug("Querygeometry: "+geometry.toText()); + + double x = ((Point)geometry).getX(); + double y = ((Point)geometry).getY(); + boolean isPointInRaster = false; + if ((x >= extent.getMinX() && x <= extent.getMaxX()) && (y >= extent.getMinY() && y <= extent.getMaxY())){ + isPointInRaster = true; + } + + double dxNature = x - extent.getMinX(); + double dyNature = extent.getMaxY() - y;//y-extent.getMinY(); + double widthNature = extent.getMaxX()-extent.getMinX(); + double heightNature = extent.getMaxY()-extent.getMinY(); + + int pixelX = (int)Math.round(dxNature * width / widthNature); + int pixelY = (int)Math.round(dyNature * height/ heightNature); + + log.debug("Rasterdimesion (Pixel) " + width + " / " + height); + log.debug ("Required Pixel: " + pixelX + " / "+ pixelY); + int bandNumber = rasterBand.getBandNumber(); + log.debug("BAND-ID "+rasterBand.getId().longValue()); + int maxLevel = 0; + log.debug("Maxlevel: "+maxLevel); + + if (isPointInRaster){ + int columnIndex = pixelX / 128; + int rowIndex = pixelY / 128; + String tileKey = columnIndex + "/"+ rowIndex; + + double[] tileValues = tiles.get(tileKey); + if (tileValues == null){ + if (row == null){ + pSeQuery.execute(); + row = pSeQuery.fetch(); + } + SeRasterConstraint constraint = new SeRasterConstraint(); + constraint.setLevel(maxLevel); + constraint.setBands(bandNumber); + constraint.setEnvelope(pixelX / 128, pixelY / 128, pixelX / 128, pixelY / 128); + pSeQuery.queryRasterTile(constraint); + SeRasterTile tile = row.getRasterTile(); + + if(tile != null){ + log.debug("BAND-ID Tile "+tile.getBandId().longValue()); + log.debug("Pixel "+ tile.getNumPixels()); + log.debug("Column / Row "+tile.getColumnIndex()+" / "+tile.getRowIndex()); + int localPixelX = pixelX - (tile.getColumnIndex()*128); + int localPixelY = pixelY - (tile.getRowIndex()*128); + int pos = localPixelY*128 +localPixelX; + if (pos < tile.getNumPixels()){ + tileValues = new double[tile.getNumPixels()]; + tileValues = tile.getPixels(tileValues); + this.tiles.put(tileKey, tileValues); + + } + } + }else{ + log.debug("Tile kommt aus dem Cache."); + } + + int localPixelX = pixelX - (columnIndex*128); + int localPixelY = pixelY - (rowIndex*128); + int pos = localPixelY*128 +localPixelX; + lSet.addCol(new ColDefinition("height",ColDefinition.FLOAT64)); + Row lBackingRow = new Row(1); + lBackingRow.addObject(tileValues[pos], 0); + lSet.addRow(lBackingRow); + }else{ + log.debug("The Query doesn't deliver any Information because the Point is located outside the Raster."); + } + } catch (Exception e) { + log.error(e,e); } - lSet.addRow(lBackingRow); } pSeQuery.close(); return lSet;