/* * Geopaparazzi - Digital field mapping on Android based devices * Copyright (C) 2010 HydroloGIS (www.hydrologis.com) * * This program is free software: you can redistribute it and/or modify * it under the terms of the GNU General Public License as published by * the Free Software Foundation, either version 3 of the License, or * (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public License * along with this program. If not, see <http://www.gnu.org/licenses/>. */ package eu.geopaparazzi.spatialite.database.spatial.core; import java.io.File; import java.util.ArrayList; import java.util.Collections; import java.util.HashMap; import java.util.List; import jsqlite.Database; import jsqlite.Exception; import jsqlite.Stmt; import android.graphics.Color; import android.graphics.Paint; import android.graphics.Paint.Cap; import android.graphics.Paint.Join; import com.vividsolutions.jts.geom.Coordinate; import com.vividsolutions.jts.geom.Geometry; import com.vividsolutions.jts.io.WKBReader; /** * An utility class to handle the spatial database. * * @author Andrea Antonello (www.hydrologis.com) */ public class SpatialiteDatabaseHandler implements ISpatialDatabaseHandler { // 3857 // private GeometryFactory gf = new GeometryFactory(); // private WKBWriter wr = new WKBWriter(); // private WKBReader wkbReader = new WKBReader(gf); private static final String METADATA_TABLE_GEOPACKAGE_CONTENTS = "geopackage_contents"; private static final String METADATA_TABLE_TILE_MATRIX = "tile_matrix_metadata"; private static final String METADATA_TABLE_RASTER_COLUMNS = "raster_columns"; private static final String METADATA_TABLE_GEOMETRY_COLUMNS = "geometry_columns"; private static final String METADATA_GEOPACKAGECONTENT_TABLE_NAME = "table_name"; private static final String METADATA_TILE_TABLE_NAME = "t_table_name"; private static final String METADATA_ZOOM_LEVEL = "zoom_level"; private static final String METADATA_RASTER_COLUMN = "r_raster_column"; private static final String METADATA_RASTER_TABLE_NAME = "r_table_name"; private static final String METADATA_SRID = "srid"; private static final String METADATA_GEOMETRY_TYPE4 = "geometry_type"; private static final String METADATA_GEOMETRY_TYPE3 = "type"; private static final String METADATA_GEOMETRY_COLUMN = "f_geometry_column"; private static final String METADATA_TABLE_NAME = "f_table_name"; private static final String NAME = "name"; private static final String SIZE = "size"; private static final String FILLCOLOR = "fillcolor"; private static final String STROKECOLOR = "strokecolor"; private static final String FILLALPHA = "fillalpha"; private static final String STROKEALPHA = "strokealpha"; private static final String SHAPE = "shape"; private static final String WIDTH = "width"; private static final String TEXTSIZE = "textsize"; private static final String TEXTFIELD = "textfield"; private static final String ENABLED = "enabled"; private static final String ORDER = "layerorder"; private static final String DECIMATION = "decimationfactor"; private final String PROPERTIESTABLE = "dataproperties"; private Database db; private HashMap<String, Paint> fillPaints = new HashMap<String, Paint>(); private HashMap<String, Paint> strokePaints = new HashMap<String, Paint>(); private List<SpatialVectorTable> vectorTableList; private List<SpatialRasterTable> rasterTableList; private String fileName; public SpatialiteDatabaseHandler( String dbPath ) { try { File spatialDbFile = new File(dbPath); if (!spatialDbFile.getParentFile().exists()) { throw new RuntimeException(); } db = new jsqlite.Database(); db.open(spatialDbFile.getAbsolutePath(), jsqlite.Constants.SQLITE_OPEN_READWRITE | jsqlite.Constants.SQLITE_OPEN_CREATE); fileName = spatialDbFile.getName(); } catch (Exception e) { e.printStackTrace(); } } public String getFileName() { return fileName; } /** * Get the version of Spatialite. * * @return the version of Spatialite. * @throws Exception */ public String getSpatialiteVersion() throws Exception { Stmt stmt = db.prepare("SELECT spatialite_version();"); try { if (stmt.step()) { String value = stmt.column_string(0); return value; } } finally { stmt.close(); } return "-"; } /** * Get the version of proj. * * @return the version of proj. * @throws Exception */ public String getProj4Version() throws Exception { Stmt stmt = db.prepare("SELECT proj4_version();"); try { if (stmt.step()) { String value = stmt.column_string(0); return value; } } finally { stmt.close(); } return "-"; } /** * Get the version of geos. * * @return the version of geos. * @throws Exception */ public String getGeosVersion() throws Exception { Stmt stmt = db.prepare("SELECT geos_version();"); try { if (stmt.step()) { String value = stmt.column_string(0); return value; } } finally { stmt.close(); } return "-"; } @Override public List<SpatialVectorTable> getSpatialVectorTables( boolean forceRead ) throws Exception { if (vectorTableList == null || forceRead) { vectorTableList = new ArrayList<SpatialVectorTable>(); StringBuilder sb3 = new StringBuilder(); sb3.append("select "); sb3.append(METADATA_TABLE_NAME); sb3.append(", "); sb3.append(METADATA_GEOMETRY_COLUMN); sb3.append(", "); sb3.append(METADATA_GEOMETRY_TYPE3); sb3.append(","); sb3.append(METADATA_SRID); sb3.append(" from "); sb3.append(METADATA_TABLE_GEOMETRY_COLUMNS); sb3.append(";"); String query3 = sb3.toString(); boolean is3 = true; Stmt stmt = null; try { stmt = db.prepare(query3); } catch (java.lang.Exception e) { // try with spatialite 4 syntax StringBuilder sb4 = new StringBuilder(); sb4.append("select "); sb4.append(METADATA_TABLE_NAME); sb4.append(", "); sb4.append(METADATA_GEOMETRY_COLUMN); sb4.append(", "); sb4.append(METADATA_GEOMETRY_TYPE4); sb4.append(","); sb4.append(METADATA_SRID); sb4.append(" from "); sb4.append(METADATA_TABLE_GEOMETRY_COLUMNS); sb4.append(";"); String query4 = sb4.toString(); stmt = db.prepare(query4); is3 = false; } try { while( stmt.step() ) { String name = stmt.column_string(0); String geomName = stmt.column_string(1); int geomType = 0; if (is3) { String type = stmt.column_string(2); geomType = GeometryType.forValue(type); } else { geomType = stmt.column_int(2); } String srid = String.valueOf(stmt.column_int(3)); SpatialVectorTable table = new SpatialVectorTable(name, geomName, geomType, srid); vectorTableList.add(table); } } finally { stmt.close(); } // now read styles checkPropertiesTable(); // assign the styles for( SpatialVectorTable spatialTable : vectorTableList ) { Style style4Table = getStyle4Table(spatialTable.getName()); if (style4Table == null) { spatialTable.makeDefaultStyle(); } else { spatialTable.setStyle(style4Table); } } } OrderComparator orderComparator = new OrderComparator(); Collections.sort(vectorTableList, orderComparator); return vectorTableList; } @Override public List<SpatialRasterTable> getSpatialRasterTables( boolean forceRead ) throws Exception { if (rasterTableList == null || forceRead) { rasterTableList = new ArrayList<SpatialRasterTable>(); StringBuilder sb = new StringBuilder(); sb.append("select "); sb.append(METADATA_RASTER_TABLE_NAME); sb.append(", "); sb.append(METADATA_RASTER_COLUMN); sb.append(", srid from "); sb.append(METADATA_TABLE_RASTER_COLUMNS); sb.append(";"); String query = sb.toString(); Stmt stmt = db.prepare(query); try { while( stmt.step() ) { String tableName = stmt.column_string(0); String columnName = stmt.column_string(1); String srid = String.valueOf(stmt.column_int(2)); if (tableName != null) { int[] zoomLevels = {0, 18}; getZoomLevels(tableName, zoomLevels); double[] centerCoordinate = {0.0, 0.0}; getCenterCoordinate4326(tableName, centerCoordinate); SpatialRasterTable table = new SpatialRasterTable(tableName, columnName, srid, zoomLevels[0], zoomLevels[1], centerCoordinate[0], centerCoordinate[1], null); rasterTableList.add(table); } } } finally { stmt.close(); } } // OrderComparator orderComparator = new OrderComparator(); // Collections.sort(rasterTableList, orderComparator); return rasterTableList; } /** * Extract the center coordinate of a raster tileset. * * @param tableName the raster table name. * @param centerCoordinate teh coordinate array to update with the extracted values. */ private void getCenterCoordinate4326( String tableName, double[] centerCoordinate ) { try { Stmt centerStmt = null; try { WKBReader wkbReader = new WKBReader(); StringBuilder centerBuilder = new StringBuilder(); centerBuilder.append("select ST_AsBinary(CastToXY(ST_Transform(MakePoint("); // centerBuilder.append("select AsText(ST_Transform(MakePoint("); centerBuilder.append("(min_x + (max_x-min_x)/2), "); centerBuilder.append("(min_y + (max_y-min_y)/2), "); centerBuilder.append(METADATA_SRID); centerBuilder.append("), 4326))) from "); centerBuilder.append(METADATA_TABLE_GEOPACKAGE_CONTENTS); centerBuilder.append(" where "); centerBuilder.append(METADATA_GEOPACKAGECONTENT_TABLE_NAME); centerBuilder.append("='"); centerBuilder.append(tableName); centerBuilder.append("';"); String centerQuery = centerBuilder.toString(); centerStmt = db.prepare(centerQuery); if (centerStmt.step()) { // String geomBytes = centerStmt.column_string(0); // System.out.println(); byte[] geomBytes = centerStmt.column_bytes(0); Geometry geometry = wkbReader.read(geomBytes); Coordinate coordinate = geometry.getCoordinate(); centerCoordinate[0] = coordinate.x; centerCoordinate[1] = coordinate.y; } } finally { if (centerStmt != null) centerStmt.close(); } } catch (java.lang.Exception e) { e.printStackTrace(); } } /** * Get the available zoomlevels for a raster table. * * @param tableName the raster table name. * @param zoomLevels the zoomlevels array to update with the min and max levels available. * @throws Exception */ private void getZoomLevels( String tableName, int[] zoomLevels ) throws Exception { Stmt zoomStmt = null; try { StringBuilder zoomBuilder = new StringBuilder(); zoomBuilder.append("SELECT min("); zoomBuilder.append(METADATA_ZOOM_LEVEL); zoomBuilder.append("),max("); zoomBuilder.append(METADATA_ZOOM_LEVEL); zoomBuilder.append(") FROM "); zoomBuilder.append(METADATA_TABLE_TILE_MATRIX); zoomBuilder.append(" WHERE "); zoomBuilder.append(METADATA_TILE_TABLE_NAME); zoomBuilder.append("='"); zoomBuilder.append(tableName); zoomBuilder.append("';"); String zoomQuery = zoomBuilder.toString(); zoomStmt = db.prepare(zoomQuery); if (zoomStmt.step()) { zoomLevels[0] = zoomStmt.column_int(0); zoomLevels[1] = zoomStmt.column_int(1); } } finally { if (zoomStmt != null) zoomStmt.close(); } } /** * Check availability of style for the tables. * * @throws Exception */ private void checkPropertiesTable() throws Exception { String checkTableQuery = "SELECT name FROM sqlite_master WHERE type='table' AND name='" + PROPERTIESTABLE + "';"; Stmt stmt = db.prepare(checkTableQuery); boolean tableExists = false; try { if (stmt.step()) { String name = stmt.column_string(0); if (name != null) { tableExists = true; } } } finally { stmt.close(); } if (!tableExists) { StringBuilder sb = new StringBuilder(); sb.append("CREATE TABLE "); sb.append(PROPERTIESTABLE); sb.append(" ("); sb.append(NAME).append(" TEXT, "); sb.append(SIZE).append(" REAL, "); sb.append(FILLCOLOR).append(" TEXT, "); sb.append(STROKECOLOR).append(" TEXT, "); sb.append(FILLALPHA).append(" REAL, "); sb.append(STROKEALPHA).append(" REAL, "); sb.append(SHAPE).append(" TEXT, "); sb.append(WIDTH).append(" REAL, "); sb.append(TEXTSIZE).append(" REAL, "); sb.append(TEXTFIELD).append(" TEXT, "); sb.append(ENABLED).append(" INTEGER, "); sb.append(ORDER).append(" INTEGER,"); sb.append(DECIMATION).append(" REAL"); sb.append(" );"); String query = sb.toString(); db.exec(query, null); for( SpatialVectorTable spatialTable : vectorTableList ) { StringBuilder sbIn = new StringBuilder(); sbIn.append("insert into ").append(PROPERTIESTABLE); sbIn.append(" ( "); sbIn.append(NAME).append(" , "); sbIn.append(SIZE).append(" , "); sbIn.append(FILLCOLOR).append(" , "); sbIn.append(STROKECOLOR).append(" , "); sbIn.append(FILLALPHA).append(" , "); sbIn.append(STROKEALPHA).append(" , "); sbIn.append(SHAPE).append(" , "); sbIn.append(WIDTH).append(" , "); sbIn.append(TEXTSIZE).append(" , "); sbIn.append(TEXTFIELD).append(" , "); sbIn.append(ENABLED).append(" , "); sbIn.append(ORDER).append(" , "); sbIn.append(DECIMATION); sbIn.append(" ) "); sbIn.append(" values "); sbIn.append(" ( "); Style style = new Style(); style.name = spatialTable.getName(); sbIn.append(style.insertValuesString()); sbIn.append(" );"); String insertQuery = sbIn.toString(); db.exec(insertQuery, null); } } } /** * Retrieve the {@link Style} for a given table. * * @param tableName * @return * @throws Exception */ public Style getStyle4Table( String tableName ) throws Exception { Style style = new Style(); style.name = tableName; StringBuilder sbSel = new StringBuilder(); sbSel.append("select "); sbSel.append(SIZE).append(" , "); sbSel.append(FILLCOLOR).append(" , "); sbSel.append(STROKECOLOR).append(" , "); sbSel.append(FILLALPHA).append(" , "); sbSel.append(STROKEALPHA).append(" , "); sbSel.append(SHAPE).append(" , "); sbSel.append(WIDTH).append(" , "); sbSel.append(TEXTSIZE).append(" , "); sbSel.append(TEXTFIELD).append(" , "); sbSel.append(ENABLED).append(" , "); sbSel.append(ORDER).append(" , "); sbSel.append(DECIMATION); sbSel.append(" from "); sbSel.append(PROPERTIESTABLE); sbSel.append(" where "); sbSel.append(NAME).append(" ='").append(tableName).append("';"); String selectQuery = sbSel.toString(); Stmt stmt = db.prepare(selectQuery); try { if (stmt.step()) { style.size = (float) stmt.column_double(0); style.fillcolor = stmt.column_string(1); style.strokecolor = stmt.column_string(2); style.fillalpha = (float) stmt.column_double(3); style.strokealpha = (float) stmt.column_double(4); style.shape = stmt.column_string(5); style.width = (float) stmt.column_double(6); style.textsize = (float) stmt.column_double(7); style.textfield = stmt.column_string(8); style.enabled = stmt.column_int(9); style.order = stmt.column_int(10); style.decimationFactor = (float) stmt.column_double(11); } } finally { stmt.close(); } return style; } public float[] getTableBounds( SpatialVectorTable spatialTable, String destSrid ) throws Exception { boolean doTransform = false; if (!spatialTable.getSrid().equals(destSrid)) { doTransform = true; } StringBuilder geomSb = new StringBuilder(); if (doTransform) geomSb.append("ST_Transform("); geomSb.append(spatialTable.getGeomName()); if (doTransform) { geomSb.append(", "); geomSb.append(destSrid); geomSb.append(")"); } String geom = geomSb.toString(); StringBuilder qSb = new StringBuilder(); qSb.append("SELECT Min(MbrMinX("); qSb.append(geom); qSb.append(")) AS min_x, Min(MbrMinY("); qSb.append(geom); qSb.append(")) AS min_y,"); qSb.append("Max(MbrMaxX("); qSb.append(geom); qSb.append(")) AS max_x, Max(MbrMaxY("); qSb.append(geom); qSb.append(")) AS max_y"); qSb.append(" FROM "); qSb.append(spatialTable.getName()); qSb.append(";"); String selectQuery = qSb.toString(); Stmt stmt = db.prepare(selectQuery); try { if (stmt.step()) { float w = (float) stmt.column_double(0); float s = (float) stmt.column_double(1); float e = (float) stmt.column_double(2); float n = (float) stmt.column_double(3); return new float[]{n, s, e, w}; } } finally { stmt.close(); } return null; } /** * Update a style definition. * * @param style the {@link Style} to set. * @throws Exception */ public void updateStyle( Style style ) throws Exception { StringBuilder sbIn = new StringBuilder(); sbIn.append("update ").append(PROPERTIESTABLE); sbIn.append(" set "); // sbIn.append(NAME).append("='").append(style.name).append("' , "); sbIn.append(SIZE).append("=").append(style.size).append(" , "); sbIn.append(FILLCOLOR).append("='").append(style.fillcolor).append("' , "); sbIn.append(STROKECOLOR).append("='").append(style.strokecolor).append("' , "); sbIn.append(FILLALPHA).append("=").append(style.fillalpha).append(" , "); sbIn.append(STROKEALPHA).append("=").append(style.strokealpha).append(" , "); sbIn.append(SHAPE).append("='").append(style.shape).append("' , "); sbIn.append(WIDTH).append("=").append(style.width).append(" , "); sbIn.append(TEXTSIZE).append("=").append(style.textsize).append(" , "); sbIn.append(TEXTFIELD).append("='").append(style.textfield).append("' , "); sbIn.append(ENABLED).append("=").append(style.enabled).append(" , "); sbIn.append(ORDER).append("=").append(style.order).append(" , "); sbIn.append(DECIMATION).append("=").append(style.decimationFactor); sbIn.append(" where "); sbIn.append(NAME); sbIn.append("='"); sbIn.append(style.name); sbIn.append("';"); String updateQuery = sbIn.toString(); db.exec(updateQuery, null); } @Override public Paint getFillPaint4Style( Style style ) { Paint paint = fillPaints.get(style.name); if (paint == null) { paint = new Paint(); fillPaints.put(style.name, paint); } paint.setAntiAlias(true); paint.setStyle(Paint.Style.FILL); paint.setColor(Color.parseColor(style.fillcolor)); float alpha = style.fillalpha * 255f; paint.setAlpha((int) alpha); return paint; } @Override public Paint getStrokePaint4Style( Style style ) { Paint paint = strokePaints.get(style.name); if (paint == null) { paint = new Paint(); strokePaints.put(style.name, paint); } paint.setStyle(Paint.Style.STROKE); paint.setAntiAlias(true); paint.setStrokeCap(Cap.ROUND); paint.setStrokeJoin(Join.ROUND); paint.setColor(Color.parseColor(style.strokecolor)); float alpha = style.strokealpha * 255f; paint.setAlpha((int) alpha); paint.setStrokeWidth(style.width); return paint; } public List<byte[]> getWKBFromTableInBounds( String destSrid, SpatialVectorTable table, double n, double s, double e, double w ) { List<byte[]> list = new ArrayList<byte[]>(); String query = buildGeometriesInBoundsQuery(destSrid, table, n, s, e, w); try { Stmt stmt = db.prepare(query); try { while( stmt.step() ) { list.add(stmt.column_bytes(0)); } } finally { stmt.close(); } return list; } catch (Exception ex) { ex.printStackTrace(); } return null; } @Override public byte[] getRasterTile( String query ) { try { Stmt stmt = db.prepare(query); try { if (stmt.step()) { byte[] bytes = stmt.column_bytes(0); return bytes; } } finally { stmt.close(); } } catch (Exception ex) { ex.printStackTrace(); } return null; } @Override public GeometryIterator getGeometryIteratorInBounds( String destSrid, SpatialVectorTable table, double n, double s, double e, double w ) { String query = buildGeometriesInBoundsQuery(destSrid, table, n, s, e, w); return new GeometryIterator(db, query); } private String buildGeometriesInBoundsQuery( String destSrid, SpatialVectorTable table, double n, double s, double e, double w ) { boolean doTransform = false; if (!table.getSrid().equals(destSrid)) { doTransform = true; } StringBuilder mbrSb = new StringBuilder(); if (doTransform) mbrSb.append("ST_Transform("); mbrSb.append("BuildMBR("); mbrSb.append(w); mbrSb.append(", "); mbrSb.append(n); mbrSb.append(", "); mbrSb.append(e); mbrSb.append(", "); mbrSb.append(s); if (doTransform) { mbrSb.append(", "); mbrSb.append(destSrid); mbrSb.append("), "); mbrSb.append(table.getSrid()); } mbrSb.append(")"); String mbr = mbrSb.toString(); StringBuilder qSb = new StringBuilder(); qSb.append("SELECT ST_AsBinary(CastToXY("); if (doTransform) qSb.append("ST_Transform("); qSb.append(table.getGeomName()); if (doTransform) { qSb.append(", "); qSb.append(destSrid); qSb.append(")"); } qSb.append("))"); // qSb.append(", AsText("); // if (doTransform) // qSb.append("ST_Transform("); // qSb.append(table.geomName); // if (doTransform) { // qSb.append(", "); // qSb.append(destSrid); // qSb.append(")"); // } // qSb.append(")"); qSb.append(" FROM "); qSb.append(table.getName()); qSb.append(" WHERE ST_Intersects("); qSb.append(table.getGeomName()); qSb.append(", "); qSb.append(mbr); qSb.append(") = 1"); qSb.append(" AND ROWID IN ("); qSb.append(" SELECT ROWID FROM Spatialindex WHERE f_table_name ='"); qSb.append(table.getName()); qSb.append("'"); qSb.append(" AND search_frame = "); qSb.append(mbr); qSb.append(" );"); String q = qSb.toString(); return q; } public void close() throws Exception { if (db != null) { db.close(); } } public void intersectionToStringBBOX( String boundsSrid, SpatialVectorTable spatialTable, double n, double s, double e, double w, StringBuilder sb, String indentStr ) throws Exception { boolean doTransform = false; if (!spatialTable.getSrid().equals(boundsSrid)) { doTransform = true; } String query = null; // SELECT che-cazzo-ti-pare-a-te // FROM qualche-tavola // WHERE ROWID IN ( // SELECT ROWID // FROM SpatialIndex // WHERE f_table_name = 'qualche-tavola' // AND search_frame = il-tuo-bbox // ); // { // StringBuilder sbQ = new StringBuilder(); // sbQ.append("SELECT "); // sbQ.append("*"); // sbQ.append(" from ").append(spatialTable.name); // sbQ.append(" where ROWID IN ("); // sbQ.append(" SELECT ROWID FROM Spatialindex WHERE f_table_name ='"); // sbQ.append(spatialTable.name); // sbQ.append("' AND search_frame = "); // if (doTransform) // sbQ.append("ST_Transform("); // sbQ.append("BuildMBR("); // sbQ.append(w); // sbQ.append(", "); // sbQ.append(s); // sbQ.append(", "); // sbQ.append(e); // sbQ.append(", "); // sbQ.append(n); // if (doTransform) { // sbQ.append(", "); // sbQ.append(boundsSrid); // } // sbQ.append(")"); // if (doTransform) { // sbQ.append(","); // sbQ.append(spatialTable.srid); // sbQ.append(")"); // } // sbQ.append(");"); // // query = sbQ.toString(); // Logger.i(this, query); // } { StringBuilder sbQ = new StringBuilder(); sbQ.append("SELECT "); sbQ.append("*"); sbQ.append(" from ").append(spatialTable.getName()); sbQ.append(" where ST_Intersects("); if (doTransform) sbQ.append("ST_Transform("); sbQ.append("BuildMBR("); sbQ.append(w); sbQ.append(", "); sbQ.append(s); sbQ.append(", "); sbQ.append(e); sbQ.append(", "); sbQ.append(n); if (doTransform) { sbQ.append(", "); sbQ.append(boundsSrid); sbQ.append("),"); sbQ.append(spatialTable.getSrid()); } sbQ.append("),"); sbQ.append(spatialTable.getGeomName()); sbQ.append(");"); query = sbQ.toString(); // Logger.i(this, query); } Stmt stmt = db.prepare(query); try { while( stmt.step() ) { int column_count = stmt.column_count(); for( int i = 0; i < column_count; i++ ) { String cName = stmt.column_name(i); if (cName.equalsIgnoreCase(spatialTable.getGeomName())) { continue; } String value = stmt.column_string(i); sb.append(indentStr).append(cName).append(": ").append(value).append("\n"); } sb.append("\n"); } } finally { stmt.close(); } } public void intersectionToString4Polygon( String queryPointSrid, SpatialVectorTable spatialTable, double n, double e, StringBuilder sb, String indentStr ) throws Exception { boolean doTransform = false; if (!spatialTable.getSrid().equals(queryPointSrid)) { doTransform = true; } StringBuilder sbQ = new StringBuilder(); sbQ.append("SELECT * FROM "); sbQ.append(spatialTable.getName()); sbQ.append(" WHERE ST_Intersects("); sbQ.append(spatialTable.getGeomName()); sbQ.append(", "); if (doTransform) sbQ.append("ST_Transform("); sbQ.append("MakePoint("); sbQ.append(e); sbQ.append(","); sbQ.append(n); if (doTransform) { sbQ.append(", "); sbQ.append(queryPointSrid); sbQ.append("), "); sbQ.append(spatialTable.getSrid()); } sbQ.append(")) = 1 "); sbQ.append("AND ROWID IN ("); sbQ.append("SELECT ROWID FROM Spatialindex WHERE f_table_name ='"); sbQ.append(spatialTable.getName()); sbQ.append("' AND search_frame = "); if (doTransform) sbQ.append("ST_Transform("); sbQ.append("MakePoint("); sbQ.append(e); sbQ.append(","); sbQ.append(n); if (doTransform) { sbQ.append(", "); sbQ.append(queryPointSrid); sbQ.append("), "); sbQ.append(spatialTable.getSrid()); } sbQ.append("));"); String query = sbQ.toString(); Stmt stmt = db.prepare(query); try { while( stmt.step() ) { int column_count = stmt.column_count(); for( int i = 0; i < column_count; i++ ) { String cName = stmt.column_name(i); if (cName.equalsIgnoreCase(spatialTable.getGeomName())) { continue; } String value = stmt.column_string(i); sb.append(indentStr).append(cName).append(": ").append(value).append("\n"); } sb.append("\n"); } } finally { stmt.close(); } } // public String queryComuni() { // sb.append(SEP); // sb.append("Query Comuni...\n"); // // String query = "SELECT " + NOME + // // " from " + COMUNITABLE + // // " order by " + NOME + ";"; // sb.append("Execute query: ").append(query).append("\n"); // try { // Stmt stmt = db.prepare(query); // int index = 0; // while( stmt.step() ) { // String nomeStr = stmt.column_string(0); // sb.append("\t").append(nomeStr).append("\n"); // if (index++ > 5) { // break; // } // } // sb.append("\t..."); // stmt.close(); // } catch (Exception e) { // error(e); // } // // sb.append("Done...\n"); // // return sb.toString(); // } // // public String queryComuniWithGeom() { // sb.append(SEP); // sb.append("Query Comuni with AsText(Geometry)...\n"); // // String query = "SELECT " + NOME + // // " , " + AS_TEXT_GEOMETRY + // // " as geom from " + COMUNITABLE + // // " where geom not null;"; // sb.append("Execute query: ").append(query).append("\n"); // try { // Stmt stmt = db.prepare(query); // while( stmt.step() ) { // String nomeStr = stmt.column_string(0); // String geomStr = stmt.column_string(1); // String substring = geomStr; // if (substring.length() > 40) // substring = geomStr.substring(0, 40); // sb.append("\t").append(nomeStr).append(" - ").append(substring).append("...\n"); // break; // } // stmt.close(); // } catch (Exception e) { // e.printStackTrace(); // sb.append(ERROR).append(e.getLocalizedMessage()).append("\n"); // } // sb.append("Done...\n"); // // return sb.toString(); // } // // public String queryGeomTypeAndSrid() { // sb.append(SEP); // sb.append("Query Comuni geom type and srid...\n"); // // String query = "SELECT " + NOME + // // " , " + AS_TEXT_GEOMETRY + // // " as geom from " + COMUNITABLE + // // " where geom not null;"; // sb.append("Execute query: ").append(query).append("\n"); // try { // Stmt stmt = db.prepare(query); // while( stmt.step() ) { // String nomeStr = stmt.column_string(0); // String geomStr = stmt.column_string(1); // String substring = geomStr; // if (substring.length() > 40) // substring = geomStr.substring(0, 40); // sb.append("\t").append(nomeStr).append(" - ").append(substring).append("...\n"); // break; // } // stmt.close(); // } catch (Exception e) { // e.printStackTrace(); // sb.append(ERROR).append(e.getLocalizedMessage()).append("\n"); // } // sb.append("Done...\n"); // // return sb.toString(); // } // // public String queryComuniArea() { // sb.append(SEP); // sb.append("Query Comuni area sum...\n"); // // String query = "SELECT ST_Area(Geometry) / 1000000.0 from " + COMUNITABLE + // // ";"; // sb.append("Execute query: ").append(query).append("\n"); // try { // Stmt stmt = db.prepare(query); // double totalArea = 0; // while( stmt.step() ) { // double area = stmt.column_double(0); // totalArea = totalArea + area; // } // sb.append("\tTotal area by summing each area: ").append(totalArea).append("Km2\n"); // stmt.close(); // } catch (Exception e) { // e.printStackTrace(); // sb.append(ERROR).append(e.getLocalizedMessage()).append("\n"); // } // query = "SELECT sum(ST_Area(Geometry) / 1000000.0) from " + COMUNITABLE + // // ";"; // sb.append("Execute query: ").append(query).append("\n"); // try { // Stmt stmt = db.prepare(query); // double totalArea = 0; // if (stmt.step()) { // double area = stmt.column_double(0); // totalArea = totalArea + area; // } // sb.append("\tTotal area by summing in query: ").append(totalArea).append("Km2\n"); // stmt.close(); // } catch (Exception e) { // e.printStackTrace(); // sb.append(ERROR).append(e.getLocalizedMessage()).append("\n"); // } // sb.append("Done...\n"); // // return sb.toString(); // } // // public String queryComuniNearby() { // sb.append(SEP); // sb.append("Query Comuni nearby...\n"); // // String query = // "SELECT Hex(ST_AsBinary(ST_Buffer(Geometry, 1.0))), ST_Srid(Geometry), ST_GeometryType(Geometry) from " // + COMUNITABLE + // // " where " + NOME + "= 'Bolzano';"; // sb.append("Execute query: ").append(query).append("\n"); // String bufferGeom = ""; // String bufferGeomShort = ""; // try { // Stmt stmt = db.prepare(query); // if (stmt.step()) { // bufferGeom = stmt.column_string(0); // String geomSrid = stmt.column_string(1); // String geomType = stmt.column_string(2); // sb.append("\tThe selected geometry is of type: ").append(geomType).append(" and of SRID: ").append(geomSrid) // .append("\n"); // } // bufferGeomShort = bufferGeom; // if (bufferGeom.length() > 10) // bufferGeomShort = bufferGeom.substring(0, 10) + "..."; // sb.append("\tBolzano polygon buffer geometry in HEX: ").append(bufferGeomShort).append("\n"); // stmt.close(); // } catch (Exception e) { // e.printStackTrace(); // sb.append(ERROR).append(e.getLocalizedMessage()).append("\n"); // } // // query = "SELECT " + NOME + ", AsText(ST_centroid(Geometry)) from " + COMUNITABLE + // // " where ST_Intersects( ST_GeomFromWKB(x'" + bufferGeom + "') , Geometry );"; // // just for print // String tmpQuery = "SELECT " + NOME + " from " + COMUNITABLE + // // " where ST_Intersects( ST_GeomFromWKB(x'" + bufferGeomShort + "') , Geometry );"; // sb.append("Execute query: ").append(tmpQuery).append("\n"); // try { // sb.append("\tComuni nearby Bolzano: \n"); // Stmt stmt = db.prepare(query); // while( stmt.step() ) { // String name = stmt.column_string(0); // String wkt = stmt.column_string(1); // sb.append("\t\t").append(name).append(" - with centroid in ").append(wkt).append("\n"); // } // stmt.close(); // } catch (Exception e) { // e.printStackTrace(); // sb.append(ERROR).append(e.getLocalizedMessage()).append("\n"); // } // sb.append("Done...\n"); // // return sb.toString(); // } // // public byte[] getBolzanoWKB() { // String query = "SELECT ST_AsBinary(ST_Transform(Geometry, 4326)) from " + COMUNITABLE + // // " where " + NOME + "= 'Bolzano';"; // try { // Stmt stmt = db.prepare(query); // byte[] theGeom = null; // if (stmt.step()) { // theGeom = stmt.column_bytes(0); // } // stmt.close(); // return theGeom; // } catch (Exception e) { // e.printStackTrace(); // } // return null; // } // // public List<byte[]> getIntersectingWKB( double n, double s, double e, double w ) { // List<byte[]> list = new ArrayList<byte[]>(); // Coordinate ll = new Coordinate(w, s); // Coordinate ul = new Coordinate(w, n); // Coordinate ur = new Coordinate(e, n); // Coordinate lr = new Coordinate(e, s); // Polygon bboxPolygon = gf.createPolygon(new Coordinate[]{ll, ul, ur, lr, ll}); // // byte[] bbox = wr.write(bboxPolygon); // String query = "SELECT ST_AsBinary(ST_Transform(Geometry, 4326)) from " + COMUNITABLE + // // " where ST_Intersects(ST_Transform(Geometry, 4326), ST_GeomFromWKB(?));"; // try { // Stmt stmt = db.prepare(query); // stmt.bind(1, bbox); // while( stmt.step() ) { // list.add(stmt.column_bytes(0)); // } // stmt.close(); // return list; // } catch (Exception ex) { // ex.printStackTrace(); // } // return null; // } // // public String doSimpleTransform() { // // sb.append(SEP); // sb.append("Coordinate transformation...\n"); // // String query = "SELECT AsText(Transform(MakePoint(" + TEST_LON + ", " + TEST_LAT + // ", 4326), 32632));"; // sb.append("Execute query: ").append(query).append("\n"); // try { // Stmt stmt = db.prepare(query); // if (stmt.step()) { // String pointStr = stmt.column_string(0); // sb.append("\t").append(TEST_LON + "/" + TEST_LAT + "/EPSG:4326").append(" = ")// // .append(pointStr + "/EPSG:32632").append("...\n"); // } // stmt.close(); // } catch (Exception e) { // e.printStackTrace(); // sb.append(ERROR).append(e.getLocalizedMessage()).append("\n"); // } // sb.append("Done...\n"); // // return sb.toString(); // // } }