1

I have point data within an MS SQL Server.

I can add it as a layer to QGIS by creating a DB connection under MS SQL Server and double clicking the table listed.

I also have a polygon layer again added via same MS SQL Server connection.

If I try and do Select by Location (or Select Within plugin) - asking for all points that are within the polygons - the result is that all points are selected not just the ones within the polygons:

![enter image description here

But if I add the points layer as a Virtual layer (using same connection) the Select by Location works - only points within polygons selected: ![enter image description here

Why? Surely it should work with the direct SQL connection method? What is the difference between virtual and direct SQL layer?

Improve this question
2
  • 1
    Maybe the CRS is properly set in the virtual layer but not in the original layer?
    – JGH
    Commented Nov 30, 2023 at 13:36
  • Yes, I have had that before but no the cause in this case.
    – James Bourne
    Commented Nov 30, 2023 at 14:13

1 Answer 1

Reset to default
0

I realised that other linked tables were ok with select by location.

Checked the underlying query in the DB and that was the problem - didn't have a proper unique ID attached.

Changed the query to include unique [id] as first field: (SELECT ROW_NUMBER() OVER (ORDER BY [URN]) AS [id]

That made the difference.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.