[postgis-users] Finding the closest house number on a street

Sun Oct 16 18:38:16 PDT 2011

Rene,

Actually we implemented a reverse geocoding function too for tiger 2010.
That might be more what you are looking for.

http://trac.osgeo.org/postgis/browser/trunk/extras/tiger_geocoder/tiger_2010
/geocode/reverse_geocode.sql

The function usage is described here:

http://www.postgis.org/documentation/manual-svn/Reverse_Geocode.html

As far as installing the functions.  A lot fo the functions have
dependencies on other functions withing tiger schema.  

If you download the latest PostGIS 2.0 tar ball, that might be the easiest
way to get started. 
http://www.postgis.org/download/postgis-2.0.0SVN.tar.gz

 There is a create_geocode.sh/bat scripts that install all the functions in
the extras\tiger_geocoder\tiger_2010
  folder and a README which I think is more or less up to date detailing
installation etc.

Leo
http://www.postgis.us

  _____  

From: René Fournier [mailto:m5 at renefournier.com] 
Sent: Sunday, October 16, 2011 9:17 PM
To: PostGIS Users Discussion; Paragon Corporation
Subject: Re: [postgis-users] Finding the closest house number on a street

Hi Regina, 

Thanks! ST_LineMerge helped me get ST_line_locate_point working. But the
tiger helper function is more interesting to me now, since I would also like
to have reverse-geocoding for the Tiger line 2010 data I've imported
 And
actually, if I modify the function slightly it should work with the Canadian
tables as well.

First though
 What's the best way to import and use custom functions like
this one? I've done the typical psql -U me -d database <
interpolate_from_address.sql. Seems to have worked. But now I'm not sure
where it's saved
 And more importantly, how to use it in my query. (I've
just jumped into PostGIS a week ago, so please excuse my noobness.)

By the way, the  tiger_gecoding branch looks really rich. I wonder if I
wouldn't be better off just using it instead
 Anyone have good things to say
about it? I've downloaded all the Tiger 2010 data already. After importing
the Edges shape files via shp2pgsql I can successfully and quickly
reverse-geocode (using a simple "WHERE the_geom && SetSRID('BOX3D" query)
any lat/lng to the nearest street
 However, the city/town name isn't
included in those rows, so I will have more work to do. I'm very new to
PostGIS, but it appears that the stuff on osgeo handles all of this stuff
for me. One thing--does it require PostGIS 2.0? 

Thanks!

On 2011-10-14, at 3:02 AM, Paragon Corporation wrote:

Rene,

I'll have to double check the docs.  I tried on my 1.5.3 and 2.0 and it
appears the function doesn't work on multilinestrings so have to check where
that information came from to make sure its not a regression failure.

You can use ST_LineMerge or ST_GeometryN(geom,1) to make your
multilinestring a single linestring.  Then it will work.

Also you may want to take a look at the tiger helper  function --

http://trac.osgeo.org/postgis/browser/trunk/extras/tiger_geocoder/tiger_2010
/geocode/interpolate_from_address.sql

I think it does exactly what you are trying to do and also offsets to the
correct side of street.  It's tiger specific though 

The the tiger extra packaged with postgis 2.0 and documented with PostGIS
2.0:

http://www.postgis.org/documentation/manual-svn/Extras.html#Tiger_Geocoder

 but most people using it in production are using it with PostGIS 1.5 and
works fine with that.

Hope that helps,
Regina
http://www.postgis.us <http://www.postgis.us/> 

  _____  

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of René
Fournier
Sent: Thursday, October 13, 2011 8:27 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Finding the closest house number on a street

Based on a suggestion from another list, I tried using ST_line_locate_point,
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-13, at 6:19 PM, René Fournier wrote:

In PostGIS parlance, given a lat/lng point... where -- in terms of
percentage or decimal -- does it lie along on a row's multilinestring? 

The thing is, the multilinestring is just a single line string. But I
suppose there are cases where there are more than one segment to the street

In any case, I have a range of house numbers, and I need to determine which
side of the multilinestring the latlng lies, and then how far along
 I know
I'm not the first person to ask this, but I haven't been able to find a
suitable answer.

For what it's worth, I'm using the 2010 Tigerline data imported via
shp2pgsql. 

Any ideas? Thanks!

Rene

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20111016/0d078800/attachment.html>