Parsing MSDN Geometry Data Type

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

I want to access this database from Python and was wandering if anyone knew the format of the Geometry Data Type, or any libraries capable of parsing it out into a set of Geo Coordinates in Python.

The link states that Microsoft used the "Open Geospatial Consortium (OGC) standard" in designing this data type, does this mean the spatial coordinates are defined by this standard?

Does any one else have any experience with this?

Any help would be much appreciated!


Asked by: Adelaide498 | Posted: 06-12-2021






Answer 1

As figured out in the comments below (thanks MarkJ!):

  • geometry is a .NET datatype but uses its own custom serialization format; you could select the column whole and then reimplement this by opening Microsoft.SqlServer.Types.dll in Reflector and starting from
  • or you can use the SQL server support for the type to read out the properties of the geometry data from the database, e.g. select geocolumn.STX, geocolumn.STY from myTable;
  • or you can export the whole value as GML with e.g. select geocolumn.AsGml() from myTable; which can be processed by Python geometry libraries such as http://gispython.org/ http://mapnik.org/ http://www.qgis.org/wiki/Python_Bindings

I had originally thought SQL Server stored CLR data types as serialized .NET objects directly in the table but this turned out to be wrong.

Answered by: Jared684 | Posted: 07-01-2022



Answer 2

"…if anyone knew the format of the Geometry [data type]…"

The binary serialization format for SQL Server's spatial GEOMETRY and GEOGRAPHY types is specified here:

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

"Specifies the binary format of the GEOGRAPHY, GEOMETRY, HIERARCHYID, and CLR user-defined type (UDT) structures that are managed by SQL Server."

This specification is well written and the binary format is easy to understand, so it shouldn't be much of a problem implementing a basic parser for the binary format yourself.

"…or any libraries capable of parsing it out into 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 fairly simple), but you can find a way to interact with a .NET assembly from Python — perhaps via pythonnet? —, then the following hints may be of interest:

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

If you're only interested in de-serializing SQL Server spatial data, and you're careful not to invoke any spatial functions on SqlGeometry or SqlGeography, then you might be able to use Microsoft.SqlServer.Types to de-serialize spatial binary data for you, then inspecting it with an implementation of IGeometrySink110 that you have to provide to e.g. the SqlGeometry.Populate method.

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

Well-Known Text (WKT) and Well-Known Binary (WKB):

One more option would be to let SQL Server translate spatial values to Well-Known Text (WKT) or Well-Known Binary (WKB) using SELECT geometryColumn.STAsText() or SELECT geometryColumn.STAsBinary(), then look for a Python library that can parse these standard interchange formats.

(One word of caution: If you go down that route, just be careful if your data contains circular arcs. There are different versions of the WKT and WKB data format. They were first specified as part of the Simple Features Access specification of the Open Geospatial Consortium; that version doesn't understand about circular arcs. Support for circular curve segments was added in the SQL/MM Part 3: Spatial standard, which SQL Server implements.)

Answered by: Marcus472 | Posted: 07-01-2022



Similar questions

python - How do I overlap widgets with the Tkinter pack geometry manager?

I want to put a Canvas with an image in my window, and then I want to pack widgets on top of it, so the Canvas acts as a background. Is it possible to have two states for the pack manager: one for one set of widgets and another for another set?


Good geometry library in python?

Closed. This question does not meet Stack Overflow guid...


python - Postgis - How do i check the geometry type before i do an insert

i have a postgres database with millions of rows in it it has a column called geom which contains the boundary of a property. using a python script i am extracting the information from this table and re-inserting it into a new table. when i insert in the new table the script bugs out with the following: Traceback (most recent call last): File "build_parcels.py", line 258, in <module>...


python - How do I use Django to insert a Geometry Field into the database?

class LocationLog(models.Model): user = models.ForeignKey(User) utm = models.GeometryField(spatial_index=True) This is my database model. I would like to insert a row. I want to insert a circle at point -55, 333. With a radius of 10. How can I put this circle into the geometry field? Of course, then I would want to check which circles overlap a given circle. (my select stat...


Help with Windows Geometry in Python

Why are the commands to change the window position before and after sleep(3.00) being ignored? if self.selectedM.get() == 'Bump': W1 = GetSystemMetrics(1) + 200 print W1 w1.wm_geometry("+100+" + str(W1)) w2.wm_geometry("+100+" + str(W1)) w3.wm_geometry("+100+" + str(W1)) w4.wm_geometry("+100+" + str(W1)) self.rvar.set(0) self.rvar2.set(0) ...


python - GUI layout using Tk Grid Geometry Manager

Building a small application for personal use with Python and thought I'd try my hand with a little GUI programming using Tkinter. This is the GUI I've created so far: Application doubts: How can I make sure that the three LableFrames - A, B and C in the screenshot - have the same width? (Or rather, have ...


geometry - Integral of a triangle function in python

I have just defined a function which is the integral of a given step function, and now I would like to integrate the triangle function obtained. jerk:1,-1,1-1 The step function correspond of a given list of successive jerk. For getting the triangle function, I have used the formula: a(t)=kt+ac with k the Jerk and ac a con...


python - Is there a GUI design app for the Tkinter / grid geometry?

Closed. This question does not meet Stack Overflow guid...


geometry - Python - Pygame Drawing Angle

I need to draw an angle(in Pygame), given it's 1. Measure of the angle (θ) 2. Endpoints of the base(A & B) Here I know 1. Measure of θ (in radians and degrees) 2. (x,y) of A and B 3. Measure of BC My Question How do I calculate the position of the Co-ordinates(...


python - Convert Point Geometry to list

I have the following script that creates a point geometry. How can I convert this point geometry to a list only containing the coordiantes to look like [258432.79138201929, 1001957.4394514663]? >>> import ogr >>> driver = ogr.GetDriverByName('ESRI Shapefile') >>> pointshp = driver.Open('U:/My Documents/Tool/shp/point.shp', 0) >>> pointlyr = pointshp.GetLayer...






Still can't find your answer? Check out these communities...



PySlackers | Full Stack Python | NHS Python | Pythonist Cafe | Hacker Earth | Discord Python



top