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?