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

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…. ???

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
>