Getting Spatial Data into SQL Server 2008

If you've attended one of our recent SQL Server 2008 events then you'll know that SQL Server 2008 has native support for spatial datatypes (as it happens it has two types, geometry to represent planar or flat-earth data such as Ordnance Survey grid references or building plans and geography to represent round-earth or geodetic data such as Lat/Lon data).

One of the things I show is integration with Virtual Earth (or Live Search Maps) - BTW if you haven't installed my Live Search Maps Sidebar Gadget, what are you waiting for? [If you think it sucks, drop me a mail and tell me why or what I could do to improve it and maybe I will - you'll see what I mean if you follow the link. I use it a lot as a fast way to find place and find driving times].

Anyway, back to integration with Virtual Earth. As part of my demo I show a web page written by Johannes Kebeck which allows you to do various things including generating T-SQL INSERT statements to add spatial features to your DB by drawing on a Virtual Earth Map. In fact this is how I created the raw data of Thames Valley Park for the rest of the demo.

SQL Server 2008 supports the Well Known Text (WKT) and Well Known Binary (WKB) formats as well as a subset of Geography Markup Language (GML) all defined by the Open Geospatial Consortium (OGC). One thing I have learned is that the world of Geographic Information Systems (GIS) has more TLAs (three letter acronyms) than any other branch of science...

A few people have asked if I can release the demo code. Well I could have approached Johannes and asked if he'd publish it but having told people how straightforward it was, I thought it might be more honest if I were to write a similar page. So I did.

The page allows you to create Points, Lines and Polygons by drawing on a Virtual Earth map. Although it's an aspx page, there's actually no managed code behind, I simply wanted to take advantage of the AJAX script library. All the work is done in JavaScript.


To use it, simply select whether you want a Point, Line or Polygon then mark the feature on the map (of course you can pan and zoom the map to your hearts content before you start). Use the left mouse button to mark points and the right mouse button to enter end-points (for Lines and Polygons). When you've entered a feature you need to give it a name. You can then carry on entering more features or generate SQL. Before you generate SQL, customise the column names (the INSERT statement only populates two columns - one for the name of the feature and the other for its geography) and the table name and then hit "Generate SQL". You'll get a block of T-SQL that will populate your features in SQL Server 2008.

Technorati Tags: sql server,sql server 2008,spatial,virtual earth,live search maps