<html><head></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ">Hi Regina,<div><br></div><div>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.</div><div><br></div><div>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.)</div><div><br></div><div>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? </div><div><br></div><div>Thanks!</div><div><br><div><div>On 2011-10-14, at 3:02 AM, Paragon Corporation wrote:</div><br class="Apple-interchange-newline"><blockquote type="cite">
<meta content="text/html; charset=iso-8859-1" http-equiv="Content-Type">
<meta name="GENERATOR" content="MSHTML 9.00.8112.16437">
<div 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-color: rgb(0, 0, 255); border-left-width: 2px; border-left-style: solid; padding-left: 5px; margin-left: 5px; margin-right: 0px; position: static; z-index: auto; ">
<div dir="ltr" lang="en-us" class="OutlookMessageHeader" align="left">
<hr tabindex="-1">
<font size="2" face="Tahoma"><b>From:</b>
<a href="mailto:postgis-users-bounces@postgis.refractions.net">postgis-users-bounces@postgis.refractions.net</a>
[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><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br></blockquote></div><br></blockquote></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></div></body></html>