SpatialIndex table broken after copying databases

Question

I have several map layers in a SpatiaLite database in memory. All of these have been copied from multiple separate on-disk databases. I want to do a query that involves updating the columns in a layer with points (point_layer) with entries from the columns of the same name in other layers where the points intersect with those layers. In my example this other layer is polygon_layer which has also been copied to the in-memory database.

I have one working query:

QUERY 1

UPDATE point_layer SET field_name = 
   (SELECT field_name FROM polygon_layer 
      WHERE INTERSECTS(polygon_layer.GEOMETRY, point_layer.GEOMETRY))

Which shows me that the geometry works correctly, but it is quite slow and so I would like to speed it up using spatialindex, for which I think the query should be:

QUERY 2

UPDATE point_layer SET field_name = 
   (SELECT lyr.field_name FROM polygon_layer AS lyr 
      WHERE (lyr.ROWID IN
         (SELECT ROWID FROM SpatialIndex 
             WHERE(f_table_name = polygon_layer AND search_frame = point_layer.GEOMETRY)))
      AND
         (INTERSECTS(lyr.GEOMETRY, point_layer.GEOMETRY)))

But while it does not throw up any errors, it does not fill the columns either. This leads me to suspect that there is something wrong with my spatial index. I copied the SpatialIndex tables, as well as the other SpatiaLite reference tables, such as geometry_columns etc, from the original file using create table:

CREATE TABLE "SpatialIndex" AS SELECT * FROM point_copy.SpatialIndex
CREATE TABLE "geometry_columns" AS SELECT * FROM point_copy.SpatialIndex

where point_copy is the attached on-disk spatialite database that contains point_layer. Then I attach the database containing the polygon layer and copy the main table, and INSERT INTO tables such as geometry_columns.

If I do only this, QUERY 2 throws an error at me, namely that it cannot find the SpatialIndex table. So I create new spatial indices:

SELECT CreateSpatialIndex(point_layer, GEOMETRY)
SELECT CreateSpatialIndex(polygon_layer, GEOMETRY)

Which complete without error. QUERY 2 now produces no errors, but also doesn't fill the columns. I have also tried emptying the SpatialIndex table (DELETE FROM SpatialIndex) before creating the new spatial indices, which produces the same results. Should I be copying or creating the spatial indices in some other way, or does QUERY 2 have some kind of problem?

This is using SpatiaLite 4.3.0 through the spatialite plugin for Python3.7. I've excluded the Python bits for convenience, which consists only of cursor executes and connection commits.

Answer 1

I have found a solution. As user30184 mentioned the SpatialIndex is a virtual table, and using CREATE TABLE [..] AS SELECT * FROM[...] therefore does not work.

The correct way to create a new spatial index table is:

CREATE VIRTUAL TABLE 'SpatialIndex' USING VirtualSpatialIndex

In which VirtualSpatialIndex is the module from Spatialite that builds the spatial index.

Which can then be populated using:

SELECT CreateSpatialIndex('layer_name', geometry_column)

This is functionally the same as copying the SpatialIndex table. The documentation on building a virtual table (in SQLite) can be found here.

Answer 2

The SpatiaLite document https://www.gaia-gis.it/fossil/libspatialite/wiki?name=SpatialIndex has a section about how to use spatial index with an attached database. Your trial looks very odd and would not work with any SpatiaLite database but maybe it is just because some copy-paste errors when you have edited your question.

(SELECT ROWID FROM SpatialIndex WHERE(f_table_name = lyr.field_name...

f_table_name naturally refers to table name and with a normal database you would use

f_table_name='polygon_layer'

but with attached database you must use a prefix

f_table_name='DB=a.polygon_layer'

However, it is not clear for me if your "polygon_layer" is the attached database "a.polygon_layer" or if you have also made a copy of that with something like create table polygon_layer as select * from a.polygon_layer