OGR2OGR Importing Spatial Data to SQL Server

A few months back, I posted an article explaining how to import spatial data into SQL Server 2008 from any format supported by the OGR library (including ESRI shapefiles, GML, and TIGER data), using OGR2OGR. That article was written using OGR2OGR from v1.7 of the GDAL 1.7 library, which doesn’t support SQL Server 2008 directly, so I instead used OGR2OGR to create a CSV file containing spatial data in Well-Known Text format and then parsed that data in SQL Server using the STGeomFromText() method.

The good news is that things have become a bit easier since then, and version 1.8 of the GDAL library now has a MSSQLSpatial driver that can interface directly with geometry and geography data in SQL Server 2008.

The bad news is that most of the places that offer pre-compiled GDAL binaries for Windows have yet to update to the new version. FWTools, for example, still comes packaged only with v1.7. Likewise, the osgeo download site at http://download.osgeo.org/gdal/win32/ also lists GDAL versions up to v1.7.

So, if you want to get hold of the latest GDAL to import directly into SQL Server you’ll have to build it yourself from source, which can be downloaded from http://download.osgeo.org/gdal/gdal180.zip

Fortunately, the source has been very considerately packaged, and includes solution files that will build GDAL out-of-the-box in VS2005, 2008, and 2010. Simply load the .sln file, click build, and wait a few minutes:

Building GDAL 1.8 in VS2010

Then, if you look in the output directory (warmerdabldbin, by default) you should see a lovely collection of utilities for working with spatial data – GDAL (for working with raster data), and OGR (for its vector sibling).

Here’s the output of calling ogr2ogr –formats, which retrieves the list of supported vector spatial formats – note the MSSQLSpatial format supported for both read/write:

image

Example usage to load a shapefile to SQL Server as follows:

[php]
ogr2ogr -overwrite -f MSSQLSpatial "MSSQL:server=.\MSSQLSERVER2008;database=spatial;trusted_connection=yes" "TG20.shp"
[/php]

 

Text images without (much) coding

Posted by Mano Marks, Google Geo APIs Team

Valery Hronusov has created a cool visual interface for his Text to Image service. This service allows you to easily create a text image, control its color, add shadow effects, and then place it on directly a map. It outputs to KML, Maps API JavaScript code, or just a plain URL to an image. This is really useful to creating, for instance, road labels or text based ScreenOverlays. All you have to do is copy the output into your own code. The interface is pretty simple:

Here’s a sample of the KML output:


  ...
     
             http://text2image.ning.com/TextToImage.php?text=San+Francisco%2C+here+we+come%21&LabelType=2&FontSize=24&casing=asis&alignment=1&font=arial&fontStyle=bold&LabelLocation=topleft&dX=11&dY=0&FontColor=00ff00f9&ShadowColor=00000000&depth=3&effect=0&LabelHeight=35&LabelWidth=438&IconHeight=18&IconName=http://localhost/wordpress/wp-content/uploads/2010/11/1da66f57feed_dot.png.png

Here’s a sample of the JavaScript code it creates:

var pointCenter = new GLatLng(37.775196,-122.419204);
var map = new GMap2(document.getElementById("map"));
map.setCenter(pointCenter, 10);

var pointSW = new GLatLng(37.741743,-122.470044);
var pointNE = new GLatLng(37.786659,-122.272387);

var groundOverlay = new GGroundOverlay(
   "http://text2image.ning.com/TextToImage.php?text=San+Francisco%2C+here+we+come%21&LabelType=2&FontSize=24&casing=asis&alignment=1&font=arial&fontStyle=bold&LabelLocation=topleft&dX=11&dY=0&FontColor=00ff00f9&ShadowColor=00000000&depth=3&effect=0&LabelHeight=35&LabelWidth=438&IconHeight=18&IconName=http://localhost/wordpress/wp-content/uploads/2010/11/1da66f57feed_dot.png.png", 
   new GLatLngBounds(pointSW, pointNE));

map.addOverlay(groundOverlay);

And here’s what it looks like: