Canovice Canovice - 4 years ago 81
SQL Question

SQL - find distances between two cities, have to format the table

I am a beginner to SQL and have a question that involves running a query in Hive which I think is very similar / the same as SQL code. My data table looks as follows:

name lat long
NY 40.3 70.3
SF 36.1 60.2
LA 36.5 53.1
CH 45.2 62.3
...


i need to find all cities within a certain distance from one another, with distance being measured by latitutde and longitude.

My current idea on how to solve this is as follows:


  1. If there are n=10 cities (thus n=10 rows in the table), create a new table with (10*9)/2 = 45 rows, where each row now has 6 columns (city1, city2, lat1, lat2, long1, long2)

  2. With rows in this format, I can simply use a pythagorean like calculation to calculate the distance.



I'm not sure how to do step 1 in SQL. Essentially its creating every possible unique combination of pairs from the first column, and including the corresponding lat and long for each city in the pair in that row.

Is this approach smart? and how could i implement it? is there a better way?

Thanks!!

vkp vkp
Answer Source

You can accomplish this task with a self-join.

select 
d1.name as city1, d2.name as city2,
d1.lat as lat1, d2.lat as lat2, 
d1.long as long1, d2.long as long2
from datatable d1
join datatable d2 on d1.name <> d2.name
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download