QGIS » QGIS Application

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;