Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 = '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!

share|improve this question
possible duplicate of Nearest Neighbor calculation in PostGIS – Ragi Yaser Burhum Sep 18 '12 at 1:13
up vote 3 down vote accepted

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

SELECT, AsText(tracks.way), 
FROM tracks, 
    (SELECT FROM cities AS c1, tracks AS t1
     ORDER BY ST_Distance(c1.way, t1.way) ASC LIMIT 1) AS c
WHERE'Mountain Trail';
share|improve this answer
Great, this approach works perfectly. Thanks! – Bruce Sep 18 '12 at 15:47

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.