Bug report #16048

Spatial select doesn't work properly on filtered sqlite layers

Added by John Bryant over 7 years ago. Updated over 5 years ago.

Status:Closed
Priority:Low
Assignee:-
Category:Data Provider/SpatiaLite
Affected QGIS version:2.18.2 Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:end of life
Crashes QGIS or corrupts data:No Copied to github as #:23963

Description

When a sqlite layer is filtered, spatial select (eg. select by rectangle) selects the wrong features, ie. ones that are outside the selection area. See screenshots:

Select by rectangle:
select by rectangle

Incorrect results:
incorrect results

The number of features is correct, but they are in the wrong location. This problem doesn't seem to occur with other formats, eg. shapefile.

The attached QGIS project and data demonstrate this issue.

This issue makes sqlite a problematic format for usage in QGIS.

qgis_sqlite_issue_test.zip - qgis project and data demonstrating issue (413 KB) John Bryant, 2017-01-08 06:41 PM

select_area.jpg - select by rectangle (16.1 KB) John Bryant, 2017-01-08 06:41 PM

results.jpg - incorrect results (14.6 KB) John Bryant, 2017-01-08 06:41 PM

pts_sqlite.sqlite.zip (411 KB) Bernd Marcus, 2017-03-08 04:27 AM

qgis_sqlite_issue_test2.zip - updated test case (823 KB) John Bryant, 2017-03-08 02:48 PM

spatial_index_greyed_out.JPG (15.7 KB) John Bryant, 2017-03-08 03:02 PM

Related issues

Related to QGIS Application - Bug report #16330: "Save as" to Spatialite does not create the spatial index... Closed 2017-03-09

History

#1 Updated by Giovanni Manghi over 7 years ago

  • Category set to Data Provider/SpatiaLite

Confirmed also on 2.14 and 2.8.

#2 Updated by Giovanni Manghi over 7 years ago

  • Priority changed from High to Normal

#3 Updated by Bernd Marcus over 7 years ago

The wrong selection of features working with filtered and non-filtered spatialite tables usualy appear by using tables with no primary key.
BUT in the example file the table has such a key. Checking the Geometry and the CREATE statement show two critical issues:
1. the SRID is 0; this is never a good idea, cause there is no coordinate reference system defined (and no EPSG code); in such case QGIS will setup the reference system to the default CRS;
2. the main problem is the “Geometry” column, due to its declared type of ‘POINT’. This might be the right way in other databases but using sqlite you should follow its type rules (see [[http://www.sqlite.org/datatype3.html]]) and define the feature type later on by recovering the geometry.

Attached is the example file with an additional spatial table, where I used “blob” as the column type of the geom. Even with a filter on the table selections work corrrect now.

#4 Updated by Bernd Marcus over 7 years ago

couldn't upload the database file, one more try.

#5 Updated by Giovanni Manghi over 7 years ago

  • Subject changed from Spatial select doesn't work properly on filtered sqlite layers to Spatial select doesn't work properly on filtered sqlite layers when SRID=0
  • Priority changed from Normal to Low

Bernd Marcus wrote:

The wrong selection of features working with filtered and non-filtered spatialite tables usualy appear by using tables with no primary key.
BUT in the example file the table has such a key. Checking the Geometry and the CREATE statement show two critical issues:
1. the SRID is 0; this is never a good idea, cause there is no coordinate reference system defined (and no EPSG code); in such case QGIS will setup the reference system to the default CRS;
2. the main problem is the “Geometry” column, due to its declared type of ‘POINT’. This might be the right way in other databases but using sqlite you should follow its type rules (see [[http://www.sqlite.org/datatype3.html]]) and define the feature type later on by recovering the geometry.

Attached is the example file with an additional spatial table, where I used “blob” as the column type of the geom. Even with a filter on the table selections work corrrect now.

many thanks for this notes. It seems then that this issue is much less important than I supposed.

#6 Updated by Harrissou Santanna over 7 years ago

I do not totally get the subtlety between sqlite and spatiaLite formats (so bear with me, please) but if the original (and wrong) layer can be created from QGIS, then it can be seen as an important issue.
I mean, Does QGIS sqlite/spatiaLite creation mechanism ensure that noone (namely beginners) will create a layer with the critics raised by Bernd?
John didn't precise where the file comes from, though.

#7 Updated by Giovanni Manghi over 7 years ago

Harrissou Santanna wrote:

I do not totally get the subtlety between sqlite and spatiaLite formats (so bear with me, please) but if the original (and wrong) layer can be created from QGIS, then it can be seen as an important issue.
I mean, Does QGIS sqlite/spatiaLite creation mechanism ensure that noone (namely beginners) will create a layer with the critics raised by Bernd?
John didn't precise where the file comes from, though.

both when you create a SL db from the "save as" dialog or from the "new spatialite layer" one you cannot avoid choosing a CRS for the data.

#8 Updated by Bernd Marcus over 7 years ago

Dear Giovanni,
the new title of this ticket is not correct. You can work with spatialite tables with a SRID = 0 in QGIS, but you can run into deep trouble with those 'layers'.
The SRID = 0 is not the main reason for the wrong selected features.

In the database I've attached in the previous post, I havn't changed the SRID. But I've rebuilt the table and changed the type of the geometry column from "POINT" to "BLOB". Afterwards I assigned the 'POINT' geometry type to the geometry column with RecoverGeometryColumn(). And THIS solved the problem with the wrong selections.

@John Bryant:
How did you create the pts_sqlite table? Did you save the shape file as a spatialite table with QGIS's 'save as' dialog or with drag 'n' drop in the db-manager or manually with ogr2ogr or in a completely other fancy way? Would be interesting to know why you get an geometry type of 'Point' straight on.

#9 Updated by Giovanni Manghi over 7 years ago

  • Subject changed from Spatial select doesn't work properly on filtered sqlite layers when SRID=0 to Spatial select doesn't work properly on filtered sqlite layers

#10 Updated by John Bryant over 7 years ago

Interesting! Thanks a lot Bernd for looking into this. In this case I created the table using the QGIS 'Save as' dialog. But in my workflow I often use other tools, such as FME, to create Spatialite databases.

I've created an updated version of the test case with an explicitly named sqlite table 'saveas_spatialite' to illustrate. I can confirm the problem still occurs in exactly the same way with this new table.

So, if you're right that the issue is with the geometry type (POINT vs BLOB), then possibly the QGIS export function is the source of the issue?

Also, I'm a bit confused about the SRID=0 issue you raised - in QGIS I can see an SRID of 4326 for these tables. I'm not very familiar with the sqlite database structure so I don't know where to look for the SRID in a sqlite browser. In any case, again, the table was exported from QGIS so perhaps an issue with the 'Save as' function.

#11 Updated by Giovanni Manghi over 7 years ago

  • Status changed from Open to Feedback

John Bryant wrote:

Interesting! Thanks a lot Bernd for looking into this. In this case I created the table using the QGIS 'Save as' dialog. But in my workflow I often use other tools, such as FME, to create Spatialite databases.

I've created an updated version of the test case with an explicitly named sqlite table 'saveas_spatialite' to illustrate. I can confirm the problem still occurs in exactly the same way with this new table.

So, if you're right that the issue is with the geometry type (POINT vs BLOB), then possibly the QGIS export function is the source of the issue?

Also, I'm a bit confused about the SRID=0 issue you raised - in QGIS I can see an SRID of 4326 for these tables. I'm not very familiar with the sqlite database structure so I don't know where to look for the SRID in a sqlite browser. In any case, again, the table was exported from QGIS so perhaps an issue with the 'Save as' function.

after adding a spatial index on the SL layer the selection works as expected also when filtered.

#12 Updated by John Bryant over 7 years ago

But how does one add a spatial index in QGIS? That option is greyed out.

#13 Updated by John Bryant over 7 years ago

I checked the QGIS export dialog - there is a SPATIAL_INDEX Layer Option, which defaults to YES. But even confirming this is so, the exported table still exhibits the same problematic behaviour.

#14 Updated by Giovanni Manghi over 7 years ago

John Bryant wrote:

But how does one add a spatial index in QGIS? That option is greyed out.

spatial indexes for rdbms data must be created within the same datasource. In QGIS for example you can use DB Manager.

#15 Updated by Giovanni Manghi over 7 years ago

  • Status changed from Feedback to Closed
  • Resolution set to invalid

John Bryant wrote:

I checked the QGIS export dialog - there is a SPATIAL_INDEX Layer Option, which defaults to YES. But even confirming this is so, the exported table still exhibits the same problematic behaviour.

so I see two issues here:

  • using "save as" to Spatialite does not seems to create the spatial index even if the option is checked
  • With DB Manager the spatial index can be created, but apparently DB Manager has never had implemented a way to show you that the SI for SL is present (like it does for PostGIS layers).

I'm closing this and open two new ones.

#16 Updated by Bernd Marcus over 7 years ago

  • Status changed from Closed to Reopened

I did a small exercise und checked different options to import data into a sqlite database, to figure out when the described selection problem arises.
I used the pts_shp.shp from John as the base layer and did the following:

1. exporting the points to a new sqlite db with the "save as..." dialog in QGIS
2. drag 'n' drop pts_shp into an existing db using db-manager
3. drag 'n' drop pts_shp into an existing db using the browser windows
4. import the pts_shp into an existing db using Spatialite_GUI
5. using ogr2ogr to transfer the shp to a Spatialite table.

Task 2 didn't work (-> error dialog: invalid layer; could not load pts_shp).
In all other cases I used indexed and unindexed geometries. An spatial index is not part of the problem. Also not a strict type of BLOB for the geometry column, so I have to correct myself from yesterday. By importing a shp into a sqlite db with Spatialite_GUI the feature type (in our case POINT) is used for the geometry column type.

Apart from case 1 the other three created spatial tables had no selection issues when filtered.
The only remarkable difference between the 4 tables is the kind of how they are loaded into QGIS. When exporting a vector layer with the “save as...” dialog, QGIS will load the new created layer/table automatically if the option is not switched off. In such case we will get corrupted selections on filtered spatialite tables.
Loading the same table manually in QGIS using an existing database connection, everything works well.
Deactivate the automatic layer load option to avoid confusion when working with sqlite/spatialite tables (maybe other vector formats as well).

#17 Updated by Giovanni Manghi about 7 years ago

  • Easy fix? set to No
  • Regression? set to No

#18 Updated by Andy Harfoot almost 7 years ago

Just encountered this issue myself on 2.18.11 on Windows 7 x64

I agree with Marcus that the problem appears to manifest in the loading of the data

The working method I have found uses the following steps:

  1. Load the Spatalite / SQLite DB layer through DB Manager (Right click > Add to Canvas) OR Drag and drop from a Spatialite DB connection in Browser Window
  2. Apply the filter through the QGIS Layer properties

Applying the filter in the DB manager using SQL prior to loading the layer results in the bug regardless of whether the 'Avoid selecting by feature id' option is used.

Loading the DB layer by dragging and dropping the Spatialite file from Explorer or the Browser window, treating it as a file based datasource, results in the bug

The Layer source strings reported in the layer properties generated by the three methods are as follows:

  1. Database connection load, QGIS layer properties filter (no select bug)
    dbname='<DatabaseName>' table="<TableName>" (geometry) sql="natural" = 'spring'
  1. DB Manager query and load (select bug)
    dbname='<DatabaseName>' table="(SELECT * FROM <TableName> WHERE \"natural\" = 'spring')" (geometry) sql=
  1. QGIS File (OGR) based load, QGIS layer properties filter (select bug)
    <DatabaseName>|layerid=2|subset="natural" = 'spring'

#19 Updated by Giovanni Manghi over 5 years ago

  • Status changed from Reopened to Closed
  • Resolution changed from invalid to end of life

End of life notice: QGIS 2.18 LTR

Source:
http://blog.qgis.org/2019/03/09/end-of-life-notice-qgis-2-18-ltr/

QGIS 3.4 has recently become our new Long Term Release (LTR) version. This is a major step in our history – a long term release version based on the massive updates, library upgrades and improvements that we carried out in the course of the 2.x to 3x upgrade cycle.

We strongly encourage all users who are currently using QGIS 2.18 LTR as their preferred QGIS release to migrate to QGIS 3.4. This new LTR version will receive regular bugfixes for at least one year. It also includes hundreds of new functions, usability improvements, bugfixes, and other goodies. See the relevant changelogs for a good sampling of all the new features that have gone into version 3.4

Most plugins have been either migrated or incorporated into the core QGIS code base.

We strongly discourage the continued use of QGIS 2.18 LTR as it is now officially unsupported, which means we’ll not provide any bug fix releases for it.

You should also note that we intend to close all bug tickets referring to the now obsolete LTR version. Original reporters will receive a notification of the ticket closure and are encouraged to check whether the issue persists in the new LTR, in which case they should reopen the ticket.

If you would like to better understand the QGIS release roadmap, check out our roadmap page! It outlines the schedule for upcoming releases and will help you plan your deployment of QGIS into an operational environment.

The development of QGIS 3.4 LTR has been made possible by the work of hundreds of volunteers, by the investments of companies, professionals, and administrations, and by continuous donations and financial support from many of you. We sincerely thank you all and encourage you to collaborate and support the project even more, for the long term improvement and sustainability of the QGIS project.

Also available in: Atom PDF