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
>
