This is an automated email from the ASF dual-hosted git repository. amanin pushed a commit to branch fix/fast-envelope in repository https://gitbox.apache.org/repos/asf/sis.git
commit 09430ce1a0ee2289911a626474d0957c090bd81f Author: Alexis Manin <[email protected]> AuthorDate: Mon Jan 20 18:18:46 2020 +0100 fix(SQLStore): Add a feature to ask database for an estimate of a dataset envelope (table, simple query) --- .../main/java/org/apache/sis/feature/Features.java | 191 +++++++++++++++++++-- .../sis/internal/sql/feature/ColumnAdapter.java | 14 ++ .../sis/internal/sql/feature/FeatureAdapter.java | 32 +++- .../sis/internal/sql/feature/PostGISMapping.java | 97 ++++++++++- .../org/apache/sis/internal/sql/feature/Table.java | 26 ++- 5 files changed, 336 insertions(+), 24 deletions(-) diff --git a/core/sis-feature/src/main/java/org/apache/sis/feature/Features.java b/core/sis-feature/src/main/java/org/apache/sis/feature/Features.java index 16f1b7d..1b27109 100644 --- a/core/sis-feature/src/main/java/org/apache/sis/feature/Features.java +++ b/core/sis-feature/src/main/java/org/apache/sis/feature/Features.java @@ -16,32 +16,42 @@ */ package org.apache.sis.feature; +import java.util.IdentityHashMap; +import java.util.List; import java.util.Map; import java.util.Optional; -import java.util.IdentityHashMap; -import org.opengis.util.GenericName; -import org.opengis.util.NameFactory; -import org.opengis.util.InternationalString; -import org.opengis.metadata.maintenance.ScopeCode; -import org.opengis.metadata.quality.ConformanceResult; -import org.opengis.metadata.quality.DataQuality; -import org.opengis.metadata.quality.Element; -import org.opengis.metadata.quality.Result; -import org.apache.sis.util.Static; -import org.apache.sis.util.iso.DefaultNameFactory; -import org.apache.sis.internal.system.DefaultFactories; -import org.apache.sis.internal.feature.Resources; +import java.util.function.Predicate; +import java.util.logging.Level; +import java.util.stream.Collectors; -// Branch-dependent imports import org.opengis.feature.Attribute; import org.opengis.feature.AttributeType; import org.opengis.feature.Feature; -import org.opengis.feature.FeatureType; import org.opengis.feature.FeatureAssociationRole; +import org.opengis.feature.FeatureType; import org.opengis.feature.IdentifiedType; import org.opengis.feature.InvalidPropertyValueException; import org.opengis.feature.Operation; +import org.opengis.feature.PropertyNotFoundException; import org.opengis.feature.PropertyType; +import org.opengis.metadata.maintenance.ScopeCode; +import org.opengis.metadata.quality.ConformanceResult; +import org.opengis.metadata.quality.DataQuality; +import org.opengis.metadata.quality.Element; +import org.opengis.metadata.quality.Result; +import org.opengis.referencing.crs.CoordinateReferenceSystem; +import org.opengis.util.GenericName; +import org.opengis.util.InternationalString; +import org.opengis.util.NameFactory; + +import org.apache.sis.internal.feature.AttributeConvention; +import org.apache.sis.internal.feature.Resources; +import org.apache.sis.internal.system.DefaultFactories; +import org.apache.sis.util.Static; +import org.apache.sis.util.iso.DefaultNameFactory; +import org.apache.sis.util.logging.Logging; + +// Branch-dependent imports /** @@ -55,6 +65,13 @@ import org.opengis.feature.PropertyType; * @module */ public final class Features extends Static { + + /** + * A test to know if a given property is an SIS convention or not. Return true if + * the property is NOT marked as an SIS convention, false otherwise. + */ + private static final Predicate<IdentifiedType> IS_NOT_CONVENTION = p -> !AttributeConvention.contains(p.getName()); + /** * Do not allow instantiation of this class. */ @@ -306,4 +323,148 @@ public final class Features extends Static { } } } + + + /** + * Search for the main geometric property in the given type. We'll search + * for an SIS convention first (see + * {@link AttributeConvention#GEOMETRY_PROPERTY}. If no convention is set on + * the input type, we'll check if it contains a single geometric property. + * If it's the case, we return it. Otherwise (no or multiple geometries), we + * throw an exception. + * + * @param type The data type to search into. + * @return The main geometric property we've found. It will never be null. If no geometric property is found, an + * exception is thrown. + * @throws PropertyNotFoundException If no geometric property is available + * in the given type. + * @throws IllegalStateException If no convention is set (see + * {@link AttributeConvention#GEOMETRY_PROPERTY}), and we've found more than + * one geometry. + */ + public static PropertyType getDefaultGeometry(final FeatureType type) throws PropertyNotFoundException, IllegalStateException { + PropertyType geometry; + try { + geometry = type.getProperty(AttributeConvention.GEOMETRY_PROPERTY.toString()); + } catch (PropertyNotFoundException e) { + try { + geometry = searchForGeometry(type); + } catch (RuntimeException e2) { + e2.addSuppressed(e); + throw e2; + } + } + + return geometry; + } + + /** + * Search for a geometric attribute outside SIS conventions. More accurately, + * we expect the given type to have a single geometry attribute. If many are + * found, an exception is thrown. + * + * @param type The data type to search into. + * @return The only geometric property we've found. + * @throws PropertyNotFoundException If no geometric property is available in + * the given type. + * @throws IllegalStateException If we've found more than one geometry. + */ + private static PropertyType searchForGeometry(final FeatureType type) throws PropertyNotFoundException, IllegalStateException { + final List<? extends PropertyType> geometries = type.getProperties(true).stream() + .filter(IS_NOT_CONVENTION) + .filter(AttributeConvention::isGeometryAttribute) + .collect(Collectors.toList()); + + if (geometries.size() < 1) { + throw new PropertyNotFoundException("No geometric property can be found outside of sis convention."); + } else if (geometries.size() > 1) { + throw new IllegalStateException("Multiple geometries found. We don't know which one to select."); + } else { + return geometries.get(0); + } + } + + /** + * Test if given property type is an attribute as defined by {@link AttributeType}, or if it produces one as an + * {@link Operation#getResult() operation result}. It it is, we return the found attribute. + * + * @param input the data type to unravel the attribute from. + * @return The found attribute or an empty shell if we cannot find any. + */ + public static Optional<AttributeType<?>> castOrUnwrap(IdentifiedType input) { + // In case an operation also implements attribute type, we check it first. + // TODO : cycle detection ? + while (!(input instanceof AttributeType) && input instanceof Operation) { + input = ((Operation) input).getResult(); + } + + if (input instanceof AttributeType) { + return Optional.of((AttributeType) input); + } + + return Optional.empty(); + } + + /** + * Search for a specific characteristic in given property. Note that if the property is not an attribute, we'll try + * to get one for the search (see {@link #castOrUnwrap(IdentifiedType)} for more details). + * + * @param type The property to search into. + * @param characteristicName The name of the searched characteristic. + * @return Found characteristic, or nothing if we cannot find any matching given name. + */ + public static Optional<AttributeType> getCharacteristic(PropertyType type, String characteristicName) { + return castOrUnwrap(type) + .map(attr -> attr.characteristics().get(characteristicName)); + } + + /** + * Search for a characteristic value in a given property. For more complete information, you can get the complete + * characteristic definition through {@link #getCharacteristic(PropertyType, String)}. + * + * @param type The property to search into + * @param characteristicName Name of the characteristic to get a value for. + * @param <T> Expected type for characteristics values. Be careful, if using a wrong type, an error could occur on + * execution. + * @return The default value of the characteristic if we've found it, or an empty shell if the characteristic does + * not exists or has no default value. + * @throws ClassCastException If a value is found, but does not match specified data type. + */ + public static <T> Optional<T> getCharacteristicValue(PropertyType type, String characteristicName) { + return getCharacteristic(type, characteristicName) + .map(characteristic -> (T) characteristic.getDefaultValue()); + } + + /** + * Extract the coordinate reference system associated to the primary geometry of input data type. + * + * @implNote + * Primary geometry is determined using {@link #getDefaultGeometry(org.opengis.feature.FeatureType) }. + * + * @param type The data type to extract reference system from. + * @return The CRS associated to the default geometry of this data type, or + * an empty value if we cannot determine what is the primary geometry of the + * data type. Note that an empty value is also returned if a geometry property + * is found, but no CRS characteristics is associated with it. + */ + public static Optional<CoordinateReferenceSystem> getDefaultCrs(FeatureType type) { + try { + return getDefaultCrs(getDefaultGeometry(type)); + } catch (IllegalArgumentException | IllegalStateException ex) { + Logging.getLogger("org.apache.sis.feature").log(Level.FINE, "Cannot extract CRS from type, cause no default geometry is available", ex); + //no default geometry property + return Optional.empty(); + } + } + + /** + * Extract CRS characteristic if it exists. + * + * @param type The property that we want information for. + * @return If any Coordinate reference system characteristic (as defined by {@link AttributeConvention#CRS_CHARACTERISTIC SIS convention}) + * is available, its default value is returned. Otherwise, nothing. + */ + public static Optional<CoordinateReferenceSystem> getDefaultCrs(PropertyType type) { + return getCharacteristicValue(type, AttributeConvention.CRS_CHARACTERISTIC.toString()); + } } diff --git a/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/ColumnAdapter.java b/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/ColumnAdapter.java index 8f2387d..e646298 100644 --- a/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/ColumnAdapter.java +++ b/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/ColumnAdapter.java @@ -18,8 +18,10 @@ package org.apache.sis.internal.sql.feature; import java.sql.Connection; import java.sql.ResultSet; +import java.sql.SQLException; import java.util.Optional; +import org.opengis.geometry.Envelope; import org.opengis.referencing.crs.CoordinateReferenceSystem; import static org.apache.sis.util.ArgumentChecks.ensureNonNull; @@ -55,6 +57,18 @@ public interface ColumnAdapter<T> { } /** + * If possible, return a fast estimation of the dataset envelope based on this column geometry. + * + * @implNote Note that no cache system should be implemented here, as we query a valid connection for direct + * computation. However, containers are free to cache returned information at their convenience. + * + * @param target The database connection to use to compute the envelope. + * @return If available, the envelope of the current column. Otherwise, an empty shell. + * @throws SQLException If we fail fetching information from database. + */ + default Optional<Envelope> getEnvelope(final Connection target) throws SQLException { return Optional.empty(); } + + /** * * @return The (possibly parent) type of objects read by this mapper. Note that it MUST NOT return null values. */ diff --git a/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/FeatureAdapter.java b/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/FeatureAdapter.java index 061e9d2..9668b55 100644 --- a/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/FeatureAdapter.java +++ b/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/FeatureAdapter.java @@ -22,10 +22,16 @@ import java.sql.SQLException; import java.util.ArrayList; import java.util.Collections; import java.util.List; +import java.util.Objects; +import java.util.Optional; import java.util.stream.Collectors; import org.opengis.feature.Feature; import org.opengis.feature.FeatureType; +import org.opengis.geometry.Envelope; + +import org.apache.sis.internal.feature.Geometries; +import org.apache.sis.util.collection.BackingStoreException; import static org.apache.sis.util.ArgumentChecks.ensureNonNull; @@ -75,6 +81,30 @@ class FeatureAdapter { return new ResultSetAdapter(rtu); } + + public Optional<Envelope> getEnvelope(final Connection target) throws SQLException { + // TODO: Allow user to specify a specific column + try { + return attributeMappers.stream() + .map(mapper -> mapper.fetchValue) + .filter(column -> Geometries.isKnownType(column.getJavaType())) + .map(column -> getEnvelope(target, column)) + .filter(Objects::nonNull) + // TODO: that's not accurate, but has the benefit to avoid heavy-weight trasforms in a common-space. + .findAny(); + } catch (BackingStoreException e) { + throw e.unwrapOrRethrow(SQLException.class); + } + } + + private Envelope getEnvelope(Connection target, ColumnAdapter<?> column) { + try { + return column.getEnvelope(target).orElse(null); + } catch (SQLException e) { + throw new BackingStoreException(e); + } + } + /** * Specialization of {@link FeatureAdapter} as a short-live object, able to use a database connection to load third- * party data. @@ -146,7 +176,7 @@ class FeatureAdapter { // nature, and an indexed implementation could (to verify, still) be bad on memory footprint. final String propertyName; final int columnIndex; - final ColumnAdapter fetchValue; + final ColumnAdapter<?> fetchValue; PropertyMapper(String propertyName, int columnIndex, ColumnAdapter fetchValue) { this.propertyName = propertyName; diff --git a/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/PostGISMapping.java b/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/PostGISMapping.java index a2dffbf..4445234 100644 --- a/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/PostGISMapping.java +++ b/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/PostGISMapping.java @@ -17,6 +17,7 @@ package org.apache.sis.internal.sql.feature; import java.sql.Connection; +import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; @@ -25,10 +26,15 @@ import java.util.Optional; import java.util.logging.Level; import java.util.logging.Logger; +import org.opengis.geometry.Envelope; import org.opengis.referencing.crs.CoordinateReferenceSystem; +import org.apache.sis.geometry.DirectPosition2D; +import org.apache.sis.geometry.Envelope2D; import org.apache.sis.internal.feature.Geometries; import org.apache.sis.internal.metadata.sql.Dialect; +import org.apache.sis.internal.metadata.sql.SQLBuilder; +import org.apache.sis.referencing.CRS; import org.apache.sis.setup.GeometryLibrary; import org.apache.sis.util.collection.BackingStoreException; import org.apache.sis.util.collection.Cache; @@ -109,11 +115,11 @@ public final class PostGISMapping implements DialectMapping { final Class geomClass = getGeometricClass(geometryType, library); if (geomDef == null || geomDef.crs == null) { - return new HexEWKBDynamicCrs(geomClass); + return new HexEWKBDynamicCrs(geomClass, definition); } else { // TODO: activate optimisation : WKB is lighter, but we need to modify user query, and to know CRS in advance. //geometryDecoder = new WKBReader(geomDef.crs); - return new HexEWKBFixedCrs(geomClass, geomDef.crs); + return new HexEWKBFixedCrs(geomClass, definition, geomDef.crs); } } @@ -154,14 +160,43 @@ public final class PostGISMapping implements DialectMapping { } } - private final class HexEWKBFixedCrs extends Reader { + private abstract class PostGisReader extends Reader { + + final SQLColumn columnDefinition; final CoordinateReferenceSystem crsToApply; - public HexEWKBFixedCrs(Class geomClass, CoordinateReferenceSystem crsToApply) { + public PostGisReader(Class geomClass, final SQLColumn colDef, CoordinateReferenceSystem crsToApply) { super(geomClass); + this.columnDefinition = colDef; this.crsToApply = crsToApply; } + /** + * @implNote Use PostGIS <a href="https://postgis.net/docs/ST_EstimatedExtent.html";>ST_EstimatedExtent</a> + * function to get a rough estimation of column extent. If it returns no result, we fallback on + * <a href="https://postgis.net/docs/ST_Extent.html";>ST_Extent</a> to get information. + * @return Envelope of the column if available, else nothing. + */ + @Override + public Optional<Envelope> getEnvelope(final Connection target) throws SQLException { + if (columnDefinition.origin != null) { + EnvelopeEstimator estimator = new EnvelopeEstimator(target, columnDefinition.naming, columnDefinition.origin, crsToApply); + + Optional<Envelope> env = estimator.execute(true); + if (!env.isPresent()) env = estimator.execute(false); + + return env; + } + return Optional.empty(); + } + } + + private final class HexEWKBFixedCrs extends PostGisReader { + + public HexEWKBFixedCrs(Class geomClass, final SQLColumn colDef, CoordinateReferenceSystem crsToApply) { + super(geomClass, colDef, crsToApply); + } + @Override public SQLBiFunction prepare(Connection target) { return new HexEWKBReader(new EWKBReader(library).forCrs(crsToApply)); @@ -173,10 +208,10 @@ public final class PostGISMapping implements DialectMapping { } } - private final class HexEWKBDynamicCrs extends Reader { + private final class HexEWKBDynamicCrs extends PostGisReader { - public HexEWKBDynamicCrs(Class geomClass) { - super(geomClass); + public HexEWKBDynamicCrs(Class geomClass, final SQLColumn colDef) { + super(geomClass, colDef, null); } @Override @@ -210,4 +245,52 @@ public final class PostGISMapping implements DialectMapping { return hexa == null ? null : reader.readHexa(hexa); } } + + private static final class EnvelopeEstimator { + final SQLBuilder builder; + final Connection target; + private final ColumnRef colRef; + private final TableReference table; + final CoordinateReferenceSystem crs; + + private EnvelopeEstimator(final Connection target, final ColumnRef colRef, final TableReference table, final CoordinateReferenceSystem optCrs) throws SQLException { + builder = new SQLBuilder(target.getMetaData(), false); + this.target = target; + this.colRef = colRef; + this.table = table; + + if (optCrs != null) { + crs = CRS.getHorizontalComponent(optCrs); + } else crs = null; + } + + private Optional<Envelope> execute(boolean fast) throws SQLException { + builder.clear(); + builder + .append("SELECT st_xmin(box) as minx, st_ymin(box) as miny, st_xmax(box) as maxx, st_ymax(box) as maxy") + .append(" FROM (") + .append("SELECT "+(fast ? "ST_EstimatedExtent" : "ST_Extent")+"(") + .appendIdentifier(colRef.getColumnName()) + .append(") as box FROM ") + .appendIdentifier(table.schema, table.table) + .append(") as sub") + .toString(); + + try ( + PreparedStatement query = target.prepareStatement("TODO"); + ResultSet dbResult = query.executeQuery() + ) { + if (dbResult.next()) { + final Envelope2D env = new Envelope2D( + new DirectPosition2D(dbResult.getDouble(1), dbResult.getDouble(2)), + new DirectPosition2D(dbResult.getDouble(3), dbResult.getDouble(4)) + ); + if (crs != null) env.setCoordinateReferenceSystem(crs); + return Optional.of(env); + } + } + + return Optional.empty(); + } + } } diff --git a/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/Table.java b/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/Table.java index 9669c29..606d858 100644 --- a/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/Table.java +++ b/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/Table.java @@ -34,7 +34,9 @@ import org.opengis.feature.AttributeType; import org.opengis.feature.Feature; import org.opengis.feature.FeatureAssociationRole; import org.opengis.feature.FeatureType; +import org.opengis.feature.PropertyNotFoundException; import org.opengis.feature.PropertyType; +import org.opengis.geometry.Envelope; import org.opengis.util.GenericName; import org.apache.sis.internal.metadata.sql.Reflection; @@ -196,7 +198,16 @@ final class Table extends AbstractFeatureSet { this.importedKeys = toArray(specification.getImports()); this.exportedKeys = toArray(specification.getExports()); this.primaryKeyClass = primaryKeys.length < 2 ? Object.class : Object[].class; - this.hasGeometry = specification.getPrimaryGeometryColumn().isPresent(); + boolean geometryFound; + try { + org.apache.sis.feature.Features.getDefaultGeometry(featureType); + geometryFound = true; + } catch (PropertyNotFoundException e) { + geometryFound= false; + } catch (IllegalStateException e) { + geometryFound = true; + } + this.hasGeometry = geometryFound; this.attributes = Collections.unmodifiableList( specification.getColumns().stream() .map(column -> column.naming) @@ -204,6 +215,19 @@ final class Table extends AbstractFeatureSet { ); } + + @Override + public Optional<Envelope> getEnvelope() throws DataStoreException { + if (hasGeometry) { + try (Connection target = Database.connectReadOnly(source)) { + return adapter.getEnvelope(target); + } catch (SQLException e) { + throw new DataStoreException("Database interrogation for an envelope failed", e); + } + } + return Optional.empty(); + } + @Override public FeatureSet subset(Query query) throws DataStoreException { if (query instanceof SimpleQuery) {
