1

I want to write a GeoPackage file with GeoTools and create a spatial index in the end. After consulting the documentation and also the implementation I still can not figure out a way to do this using the DataStore-based high-level API.

My questions are:

  1. is there a way to write an rtree-based spatial index with the DataStore API? I figure JDBCDataStore.createIndex is not what we want. See createIndexHighLevelApi in the code below.
  2. Assuming the index can only be created using the low-level API I tried to create the index after populating the GeoPackage with the high-level API. But this didn't work either as can be seen in createSpatialIndexLowLevelApiOnExistingGpkg below. What am I missing here?
    • java.sql.BatchUpdateException: batch entry 1: [SQLITE_ERROR] SQL error or missing database (no such function: ST_MinX)
import java.io.File;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import org.geotools.data.DataStore;
import org.geotools.data.DataStoreFinder;
import org.geotools.data.DataUtilities;
import org.geotools.data.collection.ListFeatureCollection;
import org.geotools.data.simple.SimpleFeatureCollection;
import org.geotools.data.simple.SimpleFeatureStore;
import org.geotools.feature.simple.SimpleFeatureBuilder;
import org.geotools.feature.simple.SimpleFeatureTypeBuilder;
import org.geotools.geometry.jts.JTSFactoryFinder;
import org.geotools.geopkg.FeatureEntry;
import org.geotools.geopkg.GeoPackage;
import org.geotools.geopkg.GeoPkgDataStoreFactory;
import org.geotools.jdbc.Index;
import org.geotools.jdbc.JDBCDataStore;
import org.geotools.jdbc.JDBCDataStoreFactory;
import org.locationtech.jts.geom.Coordinate;
import org.locationtech.jts.geom.GeometryFactory;
import org.locationtech.jts.geom.LineString;
import org.opengis.feature.simple.SimpleFeature;
import org.opengis.feature.simple.SimpleFeatureType;

import com.google.common.base.Joiner;

public class GpkgSpatialIndexTest {

    private static final String GEOMETRY_COLUMN = "geom";

    public static void main(String[] args) throws IOException, InterruptedException {
        SimpleFeatureType featureType = createType();
        SimpleFeatureCollection featureCollection = createFeatureCollection(featureType, 100);

        writeFeaturesAndCreateIndexWithLowLevelApi(featureCollection);
        writeFeaturesAndCreateIndexWithDataStoreApi(featureType, featureCollection);
    }

    private static void writeFeaturesAndCreateIndexWithLowLevelApi(SimpleFeatureCollection featureCollection)
            throws IOException {
        File file = File.createTempFile("low_level_api", ".gpkg");
        GeoPackage geopkg = new GeoPackage(file);
        geopkg.init();

        FeatureEntry entry = new FeatureEntry();
        entry.setDescription("Cities of the world");
        geopkg.add(entry, featureCollection);
        geopkg.createSpatialIndex(entry);
    }

    public static void writeFeaturesAndCreateIndexWithDataStoreApi(SimpleFeatureType featureType,
            SimpleFeatureCollection featureCollection) throws IOException, InterruptedException {
        HashMap<String, Object> map = new HashMap<>();
        map.put(GeoPkgDataStoreFactory.DBTYPE.key, GeoPkgDataStoreFactory.DBTYPE.sample);
        Path path = Files.createTempFile("datastore_api", ".gpkg");
        map.put(GeoPkgDataStoreFactory.DATABASE.key, path.toString());
        map.put(JDBCDataStoreFactory.BATCH_INSERT_SIZE.key, 50);

        DataStore dataStore = DataStoreFinder.getDataStore(map);
        dataStore.createSchema(featureType);
        SimpleFeatureStore featureStore = (SimpleFeatureStore) dataStore.getFeatureSource(featureType.getTypeName());
        featureStore.addFeatures(DataUtilities.collection(featureCollection));

        // first try. but that's not what we want - we want an rtree index!
        // also it fails:
        // org.sqlite.SQLiteException: [SQLITE_CONSTRAINT_UNIQUE] A UNIQUE constraint
        // failed (UNIQUE constraint failed: layerName.geom)
        try {
            createIndexHighLevelApi(dataStore, featureType);
        } catch (Exception e) {
            e.printStackTrace();
        }

        dataStore.dispose();

        // 2nd try. let's use the low level api after the datastore
        // fails as well:
        // Exception in thread "main" java.io.IOException:
        // java.sql.BatchUpdateException: batch entry 1: [SQLITE_ERROR] SQL error or
        // missing database (no such function: ST_MinX)
        createSpatialIndexLowLevelApiOnExistingGpkg(path.toFile(), featureType.getTypeName());
    }

    private static void createIndexHighLevelApi(DataStore dataStore, SimpleFeatureType featureType) throws IOException {
        if (dataStore instanceof JDBCDataStore) {
            String layerName = featureType.getTypeName();
            ((JDBCDataStore) dataStore).createIndex(new Index(layerName,
                    Joiner.on("_").join(layerName, GEOMETRY_COLUMN, "idx"), true, GEOMETRY_COLUMN));
        }
    }

    private static void createSpatialIndexLowLevelApiOnExistingGpkg(File file, String layerName) throws IOException {
        GeoPackage gpkg = new GeoPackage(file);
        gpkg.init();
        FeatureEntry layer = gpkg.feature(layerName);
        if (layer != null) {
            // force lazy initialization of dataStore, otherwise we get a
            // NullPointerException in the next step
            gpkg.writer(layer, true, null, null);
            gpkg.createSpatialIndex(layer);
        }
        gpkg.close();
    }

    private static SimpleFeatureType createType() {
        SimpleFeatureTypeBuilder builder = new SimpleFeatureTypeBuilder();
        String layerName = "layerName";
        builder.setName(layerName);
        builder.setSRS("EPSG:4326");
        builder.add(GEOMETRY_COLUMN, LineString.class);
        builder.add("id", Integer.class);
        return builder.buildFeatureType();
    }

    private static SimpleFeatureCollection createFeatureCollection(SimpleFeatureType featureType, int count) {
        GeometryFactory gf = JTSFactoryFinder.getGeometryFactory();
        Coordinate[] coordinates = new Coordinate[] { new Coordinate(16, 48), new Coordinate(14, 48) };
        LineString lineString = gf.createLineString(coordinates);
        List<SimpleFeature> features = new ArrayList<>();
        for (int i = 0; i < count; i++) {
            Object[] attributes = new Object[] { lineString, i };
            SimpleFeature feature = SimpleFeatureBuilder.build(featureType, attributes, null);
            features.add(feature);
        }
        return new ListFeatureCollection(featureType, features);
    }

}

1 Answer 1

1

I would look at the unit test:

    FeatureEntry entry = new FeatureEntry();
    entry.setBounds(coll.getBounds());
    geopkg.create(entry, shp.getSchema());

    // write some features before and some after
    try (SimpleFeatureIterator it = coll.features()) {

        // some features
        try (Transaction tx = new DefaultTransaction();
                SimpleFeatureWriter w = geopkg.writer(entry, true, null, tx)) {
            for (int i = 0; i < 3; i++) {
                SimpleFeature f = it.next();
                SimpleFeature g = w.next();
                for (PropertyDescriptor pd : coll.getSchema().getDescriptors()) {
                    String name = pd.getName().getLocalPart();
                    g.setAttribute(name, f.getAttribute(name));
                }

                w.write();
            }
            tx.commit();
        }

        // create spatial index
        geopkg.createSpatialIndex(entry);

        // the rest of features
        try (Transaction tx = new DefaultTransaction();
                SimpleFeatureWriter w = geopkg.writer(entry, true, null, tx)) {
            while (it.hasNext()) {
                SimpleFeature f = it.next();
                SimpleFeature g = w.next();
                for (PropertyDescriptor pd : coll.getSchema().getDescriptors()) {
                    String name = pd.getName().getLocalPart();
                    g.setAttribute(name, f.getAttribute(name));
                }

                w.write();
            }
            tx.commit();
        }
    }
2
  • The unit test does not provide much more detail imho. I managed to create a Geopackage with the low-level API which is used there. What I could not achieve was to create an index using the DataStore API. So I figure it's simply not possible?
    – evod
    Commented Nov 24, 2022 at 17:01
  • Not all DataStore's have indexes so I would expect to need to use a datastore specific method
    – Ian Turton
    Commented Nov 24, 2022 at 17:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.