How do we calculate the distance in miles ? Syntax help please ?

Question

Post reply

How do we calculate the distance in miles ? Syntax help please ?

mw_sql_developer

SSCoach

Points: 19445
More actions
October 15, 2015 at 7:53 am

#312326

DECLARE @Latitude NUMERIC(9, 6), @Longitude NUMERIC(9, 6)
DECLARE @MyLatitude NUMERIC(9, 6), @MyLongitude NUMERIC(9, 6)
Set @Latitude = 42.329596;
Set @Longitude = -83.709286;
Set @MyLatitude = 42.430883;
Set @MyLongitude = -82.923642;
Question: How do we calculate the distance in miles between the 2 points.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

g.britton SSChampion Points: 13863 More actions · Answer 1

Calculate distance, bearing and more between Latitude/Longitude points

Gerald Britton, Pluralsight courses

Lowell SSC Guru Points: 323518 More actions · Answer 2

i have this saved in my snippets; does ~7 miles sound correct for what you posted?

Miles
6.99824265061246

DECLARE

@Latitude NUMERIC(9, 6),

@Longitude NUMERIC(9, 6),

@MyLatitude NUMERIC(9, 6),

@MyLongitude NUMERIC(9, 6)

Set @Latitude = 42.329596;

Set @Longitude = -83.709286;

Set @MyLatitude = 42.430883;

Set @MyLongitude = -82.923642;

Select

Avg(3958.75 * ACos(Sin(@Latitude/57.2958) *

Sin(@MyLatitude/57.2958) +

Cos(@Latitude/57.2958) *

Cos(@MyLatitude/57.2958) *

Cos(@MyLongitude/57.2958 - @MyLongitude/57.2958))) As Miles

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mw_sql_developer SSCoach Points: 19445 More actions · Answer 3

Thanks

But I found another way.... The answers that I am getting are different when I do your way vs this way. I think I will go with the clode below. But thanks

DECLARE @RangeDivisor FLOAT

SET @RangeDivisor = 1609.344;

DECLARE @MyLatitude NUMERIC(9, 6), @MyLongitude NUMERIC(9, 6)

Set @MyLatitude = 42.430883;

Set @MyLongitude = -82.923642;

create table #r ( LATx FLOAT, LONGTx FLOAT, GEO GEOGRAPHY );

INSERT INTO #r ( LATx, LONGTx ) VALUES ( 42.329596, -83.709286 );

UPDATE #r

Set GEO = GEOGRAPHY::STGeomFromText('Point(' + CAST([LATX] AS VARCHAR(32)) + ' ' + CAST(LONGTx AS VARCHAR(32)) + ')',4326);

Select CAST(GEO.STDistance(geography::Point(@MyLatitude, @MyLongitude, 4326))/@RangeDivisor AS DECIMAL(18,1)) [Distance]

FROM

#r;

mw_sql_developer SSCoach Points: 19445 More actions · Answer 4

Slight error in that previous code section...... This is correct

DECLARE @RangeDivisor FLOAT

SET @RangeDivisor = 1609.344;

DECLARE @MyLatitude NUMERIC(9, 6), @MyLongitude NUMERIC(9, 6)

Set @MyLatitude = 42.430883;

Set @MyLongitude = -82.923642;

create table #r ( LATx FLOAT, LONGTx FLOAT, GEO GEOGRAPHY );

INSERT INTO #r ( LATx, LONGTx ) VALUES ( 42.329596, -83.709286 );

UPDATE #r

Set GEO = GEOGRAPHY::STGeomFromText('Point(' + CAST(LONGTx AS VARCHAR(32)) + ' ' + CAST(LATx AS VARCHAR(32)) + ')',4326);

Select CAST(GEO.STDistance(geography::Point(@MyLatitude, @MyLongitude, 4326))/@RangeDivisor AS DECIMAL(18,1)) [Distance]

FROM

#r;

--NOTE: WHEN USING THE STGeomFromText you must past the LONGT as the first parameter.... That was the error

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 5

mw112009 (10/15/2015)
Slight error in that previous code section...... This is correct
DECLARE @RangeDivisor FLOAT
SET @RangeDivisor = 1609.344;
DECLARE @MyLatitude NUMERIC(9, 6), @MyLongitude NUMERIC(9, 6)
Set @MyLatitude = 42.430883;
Set @MyLongitude = -82.923642;
create table #r ( LATx FLOAT, LONGTx FLOAT, GEO GEOGRAPHY );
INSERT INTO #r ( LATx, LONGTx ) VALUES ( 42.329596, -83.709286 );
UPDATE #r
Set GEO = GEOGRAPHY::STGeomFromText('Point(' + CAST(LONGTx AS VARCHAR(32)) + ' ' + CAST(LATx AS VARCHAR(32)) + ')',4326);
Select CAST(GEO.STDistance(geography::Point(@MyLatitude, @MyLongitude, 4326))/@RangeDivisor AS DECIMAL(18,1)) [Distance]
FROM
#r;
--NOTE: WHEN USING THE STGeomFromText you must past the LONGT as the first parameter.... That was the error

So what did that come up with a distance as for the original problem?

--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)