Direct access to Spatial Index from view not working as expected

[Argiris Grivas]

Hi all,

I've unsuccessfully tried to follow the example shown in section "Changes affecting the VirtualSpatialIndex interface" of the switching-to-4.0 document. In particular, I've set up a brand new database like this:

• CREATE TABLE groups (group_id INTEGER PRIMARY KEY AUTOINCREMENT, group_name TEXT NOT NULL);
  
• CREATE TABLE items (item_id INTEGER PRIMARY KEY AUTOINCREMENT, group_id INTEGER NOT NULL,item_name TEXT NOT NULL, CONSTRAINT fk_item FOREIGN KEY (group_id) REFERENCES groups (group_id));
  
• SELECT AddGeometryColumn('items', 'geom', 4326, 'POINT', 'XY');
  
• SELECT CreateSpatialIndex('items', 'geom');
  
• CREATE VIEW items_view AS SELECT i.ROWID AS rowid, i.item_id AS item_id, i.item_name AS item_name, i.group_id AS group_id, g.group_name AS group_name, i.geom AS geometry FROM items AS i JOIN groups AS g ON (g.group_id = i.group_id);
  
• INSERT INTO views_geometry_columns (view_name, view_geometry, view_rowid, f_table_name, f_geometry_column, read_only) VALUES ('items_view', 'geometry', 'rowid', 'items', 'geom', 1);
  
• INSERT INTO groups VALUES (1, 'test group');
  
• INSERT INTO items (item_id, group_id,item_name, geom) VALUES (1,1,'test item',GeomFromText('POINT(11.5 42.5)', 4326))
  

After setting it all up, I ran the following two queries on the above database:

1. SELECT * FROM items_view WHERE ROWID IN (SELECT ROWID FROM SpatialIndex WHERE f_table_name = 'items' AND f_geometry_column = 'geom' AND search_frame = BuildMbr(11, 42, 12, 43, 4326) );
2. SELECT * FROM items_view WHERE rowid IN (SELECT ROWID FROM SpatialIndex WHERE f_table_name = 'items_view' AND f_geometry_column = 'geometry' AND search_frame = BuildMbr(11, 42, 12, 43, 4326) );

Although query #1 returns the row as expected, query #2 returns an empty result set. The documentation suggests #2 should give the same result as #1. Am I missing something?

Many thanks in advance.
Argiris

PS:The experiment was conducted using Spatialite_gui 2.0.0-devel (SpatiaLite version 4.3.0a).

[a.fu...@lqt.it]

On Fri, 1 Apr 2016 08:54:50 -0700 (PDT), Argiris Grivas wrote:
> Although query #1 returns the row as expected, query #2 returns an
> empty result set. The documentation suggests #2 should give the same
> result as #1. Am I missing something?
>

Hi Argiris,

you are absolutely right; there was an undetected regression
forbidding the Spatial Index to correctly support Spatial Views.

short history: version 4.2 introduced several safety checks
intended to shield the Spatial Index against table declaring
a WITHOUT ROWID clause, as reported here:
https://www.gaia-gis.it/fossil/libspatialite/wiki?name=4.2.0+functions#5

Unluckily the code implementation badly ignored the special case
of Spatial Views, and as a sad consequence all Spatial Views were
systematically discarded by the Spatial Index module.

an appropriate patch is already available since the most recent commit
into the Fossil repository and will be publicly released with the
next-to-come 4.4.0

bye Sandro

[Argiris Grivas]

Hi Sandro,

That's absolutely fine. I know the workaround already. Many thanks for the quick fix and all your hard work on this great piece of software.

Regards,

Argiris