Feature request #21630

QGIS does not support the current version (version 2) of the SQL Server Spatial binary format

Added by Snorre Aakerø over 1 year ago. Updated over 1 year ago.

Status:Open
Priority:Normal
Assignee:-
Category:Data Provider/MSSQL
Pull Request or Patch supplied:No Resolution:
Easy fix?:No Copied to github as #:29446

Description

After editing the geometry of a feature from a SQL server Spatial connection in SoftWare supporting SQL Server Spatial binary format version 2 like GeoMedia , it will not display in QGIS.
This issue occurs because QGIS does not support the current version of the SQL Server Spatial binary format. Specifically QGIS appears to support only version 1 of the format (which was introduced with SQL Server 2008). However this format was updated to version 2 at the time SQL Server 2012 was released, so any geometry written to SQL Server Spatial by the current GeoMedia SQL Server Spatial data server is written using version 2 of the format. This means that if you have a SQL Server Spatial database that has the old (version 1) format geometry present in it and you edit a feature as described in the ticket (or insert a new feature) the features will not be visible in QGIS because QGIS does not recognize the geometry that is written using version 2 of the format.

To verify that the format type is the reason QGIS fails to display the feature instance I used Microsoft SQL Server Management Studio to edit the value stored in the native Geometry column to change the character representing the format version from 2 to 1. After making the change the feature instance was then visible in QGIS. For instance in my example the value for the native Geometry column started with the characters shown below (although the complete value is much longer).

0x0000000002050E00000012E761A....

The character that represents the version is the first 2 (actually 02), so for the edit I changed that value to 1, so that the first part of value became:

0x0000000001050E00000012E761A....

As noted presviously, after making this edit the feature is visible in QGIS.

The Microsoft site with the documentation for the CLR Types is located at...
https://docs.microsoft.com/en-us/openspecs/sql_server_protocols/ms-ssclrt/77460aa9-8c2f-4449-a65e-1d649ebd77fa

There is a PDF download link for the latest version (i.e. Protocal Revision 6.0 dated 8/16/2017) and that '[MS-SSCLRT].pdf' file includes a description of the CLR format. As documented in the PDF the first 4 bytes of the string represent SRID and the next byte represents "Version (1 byte): The version of the GEOGRAPHY structure". Additionally the footnote for this entry states "In Microsoft implementations, a value set to 1 denotes version 1 of the structure and a value set to 2 denotes version 2 of the structure." The PDF also describes the differences in version 1 and 2 of the format. Here is the direct download link to the PDF file... https://sqlprotocoldoc.blob.core.windows.net/productionsqlarchives/MS-SSCLRT/%5bMS-SSCLRT%5d.pdf

Version 2 of the format has been in place for over six years now, but I've tested with the latest QGIS 3.6.0 release and it appears that they have still not implemented support for that version of the format.
--------------

Regards Snorre

History

#1 Updated by Giovanni Manghi over 1 year ago

  • Assignee deleted (Snorre Aakerø)
  • Tracker changed from Bug report to Feature request

Also available in: Atom PDF