Posted by: Karlo Bartels | 2009/03/18

Migrating spatial data

Converting latitude/longitude to geography

I have a lot of spatial data stored in a SQL Server 2005 database. Now that SQL Server 2008 is around, I wanted to migrate that data to a new database, which would come down to converting the ‘old’ latitude/longitude fields to the new geography data type.

After doing some research it turned out I had to:

  • convert my latitude/longitude floats to varchars,
  • construct a POINT string and
  • call the STPointFromText method of the geography type to get what I wanted.

This is quit a hassle if you already have a database full of spatial data!

To make things easy, I created two UDFs that take care of this. The first one converts a float to a varchar, while preserving its precision:

CREATE FUNCTION [dbo].[ConvertFloatToVarChar]
@In float
RETURNS varchar(MAX)
CONVERT(varchar(MAX), CAST(@In AS decimal(38, 13))); 


The second one wraps the aforementioned UDF into a new function that constructs the POINT string and calls the STPointFromText method:


CREATE FUNCTION [dbo].[ConvertLatLonToGeography]
@Latitude float,
@Longitude float,
@SRID int = 4326
RETURNS geography
geography::STPointFromText('POINT(' +
dbo.ConvertFloatToVarChar(@Longitude) + ' ' +
dbo.ConvertFloatToVarChar(@Latitude) + ')', @SRID)


This function accepts a spatial reference identifier (SRID) that specifies the reference system you want to use. I used the default one which maps to the WGS 84 spatial reference system.


Data migration

Migrating ‘old’ data (in latitude/longitude form) from a table called ‘Locations’ to another table ‘LocationsNew’ which contains a geography column can now be realised as follows:


INSERT INTO LocationsNew
dbo.ConvertLatLonToGeography(Latitude, Longitude, 4326)
FROM Locations

This inserts the location name and geographic location into the new table.

Download the UDTs and the sample script here


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: