Reconstructing spatial indexes

[ckgoo...@gmail.com]

Hi.  I think my spatial indexes have gone wrong since a query that uses one returns nothing but the query with the spatial index removed works okay (but slowly of course).

I try:

SELECT DisableSpatialIndex( ‘spt_ways’, ‘ways1’ );

But get the message that either this index does not exist or the column is not spatial – although it has a geometry type of ‘LINESTRING’.

And trying:

SELECT CreateSpatialIndex( ‘spt_ways’, ‘ways1’ );

Gets a similar message.

I’m thinking I want to remove all my spatial indexes I’ve created and effectively start afresh.  Is this the right approach and how to do?

Best, Chris

[sandro furieri]

Il 2026-01-04 15:59 ckgoo...@gmail.com ha scritto:
> Hi. I think my spatial indexes have gone wrong since a query that
> uses one returns nothing but the query with the spatial index removed
> works okay (but slowly of course).
>

Hi Chris,

SpatiaLite's SpatialIndex is a delicate interlocking mechanism
entirely based on some strategic Triggers that guarantee its
correct functioning.

For everything to work properly, it's absolutely essential to
scrupulously follow the rules and absolutely avoid any shortcuts.
Above all, you must always use the dedicated SQL functions
designed to manage the SpatialIndex; any other approach will
result in a corrupted or malfunctioning SpatialIndex.

> I try:
>
> SELECT DisableSpatialIndex( 'spt_ways', 'ways1' );
>
> But get the message that either this index does not exist or the

> column is not spatial - although it has a geometry type of

> 'LINESTRING'.
>
> And trying:
>
> SELECT CreateSpatialIndex( 'spt_ways', 'ways1' );
>
> Gets a similar message.
>

This is a pretty clear indication that your DB is not following
the rules correctly.
Here are a couple of simple SQL queries that will allow you to
verify the actual situation:

SELECT * FROM geometry_columns
WHERE f_table_name = 'spt_ways' AND
f_geometry_column = 'ways1';

- if the line exists, OK.
- if instead you receive an empty resultset, it means that
your Table is not a genuine SpatialTable because it was
created incorrectly.

Please note: The fact that the Table contains a column
declared as LINESTRING is completely irrelevant.
The only thing that matters is that it's correctly
registered in "geometry_columns".


SELECT * FROM sqlite_master
WHERE name LIKE 'idx_spt_ways_ways1%';

- if it finds 4 Tables, OK
- otherwise it means that the SpatialIndex (R*Tree) is
not present or is damaged.


Finally here is a third useful query to check if all
the essential triggers are correctly defined:

SELECT * FROM sqlite_master
WHERE type = 'trigger' AND
tbl_name = 'spt_ways';

There should be at least 8 triggers defined.
Pay particular attention to the 3 that call the
RTreeAlign() function or reference the
"idx_spt_ways_ways1" table.
These are the ones that keep the SpatialIndex
properly synchronized.

If the situation you find is different, it's the
definitive confirmation that your DB is not a genuine
SpatiaLite DB because it was created incorrectly and
will never work properly.

> I'm thinking I want to remove all my spatial indexes I've created and
> effectively start afresh. Is this the right approach and how to do?
>

If you actually find confirmation that your DB is severely damaged,
starting from scratch is definitely the right thing to do.

1. create and initializa a brand new DB-file.

2. create your SpatialTable omitting at all the Geometry column,
something like:

CREATE TABLE spt_ways (
id INTEGER PRIMARY KEY,
... add any other non-Spatial column);

NOTE; explicitly defining a PRIMARY KEY of the INTEGER type
is absolutely crucial in order to get a good SpatialIndex.

3. now is the time to add any required Geometry:

SELECT AddGeometryColumn('spt_ways, "ways1', 4326,
'LINESTRING', 'XY');

NOTE: This way we are sure that the Geometry will be
correctly recorded in "geometry_columns" and that all
the safeguard Triggers will be created.

4. If you want, you can also define a SpatialIndex:

SELECT CreateSpatialIndex('spt_ways', 'ways1');

5. At this point we are sure that the DB has been
created correctly, and we can safely proceed to
populating the SpatialTable.
Do what's most convenient for you:
A) you can repeat the data import from scratch.
B) or you can simply copy the data already inserted
into the old DB.

Here are a few more SQL functions that might be useful
if you run into any future problems with SpatialIndex:

SELECT CheckSpatialIndex('spt_ways', 'ways1');

SELECT RecoverSpatialIndex('spt_ways', 'ways1');

The first checks whether the SpatialIndex is valid or
corrupted.
The second rebuilds the SpatialIndex, thus repairing
any damage.

-----------------------------------------

IMPORTANT: one final warning.
Never try to remove a SpatialTable using DROP TABLE,
as this is a surefire way to irreversibly damage your
database.

Instead, use the SQL function DropTable() which will
take care of completely removing all the definitions
created by SpatiaLite in the system tables, any
SpatialIndexes and, above all, all the triggers
installed by SpatiaLite.

bye Sandro