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:

<pre>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]')
</pre>

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 send this query to MSSQL:

<pre>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]
</pre>

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

I've found the this article "How do I calulate an optimal bounding box for a SQL Server spatial index?":https://community.hexagongeospatial.com/t5/GeoMedia-Q-A/How-do-I-calulate-an-optimal-bounding-box-for-a-SQL-Server/ta-p/3346 from

[[https://community.hexagongeospatial.com/t5/GeoMedia-Q-A/How-do-I-calulate-an-optimal-bounding-box-for-a-SQL-Server/ta-p/3346]]

and this is the right query that MSSQL must execute to return
the correct bbox:

<pre>
WITH &lt;pre&gt;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;
</pre>