Mercurial > dive4elements > gnv-client
view geo-backend/src/main/java/de/intevation/gnv/geobackend/sde/datasources/ArcSDEStatement.java @ 660:b4c7de93d5e8
Added Suport for the Geometrytyp SimpleLine.
geo-backend/trunk@748 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author | Tim Englich <tim.englich@intevation.de> |
---|---|
date | Mon, 08 Mar 2010 14:06:50 +0000 |
parents | 825781a39c70 |
children | 9bcc423d8d76 |
line wrap: on
line source
package de.intevation.gnv.geobackend.sde.datasources; import com.esri.sde.sdk.client.SDEPoint; 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.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.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; import com.esri.sde.sdk.client.SeSqlConstruct; import com.vividsolutions.jts.geom.Geometry; import com.vividsolutions.jts.geom.Point; import com.vividsolutions.jts.geom.Polygon; import com.vividsolutions.jts.io.WKTReader; import de.intevation.gnv.geobackend.util.RedundancyRemover; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.SQLWarning; import java.sql.Statement; import org.apache.log4j.Logger; /** * @author Tim Englich (tim.englich@intevation.de) * @author Sascha L. Teichmann (sascha.teichmann@intevation.de) */ public class ArcSDEStatement implements Statement { /** * the logger, used to log exceptions and additonaly information */ private static Logger log = Logger.getLogger(ArcSDEStatement.class); private ArcSDEConnection connection = null; /** * Constructor */ public ArcSDEStatement(ArcSDEConnection connection) { this.connection = connection; } /** * @see java.sql.Statement#addBatch(java.lang.String) */ public void addBatch(String arg0) throws SQLException { } /** * @see java.sql.Statement#cancel() */ public void cancel() throws SQLException { } /** * @see java.sql.Statement#clearBatch() */ public void clearBatch() throws SQLException { } /** * @see java.sql.Statement#clearWarnings() */ public void clearWarnings() throws SQLException { } /** * @see java.sql.Statement#close() */ public void close() throws SQLException { } /** * @see java.sql.Statement#execute(java.lang.String) */ public boolean execute(String arg0) throws SQLException { return false; } /** * @see java.sql.Statement#execute(java.lang.String, int) */ public boolean execute(String arg0, int arg1) throws SQLException { return false; } /** * @see java.sql.Statement#execute(java.lang.String, int[]) */ public boolean execute(String arg0, int[] arg1) throws SQLException { return false; } /** * @see java.sql.Statement#execute(java.lang.String, java.lang.String[]) */ public boolean execute(String arg0, String[] arg1) throws SQLException { return false; } /** * @see java.sql.Statement#executeBatch() */ public int[] executeBatch() throws SQLException { return null; } /** * @see java.sql.Statement#executeQuery(java.lang.String) */ public ResultSet executeQuery(String statement) throws SQLException { try { SeQuery query = null; 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() : ""; String[] tableNames = values[0].substring(values[0].indexOf("from")).replaceFirst("from", "").toUpperCase().trim().split(", "); String columnValueString = values[0].substring(0, values[0].indexOf("from")).trim(); columnValueString = columnValueString.replaceFirst("select", "").trim(); String[] returnFields = columnValueString.split(", "); String geometryColumnName = null; String byClause = null; int byClausePos = where.indexOf("group by"); if (byClausePos < 0){ byClausePos = where.indexOf("order by"); } if (byClausePos > 0){ byClause = where.substring(byClausePos); where = where.substring(0,byClausePos); } for (int i = 0; i < returnFields.length; i++){ returnFields[i] = returnFields[i].trim(); if (returnFields[i].startsWith("st_astext(")){ returnFields[i] = returnFields[i].replaceAll("st_astext", ""); returnFields[i] = returnFields[i].substring(1, returnFields[i].length()-1); geometryColumnName = returnFields[i]; } } for (int i = 0; i < tableNames.length; i++){ tableNames[i] = tableNames[i].trim(); } Geometry g = null; int pos = where.indexOf("intersects"); if (pos >= 0 ){ String substr = where.substring(pos); where = where.substring(0,where.lastIndexOf("intersects")); int andPos = where.lastIndexOf("and"); if (andPos < 0){ andPos = 0; } where = where.substring(0,andPos); // TODO support or also String intersectsStmt = substr.substring(0, substr.lastIndexOf(")")); // Spatial-Statment is the last one 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: Support the selection of more than one Geometry wkt = resultSet.getString("SHAPE"); } }else{ wkt = intersectsStmt.substring(intersectsStmt.indexOf("\""), intersectsStmt.lastIndexOf("\"")).replace("\"", "").trim(); } g = new WKTReader().read(wkt.toUpperCase()); if (geometryColumnName == null){ geometryColumnName = "SHAPE"; // TODO dynamisch aus Intersects auslesen. } } 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,false,null); } } catch (Exception e) { log.error(e,e); throw new SQLException(e.getMessage()); } } private ResultSet executeQuery(SeConnection con, String[] pLayername, String pSpatialColumnName, String pWhere, Geometry g, String[] pReturnFields, String byClause) throws SQLException { log.debug("executeQuery()"); try { // get the layer for querying boolean isRaster = pSpatialColumnName.equalsIgnoreCase("raster"); SeShapeFilter[] filters = null; if (g != null){ 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; } } SeQuery spatialQuery = null; SeSqlConstruct sqlCons = new SeSqlConstruct(pLayername, pWhere); spatialQuery = new SeQuery(con);//, pReturnFields, sqlCons); SeQueryInfo queryInfo = new SeQueryInfo(); queryInfo.setColumns(pReturnFields); if (byClause != null){ queryInfo.setByClause(byClause); } queryInfo.setConstruct(sqlCons); spatialQuery.prepareQueryInfo(queryInfo); /* * Set spatial constraints */ if (filters != null){ spatialQuery.setSpatialConstraints(SeQuery.SE_OPTIMIZE, false, filters); } if (!isRaster){ spatialQuery.execute(); } return this.handleResultSet(spatialQuery,isRaster,g); } catch (Exception e) { if (e instanceof SeException){ ArcSDEUtils.printError((SeException)e); }else{ log.error(e.getMessage(), e); } throw new SQLException(e.getMessage()); } } /** * @see java.sql.Statement#executeUpdate(java.lang.String) */ public int executeUpdate(String arg0) throws SQLException { return 0; } /** * @see java.sql.Statement#executeUpdate(java.lang.String, int) */ public int executeUpdate(String arg0, int arg1) throws SQLException { return 0; } /** * @see java.sql.Statement#executeUpdate(java.lang.String, int[]) */ public int executeUpdate(String arg0, int[] arg1) throws SQLException { return 0; } /** * @see java.sql.Statement#executeUpdate(java.lang.String, java.lang.String[]) */ public int executeUpdate(String arg0, String[] arg1) throws SQLException { return 0; } /** * @see java.sql.Statement#getConnection() */ public Connection getConnection() throws SQLException { return this.connection; } /** * @see java.sql.Statement#getFetchDirection() */ public int getFetchDirection() throws SQLException { return 0; } /** * @see java.sql.Statement#getFetchSize() */ public int getFetchSize() throws SQLException { return 0; } /** * @see java.sql.Statement#getGeneratedKeys() */ public ResultSet getGeneratedKeys() throws SQLException { return null; } /** * @see java.sql.Statement#getMaxFieldSize() */ public int getMaxFieldSize() throws SQLException { return 0; } /** * @see java.sql.Statement#getMaxRows() */ public int getMaxRows() throws SQLException { return 0; } /** * @see java.sql.Statement#getMoreResults() */ public boolean getMoreResults() throws SQLException { return false; } /** * @see java.sql.Statement#getMoreResults(int) */ public boolean getMoreResults(int arg0) throws SQLException { return false; } /** * @see java.sql.Statement#getQueryTimeout() */ public int getQueryTimeout() throws SQLException { return 0; } /** * @see java.sql.Statement#getResultSet() */ public ResultSet getResultSet() throws SQLException { return null; } /** * @see java.sql.Statement#getResultSetConcurrency() */ public int getResultSetConcurrency() throws SQLException { return 0; } /** * @see java.sql.Statement#getResultSetHoldability() */ public int getResultSetHoldability() throws SQLException { return 0; } /** * @see java.sql.Statement#getResultSetType() */ public int getResultSetType() throws SQLException { return 0; } /** * @see java.sql.Statement#getUpdateCount() */ public int getUpdateCount() throws SQLException { return 0; } /** * @see java.sql.Statement#getWarnings() */ public SQLWarning getWarnings() throws SQLException { return null; } /** * @see java.sql.Statement#setCursorName(java.lang.String) */ public void setCursorName(String arg0) throws SQLException { } /** * @see java.sql.Statement#setEscapeProcessing(boolean) */ public void setEscapeProcessing(boolean arg0) throws SQLException { } /** * @see java.sql.Statement#setFetchDirection(int) */ public void setFetchDirection(int arg0) throws SQLException { } /** * @see java.sql.Statement#setFetchSize(int) */ public void setFetchSize(int arg0) throws SQLException { } /** * @see java.sql.Statement#setMaxFieldSize(int) */ public void setMaxFieldSize(int arg0) throws SQLException { } /** * @see java.sql.Statement#setMaxRows(int) */ public void setMaxRows(int arg0) throws SQLException { } /** * @see java.sql.Statement#setQueryTimeout(int) */ public void setQueryTimeout(int arg0) throws SQLException { } /** * Copied from de.intevation.gnv.geobackend.sde.datasources.SDEQuery * @param pSeQuery * @return * @throws SeException */ private ResultSet handleResultSet(SeQuery pSeQuery, boolean isRaster, Geometry geometry) throws SeException { boolean debug = log.isDebugEnabled(); if (debug) { log.debug("ArcSDEStatement.handleResultSet()"); } SDEResultSet lSet = new SDEResultSet(); SeRow row = null; int lCount; if (!isRaster){ RedundancyRemover [] removers = null; SeColumnDefinition [] lCols = null; for (lCount = 0; (row = pSeQuery.fetch()) != null; lCount++) { // one time execution if (lCount == 0) { // analyze cols of result set lCols = row.getColumns(); for (SeColumnDefinition lCol : lCols) { lSet.addCol(new ColDefinition(lCol.getName(), lCol.getType())); // notice: esri-types have been copied into colDefinition class! } removers = new RedundancyRemover[lCols.length]; for (int i = 0; i < removers.length; ++i) { removers[i] = new RedundancyRemover(); } } short lNumCols = row.getNumColumns(); Row lBackingRow = new Row(lNumCols); for (int i = 0; i < lNumCols; i++) { lBackingRow.addObject( removers[i].filter(row.getObject(i)), i); } lSet.addRow(lBackingRow); } if (debug && removers != null) { log.debug("datasets: " + lCount); StringBuilder sb = new StringBuilder("removed redundancy: "); float percent = 100f/lCount; for (int i = 0; i < removers.length; ++i) { if (i > 0) sb.append(", "); sb.append(lCols[i].getName()) .append(": ").append(removers[i].numRemoved()) .append(" (").append(removers[i].numRemoved()*percent) .append("%)"); } log.debug(sb.toString()); } }else{ try { pSeQuery.execute(); row = pSeQuery.fetch(); SeRasterAttr attr = row.getRaster(0); SeRaster raster = attr.getRasterInfo(); SeRasterBand [] bands = raster.getBands(); SeRasterBand rasterBand = bands[0]; SeExtent extent = rasterBand.getExtent(); /* if (debug) { log.debug("Extent: " + extent.getMinX() + " " + extent.getMinY() + " "+ extent.getMaxX() + " " + extent.getMaxY()); log.debug("Query geometry: "+geometry.toText()); } */ double x = ((Point)geometry).getX(); double y = ((Point)geometry).getY(); boolean isPointInRaster = x >= extent.getMinX() && x <= extent.getMaxX() && y >= extent.getMinY() && y <= extent.getMaxY(); if (isPointInRaster){ if (row == null){ pSeQuery.execute(); row = pSeQuery.fetch(); } double midX = 0.5d*(extent.getMinX() + extent.getMaxX()); double midY = 0.5d*(extent.getMinY() + extent.getMaxY()); SDEPoint origin = rasterBand.getTileOrigin(); double maxX = origin.getX() < midX ? extent.getMaxX() : extent.getMinX(); double maxY = origin.getY() < midY ? extent.getMaxY() : extent.getMinY(); /* 0 = origin.getX()*mx + bx rasterBand.getBandWidth() = maxX + bx rasterBand.getBandWidth() = (maxX-origin.getX())*mx */ double mx = rasterBand.getBandWidth()/(maxX-origin.getX()); double bx = -origin.getX()*mx; double px = mx*x + bx; double my = rasterBand.getBandHeight()/(maxY-origin.getY()); double by = -origin.getY()*my; double py = my*y + by; /* if (debug) { log.debug("p(x, y): " + px + " / " + py); } */ SeRasterConstraint constraint = new SeRasterConstraint(); constraint.setLevel(0); // best resolution constraint.setBands(rasterBand.getBandNumber()); int tx = (int)Math.floor(px / rasterBand.getTileWidth()); int ty = (int)Math.floor(py / rasterBand.getTileHeight()); constraint.setEnvelope(tx, ty, tx, ty); pSeQuery.queryRasterTile(constraint); SeRasterTile tile = row.getRasterTile(); if (tile != null){ double[] tileValues = new double[tile.getNumPixels()]; tileValues = tile.getPixels(tileValues); lSet.addCol(new ColDefinition("tile", ColDefinition.FLOAT64)); Row lBackingRow = new Row(1); /* 0 = wx1*mt + bt tileWidth-EPS = wx2*mt + bt tileWidth-EPS = mt*(wx2 - wx1) */ double wx1 = (rasterBand.getTileWidth()*tx - bx)/mx; double wx2 = (rasterBand.getTileWidth()*(tx+1) - bx)/mx; double mxt = (rasterBand.getTileWidth()-1e-5d)/(wx2 - wx1); double bxt = -wx1*mxt; double wy1 = (rasterBand.getTileHeight()*ty - by)/my; double wy2 = (rasterBand.getTileHeight()*(ty+1) - by)/my; double myt = (rasterBand.getTileHeight()-1e-5d)/(wy2 - wy1); double byt = -wy1*myt; RasterObject ro = new RasterObject( mxt, bxt, myt, byt, tile.getColumnIndex(), tile.getRowIndex(), tileValues, rasterBand.getTileWidth(), rasterBand.getTileHeight()); lBackingRow.addObject(ro, 0); lSet.addRow(lBackingRow); /* if (debug) { log.debug("x / y: " + x + " / " + y); log.debug("wx1: " + wx1); log.debug("wx1 -> " + (wx1*mxt+bxt)); log.debug("wx2: " + wx2); log.debug("wx2 -> " + (wx2*mxt+bxt)); log.debug("wx2 - wx1: " + Math.abs(wx2-wx1)); log.debug("wy2 - wy1: " + Math.abs(wy2-wy1)); log.debug("pix: " + (x*mxt + bxt)+ " " + (y*myt + byt)); log.debug("requesting tile: " + tx + " / " + ty); log.debug("got tile: " + tile.getColumnIndex() + " / " + tile.getRowIndex()); log.debug("tile orig: " + origin.getX() + " / " + origin.getY()); log.debug("tile width: " + rasterBand.getTileWidth()); log.debug("tile height: " + rasterBand.getTileHeight()); log.debug("Rasterdimesion (Pixel) " + rasterBand.getBandWidth() + " / " + rasterBand.getBandHeight()); log.debug("BAND-ID "+rasterBand.getId().longValue()); log.debug("Pixels: "+ tile.getNumPixels()); log.debug("Column / Row "+tile.getColumnIndex()+" / "+tile.getRowIndex()); } */ } } 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); } } pSeQuery.close(); return lSet; } public boolean isClosed() throws SQLException { return false; } public boolean isPoolable() throws SQLException { return false; } public void setPoolable(boolean arg0) throws SQLException { } public boolean isWrapperFor(Class<?> iface) throws SQLException { return false; } public <T> T unwrap(Class<T> iface) throws SQLException { return null; } }