Introduction
The very basics
Infinite looping
Processing trees with recursive CTE
Processing graphs with recursive CTE
Instruction
Excellent. Let's take the next step in the construction of our query.
Exercise
We've turned your previous query into a CTE. Your task is to complete the outer query based on the template. For London and all roads from London, show the following columns:
path– forLondon, it's simply 'London', for other cities, it will be the path from London in the following format:London->CityName,lat,lon,total_distance– here: distance from London to that city,count_places– here: 2.
You can use the function lat_lon_distance(lat1,lon1,lat2,lon2) that we prepared for you to calculate the distance between two cities.
Stuck? Here's a hint!
In the outer query, first select all information for London from the CTE, then use UNION ALL and construct the path using the concatenation operator ||.
