Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[GEOT-4707] Add a flag to force spatial index usage in sql server #371

Merged
merged 2 commits into from Feb 20, 2014
Merged

[GEOT-4707] Add a flag to force spatial index usage in sql server #371

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
2 commits
Select commit Hold shift + click to select a range
87e3d7b
Fixing drop index support in sql server
aaime Feb 19, 2014
d1c78a3
[GEOT-4707] Add a flag to force spatial index usage in sql server
aaime Jan 15, 2014
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Jump to
Jump to file
Failed to load files.
Diff view
Diff view
14 changes: 13 additions & 1 deletion modules/library/jdbc/src/main/java/org/geotools/jdbc/JDBCDataStore.java
View file
Open in desktop
Expand Up @@ -3010,12 +3010,24 @@ protected String selectSQL(SimpleFeatureType featureType, Query query) throws IO
//sorting
sort(featureType, query.getSortBy(), null, sql);

// finally encode limit/offset, if necessary
// encode limit/offset, if necessary
applyLimitOffset(sql, query);

// add search hints if the dialect supports them
applySearchHints(featureType, query, sql);

return sql.toString();
}

private void applySearchHints(SimpleFeatureType featureType, Query query, StringBuffer sql) {
// we can apply search hints only on real tables
if(virtualTables.containsKey(featureType.getTypeName())) {
return;
}

dialect.handleSelectHints(sql, featureType, query);
}

protected String selectJoinSQL(SimpleFeatureType featureType, JoinInfo join, Query query)
throws IOException, SQLException {

Expand Down
11 changes: 11 additions & 0 deletions modules/library/jdbc/src/main/java/org/geotools/jdbc/SQLDialect.java
View file
Open in desktop
Expand Up @@ -1331,6 +1331,17 @@ public List<Index> getIndexes(Connection cx, String databaseSchema, String typeN
} finally {
dataStore.closeSafe(indexInfo);
}
}


/**
* Used to apply search hints on the fully generated SQL (complete of select, from, where, sort,
* limit/offset)
* @param sql
* @param featureType
* @param query
*/
public void handleSelectHints(StringBuffer sql, SimpleFeatureType featureType, Query query) {
// nothing to do
}
}
12 changes: 12 additions & 0 deletions ...c/jdbc-sqlserver/src/main/java/org/geotools/data/sqlserver/SQLServerDataStoreFactory.java
View file
Open in desktop
Expand Up @@ -51,6 +51,11 @@ public class SQLServerDataStoreFactory extends JDBCDataStoreFactory {
public static final Param NATIVE_SERIALIZATION = new Param("Use native geometry serialization", Boolean.class,
"Use native SQL Server serialization, or WKB serialization.", false, Boolean.FALSE);

/** parameter for forcing the usage of spatial indexes in queries via sql hints */
public static final Param FORCE_SPATIAL_INDEX = new Param("Force spatial index usage via hints", Boolean.class,
"When enabled, spatial filters will be accompained by a WITH INDEX sql hint forcing the usage of the spatial index.", false, Boolean.FALSE);


@Override
protected SQLDialect createSQLDialect(JDBCDataStore dataStore) {
return new SQLServerDialect(dataStore);
Expand Down Expand Up @@ -84,6 +89,7 @@ protected void setupParameters(Map parameters) {
parameters.put(NATIVE_PAGING.key, NATIVE_PAGING);
parameters.put(NATIVE_SERIALIZATION.key, NATIVE_SERIALIZATION);
parameters.put(GEOMETRY_METADATA_TABLE.key, GEOMETRY_METADATA_TABLE);
parameters.put(FORCE_SPATIAL_INDEX.key, FORCE_SPATIAL_INDEX);
}

/**
Expand Down Expand Up @@ -124,6 +130,12 @@ protected JDBCDataStore createDataStoreInternal(JDBCDataStore dataStore, Map par
if (useNativeSerialization != null) {
dialect.setUseNativeSerialization(useNativeSerialization);
}

// check spatial index hints usage
Boolean forceSpatialIndexes = (Boolean) FORCE_SPATIAL_INDEX.lookUp(params);
if (forceSpatialIndexes != null) {
dialect.setForceSpatialIndexes(forceSpatialIndexes);
}

return dataStore;
}
Expand Down
202 changes: 188 additions & 14 deletions ...lugin/jdbc/jdbc-sqlserver/src/main/java/org/geotools/data/sqlserver/SQLServerDialect.java
View file
Open in desktop
Expand Up @@ -16,12 +16,25 @@
*/
package org.geotools.data.sqlserver;

import com.vividsolutions.jts.geom.*;
import com.vividsolutions.jts.io.ParseException;
import com.vividsolutions.jts.io.WKBReader;
import com.vividsolutions.jts.io.WKTReader;
import com.vividsolutions.jts.io.WKTWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Types;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;
import java.util.UUID;
import java.util.logging.Level;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.geotools.data.Query;
import org.geotools.data.jdbc.FilterToSQL;
import org.geotools.data.sqlserver.reader.SqlServerBinaryReader;
import org.geotools.factory.Hints;
Expand All @@ -32,18 +45,25 @@
import org.opengis.feature.simple.SimpleFeatureType;
import org.opengis.feature.type.AttributeDescriptor;
import org.opengis.feature.type.GeometryDescriptor;
import org.opengis.filter.Filter;
import org.opengis.referencing.crs.CoordinateReferenceSystem;
import org.opengis.referencing.cs.CoordinateSystem;
import org.opengis.referencing.cs.CoordinateSystemAxis;

import java.io.IOException;
import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.UUID;
import java.util.logging.Level;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import com.vividsolutions.jts.geom.Envelope;
import com.vividsolutions.jts.geom.Geometry;
import com.vividsolutions.jts.geom.GeometryCollection;
import com.vividsolutions.jts.geom.GeometryFactory;
import com.vividsolutions.jts.geom.LineString;
import com.vividsolutions.jts.geom.MultiLineString;
import com.vividsolutions.jts.geom.MultiPoint;
import com.vividsolutions.jts.geom.MultiPolygon;
import com.vividsolutions.jts.geom.Point;
import com.vividsolutions.jts.geom.Polygon;
import com.vividsolutions.jts.io.ParseException;
import com.vividsolutions.jts.io.WKBReader;
import com.vividsolutions.jts.io.WKTReader;
import com.vividsolutions.jts.io.WKTWriter;

/**
* Dialect implementation for Microsoft SQL Server.
Expand All @@ -56,6 +76,7 @@ public class SQLServerDialect extends BasicSQLDialect {

private static final int DEFAULT_AXIS_MAX = 10000000;
private static final int DEFAULT_AXIS_MIN = -10000000;
static final String SPATIAL_INDEX_KEY = "SpatialIndex";

/**
* Pattern used to match the first FROM element in a SQL query, without matching
Expand All @@ -73,6 +94,8 @@ public class SQLServerDialect extends BasicSQLDialect {

private Boolean useNativeSerialization = false;

private Boolean forceSpatialIndexes = false;

final static Map<String, Class> TYPE_TO_CLASS_MAP = new HashMap<String, Class>() {
{
put("GEOMETRY", Geometry.class);
Expand Down Expand Up @@ -738,5 +761,156 @@ public void setUseOffSetLimit(Boolean useOffsetLimit) {
public void setUseNativeSerialization(Boolean useNativeSerialization) {
this.useNativeSerialization = useNativeSerialization;
}


/**
* Sets whether to force the usage of spatial indexes by including a WITH INDEX hint
* @param useNativeSerialization
*/
public void setForceSpatialIndexes(boolean forceSpatialIndexes) {
this.forceSpatialIndexes = forceSpatialIndexes;
}

/**
* Drop the index. Subclasses can override to handle extra syntax or db specific situations
*
* @param cx
* @param schema
* @param databaseSchema
* @param indexName
* @throws SQLException
*/
public void dropIndex(Connection cx, SimpleFeatureType schema, String databaseSchema,
String indexName) throws SQLException {
StringBuffer sql = new StringBuffer();
String escape = getNameEscape();
sql.append("DROP INDEX ");
sql.append(escape).append(indexName).append(escape);
sql.append(" ON ");
if (databaseSchema != null) {
encodeSchemaName(databaseSchema, sql);
sql.append(".");
}
sql.append(escape).append(schema.getTypeName()).append(escape);

Statement st = null;
try {
st = cx.createStatement();
st.execute(sql.toString());
if(!cx.getAutoCommit()) {
cx.commit();
}
} finally {
dataStore.closeSafe(cx);
}
}

@Override
public void postCreateFeatureType(SimpleFeatureType featureType, DatabaseMetaData md,
String databaseSchema, Connection cx) throws SQLException {
// collect the spatial indexes (index metadata does not work properly for spatial indexes)
String sql = "SELECT \n" +
" index_name = ind.name,\n" +
" column_name = col.name\n" +
"FROM \n" +
" sys.indexes ind \n" +
"INNER JOIN \n" +
" sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id \n" +
"INNER JOIN \n" +
" sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id \n" +
"INNER JOIN \n" +
" sys.tables t ON ind.object_id = t.object_id \n" +
"WHERE \n" +
" ind.type_desc = 'SPATIAL'\n" +
" and t.name = '" + featureType.getTypeName() + "'";
ResultSet indexInfo = null;
Statement st = null;
Map<String, Set<String>> indexes = new HashMap<String, Set<String>>();
try {
st = cx.createStatement();
indexInfo = st.executeQuery(sql);
while (indexInfo.next()) {
String indexName = indexInfo.getString("index_name");
String columnName = indexInfo.getString("column_name");
Set<String> indexColumns = indexes.get(indexName);
if (indexColumns == null) {
indexColumns = new HashSet<String>();
indexes.put(indexName, indexColumns);
}
indexColumns.add(columnName);
}
} finally {
dataStore.closeSafe(st);
dataStore.closeSafe(indexInfo);
}

// search for single column spatial indexes and attach them to the descriptors
for (Map.Entry<String, Set<String>> entry : indexes.entrySet()) {
Set<String> columns = entry.getValue();
if(columns.size() == 1) {
String column = columns.iterator().next();
AttributeDescriptor descriptor = featureType.getDescriptor(column);
if(descriptor instanceof GeometryDescriptor) {
descriptor.getUserData().put(SPATIAL_INDEX_KEY, entry.getKey());
}
}
}
}

@Override
public void handleSelectHints(StringBuffer sql, SimpleFeatureType featureType, Query query) {
// optional feature, apply only if requested
if(!forceSpatialIndexes) {
return;
}

// check we have a filter
Filter filter = query.getFilter();
if(filter == Filter.INCLUDE) {
return;
}

// that is has spatial attributes
SpatialIndexAttributeExtractor attributesExtractor = new SpatialIndexAttributeExtractor();
filter.accept(attributesExtractor, null);
Map<String, Integer> attributes = attributesExtractor.getSpatialProperties();
if(attributes.isEmpty() || attributes.size() > 1) {
return;
}

// and that those attributes have a spatial index
Set<String> indexes = new HashSet<String>();
for (Map.Entry<String, Integer> attribute : attributes.entrySet()) {
// we can only apply one index on one condition
if(attribute.getValue() > 1) {
continue;
}
AttributeDescriptor descriptor = featureType.getDescriptor(attribute.getKey());
if(descriptor instanceof GeometryDescriptor) {
String indexName = (String) descriptor.getUserData().get(SPATIAL_INDEX_KEY);
if(indexName != null) {
indexes.add(indexName);
}
}
}
if(indexes.isEmpty()) {
return;
}

// apply the index hints
String fromStatement = "FROM \"" + featureType.getTypeName() + "\"";
int idx = sql.indexOf(fromStatement);
if(idx > 0) {
int base = idx + fromStatement.length();
StringBuilder sb = new StringBuilder(" WITH( INDEX(");
for (String indexName : indexes) {
sb.append("\"").append(indexName).append("\"").append(",");
}
sb.setLength(sb.length() - 1);
sb.append("))");
String tableHint = sb.toString();

sql.insert(base, tableHint);
}
}

}
1 change: 1 addition & 0 deletions ...bc-sqlserver/src/main/java/org/geotools/data/sqlserver/SQLServerJNDIDataStoreFactory.java
View file
Open in desktop
Expand Up @@ -46,5 +46,6 @@ protected void setupParameters(Map parameters) {
parameters.put(NATIVE_PAGING.key, NATIVE_PAGING);
parameters.put(NATIVE_SERIALIZATION.key, NATIVE_SERIALIZATION);
parameters.put(GEOMETRY_METADATA_TABLE.key, GEOMETRY_METADATA_TABLE);
parameters.put(FORCE_SPATIAL_INDEX.key, FORCE_SPATIAL_INDEX);
}
}