Mike Ormond's Blog

Musings on mobile development and Windows Phone 7 in particular.

Getting Spatial Data into SQL Server 2008

Getting Spatial Data into SQL Server 2008

  • Comments 9

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.

image

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.

  • "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"

    WKT, WKB, GML, GIS, OGC and even TLA are *not* acronyms. Look up acroynm in a dictionary and you'll see what I mean. TLA ought to stand for Three Letter Abbreviation. SQL is sometimes an acronym, depends on how you pronounce it of course.

  • Yes, fair point. I don't have to look it up in a dictionary to know you're correct. Just a slip of the pen (brain). :-) Mike

  • Is it possible to get spatial data (map reference for virtual earth) into SQL 2005 Express? This is something I have been trying to figure out for a while?

    Cheers

    John

  • Well it's certainly possible to store Lat / Lon etc but there's no native spatial support in SQL Server 2005 so you're on your own. If you have a look at the demo page you'll see how you can capture Lat / Lon from Virtual Earth.

    You might want to take a look at the following sample http://msdn2.microsoft.com/en-us/library/ms345264.aspx and CodePlex project http://www.codeplex.com/MsSqlSpatial.

    Mike

  • A nice article by Mike Ormond explain SQL Server 2008 has native support for spatial datatypes. Read

  • Interesting news given my interest in Virtual Earth , Live Search Maps and Spatial Data . It'll be very

  • Per far un pò di demo ed esperimenti, questo bellissimo tool online di Mike Ormond è di grandissimo aiuto

  • I'm going to re-plug my Spatial Data Generator for SQL Server 2008 as I've had a lot of very positive

  • I'm going to re-plug my Spatial Data Generator for SQL Server 2008 as I've had a lot of very

Page 1 of 1 (9 items)