You are currently browsing the tag archive for the ‘Rumb-line’ tag.

On of the problems when working with spatial data is figuring out what to GROUP BY when aggregating data spatially. I’ll talk more about this in a future post and, for one of the approaches I tried, I needed the ability to draw an arbitrary geography polygon (square in my case) that is *yay* long and wide.

Obviously with the geography type, you can’t just add *n *units to the x/y axes as you need geographic co-ordinates (latitude and longitude). I needed a function were I could find out the co-ordinates of a point, say, 500 metres north of the current position.

I turned to Chris Veness’ excellent source of spatial scripts (http://www.movable-type.co.uk/scripts/latlong.html) and cribbed the rhumb line function, converting it into T-SQL.

The function takes start point, a bearing (in degrees) and a distance in metres – returning a new point.

Please note that this function is hard coded for WGS84 (srid 4326) and may need tweaking to get the earths radius out of sys.spatial_reference_systems or changed to suit your requirement.

Disclaimer: This code is provided as-is with no warranties and suits my purpose, but I can’t guarantee that it will work for you and the missile guidance system that you may be building.

Simon Munro

`CREATE FUNCTION [dbo].[RumbLine] `

(

@start GEOGRAPHY,

@bearing FLOAT,

@distance FLOAT

)

RETURNS GEOGRAPHY

AS

BEGIN

--Rumb line function created by Simon Munro

--Original post at Original post at

--https://simonmunro.com/2010/10/13/rumb-line-function-for-sql-server

--Algorithm thanks to

--http://www.movable-type.co.uk/scripts/latlong.html

--Hard coded for WGS84 (srid 4326)

DECLARE @result GEOGRAPHY;

DECLARE @R FLOAT = 6378137.0;

DECLARE @lat1 FLOAT, @lon1 FLOAT;

DECLARE @lat2 FLOAT, @lon2 FLOAT;

```
```SET @distance = @distance/@R;

SET @bearing = RADIANS(@bearing);

SET @lat1 = RADIANS(@start.Lat);

SET @lon1 = RADIANS(@start.Long);

SET @lat2 = ASIN(SIN(@lat1)*COS(@distance) + COS(@lat1)*SIN(@distance)*COS(@bearing));

SET @lon2 = @lon1 + ATN2(SIN(@bearing)*SIN(@distance)*COS(@lat1), COS(@distance)- SIN(@lat1)*SIN(@lat2));

SET @lon2 = CONVERT(DECIMAL(20,8), (@lon2+3*PI())) % CONVERT(DECIMAL(20,8),(2*PI())) - PI();

DECLARE @resultText VARCHAR(MAX);

SET @resultText = 'POINT('+CONVERT(VARCHAR(MAX),DEGREES(@lon2)) +' '+ CONVERT(VARCHAR(MAX), DEGREES(@lat2))+')';

SET @result = geography::STGeomFromText(@resultText, 4326)

`RETURN @result; `

END;