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

@simonmunro

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
--http://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;

More posts from me

I do most of my short format blogging on CloudComments.net. So head over there for more current blog posts on cloud computing

RSS Posts on CloudComments.net

  • Free eBook on Designing Cloud Applications
    Too often we see cloud project fail, not because of the platforms or lack of enthusiasm, but from a general lack of skills on cloud computing principles and architectures. At the beginning of last year I looked at how to address this problem and realised that some guidance was needed on what is different with […]
  • AWS and high performance commodity
    One of the primary influencers on cloud application architectures is the lack of high performance infrastructure — particularly infrastructure that satisfies the I/O demands of databases. Databases running on public cloud infrastructure have never had access to the custom-build high I/O infrastructure of their on-premise counterparts. This had led to the wel […]
  • Fingers should be pointed at AWS
    The recent outage suffered at Amazon Web Services due to the failure of something-or-other caused by storms in Virginia has created yet another round of discussions about availability in the public cloud. Update: The report from AWS on the cause and ramifications of the outage is here. While there has been some of the usual […]
  • Microsoft can do it without partners
    Microsoft’s biggest strength has always its partner network and it seemed, at least for a couple of decades, that a strong channel was needed to get your product into the market. Few remember the days where buyers only saw products in computer magazines, computer trade shows and the salespeople walking through the door — the […]
  • The significance of Linux VMs on Windows Azure
    One of the most significant, highly anticipated, and worst kept secrets of the Windows Azure spring release is the inclusion of persistent VMs, with the notable addition of support for Linux on those VMs. The significance of the feature is not that high architecturally — after all, Windows Azure applications that were specifically architected for […]

@simonmunro

Follow

Get every new post delivered to your Inbox.