MSDN geometry data type analysis

Question

I have a database where one field gives spatial coordinates. I found out that the field is a serialized geometry of the MSDN Data Type ( http://msdn.microsoft.com/en-us/library/bb933973.aspx ).

I want to access this database with Python and wandered if someone knew the Geometry Data Type format or any libraries that could parse it into a set of geo-coordinates in Python.

The link indicates that Microsoft used the Open Geospatial Consortium (OGC) standard when developing this data type, does this mean that the spatial coordinates are defined by this standard?

Does anyone else have experience?

Any help would be greatly appreciated!

Answer 1

As explained in the comments below (thanks MarkJ!):

• geometry is a .NET data type, but uses its own serialization format; you can select the whole column and then override it by opening the Microsoft.SqlServer.Types.dll file in Reflector and starting with
• or you can use SQL server support so that the type reads the properties of geometric data from the database, for example. select geocolumn.STX, geocolumn.STY from myTable;
• or you can export the entire value as GML , for example. select geocolumn.AsGml() from myTable; that can be handled by Python geometry libraries such as http://gispython.org/ http://mapnik.org/ http://www.qgis.org/wiki/Python_Bindings

I initially thought that SQL Server stores CLR data types as serialized .NET objects directly in a table, but this turned out to be wrong.

Answer 2

"... if anyone knew the geometry format [data type] ..."

This is the binary serialization format for the SQL Server GEOMETRY and GEOGRAPHY spatial types:

[MS-SSCLRT]: CLR Types for Microsoft SQL Server Serialization Formats

"Defines the binary format for custom-type structures of GEOGRAPHY, GEOMETRY, HIERARCHYID, and CLR managed by SQL Server.

This specification is well written and the binary format is easy to understand, so it should not be a big problem to implement a basic analyzer for the binary format.

"... or any libraries that can parse it in a set of [geo-coordinates] in Python ..."

Using Microsoft.SqlServer.Types via .NET interop to deserialize these types:

If you don't want to implement your own de-serializer (which should be pretty simple), but you can find a way to interact with the .NET assembly from Python - perhaps through pythonnet ? -, then the following tips may be interesting:

The two T-SQL GEOMETRY and GEOGRAPHY implemented as a combination of the .NET assembly ( Microsoft.SqlServer.Types ), which performs de / serialization from / to the binary format mentioned above, and an unmanaged DLL ( SqlServerSpatial….dll ), which contains almost everything else (i.e. routines for spatial operations).

If you are only interested in de-serializing SQL Server spatial data, and you try not to call any spatial functions on SqlGeometry or SqlGeography , then you can use Microsoft.SqlServer.Types to -serialize spatial binary data for you, then check it using the implementation of IGeometrySink110 that you must provide, e.g. SqlGeometry.Populate .

Microsoft.SqlServer.Types and SqlServerSpatial….dll are available either as a .NET project package NuGet , or as a system-wide installation package MSI ( SQLSysClrTypes.msi ) . AFAIK DLLs are also automatically installed using SQL Server.

Well-known text (WKT) and well-known binary (WKB):

Another option is to let SQL Server translate spatial values ​​into well-known text (WKT) or Well-Known Binary (WKB) using SELECT geometryColumn.STAsText() or SELECT geometryColumn.STAsBinary() , and then look for a Python library that can parse these formats exchange standards.

(One word of caution: if you go down this route, be careful if your data contains circular arcs. There are different versions of WKT and WKB. They were first listed as part of the Simple Features Access specification of the open geospatial consortium; this version does not know about circular arcs Support for circular curve segments has been added in SQL / MM Part 3: The spatial standard that SQL Server implements.)