2

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?

gps point not on the linestring

Data: 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). I tried:

 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;

Sample result:

sample output

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.

underdark
  • 84,148
  • 21
  • 231
  • 413
daydayup
  • 192
  • 7
  • 1
    So you want to "move" that point onto the road? Do you have just one linestring, or many? If just one point, its ST_ClosestPoint (http://www.postgis.org/docs/ST_ClosestPoint.html), but otherwise you need to select the right line from your table first. – BradHards Jul 25 '15 at 00:49
  • Thank you @BradHards. I have many linestrings because it is a large road network (Sorry about the confusing picture). I am thinking about using ST_DWTHIN(point,line,threshold) to find the nearest line. I have to select a threshold randian very carefully, but still there will be one point-many lines and some points matches no line(because the point is way off from the roads). I am thinking about how to keep only one line for those one point-many lines case. (Do you have any good idea?) Then I will use ST_ClosestPoint as you told me to find the substitute points on the selected linestring. – daydayup Jul 25 '15 at 01:03
  • 1
    Its hard to say without knowing the schema. Can you update your question with a bit more detail about what you have, what you've already tried, and what the results / issues were? – BradHards Jul 25 '15 at 01:05
  • I am doing that. lol – daydayup Jul 25 '15 at 01:05
  • 1
    Also, check http://gis.stackexchange.com/questions/42003/how-to-find-nearest-line-from-point-using-postgis – BradHards Jul 25 '15 at 01:12
  • Thank you ! I tried ST_DISTANCE(). It is extremely slow because it calculate the distance between the accident point and all linestrings in the road network, which has a huge amount. ST_DWITHIN is much faster – daydayup Jul 25 '15 at 01:15
  • ORDER BY distance LIMIT 1 – BradHards Jul 25 '15 at 05:15
  • 1
    The term for what you want to do is map-matching. We have some related/duplicate questions specifically using PostGIS: postgis map-matching – Chris W Jul 25 '15 at 19:11

0 Answers0