<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=us-ascii" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 8.00.7601.17622"></HEAD>
<BODY>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=622425717-09072011>Mike,</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=622425717-09072011></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=622425717-09072011>Which version are you running with? For the newer
ones -- if you look at the normalize_address function , you should see a stamp
in the beginning of the code that has</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=622425717-09072011></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=622425717-09072011>--$Id: normalize_address.sql 7616 2011-07-07 12:41:13Z
robe $-</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=622425717-09072011></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=622425717-09072011>That is the latest version stamp. Really old
versions don't even have a stamp. If you are running something older
than a week or 2 ago, that is probably why you are having these
issues.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=622425717-09072011>The easiest way to upgrade to the latest
is:</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=622425717-09072011>1) Download the PostGIS 2.0 tar ball from
here -- <A
href="http://www.postgis.org/download/">http://www.postgis.org/download/</A>
(the tiger_geocoder is in extras/tiger_geocoder/tiger_2010)
</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=622425717-09072011>-- (This new version requires PostGIS 1.5+ and
PostgreSQL 8.4+)</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=622425717-09072011></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=622425717-09072011>2) Edit the upgrade_geocoder.sh file with your
postgres settings and then run it.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=622425717-09072011></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=622425717-09072011>3) Run the Missing_indexes_Generate_Script() to get the
commands to build indexes you may be missing.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=622425717-09072011></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=622425717-09072011><A
href="http://www.postgis.org/documentation/manual-svn/Missing_Indexes_Generate_Script.html">http://www.postgis.org/documentation/manual-svn/Missing_Indexes_Generate_Script.html</A></SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=622425717-09072011> and then execute that generated
script</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=622425717-09072011></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=622425717-09072011>------------------------------------------------------------------------------------------</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=622425717-09072011>I think this is a bug I might have fixed. When I
run normalize on this I don't get an error, and yours seems to be breaking in
the normalize step.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=622425717-09072011></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=622425717-09072011>When I run</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=622425717-09072011>SELECT * FROM normalize_address('<FONT color=#000000
size=3 face="Times New Roman">3261 South West 160 Avenue, Miramar, FL
33027')</FONT></SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#000000 size=3 face="Times New Roman"><SPAN
class=622425717-09072011></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=622425717-09072011>I get the below</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=622425717-09072011></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=622425717-09072011></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=622425717-09072011></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=622425717-09072011> address | predirabbrev | streetname |
streettypeabbrev | postdirabbrev | internal | location | stateabbrev |
zip |
parsed<BR>---------+--------------+------+------------------+---------------+----------+----------+-------------+-------+--------<BR>
3261 |
SW
| 160 |
Ave
|
| Miramar |
FL | 33027 |
t</SPAN></FONT></DIV>
<DIV> </DIV>
<DIV><FONT color=#0000ff size=2 face=Arial></FONT> </DIV>
<DIV><SPAN class=622425717-09072011><FONT color=#0000ff size=2
face=Arial>Unfortunately I don't have Florida data loaded so can't tell if it
will geocode right or not. </FONT></SPAN></DIV>
<DIV><SPAN class=622425717-09072011><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN class=622425717-09072011><FONT color=#0000ff size=2
face=Arial>Regarding bad rating results, the issue is often what the address
gets normalized to and a bad normalization is often the cause of bad
geocoding results. So probably the</FONT></SPAN></DIV>
<DIV><SPAN class=622425717-09072011><FONT color=#0000ff size=2 face=Arial>best
check to do is do a simple</FONT></SPAN></DIV>
<DIV><SPAN class=622425717-09072011><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN class=622425717-09072011><FONT color=#0000ff size=2
face=Arial>SELECT * FROM normalize_address(...);</FONT></SPAN></DIV>
<DIV><SPAN class=622425717-09072011><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN class=622425717-09072011><FONT color=#0000ff size=2 face=Arial>and
see what it comes with and if that normalized addess makes
sense.</FONT></SPAN></DIV>
<DIV><SPAN class=622425717-09072011><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN class=622425717-09072011><FONT color=#0000ff size=2 face=Arial>There
are many cases where it doesn't that I have fixed recently and some more that
are known bugs I am working on. Check out the list of issues and closed
ones for details:</FONT></SPAN></DIV>
<DIV><SPAN class=622425717-09072011><FONT color=#0000ff size=2 face=Arial><A
href="http://trac.osgeo.org/postgis/query?component=tiger+geocoder">http://trac.osgeo.org/postgis/query?component=tiger+geocoder</A></FONT></SPAN></DIV>
<DIV><SPAN class=622425717-09072011><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN class=622425717-09072011><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN class=622425717-09072011><FONT color=#0000ff size=2 face=Arial>The
other issue I see with your code -- which perhaps it was for testing, is that
you are normalizing twice.</FONT></SPAN></DIV>
<DIV><SPAN class=622425717-09072011><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN class=622425717-09072011><FONT color=#0000ff size=2 face=Arial>You
should just be doing a:</FONT></SPAN></DIV>
<DIV><SPAN class=622425717-09072011>geocode('3261 South West 160 Avenue,
Miramar, FL 33027')</SPAN></DIV>
<DIV><SPAN class=622425717-09072011></SPAN> </DIV>
<DIV><SPAN class=622425717-09072011><FONT color=#0000ff size=2 face=Arial>No
need for those extra steps.</FONT></SPAN></DIV>
<DIV><SPAN class=622425717-09072011><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN class=622425717-09072011><FONT color=#0000ff size=2 face=Arial>You
might want to take a look at the manual for recent improvements made too and
examples:</FONT></SPAN></DIV>
<DIV><SPAN class=622425717-09072011><FONT color=#0000ff size=2 face=Arial><A
href="http://www.postgis.org/documentation/manual-svn/Geocode.html">http://www.postgis.org/documentation/manual-svn/Geocode.html</A></FONT></SPAN></DIV>
<DIV><SPAN class=622425717-09072011><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV>
<DIV><SPAN class=622425717-09072011><FONT color=#0000ff size=2 face=Arial>Hope
that helps,</FONT></SPAN></DIV>
<DIV><SPAN class=622425717-09072011><FONT color=#0000ff size=2
face=Arial>Regina</FONT></SPAN></DIV>
<DIV><SPAN class=622425717-09072011><FONT color=#0000ff size=2 face=Arial><A
href="http://www.postgis.us">http://www.postgis.us</A></FONT></SPAN></DIV>
<DIV><FONT color=#0000ff size=2 face=Arial></FONT><FONT color=#0000ff size=2
face=Arial></FONT><FONT color=#0000ff size=2 face=Arial></FONT><FONT
color=#0000ff size=2 face=Arial></FONT><BR></DIV>
<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>Michael Papet<BR><B>Sent:</B> Saturday, July 09, 2011 12:56 PM<BR><B>To:</B>
postgis-users@postgis.refractions.net<BR><B>Subject:</B> [postgis-users] Newbie
Geocoding Error<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV
style="BACKGROUND-COLOR: #fff; FONT-FAMILY: times new roman, new york, times, serif; COLOR: #000; FONT-SIZE: 12pt">
<DIV>Hi,</DIV>
<DIV><BR></DIV>
<DIV>I'm a newbie using postgis from Debian's Testing repos. (v. 1.5) I
used the tiger_loader script to load the data.</DIV>
<DIV><BR></DIV>
<DIV>Plenty of addresses geocode just fine. Using the below query, I
get an error.</DIV>
<DIV><BR></DIV>
<DIV>SELECT g.rating, ST_X(ST_AsEWKT(ST_SnapToGrid(g.geomout,0.00000001)))
As lon, ST_Y(ST_AsEWKT(ST_SnapToGrid(g.geomout,0.00000001))) As lat,
ST_AsEWKT(ST_SnapToGrid(g.geomout,0.00000001)) As wktlonlat,
<BR> (addy).address As stno, (addy).streetname As street,
<BR> (addy).streettypeabbrev As styp, (addy).location As city,
(addy).stateabbrev As st,(addy).zip <BR> FROM
geocode(pprint_addy(normalize_address('3261 South West 160 Avenue, Miramar, FL
33027'))) As g <BR> order by g.rating</DIV>
<DIV><BR></DIV>
<DIV>Error: ERROR: query "SELECT substring(reducedStreet, '(?i)(^' ||
name<BR> || ')' || ws) FROM
direction_lookup WHERE<BR>
reducedStreet ILIKE '%' || name || '%' AND texticregexeq(reducedStreet,
'(?i)(^' || name || ')' || ws)<BR>
ORDER BY length(name) DESC" returned more than one row<BR> Where: PL/pgSQL
function "normalize_address" line 280 at assignment<BR>PL/pgSQL function
"geocode" line 10 at assignment<BR>SQLState: 21000<BR>ErrorCode:
0</DIV><BR>The problem is with the street name 'South West 160 Avenue.' Is that
a bug?<BR><BR>Also, Southwest 160 Avenue' returns some very low-accuracy
results. What queries can I use to view the tiger data to increase the
rating? In my head, I'd like to select all street names in the zip code 33027
using like statements to narrow results to figure out why the g.rating is so bad
on this address. But, that's probably different than how TIGER data is
structured. Can someone post an example of a select to improve the
accuracy? It's also possible the address is slightly wrong, but I get
accurate results from google earth.<BR><BR>Thanks in
advance.<BR><BR><BR><BR><BR>
<DIV></DIV></DIV></BODY></HTML>