Re: PostGIS: Approximating a house number from street address range

> On 2011-10-12, at 6:31 PM, Andy Colson wrote:
>
>> On 10/12/2011 06:38 PM, Andy Colson wrote:
>>> On 10/12/2011 06:29 PM, Andy Colson wrote:
>>>> On 10/12/2011 01:01 PM, René Fournier wrote:
>>>>> Hi,
>>>>>
>>>>> 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.
>>>>>
>>>>> Any ideas? Thanks!
>>>>>
>>>>> ...Rene
>>>>>
>>>>>
>>>>>
>>>> Is this the only format you have the data in? If you had two
>>>> rectangles (one for each side of the street), and each rect had an
>>>> address, this would be a lot simpler. Is that geom a line?
>>>> rectangle? Do you have a layer that has lots or parcels?
>>>>
>>>> -Andy
>>>>
>>>>
>>>>
>>>
>>> Ah, its a line:
>>> MULTILINESTRING((-114.1323277 51.0955924,-114.1333987 51.0960594000001))
>>>
>>> But then you have a problem. If this is a street line, and its going
>>> north/south, great, but what if its going east/west? What's the right
>>> hand side of a horizontal line?
>>>
>>> -Andy
>>>
>>>
>>
>> Wow. Neet. I Learned something new. PostGIS never ceases to amaze me.
>>
>> Find the point on a line closest to a click point:
>>
>> http://postgis.refractions.net/docs/ST_Line_Locate_Point.html
>>
>>
>> Then use
>> http://postgis.refractions.net/documentation/manual-svn/ST_Azimuth.html to
>> find the angle between two points.
>>
>> The angle can tell you if the click point is left/right (or
>> above/below) the street.
>>
>> I googled two things that might offer you more help: "postgis line
>> direction" and "postgis point closest to line".
>>
>> Ok, I'll quit spamming the list now. (Oh yeah, I have some med's I can
>> sell ya!)
>>
>> -Andy
>>
>

On 10/13/2011 7:25 PM, René Fournier wrote:
 > Thanks Andy for thinking about this for me.
 >
 > I tried using that function, but get this error:
 >
 >     gc3=# SELECT
 >     gc3-# ST_AsText(the_geom) as street, strunamefr, l_adddirfg,
 >     l_hnumf, l_hnuml, l_stname_c, l_placenam, r_adddirfg, r_hnumf,
 >     r_hnuml, r_stname_c, r_placenam,
 >     gc3-# ST_Distance(ST_GeomFromText('POINT(-79.639711
 >     43.8098590)',4326),the_geom) AS distance,
 >     gc3-# ST_line_locate_point(the_geom,
 >     ST_GeomFromText('POINT(-79.639711 43.8098590)')) As street_num
 >     gc3-# FROM province_on
 >     gc3-# WHERE the_geom && SetSRID('BOX3D(-79.64991853
 >     43.80470025,-79.63089798 43.81621783)'::box3d,4326) ORDER BY
 >     distance ASC LIMIT 1;
 >     ERROR: line_locate_point: 1st arg isnt a line
 >     gc3=#
 >
 >
 > So I'm using PostGIS 1.5.3, and the docs
 > (http://postgis.refractions.net/docs/ST_Line_Locate_Point.html) say that
 > multilinestrings are supported, so…. ???
 >
 >

Huh.  Sorry, I don't know that one.  You might need to post the question
over on PostGIS and see if anyone knows.

Or, is there a way to convert your geom to a linestring?  (even as just
a test?)

Sorry,

-Andy