<!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=387173101-17102011><FONT color=#0000ff
size=2 face=Arial>Rene,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=387173101-17102011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=387173101-17102011><FONT color=#0000ff
size=2 face=Arial>Actually we implemented a reverse geocoding function too for
tiger 2010. That might be more what you are looking
for.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=387173101-17102011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=387173101-17102011><FONT color=#0000ff
size=2 face=Arial><A
href="http://trac.osgeo.org/postgis/browser/trunk/extras/tiger_geocoder/tiger_2010/geocode/reverse_geocode.sql">http://trac.osgeo.org/postgis/browser/trunk/extras/tiger_geocoder/tiger_2010/geocode/reverse_geocode.sql</A></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=387173101-17102011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=387173101-17102011><FONT color=#0000ff
size=2 face=Arial>The function usage is described here:</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=387173101-17102011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=387173101-17102011><FONT color=#0000ff
size=2 face=Arial><A
href="http://www.postgis.org/documentation/manual-svn/Reverse_Geocode.html">http://www.postgis.org/documentation/manual-svn/Reverse_Geocode.html</A></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=387173101-17102011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=387173101-17102011><FONT color=#0000ff
size=2 face=Arial>As far as installing the functions. A lot fo the
functions have dependencies on other functions withing tiger schema.
</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=387173101-17102011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=387173101-17102011><FONT color=#0000ff
size=2 face=Arial>If you download the latest PostGIS 2.0 tar ball, that might be
the easiest way to get started. </FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=387173101-17102011><FONT color=#0000ff
size=2 face=Arial><A
href="http://www.postgis.org/download/postgis-2.0.0SVN.tar.gz">http://www.postgis.org/download/postgis-2.0.0SVN.tar.gz</A></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=387173101-17102011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=387173101-17102011><FONT color=#0000ff
size=2 face=Arial> There is a create_geocode.sh/bat scripts that install
all the functions <SPAN class=387173101-17102011><FONT color=#0000ff size=2
face=Arial>in the
extras\tiger_geocoder\tiger_2010</FONT></SPAN></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=387173101-17102011><FONT color=#0000ff
size=2 face=Arial> folder and a README which I think is more or less up to
date detailing installation etc.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=387173101-17102011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=387173101-17102011><FONT color=#0000ff
size=2 face=Arial>Leo</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=387173101-17102011><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=387173101-17102011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=387173101-17102011><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"
dir=ltr>
<DIV dir=ltr lang=en-us class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B> René Fournier
[mailto:m5@renefournier.com] <BR><B>Sent:</B> Sunday, October 16, 2011 9:17
PM<BR><B>To:</B> PostGIS Users Discussion; Paragon
Corporation<BR><B>Subject:</B> Re: [postgis-users] Finding the closest house
number on a street<BR></FONT><BR></DIV>
<DIV></DIV>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 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="Z-INDEX: auto; POSITION: static; BORDER-LEFT: rgb(0,0,255) 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> <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></BLOCKQUOTE></BODY></HTML>