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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
