Application: gvSIG desktop

@begin create_rtree_table

@param table_name

@param geom_name

CREATE VIRTUAL TABLE "rtree_%table_name$s_%geom_name$s" USING rtree(id, minx, maxx, miny, maxy)

@end create_rtree_table

@begin create_table_gpkg_extensions

CREATE TABLE IF NOT EXISTS gpkg_extensions (

    table_name TEXT, 

    column_name TEXT, 

    extension_name TEXT NOT NULL, 

    definition TEXT NOT NULL, 

    scope TEXT NOT NULL, 

    CONSTRAINT ge_tce UNIQUE (table_name, column_name, extension_name) )

@end create_table_gpkg_extensions

@begin insert_into_table_gpkg_extensions

@param table_name

@param geom_name

INSERT INTO "gpkg_extensions" (

    "table_name", 

    "column_name", 

    "extension_name", 

    "definition", 

    "scope"

) VALUES (

    '%table_name$s', 

    '%geom_name$s', 

    'gpkg_rtree_index', 

    'http://www.geopackage.org/spec/#extension_rtree', 

    'read-write'

)

@end insert_into_table_gpkg_extensions

@begin bboxintersects

@param table_name

@param id_name

@param geom_name

@param minX

@param minY

@param maxX

@param maxY

("%id_name$s" IN (SELECT id FROM "rtree_%table_name$s_%geom_name$s" WHERE minx <= (%maxX$s) AND miny <= (%maxY$s) AND maxx >= (%minX$s) AND maxy >= (%minY$s)))

@end bboxintersects

@begin has_index

@param table_name

@param geom_name

select count(1) from gpkg_extensions where table_name = '%table_name$s' and column_name = '%geom_name$s' and extension_name = 'gpkg_rtree_index'

@end has_index