/*
* GeoSolutions map - Digital field mapping on Android based devices
* Copyright (C) 2013 GeoSolutions (www.geo-solutions.it)
*
* 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 it.geosolutions.android.map.database.spatialite;
import it.geosolutions.android.map.database.SpatialDataSourceHandler;
import it.geosolutions.android.map.model.Attribute;
import it.geosolutions.android.map.model.Feature;
import it.geosolutions.android.map.utils.Coordinates.Coordinates_Query;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
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 android.os.Bundle;
import android.util.Log;
import com.vividsolutions.jts.geom.Coordinate;
import com.vividsolutions.jts.geom.Geometry;
import com.vividsolutions.jts.io.ParseException;
import com.vividsolutions.jts.io.WKBReader;
import eu.geopaparazzi.spatialite.database.spatial.core.GeometryIterator;
import eu.geopaparazzi.spatialite.database.spatial.core.GeometryType;
import eu.geopaparazzi.spatialite.database.spatial.core.OrderComparator;
import eu.geopaparazzi.spatialite.database.spatial.core.SpatialRasterTable;
import eu.geopaparazzi.spatialite.database.spatial.core.SpatialVectorTable;
import eu.geopaparazzi.spatialite.database.spatial.core.Style;
/**
* Implementation of interface of the Spatial data source handler.
* @author Lorenzo Natali (www.geo-solutions.it).
*/
public class SpatialiteDataSourceHandler implements SpatialDataSourceHandler{
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 ORDER_BY_DEFAULT_FIELD = "ROWID";
private static final String DEFAULT_GEOMETRY_NAME ="MAPSTORE_9384_GEOMETRY";
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 SpatialiteDataSourceHandler( 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();
}
}
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();
}
}
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;
}
@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(" 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;
{
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();
}
}
/**
* TODO needs to manage start, sorting...
*/
@Override
public ArrayList<Bundle> intersectionToBundleBBOX( String boundsSrid, SpatialVectorTable spatialTable, double n, double s, double e,
double w,Integer start,Integer limit ) throws Exception {
Stmt stmt = generateBBoxQuery(boundsSrid, spatialTable, n, s, e, w,
start, limit);
ArrayList<Bundle> features = new ArrayList<Bundle>();
try {
//every row of the table (feature)
generateBundle(spatialTable, stmt, features);
}catch(Exception ee){
Log.e("DATABASE","Error in database query:\nException:"+ee.getMessage());
throw ee;
} finally {
stmt.close();
}
return features;
}
/**
* @param spatialTable
* @param stmt
* @param features
* @throws Exception
*/
private void generateBundle(SpatialVectorTable spatialTable, Stmt stmt,
ArrayList<Bundle> features) throws Exception {
while( stmt.step() ) {
int column_count = stmt.column_count();
Bundle feature= new Bundle();
for( int i = 0; i < column_count; i++ ) {
String cName = stmt.column_name(i);
//skip geometry
if (cName.equalsIgnoreCase(spatialTable.getGeomName())) {
continue;
}
feature.putString(cName, stmt.column_string(i));
//add name->value pairs to the attribute ArrayList
}
features.add(feature);
//add the ArrayList
}
}
/**
* @param boundsSrid
* @param spatialTable
* @param n
* @param s
* @param e
* @param w
* @param start
* @param limit
* @return
* @throws Exception
*/
private Stmt generateBBoxQuery(String boundsSrid,
SpatialVectorTable spatialTable, double n, double s, double e,
double w, Integer start, Integer limit) throws Exception {
boolean doTransform = false;
if (!spatialTable.getSrid().equals(boundsSrid)) {
doTransform = true;
}
String query = null;
{
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(")");
if(limit != null){
if(start !=null){
sbQ.append(" ORDER BY ");
sbQ.append(ORDER_BY_DEFAULT_FIELD);
sbQ.append(" ");
}
sbQ.append(" LIMIT ");
if(start != null){
sbQ.append(start);
sbQ.append(",");
sbQ.append(start + limit);
}else{
sbQ.append(limit);
}
}
sbQ.append(";");
query = sbQ.toString();
// Logger.i(this, query);
}
Stmt stmt = db.prepare(query);
return stmt;
}
/**
* Generates a query for a layer where the
* @param layer
* @param attributeName
* @param attributeValue
* @param start
* @param limit
* @return
* @throws Exception
*/
private Stmt generateQueryByAttributeForGeometry(String destSrid,SpatialVectorTable table, String attributeName,
String attributeValue, Integer start, Integer limit) throws Exception {
boolean doTransform = false;
if (!table.getSrid().equals(destSrid)) {
doTransform = true;
}
String query = null;
StringBuilder sbQ = new StringBuilder();
//SELECT
sbQ.append("SELECT ST_AsBinary(CastToXY(");
if (doTransform)
sbQ.append("ST_Transform(");
sbQ.append(table.getGeomName());
if (doTransform) {
sbQ.append(", ");
sbQ.append(destSrid);
sbQ.append(")");
}
sbQ.append(")) AS ");
sbQ.append(DEFAULT_GEOMETRY_NAME);
//FROM
sbQ.append(" FROM \"").append(table.getName());
sbQ.append("\" ");
//WHERE
sbQ.append(" WHERE \"");
sbQ.append(attributeName);
sbQ.append("\"='"); //TODO now works only for strings
sbQ.append(attributeValue);
sbQ.append("' "); //LIMIT & ORDER
if(limit != null){
if(start !=null){
sbQ.append(" ORDER BY ");
sbQ.append(ORDER_BY_DEFAULT_FIELD);
sbQ.append(" ");
}
sbQ.append(" LIMIT ");
if(start != null){
sbQ.append(start);
sbQ.append(",");
sbQ.append(start + limit);
}else{
sbQ.append(limit);
}
}
sbQ.append(";");
query = sbQ.toString();
return db.prepare(query);
}
private Stmt generateQueryByAttributeForFeature(String destSrid,SpatialVectorTable table, String attributeName,
String attributeValue, Integer start, Integer limit) throws Exception {
boolean doTransform = false;
if (!table.getSrid().equals(destSrid)) {
doTransform = true;
}
String query = null;
StringBuilder sbQ = new StringBuilder();
//SELECT
sbQ.append("SELECT *");
//FROM
sbQ.append(" FROM \"").append(table.getName());
sbQ.append("\" ");
//WHERE
sbQ.append(" WHERE \"");
sbQ.append(attributeName);
sbQ.append("\"='"); //TODO now works only for strings
sbQ.append(attributeValue);
sbQ.append("' "); //LIMIT & ORDER
if(limit != null){
if(start !=null){
sbQ.append(" ORDER BY ");
sbQ.append(ORDER_BY_DEFAULT_FIELD);
sbQ.append(" ");
}
sbQ.append(" LIMIT ");
if(start != null){
sbQ.append(start);
sbQ.append(",");
sbQ.append(start + limit);
}else{
sbQ.append(limit);
}
}
sbQ.append(";");
query = sbQ.toString();
return db.prepare(query);
}
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();
}
}
/* (non-Javadoc)
* @see it.geosolutions.android.map.database.SpatialDataSourceHandler#queryBBox()
*/
@Override
public Map<String, String> queryBBox() {
// TODO Auto-generated method stub
return null;
}
/**
* IMPORTANT Implemented for interface implementation but NOT MANAGED
*/
@Override
public void updateStyle(Style style) throws Exception {
// TODO Auto-generated method stub
}
/* (non-Javadoc)
* @see it.geosolutions.android.map.database.SpatialDataSourceHandler#intersectionToBundleBBOX(java.lang.String, eu.geopaparazzi.spatialite.database.spatial.core.SpatialVectorTable, double, double, double, double)
*/
@Override
public ArrayList<Bundle> intersectionToBundleBBOX(String boundsSrid,
SpatialVectorTable spatialTable, double n, double s, double e,
double w) throws Exception {
return this.intersectionToBundleBBOX(boundsSrid, spatialTable, n, s, e, w,null,null);
}
@Override
public ArrayList<Map<String,String>> intersectionToMapBBOX(String boundsSrid,
SpatialVectorTable spatialTable, double n, double s, double e,
double w,Integer start,Integer limit) throws Exception{
Stmt stmt = generateBBoxQuery(boundsSrid, spatialTable, n, s, e, w,
start, limit);
ArrayList<Map<String,String>> features = new ArrayList<Map<String,String>>();
try {
//every row of the table (feature)
generateMap(spatialTable, stmt, features);
}catch(Exception ee){
Log.e("DATABASE","Error in database query:\nException:"+ee.getMessage());
throw ee;
} finally {
stmt.close();
}
return features;
}
@Override
public ArrayList<Feature> intersectionToFeatureListBBOX(String boundsSrid,
SpatialVectorTable spatialTable, double n, double s, double e,
double w,Integer start,Integer limit) throws Exception{
Stmt stmt = buildFeatureBBoxQuery(boundsSrid, spatialTable, n, s, e, w,
start, limit);
ArrayList<Feature> features = new ArrayList<Feature>();
try {
//every row of the table (feature)
generateAttributes(spatialTable, stmt, features,false);//TODO put out this
}catch(Exception ee){
Log.e("DATABASE","Error in database query:\nException:"+ee.getMessage());
throw ee;
} finally {
stmt.close();
}
return features;
}
@Override
public ArrayList<Feature> intersectionToCircle(String boundsSrid,
SpatialVectorTable spatialTable, double x, double y, double radius,
Integer start, Integer limit) throws Exception {
Stmt stmt = buildFeatureCircleQuery(boundsSrid, spatialTable, x, y, radius, start, limit);
ArrayList<Feature> features = new ArrayList<Feature>();
try {
//every row of the table (feature)
generateAttributes(spatialTable, stmt, features, false);//TODO put out this
}catch(Exception ee){
Log.e("DATABASE","Error in database query:\nException:"+ee.getMessage());
throw ee;
} finally {
stmt.close();
}
return features;
}
/**
* @param spatialTable
* @param stmt
* @param features
* @throws Exception
*/
private void generateMap(SpatialVectorTable spatialTable, Stmt stmt,
ArrayList<Map<String,String>> features) throws Exception {
while( stmt.step() ) {
int column_count = stmt.column_count();
Map<String,String> feature= new HashMap<String,String>();
for( int i = 0; i < column_count; i++ ) {
String cName = stmt.column_name(i);
//skip geometry
if (cName.equalsIgnoreCase(spatialTable.getGeomName())) {
continue;
}
feature.put(cName, stmt.column_string(i));
//add name->value pairs to the attribute ArrayList
}
features.add(feature);
//add the ArrayList
}
}
/**
* @param spatialTable
* @param stmt
* @param features
* @throws Exception
* @throws IOException
*/
public void generateAttributes(SpatialVectorTable spatialTable, Stmt stmt,
ArrayList<Feature> features,boolean includeGeometry) throws Exception{
try{
while( stmt.step() ) {
int column_count = stmt.column_count();
Feature feature = new Feature();
for( int i = 0; i < column_count; i++ ) {
Attribute attribute= new Attribute();
String cName = stmt.column_name(i);
//skip geometry
if (cName.equalsIgnoreCase(spatialTable.getGeomName() )|| cName.equalsIgnoreCase(DEFAULT_GEOMETRY_NAME)){
continue;
}
attribute.setName(cName);
attribute.setValue(stmt.column_string(i));
//add name->value pairs to the attribute ArrayList
feature.add(attribute);
}
features.add(feature);
//add the ArrayList
}
}catch(Exception e){
e.printStackTrace();
}
}
private Geometry generateGeometry(SpatialVectorTable spatialTable, Stmt stmt,
ArrayList<Feature> features,boolean includeGeometry) throws Exception{
WKBReader wkbReader = new WKBReader();
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(DEFAULT_GEOMETRY_NAME)) {
continue;
}else{
byte[] geomBytes = stmt.column_bytes(0);
Geometry geometry;
try {
geometry = wkbReader.read(geomBytes);
} catch (ParseException e) {
Log.e("DATABASE","Error reading geometry");
throw new Exception(e.getMessage());
}
return geometry;
}
}
}
return null;
}
/* (non-Javadoc)
* @see it.geosolutions.android.map.database.SpatialDataSourceHandler#getFeatureByAttribute(java.lang.String, java.lang.String, java.lang.String, java.lang.Object, int, boolean)
*/
@Override
public Geometry getGeometryByAttribute(String srid,SpatialVectorTable table, String attributeName,
String attributeValue, Integer start, Integer limit, boolean getGeometry) throws Exception {
Stmt stmt = generateQueryByAttributeForGeometry(srid, table, attributeName, attributeValue, start,limit);
ArrayList<Feature> features = new ArrayList<Feature>();
try {
return generateGeometry(table, stmt, features,true);
}catch(Exception ee){
Log.e("DATABASE","Error in database query:\nException:"+ee.getMessage());
throw ee;
} finally {
stmt.close();
}
}
@Override
public ArrayList<Feature> getFeaturesByAttribute(String srid,SpatialVectorTable table, String attributeName,
String attributeValue, Integer start, Integer limit, boolean getGeometry) throws Exception {
Stmt stmt = generateQueryByAttributeForFeature(srid, table, attributeName, attributeValue, start,limit);
ArrayList<Feature> features = new ArrayList<Feature>();
try {
//every row of the table (feature)
generateAttributes(table, stmt, features,false);
}catch(Exception ee){
Log.e("DATABASE","Error in database query:\nException:"+ee.getMessage());
throw ee;
} finally {
stmt.close();
}
return features;
}
/**
* Intersects a bounding box using spatial index
* @param destSrid
* @param table
* @param n
* @param s
* @param e
* @param w
* @param start
* @param limit
* @return
* @throws Exception
*/
private Stmt buildFeatureBBoxQuery( String destSrid, SpatialVectorTable table, double n, double s, double e, double w,Integer start,Integer limit ) throws Exception {
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("))AS ");
qSb.append(DEFAULT_GEOMETRY_NAME);
// 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(" )");
if(limit != null){
if(start !=null){
qSb.append(" ORDER BY ");
qSb.append(ORDER_BY_DEFAULT_FIELD);
qSb.append(" ");
}
qSb.append(" LIMIT ");
if(start != null){
qSb.append(start);
qSb.append(",");
qSb.append(start + limit);
}else{
qSb.append(limit);
}
}
qSb.append(";");
String q = qSb.toString();
Stmt stmt = db.prepare(q);
return stmt;
}
/**
* Intersect a circle using spatial index
* @param destSrid
* @param table
* @param x
* @param y
* @param radius
* @param start
* @param limit
* @return
* @throws Exception
*/
public Stmt buildFeatureCircleQuery(String destSrid, SpatialVectorTable table, double x,
double y, double radius, Integer start, Integer limit) throws Exception {
boolean doTransform = false;
if (!table.getSrid().equals(destSrid)) {
doTransform = true;
}
StringBuilder mbrSb = new StringBuilder();
if (doTransform)
mbrSb.append("ST_Transform(");
mbrSb.append("BuildCircleMBR(");
mbrSb.append(x);
mbrSb.append(" , ");
mbrSb.append(y);
mbrSb.append(" , ");
mbrSb.append(Double.toString(radius));
mbrSb.append(" , ");
mbrSb.append(destSrid);
if (doTransform) {
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("))AS ");
qSb.append(DEFAULT_GEOMETRY_NAME);
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(" )");
if(limit != null){
if(start !=null){
qSb.append(" ORDER BY ");
qSb.append(ORDER_BY_DEFAULT_FIELD);
qSb.append(" ");
}
qSb.append(" LIMIT ");
if(start != null){
qSb.append(start);
qSb.append(",");
qSb.append(start + limit);
}else{
qSb.append(limit);
}
}
qSb.append(";");
String q = qSb.toString();
Stmt stmt = db.prepare(q);
return stmt;
}
@Override
public ArrayList<Bundle> intersectionToCircleBOX(String boundsSrid,
SpatialVectorTable spatialTable, double x, double y, double radius)
throws Exception {
// TODO Auto-generated method stub
return null;
}
@Override
public ArrayList<Bundle> intersectionToCircleBOX(String boundsSrid,
SpatialVectorTable spatialTable, double x, double y, double radius,
Integer start, Integer limit) throws Exception {
// TODO Auto-generated method stub
return null;
}
@Override
public ArrayList<Bundle> intersectionToPolygonBOX(String boundsSrid,
SpatialVectorTable spatialTable,
ArrayList<Coordinates_Query> polygon_points) throws Exception {
// TODO Auto-generated method stub
return null;
}
@Override
public ArrayList<Bundle> intersectionToPolygonBOX(String boundsSrid,
SpatialVectorTable spatialTable,
ArrayList<Coordinates_Query> polygon_points, Integer start,
Integer limit) throws Exception {
// TODO Auto-generated method stub
return null;
}
@Override
public ArrayList<Feature> intersectionToPolygon(String boundsSrid,
SpatialVectorTable spatialTable, ArrayList<Coordinates_Query> polygon_points,
Integer start, Integer limit) throws Exception {
Stmt stmt = buildFeaturePolygonQuery(boundsSrid, spatialTable, polygon_points, start, limit);
ArrayList<Feature> features = new ArrayList<Feature>();
try {
//every row of the table (feature)
generateAttributes(spatialTable, stmt, features, false);//TODO put out this
}catch(Exception ee){
Log.e("DATABASE","Error in database query:\nException:"+ee.getMessage());
throw ee;
} finally {
stmt.close();
}
return features;
}
/**
* Intersect a polygon using spatial index.
* @param destSrid
* @param table
* @param polygon_points
* @param start
* @param limit
* @return
* @throws Exception
*/
public Stmt buildFeaturePolygonQuery(String destSrid,
SpatialVectorTable table,
ArrayList<Coordinates_Query> polygon_points, Integer start,
Integer limit) throws Exception {
boolean doTransform = false;
if (!table.getSrid().equals(destSrid)) {
doTransform = true;
}
StringBuilder mbrSb = new StringBuilder();
if (doTransform)
mbrSb.append("ST_Transform(");
mbrSb.append("PolygonFromText('POLYGON((");
for(int i = 0; i<polygon_points.size(); i++){
mbrSb.append(polygon_points.get(i).getX());
mbrSb.append(" ");
mbrSb.append(polygon_points.get(i).getY());
if(i<polygon_points.size()-1) {
mbrSb.append(" , ");
}
if(i==polygon_points.size()-1){
mbrSb.append(" , ");
mbrSb.append(polygon_points.get(0).getX());
mbrSb.append(" ");
mbrSb.append(polygon_points.get(0).getY());
}
}
mbrSb.append("))'");
mbrSb.append(",");
mbrSb.append(destSrid);
if (doTransform) {
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("))AS ");
qSb.append(DEFAULT_GEOMETRY_NAME);
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(" )");
if(limit != null){
if(start !=null){
qSb.append(" ORDER BY ");
qSb.append(ORDER_BY_DEFAULT_FIELD);
qSb.append(" ");
}
qSb.append(" LIMIT ");
if(start != null){
qSb.append(start);
qSb.append(",");
qSb.append(start + limit);
}else{
qSb.append(limit);
}
}
qSb.append(";");
String q = qSb.toString();
Log.v("Polygon",q);
Stmt stmt = db.prepare(q);
return stmt;
}
}