Reprojecting features using OGR

SQL Server supports spatial data defined in a variety of different spatial reference systems - using both projected coordinates (the geometry datatype) and geographic coordinates (the geography datatype). However, it doesn't include the ability to transform, or reproject, data between difference spatial reference systems - to do this, you must use external tools.

One such tool is OGR2OGR, which is a command-line tool provided as part of the FWTools package. For example, suppose that you had downloaded a shapefile that was defined using EPSG:27700 - the SRID used by the British National Grid. This is a projected coordinate system so you could import this data into a geometry column, but in order to import it into the geography datatype you need to (un)project that data into geographic coordinates from one of the supported spatial reference systems listed in the sys.spatial_reference_systems table, such as EPSG:4326 (WGS84). To convert a shapefile from projected coordinates based on EPSG:27700 to geographic coordinates using EPSG:4326, you can use ogr2ogr as follows:

ogr2ogr -s_srs EPSG:27700 -t_srs EPSG:4326 outfile.shp infile.shp

You can then import the resulting shapefile into a geography column (SRID 4326) using a shape loading tool, such as Shape2Sql.

If you simply want to transform a single point coordinate from one coordinate system to another, you can use the cs2cs tool instead, as shown in the following example which transforms the coordinate (140,-40) from the NAD27 datum to WGS84:

echo 140 -40 | cs2cs -f "%.10f" +proj=latlong +datum=NAD27 +to +proj=latlong +datum=WGS84 -w8