Feature request #21630

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

Added by Snorre Aakerø about 5 years ago. Updated about 5 years ago.

Category:Data Provider/MSSQL
Pull Request or Patch supplied:No Resolution:
Easy fix?:No Copied to github as #:29446


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).


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:


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...

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


#1 Updated by Giovanni Manghi about 5 years ago

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

Also available in: Atom PDF