PostGIS – Snapping Points to Nearest Road in PostGIS

postgispostgresql

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

thanks,

Best Answer

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.

Related Question