You are currently browsing the tag archive for the ‘Spatial Data’ 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
--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;

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

  • An error has occurred; the feed is probably down. Try again later.

@simonmunro