GPKG_SPATIAL_INDEX top-level constant
Implementation
const GPKG_SPATIAL_INDEX = [
"""
CREATE VIRTUAL TABLE rtree_TTT_CCC USING rtree(id, minx, maxx, miny, maxy);
""",
"""
INSERT OR REPLACE INTO rtree_TTT_CCC
SELECT III, ST_MinX(CCC), ST_MaxX(CCC), ST_MinY(CCC), ST_MaxY(CCC) FROM TTT
WHERE NOT ST_IsEmpty(CCC);
""",
// -- Conditions: Insertion of non-empty geometry
// -- Actions : Insert record into rtree
"""
CREATE TRIGGER rtree_TTT_CCC_insert AFTER INSERT ON TTT
WHEN (new.CCC NOT NULL AND NOT ST_IsEmpty(NEW.CCC))
BEGIN
INSERT OR REPLACE INTO rtree_TTT_CCC VALUES (
NEW.III,
ST_MinX(NEW.CCC), ST_MaxX(NEW.CCC),
ST_MinY(NEW.CCC), ST_MaxY(NEW.CCC)
);
END;
""",
// -- Conditions: Update of geometry column to non-empty geometry
// -- No row ID change
// -- Actions : Update record in rtree
"""
CREATE TRIGGER rtree_TTT_CCC_update1 AFTER UPDATE OF CCC ON TTT
WHEN OLD.III = NEW.III AND
(NEW.CCC NOTNULL AND NOT ST_IsEmpty(NEW.CCC))
BEGIN
INSERT OR REPLACE INTO rtree_TTT_CCC VALUES (
NEW.III,
ST_MinX(NEW.CCC), ST_MaxX(NEW.CCC),
ST_MinY(NEW.CCC), ST_MaxY(NEW.CCC)
);
END;
""",
// -- Conditions: Update of geometry column to empty geometry
// -- No row ID change
// -- Actions : Remove record from rtree
"""
CREATE TRIGGER rtree_TTT_CCC_update2 AFTER UPDATE OF CCC ON TTT
WHEN OLD.III = NEW.III AND
(NEW.CCC ISNULL OR ST_IsEmpty(NEW.CCC))
BEGIN
DELETE FROM rtree_TTT_CCC WHERE id = OLD.III;
END;
""",
// -- Conditions: Update of any column
// -- Row ID change
// -- Non-empty geometry
// -- Actions : Remove record from rtree for old III
// -- Insert record into rtree for new III
"""
CREATE TRIGGER rtree_TTT_CCC_update3 AFTER UPDATE OF CCC ON TTT
WHEN OLD.III != NEW.III AND
(NEW.CCC NOTNULL AND NOT ST_IsEmpty(NEW.CCC))
BEGIN
DELETE FROM rtree_TTT_CCC WHERE id = OLD.III;
INSERT OR REPLACE INTO rtree_TTT_CCC VALUES (
NEW.III,
ST_MinX(NEW.CCC), ST_MaxX(NEW.CCC),
ST_MinY(NEW.CCC), ST_MaxY(NEW.CCC)
);
END;
""",
// -- Conditions: Update of any column
// -- Row ID change
// -- Empty geometry
// -- Actions : Remove record from rtree for old and new III
"""
CREATE TRIGGER rtree_TTT_CCC_update4 AFTER UPDATE ON TTT
WHEN OLD.III != NEW.III AND
(NEW.CCC ISNULL OR ST_IsEmpty(NEW.CCC))
BEGIN
DELETE FROM rtree_TTT_CCC WHERE id IN (OLD.III, NEW.III);
END;
""",
// -- Conditions: Row deleted
// -- Actions : Remove record from rtree for old III
"""
CREATE TRIGGER rtree_TTT_CCC_delete AFTER DELETE ON TTT
WHEN old.CCC NOT NULL
BEGIN
DELETE FROM rtree_TTT_CCC WHERE id = OLD.III;
END;
""",
// -- Register the spatial index extension for this table/column
"""
INSERT INTO $TABLE_EXTENSIONS(table_name, column_name, extension_name, definition, scope)
VALUES('TTT', 'CCC', 'gpkg_rtree_index', 'GeoPackage 1.0 Specification Annex L', 'write-only');
"""
];