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
