3

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!

Bruce
  • 257
  • 2
  • 4

1 Answers1

3

I think you need a subquery which will use ST_Distance() and ORDER BY...LIMIT 1. Something like:

SELECT tracks.name, AsText(tracks.way), c.name 
FROM tracks, 
    (SELECT cities.name FROM cities AS c1, tracks AS t1
     ORDER BY ST_Distance(c1.way, t1.way) ASC LIMIT 1) AS c
WHERE tracks.name='Mountain Trail';
Micha
  • 15,555
  • 23
  • 29
  • I used a similar approach to solve this preliminary but if I need other columns of the cities table, do I make another subquery per each field needed? Is this an efficient approach? – neavilag Mar 26 '22 at 13:24