Bug report #14590

MSSQL fails to render layer with invalid features

Added by Jeremy Palmer over 4 years ago. Updated over 3 years ago.

Status:Closed
Priority:Normal
Assignee:Nathan Woodrow
Category:Data Provider/MSSQL
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 #:22558

Description

When loading a layer into QGIS which contains invalid features QGIS fails to completely render the layer and stops on any invalid feature without any error message to the user. This leaves the user confused as to what has occurred.

The reason for this is the MSSQL spatial filtering process using ST_Intersects which will throw an exception if the feature is not valid. e.g

SELECT [id],[ogr_geometry]FROM [dbo].[invalid_parcels] where [ogr_geometry].STIntersects([geometry]::STGeomFromText('POLYGON)',4326)) = 1

might throw this error:

Msg 6522, Level 16, State 1, Line 5
A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry":
System.ArgumentException: 24144: This operation cannot be completed because the instance is not valid. Use MakeValid to convert the instance to a valid instance. Note that MakeValid may cause the points of a geometry instance to shift slightly.
System.ArgumentException:
at Microsoft.SqlServer.Types.SqlGeometry.ThrowIfInvalid()
at Microsoft.SqlServer.Types.SqlGeometry.STIntersects(SqlGeometry other)

A couple options here:

1/ At least raise an error with the user that the layer contains invalid features and to fix them.
2/ Use FIlter instead or STIntersect for the spatial filtering function as Filter can operate on invalid geometries. This might also make sense due to performance reasons. See #10947.

I vote for 2!

I have attached a screenshot showing what happens in a typical situation in the map window. I have also attached a SQL dump of a test layer which contains invalid geometries.

invalid_parcels.zip (350 KB) Jeremy Palmer, 2016-03-30 02:14 AM

qgis_mssql_invalid_features.png (645 KB) Jeremy Palmer, 2016-03-30 02:14 AM

History

#1 Updated by Nathan Woodrow over 4 years ago

  • Assignee set to Nathan Woodrow
  • Priority changed from Normal to High

Hmm bugger. Yeah using Filter is the way to go. I will fix this next week if you are not in a rush. Will back port it as much as I can.

#2 Updated by Jeremy Palmer over 4 years ago

No rush. Thanks.

#3 Updated by Giovanni Manghi over 4 years ago

  • OS version deleted (ALL)
  • Operating System deleted (ALL)
  • Target version deleted (Version 2.16)
  • Priority changed from High to Normal

#4 Updated by Jeremy Palmer over 4 years ago

Nathan any update on this?

#5 Updated by Nathan Woodrow over 4 years ago

I have fixed this is in master using a IsValid() check however I think I will try the Filter method before release.

#6 Updated by Giovanni Manghi over 3 years ago

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

#7 Updated by Nathan Woodrow over 3 years ago

  • Status changed from Open to Closed
  • Description updated (diff)
  • Resolution set to fixed/implemented

Also available in: Atom PDF