<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=iso-8859-1" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 9.00.8112.16437"></HEAD>
<BODY
style="WORD-WRAP: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space">
<DIV dir=ltr align=left><SPAN class=646205208-14102011><FONT color=#0000ff
size=2 face=Arial>Rene,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=646205208-14102011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=646205208-14102011><FONT color=#0000ff
size=2 face=Arial>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.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=646205208-14102011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=646205208-14102011><FONT color=#0000ff
size=2 face=Arial>You can use ST_LineMerge or ST_GeometryN(geom,1) to make your
multilinestring a single linestring. Then it will
work.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=646205208-14102011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=646205208-14102011><FONT color=#0000ff
size=2 face=Arial>Also you may want to take a look at the tiger helper
function --</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=646205208-14102011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=646205208-14102011><FONT color=#0000ff
size=2 face=Arial><A
href="http://trac.osgeo.org/postgis/browser/trunk/extras/tiger_geocoder/tiger_2010/geocode/interpolate_from_address.sql">http://trac.osgeo.org/postgis/browser/trunk/extras/tiger_geocoder/tiger_2010/geocode/interpolate_from_address.sql</A></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=646205208-14102011></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=646205208-14102011><FONT color=#0000ff
size=2 face=Arial>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</FONT> </SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=646205208-14102011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=646205208-14102011><FONT color=#0000ff
size=2 face=Arial>The the tiger extra packaged with postgis 2.0 and documented
with PostGIS 2.0:</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=646205208-14102011><FONT color=#0000ff
size=2 face=Arial>
<DIV dir=ltr align=left><SPAN class=646205208-14102011><FONT color=#0000ff
size=2 face=Arial><A
href="http://www.postgis.org/documentation/manual-svn/Extras.html#Tiger_Geocoder">http://www.postgis.org/documentation/manual-svn/Extras.html#Tiger_Geocoder</A></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN
class=646205208-14102011></SPAN> </DIV></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=646205208-14102011><FONT color=#0000ff
size=2 face=Arial> but most people using it in production are using it with
PostGIS 1.5 and works fine with that.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=646205208-14102011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=646205208-14102011><FONT color=#0000ff
size=2 face=Arial>Hope that helps,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=646205208-14102011><FONT color=#0000ff
size=2 face=Arial>Regina</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=646205208-14102011><FONT color=#0000ff
size=2 face=Arial><A
href="http://www.postgis.us">http://www.postgis.us</A></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=646205208-14102011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV><BR>
<BLOCKQUOTE
style="BORDER-LEFT: #0000ff 2px solid; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px">
<DIV dir=ltr lang=en-us class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of
</B>René Fournier<BR><B>Sent:</B> Thursday, October 13, 2011 8:27
PM<BR><B>To:</B> PostGIS Users Discussion<BR><B>Subject:</B> Re:
[postgis-users] Finding the closest house number on a
street<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV>Based on a suggestion from another list, I tried
using ST_line_locate_point, but get this error:</DIV>
<DIV><FONT color=#0000ff size=2 face=Arial></FONT><BR></DIV>
<BLOCKQUOTE
style="BORDER-BOTTOM-STYLE: none; PADDING-BOTTOM: 0px; MARGIN: 0px 0px 0px 40px; BORDER-LEFT-STYLE: none; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; BORDER-TOP-STYLE: none; BORDER-RIGHT-STYLE: none; PADDING-TOP: 0px"
class=webkit-indent-blockquote>
<DIV>
<DIV>
<DIV>gc3=# SELECT</DIV></DIV></DIV>
<DIV>
<DIV>
<DIV>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,</DIV></DIV></DIV>
<DIV>
<DIV>
<DIV>gc3-# ST_Distance(ST_GeomFromText('POINT(-79.639711
43.8098590)',4326),the_geom) AS distance,</DIV></DIV></DIV>
<DIV>
<DIV>
<DIV>gc3-# ST_line_locate_point(the_geom, ST_GeomFromText('POINT(-79.639711
43.8098590)')) As street_num</DIV></DIV></DIV>
<DIV>
<DIV>
<DIV>gc3-# FROM province_on</DIV></DIV></DIV>
<DIV>
<DIV>
<DIV>gc3-# WHERE the_geom && SetSRID('BOX3D(-79.64991853
43.80470025,-79.63089798 43.81621783)'::box3d,4326) ORDER BY distance ASC
LIMIT 1;</DIV></DIV></DIV>
<DIV>
<DIV>
<DIV>ERROR: line_locate_point: 1st arg isnt a line</DIV></DIV></DIV>
<DIV>
<DIV>
<DIV>gc3=# </DIV></DIV></DIV></BLOCKQUOTE>
<DIV><BR></DIV>
<DIV>So I'm using PostGIS 1.5.3, and the docs (<A
href="http://postgis.refractions.net/docs/ST_Line_Locate_Point.html">http://postgis.refractions.net/docs/ST_Line_Locate_Point.html</A>)
say that multilinestrings are supported, so…. ???</DIV>
<DIV><BR></DIV>
<DIV><BR></DIV>
<DIV>
<DIV>On 2011-10-13, at 6:19 PM, René Fournier wrote:</DIV><BR
class=Apple-interchange-newline>
<BLOCKQUOTE type="cite">
<DIV
style="WORD-WRAP: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space">In
PostGIS parlance, <SPAN style="FONT-FAMILY: monospace; WHITE-SPACE: pre"
class=Apple-style-span>given a lat/lng point... where -- in terms of
percentage or decimal -- does it lie along on a row's </SPAN><SPAN
style="FONT-FAMILY: monospace; WHITE-SPACE: pre"
class=Apple-style-span>multilinestring?</SPAN>
<DIV><FONT class=Apple-style-span face=monospace><SPAN
style="WHITE-SPACE: pre" class=Apple-style-span><BR></SPAN></FONT></DIV>
<DIV><FONT class=Apple-style-span face=monospace><SPAN
style="WHITE-SPACE: pre" class=Apple-style-span>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.<BR></SPAN></FONT></DIV>
<DIV>
<DIV><FONT class=Apple-style-span face=monospace><SPAN
style="WHITE-SPACE: pre" class=Apple-style-span><BR></SPAN></FONT></DIV>
<DIV><FONT class=Apple-style-span face=monospace><SPAN
style="WHITE-SPACE: pre" class=Apple-style-span>For what it's worth, I'm
using the 2010 Tigerline data imported via shp2pgsql. </SPAN></FONT></DIV>
<DIV><FONT class=Apple-style-span face=monospace><SPAN
style="WHITE-SPACE: pre" class=Apple-style-span><BR></SPAN></FONT></DIV>
<DIV><FONT class=Apple-style-span face=monospace><SPAN
style="WHITE-SPACE: pre" class=Apple-style-span>Any ideas?
Thanks!</SPAN></FONT></DIV>
<DIV><FONT class=Apple-style-span face=monospace><SPAN
style="WHITE-SPACE: pre" class=Apple-style-span><BR></SPAN></FONT></DIV>
<DIV><FONT class=Apple-style-span face=monospace><SPAN
style="WHITE-SPACE: pre" class=Apple-style-span>…Rene</SPAN></FONT></DIV>
<DIV><BR></DIV></DIV></DIV>_______________________________________________<BR>postgis-users
mailing list<BR><A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A><BR>http://postgis.refractions.net/mailman/listinfo/postgis-users<BR></BLOCKQUOTE></DIV><BR></BLOCKQUOTE></BODY></HTML>