Posted by: Karlo Bartels | 2008/10/08

SQL/CLR Exercise

Creating a geography UDT in SQL server 2005

GPS devices return location data as coordinates consisting of latitude and longitude components. It would be nice if we could use a single table column to store latitude and longitude data instead of creating two separate columns. The new spatial support in SQL Server 2008 offers such possibilities (and many more!) in the form of geometry and geography data types, allowing you to Deliver Location Intelligence with Spatial Data.

Unfortunately, my ISP does not host SQL Server 2008 yet so I decided to build my own geography data type in SQL Server 2005 by using the SQL/CLR integration feature. Building my own UDT allows me to:

  • Add a column to a table of type geography.
  • Store POINT strings and latitude/longitude pairs as binary geography types.
  • Calculate distance between coordinates in kilometers or miles.
  • Retrieve coordinates in either decimal degrees format or in degrees, minutes and seconds format.
  • Extract any coordinate’s latitude and/or longitude components separately.

Let’s get started by downloading the code for this exercise here.

Next, enable the ability to execute CLR code in SQL Server. Open a new query window in SQL Server Management Studio and execute the following script:

sp_configure 'clr enabled', 1
RECONFIGURE

Create a new SQL Server 2005 database for our test, open a new query window and enter the following script:


--- Select proper context
USE [YourDatabaseNameHere]
GO
-- Create test table
IF OBJECT_ID('Test') IS NULL
BEGIN
   CREATE TABLE
[dbo].[Test](
  
[id] [int] IDENTITY(1,1) NOT NULL,
  
[Coordinate] [dbo].[geography] NULL,
      
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
      
(
         
[id] ASC
      
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  
) ON [PRIMARY]

END
 

 

This generates a test table with an identity column called id and a Coordinate column of type geography. Next, we’ll create two variables of type geography and fill them with some data:

-- Create geography object from POINT string
DECLARE @EiffelTower geography
SET @EiffelTower = geography::STGeomFromText('POINT(48.85836229464929 2.294468879699723)'); -- separate coordinates with a single space

-- OR create geography object from separate coordinates
DECLARE @ArcDeTriomph geography
DECLARE @Lat FLOAT
DECLARE
@Long FLOAT
SET
@Lat = 48.87378327586873
SET @Long = 2.295005321502694
SET @ArcDeTriomph = geography::STGeomFromLatLong(@Lat, @Long);

 

You may notice the strange looking method names STGeomFromText and STGeomFromLatLong. I copied this from the official Open Geospatial Consortium (OGC) naming conventions of geography methods. To find out which OGC geography methods are supported in SQL Server 2008, click here.

The parameter of the STGeomFromText method is passed in Well-Know Text (WKT) representation. There are many WKT input strings, for example LINE, POLYGON, and POINT. I only needed a single coordinate to be stored, so I chose the POINT format.

The next step is to simply INSERT the objects into our test table:

INSERT INTO Test (Coordinate) VALUES (@EiffelTower);
INSERT INTO Test (Coordinate) VALUES (@ArcDeTriomph);

Now we can display the results:

SELECT
       
-- Internal binary representation
       
Coordinate                                      
       
-- Human readable format
      
,Coordinate.STAsText() AS CoordinateText      
      
-- Display latitude part of type only
      
,Coordinate.STLatitude() AS Latitude          
      
-- Display latitude part of type only in DMS format
      
,Coordinate.STLatitudeDMS() AS LatitudeDMS        
      
-- Display longitude part of type only
      
,Coordinate.STLongitude() AS Longitude    
      
-- Display longitude part of type only in DMS format
      
,Coordinate.STLongitudeDMS() AS LongitudeDMS  
FROM Test;

This generates the following output:

SQLCLREx1

To calculate the distance between the Eiffel Tower and the Arc De Triomph, try one of the following:

-- Calculate distance between 2 coordinates directly
SELECT geography::STDistance(@EiffelTower, @ArcDeTriomph, 0) AS DistanceInKilometers, geography::STDistance(@EiffelTower, @ArcDeTriomph, 1) AS DistanceInMiles;
-- Calculate distance between 2 coordinates from table
SELECT geography::STDistance(T.Coordinate, T1.Coordinate, 0) AS DistanceInKilometers
FROM   Test T JOIN Test T1
ON     T.id = T1.id + 1;

 

Which results in the following:

SQLCLREx2

The entire example can be found in the Test Scripts folder of the sample code.

For more information on using CLR code to create User Defined Types, follow this link.

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: