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

René Fournier m5 at renefournier.com
Thu Oct 13 17:27:17 PDT 2011

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

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20111013/1d606b17/attachment.html>

More information about the postgis-users mailing list