PostGIS: Approximating a house number from address range

Question

I'm developing a reverse-geocoder for Canada. So far, given a lat/lng, I can find the nearest street (line segment), which includes line segment direction and address ranges for both sides of the street. I'm now trying to figure out the best way to programmatically approximate the nearest house number to the given lat/lng point.

Here's an example of a row containing the street data:

-[ RECORD 1 ]-
[...]
l_adddirfg | Same Direction
l_hnumf    | 3219
l_hnuml    | 3235
l_stname_c | Breen Road North-west
r_adddirfg | Same Direction
r_hnumf    | 3224
r_hnuml    | 3236
r_stname_c | Breen Road North-west
the_geom   | 0105000020E610000001000000010200000002000000B0F6990E78885CC088DF2B5F3C8C49400875B39A89885CC0A0BCA6AC4B8C4940

So, given a lat/lng coordinate that lies near the "the_geom" line segment, a person could tell visually which side of the street the point is on (left or right side), and how far along the segment it is -- thereby approximating a house number. For example, if the point lies on the right side, three-quarters down the street, I would use the fields r_hnumf (right side, first number) and r_hnuml (right side, last number)... The street address is probably close to:

3232 Breen Road North-west

What I'm looking for is a best practice in either computing/approximating this in PostGIS (which I'm new to), or in the application layer once the row is fetched.

Thanks!

Answer 1

The solution can be built entirely in PostGIS.

Given a point (house location, i've modeled it as a POINT) and a street segment (segment of street closest to this point, modeled as LINESTRING) you ask:

• How to tell if point is to the left of a street segment

A possible solution is to determine the point on the street segment that is closest to the house and then determine if this point lies of the left or right of the house (the operator &< returns true if the first geometry argument overlaps or is to the left of the second geometry argument). &< actually works with bounding boxes but since we're working with points this should not matter.

osm=# select 'POINT(4 1)'::geometry &< st_closestpoint('LINESTRING(1 1,2 3,6 6)'::geometry, 'POINT(4 1)'::geometry) as houseIsOnLeft;
 houseisonleft 
---------------
 f
(1 row)

• How far along is the house on the street segment

Again this translates to how far along (a value between 0 and 1) on the street segment is the point on the street that is closest to the house. There's a built-in function for that, called st_line_locate_point:

osm=# select st_line_locate_point('LINESTRING(1 1,2 3,6 6)'::geometry, 'POINT(2 1)'::geometry);
 st_line_locate_point 
----------------------
   0.0618033988749895
(1 row)

osm=# select st_line_locate_point('LINESTRING(1 1,2 3,6 6)'::geometry, 'POINT(5 6)'::geometry);
 st_line_locate_point 
----------------------
    0.889442719099992
(1 row)

Answer 2

Take a look at http://www.postgis.org/documentation/manual-svn/ST_Line_Locate_Point.html

The example on the page is essentially what you are trying to do too I think.