Great! Finally, it's time to construct the whole query!
Read the instructions below carefully, then modify the template.
We want to visit every city, starting from London. List all paths which start in London and visit all six cities in the table city.
Show the following columns:
path – A list of consecutive cities, separated by '->', e.g. London->Oxford->Cambridge....last_lat and last_lon – The GPS coordinates of the last city in the path.total_distance – How much we need to travel, in total, during this trip.count_places – A helper column that shows the number of cities in the path.Order the results by total_distance.
You can use the function lat_lon_distance(lat1,lon1,lat2,lon2) to calculate the distance between two cities.
The london CTE should become your anchor member. The second part of the outer query should become the recursive member, but you need to construct the path column by referring to the path from the previous recursive step.
In the outer query, show only those paths where the number of cities equals 6. Order the rows in descending order by total_distance.
We also need a termination check. We can use the function position(a IN b), which will return 0 if string a is not contained in string b. In other words, we only want to add a certain city if it didn't appear in the path so far.
We have added appropriate casts in the anchor member to help you with the query.
