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.
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.