Let's say that I have two tables in PostGIS, tracks and cities, each table has two columns, in both cases the columns are called name and way, name is a string, way is a geometry.
I want to retrieve a list of tracks by name, something like:
SELECT name, way FROM tracks WHERE name = 'Mountain Trail';
But I also want to retrieve the name of the closest city to that track. Something like:
SELECT name, way, closest_city_name FROM tracks WHERE tracks.name = 'Mountain Trail';
But there is no closest_city_name column, I want to calculate the closest city as part of the query.
I'm familiar with ST_Distance and using joins and subqueries but I'm not sure how best to put it all together for this query.
Is there a way to JOIN cities to tracks using "closest"? Or should I query against the tracks table and a subquery on the cities table or vice versa?
Sorry this is quite a vague question, I'm just really looking for someone to point me in the right direction, perhaps an example query that you have used to do something similar?
Any suggestions greatly appreciated. Thanks!