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)
AS
BEGIN 
   RETURN
CONVERT(varchar(MAX), CAST(@In AS decimal(38, 13))); 
END

 

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
AS
BEGIN 
   RETURN
geography::STPointFromText('POINT(' +
         
dbo.ConvertFloatToVarChar(@Longitude) + ' ' +
         
dbo.ConvertFloatToVarChar(@Latitude) + ')', @SRID)
END

 

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
SELECT
   
Name,
   
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

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

Categories

%d bloggers like this: