Calculating distance based on latitude and longitude

I need to be able to take the latitude and logitude of two locations and compare then to determine the number of miles between each point. It doesn't need to account for elevation, but assumes a flat plane with lat and long.

Does anyone have any algorithms in T-SQL to do this?

if this were easy to do, esri/mapinfo wouldn't charge big bucks for their GIS libraries. People get PhD's in geodesia!

Now, there is a solution.

Search your registry for MapX. MapInfo's MapX ocx is distributed in a few different software packages. I thought it came in Microsoft Office. But I see it also comes in Crystal XI.

Discussion about the usage of this library is not appropriate for this forum and I would direct you to Mapinfo.com.

I would be more than happy to develop a wrapper for you if you are willing to pay. I charge US$150 an hour for GIS development. probably looking at 3 hours.

|||
The SQL Server 2005 Samples download contains the source
for the Spatial Data Library which I believe has
a C# function for calculating the distance between two
points from their lat/lons.
|||

it appears there is one in the SQL class. result is in Arc Minutes.

should be ok for small distances, and not near the equator. it doesn't take into account 'equatorial bulge'

in most applications it should be okay, just be aware of its propensity for error.

the algorithm doesnt look that hard to translate to straight T-SQL for increased speed:

CREATE FUNCTION CARTESIAN_PI() RETURNS DECIMAL(36, 35)
AS
BEGIN
RETURN 3.14159265358979323846264338327950288
END
GO
CREATE FUNCTION DBO.CARTESIAN_EPSILON() RETURNS DECIMAL(15,15)
AS
BEGIN
RETURN 0.000000000000001
END
GO
CREATE TYPE LATLONGUNIT FROM DECIMAL(26, 20);
GO
CREATE FUNCTION CARTESIAN_COS(@.arg LATLONGUNIT) RETURNS REAL
AS
BEGIN
DECLARE @.result REAL;
IF ABS(@.ARG-DBO.CARTESIAN_PI()) < DBO.CARTESIAN_EPSILON()
SET @.RESULT = 0
ELSE
SET @.RESULT = COS(@.arg)
RETURN @.result;
END
GO
CREATE FUNCTION DistanceLatLon(@.lat1 LATLONGUNIT, @.lon1 LATLONGUNIT, @.lat2 LATLONGUNIT, @.lon2 LATLONGUNIT) RETURNS REAL
AS
BEGIN
DECLARE @.d2r REAL
DECLARE @.nx1 REAL
DECLARE @.ny1 REAL
DECLARE @.nz1 REAL
DECLARE @.nx2 REAL
DECLARE @.ny2 REAL
DECLARE @.nz2 REAL

SET @.d2r = DBO.CARTESIAN_PI() / 180;
SET @.nx1 = DBO.CARTESIAN_COS(@.lat1 * @.d2r) * DBO.CARTESIAN_COS(@.lon1 * @.d2r);
SET @.ny1 = DBO.CARTESIAN_COS(@.lat1 * @.d2r) * SIN(@.lon1 * @.d2r);
SET @.nz1 = SIN(@.lat1 * @.d2r);
SET @.nx2 = DBO.CARTESIAN_COS(@.lat2 * @.d2r) * DBO.CARTESIAN_COS(@.lon2 * @.d2r);
SET @.ny2 = DBO.CARTESIAN_COS(@.lat2 * @.d2r) * SIN(@.lon2 * @.d2r);
SET @.nz2 = SIN(@.lat2 * @.d2r);
RETURN (60 * 2 * (1 / @.d2r) * ASIN(SQRT((@.nx1 - @.nx2) * (@.nx1 - @.nx2)
+ (@.ny1 - @.ny2) * (@.ny1 - @.ny2)
+ (@.nz1 - @.nz2) * (@.nz1 - @.nz2)
) / 2));
END

|||

Cool!

Might want to double check the precisions. Real number precision confuses the hell out of me. I never feel 100% comfortable that I have allocated enough for the application.

Also. . . if you are doing alot of this, you might want to investigate MapInfo or ESRI for some really slick GIS db extensions.