[QGIS-Developer] QGIS and opening selected feature attributes

[Paul Wittle]

Hi,

We had a problem a while back with QGIS issuing really big statements to our spatial database and I finally got round to pin pointing the issues.

If you have a really big dataset (in our case addresses) and you select say 5000 addresses and then open the attribute table with the option ‘Show Selected Features’ set the SQL statement issued to the database seems to be:

WHERE Primary_Key = :a OR Primary_Key =:bb etc etc

I don’t know if it only relates to Oracle or whether it is already fixed but is there a limit to the number of where clauses QGIS requests in a single statement because we hit limits in the size of the permitted SQL?
(I’m not sure if that was technically the number of where statements or just the number of characters in the SQL but it’s probably not relevant)

We would have expected the application to detect that it is asking for x rows by primary key and potentially issued the request as multiple statements in blocks of y. I have of course explained to our users that they should take care regarding the way they work as they have some responsibly but I also wanted to check what we can do in the settings to protect the spatial databases as well?

Another good example is when using the ‘Join Attributes by Location’ function. In here the application clearly has the concept because if you hit the settings button there is a way to limit the number of features; this is great! The trouble is that regular users in our experience are unlikely to find or use the setting and more likely to just hit go.

Would it be possible to set the limit value on a layer by layer basis?

This would mean that you could set max features to query in single statement in the layer properties and if set it would automatically fill in the limit on any function where the setting is available (such as the join by location mentioned above). I’m happy for that to be possible to override but it would be great if we could setup layers with the added protection of knowing that users need to remove the limitation to run a massive process rather than worrying that they may fail to set the option which would prevent them doing so by accident.

Perhaps it could also add a message in the info box on the tool when set automatically to draw attention to it so that users are not confused by the results?

Final point, we have configured the database to throw out dodgy commands before they crash the database as I know some will say we should just configure the database but sometimes there is a good reason to run a bigger process. We are hoping to simply improve the user experience in QGIS by allowing big processes when needed but preventing accidental large processes as these give the impression to less thoughtful users that the application is slow rather than highlighting that the request they just issued was actually inefficient.

I look forward to hearing thoughts on the subject from others and I hope I’m not re-asking an FAQ.

Thanks,

Paul Wittle
[cid:image002.jpg at 01D72706.0E97B7B0]<https://www.dorsetcouncil.gov.uk/>
Business Solutions Analyst (GIS)
ICT Operations
Dorset Council
01305 228473 <tel:01305%20228473%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20>
dorsetcouncil.gov.uk<https://www.dorsetcouncil.gov.uk>

[cid:image004.png at 01D72706.0E97B7B0]<https://www.facebook.com/DorsetCouncilUK>
[cid:image006.png at 01D72706.0E97B7B0]<https://instagram.com/DorsetCouncilUK>
[cid:image008.png at 01D72706.0E97B7B0]<https://twitter.com/DorsetCouncilUK>

This e-mail and any files transmitted with it are intended solely for the use of the individual or entity to whom they are addressed. It may contain unclassified but sensitive or protectively marked material and should be handled accordingly. Unless you are the named addressee (or authorised to receive it for the addressee) you may not copy or use it, or disclose it to anyone else. If you have received this transmission in error please notify the sender immediately. All traffic may be subject to recording and/or monitoring in accordance with relevant legislation. Any views expressed in this message are those of the individual sender, except where the sender specifies and with authority, states them to be the views of Dorset Council. Dorset Council does not accept service of documents by fax or other electronic means. Virus checking: Whilst all reasonable steps have been taken to ensure that this electronic communication and its attachments whether encoded, encrypted or otherwise supplied are free from computer viruses, Dorset Council accepts no liability in respect of any loss, cost, damage or expense suffered as a result of accessing this message or any of its attachments. For information on how Dorset Council processes your information, please see www.dorsetcouncil.gov.uk/data-protection
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20210401/6440ad77/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image002.jpg
Type: image/jpeg
Size: 5737 bytes
Desc: image002.jpg
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20210401/6440ad77/attachment-0001.jpg>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image004.png
Type: image/png
Size: 3145 bytes
Desc: image004.png
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20210401/6440ad77/attachment-0003.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image006.png
Type: image/png
Size: 6148 bytes
Desc: image006.png
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20210401/6440ad77/attachment-0004.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image008.png
Type: image/png
Size: 2535 bytes
Desc: image008.png
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20210401/6440ad77/attachment-0005.png>