Re: PostGIS: Approximating a house number from street address range - Mailing list pgsql-general
From | Andy Colson |
---|---|
Subject | Re: PostGIS: Approximating a house number from street address range |
Date | |
Msg-id | 4E9840A0.1020300@squeakycode.net Whole thread Raw |
In response to | Re: PostGIS: Approximating a house number from street address range (René Fournier <m5@renefournier.com>) |
List | pgsql-general |
> 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
pgsql-general by date: