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

You can accomplish this task with a `self-join`.
``````select