SQL: calculating distances between a town and multiple other locations

I have three tables in Oracle:

HOTEL: Lists the hotel number, hotel name, and the town ID of where the hotel is located. There are 5 rows here (meaning 5 hotels, but not every town has a hotel).

TOWN: Lists the town ID, the town name, the town longitude, and town latitude. There are 15 rows here (meaning 15 towns).

TOURIST ATTRACTION: List the attraction number, attraction name, and the town ID of where the attraction is located. There are 20 rows here (meaning 20 tourist attractions, some towns may have multiple attractions, other towns may have no attractions).

Basically what I want to do is work out the straight-line distances between each hotel and each attraction. For example, I want the distance between hotel 1 and attraction 1, then the distance between hotel 1 and attraction 2, the the distance between hotel 1 and attraction 3, and so on.

To calculate the distances I am using the geodistance function (SELECT geodistance(latitude 1, longitude 1, latitude 2, longitude 2) FROM dual).

I am able to get a list of the hotels and their respective longitudes and latitudes by joining HOTEL to TOWN on town ID. I am also able to get a list of tourist attractions and their respective longitudes and latitudes by joining TOURIST ATTRACTION to TOWN on town ID, but in another query. When I try to write one query I get a ‘single row subquery returns more than one row’ error.

One of the restrictions is that a cross join CANNOT be used get the required results. I want my output to be 100 rows (20 distances from hotel to attraction for each hotel), but I have absolutely no idea how to do that without using a cross join.

Any help is appreciated.

Thanks in advance!

Answer

You seem to want a cross join and distance calculation:

select ha.*
from (select h.*, a.*,
             geodistance(h.latitude, h.longitude, a.latitude, a.longitude) as distance,
             row_number() over (partition by h.hotelid order by geodistance(h.latitude, h.longitude, a.latitude, a.longitude) as seqnum
      from hotel h cross join
           attraction a
     ) ha
where seqnum < 20;

If for some pedantic reason you are not allowed cross join, one possible equivalent is:

hotel h join
attraction a
on 1=1

Or in Oracle 12C:

hotel h cross apply
attraction a

If you have 5 hotels, 20 attractions, and want 100 rows, then cross join is what you want. I would say the restriction is meaningless.