A fast index can find the wrong set if its relationship with the geometry has drifted
A park is present in a GeoPackage’s feature table, but a map query does not return it. Opening the geometry directly shows it inside the requested region. One possible explanation is a spatial index whose stored bounds no longer match the geometry. The file can remain readable while a particular accelerated query loses evidence.
This guide explains the relationship between source geometry, its bounding rectangle, an RTree index and an exact spatial test. It is not another general introduction to the R-tree data structure. Its focus is the practical audit: how a derived search structure can remain internally tidy yet become inconsistent with the records it is supposed to help find.
All coordinates and failure cases below are invented teaching examples in a simple planar system. Do not experiment on the only copy of an operational dataset. Read-only inspection and authorised test copies should precede any repair, and actual schema or index maintenance should use compatible GeoPackage-aware tools and the relevant specification version.
Geoinformatics subject guide · Learning that transfers into life · How X Works hub
Choose your reading route
Route 1: Understand the dependency · Chapters 1–3
Route 2: Work through drift · Chapters 4–5
Route 3: Inspect maintenance and checks · Chapters 6–8
Route 4: Validate and repair carefully · Chapters 9–10
Route 5: Transfer and report · Chapters 11–12
Transfer the same reasoning to other derived search structures
Full chapter index · Independent worked case · Questions and practical checks
Chapter index
Understand the dependency · Chapters 1–3
Separate the geometry from its search aid
Read the GeoPackage contract at the right level
Understand conservative candidate filtering
Work through drift · Chapters 4–5
Work a moved-feature example
Distinguish missing, extra and wrong-bound entries
Inspect maintenance and checks · Chapters 6–8
Inspect maintenance behaviour, not just trigger names
Separate internal tree integrity from source consistency
Account for floating-point bounds without hiding real drift
Validate and repair carefully · Chapters 9–10
Compare against a genuinely independent retrieval path
Repair only the derived component that is actually wrong
Transfer and report · Chapters 11–12
Transfer the same reasoning to other derived search structures
Report separate claims about correctness and performance
CHAPTER 1 OF 12
1. Separate the geometry from its search aid
A feature table stores the geographic objects and their attributes. A spatial index stores a derived structure that helps select promising records efficiently. For a two-dimensional RTree, an entry can carry an object identifier and minimum and maximum x and y coordinates. Those bounds describe an envelope, not the complete shape.
Imagine a winding river. Its rectangular envelope includes places the river never touches. An index search can quickly reject rectangles far from a query, but surviving candidates may still require an exact geometry test. The index makes the search cheaper; it does not replace the meaning of the geographic relationship.
This creates a dependency: the stored envelope must remain an appropriate bound for the current geometry. If the geometry changes, the associated search entry may need updating. An index can be a valid tree of rectangles while those rectangles describe yesterday’s objects rather than today’s ones.
The gold-standard question is therefore not merely whether an index exists. It is whether the index, source geometry and query rule agree for the same dataset state. Think of a library catalogue pointing to books: the catalogue can be well sorted while still pointing to shelves where the books no longer stand.
CHAPTER 2 OF 12
2. Read the GeoPackage contract at the right level
GeoPackage uses a SQLite container with standardised spatial structures. Its R-tree Spatial Indexes extension is identified as gpkg_rtree_index. The extension connects feature geometry columns with SQLite RTree virtual tables and maintenance triggers. Its purpose is efficient retrieval without changing the intended result of reads.
The existence of the extension is not the same as successful implementation or current consistency. A declaration tells a reader which mechanism is intended. The table, population, trigger behaviour and resulting query evidence need their own checks. A caption saying spatial index enabled is not a complete audit.
The approved GeoPackage 1.4.0 document, OGC 12-128r19, gives version-specific requirements and trigger templates. Use the authoritative published document rather than an unversioned draft copy when evaluating compliance. Older packages and toolchains can have different trigger histories, so do not compare them to a random snippet without identifying the relevant version.
This article teaches the logic of the checks, not a replacement implementation of the standard. For operational changes, use a maintained driver and current conformance guidance. Hand-editing schema objects because their names look familiar can break relationships beyond the one viewport being investigated.
CHAPTER 3 OF 12
3. Understand conservative candidate filtering
Let a query rectangle cover x = 10–20 and y = 10–20. An indexed object is a candidate for an overlap search if its stored rectangle overlaps that query. In a simple closed-interval test, there is no overlap when one rectangle lies completely beyond another along x or y. Otherwise the candidate survives.
A candidate is not yet an exact hit. A crescent-shaped polygon could have a large envelope covering a query that lies entirely in the crescent’s empty middle. The envelope comparison admits it, and a subsequent exact intersects test rejects it. That false positive at the candidate stage is expected and can be harmless.
A false negative at the candidate stage is more serious. If the index rejects a real intersecting object, the exact test may never receive it. No precision in the second stage can recover an object that the first stage has already excluded. Candidate filtering must therefore preserve all objects needed by the intended exact result.
Keep the two stages separate in diagnostics: candidate IDs and final exact-hit IDs. A large candidate set can indicate poor selectivity without incorrect results. A small fast set can hide missing objects. Performance and completeness are related engineering goals, but neither proves the other.
CHAPTER 4 OF 12
4. Work a moved-feature example
A fictional point P1 originally lies at (5,5), with index bounds x = 5–5 and y = 5–5. An authorised edit moves the source geometry to (15,15). Suppose, as a deliberate failure scenario, the corresponding index entry is not updated and still records the old position.
Query the rectangle x = 10–20 and y = 10–20. A direct exact test on the current point includes P1. An overlap filter using its stale index rectangle rejects P1 because the old rectangle is outside the query. If the accelerated path depends on those candidates, the final result misses an object that actually qualifies.
Now query a small box around the old location. The stale index can admit P1 as a candidate, but an exact test on the current geometry rejects it. This second result is merely extra work if the exact stage runs correctly. The first result is an omission. The same stale entry can therefore cause different symptoms in different places.
The example does not claim that normal GeoPackage-aware edits routinely fail this way. It isolates the consequence if the maintenance relationship is broken. That distinction matters: a teaching counterexample explains why a check is needed, not the observed failure rate of a particular product.
CHAPTER 5 OF 12
5. Distinguish missing, extra and wrong-bound entries
Index drift has several forms. A source object with no index entry can be absent from candidate-based searches. An orphan index entry can refer to a deleted or nonexistent source ID. A present entry with wrong bounds can return the object for some regions and omit it for others.
These cases need different comparisons. Match eligible source IDs against index IDs to detect missing and orphan entries. Compare stored bounds against bounds derived from the current geometry to detect inconsistent envelopes. Matching row counts alone cannot establish either relationship.
For an original count trap, suppose the source has IDs 1, 2 and 3, while the index has IDs 1, 2 and 9. Both contain three rows. The equal counts conceal missing ID 3 and orphan ID 9. A set comparison identifies the mismatch immediately. A second fixture with IDs 1, 2 and 3 but stale bounds shows why correct IDs still do not prove correct geometry correspondence.
Null and empty geometries need deliberate treatment under the specification and tool behaviour. They should not be mistaken for ordinary bounded objects. Determine the eligible population before comparing counts, and distinguish a legitimate absence from the index from an omitted usable geometry. Otherwise a well-intentioned repair can insert meaningless entries merely to make totals match.
CHAPTER 6 OF 12
6. Inspect maintenance behaviour, not just trigger names
The GeoPackage spatial-index extension specifies population and maintenance of index values, including insert, update and delete behaviour. A trigger’s name can suggest that the mechanism exists, but its actual definition and compatibility with the package version matter. A familiar label does not prove that every relevant edit path is covered.
Use an authorised test copy to exercise distinct state changes: insert a nonempty geometry, move it, change it to an empty or null state where permitted, restore it, change an identifier under the supported rules and delete it. Observe the resulting source and index states after each supported operation.
The objective is not to invent a new trigger implementation. It is to verify that the toolchain preserves the declared relationship. The published specification documents changes to older update-trigger designs, illustrating why version-aware testing matters. Copying an old maintenance fragment into a newer package without review can preserve a historical problem.
Keep tests reversible and outside production unless explicitly authorised. Record the software version and edit operation, not merely that a SQL statement succeeded. A write can be accepted while a derived structure is left inconsistent by an unsupported workflow. The acceptance condition is the correct resulting state, not the absence of an error message.
CHAPTER 7 OF 12
7. Separate internal tree integrity from source consistency
SQLite provides rtreecheck to test the internal structure of an RTree virtual table. Its documented checks concern relationships within the tree and its supporting structures. A clean result is useful evidence that the tree is well formed. It does not by itself compare every stored envelope against the current geometry in an unrelated feature table.
Our moved-point example can remain a perfectly consistent tree containing the rectangle at (5,5). The problem is external correspondence: P1’s current geometry is at (15,15). An internal integrity check has no reason to infer that real-world or source-table relationship unless a separate comparison supplies it.
Similarly, a general database integrity check and a GeoPackage conformance check have different scopes. They can reveal important structural problems without proving that every published spatial claim is correct. Treat passing checks as evidence about their stated invariants, not as universal certificates of geographic truth.
Build an evidence ladder: file opens; database structures pass their checks; extension structures conform; source IDs and envelopes reconcile; indexed and independent exact queries agree over appropriate tests. Each step adds information. Skipping directly from the first or second step to complete spatial reliability leaves a substantial gap.
CHAPTER 8 OF 12
8. Account for floating-point bounds without hiding real drift
SQLite’s ordinary RTree stores coordinate bounds using 32-bit floating-point values. Its documentation explains that lower bounds are rounded downward and upper bounds upward. This can make stored envelopes slightly larger than the original values. Tiny outward differences are therefore not automatically stale-index errors.
That conservative rounding is useful for overlap candidate searches because it can admit a few extra edge candidates rather than shrink the stored envelope. A contained-within query has different edge behaviour: an outward-rounded envelope may fail an exact containment comparison at a boundary. The query design and the intended final predicate must be considered together.
Do not respond by demanding byte-for-byte equality between double-precision geometry bounds and stored index numbers. Instead, apply a documented, implementation-aware comparison and retain exact geometry checks where required. Equally, do not use roundoff as an excuse for a point whose recorded bounds are kilometres from its actual geometry.
Separate representational tolerance from positional uncertainty. The first concerns numeric storage and algorithmic comparison. The second concerns how accurately coordinates describe the real object. A tiny storage tolerance cannot repair a wrong datum or an inaccurate survey, and a broad physical uncertainty interval should not conceal broken index maintenance.
CHAPTER 9 OF 12
9. Compare against a genuinely independent retrieval path
To test completeness, compare the accelerated query with a reference path that evaluates the intended predicate on the same frozen feature population without relying on the suspect index. The important word is independent. A second query that silently uses the same index can repeat the same omission while appearing to confirm it.
In a controlled small fixture, enumerate every eligible source geometry and apply the exact relation using a compatible geometry engine. For a real large dataset, use a documented test method and inspect the execution path where necessary. Sampling can provide useful evidence but does not prove global completeness unless the design supports that claim.
Keep the dataset state fixed during comparison. If one path reads before an edit and another after it, a legitimate change can look like an index defect. Record source version or an appropriate consistent copy, query geometry, coordinate reference, predicate and any tolerances.
Compare ID sets, not only hit counts. An accelerated result of twelve and a reference result of twelve can still differ by one missing and one unexpected object. Inspect the symmetric difference and trace each discrepancy to geometry, query interpretation, source state or the derived index. A concise mismatch list is more informative than a reassuring equal total.
CHAPTER 10 OF 12
10. Repair only the derived component that is actually wrong
If evidence shows that source geometry is correct but the index is stale, a compatible rebuild of the derived index may be appropriate on an authorised copy. Preserve the original package and the failure evidence first. Otherwise a successful rebuild can erase the conditions needed to understand why the maintenance relationship broke.
Do not move source geometry merely to make it agree with stale index bounds. The search aid is derived from the geometry, not the authority that decides where a feature belongs. Conversely, rebuilding an index around wrong source coordinates does not make those coordinates correct. Diagnose which component is defective before choosing the repair.
After repair, repeat source–index reconciliation, the mutation fixture and independent spatial queries. A newly populated index can pass the current snapshot comparison while the same unsupported edit path remains capable of breaking it tomorrow. Restoration and recurrence prevention are related but separate jobs.
Avoid direct edits to SQLite RTree shadow tables. They are internal implementation structures, not ordinary feature attributes. Use supported maintenance tools and the relevant specification. This educational explanation authorises no change to an operational file; it describes the checks a qualified practitioner should use to justify and verify an approved repair.
CHAPTER 11 OF 12
11. Transfer the same reasoning to other derived search structures
Replace the GeoPackage index with a cached geographic extent used by a catalogue. A feature moves outside the old extent, but discovery still searches the stale summary. The mechanism is similar: a derived selector can exclude an object before a later exact stage sees it. The implementation and repair procedure, however, are different.
Now replace it with a text search index after an address correction. The source record can be present under its new address while the search index still reflects the old wording. The general lesson is correspondence between source and derived retrieval structures, not a claim that text indexes use spatial rectangles.
A useful transfer test asks you to identify the source of truth, the derived summary, the update path, the candidate-selection rule and an independent comparison. Then identify what does not transfer: GeoPackage trigger names, coordinate tolerances and RTree-specific internal checks have no automatic authority in the new system.
This is why the topic belongs to geoinformatics as well as database engineering. Reliable geographic information depends on the entire path from stored objects to returned answers. A correct source that users cannot retrieve under the intended query is not enough for a trustworthy information service.
CHAPTER 12 OF 12
12. Report separate claims about correctness and performance
A release note should distinguish an internally valid index, a source-consistent index and a tested query result. If you measured performance, state the workload and environment separately. Faster retrieval does not prove completeness, and a complete full scan does not establish that the accelerated path is healthy.
An example statement might say: on a fixed test copy, all eligible source IDs matched index IDs; envelopes satisfied the documented comparison; and twenty boundary-focused queries returned the same IDs as an independent exact evaluation. This is bounded evidence about that dataset and those tests, not a guarantee about every future edit.
Retain the original mismatch examples, source version, package version, software version, maintenance action and post-repair results. If the root cause was an unsupported write path, document the supported replacement and test it. Simply saying rebuilt successfully leaves the next operator without the knowledge needed to preserve the repaired state.
The practical endpoint is straightforward: an index should help users reach the intended geometry without changing which objects qualify. You should be able to explain why an envelope is only a candidate, how it stays aligned with its source, what each integrity check establishes and which evidence would reveal a missing object.
Independent worked case and evidence checks
Try an independent fictional fixture with four source points: A at (2,2), B at (12,12), C at (18,18) and D at (30,30). Query Q covers x = 10–20 and y = 10–20. Under the stated inclusive rectangle rule, the exact source result is {B,C}. There are two qualifying objects.
Suppose the index contains A correctly, B with stale bounds at (4,4), C correctly, and orphan E at (15,15), while D is missing from the index. Source and index each have four entries. The equal counts are misleading: B’s envelope is wrong, D is missing and E has no source object.
The index overlap candidates for Q are {C,E}. Joining those candidates to the source table removes orphan E, leaving {C}. Applying the exact predicate cannot recover B because B was excluded before that stage. The final result has one object rather than two, a 50% omission for this particular query.
Now query R around x = 25–35 and y = 25–35. The exact result is {D}, but the index lacks D entirely, so the candidate result is empty. The same test suite has exposed both a stale-envelope omission and a missing-entry omission. These are intentionally constructed failures, not measured prevalence in real GeoPackages.
For a repair check, rebuild the authorised test copy’s index from the current eligible source geometries using a compatible tool. The index IDs should become {A,B,C,D}, with appropriate bounds. Q should return {B,C}, and R should return {D}. Then move B again through the supported edit path and repeat the checks to test maintenance rather than only reconstruction.
For a changed-context challenge, replace C’s point with a valid ring-shaped polygon whose envelope overlaps Q but whose actual surface does not. It should survive the candidate filter and fail the exact test. That extra candidate is not the same failure as missing B. Explain the distinction before treating every candidate mismatch as evidence of corruption.
The final evidence record should preserve both expected and actual ID sets. Equal row counts, a clean file open and an internally valid tree can coexist with the failures in this fixture. Only checks aimed at source correspondence and query completeness expose the specific broken relationships.
Add a query that exactly touches B at its current position. State whether the intended final relation includes that boundary contact, then compare both paths under the same rule. If one path uses strict inequalities and the other uses inclusive contact, a discrepancy can arise even with a perfectly maintained index. Repairing the index would be the wrong response to an inconsistent query definition.
Next let the source table change between the two comparisons: B moves out of Q after the reference evaluation but before the accelerated query. The different result now has another plausible cause. Freeze the test state and repeat before diagnosing corruption. This is an experimental-design lesson as well as a database lesson: change one relevant condition at a time when trying to identify the failing mechanism.
Finally record candidate counts separately from final hit counts. A healthy index might admit three candidates and yield two exact hits; a less selective but still correct index might admit all four and yield the same two hits. The second query can be slower without being incomplete. A broken index that admits only C can be faster than both while omitting B. These three cases demonstrate why elapsed time cannot serve as the acceptance test for spatial correctness.
Worked comparison
| Check | What it establishes | What it does not establish |
|---|---|---|
| Index declaration | An intended extension is recorded | That entries are complete or current |
| Internal RTree check | Tree structures satisfy their tested invariants | Agreement with current source geometry |
| Source–index ID comparison | Eligible identities correspond | Correct bounds for each identity |
| Envelope comparison | Bounds correspond under a documented numeric rule | Exact intersection with every query |
| Independent query comparison | Agreement for the fixed state and tested queries | Automatic correctness after every future edit |
Questions and practical checks
Is an RTree rectangle the feature itself?
No. It is a bounding search representation. Nonrectangular candidates can still fail the final exact geometry test.
Can rtreecheck return ok while bounds are stale?
Yes. Its documented internal checks do not by themselves compare the tree with current geometries in the feature table.
Do equal source and index row counts prove completeness?
No. A missing source ID and an orphan index ID can cancel numerically. Compare the actual eligible ID sets and bounds.
Should every small bound difference be treated as damage?
No. SQLite RTree uses documented outward floating-point rounding. Distinguish that representation from substantive drift.
Does rebuilding solve the whole problem?
It can restore a derived index, but the update path must also be tested. A recurring maintenance failure can break the rebuilt structure again.
Continue with the connected subjects
Learn the structure and container
R-trees: how bounding rectangles accelerate spatial search
GeoPackage: preserving geometry, schema and spatial references during localisation
Check the geographic relationship
Spatial joins: what the final matching relationship means
Topology: validity, boundaries and geometric relationships
Sources and scope
OGC GeoPackage 1.4.0: published spatial-index extension and maintenance requirements
SQLite RTree: bounds, outward rounding, internal integrity and shadow tables
GDAL GeoPackage driver: supported spatial-index functions and validation
PostGIS: the separate exact intersection relationship
The numerical cases in this guide are original fictional teaching examples, not measurements from a real site. This is an educational explanation, not a field safety instruction, engineering approval or an official certification. For a real project, use qualified practitioners, current product documentation and site-specific evidence. Sources were checked on 30 October 2026.
Continue the representation checks
A raster mask governs which observations enter a calculation; tile identity governs which representations count as one object; a spatial join governs which relationships enter a total; and an index governs which candidates reach an exact test. Use the companion guide when your question crosses one of those boundaries.
Raster Validity Masks, Zero Measurements and Multiband Coverage
Vector-Tile Feature Counts, Clipped Fragments and Source Identity
Spatial-Join Multiplicity, Boundary Matches and Allocation Totals