Bug report #21556

Bbox processing error with MSSQL

Added by Andy Gio over 5 years ago. Updated over 5 years ago.

Status:Closed
Priority:Normal
Assignee:-
Category:Data Provider/MSSQL
Affected QGIS version:3.6.0 Regression?:No
Operating System: Easy fix?:No
Pull Request or Patch supplied:No Resolution:wontfix
Crashes QGIS or corrupts data:No Copied to github as #:29372

Description

QGIS 3.6.0-Noosa
MSSQL 2017

Opening a geometry table from MSSQL, Qgis takes the bounding box directly from table's Spatial Index:

SELECT 
    min(bounding_box_xmin), min(bounding_box_ymin),
    max(bounding_box_xmax), max(bounding_box_ymax)
FROM 
    sys.spatial_index_tessellations 
WHERE 
    object_id = OBJECT_ID('[dbo].[GeoTable]')

This is OK.

Opening a geometry view from MSSQL, Qgis can't get the bounding box from a Spatial Index because there is not any Spatial Index defined for a view. So Qgis sends this query to MSSQL:

SELECT 
    MIN(CASE WHEN ([SP_GEOMETRY].STIsValid() = 1) 
         THEN [SP_GEOMETRY].STPointN(1).STX 
         ELSE NULL END), 
    MIN(CASE WHEN ([SP_GEOMETRY].STIsValid() = 1) 
         THEN [SP_GEOMETRY].STPointN(1).STY 
         ELSE NULL END), 
    MAX(CASE WHEN ([SP_GEOMETRY].STIsValid() = 1) 
         THEN [SP_GEOMETRY].STPointN(1).STX 
         ELSE NULL END), 
    MAX(CASE WHEN ([SP_GEOMETRY].STIsValid() = 1) 
         THEN [SP_GEOMETRY].STPointN(1).STY 
         ELSE NULL END) 
FROM [dbo].[GeoView]

This query don't return the right bbox, because it analyze the first point on a feature; and the first point
of a feature is not necessarly the min or max.

I've found the article How do I calulate an optimal bounding box for a SQL Server spatial index? and this is the right query that MSSQL must execute to return the correct bbox:

WITH
  ENVELOPE as  ( SELECT SP_GEOMETRY.STEnvelope() as envelope from [dbo].[GeoView] ),
  CORNERS  as  ( SELECT envelope.STPointN(1) as point from ENVELOPE  UNION ALL select envelope.STPointN(3) from ENVELOPE  )
SELECT
        MIN(point.STX) as MinX,
        MIN(point.STY) as MinY,
        MAX(point.STX) as MaxX,
        MAX(point.STY) as MaxY
   FROM  CORNERS;

History

#1 Updated by Andy Gio over 5 years ago

Can anyone tell me something about this?

#2 Updated by Jürgen Fischer over 5 years ago

  • Description updated (diff)

#3 Updated by Jürgen Fischer over 5 years ago

  • Resolution set to wontfix
  • Status changed from Open to Closed

The query you quoted is used "use estimated table parameters" option is enabled in the connection ui - otherwise the envelope is queried (see source:src/providers/mssql/qgsmssqlprovider.cpp#L710)

#4 Updated by Andy Gio over 5 years ago

Thanks for your reply. You're right.
Therefore I think that also when 'use estimated table parameters' is checked,
(lines 715 and 717 of [[https://issues.qgis.org/projects/qgis/repository/entry/src/providers/mssql/qgsmssqlprovider.cpp]])
QGIS must have to use the correct query as in line 732 and 734,
because the actual result is incorrect.
Please give me a feedback.

#5 Updated by Jürgen Fischer over 5 years ago

Andy Gio wrote:

Thanks for your reply. You're right.
Therefore I think that also when 'use estimated table parameters' is checked,
(lines 715 and 717 of [[https://issues.qgis.org/projects/qgis/repository/entry/src/providers/mssql/qgsmssqlprovider.cpp]])
QGIS must have to use the correct query as in line 732 and 734,
because the actual result is incorrect.
Please give me a feedback.

That's by design - setting the checkbox trades accurate results for access speed (see tip Use estimated table metadata to speed up operations)

Also available in: Atom PDF