Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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!

share|improve this question
1  
possible duplicate of Nearest Neighbor calculation in PostGIS –  Ragi Yaser Burhum Sep 18 '12 at 1:13
add comment

1 Answer

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 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';
share|improve this answer
    
Great, this approach works perfectly. Thanks! –  Bruce Sep 18 '12 at 15:47
add comment

Your Answer

 
discard

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.