/* * 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 java.util.HashMap; /** * General sql query to retrieve vector data of the whole Database in 1 query * <p/> * - this is Spatialite4+ specfic and will be called in checkDatabaseTypeAndValidity * - invalid entries are filtered out (row_count > 0 and min/max x+y NOT NULL) * -- and will returned spatialVectorMap with the 2 Fields returned by this query * -- the result will be sorted with views first and Tables second * - Field-Names and use: * -- 'vector_key' : fields often needed and used in map.key [always valid] * -- 'vector_data' : fields NOT often needed and used in map.value [first portion and always valid] * -- 'vector_extent': fields NOT often needed and used in map.value [second portion and NOT always valid] * <p/> * Queries for Spatialite (all versions) at: * https://github.com/mj10777/Spatialite-Tasks-with-Sql-Scripts/wiki/VECTOR_LAYERS_QUERYS-geopaparazzi-specific * <p/> * Queries for RasterLite2 at: * https://github.com/geopaparazzi/Spatialite-Tasks-with-Sql-Scripts/wiki/RASTER_COVERAGES_QUERYS-geopaparazzi-specific * <p/> * Queries for GeoPackage R10 at: * https://github.com/geopaparazzi/Spatialite-Tasks-with-Sql-Scripts/wiki/GEOPACKAGE_QUERY_R10-geopaparazzi-specific * <p/> * <ol> * <li>3 Fields will be returned with the following structure</li> * <li>0 table_name: berlin_stadtteile</li> * <li>1: geometry_column - soldner_polygon</li> * <li>2: layer_type - SpatialView or AbstractSpatialTable</li> * <li>3: ROWID - AbstractSpatialTable: default ; when SpatialView or will be replaced</li> * <li>4: view_read_only - AbstractSpatialTable: -1 ; when SpatialView: 0=read_only or 1 writable</li> * <li>vector_data: Seperator: ';' 7 values</li> * <li>0: geometry_type - 3</li> * <li>1: coord_dimension - 2</li> * <li>2: srid - 3068</li> * <li>3: spatial_index_enabled - 0 or 1</li> * <li>4: rows 4 * <li>5: extent_min/max - Seperator ',' - 4 values * <li>5.1:extent_min_x - 20847.6171111586</li> * <li>5.2:extent_min_y - 18733.613614603</li> * <li>5.3:extent_max_x - 20847.6171111586</li> * <li>5.4:extent_max_y - 18733.613614603</li></li> * <li>6:last_verified - 2014-03-12T12:22:39.688Z</li> * </ol> * <p/> * Validity: s_vector_key.split(";"); must return the length of 5 * <p/> * Validity: s_vector_data.split(";"); must return the length of 7 * <p/> * sa_vector_data[5].split(","); must return the length of 4 * * @author Mark Johnson * @author Andrea Antonello - refactoring to enum */ public enum GeneralQueriesPreparer implements ISpatialiteTableAndFieldsNames { /** * The Sql-String to retrieve valid Vector-Layers from a Spatialite 4 Database. * <p/> * further documentation can be found here: * https://github.com/geopaparazzi/Spatialite-Tasks-with-Sql-Scripts/wiki/VECTOR_LAYERS_QUERYS-geopaparazzi-specific#VECTOR_LAYERS_QUERY_EXTENT_VALID_V4 */ VECTOR_LAYERS_QUERY_EXTENT_VALID_V4, /** * The Sql-String to retrieve invalid Vector-Layers from a Spatialite 4 Database. * <p/> * further documentation can be found here: * https://github.com/geopaparazzi/Spatialite-Tasks-with-Sql-Scripts/wiki/VECTOR_LAYERS_QUERYS-geopaparazzi-specific#VECTOR_LAYERS_QUERY_EXTENT_INVALID_V4 */ VECTOR_LAYERS_QUERY_EXTENT_INVALID_V4, /** * The Sql-String to retrieve a minimal information on a valid or invalid Vector-Layers from a Spatialite 4 Database. * <p/> * Used for debugging. * <p/> * further documentation can be found here: * https://github.com/geopaparazzi/Spatialite-Tasks-with-Sql-Scripts/wiki/VECTOR_LAYERS_QUERYS-geopaparazzi-specific#VECTOR_LAYERS_QUERY_EXTENT_LIST_V4 */ VECTOR_LAYERS_QUERY_EXTENT_LIST_V4, /** * The Sql-String to retrieve valid Vector-Layers from a Spatialite 3 Database. * <p/> * - that may have been changed with a Spatialite 4 software<br/> * - for spatialite 4.0 with non-working vector_layers_statistics, but still has a valid layers_statistics table<br/> * - This should only be needed for cases where `UpdateLayerStatistics` has failed * <p/> * further documentation can be found here: * https://github.com/geopaparazzi/Spatialite-Tasks-with-Sql-Scripts/wiki/VECTOR_LAYERS_QUERYS-geopaparazzi-specific#LAYERS_QUERY_EXTENT_VALID_V4 */ LAYERS_QUERY_EXTENT_VALID_V4, /** * The Sql-String to retrieve invalid Vector-Layers from a Spatialite 3 Database. * <p/> * - that may have been changed with a Spatialite 4 software<br/> * - for spatialite 4.0 with non-working vector_layers_statistics, but still has a valid layers_statistics table<br/> * - This should only be needed for cases where `UpdateLayerStatistics` has failed<br/> * <p/> * further documentation can be found here: * https://github.com/geopaparazzi/Spatialite-Tasks-with-Sql-Scripts/wiki/VECTOR_LAYERS_QUERYS-geopaparazzi-specific#LAYERS_QUERY_EXTENT_INVALID_V4 */ LAYERS_QUERY_EXTENT_INVALID_V4, /** * The Sql-String to retrieve valid Vector-Layers from a Spatialite 3 Database. * <p/> * - that may have been changed with a Spatialite 4 software<br/> * - for spatialite 4.0 with non-working vector_layers_statistics, but still has a valid layers_statistics table<br/> * - This should only be needed for cases where `UpdateLayerStatistics` has failed<br/> * - used for debugging<br/> * <p/> * further documentation can be found here: * https://github.com/geopaparazzi/Spatialite-Tasks-with-Sql-Scripts/wiki/VECTOR_LAYERS_QUERYS-geopaparazzi-specific#LAYERS_QUERY_EXTENT_LIST_V4 */ LAYERS_QUERY_EXTENT_LIST_V4, /** * The Sql-String to retrieve a minimal information on a valid or invalid Vector-Layers from a Spatialite 3 Database. * <p/> * - that may have been changed with a Spatialite 4 software<br/> * - for spatialite 4.0 with non-working vector_layers_statistics, but still has a valid layers_statistics table<br/> * - used for debugging<br/> * <p/> * Results will be returned in a format used by Spatialite 4 Databases * further documentation can be found here: * https://github.com/geopaparazzi/Spatialite-Tasks-with-Sql-Scripts/wiki/VECTOR_LAYERS_QUERYS-geopaparazzi-specific#LAYERS_QUERY_EXTENT_VALID_V3 */ LAYERS_QUERY_EXTENT_VALID_V3, /** * The Sql-String to retrieve invalid Vector-Layers from a Spatialite 3 Database. * <p/> * - for spatialite 2.4 until 3.1.0 [Tables-Only]<br/> * <p/> * Results will be returned in a format used by Spatialite 4 Databases * further documentation can be found here: * https://github.com/geopaparazzi/Spatialite-Tasks-with-Sql-Scripts/wiki/VECTOR_LAYERS_QUERYS-geopaparazzi-specific#LAYERS_QUERY_EXTENT_INVALID_V3 */ LAYERS_QUERY_EXTENT_INVALID_V3, /** * The Sql-String to retrieve a minimal information on a valid or invalid Vector-Layers from a Spatialite 3 Database. * <p/> * - for spatialite 2.4 until 3.1.0 [Tables-Only]<br/> * - used for debugging<br/> * <p/> * further documentation can be found here: * https://github.com/geopaparazzi/Spatialite-Tasks-with-Sql-Scripts/wiki/VECTOR_LAYERS_QUERYS-geopaparazzi-specific#LAYERS_QUERY_EXTENT_LIST_V3 */ LAYERS_QUERY_EXTENT_LIST_V3, /** * The Sql-String to retrieve valid Vector-Layers from a Spatialite 3 Database. * <p/> * - for spatialite 2.4 until 3.1.0 [Views-Only]<br/> * <p/> * Results will be returned in a format used by Spatialite 4 Databases * further documentation can be found here: * https://github.com/geopaparazzi/Spatialite-Tasks-with-Sql-Scripts/wiki/VECTOR_LAYERS_QUERYS-geopaparazzi-specific#VIEWS_QUERY_EXTENT_VALID_V3 */ VIEWS_QUERY_EXTENT_VALID_V3, /** * The Sql-String to retrieve invalid Vector-Layers from a Spatialite 3 Database. * <p/> * - for spatialite 2.4 until 3.1.0 [Views-Only]<br/> * <p/> * Results will be returned in a format used by Spatialite 4 Databases * further documentation can be found here: * https://github.com/geopaparazzi/Spatialite-Tasks-with-Sql-Scripts/wiki/VECTOR_LAYERS_QUERYS-geopaparazzi-specificVIEWS_QUERY_EXTENT_INVALID_V3 */ VIEWS_QUERY_EXTENT_INVALID_V3, /** * The Sql-String to retrieve a minimal information on a valid or invalid Vector-Layers from a Spatialite 3 Database. * <p/> * - for spatialite 2.4 until 3.1.0 [Views-Only]<br/> * - used for debugging<br/> * <p/> * further documentation can be found here: * https://github.com/geopaparazzi/Spatialite-Tasks-with-Sql-Scripts/wiki/VECTOR_LAYERS_QUERYS-geopaparazzi-specificVIEWS_QUERY_EXTENT_LIST_V3 */ VIEWS_QUERY_EXTENT_LIST_V3, /** * The Sql-String to retrieve valid RasterLite2-Layers from a Spatialite 4 Database. * <p/> * Results will be returned in a format used by Spatialite 4 Databases * further documentation can be found here: * https://github.com/geopaparazzi/Spatialite-Tasks-with-Sql-Scripts/wiki/RASTER_COVERAGES_QUERYS-geopaparazzi-specific#RASTER_COVERAGES_QUERY_EXTENT_VALID_V42 */ RASTER_COVERAGES_QUERY_EXTENT_VALID_V42, /** * The Sql-String to retrieve invalid RasterLite2-Layers from a Spatialite 4 Database. * <p/> * Results will be returned in a format used by Spatialite 4 Databases * further documentation can be found here: * https://github.com/geopaparazzi/Spatialite-Tasks-with-Sql-Scripts/wiki/RASTER_COVERAGES_QUERYS-geopaparazzi-specific#RASTER_COVERAGES_QUERY_EXTENT_INVALID_V42 */ RASTER_COVERAGES_QUERY_EXTENT_INVALID_V42, /** * The Sql-String to retrieve a minimal information on a valid or invalid Rasterlite2-Layers from a Spatialite 4 Database. * <p/> * - used for debugging * <p/> * https://github.com/geopaparazzi/Spatialite-Tasks-with-Sql-Scripts/wiki/RASTER_COVERAGES_QUERYS-geopaparazzi-specificRASTER_COVERAGES_QUERY_EXTENT_LIST_V42 */ RASTER_COVERAGES_QUERY_EXTENT_LIST_V42, /** * The Sql-String to retrieve valid SPL_Geopackage-Layers from a SPL_Geopackage Revision 10 Database. * <p/> * Results will be returned in a format used by Spatialite 4 Databases * https://github.com/geopaparazzi/Spatialite-Tasks-with-Sql-Scripts/wiki/GEOPACKAGE_QUERY_R10-geopaparazzi-specific#GEOPACKAGE_QUERY_EXTENT_VALID_R10 */ GEOPACKAGE_QUERY_EXTENT_VALID_R10, /** * The Sql-String to retrieve valid SPL_Geopackage-Layers from a SPL_Geopackage Revision 10 Database. * <p/> * Results will be returned in a format used by Spatialite 4 Databases * further documentation can be found here: * https://github.com/geopaparazzi/Spatialite-Tasks-with-Sql-Scripts/wiki/GEOPACKAGE_QUERY_R10-geopaparazzi-specific#GEOPACKAGE_QUERY_EXTENT_INVALID_R10 */ GEOPACKAGE_QUERY_EXTENT_INVALID_R10, /** * The Sql-String to retrieve a minimal information on a valid or invalid SPL_Geopackage-Layers from a SPL_Geopackage Revision 10 Database. * <p/> * - used for debugging * <p/> * further documentation can be found here: * https://github.com/geopaparazzi/Spatialite-Tasks-with-Sql-Scripts/wiki/GEOPACKAGE_QUERY_R10-geopaparazzi-specificGEOPACKAGE_QUERY_EXTENT_LIST_R10 */ GEOPACKAGE_QUERY_EXTENT_LIST_R10; private String VIEWS_QUERY_EXTENT_INVALID = ""; private HashMap<String, String> queriesMap = new HashMap<String, String>(); private GeneralQueriesPreparer() { String VECTOR_LAYERS_QUERY_BASE = ""; { StringBuilder sb_query = new StringBuilder(); sb_query.append("SELECT DISTINCT "); sb_query.append(METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME + ".table_name"); sb_query.append("||';'||" + METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME + ".geometry_column"); sb_query.append("||';'||" + METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME + "." + "layer_type"); sb_query.append("||';ROWID;-1'"); sb_query.append(" AS vector_key," + METADATA_VECTOR_LAYERS_TABLE_NAME + "." + "geometry_type"); sb_query.append("||';'||" + METADATA_VECTOR_LAYERS_TABLE_NAME + ".coord_dimension"); sb_query.append("||';'||" + METADATA_VECTOR_LAYERS_TABLE_NAME + "." + "srid"); sb_query.append("||';'||" + METADATA_VECTOR_LAYERS_TABLE_NAME + ".spatial_index_enabled||';' AS vector_data,"); VECTOR_LAYERS_QUERY_BASE = sb_query.toString(); } String LAYERS_QUERY_BASE_V4 = ""; String VECTOR_KEY_BASE = ""; { LAYERS_QUERY_BASE_V4 = VECTOR_LAYERS_QUERY_BASE.replace(METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME, METADATA_LAYER_STATISTICS_TABLE_NAME); LAYERS_QUERY_BASE_V4 = LAYERS_QUERY_BASE_V4.replace(METADATA_LAYER_STATISTICS_TABLE_NAME + ".layer_type", METADATA_VECTOR_LAYERS_TABLE_NAME + ".layer_type"); StringBuilder sb_query = new StringBuilder(); sb_query.append(" AS vector_key,CASE"); // 0 of second field sb_query.append(" WHEN type = 'GEOMETRY' THEN '0'"); sb_query.append(" WHEN type = 'POINT' THEN '1'"); sb_query.append(" WHEN type = 'LINESTRING' THEN '2'"); sb_query.append(" WHEN type = 'POLYGON' THEN '3'"); sb_query.append(" WHEN type = 'MULTIPOINT' THEN '4'"); sb_query.append(" WHEN type = 'MULTILINESTRING' THEN '5'"); sb_query.append(" WHEN type = 'MULTIPOLYGON' THEN '6'"); sb_query.append(" WHEN type = 'GEOMETRYCOLLECTION' THEN '7'"); sb_query.append(" END"); // 0 sb_query.append("||';'||CASE"); // 2 sb_query.append(" WHEN ((coord_dimension = '2') OR (coord_dimension = 'XY')) THEN '2'"); sb_query.append(" WHEN ((coord_dimension = '3') OR (coord_dimension = 'XYZ') OR (coord_dimension = 'XYM')) THEN '3'"); sb_query.append(" WHEN ((coord_dimension = '4') OR (coord_dimension = 'XYZM')) THEN '4'"); sb_query.append(" END"); // 0 sb_query.append("||';'||srid"); // 3 sb_query.append("||';'||spatial_index_enabled||';' AS vector_data,"); // 4 VECTOR_KEY_BASE = sb_query.toString(); } String LAYERS_QUERY_BASE_V3 = ""; { StringBuilder sb_query = new StringBuilder(); // SELECT // f_table_name,f_geometry_column,geometry_type,coord_dimension,srid,spatial_index_enabled // FROM geometry_columns; // SELECT f_table_name,f_geometry_column,type,coord_dimension,srid,spatial_index_enabled // FROM geometry_columns sb_query.append("SELECT DISTINCT "); sb_query.append(" f_table_name"); // 0 of 1st field sb_query.append("||';'||f_geometry_column"); // 1 of 1st field sb_query.append("||';'||'AbstractSpatialTable'"); // 2 of 1st field sb_query.append("||';ROWID;-1'"); // 3+4 of 1st field sb_query.append(VECTOR_KEY_BASE); LAYERS_QUERY_BASE_V3 = sb_query.toString(); } String VIEWS_QUERY_BASE_V3 = ""; { StringBuilder sb_query = new StringBuilder(); sb_query.append("SELECT DISTINCT"); sb_query.append(" view_name"); // 0 of 1st field sb_query.append("||';'||view_geometry"); // 1 of 1st field sb_query.append("||';'||'SpatialView'"); // 2 of 1st field sb_query.append("||';ROWID;-1'"); // 3+4 of 1st field sb_query.append(VECTOR_KEY_BASE); VIEWS_QUERY_BASE_V3 = sb_query.toString(); } String VECTOR_LAYERS_QUERY_FROM = ""; { // sb_query.append(" FROM FROM geometry_columns ORDER BY f_table_name ASC,f_geometry_column"; StringBuilder sb_query = new StringBuilder(); sb_query.append(" FROM " + METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME + " INNER JOIN " + METADATA_VECTOR_LAYERS_TABLE_NAME); sb_query.append(" ON " + METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME + ".table_name"); sb_query.append(" = " + METADATA_VECTOR_LAYERS_TABLE_NAME + ".table_name"); sb_query.append(" AND " + METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME + ".geometry_column"); sb_query.append(" = " + METADATA_VECTOR_LAYERS_TABLE_NAME + ".geometry_column"); VECTOR_LAYERS_QUERY_FROM = sb_query.toString(); } String LAYERS_QUERY_FROM_V3 = ""; String LAYERS_QUERY_FROM_V4 = ""; String VIEWS_QUERY_FROM_V3 = ""; { StringBuilder sb_query = new StringBuilder(); // V2.4: SELECT // raster_layer,table_name,geometry_column,row_count,extent_min_x,extent_min_y,extent_max_x,extent_max_y // FROM layer_statistics sb_query.append(" FROM " + METADATA_GEOMETRY_COLUMNS_TABLE_NAME + " INNER JOIN " + METADATA_LAYER_STATISTICS_TABLE_NAME); sb_query.append(" ON " + METADATA_GEOMETRY_COLUMNS_TABLE_NAME + ".f_table_name"); sb_query.append(" = " + METADATA_LAYER_STATISTICS_TABLE_NAME + ".table_name"); sb_query.append(" AND " + METADATA_GEOMETRY_COLUMNS_TABLE_NAME + ".f_geometry_column"); sb_query.append(" = " + METADATA_LAYER_STATISTICS_TABLE_NAME + ".geometry_column"); LAYERS_QUERY_FROM_V3 = sb_query.toString(); VIEWS_QUERY_FROM_V3 = LAYERS_QUERY_FROM_V3.replace(METADATA_GEOMETRY_COLUMNS_TABLE_NAME, METADATA_VIEWS_GEOMETRY_COLUMNS_TABLE_NAME); VIEWS_QUERY_FROM_V3 = VIEWS_QUERY_FROM_V3.replace(".f_table_name", ".view_name"); VIEWS_QUERY_FROM_V3 = VIEWS_QUERY_FROM_V3.replace(".f_geometry_column", ".view_geometry"); // VIEWS_QUERY_FROM_V3 will be continued after finishing LAYERS_QUERY_FROM_V4 sb_query.append(" INNER JOIN " + METADATA_VECTOR_LAYERS_TABLE_NAME); sb_query.append(" ON " + METADATA_VECTOR_LAYERS_TABLE_NAME + ".table_name"); sb_query.append(" = " + METADATA_LAYER_STATISTICS_TABLE_NAME + ".table_name"); sb_query.append(" AND " + METADATA_VECTOR_LAYERS_TABLE_NAME + ".geometry_column"); sb_query.append(" = " + METADATA_LAYER_STATISTICS_TABLE_NAME + ".geometry_column"); LAYERS_QUERY_FROM_V4 = sb_query.toString(); } { StringBuilder sb_query = new StringBuilder(); sb_query.append(VIEWS_QUERY_FROM_V3); sb_query.append(" INNER JOIN " + METADATA_GEOMETRY_COLUMNS_TABLE_NAME); sb_query.append(" ON " + METADATA_GEOMETRY_COLUMNS_TABLE_NAME + ".f_table_name"); sb_query.append(" = " + METADATA_VIEWS_GEOMETRY_COLUMNS_TABLE_NAME + ".f_table_name"); sb_query.append(" AND " + METADATA_GEOMETRY_COLUMNS_TABLE_NAME + ".f_geometry_column"); sb_query.append(" = " + METADATA_VIEWS_GEOMETRY_COLUMNS_TABLE_NAME + ".f_geometry_column"); // VIEWS_QUERY_FROM_V3 is now compleate VIEWS_QUERY_FROM_V3 = sb_query.toString(); } String VECTOR_LAYERS_QUERY_EXTENT_VALID = ""; { StringBuilder sb_query = new StringBuilder(); // if the record is invalid, only this field will be null // 'vector_key' and 'vector_data' will be use to attempt to recover from the error. sb_query.append(METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME + ".row_count"); // 0 sb_query.append("||';'||" + METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME + ".extent_min_x"); // 1.0 sb_query.append("||','||" + METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME + ".extent_min_y"); // 1.1 sb_query.append("||','||" + METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME + ".extent_max_x"); // 1.2 sb_query.append("||','||" + METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME + ".extent_max_y"); // 1.3 sb_query.append("||';'||" + METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME + ".last_verified AS vector_extent"); // 2 VECTOR_LAYERS_QUERY_EXTENT_VALID = sb_query.toString(); } String LAYERS_QUERY_EXTENT_VALID = ""; String VECTOR_LAYERS_QUERY_EXTENT_INVALID = ""; String LAYERS_QUERY_EXTENT_INVALID = ""; { LAYERS_QUERY_EXTENT_VALID = VECTOR_LAYERS_QUERY_EXTENT_VALID.replace(METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME, METADATA_LAYER_STATISTICS_TABLE_NAME); LAYERS_QUERY_EXTENT_VALID = LAYERS_QUERY_EXTENT_VALID.replace(METADATA_LAYER_STATISTICS_TABLE_NAME + ".last_verified", "strftime('%Y-%m-%dT%H:%M:%fZ','now')"); StringBuilder sb_query = new StringBuilder(); // if the record is invalid, only this field will what is invalid // - where 'field_name' is shown, that field is invalid sb_query.append("CASE WHEN " + METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME + ".row_count IS NULL THEN 'row_count' ELSE "); // 0 sb_query.append(METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME + ".row_count END "); // 0 sb_query.append("||';'||CASE WHEN " + METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME + ".extent_min_x IS NULL THEN 'extent_min_x' ELSE "); // 1.0 sb_query.append(METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME + ".extent_min_x END "); // 1.0 sb_query.append("||','||CASE WHEN " + METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME + ".extent_min_y IS NULL THEN 'extent_min_y' ELSE "); // 1.1 sb_query.append(METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME + ".extent_min_y END "); // 1.1 sb_query.append("||','||CASE WHEN " + METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME + ".extent_max_x IS NULL THEN 'extent_max_x' ELSE "); // 1.2 sb_query.append(METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME + ".extent_max_x END "); // 1.2 sb_query.append("||','||CASE WHEN " + METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME + ".extent_max_y IS NULL THEN 'extent_max_y' ELSE "); // 1.3 sb_query.append(METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME + ".extent_max_y END "); // 1.3 // LAYERS_STATISTICS has no last_verified. Store result now and the continue to append LAYERS_QUERY_EXTENT_INVALID = sb_query.toString(); LAYERS_QUERY_EXTENT_INVALID = LAYERS_QUERY_EXTENT_INVALID.replace(METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME, METADATA_LAYER_STATISTICS_TABLE_NAME); sb_query.append("||';'||CASE WHEN " + METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME + ".last_verified IS NULL THEN 'last_verified' ELSE "); // 2 sb_query.append(METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME + ".last_verified END AS vector_extent"); // 2 VECTOR_LAYERS_QUERY_EXTENT_INVALID = sb_query.toString(); sb_query = new StringBuilder(); sb_query.append(LAYERS_QUERY_EXTENT_INVALID); sb_query.append("||';'||strftime('%Y-%m-%dT%H:%M:%fZ','now') AS vector_extent"); // 2 LAYERS_QUERY_EXTENT_INVALID = sb_query.toString(); VIEWS_QUERY_EXTENT_INVALID = LAYERS_QUERY_EXTENT_INVALID.replace(METADATA_LAYER_STATISTICS_TABLE_NAME, METADATA_VIEWS_GEOMETRY_COLUMNS_TABLE_NAME); } String VECTOR_LAYERS_QUERY_ORDER = ""; { StringBuilder sb_query = new StringBuilder(); // first Views (Spatialview) then tables (AbstractSpatialTable), then Table-Name/Column sb_query.append(" ORDER BY " + METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME + "." + "layer_type DESC"); sb_query.append("," + METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME + "." + "table_name ASC"); sb_query.append("," + METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME + "." + "geometry_column ASC"); VECTOR_LAYERS_QUERY_ORDER = sb_query.toString(); } String LAYERS_QUERY_ORDER_V3 = ""; { StringBuilder sb_query = new StringBuilder(); sb_query.append(" ORDER BY " + METADATA_LAYER_STATISTICS_TABLE_NAME + "." + "table_name ASC"); sb_query.append("," + METADATA_LAYER_STATISTICS_TABLE_NAME + "." + "geometry_column ASC"); LAYERS_QUERY_ORDER_V3 = sb_query.toString(); } String LAYERS_QUERY_ORDER_V4 = ""; { StringBuilder sb_query = new StringBuilder(); sb_query.append(" ORDER BY " + METADATA_VECTOR_LAYERS_TABLE_NAME + "." + "layer_type DESC"); sb_query.append("," + METADATA_VECTOR_LAYERS_TABLE_NAME + "." + "table_name ASC"); sb_query.append("," + METADATA_VECTOR_LAYERS_TABLE_NAME + "." + "geometry_column ASC"); LAYERS_QUERY_ORDER_V4 = sb_query.toString(); } { StringBuilder sb_query = new StringBuilder(); sb_query.append(VECTOR_LAYERS_QUERY_BASE); sb_query.append(VECTOR_LAYERS_QUERY_FROM); sb_query.append(VECTOR_LAYERS_QUERY_ORDER); String query = sb_query.toString(); // remove comma - last field query = query.replace("AS vector_data,", "AS vector_data"); queriesMap.put("VECTOR_LAYERS_QUERY_EXTENT_LIST_V4", query); } { StringBuilder sb_query = new StringBuilder(); sb_query.append(LAYERS_QUERY_BASE_V3); sb_query.append(LAYERS_QUERY_FROM_V3); sb_query.append(LAYERS_QUERY_ORDER_V3); String query = sb_query.toString(); // remove comma - last field query = query.replace("AS vector_data,", "AS vector_data"); queriesMap.put("LAYERS_QUERY_EXTENT_LIST_V3", query); } { StringBuilder sb_query = new StringBuilder(); sb_query.append(VIEWS_QUERY_BASE_V3); sb_query.append(VIEWS_QUERY_FROM_V3); sb_query.append(LAYERS_QUERY_ORDER_V3); String query = sb_query.toString(); // remove comma - last field query = query.replace("AS vector_data,", "AS vector_data"); queriesMap.put("VIEWS_QUERY_EXTENT_LIST_V3", query); } { StringBuilder sb_query = new StringBuilder(); sb_query.append(LAYERS_QUERY_BASE_V4); sb_query.append(LAYERS_QUERY_FROM_V4); sb_query.append(LAYERS_QUERY_ORDER_V4); String query = sb_query.toString(); query = query.replace("AS vector_data,", "AS vector_data"); // remove queriesMap.put("LAYERS_QUERY_EXTENT_LIST_V4", query); } String VECTOR_LAYERS_QUERY_WHERE = ""; { StringBuilder sb_query = new StringBuilder(); // if the creation of a spatial-view fails, a record may exist with 'row_count=NULL': this // is an invalid record and must be ignored sb_query.append(" WHERE (" + METADATA_VECTOR_LAYERS_TABLE_NAME + ".spatial_index_enabled = 1)"); sb_query.append(" AND (" + METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME + ".row_count IS NOT NULL)"); sb_query.append(" AND ((" + METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME + ".row_count = 0)"); sb_query.append(" OR ((" + METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME + ".extent_min_x IS NOT NULL)"); sb_query.append(" AND (" + METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME + ".extent_min_y IS NOT NULL)"); sb_query.append(" AND (" + METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME + ".extent_max_x IS NOT NULL)"); sb_query.append(" AND (" + METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME + ".extent_max_y IS NOT NULL)))"); VECTOR_LAYERS_QUERY_WHERE = sb_query.toString(); } String LAYERS_QUERY_WHERE = ""; { // 'vector_layers.' to 'geometry_columns.' - without changing 'vector_layers_statistics.' LAYERS_QUERY_WHERE = VECTOR_LAYERS_QUERY_WHERE.replace(METADATA_VECTOR_LAYERS_TABLE_NAME + ".", METADATA_GEOMETRY_COLUMNS_TABLE_NAME + "."); LAYERS_QUERY_WHERE = LAYERS_QUERY_WHERE.replace(METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME, METADATA_LAYER_STATISTICS_TABLE_NAME); StringBuilder sb_query = new StringBuilder(); sb_query.append(VECTOR_LAYERS_QUERY_BASE); sb_query.append(VECTOR_LAYERS_QUERY_EXTENT_VALID); sb_query.append(VECTOR_LAYERS_QUERY_FROM); sb_query.append(VECTOR_LAYERS_QUERY_WHERE); sb_query.append(VECTOR_LAYERS_QUERY_ORDER); // priority_marks_joined_lincoln;geometry;AbstractSpatialTable;ROWID 1;2;2913;1 NULL String query = sb_query.toString(); queriesMap.put("VECTOR_LAYERS_QUERY_EXTENT_VALID_V4", query); } { StringBuilder sb_query = new StringBuilder(); sb_query.append(LAYERS_QUERY_BASE_V3); sb_query.append(LAYERS_QUERY_EXTENT_VALID); sb_query.append(LAYERS_QUERY_FROM_V3); sb_query.append(LAYERS_QUERY_WHERE); sb_query.append(LAYERS_QUERY_ORDER_V3); // priority_marks_joined_lincoln;geometry;AbstractSpatialTable;ROWID 1;2;2913;1 NULL String query = sb_query.toString(); queriesMap.put("LAYERS_QUERY_EXTENT_VALID_V3", query); } { StringBuilder sb_query = new StringBuilder(); sb_query.append(VIEWS_QUERY_BASE_V3); sb_query.append(LAYERS_QUERY_EXTENT_VALID); sb_query.append(VIEWS_QUERY_FROM_V3); sb_query.append(LAYERS_QUERY_WHERE); sb_query.append(LAYERS_QUERY_ORDER_V3); // priority_marks_joined_lincoln;geometry;AbstractSpatialTable;ROWID 1;2;2913;1 NULL String query = sb_query.toString(); queriesMap.put("VIEWS_QUERY_EXTENT_VALID_V3", query); } { StringBuilder sb_query = new StringBuilder(); sb_query.append(LAYERS_QUERY_BASE_V4); sb_query.append(LAYERS_QUERY_EXTENT_VALID); sb_query.append(LAYERS_QUERY_FROM_V4); sb_query.append(LAYERS_QUERY_WHERE); sb_query.append(LAYERS_QUERY_ORDER_V4); // priority_marks_joined_lincoln;geometry;AbstractSpatialTable;ROWID 1;2;2913;1 NULL String query = sb_query.toString(); queriesMap.put("LAYERS_QUERY_EXTENT_VALID_V4", query); } { StringBuilder sb_query = new StringBuilder(); // if the creation of a spatial-view fails, a record may exist with 'row_count=NULL': this // is an invalid record and must be ignored sb_query.append(" WHERE (" + METADATA_VECTOR_LAYERS_TABLE_NAME + ".spatial_index_enabled = 0)"); sb_query.append(" OR (" + METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME + ".row_count IS NULL)"); sb_query.append(" OR ((" + METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME + ".row_count > 0)"); sb_query.append(" AND ((" + METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME + ".extent_min_x IS NULL)"); sb_query.append(" OR (" + METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME + ".extent_min_y IS NULL)"); sb_query.append(" OR (" + METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME + ".extent_max_x IS NULL)"); sb_query.append(" OR (" + METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME + ".extent_max_y IS NULL)))"); VECTOR_LAYERS_QUERY_WHERE = sb_query.toString(); } { // 'vector_layers.' to 'geometry_columns.' - without changing 'vector_layers_statistics.' LAYERS_QUERY_WHERE = VECTOR_LAYERS_QUERY_WHERE.replace(METADATA_VECTOR_LAYERS_TABLE_NAME + ".", METADATA_GEOMETRY_COLUMNS_TABLE_NAME + "."); LAYERS_QUERY_WHERE = LAYERS_QUERY_WHERE.replace(METADATA_VECTOR_LAYERS_STATISTICS_TABLE_NAME, METADATA_LAYER_STATISTICS_TABLE_NAME); StringBuilder sb_query = new StringBuilder(); sb_query.append(VECTOR_LAYERS_QUERY_BASE); sb_query.append(VECTOR_LAYERS_QUERY_EXTENT_INVALID); sb_query.append(VECTOR_LAYERS_QUERY_FROM); sb_query.append(VECTOR_LAYERS_QUERY_WHERE); sb_query.append(VECTOR_LAYERS_QUERY_ORDER); String query = sb_query.toString(); queriesMap.put("VECTOR_LAYERS_QUERY_EXTENT_INVALID_V4", query); } { StringBuilder sb_query = new StringBuilder(); sb_query.append(LAYERS_QUERY_BASE_V3); sb_query.append(LAYERS_QUERY_EXTENT_INVALID); sb_query.append(LAYERS_QUERY_FROM_V3); sb_query.append(LAYERS_QUERY_WHERE); sb_query.append(LAYERS_QUERY_ORDER_V3); String query = sb_query.toString(); queriesMap.put("LAYERS_QUERY_EXTENT_INVALID_V3", query); } { StringBuilder sb_query = new StringBuilder(); sb_query.append(VIEWS_QUERY_BASE_V3); sb_query.append(LAYERS_QUERY_EXTENT_VALID); sb_query.append(VIEWS_QUERY_FROM_V3); sb_query.append(LAYERS_QUERY_WHERE); sb_query.append(LAYERS_QUERY_ORDER_V3); // priority_marks_joined_lincoln;geometry;AbstractSpatialTable;ROWID 1;2;2913;1 NULL String query = sb_query.toString(); queriesMap.put("VIEWS_QUERY_EXTENT_INVALID_V3", query); } { StringBuilder sb_query = new StringBuilder(); sb_query.append(LAYERS_QUERY_BASE_V4); sb_query.append(LAYERS_QUERY_EXTENT_INVALID); sb_query.append(LAYERS_QUERY_FROM_V4); sb_query.append(LAYERS_QUERY_WHERE); sb_query.append(LAYERS_QUERY_ORDER_V4); String query = sb_query.toString(); queriesMap.put("LAYERS_QUERY_EXTENT_INVALID_V4", query); } { // ------------------- // end of building of Spatialite Queries // ------------------- // RasterLite2 support - begin // ------------------- StringBuilder sb_query = new StringBuilder(); sb_query.append("SELECT DISTINCT "); sb_query.append("coverage_name"); // 0 of 1st field sb_query.append("||';'||compression"); // 1 of 1st field sb_query.append("||';'||'RasterLite2'"); // 2 of 1st field sb_query.append("||';'||REPLACE(title,';','-')"); // 3 of 1st field sb_query.append("||';'||REPLACE(abstract,';','-')"); // 4 of 1st field sb_query.append(" AS vector_key,pixel_type"); // 0 of second field sb_query.append("||';'||tile_width"); // 2 sb_query.append("||';'||srid"); // 3 sb_query.append("||';'||horz_resolution||';' AS vector_data,"); // 4 VECTOR_LAYERS_QUERY_BASE = sb_query.toString(); } { StringBuilder sb_query = new StringBuilder(); // if the record is invalid, only this field will be null // 'vector_key' and 'vector_data' will be use to attempt to recover from the error. sb_query.append("num_bands"); // 0 sb_query.append("||';'||extent_minx"); // 1.0 sb_query.append("||','||extent_miny"); // 1.1 sb_query.append("||','||extent_maxx"); // 1.2 sb_query.append("||','||extent_maxy"); // 1.3 sb_query.append("||';'||strftime('%Y-%m-%dT%H:%M:%fZ','now') AS vector_extent"); // 2 VECTOR_LAYERS_QUERY_EXTENT_VALID = sb_query.toString(); } { StringBuilder sb_query = new StringBuilder(); sb_query.append(" FROM " + METADATA_RASTERLITE2_RASTER_COVERAGES_TABLE_NAME); VECTOR_LAYERS_QUERY_FROM = sb_query.toString(); } { StringBuilder sb_query = new StringBuilder(); // first Views (Spatialview) then tables (AbstractSpatialTable), then Table-Name/Column sb_query.append(" ORDER BY coverage_name ASC"); sb_query.append(",title ASC"); VECTOR_LAYERS_QUERY_ORDER = sb_query.toString(); } { StringBuilder sb_query = new StringBuilder(); // if the SELECT RL2_LoadRaster(...) was not executed, // - a record may exist with 'statistics and extent=NULL': // - this is an invalid record and must be ignored sb_query.append(" WHERE (statistics IS NOT NULL)"); sb_query.append(" AND (extent_minx IS NOT NULL)"); sb_query.append(" AND (extent_miny IS NOT NULL)"); sb_query.append(" AND (extent_maxx IS NOT NULL)"); sb_query.append(" AND (extent_maxy IS NOT NULL)"); VECTOR_LAYERS_QUERY_WHERE = sb_query.toString(); } { StringBuilder sb_query = new StringBuilder(); sb_query.append(VECTOR_LAYERS_QUERY_BASE); sb_query.append(VECTOR_LAYERS_QUERY_EXTENT_VALID); sb_query.append(VECTOR_LAYERS_QUERY_FROM); sb_query.append(VECTOR_LAYERS_QUERY_WHERE); sb_query.append(VECTOR_LAYERS_QUERY_ORDER); String query = sb_query.toString(); queriesMap.put("RASTER_COVERAGES_QUERY_EXTENT_VALID_V42", query); } { StringBuilder sb_query = new StringBuilder(); sb_query.append(VECTOR_LAYERS_QUERY_BASE); sb_query.append(VECTOR_LAYERS_QUERY_FROM); sb_query.append(VECTOR_LAYERS_QUERY_ORDER); String query = sb_query.toString(); query = query.replace("AS vector_data,", "AS vector_data"); // remove queriesMap.put("RASTER_COVERAGES_QUERY_EXTENT_LIST_V42", query); } { StringBuilder sb_query = new StringBuilder(); // if the SELECT RL2_LoadRaster(...) was not executed, // - a record may exist with 'statistics and extent=NULL': // - this is an invalid record and must be ignored sb_query.append(" WHERE (statistics IS NULL)"); sb_query.append(" OR (extent_minx IS NULL)"); sb_query.append(" OR (extent_miny IS NULL)"); sb_query.append(" OR (extent_maxx IS NULL)"); sb_query.append(" OR (extent_maxy IS NULL)"); VECTOR_LAYERS_QUERY_WHERE = sb_query.toString(); } { StringBuilder sb_query = new StringBuilder(); // if the record is invalid, only this field will what is invalid // - where 'field_name' is shown, that field is invalid sb_query.append("CASE WHEN statistics IS NULL THEN 'statistics' ELSE "); // 0 sb_query.append("pixel_type END "); // 0 sb_query.append("||';'||CASE WHEN extent_minx IS NULL THEN 'extent_minx' ELSE extent_minx END"); // 1.0 sb_query.append("||','||CASE WHEN extent_miny IS NULL THEN 'extent_miny' ELSE extent_miny END"); // 1.1 sb_query.append("||','||CASE WHEN extent_maxx IS NULL THEN 'extent_maxx' ELSE extent_maxx END"); // 1.2 sb_query.append("||','||CASE WHEN extent_maxy IS NULL THEN 'extent_maxy' ELSE extent_maxy END"); // 1.3 sb_query.append("||';'||strftime('%Y-%m-%dT%H:%M:%fZ','now') AS vector_extent"); // 2 VECTOR_LAYERS_QUERY_EXTENT_INVALID = sb_query.toString(); } { StringBuilder sb_query = new StringBuilder(); sb_query.append(VECTOR_LAYERS_QUERY_BASE); sb_query.append(VECTOR_LAYERS_QUERY_EXTENT_INVALID); sb_query.append(VECTOR_LAYERS_QUERY_FROM); sb_query.append(VECTOR_LAYERS_QUERY_WHERE); sb_query.append(VECTOR_LAYERS_QUERY_ORDER); String query = sb_query.toString(); queriesMap.put("RASTER_COVERAGES_QUERY_EXTENT_INVALID_V42", query); } { // ------------------- // RasterLite2 support - end // ------------------- // GeoPackage support - begin // ------------------- StringBuilder sb_query = new StringBuilder(); sb_query.append("SELECT DISTINCT "); sb_query.append("table_name"); // 0 of 1st field sb_query.append("||';'||CASE"); // 1 of 1st field sb_query.append(" WHEN data_type = 'features' THEN ("); // 1 of 1st field sb_query.append("SELECT column_name FROM gpkg_geometry_columns WHERE table_name = ''||table_name||''"); // 1 // of // 1st // field sb_query.append(") WHEN data_type = 'tiles' THEN 'tile_data'"); // 1 of 1st field sb_query.append(" END"); // 1 of 1st field sb_query.append(" ||';'||CASE"); // 2 of 1st field sb_query.append(" WHEN data_type = 'features' THEN 'GeoPackage_features'"); // 2 of 1st // field sb_query.append(" WHEN data_type = 'tiles' THEN 'GeoPackage_tiles'"); // 2 of 1st field sb_query.append(" END"); // 2 of 1st field sb_query.append("||';'||REPLACE(identifier,';','-')"); // 3 of second field sb_query.append("||';'||REPLACE(description,';','-') AS vector_key,"); // 4 of second field // fromosm_tiles;tile_data;GeoPackage_tiles;© OpenStreetMap contributors, See // http://www.openstreetmap.org/copyright;OSM Tiles; // geonames;geometry;GeoPackage_features;Data from http://www.geonames.org/, under Creative // Commons Attribution 3.0 License;Geonames; sb_query.append("CASE"); // 0 of second field sb_query.append(" WHEN data_type = 'features' THEN ("); // 0 of second field sb_query.append(""); // 0 of second field // Now the horror begins ... LAYERS_QUERY_BASE_V3 = "SELECT geometry_type_name FROM gpkg_geometry_columns WHERE table_name = ''||table_name||''"; // 0 // of // second // field sb_query.append("CASE WHEN (" + LAYERS_QUERY_BASE_V3 + ") = 'GEOMETRY' THEN '0'"); sb_query.append(" WHEN (" + LAYERS_QUERY_BASE_V3 + ") = 'POINT' THEN '1'"); sb_query.append(" WHEN (" + LAYERS_QUERY_BASE_V3 + ") = 'LINESTRING' THEN '2'"); sb_query.append(" WHEN (" + LAYERS_QUERY_BASE_V3 + ") = 'POLYGON' THEN '3'"); sb_query.append(" WHEN (" + LAYERS_QUERY_BASE_V3 + ") = 'MULTIPOINT' THEN '4'"); sb_query.append(" WHEN (" + LAYERS_QUERY_BASE_V3 + ") = 'MULTILINESTRING' THEN '5'"); sb_query.append(" WHEN (" + LAYERS_QUERY_BASE_V3 + ") = 'MULTIPOLYGON' THEN '6'"); sb_query.append(" WHEN (" + LAYERS_QUERY_BASE_V3 + ") = 'GEOMETRYCOLLECTION' THEN '7' END"); // ... to be continued ... sb_query.append(") WHEN data_type = 'tiles' THEN ("); // 1 of 1st field sb_query.append("SELECT min(zoom_level) FROM gpkg_tile_matrix WHERE table_name = ''||table_name||''"); // 1 // of // second // field sb_query.append(") END"); // 0 of second field sb_query.append("||';'||CASE"); // 1 of second field sb_query.append(" WHEN data_type = 'features' THEN ("); // 1 of second field // ... and now for something completely different ... LAYERS_QUERY_BASE_V3 = "SELECT z||','||m FROM gpkg_geometry_columns WHERE table_name = ''||table_name||''"; // 1 // of // second // field sb_query.append("CASE WHEN (" + LAYERS_QUERY_BASE_V3 + ") = '0,0' THEN '2'"); sb_query.append(" WHEN (" + LAYERS_QUERY_BASE_V3 + ") = '1,0' THEN '3'"); sb_query.append(" WHEN (" + LAYERS_QUERY_BASE_V3 + ") = '1,1' THEN '4' END"); // ... ich habe fertig. sb_query.append(") WHEN data_type = 'tiles' THEN ("); // 1 of second field sb_query.append("SELECT max(zoom_level) FROM gpkg_tile_matrix WHERE table_name = ''||table_name||''"); // 1 // of // second // field sb_query.append(") END"); // 1 of second field sb_query.append("||';'||CASE"); // 2 of second field sb_query.append(" WHEN srs_id = '1' THEN '4326'"); // 2 of second field sb_query.append(" WHEN srs_id = '2' THEN '3857'"); // 2 of second field sb_query.append(" ELSE srs_id "); // 2 of second field sb_query.append(" END"); // 2 of 1st field sb_query.append("||';'||'-1'||';' AS vector_data,"); // 3 of second field // 0;10;3857;0; // 1;2;4326;0; VECTOR_LAYERS_QUERY_BASE = sb_query.toString(); } { StringBuilder sb_query = new StringBuilder(); // if the record is invalid, only this field will be null // 'vector_key' and 'vector_data' will be use to attempt to recover from the error. sb_query.append("'-1'"); // 0 sb_query.append("||';'||min_x"); // 1.0 sb_query.append("||','||min_y"); // 1.1 sb_query.append("||','||max_x"); // 1.2 sb_query.append("||','||max_y"); // 1.3 sb_query.append("||';'||last_change AS vector_extent"); // 2 // -1;-180.0;-90.0;180.0;90.0;2013-01-18T17:39:20.000Z VECTOR_LAYERS_QUERY_EXTENT_VALID = sb_query.toString(); } { StringBuilder sb_query = new StringBuilder(); sb_query.append(" FROM " + METADATA_GEOPACKAGE_TABLE_NAME); VECTOR_LAYERS_QUERY_FROM = sb_query.toString(); } { StringBuilder sb_query = new StringBuilder(); // condition not known when this is NOT true // - this is an invalid record and must be ignored sb_query.append(" WHERE (last_change IS NOT NULL)"); sb_query.append(" AND (min_x IS NOT NULL)"); sb_query.append(" AND (min_y IS NOT NULL)"); sb_query.append(" AND (max_x IS NOT NULL)"); sb_query.append(" AND (max_y IS NOT NULL)"); VECTOR_LAYERS_QUERY_WHERE = sb_query.toString(); } { StringBuilder sb_query = new StringBuilder(); // first Views (Spatialview) then tables (AbstractSpatialTable), then Table-Name/Column sb_query.append(" ORDER BY table_name ASC"); sb_query.append(",identifier ASC"); VECTOR_LAYERS_QUERY_ORDER = sb_query.toString(); } { StringBuilder sb_query = new StringBuilder(); sb_query.append(VECTOR_LAYERS_QUERY_BASE); sb_query.append(VECTOR_LAYERS_QUERY_EXTENT_VALID); sb_query.append(VECTOR_LAYERS_QUERY_FROM); sb_query.append(VECTOR_LAYERS_QUERY_WHERE); sb_query.append(VECTOR_LAYERS_QUERY_ORDER); String query = sb_query.toString(); queriesMap.put("GEOPACKAGE_QUERY_EXTENT_VALID_R10", query); } { StringBuilder sb_query = new StringBuilder(); sb_query.append(VECTOR_LAYERS_QUERY_BASE); sb_query.append(VECTOR_LAYERS_QUERY_FROM); sb_query.append(VECTOR_LAYERS_QUERY_ORDER); String query = sb_query.toString(); query = query.replace("AS vector_data,", "AS vector_data"); // remove queriesMap.put("GEOPACKAGE_QUERY_EXTENT_LIST_R10", query); } { StringBuilder sb_query = new StringBuilder(); // if the SELECT RL2_LoadRaster(...) was not executed, // - a record may exist with 'statistics and extent=NULL': // - this is an invalid record and must be ignored sb_query.append(" WHERE (last_change IS NULL)"); sb_query.append(" OR (min_x IS NULL)"); sb_query.append(" OR (min_y IS NULL)"); sb_query.append(" OR (max_x IS NULL)"); sb_query.append(" OR (max_y IS NULL)"); VECTOR_LAYERS_QUERY_WHERE = sb_query.toString(); } { StringBuilder sb_query = new StringBuilder(); // if the record is invalid, only this field will what is invalid // - where 'field_name' is shown, that field is invalid sb_query.append("'-1'"); // 0 sb_query.append("||';'||CASE WHEN min_x IS NULL THEN 'min_x' ELSE min_x END"); // 1.0 sb_query.append("||','||CASE WHEN min_y IS NULL THEN 'min_y' ELSE min_y END"); // 1.1 sb_query.append("||','||CASE WHEN max_x IS NULL THEN 'max_x' ELSE max_x END"); // 1.2 sb_query.append("||','||CASE WHEN max_y IS NULL THEN 'max_y' ELSE max_y END"); // 1.3 sb_query.append("||','||CASE WHEN max_y IS NULL THEN 'max_y' ELSE max_y END"); // 1.3 sb_query.append("||';'||strftime('%Y-%m-%dT%H:%M:%fZ','now') AS vector_extent"); // 2 VECTOR_LAYERS_QUERY_EXTENT_INVALID = sb_query.toString(); } { StringBuilder sb_query = new StringBuilder(); sb_query.append(VECTOR_LAYERS_QUERY_BASE); sb_query.append(VECTOR_LAYERS_QUERY_EXTENT_INVALID); sb_query.append(VECTOR_LAYERS_QUERY_FROM); sb_query.append(VECTOR_LAYERS_QUERY_WHERE); sb_query.append(VECTOR_LAYERS_QUERY_ORDER); String query = sb_query.toString(); queriesMap.put("GEOPACKAGE_QUERY_EXTENT_INVALID_R10", query); } // ------------------- // GoPackage support - end // ------------------- // This is not something that should be developed more than once ... /* GPLog.androidLog(-1, "DaoSpatialite: VECTOR_LAYERS_QUERY_EXTENT_VALID_V4["+ VECTOR_LAYERS_QUERY_EXTENT_VALID_V4+"]"); GPLog.androidLog(-1, "DaoSpatialite: VECTOR_LAYERS_QUERY_EXTENT_INVALID_V4[" + VECTOR_LAYERS_QUERY_EXTENT_INVALID_V4 + "] "); GPLog.androidLog(-1, "DaoSpatialite: VECTOR_LAYERS_QUERY_EXTENT_LIST_V4["+ VECTOR_LAYERS_QUERY_EXTENT_LIST_V4 + "] "); GPLog.androidLog(-1, "DaoSpatialite: LAYERS_QUERY_EXTENT_VALID_V4["+ LAYERS_QUERY_EXTENT_VALID_V4+"]"); GPLog.androidLog(-1, "DaoSpatialite: LAYERS_QUERY_EXTENT_INVALID_V4[" + LAYERS_QUERY_EXTENT_INVALID_V4 + "] "); GPLog.androidLog(-1, "DaoSpatialite: LAYERS_QUERY_EXTENT_LIST_V4["+ LAYERS_QUERY_EXTENT_LIST_V4 + "] "); GPLog.androidLog(-1, "DaoSpatialite: LAYERS_QUERY_EXTENT_VALID_V3["+ LAYERS_QUERY_EXTENT_VALID_V3+"] "); GPLog.androidLog(-1, "DaoSpatialite: LAYERS_QUERY_EXTENT_INVALID_V3[" + LAYERS_QUERY_EXTENT_INVALID_V3 + "] "); GPLog.androidLog(-1, "DaoSpatialite: LAYERS_QUERY_EXTENT_LIST_V3["+ LAYERS_QUERY_EXTENT_LIST_V3 + "] "); GPLog.androidLog(-1, "DaoSpatialite: VIEWS_QUERY_EXTENT_VALID_V3["+ VIEWS_QUERY_EXTENT_VALID_V3+"]"); GPLog.androidLog(-1, "DaoSpatialite: VIEWS_QUERY_EXTENT_INVALID_V3[" + VIEWS_QUERY_EXTENT_INVALID_V3 + "] "); GPLog.androidLog(-1, "DaoSpatialite: VIEWS_QUERY_EXTENT_LIST_V3["+ VIEWS_QUERY_EXTENT_LIST_V3 + "] "); GPLog.androidLog(-1, "DaoSpatialite: RASTER_COVERAGES_QUERY_EXTENT_VALID_V42["+ RASTER_COVERAGES_QUERY_EXTENT_VALID_V42+"]"); GPLog.androidLog(-1, "DaoSpatialite: RASTER_COVERAGES_QUERY_EXTENT_INVALID_V42[" + RASTER_COVERAGES_QUERY_EXTENT_INVALID_V42 + "] "); GPLog.androidLog(-1, "DaoSpatialite: RASTER_COVERAGES_QUERY_EXTENT_LIST_V42["+ RASTER_COVERAGES_QUERY_EXTENT_LIST_V42 + "] "); GPLog.androidLog(-1, "DaoSpatialite: GEOPACKAGE_QUERY_EXTENT_VALID_R10["+ GEOPACKAGE_QUERY_EXTENT_VALID_R10+"]"); GPLog.androidLog(-1, "DaoSpatialite: GEOPACKAGE_QUERY_EXTENT_INVALID_R10[" + GEOPACKAGE_QUERY_EXTENT_INVALID_R10 + "] "); GPLog.androidLog(-1, "DaoSpatialite: GEOPACKAGE_QUERY_EXTENT_LIST_R10["+ GEOPACKAGE_QUERY_EXTENT_LIST_R10 + "] "); */ } /** * Get the sql for the current query. * * @return the sql. */ public String getQuery() { return queriesMap.get(name()); } }