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

Paragon Corporation lr at pcorp.us
Fri Oct 14 02:02:13 PDT 2011

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 --
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
 but most people using it in production are using it with PostGIS 1.5 and
works fine with that.
Hope that helps,


From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of René
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,
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

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

Any ideas? Thanks!


postgis-users mailing list
postgis-users at postgis.refractions.net

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20111014/5843756b/attachment.html>

More information about the postgis-users mailing list