<!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=036421406-27102011><FONT color=#0000ff
size=2 face=Tahoma>René,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=036421406-27102011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=036421406-27102011><FONT color=#0000ff
size=2 face=Arial>You are probably missing tiger in your database search
path.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=036421406-27102011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=036421406-27102011><FONT color=#0000ff
size=2 face=Arial>When you restore a database, it doesn't restore the search
path so you have to run something like:</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=036421406-27102011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=036421406-27102011><FONT color=#0000ff
size=2 face=Arial>ALTER DATABASE yourdatabase SET search_path=public,
tiger;</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=036421406-27102011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=036421406-27102011><FONT color=#0000ff
size=2 face=Arial>Hope that helps,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=036421406-27102011><FONT color=#0000ff
size=2 face=Arial>Regina</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=036421406-27102011><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=036421406-27102011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=036421406-27102011><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>
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 27, 2011 1:51
AM<BR><B>To:</B> PostGIS Users Discussion<BR><B>Subject:</B> [postgis-users]
PostGIS 1.5.3 and Tiger Geocoder after restore<BR></FONT><BR></DIV>
<DIV></DIV>I had the Tiger Geocoder 2010 working great on one machine. I
dumped the database to a file, and then restored it to a fresh Postgresql90 +
PostGIS 1.5.3 (a la Macports)… via psql -U postgres -q -d gc4 -f
tiger_dump.sql
<DIV><BR></DIV>
<DIV>But there were a few notices/errors on the import:</DIV>
<BLOCKQUOTE
style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; PADDING-BOTTOM: 0px; MARGIN: 0px 0px 0px 40px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: medium none; BORDER-RIGHT: medium none; PADDING-TOP: 0px"
class=webkit-indent-blockquote>
<DIV><BR></DIV>
<DIV>
<DIV>psql:/Volumes/Extra/GC/USA/dump/gc4-20111023.sql:54: ERROR: type
"box2d" already exists</DIV></DIV>
<DIV>
<DIV>psql:/Volumes/Extra/GC/USA/dump/gc4-20111023.sql:63: ERROR:
function "box2d_in" already exists with same argument
types</DIV></DIV>
<DIV>
<DIV>psql:/Volumes/Extra/GC/USA/dump/gc4-20111023.sql:74: ERROR:
function "box2d_out" already exists with same argument
types</DIV></DIV>
<DIV>
<DIV>psql:/Volumes/Extra/GC/USA/dump/gc4-20111023.sql:89: ERROR: type
"box2d" already exists</DIV></DIV>
<DIV>
<DIV>psql:/Volumes/Extra/GC/USA/dump/gc4-20111023.sql:98: ERROR: type
"box3d" already exists</DIV></DIV>
<DIV>[..many more…]</DIV>
<DIV>
<DIV>psql:/Volumes/Extra/GC/USA/dump/gc4-20111023.sql:2328993: ERROR:
duplicate key value violates unique constraint
"spatial_ref_sys_pkey"</DIV></DIV>
<DIV>
<DIV>DETAIL: Key (srid)=(3819) already exists.</DIV></DIV>
<DIV>
<DIV>CONTEXT: COPY spatial_ref_sys, line 1: "3819<SPAN
style="WHITE-SPACE: pre" class=Apple-tab-span> </SPAN>EPSG<SPAN
style="WHITE-SPACE: pre" class=Apple-tab-span> </SPAN>3819<SPAN
style="WHITE-SPACE: pre" class=Apple-tab-span>
</SPAN>GEOGCS["HD1909",DATUM["Hungarian_Datum_1909",SPHEROID["Bessel
1841",6377397.155,299.1..."</DIV></DIV>
<DIV>
<DIV>psql:/Volumes/Extra/GC/USA/dump/gc4-20111023.sql:208533404: ERROR:
multiple primary keys for table "geometry_columns" are not
allowed</DIV></DIV>
<DIV>
<DIV>psql:/Volumes/Extra/GC/USA/dump/gc4-20111023.sql:208533524: ERROR:
multiple primary keys for table "spatial_ref_sys" are not
allowed</DIV></DIV></BLOCKQUOTE>
<DIV><BR></DIV>
<DIV>Still, the whole thing (~87GB) seems to have imported anyway.
But now, I try to use it:</DIV>
<DIV><BR></DIV>
<BLOCKQUOTE
style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; PADDING-BOTTOM: 0px; MARGIN: 0px 0px 0px 40px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; BORDER-TOP: medium none; BORDER-RIGHT: medium none; PADDING-TOP: 0px"
class=webkit-indent-blockquote>
<DIV>
<DIV>gc4=# SELECT g.rating, </DIV></DIV>
<DIV>
<DIV>ST_X(geomout) As lon, </DIV></DIV>
<DIV>
<DIV>ST_Y(geomout) As lat, (addy).* </DIV></DIV>
<DIV>
<DIV>FROM geocode('1731 New Hampshire Avenue Northwest, Washington, DC
20010', 1) As g;</DIV></DIV>
<DIV>
<DIV>ERROR: function geocode(unknown, integer) does not
exist</DIV></DIV>
<DIV>
<DIV>LINE 4: FROM geocode('1731 New Hampshire Avenue Northwest,
Washingto...</DIV></DIV>
<DIV>
<DIV> ^</DIV></DIV>
<DIV>
<DIV>HINT: No function matches the given name and argument types. You
might need to add explicit type casts.</DIV></DIV>
<DIV>
<DIV>gc4=# </DIV></DIV></BLOCKQUOTE>
<DIV><BR></DIV>
<DIV>What did I miss??</DIV>
<DIV><BR></DIV>
<DIV>…Rene</DIV></BLOCKQUOTE></BODY></HTML>