Revision: 5485 http://sourceforge.net/p/jump-pilot/code/5485 Author: elnico Date: 2017-08-10 09:16:39 +0000 (Thu, 10 Aug 2017) Log Message: ----------- Spatial Index support for SQLite/Spatialte datasources: checks if geometry columns are indexed and builds custom SQL queries to use index according to detetected SQLite flavors
Modified Paths: -------------- core/trunk/ChangeLog core/trunk/src/com/vividsolutions/jump/datastore/GeometryColumn.java core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteDSMetadata.java core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteSQLBuilder.java Modified: core/trunk/ChangeLog =================================================================== --- core/trunk/ChangeLog 2017-08-05 14:05:48 UTC (rev 5484) +++ core/trunk/ChangeLog 2017-08-10 09:16:39 UTC (rev 5485) @@ -3,6 +3,11 @@ # 2. make sure that lines break at 80 chars for constricted display situations #<-------------------------------- 80 chars ----------------------------------># +2017-08-10 Nicolas Ribot <nicolas.ri...@gmail.com> + * Spatial Index support for SQLite/Spatialte datasources: checks if geometry + columns are indexed and builds custom SQL queries to use index according to + detetected SQLite flavors + 2017-08-05 * Improvement from ioan to honour IncrementalAngle constraint even on the first segment Modified: core/trunk/src/com/vividsolutions/jump/datastore/GeometryColumn.java =================================================================== --- core/trunk/src/com/vividsolutions/jump/datastore/GeometryColumn.java 2017-08-05 14:05:48 UTC (rev 5484) +++ core/trunk/src/com/vividsolutions/jump/datastore/GeometryColumn.java 2017-08-10 09:16:39 UTC (rev 5485) @@ -42,10 +42,23 @@ /** * Ctor with boolean parameter telling if column is indexed * @param name the name of the geometry column + * @param coordDimension the dimension of coordinates (2D, 3D) * @param srid the SRID of the geometry column * @param type the geometric native type of the geometry column (GEOMETRY, SDO_GEOMETRY...) * @param indexed true if the geometry column is indexed */ + public GeometryColumn(String name, int coordDimension, int srid, String type, boolean indexed) { + this(name, coordDimension, srid, type); + this.indexed = indexed; + } + + /** + * Ctor with boolean parameter telling if column is indexed + * @param name the name of the geometry column + * @param srid the SRID of the geometry column + * @param type the geometric native type of the geometry column (GEOMETRY, SDO_GEOMETRY...) + * @param indexed true if the geometry column is indexed + */ public GeometryColumn(String name, int srid, String type, boolean indexed) { this(name, srid, type); this.indexed = indexed; Modified: core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteDSMetadata.java =================================================================== --- core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteDSMetadata.java 2017-08-05 14:05:48 UTC (rev 5484) +++ core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteDSMetadata.java 2017-08-10 09:16:39 UTC (rev 5485) @@ -1,6 +1,7 @@ package com.vividsolutions.jump.datastore.spatialite; import com.vividsolutions.jump.datastore.DataStoreConnection; +import com.vividsolutions.jump.datastore.GeometryColumn; import com.vividsolutions.jump.datastore.spatialdatabases.*; import com.vividsolutions.jump.datastore.jdbc.JDBCUtil; import com.vividsolutions.jump.datastore.jdbc.ResultSetBlock; @@ -9,7 +10,11 @@ import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; +import java.util.ArrayList; import java.util.HashMap; +import java.util.Iterator; +import java.util.List; +import java.util.Locale; import java.util.Map; /** @@ -47,8 +52,20 @@ * suitable SQL query OJ can read */ private String geoColumnTypesQuery = null; - + /** + * the query to know if a geometry is indexed or not. + */ + private String spatialIndexQuery = null; + + /** + * The map of geometryColumn list for each dataset: stores it locally as it is accessed + * several times for spatialite: at UI init, and when loading a layer + */ + private Map<String, List<GeometryColumn>> geometryColumnListMap = null; + + + /** * * @param con */ @@ -58,6 +75,7 @@ this.spatialiteVersion = ""; this.geometryColumnsLayout = GeometryColumnsLayout.NO_LAYOUT; this.geoColTypesdMap = new HashMap<String, GeometricColumnType>(); + this.geometryColumnListMap = new HashMap<String, List<GeometryColumn>>(); checkSpatialiteLoaded(); setGeoColLayout(); @@ -123,8 +141,74 @@ } else { coordDimQuery = "SELECT coord_dimension FROM geometry_columns where f_table_name = '%s' and f_geometry_column = '%s'"; } + + // spatial index query + + + if (this.geometryColumnsLayout == GeometryColumnsLayout.OGC_GEOPACKAGE_LAYOUT) { + spatialIndexQuery = "select exists(SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'rtree_%s_%s')"; + } else if (this.spatialiteLoaded) { + // for all cases where spatialite is detected: normal function should be used + spatialIndexQuery = "SELECT CASE WHEN CheckSpatialIndex('%s', '%s') = 1 then 1 else 0 end as isindexed"; + } else if (this.geometryColumnsLayout == GeometryColumnsLayout.OGC_SPATIALITE_LAYOUT) { + spatialIndexQuery = "select spatial_index_enabled from geometry_columns where f_table_name = '%s' and f_geometry_column = '%s'"; + } else { + // TODO: + spatialIndexQuery = ""; + } + + // TODO: remove in prod. +// JUMPWorkbench.getInstance().getFrame().log( +// "Spatialite MD:\n\t" +// + "geo col layout: " + geometryColumnsLayout + "\n\t" +// + "spatialite loaded: " + spatialiteLoaded + "\n\t" +// + "version: " + spatialiteVersion, +// this.getClass()); } + + /** + * Overriden to deal with indexed geo columns, as queries to get features are different + * if spatial index is detected on the column. + * Buids a GeometryColumn object with 5 params ctor. + * @param sql + * @param datasetName + * @return + */ + @Override + protected List<GeometryColumn> getGeometryAttributes(String sql, String datasetName) { + if (this.geometryColumnListMap.get(datasetName) == null) { + final List<GeometryColumn> geometryAttributes = new ArrayList<GeometryColumn>(); + //System.out.println("getting geom Attribute for dataset: " + datasetName + " with query: " + sql); + JDBCUtil.execute( + conn.getJdbcConnection(), sql, + new ResultSetBlock() { + public void yield(ResultSet resultSet) throws SQLException { + while (resultSet.next()) { + // TODO: escape single quotes in geo column name ? + geometryAttributes.add(new GeometryColumn( + resultSet.getString(1), + resultSet.getInt(2), + resultSet.getInt(3), + resultSet.getString(4))); + } + } + }); + + // sets geo columns index information by querying spatialite function or direct + // index table + for (Iterator<GeometryColumn> iterator = geometryAttributes.iterator(); iterator.hasNext();) { + GeometryColumn gc = iterator.next(); + setIndexInfo(datasetName, gc); + + } + this.geometryColumnListMap.put(datasetName, geometryAttributes); + + } + + return this.geometryColumnListMap.get(datasetName); + } + @Override public String getSpatialExtentQuery1(String schema, String table, String attributeName) { // No schema in SQLite, schema param not used @@ -218,7 +302,7 @@ * * Geometry_columns metadata table may have 4 layouts: * options used to create the table or using a geo package (http://www.geopackage.org/) layout - * 1\xB0) the "FDO provider for spatialite (https://trac.osgeo.org/fdo/wiki/FDORfc16)", as used in "regular sqlite database" (cf.ogr spatialite format doc): + * 1?) the "FDO provider for spatialite (https://trac.osgeo.org/fdo/wiki/FDORfc16)", as used in "regular sqlite database" (cf.ogr spatialite format doc): * f_table_name TEXT * f_geometry_column TEXT * geometry_type INTEGER @@ -225,7 +309,7 @@ * coord_dimension INTEGER * srid INTEGER * geometry_format TEXT - * 2\xB0) the "OGC Spatialite" flavour, as understood by qgis for instance, as used in spatialite-enabled sqlite database: + * 2?) the "OGC Spatialite" flavour, as understood by qgis for instance, as used in spatialite-enabled sqlite database: * f_table_name VARCHAR * f_geometry_column VARCHAR * type VARCHAR @@ -232,7 +316,7 @@ * coord_dimension INTEGER * srid INTEGER * spatial_index_enabled INTEGER - * 3\xB0) the "OGC OGR" layout: + * 3?) the "OGC OGR" layout: * f_table_name VARCHAR * f_geometry_column VARCHAR * geometry_type VARCHAR @@ -239,7 +323,7 @@ * coord_dimension INTEGER * srid INTEGER * spatial_index_enabled INTEGER - * 3\xB0) the "OGC GeoPackage" layout, as specificed by standard: + * 3?) the "OGC GeoPackage" layout, as specificed by standard: * table_name TEXT NOT NULL, * column_name TEXT NOT NULL, * geometry_type_name TEXT NOT NULL, @@ -298,7 +382,7 @@ geometryColumnsLayout = GeometryColumnsLayout.OGC_OGR_LAYOUT; } else { geometryColumnsLayout = GeometryColumnsLayout.NO_LAYOUT; - }; + } rs.close(); } } @@ -359,5 +443,51 @@ public Map<String, GeometricColumnType> getGeoColTypesdMap() { return geoColTypesdMap; } + + /** + * set if given gc column is spatially indexed (true/false) according to spatialite + * DB type + * @param datasetName the name of the dataset this column belongs to + * @param gc the geometry column to set + */ + private void setIndexInfo(String datasetName, final GeometryColumn gc) { + String q = String.format(Locale.US, spatialIndexQuery, datasetName, gc.getName()); + try { + JDBCUtil.execute( + conn.getJdbcConnection(), + q, + new ResultSetBlock() { + public void yield(ResultSet resultSet) throws SQLException { + while (resultSet.next()) { + gc.setIndexed(resultSet.getBoolean(1)); + } + } + }); + } catch (Exception e) { + //TODO... + e.printStackTrace(); + } + } + + /** + * Convenience method to get a geometryColumn object from this metadata + * @param datasetName the name of the dataset + * @param geoCol the name of the geo column + * @return + */ + public GeometryColumn getGeometryColumn(String datasetName, String geoCol) { + List<GeometryColumn> l = this.geometryColumnListMap.get(datasetName); + if (l == null) { + return null; + } + + for (Iterator<GeometryColumn> iterator = l.iterator(); iterator.hasNext();) { + GeometryColumn gc = iterator.next(); + if (gc.getName().equals(geoCol)) { + return gc; + } + } + return null; + } } Modified: core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteSQLBuilder.java =================================================================== --- core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteSQLBuilder.java 2017-08-05 14:05:48 UTC (rev 5484) +++ core/trunk/src/com/vividsolutions/jump/datastore/spatialite/SpatialiteSQLBuilder.java 2017-08-10 09:16:39 UTC (rev 5485) @@ -5,6 +5,7 @@ import com.vividsolutions.jts.geom.Envelope; import com.vividsolutions.jump.datastore.DataStoreLayer; import com.vividsolutions.jump.datastore.FilterQuery; +import com.vividsolutions.jump.datastore.GeometryColumn; import com.vividsolutions.jump.datastore.SpatialReferenceSystemID; import com.vividsolutions.jump.datastore.spatialdatabases.SpatialDatabasesSQLBuilder; import com.vividsolutions.jump.workbench.JUMPWorkbench; @@ -40,9 +41,9 @@ //System.out.println(qs); String s = String.format(ret, cols, this.datasetName, bbox, and, lim); -// JUMPWorkbench.getInstance().getFrame().log( -// "SQL query to get Spatial table features:\n\t" -// + s, this.getClass()); + JUMPWorkbench.getInstance().getFrame().log( + "SQL query to get Spatial table features:\n\t" + + s, this.getClass()); return s; } @@ -111,9 +112,10 @@ protected String buildBoxFilter(FilterQuery query) { Envelope env = query.getFilterGeometry().getEnvelopeInternal(); String ret = "1"; + String indexQuery = buildSpatialIndexFilter(query); // Example of Spatialite SQL: - // select nom_comm from commune where st_envIntersects(wkt_geometry, bbox(516707,6279239,600721,6347851) SpatialiteDSMetadata dsm = (SpatialiteDSMetadata) getDbMetadata(); + if (dsm.isSpatialiteLoaded()) { GeometricColumnType gcType = dsm.getGeoColTypesdMap().get( query.getDatasetName().toLowerCase() + "." + query.getGeometryAttributeName().toLowerCase()); @@ -128,10 +130,46 @@ ret = String.format(Locale.US, "st_envIntersects(st_geomFromText(%s), %f,%f,%f,%f)", query.getGeometryAttributeName(), env.getMinX(), env.getMinY(), env.getMaxX(), env.getMaxY()); } else { - // TODO: log - System.out.println("BAD gc column type: " + gcType); + JUMPWorkbench.getInstance().getFrame().log( + "Spatialite SQL builder: invalid geometric column type: " + gcType , + this.getClass()); } } + ret += indexQuery; return ret; } + + /** + * Builds a SQL filter to use spatial index, if concerned geometry column supports + * it. + * @param query the query to build the filter for + * @return the spatial index filter query, or an empty string if geometry + * column is not spatially indexed + */ + protected String buildSpatialIndexFilter(FilterQuery query) { + String ret = ""; + + Envelope env = query.getFilterGeometry().getEnvelopeInternal(); + SpatialiteDSMetadata dsm = (SpatialiteDSMetadata) getDbMetadata(); + // test if geometry column is indexed, if so, builds special query according to type: + GeometryColumn gc = dsm.getGeometryColumn(query.getDatasetName(), query.getGeometryAttributeName()); + if (gc.isIndexed()) { + if (dsm.getGeometryColumnsLayout() == GeometryColumnsLayout.OGC_GEOPACKAGE_LAYOUT) { + ret = String.format(Locale.US, + " AND ROWID IN (SELECT id FROM rtree_%s_%s WHERE minx > %f and maxx < %f and miny > %f and maxy < %f) ", + query.getDatasetName(), query.getGeometryAttributeName(), + env.getMinX(), env.getMaxX(), env.getMinY(), env.getMaxY()); + } else if (dsm.isSpatialiteLoaded()) { + // always use spatialIndex table if spatialite + ret = String.format(Locale.US, + " AND ROWID IN (SELECT ROWID FROM SpatialIndex WHERE f_table_name = '%s' AND search_frame = BuildMbr(%f,%f,%f,%f))", + query.getDatasetName(), env.getMinX(), env.getMinY(), env.getMaxX(), env.getMaxY()); + } else { + // TODO other cases: no idx + ret = ""; + } + } + + return ret; + } } ------------------------------------------------------------------------------ Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot _______________________________________________ Jump-pilot-devel mailing list Jump-pilot-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/jump-pilot-devel