tim@130: /** tim@130: * tim@130: */ tim@266: package de.intevation.gnv.geobackend.sde.datasources; tim@130: tim@130: import java.sql.Connection; tim@130: import java.sql.ResultSet; tim@130: import java.sql.SQLException; tim@130: import java.sql.SQLWarning; tim@130: import java.sql.Statement; tim@130: tim@130: import org.apache.log4j.Logger; tim@130: tim@263: import com.esri.sde.sdk.client.SDEPoint; tim@130: import com.esri.sde.sdk.client.SeColumnDefinition; tim@263: import com.esri.sde.sdk.client.SeConnection; tim@130: import com.esri.sde.sdk.client.SeException; tim@543: import com.esri.sde.sdk.client.SeExtent; tim@263: import com.esri.sde.sdk.client.SeFilter; tim@263: import com.esri.sde.sdk.client.SeLayer; tim@130: import com.esri.sde.sdk.client.SeQuery; tim@267: import com.esri.sde.sdk.client.SeQueryInfo; tim@543: import com.esri.sde.sdk.client.SeRaster; tim@543: import com.esri.sde.sdk.client.SeRasterAttr; tim@543: import com.esri.sde.sdk.client.SeRasterBand; tim@543: import com.esri.sde.sdk.client.SeRasterConstraint; tim@543: import com.esri.sde.sdk.client.SeRasterTile; tim@130: import com.esri.sde.sdk.client.SeRow; tim@263: import com.esri.sde.sdk.client.SeShape; tim@263: import com.esri.sde.sdk.client.SeShapeFilter; tim@263: import com.esri.sde.sdk.client.SeSqlConstruct; tim@544: import com.vividsolutions.jts.geom.Coordinate; tim@544: import com.vividsolutions.jts.geom.Envelope; tim@263: import com.vividsolutions.jts.geom.Geometry; tim@383: import com.vividsolutions.jts.geom.Point; tim@383: import com.vividsolutions.jts.geom.Polygon; tim@263: import com.vividsolutions.jts.io.WKTReader; tim@263: tim@130: tim@130: /** tim@130: * @author Tim Englich tim@130: * tim@130: */ tim@130: public class ArcSDEStatement implements Statement { tim@130: tim@130: /** tim@130: * the logger, used to log exceptions and additonaly information tim@130: */ tim@130: private static Logger log = Logger.getLogger(ArcSDEStatement.class); tim@130: tim@130: private ArcSDEConnection connection = null; tim@130: tim@130: /** tim@130: * Constructor tim@130: */ tim@130: public ArcSDEStatement(ArcSDEConnection connection) { tim@130: this.connection = connection; tim@130: } tim@130: tim@130: /** tim@130: * @see java.sql.Statement#addBatch(java.lang.String) tim@130: */ tim@130: public void addBatch(String arg0) throws SQLException { tim@130: } tim@130: tim@130: /** tim@130: * @see java.sql.Statement#cancel() tim@130: */ tim@130: public void cancel() throws SQLException { tim@130: } tim@130: tim@130: /** tim@130: * @see java.sql.Statement#clearBatch() tim@130: */ tim@130: public void clearBatch() throws SQLException { tim@130: } tim@130: tim@130: /** tim@130: * @see java.sql.Statement#clearWarnings() tim@130: */ tim@130: public void clearWarnings() throws SQLException { tim@130: } tim@130: tim@130: /** tim@130: * @see java.sql.Statement#close() tim@130: */ tim@130: public void close() throws SQLException { tim@130: } tim@130: tim@130: /** tim@130: * @see java.sql.Statement#execute(java.lang.String) tim@130: */ tim@130: public boolean execute(String arg0) throws SQLException { tim@130: tim@130: return false; tim@130: } tim@130: tim@130: /** tim@130: * @see java.sql.Statement#execute(java.lang.String, int) tim@130: */ tim@130: public boolean execute(String arg0, int arg1) throws SQLException { tim@130: tim@130: return false; tim@130: } tim@130: tim@130: /** tim@130: * @see java.sql.Statement#execute(java.lang.String, int[]) tim@130: */ tim@130: public boolean execute(String arg0, int[] arg1) throws SQLException { tim@130: tim@130: return false; tim@130: } tim@130: tim@130: /** tim@130: * @see java.sql.Statement#execute(java.lang.String, java.lang.String[]) tim@130: */ tim@130: public boolean execute(String arg0, String[] arg1) throws SQLException { tim@130: tim@130: return false; tim@130: } tim@130: tim@130: /** tim@130: * @see java.sql.Statement#executeBatch() tim@130: */ tim@130: public int[] executeBatch() throws SQLException { tim@130: tim@130: return null; tim@130: } tim@130: tim@130: /** tim@130: * @see java.sql.Statement#executeQuery(java.lang.String) tim@130: */ tim@130: public ResultSet executeQuery(String statement) throws SQLException { tim@130: try { tim@263: SeQuery query = null; tim@275: if (statement.toLowerCase().contains("st_astext") || statement.toLowerCase().contains("intersects")){ tim@263: tim@263: String[] values = statement.toLowerCase().split("where", 2); tim@263: String where = values.length > 1 ? values[1].trim() : ""; tim@277: String[] tableNames = values[0].substring(values[0].indexOf("from")).replaceFirst("from", "").toUpperCase().trim().split(", "); tim@277: String columnValueString = values[0].substring(0, values[0].indexOf("from")).trim(); tim@277: columnValueString = columnValueString.replaceFirst("select", "").trim(); tim@377: String[] returnFields = columnValueString.split(", "); tim@275: String geometryColumnName = null; tim@267: String byClause = null; tim@274: int byClausePos = where.indexOf("group by"); tim@267: if (byClausePos < 0){ tim@267: byClausePos = where.indexOf("order by"); tim@267: } tim@267: if (byClausePos > 0){ tim@267: byClause = where.substring(byClausePos); tim@267: where = where.substring(0,byClausePos); tim@267: } tim@267: tim@263: for (int i = 0; i < returnFields.length; i++){ tim@263: returnFields[i] = returnFields[i].trim(); tim@263: if (returnFields[i].startsWith("st_astext(")){ tim@263: returnFields[i] = returnFields[i].replaceAll("st_astext", ""); tim@263: returnFields[i] = returnFields[i].substring(1, returnFields[i].length()-1); tim@263: geometryColumnName = returnFields[i]; tim@263: } tim@263: } tim@267: tim@267: for (int i = 0; i < tableNames.length; i++){ tim@267: tableNames[i] = tableNames[i].trim(); tim@267: } tim@267: tim@263: Geometry g = null; tim@263: int pos = where.indexOf("intersects"); tim@543: if (pos >= 0 ){ tim@263: String substr = where.substring(pos); tim@546: where = where.substring(0,where.lastIndexOf("intersects")); tim@543: int andPos = where.lastIndexOf("and"); tim@543: if (andPos < 0){ tim@543: andPos = 0; tim@543: } tim@546: where = where.substring(0,andPos); // TODO support or also tim@546: String intersectsStmt = substr.substring(0, substr.lastIndexOf(")")); // Spatial-Statment is the last one tim@275: String wkt = null; tim@275: if (intersectsStmt.contains("select")){ tim@275: // Anstelle eines WKT ist ein InnerSelect zum Bestimmen der Comparatorgeometrie gegeben. tim@275: String stmt = intersectsStmt.substring(intersectsStmt.indexOf("select"),intersectsStmt.lastIndexOf(")")); tim@275: ResultSet resultSet = this.executeQuery(stmt); tim@275: if (resultSet != null && resultSet.next()){ tim@546: // TODO: Support the selection of more than one Geometry tim@275: wkt = resultSet.getString("SHAPE"); tim@275: } tim@275: }else{ tim@275: wkt = intersectsStmt.substring(intersectsStmt.indexOf("\""), intersectsStmt.lastIndexOf("\"")).replace("\"", "").trim(); tim@275: } tim@383: g = new WKTReader().read(wkt.toUpperCase()); tim@263: tim@275: if (geometryColumnName == null){ tim@275: geometryColumnName = "SHAPE"; // TODO dynamisch aus Intersects auslesen. tim@275: } tim@275: tim@263: } tim@543: tim@267: return this.executeQuery(this.connection.getSeConnection(), tableNames, geometryColumnName, where, g, returnFields,byClause); tim@263: }else{ tim@263: query = new SeQuery(this.connection.getSeConnection()); tim@263: query.prepareSql(statement); tim@263: query.execute(); tim@543: return this.handleResultSet(query,false,null); tim@263: } tim@263: tim@263: } catch (Exception e) { tim@130: log.error(e,e); tim@130: throw new SQLException(e.getMessage()); tim@130: } tim@130: tim@130: tim@130: } tim@263: tim@267: private ResultSet executeQuery(SeConnection con, String[] pLayername, tim@263: String pSpatialColumnName, String pWhere, tim@267: Geometry g, String[] pReturnFields, String byClause) tim@263: throws SQLException { tim@263: log.debug("executeQuery()"); tim@263: try { tim@263: // get the layer for querying tim@543: boolean isRaster = pSpatialColumnName.equalsIgnoreCase("raster"); tim@543: tim@266: SeShapeFilter[] filters = null; tim@266: if (g != null){ tim@543: if (!isRaster){ tim@543: SeLayer lLayer = new SeLayer(con, pLayername[0], pSpatialColumnName); tim@543: SeShape shape = new SeShape(); tim@543: shape.setCoordRef(lLayer.getCoordRef()); tim@543: tim@543: SDEPoint[] lPoints = new ArcSDEUtils().createPoints(g); tim@543: tim@543: int searchMode = SeFilter.METHOD_AI; tim@543: if (g instanceof Polygon){ tim@543: shape.generatePolygon(lPoints.length, 1, null, lPoints); tim@543: }else if (g instanceof Point){ tim@543: shape.generatePoint(1, lPoints); tim@543: searchMode = SeFilter.METHOD_PC; tim@543: } tim@543: tim@543: SeShapeFilter filter = new SeShapeFilter(pLayername[0], tim@543: pSpatialColumnName, shape,searchMode); tim@543: filters = new SeShapeFilter[1]; tim@543: filters[0] = filter; tim@383: } tim@266: } tim@263: tim@263: SeQuery spatialQuery = null; tim@263: SeSqlConstruct sqlCons = new SeSqlConstruct(pLayername, pWhere); tim@267: spatialQuery = new SeQuery(con);//, pReturnFields, sqlCons); tim@267: tim@267: SeQueryInfo queryInfo = new SeQueryInfo(); tim@267: queryInfo.setColumns(pReturnFields); tim@267: tim@267: if (byClause != null){ tim@267: queryInfo.setByClause(byClause); tim@267: } tim@267: tim@267: queryInfo.setConstruct(sqlCons); tim@267: spatialQuery.prepareQueryInfo(queryInfo); tim@267: tim@263: /* tim@263: * Set spatial constraints tim@263: */ tim@266: if (filters != null){ tim@266: spatialQuery.setSpatialConstraints(SeQuery.SE_OPTIMIZE, false, tim@266: filters); tim@266: } tim@543: tim@543: if (!isRaster){ tim@543: spatialQuery.execute(); tim@543: } tim@543: tim@543: return this.handleResultSet(spatialQuery,isRaster,g); tim@263: tim@263: } catch (Exception e) { tim@267: if (e instanceof SeException){ tim@267: ArcSDEUtils.printError((SeException)e); tim@267: }else{ tim@267: log.error(e.getMessage(), e); tim@267: } tim@267: tim@263: throw new SQLException(e.getMessage()); tim@263: } tim@263: } tim@130: tim@130: /** tim@130: * @see java.sql.Statement#executeUpdate(java.lang.String) tim@130: */ tim@130: public int executeUpdate(String arg0) throws SQLException { tim@130: tim@130: return 0; tim@130: } tim@130: tim@130: /** tim@130: * @see java.sql.Statement#executeUpdate(java.lang.String, int) tim@130: */ tim@130: public int executeUpdate(String arg0, int arg1) throws SQLException { tim@130: tim@130: return 0; tim@130: } tim@130: tim@130: /** tim@130: * @see java.sql.Statement#executeUpdate(java.lang.String, int[]) tim@130: */ tim@130: public int executeUpdate(String arg0, int[] arg1) throws SQLException { tim@130: tim@130: return 0; tim@130: } tim@130: tim@130: /** tim@130: * @see java.sql.Statement#executeUpdate(java.lang.String, java.lang.String[]) tim@130: */ tim@130: public int executeUpdate(String arg0, String[] arg1) throws SQLException { tim@130: tim@130: return 0; tim@130: } tim@130: tim@130: /** tim@130: * @see java.sql.Statement#getConnection() tim@130: */ tim@130: public Connection getConnection() throws SQLException { tim@130: return this.connection; tim@130: } tim@130: tim@130: /** tim@130: * @see java.sql.Statement#getFetchDirection() tim@130: */ tim@130: public int getFetchDirection() throws SQLException { tim@130: tim@130: return 0; tim@130: } tim@130: tim@130: /** tim@130: * @see java.sql.Statement#getFetchSize() tim@130: */ tim@130: public int getFetchSize() throws SQLException { tim@130: tim@130: return 0; tim@130: } tim@130: tim@130: /** tim@130: * @see java.sql.Statement#getGeneratedKeys() tim@130: */ tim@130: public ResultSet getGeneratedKeys() throws SQLException { tim@130: tim@130: return null; tim@130: } tim@130: tim@130: /** tim@130: * @see java.sql.Statement#getMaxFieldSize() tim@130: */ tim@130: public int getMaxFieldSize() throws SQLException { tim@130: tim@130: return 0; tim@130: } tim@130: tim@130: /** tim@130: * @see java.sql.Statement#getMaxRows() tim@130: */ tim@130: public int getMaxRows() throws SQLException { tim@130: tim@130: return 0; tim@130: } tim@130: tim@130: /** tim@130: * @see java.sql.Statement#getMoreResults() tim@130: */ tim@130: public boolean getMoreResults() throws SQLException { tim@130: tim@130: return false; tim@130: } tim@130: tim@130: /** tim@130: * @see java.sql.Statement#getMoreResults(int) tim@130: */ tim@130: public boolean getMoreResults(int arg0) throws SQLException { tim@130: tim@130: return false; tim@130: } tim@130: tim@130: /** tim@130: * @see java.sql.Statement#getQueryTimeout() tim@130: */ tim@130: public int getQueryTimeout() throws SQLException { tim@130: tim@130: return 0; tim@130: } tim@130: tim@130: /** tim@130: * @see java.sql.Statement#getResultSet() tim@130: */ tim@130: public ResultSet getResultSet() throws SQLException { tim@130: tim@130: return null; tim@130: } tim@130: tim@130: /** tim@130: * @see java.sql.Statement#getResultSetConcurrency() tim@130: */ tim@130: public int getResultSetConcurrency() throws SQLException { tim@130: tim@130: return 0; tim@130: } tim@130: tim@130: /** tim@130: * @see java.sql.Statement#getResultSetHoldability() tim@130: */ tim@130: public int getResultSetHoldability() throws SQLException { tim@130: tim@130: return 0; tim@130: } tim@130: tim@130: /** tim@130: * @see java.sql.Statement#getResultSetType() tim@130: */ tim@130: public int getResultSetType() throws SQLException { tim@130: tim@130: return 0; tim@130: } tim@130: tim@130: /** tim@130: * @see java.sql.Statement#getUpdateCount() tim@130: */ tim@130: public int getUpdateCount() throws SQLException { tim@130: tim@130: return 0; tim@130: } tim@130: tim@130: /** tim@130: * @see java.sql.Statement#getWarnings() tim@130: */ tim@130: public SQLWarning getWarnings() throws SQLException { tim@130: tim@130: return null; tim@130: } tim@130: tim@130: /** tim@130: * @see java.sql.Statement#setCursorName(java.lang.String) tim@130: */ tim@130: public void setCursorName(String arg0) throws SQLException { tim@130: } tim@130: tim@130: /** tim@130: * @see java.sql.Statement#setEscapeProcessing(boolean) tim@130: */ tim@130: public void setEscapeProcessing(boolean arg0) throws SQLException { tim@130: } tim@130: tim@130: /** tim@130: * @see java.sql.Statement#setFetchDirection(int) tim@130: */ tim@130: public void setFetchDirection(int arg0) throws SQLException { tim@130: } tim@130: tim@130: /** tim@130: * @see java.sql.Statement#setFetchSize(int) tim@130: */ tim@130: public void setFetchSize(int arg0) throws SQLException { tim@130: } tim@130: tim@130: /** tim@130: * @see java.sql.Statement#setMaxFieldSize(int) tim@130: */ tim@130: public void setMaxFieldSize(int arg0) throws SQLException { tim@130: } tim@130: tim@130: /** tim@130: * @see java.sql.Statement#setMaxRows(int) tim@130: */ tim@130: public void setMaxRows(int arg0) throws SQLException { tim@130: } tim@130: tim@130: /** tim@130: * @see java.sql.Statement#setQueryTimeout(int) tim@130: */ tim@130: public void setQueryTimeout(int arg0) throws SQLException { tim@130: } tim@130: tim@130: /** tim@130: * Copied from de.intevation.gnv.geobackend.sde.datasources.SDEQuery tim@130: * @param pSeQuery tim@130: * @return tim@130: * @throws SeException tim@130: */ tim@543: private ResultSet handleResultSet(SeQuery pSeQuery, boolean isRaster, Geometry geometry) throws SeException { tim@130: log.debug("ArcSDEStatement,handleResultSet()"); tim@130: SDEResultSet lSet = new SDEResultSet(); tim@543: SeRow row = null;; tim@130: int lCount; tim@543: if (!isRaster){ tim@543: for (lCount = 0; (row = pSeQuery.fetch()) != null; lCount++) { tim@543: // one time execution tim@543: if (lCount == 0) { tim@543: // analyze cols of result set tim@543: SeColumnDefinition[] lCols = row.getColumns(); tim@543: for (SeColumnDefinition lCol : lCols) { tim@543: lSet.addCol(new ColDefinition(lCol.getName(), lCol.getType()));// notice: esri-types have been copied into colDefinition class! tim@543: } tim@130: } tim@543: short lNumCols = row.getNumColumns(); tim@543: Row lBackingRow = new Row(lNumCols); tim@543: for (int i = 0; i < lNumCols; i++) { tim@543: lBackingRow.addObject(row.getObject(i), i); tim@543: } tim@543: lSet.addRow(lBackingRow); tim@130: } tim@543: }else{ tim@543: try { tim@543: tim@544: int rasterSize = 128; tim@544: pSeQuery.execute(); tim@544: row = pSeQuery.fetch(); tim@544: SeRasterAttr attr = row.getRaster(0); tim@544: SeRaster raster = attr.getRasterInfo(); tim@544: SeRasterBand[] bands = raster.getBands(); tim@544: SeRasterBand rasterBand = bands[0]; tim@544: tim@543: tim@543: int height = rasterBand.getBandHeight(); tim@543: int width = rasterBand.getBandWidth(); tim@543: tim@543: SeExtent extent = rasterBand.getExtent(); tim@543: log.debug("Extent: "+ extent.getMinX() +" "+extent.getMinY()+" "+extent.getMaxX()+ " "+extent.getMaxY() ); tim@543: log.debug("Querygeometry: "+geometry.toText()); tim@543: tim@543: double x = ((Point)geometry).getX(); tim@543: double y = ((Point)geometry).getY(); tim@543: boolean isPointInRaster = false; tim@543: if ((x >= extent.getMinX() && x <= extent.getMaxX()) && (y >= extent.getMinY() && y <= extent.getMaxY())){ tim@543: isPointInRaster = true; tim@543: } tim@543: tim@543: double dxNature = x - extent.getMinX(); tim@543: double dyNature = extent.getMaxY() - y;//y-extent.getMinY(); tim@543: double widthNature = extent.getMaxX()-extent.getMinX(); tim@543: double heightNature = extent.getMaxY()-extent.getMinY(); tim@543: tim@543: int pixelX = (int)Math.round(dxNature * width / widthNature); tim@543: int pixelY = (int)Math.round(dyNature * height/ heightNature); tim@543: tim@543: log.debug("Rasterdimesion (Pixel) " + width + " / " + height); tim@543: log.debug ("Required Pixel: " + pixelX + " / "+ pixelY); tim@543: int bandNumber = rasterBand.getBandNumber(); tim@543: log.debug("BAND-ID "+rasterBand.getId().longValue()); tim@543: int maxLevel = 0; tim@543: log.debug("Maxlevel: "+maxLevel); tim@543: tim@543: if (isPointInRaster){ tim@544: tim@544: if (row == null){ tim@544: pSeQuery.execute(); tim@544: row = pSeQuery.fetch(); tim@544: } tim@544: SeRasterConstraint constraint = new SeRasterConstraint(); tim@544: constraint.setLevel(maxLevel); tim@544: constraint.setBands(bandNumber); tim@544: constraint.setEnvelope(pixelX / rasterSize, pixelY / rasterSize, pixelX / rasterSize, pixelY / rasterSize); tim@544: pSeQuery.queryRasterTile(constraint); tim@544: SeRasterTile tile = row.getRasterTile(); tim@543: tim@544: if(tile != null){ tim@544: log.debug("BAND-ID Tile "+tile.getBandId().longValue()); tim@544: log.debug("Pixel "+ tile.getNumPixels()); tim@544: log.debug("Column / Row "+tile.getColumnIndex()+" / "+tile.getRowIndex()); tim@544: tim@544: tim@544: double[] tileValues = new double[tile.getNumPixels()]; tim@544: tileValues = tile.getPixels(tileValues); tim@544: lSet.addCol(new ColDefinition("tile",ColDefinition.FLOAT64)); tim@544: Row lBackingRow = new Row(1); tim@544: Envelope envelope = new Envelope( tim@544: new Coordinate(extent.getMinX(), tim@544: extent.getMinY()), tim@544: new Coordinate(extent.getMaxX(), tim@544: extent.getMaxY())); tim@544: RasterObject ro = new RasterObject(envelope, tim@544: tileValues, tim@544: rasterSize, tim@544: tile.getRowIndex(), tim@544: tile.getColumnIndex(), tim@544: width, tim@544: height); tim@544: lBackingRow.addObject(ro,0); tim@544: lSet.addRow(lBackingRow); tim@544: } tim@543: tim@543: }else{ tim@543: log.debug("The Query doesn't deliver any Information because the Point is located outside the Raster."); tim@543: } tim@543: } catch (Exception e) { tim@543: log.error(e,e); tim@130: } tim@130: } tim@130: pSeQuery.close(); tim@130: return lSet; tim@130: } tim@130: tim@380: public boolean isClosed() throws SQLException { tim@380: return false; tim@380: } tim@380: tim@380: public boolean isPoolable() throws SQLException { tim@380: return false; tim@380: } tim@380: tim@380: public void setPoolable(boolean arg0) throws SQLException { tim@380: } tim@380: tim@380: public boolean isWrapperFor(Class iface) throws SQLException { tim@380: return false; tim@380: } tim@380: tim@380: public T unwrap(Class iface) throws SQLException { tim@380: return null; tim@380: } tim@380: tim@130: }