CODEWITHSUNDEEP
sql
benderuniut
benderuniut

Reputation: 1

SQL Distance calculation between 1 point and any other

This might be a clone question, but no of the other answers I searched for did make any sense to me. I am still learning SQL so I would appreciate if you would guide me through the process of doing this. Thanks in advance.

So the problem is : I have this table ( with more data in it ) and I need to get the name of the airport that is the farthest away from Fiumicino airport ( that means I only have 1 set of longitude and latitude data ) and I have to do it with the distance function. Sql table

Upvotes: 0

Views: 1708

Answers (3)

S3S
S3S

Reputation: 25152

SQL SERVER

You will need a function if you are trying to find the furthest one from each airport. But since you said FCO, I did it for FCO.

--temp table for testing

select 'FCO' as code, 'Fiumicino' as name, 'Rome' as city, 'Italy' as country, 41.7851 as latitude, 12.8903 as longitude into #airports
union all
select 'VCE', 'Marco Polo','Venice','Italy',45.5048,12.3396
union all
select 'NAP', 'capodichino','Naples','Italy',40.8830,14.2866
union all
select 'CDG', 'Charles de Gaulle','Paris','France',49.0097,2.5479

--create a point from your LAT/LON
with cte as(
select 
    *,
    geography::Point(latitude,longitude,4326) as Point --WGS 84 datum
from #airports),

--Get the distance from your airport of interest and all others.
cteDistance as(
select
    *,
    Point.STDistance((select Point from cte where code = 'FCO')) as MetersToFiuminico
from cte)

--this is the one that's furthest away. Remove the inner join to see them all
select d.* 
from 
    cteDistance d
    inner join(select max(MetersToFiuminico) as m from cteDistance where MetersToFiuminico > 0) d2 on d.MetersToFiuminico = d2.m

Upvotes: 0

Chintan7027
Chintan7027

Reputation: 7615

Simply you can run following sql query

SELECT *, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM table_name;

Where;

To search distance by kilometers instead of miles, replace 3959 with 6371.

37 is Your input latitude

-122 is your input longitude

lat is table column name which contains airport latitude values

lng is table column name which contains airport longitude value

More details answer: Creating a store locator

Upvotes: 3

Charles Bretana
Charles Bretana

Reputation: 146597

Whatever distance function you are using (simple straight line Pythagorean for short distances, or Great circle formula for anything over a few thousand miles),

Select * from table 
 where [DistanceFunction]
         (Latitude, Longitude, FiumicinoLatitude, FiumicinoLongitude) = 
     (Select Max([DistanceFunction]
         (Latitude, Longitude, FiumicinoLatitude, FiumicinoLongitude))
      From table)

if you need to find the airport the farthest away from some arbitrary airport (not always Fiumicino), then, assuming @code is airport code of arbitrary airport:

Select * from table t
    join table r on r.code = @code
 where [DistanceFunction]
         (t.Latitude, t.Longitude, r.Latitude, r.Longitude) = 
     (Select Max([DistanceFunction]
         (Latitude, Longitude, r.Latitude, r.Longitude))

Upvotes: 0

Related Questions