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