This question already has an answer here:
I have some noisy GPS data(Points) that they are not exactly on the road because of the noise. But I want to match them to the closest road linestrings. How to do this in PostgreSQL?
accident_tn is the traffic accidents data that contains
st_case as its unique id, and
geom as its geometry (which is Point type);
road is the road network that contains
id as the unique id of the linestring, and
geom_way as its geometry (which is linestring type);
Goal: match each accident point with a road network linestring (the closest one).
select acc.st_case, road.id from accident_tn acc, at_2po_4pgr road where st_dwithin(acc.geom,road.geom_way,0.0002) order by acc.st_case;
Remaining Problem: Because of the radian value in
ST_DWITHIN(), some accidents match more than one linestring while some accidents match none.
I am thinking about how can I modify the query to keep only one linestring for those one point-many linestrings case. In this sense, I can then select a larger randian threshold to match all accident point to one and only one linestring.