Parsing MSDN Geometry Data Type

views:

answers:

+1 Q:

Parsing MSDN Geometry Data Type

Hi,

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!

+2 A:

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.

Rup 2010-08-11 16:45:27

I understand - thank you. I will have a play, but why on earth would the guys at Microsoft think it was a good idea to serialise an object into a Database table? It breaks the rule of separation!

Nick Cartwright 2010-08-12 07:23:18

@nickcartwright @Rup The contents are not a serialized .NET object but a representation of geometry. This is a very good thing because it means you can do spatial queries in SQL, for instance "list all customers within 10 miles of this office" or "join customers to offices so that each customer is paired with their nearest office" [More here](http://www.microsoft.com/sqlserver/2008/en/us/spatial-data.aspx) I don't know anything about the Python geometry support or SQL server support, but I'd have hoped the Python SQL server connection would translate SQL Server geometries into Python ones.

MarkJ 2010-08-12 07:36:53

@MarkJ OK, I'm not 100% sure how it's stored but it is a .NET object http://msdn.microsoft.com/en-us/library/cc280487.aspx "The planar spatial data type, geometry, is implemented as a common language runtime (CLR) data type in SQL Server." If you select the column whole in management studio you get a binary blob. I was going to try selecting back a column as-is then feeding it into BinarySerializer but the SqlClient class automatically returns you a SqlGeometry object instead so I can't conclude anything from that.

Rup 2010-08-12 08:35:57

OK, I'm wrong - it is binary serialised but not as a whole object. Open Microsoft.SqlServer.Types.dll in Reflector and look at SqlGeometry's Read() method. You might do better working from the GML format which might already have support in your classes `select geocolumn.AsGml() from myTable` else it would be possible to replicate the SqlGeometry parse logic from Reflector.

Rup 2010-08-12 08:54:19

OK, I was wrong too, it is a .Net object! I suppose my point is that the underlying representation isn't really relevant. "The guys at Microsoft" have provided a higher level abstraction for dealing with geometry in SQL Server, which is an excellent idea. [GML](http://en.wikipedia.org/wiki/Geography_Markup_Language) is a widely used XML standard for exchanging spatial geometry data. So GML ought to be a good option. I would hope that there are GML libraries for Python. Google is suggesting these http://gispython.org/ http://mapnik.org/ http://www.qgis.org/wiki/Python_Bindings

MarkJ 2010-08-12 11:22:17

Thanks @Rup and @MarkJ for your great help here. Much much much appreciated!!!

Nick Cartwright 2010-08-20 10:47:56

The real key I think in all this is the .AsGml() command!

Nick Cartwright 2010-08-23 07:18:37

ansaurus

tags:

views:

answers:

Parsing MSDN Geometry Data Type

related questions