SQL Distance calculation between 1 point and any other

Question

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

Can you show us what you have tried and explain why it didn't solve your issue? — dfundako, Sep 28 '16 at 16:20
I removed the incompatible database tags. Add back the one for the database you are using. — Gordon Linoff, Sep 28 '16 at 16:26
http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557 — , Sep 28 '16 at 16:40
Here is a solution for Postgres: http://stackoverflow.com/questions/10034636/postgresql-latitude-longitude-query — , Sep 28 '16 at 16:46

Chintan7027 · Answer 1 · 2016-09-28T16:51:18.770

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

Charles Bretana · Answer 2 · 2016-09-28T16:29:47.197

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

S3S · Answer 3 · 2016-09-28T18:07:19.270

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

SQL Distance calculation between 1 point and any other

3 Answers3