I am using tiger line road data to find roads that runs through certain points. I know what road is immediately next to the point by the road name and road type. What I currently do is grab all the road segments within a 10km radius from the point of interest and then filter those based on the name of the initial road segment and its type. However, road names change and therefore, I fail to always create the correct road that I need.

SELECT [fields] FROM point_and_initial_road_matches pr INNER JOIN tl_edges tl ON ST_DWithin(ST_GeomFromText(pr.point_wkt, 4326), tl.geom, 0.1) AND SIMILARITY(pr.road_name, tl.fullname) > 0.7 AND pr.road_type=tl.mtfcc

If you look at the image below, although I am trying to catch the highway, because the name of the highway changes, I only am able to extract part of the highway and the rest is coming from a road that is next to it which has a similar name. How can I find line strings that are connected, so that, I can restrict the set by the road type and extract only the line strings that have the same road type as the original line segment? or is there a better way to do this whole exercise?

disconnected roads

Your Answer

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

Browse other questions tagged or ask your own question.