Bug report #4051

Cannot display SpatiaLite views based on tables with spatial index

Added by Anita Graser almost 9 years ago. Updated over 4 years ago.

Status:Closed
Priority:Normal
Assignee:-
Category:Data Provider/SpatiaLite
Affected QGIS version:master Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:fixed/implemented
Crashes QGIS or corrupts data:No Copied to github as #:14037

Description

If I add a spatial index to the parent table of my view in SpatiaLite, the features are not rendered in QGIS anymore. When I disable the index, it renders again.

Tested on Ubuntu Natty.

History

#1 Updated by Tim Sutton over 8 years ago

  • Target version changed from Version 1.7.1 to Version 1.7.2

#2 Updated by Giovanni Manghi over 8 years ago

  • Target version changed from Version 1.7.2 to Version 1.7.3

#3 Updated by Giovanni Manghi over 8 years ago

  • Target version changed from Version 1.7.3 to Version 1.7.4

#4 Updated by Paolo Cavallini almost 8 years ago

  • Target version changed from Version 1.7.4 to Version 1.8.0
  • Affected QGIS version set to master
  • Crashes QGIS or corrupts data set to No

#5 Updated by Paolo Cavallini over 7 years ago

  • Target version changed from Version 1.8.0 to Version 2.0.0

#6 Updated by Stefano Costa - about 6 years ago

  • Target version changed from Version 2.0.0 to Future Release - Lower Priority

Similar issue still found in QGIS 2.2?

#7 Updated by Giovanni Manghi about 6 years ago

  • Status changed from Open to Feedback
  • Pull Request or Patch supplied set to No

#8 Updated by Anita Graser about 6 years ago

To me, it seems like the problem persists. If I create a new Spatialite table with QGIS new layer, it automatically adds indexes. If I then create a view based on this table, the features won't be rendered in the DB Manager preview (it's not enabled) or in the QGIS main window. (I can add the layer but it remains empty).

Querying features using e.g. SELECT astext(geometry) FROM my_view; works fine as expected.

#9 Updated by pvanbosgeo - almost 6 years ago

I am having the same problem in QGIS master. Spatial view are not shown if the parent table(s) have a spatial index. Removing the index and the spatial view shows in QGIS.

#10 Updated by Anita Graser almost 6 years ago

  • Category changed from Data Provider to Data Provider/SpatiaLite

Can someone change this back to "open" please?

#11 Updated by Giovanni Manghi almost 6 years ago

  • Status changed from Feedback to Open

#12 Updated by Stefano Maran over 5 years ago

I am using QGIS 2.4.0. If I use the info tool, I can see the attributes; if I use the DB Manager, I can see the attribute table; but if I use the "open attribute table" button, I see only columns filled with ERROR string and, of course, I cannot use the table tools.

#13 Updated by Médéric RIBREUX over 4 years ago

  • Status changed from Open to Feedback

Hello, bug triage...

I've made some tests with QGIS 2.13. I've used the Spatialite provider to create a new database and a new layer (which also configures a spatial index on it).
I've then used DBManager to create a view on this table based on a select query and "create view" button.
DBManager (Spatialite actually) complains that the view name needs to be lower case. Whenever I set a lower case name for a view, it is correctly inserted into the 'geometry_columns' table/view and the view can be correctly displayed in QGIS canvas and in DBManager preview tab.

So it seems that this bug is fixed, at least in QGIS 2.13 master. QGIS displays all the errors to informs the user about what to do to have a correct spatial view in Spatialite.

Do you still have problems on your side ?

#14 Updated by Anita Graser over 4 years ago

  • Status changed from Feedback to Closed
  • Resolution set to fixed/implemented

Thanks for checking this. I can confirm that your describe workflow for creating a view from a table with index on it works. Closing this issue.

Also available in: Atom PDF