PostGIS query for geocoding street intersectons using local data sets?

Question

I'm currently looking for a script example for geocoding two intersecting streets using local data sets and PostGIS.

I'm aware that the Tiger Geocoder can used for geocoding both addresses and street intersections, but the results for street intersections isn't as accurate as the local data sets and has been giving completely inaccurate results.

The current documentation only refers to the Tiger Geocoder and there isn't other docs or examples relating to this problem.

Answer 1

You can query the dataset directly. Basically you need to get the 1st street segments, then the 2nd street segments, and when then do intersect you compute the intersection point(s).

Don't forget to keep distinct results, else you will often have 4 results per intersection (4 segments from each side of the intersection)

SELECT distinct st_asText(st_intersection(a.the_geom,b.the_geom))
FROM can.roads a
    JOIN can.roads b
    ON st_intersects(a.the_geom,b.the_geom)
WHERE a.name = '1st'
    AND a.city = 'my town'
    AND b.name = '2nd'
    AND b.city  = 'my town';

The difficult part is not so much to get the coordinates but to find the proper street (spelling, wrong street type etc). You will likely want to add some fuzzy search on the street name.