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

[René Fournier]

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
>  
> 
> 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/fe2b3ca9/attachment.html>