Centerline Geocoding with PostGIS

One of my co-workers thought this one up. In our jurisdiction we have a point address database that has all of the known, “official” addresses in our county that we use for all of our geocoding needs. Sometimes, though, geocoding to a centerline is handy. Not having an explicit street number sometimes happens, so being able to estimate a position based on a range has some advantages.

With PostGIS, this is pretty straight-forward. The first step, of course, is finding your street segment. This is a simple SQL call (though you can use soundex searches and address normalization if you want). Find your subject street segment, and grab the internal id number (gid) so we can identify it later.

The next step is figuring out how far along a road segment you want to go. That involves a wee bit of math:

travel_factor = ( - ) / ( - )

Viola. This is the proportion of the street segment you want to travel up, from 0 (begin) to 1 (end). Now we just need to query PostGIS using the line_interpolate_point function:

select x(line_interpolate_point(geometryn(the_geom, 1), )), y(line_interpolate_point(geometryn(the_geom, 1), )) from roads where gid =

We’re telling Postgis to grab our street segment, travel up the segment a given proportion, and return the XY coordinate.

Now you can centerline geocode with PostGIS. Wrap a web service around it, add it to you SOA, and if anybody asks, swoon a bit and tell them it was horribly difficult, throwing out phrases like topology and quantum gravity and string theory. They’ll probably never know.