I have a small table of 10 cities (in one table) that I am snapping to the nearest road (in another table) with the following select and storing the table snapped_cities:

Select b.id, ST_Closestpoint(ST_Collect(a.geom), b.geom) as geom
into snapped_cities
from roads a inner join cities b
on ST_Dwithin(a.geom, b.geom, 1000)
group by b.id;

I would however like to have road ids and not just the geom (a.id above). How do I modify the select above to get these?

I use postgresql 9.2 and postgis 2.0



I found a solution myself:

SELECT DISTINCT ON(b.id) b.id as id_city, a.id as id_road, a.geom
FROM roads a INNER JOIN cities b
ON ST_DWithin(b.geom, a.geom, 1000)
ORDER BY b.id, ST_Distance(a.geom, b.geom);

It's probably also possible to use st_closestpoint, but I couldn't get it to work.

  • see st_closestpoint in blog.jordogskog.no/2010/02/07/…, I use theses sample - it's work. Try change "group by b.id;" to "group by b.id, b.geom;" – HasT Oct 21 '13 at 12:41
  • I have indeed used that blog already. Including b.geom in the group by clause doesn't help on my problem - I want the a.id. I think the problem is that st_closestpoint() finds the closest POINT (of course!) where I want the closest road ID. – Mace Oct 21 '13 at 12:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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