/* * 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.daos; import com.vividsolutions.jts.geom.Geometry; import java.util.HashMap; import java.util.LinkedHashMap; import java.util.List; import java.util.Locale; import eu.geopaparazzi.library.database.GPLog; import eu.geopaparazzi.library.features.Feature; import eu.geopaparazzi.library.util.types.EDataType; import eu.geopaparazzi.spatialite.database.spatial.SpatialiteSourcesManager; import eu.geopaparazzi.spatialite.database.spatial.core.databasehandlers.SpatialiteDatabaseHandler; import eu.geopaparazzi.spatialite.database.spatial.core.enums.GeometryType; import eu.geopaparazzi.spatialite.database.spatial.core.tables.SpatialVectorTable; import eu.geopaparazzi.spatialite.database.spatial.util.SpatialiteUtilities; import jsqlite.Database; import jsqlite.Exception; import jsqlite.Stmt; /** * Spatialite support methods. * <p/> * This class should contain a more user oriented tasks API. * * @author Andrea Antonello (www.hydrologis.com) */ @SuppressWarnings("nls") public class DaoSpatialite implements ISpatialiteTableAndFieldsNames { /** * Collects the fields of a given table. * <p/> * <br>- name of Field * <br>- type of field as defined in Database * * @param database the database to use. * @param tableName name of table to read. * @return the {@link HashMap} of fields: [name of field, type of field] * @throws Exception if something goes wrong. */ public static HashMap<String, String> collectTableFields(Database database, String tableName) throws Exception { HashMap<String, String> fieldNamesToTypeMap = new LinkedHashMap<String, String>(); String s_sql_command = "pragma table_info('" + tableName + "')"; String tableType = ""; String sqlCreationString = ""; Stmt statement = null; String name = ""; try { statement = database.prepare(s_sql_command); while (statement.step()) { name = statement.column_string(1); tableType = statement.column_string(2); sqlCreationString = statement.column_string(5); // pk // try to unify the data-types: varchar(??),int(11) mysql-syntax if (tableType.contains("int(")) tableType = "INTEGER"; if (tableType.contains("varchar(")) tableType = "TEXT"; // pk: 0 || 1;Data-TypeTEXT || DOUBLE || INTEGER || REAL || DATE || BLOB || // geometry-types fieldNamesToTypeMap.put(name, sqlCreationString + ";" + tableType.toUpperCase(Locale.US)); } } catch (jsqlite.Exception e_stmt) { GPLog.error("DAOSPATIALIE", "collectTableFields[" + tableName + "] sql[" + s_sql_command + "] db[" + database.getFilename() + "]", e_stmt ); } finally { if (statement != null) { statement.close(); } } return fieldNamesToTypeMap; } /** * Attemt to retrieve row-count and bounds for this geometry field. * * @param database the db to use. * @param tableName the table of the db to use. * @param geometryColumn the geometry field of the table to use. * @return 'rows_count;min_x,min_y,max_x,max_y;datetimestamp_now'. * @throws Exception if something goes wrong. */ public static String getGeometriesBoundsString(Database database, String tableName, String geometryColumn) throws Exception { StringBuilder queryBuilder = new StringBuilder(); String s_vector_extent = ""; // return the format used in DaoSpatialite.checkDatabaseTypeAndValidity() queryBuilder.append("SELECT count("); queryBuilder.append(geometryColumn); queryBuilder.append(")||';'||Min(MbrMinX("); queryBuilder.append(geometryColumn); queryBuilder.append("))||','||Min(MbrMinY("); queryBuilder.append(geometryColumn); queryBuilder.append("))||','||Max(MbrMaxX("); queryBuilder.append(geometryColumn); queryBuilder.append("))||','||Max(MbrMaxY("); queryBuilder.append(geometryColumn); queryBuilder.append("))||';'||strftime('%Y-%m-%dT%H:%M:%fZ','now')"); queryBuilder.append(" FROM \""); queryBuilder.append(tableName); queryBuilder.append("\" ;"); // ;617;7255796.59288944,246133.478270624,7395508.96772464,520956.218508861;2014-03-26T06:32:58.572Z String s_select_bounds = queryBuilder.toString(); Stmt statement = null; try { statement = database.prepare(s_select_bounds); if (statement.step()) { if (statement.column_string(0) != null) { // The geometries may be null, thus // returns null s_vector_extent = statement.column_string(0); return s_vector_extent; } } } catch (jsqlite.Exception e_stmt) { GPLog.error("DAOSPATIALIE", "spatialiteRetrieveBounds sql[" + s_select_bounds + "] db[" + database.getFilename() + "]", e_stmt); } finally { if (statement != null) statement.close(); } return s_vector_extent; } /** * Attemt to count geometry field. * returned the number of Geometries that are NOT NULL * - no recovery attemts should be done when this returns 0 * --- will abort attemts to recover if returns 0 * --- this speeds up the loading by 50% in my case * VECTOR_LAYERS_QUERY_MODE=3 : fragment_about 5 seconds [before fragment_about 10 seconds] * VECTOR_LAYERS_QUERY_MODE=0 : fragment_about 2 seconds * * @param database the db to use. * @param tableName the table of the db to use. * @param geometryColumn the geometry field of the table to use. * @return count of Geometries NOT NULL * @throws Exception if something goes wrong. */ public static int getGeometriesCount(Database database, String tableName, String geometryColumn) throws Exception { int i_count = 0; if ((tableName.equals("")) || (geometryColumn.equals(""))) return i_count; // SELECT CreateSpatialIndex('prov2008_s','Geometry'); String s_CountGeometries = "SELECT count('" + geometryColumn + "') FROM '" + tableName + "' WHERE '" + geometryColumn + "' IS NOT NULL;"; Stmt statement = null; try { statement = database.prepare(s_CountGeometries); if (statement.step()) { i_count = statement.column_int(0); return i_count; } } catch (jsqlite.Exception e_stmt) { GPLog.error("DAOSPATIALIE", "spatialiteCountGeometries sql[" + s_CountGeometries + "] db[" + database.getFilename() + "]", e_stmt); } finally { if (statement != null) statement.close(); } return i_count; } /** * Delete a list of features in the given database. * <p/> * <b>The features need to be from the same table</b> * * @param features the features list. * @throws Exception if something goes wrong. */ public static void deleteFeatures(List<Feature> features) throws Exception { Feature firstFeature = features.get(0); String databasePath = firstFeature.getDatabasePath(); SpatialiteDatabaseHandler databaseHandler = SpatialiteSourcesManager.INSTANCE.getExistingDatabaseHandlerByPath(databasePath); Database database = databaseHandler.getDatabase(); String tableName = firstFeature.getTableName(); StringBuilder sbIn = new StringBuilder(); sbIn.append("delete from \"").append(tableName); sbIn.append("\" where "); StringBuilder sb = new StringBuilder(); for (Feature feature : features) { sb.append(" OR "); sb.append(SpatialiteUtilities.SPATIALTABLE_ID_FIELD).append("="); sb.append(feature.getId()); } String valuesPart = sb.substring(4); sbIn.append(valuesPart); String updateQuery = sbIn.toString(); database.exec(updateQuery, null); } /** * Add a new spatial record by adding a geometry. * <p/> * <p>The other attributes will not be populated. * * @param geometry the geometry that will create the new record. * @param geometrySrid the srid of the geometry without the EPSG prefix. * @param spatialVectorTable the table into which to insert the record. * @throws Exception if something goes wrong. */ public static void addNewFeatureByGeometry(Geometry geometry, String geometrySrid, SpatialVectorTable spatialVectorTable) throws Exception { SpatialiteDatabaseHandler databaseHandler = SpatialiteSourcesManager.INSTANCE.getExistingDatabaseHandlerByTable(spatialVectorTable); Database database = databaseHandler.getDatabase(); String tableName = spatialVectorTable.getTableName(); String geometryFieldName = spatialVectorTable.getGeomName(); String srid = spatialVectorTable.getSrid(); int geomType = spatialVectorTable.getGeomType(); GeometryType geometryType = GeometryType.forValue(geomType); String geometryTypeCast = geometryType.getGeometryTypeCast(); String spaceDimensionsCast = geometryType.getSpaceDimensionsCast(); String multiSingleCast = geometryType.getMultiSingleCast(); String pkIgnoredField = SpatialiteUtilities.getIgnoredPkField(spatialVectorTable); // get list of non geom fields and default values String nonGeomFieldsNames = ""; String nonGeomFieldsValues = ""; for (String field : spatialVectorTable.getTableFieldNamesList()) { boolean ignore = SpatialiteUtilities.doIgnoreField(field, pkIgnoredField); if (!ignore) { EDataType tableFieldType = spatialVectorTable.getTableFieldType(field); if (tableFieldType != null) { nonGeomFieldsNames = nonGeomFieldsNames + "," + field; nonGeomFieldsValues = nonGeomFieldsValues + "," + tableFieldType.getDefaultValueForSql(); } } } boolean doTransform = true; if (srid.equals(geometrySrid)) { doTransform = false; } StringBuilder sbIn = new StringBuilder(); sbIn.append("insert into \"").append(tableName); sbIn.append("\" ("); sbIn.append(geometryFieldName); // add fields if (nonGeomFieldsNames.length() > 0) { sbIn.append(nonGeomFieldsNames); } sbIn.append(") values ("); if (doTransform) sbIn.append("ST_Transform("); if (multiSingleCast != null) sbIn.append(multiSingleCast).append("("); if (spaceDimensionsCast != null) sbIn.append(spaceDimensionsCast).append("("); if (geometryTypeCast != null) sbIn.append(geometryTypeCast).append("("); sbIn.append("GeomFromText('"); sbIn.append(geometry.toText()); sbIn.append("' , "); sbIn.append(geometrySrid); sbIn.append(")"); if (geometryTypeCast != null) sbIn.append(")"); if (spaceDimensionsCast != null) sbIn.append(")"); if (multiSingleCast != null) sbIn.append(")"); if (doTransform) { sbIn.append(","); sbIn.append(srid); sbIn.append(")"); } // add field default values if (nonGeomFieldsNames.length() > 0) { sbIn.append(nonGeomFieldsValues); } sbIn.append(")"); String insertQuery = sbIn.toString(); database.exec(insertQuery, null); } protected static void createImageField(SpatialVectorTable table) { String GEOPAP_IMG_TYPE = "GEOPAP_TEXTARRAY_IMG"; String GEOPAP_IMG_DEFAULT_NAME = "geopapimgs"; String GEOPAP_IMG_DEFAULT_VALUE = "'[]'"; SpatialiteDatabaseHandler spatialiteDatabaseHandler = SpatialiteSourcesManager.INSTANCE.getExistingDatabaseHandlerByTable(table); Database database = spatialiteDatabaseHandler.getDatabase(); String tableName = table.getTableName(); try { HashMap<String, String> names2fieldInfo = collectTableFields(database, table.getTableName()); String imageField = null; for (String name: names2fieldInfo.keySet()) { String typeInfo = names2fieldInfo.get(name); if (typeInfo.contains(GEOPAP_IMG_TYPE)) { imageField = name; break; } } if (imageField==null) { StringBuilder sqlImageField = new StringBuilder(); sqlImageField.append("ALTER TABLE ").append(tableName); sqlImageField.append(" ADD COLUMN "); sqlImageField.append(GEOPAP_IMG_DEFAULT_NAME); sqlImageField.append(" "); sqlImageField.append(GEOPAP_IMG_TYPE); sqlImageField.append(" DEFAULT ").append(GEOPAP_IMG_DEFAULT_VALUE); database.exec(sqlImageField.toString(), null); } } catch (Exception e) { e.printStackTrace(); } } /** * Updates the alphanumeric values of a feature in the given database. * * @param database the database. * @param feature the feature. * @throws Exception if something goes wrong. */ public static void updateFeatureAlphanumericAttributes(Database database, Feature feature) throws Exception { String tableName = feature.getTableName(); List<String> attributeNames = feature.getAttributeNames(); List<String> attributeValuesStrings = feature.getAttributeValuesStrings(); List<String> attributeTypes = feature.getAttributeTypes(); StringBuilder sbIn = new StringBuilder(); sbIn.append("update \"").append(tableName); sbIn.append("\" set "); StringBuilder sb = new StringBuilder(); for (int i = 0; i < attributeNames.size(); i++) { String fieldName = attributeNames.get(i); String value = attributeValuesStrings.get(i); String type = attributeTypes.get(i); boolean ignore = SpatialiteUtilities.doIgnoreField(fieldName); if (!ignore) { EDataType dataType = EDataType.getType4Name(type); if (dataType == EDataType.TEXT || dataType == EDataType.DATE) { value = escapeString(value); sb.append(" , ").append(fieldName).append("='").append(value).append("'"); } else if (value == null || "".equals(value)) { sb.append(" , ").append(fieldName).append("=NULL"); } else{ sb.append(" , ").append(fieldName).append("=").append(value); } } } String valuesPart = sb.substring(3); sbIn.append(" "); sbIn.append(valuesPart); sbIn.append(" where "); sbIn.append(SpatialiteUtilities.SPATIALTABLE_ID_FIELD); sbIn.append("="); sbIn.append(feature.getId()); String updateQuery = sbIn.toString(); database.exec(updateQuery, null); //SpatialVectorTable table = SpatialiteSourcesManager.INSTANCE.getTableFromFeature(feature); //createImageField(table); } private static String escapeString(String value) { return value.replaceAll("'", "''"); } /** * Updates the geometry of a feature in the given database. * * @throws Exception if something goes wrong. */ public static void updateFeatureGeometry(String id, Geometry geometry, String geometrySrid, SpatialVectorTable spatialVectorTable) throws Exception { SpatialiteDatabaseHandler databaseHandler = SpatialiteSourcesManager.INSTANCE.getExistingDatabaseHandlerByTable(spatialVectorTable); Database database = databaseHandler.getDatabase(); String tableName = spatialVectorTable.getTableName(); String geometryFieldName = spatialVectorTable.getGeomName(); String srid = spatialVectorTable.getSrid(); int geomType = spatialVectorTable.getGeomType(); GeometryType geometryType = GeometryType.forValue(geomType); String geometryTypeCast = geometryType.getGeometryTypeCast(); String spaceDimensionsCast = geometryType.getSpaceDimensionsCast(); String multiSingleCast = geometryType.getMultiSingleCast(); boolean doTransform = true; if (srid.equals(geometrySrid)) { doTransform = false; } StringBuilder sbIn = new StringBuilder(); sbIn.append("update \"").append(tableName); sbIn.append("\" set "); sbIn.append(geometryFieldName); sbIn.append(" = "); if (doTransform) sbIn.append("ST_Transform("); if (multiSingleCast != null) sbIn.append(multiSingleCast).append("("); if (spaceDimensionsCast != null) sbIn.append(spaceDimensionsCast).append("("); if (geometryTypeCast != null) sbIn.append(geometryTypeCast).append("("); sbIn.append("GeomFromText('"); sbIn.append(geometry.toText()); sbIn.append("' , "); sbIn.append(geometrySrid); sbIn.append(")"); if (geometryTypeCast != null) sbIn.append(")"); if (spaceDimensionsCast != null) sbIn.append(")"); if (multiSingleCast != null) sbIn.append(")"); if (doTransform) { sbIn.append(","); sbIn.append(srid); sbIn.append(")"); } sbIn.append(""); sbIn.append(" where "); sbIn.append(SpatialiteUtilities.SPATIALTABLE_ID_FIELD).append("="); sbIn.append(id); String insertQuery = sbIn.toString(); database.exec(insertQuery, null); } /** * Get the area and length in original units of a feature by its id. * * @param id the id of the feature, as defined by field * {@link eu.geopaparazzi.spatialite.database.spatial.util.SpatialiteUtilities#SPATIALTABLE_ID_FIELD} * @param spatialVectorTable the table in which the feature resides. * @return the array with [area, length]. * @throws Exception if something goes wrong. */ public static double[] getAreaAndLengthById(String id, SpatialVectorTable spatialVectorTable) throws Exception { SpatialiteDatabaseHandler databaseHandler = SpatialiteSourcesManager.INSTANCE.getExistingDatabaseHandlerByTable(spatialVectorTable); Database database = databaseHandler.getDatabase(); String tableName = spatialVectorTable.getTableName(); String geomName = spatialVectorTable.getGeomName(); StringBuilder sbIn = new StringBuilder(); sbIn.append("SELECT "); sbIn.append("Area(").append(geomName).append("),"); sbIn.append("Length(").append(geomName).append(")"); sbIn.append(" from \"").append(tableName); sbIn.append("\" where "); sbIn.append(SpatialiteUtilities.SPATIALTABLE_ID_FIELD).append(" = ").append(id); String selectQuery = sbIn.toString(); Stmt statement = null; try { statement = database.prepare(selectQuery); if (statement.step()) { double area = statement.column_double(0); double length = statement.column_double(1); return new double[]{area, length}; } } catch (jsqlite.Exception e_stmt) { GPLog.error("DAOSPATIALIE", "getAreaAndLengthById[" + tableName + "] sql[" + selectQuery + "] db[" + database.getFilename() + "]", e_stmt ); } finally { if (statement != null) { statement.close(); } } return null; } }