Calculating distance between 2 Easting & Northing values (UK)
-
Yo!
I have a db of location easting and northing values (from the Royal Mail PAF db) and I want to create a UDF which will allow me to pass 2 sets of values over and calculate the distance between the 2 places in miles. I did have this working for long and lat values using the "Great Circle" calculation method but need to use these Grid Refs now.
Sample Values being stored are: 417789 (easting) and 305045 (northing) which is a small town called "Hopwas" in Staforshire, UK.
Any help will be greatly appreciated.
-
Just want to confirm: Are you using SQL Server 2008? (since posted in the 2K8 forum)
If so: did you look into geospatial data type?
-
With a simple google search i found this (very good) description.
http://en.wikipedia.org/wiki/Grid_reference
Would seem that simple Pythagoras need only be applied
-
Dave Ballantyne (2/18/2010)
With a simple google search i found this (very good) description.http://en.wikipedia.org/wiki/Grid_reference
Would seem that simple Pythagoras need only be applied
... and based on your interesting link I used "Grid reference latitude longitude convert" as search keywords on google again and it returned a number of articles including samples to convert the grid refernce to lat/long in order to be able to do perform a "Great Circle" calculation again. Or use the geospatial data type as mentioned before...
The Pythagoras will loose precision the longer the distance of the two points will get (increasig influence of the spherical shape)
-
lmu92 (2/18/2010)
The Pythagoras will loose precision the longer the distance of the two points will get (increasig influence of the spherical shape)True , but i dont think that the easting northings notation itself was designed for that ,ie calculating long distances.
Looking at the link , it it used to split an area in 1Km*1km squares, and ,over a km, that will have minimal effect. Im someone much better at maths than I could give a proper formula.
Here's something interesting on the subject
-
lmu92 (2/18/2010)
The Pythagoras will loose precision the longer the distance of the two points will get (increasig influence of the spherical shape)Not if it's a true "Grid" reference like the UK grid or the Telcordia VnH grid... both are based on a (IIRC) Donald Elliptical grid which removes problems with curature of the earth. In fact, the two grid systems were designed to allow "The Pythagoras" to just as accurately for short and long distances.
An example of the Telcordia VnH grid system follows...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi guys,
Cheers for all the responses. Top stuff!
I've managed to put together the following function if it helps anyone:
ALTER FUNCTION [dbo].[fn_radialDistance] (@east1 float,
@north1 float,
@east2 float,
@north2 float
) returns float as
begin
/*
Distance = ((e1-e2)^2 + (n1-n2)^2)^0.5
Distance / 1609 (= miles)
*/
declare @d float;
set @d = power(square(@east1-@east2)+square(@north1-@north2),0.5E)/1609.0E
return @d
end
I only need 'as the crow flys' distance so this works a treat. Also, I'm only dealing with distances between 2 places in the UK so it's not *HUGE* distances. As far as I can tell, this should be accurate enough. If not, you may see me back here in this thread! 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply