Rebuild the index by creating a trigger

[sunshine0576]

The index must be updated manually.Is there a plan to provide automatic updating index?

        // Update a Geometry 
        TestCase.assertEquals(1, featureDao.update(testFeatureRow));
        //update the index of a single feature row
        TestCase.assertTrue(featureIndexManager.index(testFeatureRow));

When I build a new Geopacakge database in QGIS, QGIS will help me create related indexes and triggers automatically.I didn't care about index maintenance when I was working, and simplified my work.

CREATE TABLE "taiwan-EPSG_3857" ( "fid" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "geom" LINESTRING, "ID" MEDIUMINT, "ELEV" REAL);

CREATE TRIGGER "trigger_insert_feature_count_taiwan-EPSG_3857" AFTER INSERT ON "taiwan-EPSG_3857" BEGIN UPDATE gpkg_ogr_contents SET feature_count = feature_count + 1 WHERE lower(table_name) = lower('taiwan-EPSG_3857'); END;

CREATE TRIGGER "rtree_taiwan-EPSG_3857_geom_update3" AFTER UPDATE ON "taiwan-EPSG_3857" WHEN OLD."fid" != NEW."fid" AND (NEW."geom" NOTNULL AND NOT ST_IsEmpty(NEW."geom")) BEGIN DELETE FROM "rtree_taiwan-EPSG_3857_geom" WHERE id = OLD."fid"; INSERT OR REPLACE INTO "rtree_taiwan-EPSG_3857_geom" VALUES (NEW."fid",ST_MinX(NEW."geom"), ST_MaxX(NEW."geom"),ST_MinY(NEW."geom"), ST_MaxY(NEW."geom")); END;

CREATE TRIGGER "rtree_taiwan-EPSG_3857_geom_update4" AFTER UPDATE ON "taiwan-EPSG_3857" WHEN OLD."fid" != NEW."fid" AND (NEW."geom" ISNULL OR ST_IsEmpty(NEW."geom")) BEGIN DELETE FROM "rtree_taiwan-EPSG_3857_geom" WHERE id IN (OLD."fid", NEW."fid"); END;

CREATE TRIGGER "trigger_delete_feature_count_taiwan-EPSG_3857" AFTER DELETE ON "taiwan-EPSG_3857" BEGIN UPDATE gpkg_ogr_contents SET feature_count = feature_count - 1 WHERE lower(table_name) = lower('taiwan-EPSG_3857'); END;

CREATE TRIGGER "rtree_taiwan-EPSG_3857_geom_update2" AFTER UPDATE OF "geom" ON "taiwan-EPSG_3857" WHEN OLD."fid" = NEW."fid" AND (NEW."geom" ISNULL OR ST_IsEmpty(NEW."geom")) BEGIN DELETE FROM "rtree_taiwan-EPSG_3857_geom" WHERE id = OLD."fid"; END;

CREATE TRIGGER "rtree_taiwan-EPSG_3857_geom_delete" AFTER DELETE ON "taiwan-EPSG_3857" WHEN old."geom" NOT NULL BEGIN DELETE FROM "rtree_taiwan-EPSG_3857_geom" WHERE id = OLD."fid"; END;

CREATE TRIGGER "rtree_taiwan-EPSG_3857_geom_insert" AFTER INSERT ON "taiwan-EPSG_3857" WHEN (new."geom" NOT NULL AND NOT ST_IsEmpty(NEW."geom")) BEGIN INSERT OR REPLACE INTO "rtree_taiwan-EPSG_3857_geom" VALUES (NEW."fid",ST_MinX(NEW."geom"), ST_MaxX(NEW."geom"),ST_MinY(NEW."geom"), ST_MaxY(NEW."geom")); END;

CREATE TRIGGER "rtree_taiwan-EPSG_3857_geom_update1" AFTER UPDATE OF "geom" ON "taiwan-EPSG_3857" WHEN OLD."fid" = NEW."fid" AND (NEW."geom" NOTNULL AND NOT ST_IsEmpty(NEW."geom")) BEGIN INSERT OR REPLACE INTO "rtree_taiwan-EPSG_3857_geom" VALUES (NEW."fid",ST_MinX(NEW."geom"), ST_MaxX(NEW."geom"),ST_MinY(NEW."geom"), ST_MaxY(NEW."geom")); END;

[sunshine0576]

This is the data I used in QGIS.

taiwan-EPSG_3857.zip

[sunshine0576]

https://www.sqlite.org/rtree.html

[bosborn]

There are some RTree changes coming in the next release of core and Android. RTree is not supported by the the default Android SQLite library, so a dependency on the SQLite Android Bindings is being added. The bindings still do not support user defined functions (ST_MinX, etc), so the Android RTree support will be read only.

The Feature Index Manager is getting an overhaul to support the RTree Extension, Geometry Index Extension, App Metadata Index, and manual unindexed queries.

I haven't found or thought of an easy way to support index triggers on a geometry blob without user defined function support. Similarly, how to support SQLite user defined functions without adding NDK dependencies.

[sunshine0576]

https://www.sqlite.org/android/doc/trunk/www/install.wiki I don't know whether this will affect other functions（com.j256.ormlite may be need to recompile.）.

[bosborn]

Right, unreleased geopackage-android version 3.1.0 is already including the "Pre-Built aar File". A version released into the central repo so users of this library are not forced to include it themselves. This provides read access to the RTree extension.

But... the SQLite Android Bindings do not support user defined functions that return values. Line 250 : // TODO: Support functions that return values.