<!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><SPAN class=009003118-10072011><FONT color=#0000ff
size=2 face=Arial> Mike, </FONT></SPAN><BR><BR><SPAN
class=009003118-10072011><FONT color=#0000ff size=2 face=Arial> >
</FONT></SPAN>1. the upgrade_geocoder script ran with most of the sql
returning 'already exists' errors. Do I need to modify the script to drop
before recreating?<SPAN class=009003118-10072011><FONT color=#0000ff size=2
face=Arial> </FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=009003118-10072011><FONT color=#0000ff
size=2 face=Arial>No you should be fine. The already exists and
already exists skipping issue can be ignored. I didn't want
to destroy some of these structures if they existed because they could be tied
to data.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=009003118-10072011><FONT color=#0000ff
size=2 face=Arial>That is why </FONT> <FONT color=#0000ff size=2
face=Arial>I don't have the whole upgrade script in a transaction because it
would fail the whole thing when it doesn't need to. Unfortunately
PostgresQL doesn't yet support the CREATE IF NOT EXISTS for a lot of things like
new columns I needed to add to lookup etc.</FONT></SPAN></DIV><SPAN
class=009003118-10072011><FONT color=#0000ff size=2 face=Arial></FONT></SPAN>
<DIV dir=ltr align=left><BR><SPAN class=009003118-10072011><FONT color=#0000ff
size=2 face=Arial> > </FONT></SPAN>2. My original error went
away.<BR><BR><SPAN class=009003118-10072011><FONT color=#0000ff size=2
face=Arial> <FONT color=#000000 size=3 face="Times New Roman">>
</FONT> </FONT></SPAN>So, now onto another address that does not
geocode. The results don't even include the street in the query.<BR><SPAN
class=009003118-10072011><FONT color=#0000ff size=2 face=Arial> <FONT
color=#000000 size=3 face="Times New Roman">> </FONT> </FONT></SPAN>477
Camino del Rio South, San Diego, CA 94115<BR><BR><SPAN
class=009003118-10072011><FONT color=#0000ff size=2 face=Arial> <FONT
color=#000000 size=3 face="Times New Roman">>
</FONT> </FONT></SPAN>SELECT * from normalize_address
returns <BR><SPAN class=009003118-10072011><FONT color=#0000ff size=2
face=Arial> <FONT color=#000000 size=3 face="Times New Roman">>
</FONT> </FONT></SPAN>address
predirabbrev streetname
streettypeabbrev postdirabbrev
internal location
stateabbrev zip parsed<BR><SPAN
class=009003118-10072011><FONT color=#0000ff size=2 face=Arial> <FONT
color=#000000 size=3 face="Times New Roman">>
</FONT> </FONT></SPAN>477 <null>
Camino del Rio <null>
S <null> San Diego
CA 94115 true<BR><BR><SPAN
class=009003118-10072011><FONT color=#0000ff size=2 face=Arial> <FONT
color=#000000 size=3 face="Times New Roman">> </FONT> </FONT></SPAN>I
don't have enough knowledge of postGIS right now to know if it's a bug or
operator error.<BR><SPAN class=009003118-10072011><FONT color=#0000ff size=2
face=Arial> As Steve mentioned in another post, it could be a data issue
since the normalization looks right. I looked at the
results</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=009003118-10072011><FONT color=#0000ff
size=2 face=Arial>for California and I see what you mean that the street
appears nowhere in results.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=009003118-10072011></SPAN><SPAN
class=009003118-10072011><FONT color=#0000ff size=2
face=Arial> </FONT></SPAN></DIV>
<DIV dir=ltr align=left><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=009003118-10072011>If I look at my underlying Tiger edges and featnames
for California. ca_edges, ca_featnames </SPAN></FONT></FONT></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=009003118-10072011></SPAN></FONT></FONT></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=009003118-10072011>There is a Camino del Rio Ct, but has its zip
listed as 93308 which is no where near 94115 as far as numeric
distance goes.</SPAN></FONT></FONT></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=009003118-10072011>If I look at the street ranges in ca_addr for that
tlid, It gives me ranges from 2400 - 3199 again which is no where near what that
is. So your address</SPAN></FONT></FONT></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=009003118-10072011>fails the only Camino del Rio match it could possibly
match to in a big way.</SPAN></FONT></FONT></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=009003118-10072011></SPAN></FONT></FONT></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=009003118-10072011>In fact when I look thru all the street names in
ca_edges with zip 94115, none start with Camino
anything.</SPAN></FONT></FONT></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=009003118-10072011></SPAN></FONT></FONT></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=009003118-10072011>So even taking off the zip doesn't help. It does
seem to be a data issue.</SPAN></FONT></FONT></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=009003118-10072011></SPAN></FONT></FONT></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=009003118-10072011>-- Steve,</SPAN></FONT></FONT></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=009003118-10072011>I don't think its the zip issue per se because I do
have that zip listed in my ca_zip_state and as I recall, I don't think I
have</SPAN></FONT></FONT></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=009003118-10072011>my loader generate the zips from the zcta5 file, since
that would be inaccurate since zips aren't really polygons and also aren't
updated</SPAN></FONT></FONT></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=009003118-10072011>as frequently as you stated.
</SPAN></FONT></FONT></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=009003118-10072011></SPAN></FONT></FONT></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=009003118-10072011>It could be this street is known by another name and
Tiger doesn't have this particular name listed in its featnames alias or it
somehow missed this place entirely.</SPAN></FONT></FONT></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=009003118-10072011>Leo says his friend works around there :) so
I guess its an important place to miss.</SPAN></FONT></FONT></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=009003118-10072011></SPAN></FONT></FONT></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=009003118-10072011>Hope that helps,</SPAN></FONT></FONT></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=009003118-10072011>Regina and Leo</SPAN></FONT></FONT></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=009003118-10072011><A
href="http://www.postgis.us">http://www.postgis.us</A></SPAN></FONT></FONT></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=009003118-10072011></SPAN></FONT></FONT></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=009003118-10072011></SPAN></FONT></FONT></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=009003118-10072011> </SPAN><SPAN
class=009003118-10072011> </SPAN><SPAN
class=009003118-10072011> </SPAN></FONT></FONT></FONT><BR>Message:
10<BR>Date: Sat, 9 Jul 2011 14:23:09 -0400<BR>From: "Paragon Corporation" <<A
href="mailto:lr@pcorp.us">lr@pcorp.us</A>><BR>Subject: Re: [postgis-users]
Newbie Geocoding Error<BR>To: "'PostGIS Users Discussion'"<BR>
<<A
href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</A>><BR>Message-ID:
<20AE3B391EAA4F1A8CBE0347002383E2@J><BR>Content-Type: text/plain;
charset="us-ascii"<BR><BR>Mike,<BR><BR>Which version are you running with?
For the newer ones -- if you look at<BR>the normalize_address function , you
should see a stamp in the beginning of<BR>the code that has<BR><BR>--$Id:
normalize_address.sql 7616 2011-07-07 12:41:13Z robe $-<BR><BR>That is the
latest version stamp. Really old versions don't even have a<BR>stamp. If
you are running something older than a week or 2 ago, that is<BR>probably why
you are having these issues.<BR>The easiest way to upgrade to the latest
is:<BR>1) Download the PostGIS 2.0 tar ball from here --<BR><A
href="http://www.postgis.org/download/"
target=_blank>http://www.postgis.org/download/</A> (the tiger_geocoder is
in<BR>extras/tiger_geocoder/tiger_2010) <BR>-- (This new version requires
PostGIS 1.5+ and PostgreSQL 8.4+)<BR><BR>2) Edit the upgrade_geocoder.sh
file with your postgres settings and then<BR>run it.<BR><BR>3) Run the
Missing_indexes_Generate_Script() to get the commands to build<BR>indexes you
may be missing.<BR><BR><A
href="http://www.postgis.org/documentation/manual-svn/Missing_Indexes_Generate_Scr"
target=_blank>http://www.postgis.org/documentation/manual-svn/Missing_Indexes_Generate_Scr</A><BR>ipt.html<BR>and
then execute that generated
script<BR><BR>----------------------------------------------------------------------------<BR>--------------<BR>I
think this is a bug I might have fixed. When I run normalize on this
I<BR>don't get an error, and yours seems to be breaking in the normalize
step.<BR><BR>When I run<BR>SELECT * FROM normalize_address('3261 South West 160
Avenue, Miramar, FL<BR>33027')<BR><BR>I get the below<BR><BR><BR><BR>address |
predirabbrev | streetname | streettypeabbrev | postdirabbrev |<BR>internal |
location | stateabbrev | zip |
parsed<BR>---------+--------------+------+------------------+---------------+---------<BR>-+----------+-------------+-------+--------<BR>
3261 | SW | 160
| Ave
|<BR>| Miramar | FL |
33027 | t<BR><BR><BR>Unfortunately I don't have Florida data loaded so can't
tell if it will<BR>geocode right or not. <BR><BR>Regarding bad rating results,
the issue is often what the address gets<BR>normalized to and a bad
normalization is often the cause of bad geocoding<BR>results. So probably
the<BR>best check to do is do a simple<BR><BR>SELECT * FROM
normalize_address(...);<BR><BR>and see what it comes with and if that normalized
addess makes sense.<BR><BR>There are many cases where it doesn't that I have
fixed recently and some<BR>more that are known bugs I am working on. Check
out the list of issues and<BR>closed ones for details:<BR><A
href="http://trac.osgeo.org/postgis/query?component=tiger+geocoder"
target=_blank>http://trac.osgeo.org/postgis/query?component=tiger+geocoder</A><BR><BR><BR>The
other issue I see with your code -- which perhaps it was for testing, is<BR>that
you are normalizing twice.<BR><BR>You should just be doing a:<BR>geocode('3261
South West 160 Avenue, Miramar, FL 33027')<BR><BR>No need for those extra
steps.<BR><BR>You might want to take a look at the manual for recent
improvements made too<BR>and examples:<BR><A
href="http://www.postgis.org/documentation/manual-svn/Geocode.html"
target=_blank>http://www.postgis.org/documentation/manual-svn/Geocode.html</A><BR><BR>Hope
that helps,<BR>Regina<BR><A href="http://www.postgis.us/"
target=_blank>http://www.postgis.us</A></DIV></BODY></HTML>