<!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 9.00.8112.16450"></HEAD>
<BODY>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012>Mike,</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012>Hard to tell what the issue is here. So here are
a couple of comments reading from your prior posts</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012>1) You don't need tiger_data in your search path
because all the geocoder functions just reference the tiger master tables that
the tiger_data tables are children of.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012>2) The county table not being created issue you were
having. I recall this happening a while back to somebody because
they had a table called county in their public schema so the create skipped and
since their public county table was structured differently, things would break
mysteriously.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012>So verify you don't have any tables in your public
schema with same name as those in tiger schema.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012>3) Your search_path issue. How exactly did you
set your search path?</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012>here are a couple of mistakes we've
seen</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012>a) Not setting the search path of the database
</SPAN></FONT><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012></SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012>Did you use the remarked out line in
create_geocoder</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012>ALTER DATABASE geocder SET search_path=public,
tiger;</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012>If you simply use:</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012>SET search_path=public,tiger;</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012>That only lasts for the life of the session, but is a
good way to verify you have things at least installed
correctly.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012>The other thing to keep in mind is that ALTER DATABASE
doesn't take effect for current session, you need to create a new session to get
the new settings</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012>b) explicitly setting the search_path of a user.
PostgreSQL has this neat (for super experts)/awful (for newbies or forgetfuls)
feature that allows you to set the default search path</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012>for the individual user (at the server level) and even
for specific user at the database level.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012>Youc an do something as crazy as
this:</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012>ALTER ROLE postgres IN DATABASE geocoder SET
search_path = "$user", public;</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012>and if you have such as setting, your ALTER DATABASE ..
SET search_path will be powerless when the postgres user logs
in.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012>You can also set search path at the server level for a
user</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012>Anyway check if yo uhave any of these
issues.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012>Hope that helps,</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012>Regina</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012><A
href="http://www.postgis.us">http://www.postgis.us</A></SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT color=#0000ff size=2 face=Arial><SPAN
class=696033500-11112012></SPAN></FONT> </DIV><BR>
<DIV dir=ltr lang=en-us class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B> postgis-users-bounces@lists.osgeo.org
[mailto:postgis-users-bounces@lists.osgeo.org] <B>On Behalf Of </B>Michael
Papet<BR><B>Sent:</B> Friday, November 09, 2012 12:46 PM<BR><B>To:</B>
postgis-users@lists.osgeo.org<BR><B>Subject:</B> [postgis-users] Newbie:
search_path variable<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><SPAN>More on this pprint_addy issue. The search_path variable
appears to not be set correctly. I have "$user", public, topology, tiger,
tiger_data, tiger_staging as the search_path value for the geocoder
database.<BR></SPAN></DIV>
<DIV
style="BACKGROUND-COLOR: transparent; FONT-STYLE: normal; FONT-FAMILY: times new roman,new york,times,serif; COLOR: rgb(0,0,0); FONT-SIZE: 16px"><SPAN><BR></SPAN></DIV>
<DIV
style="BACKGROUND-COLOR: transparent; FONT-STYLE: normal; FONT-FAMILY: times new roman,new york,times,serif; COLOR: rgb(0,0,0); FONT-SIZE: 16px"><SPAN>I
took the query that makes the tiger.pprint_addy function and altered it so the
pprint_addy function gets made in public.pprint_addy and now pprint_addy
function works correctly. <BR></SPAN></DIV>
<DIV
style="BACKGROUND-COLOR: transparent; FONT-STYLE: normal; FONT-FAMILY: times new roman,new york,times,serif; COLOR: rgb(0,0,0); FONT-SIZE: 16px"><SPAN><BR></SPAN></DIV>
<DIV
style="BACKGROUND-COLOR: transparent; FONT-STYLE: normal; FONT-FAMILY: times new roman,new york,times,serif; COLOR: rgb(0,0,0); FONT-SIZE: 16px"><SPAN>Attempts
to use the geocode example from here
http://postgis.refractions.net/docs/Geocode.html with Tiger2011 data return
nothing. No errors.<BR></SPAN></DIV>
<DIV><BR></DIV>
<DIV
style="FONT-FAMILY: times new roman, new york, times, serif; FONT-SIZE: 12pt">
<DIV
style="FONT-FAMILY: times new roman, new york, times, serif; FONT-SIZE: 12pt">
<DIV dir=ltr><FONT size=2 face=Arial>
<HR SIZE=1>
<B><SPAN style="FONT-WEIGHT: bold">From:</SPAN></B> Michael Papet
<mpapet@yahoo.com><BR><B><SPAN style="FONT-WEIGHT: bold">To:</SPAN></B>
"postgis-users@lists.osgeo.org" <postgis-users@lists.osgeo.org>
<BR><B><SPAN style="FONT-WEIGHT: bold">Sent:</SPAN></B> Friday, November 9, 2012
9:28 AM<BR><B><SPAN style="FONT-WEIGHT: bold">Subject:</SPAN></B> Re: Newbie:
census_loader.sql Issue<BR></FONT></DIV><BR>
<META content=off http-equiv=x-dns-prefetch-control>
<DIV id=yiv1558466896>
<DIV>
<DIV
style="BACKGROUND-COLOR: #fff; FONT-FAMILY: times new roman, new york, times, serif; COLOR: #000; FONT-SIZE: 12pt">
<DIV><SPAN>I discovered another table not made tiger.tract. Again, no
error. It just doesn't get made.<BR></SPAN></DIV>
<DIV
style="BACKGROUND-COLOR: transparent; FONT-STYLE: normal; FONT-FAMILY: times new roman, new york, times, serif; COLOR: rgb(0,0,0); FONT-SIZE: 16px"><BR><SPAN></SPAN></DIV>
<DIV
style="BACKGROUND-COLOR: transparent; FONT-STYLE: normal; FONT-FAMILY: times new roman, new york, times, serif; COLOR: rgb(0,0,0); FONT-SIZE: 16px"><SPAN>I
used the included script to load Massachusetts data to use the documentation
examples and am having more issues. <BR></SPAN></DIV>
<DIV
style="BACKGROUND-COLOR: transparent; FONT-STYLE: normal; FONT-FAMILY: times new roman, new york, times, serif; COLOR: rgb(0,0,0); FONT-SIZE: 16px"><SPAN><BR></SPAN></DIV>
<DIV
style="BACKGROUND-COLOR: transparent; FONT-STYLE: normal; FONT-FAMILY: times new roman, new york, times, serif; COLOR: rgb(0,0,0); FONT-SIZE: 16px"><SPAN>#1:
Select Function pprint_addy example does not work. Function pprint_addy is
present as pprint_addy(tiger.norm_addy). </SPAN><SPAN>I can run
SELECT normalize_address(</SPAN>'202 East Fremont Street, Las Vegas, Nevada
89101')) As pretty_address; and it returns a formatted address.</DIV>
<DIV
style="BACKGROUND-COLOR: transparent; FONT-STYLE: normal; FONT-FAMILY: times new roman, new york, times, serif; COLOR: rgb(0,0,0); FONT-SIZE: 16px"><BR></DIV>
<DIV
style="BACKGROUND-COLOR: transparent; FONT-STYLE: normal; FONT-FAMILY: times new roman, new york, times, serif; COLOR: rgb(0,0,0); FONT-SIZE: 16px"><SPAN
style="FONT-STYLE: italic"> SELECT pprint_addy(normalize_address('202 East
Fremont Street, Las Vegas, Nevada 89101')) As pretty_address;<BR>2012-11-09
08:36:50 PST ERROR: function pprint_addy(norm_addy) does not exist at
character 8<BR>2012-11-09 08:36:50 PST HINT: No function matches the given
name and argument types. You might need to add explicit type casts.</SPAN></DIV>
<DIV
style="BACKGROUND-COLOR: transparent; FONT-STYLE: normal; FONT-FAMILY: times new roman, new york, times, serif; COLOR: rgb(0,0,0); FONT-SIZE: 16px"><BR></DIV>
<DIV
style="BACKGROUND-COLOR: transparent; FONT-STYLE: normal; FONT-FAMILY: times new roman, new york, times, serif; COLOR: rgb(0,0,0); FONT-SIZE: 16px">#2
the geocode example returns nothing. No error. Just no
results.</DIV>
<DIV
style="BACKGROUND-COLOR: transparent; FONT-STYLE: normal; FONT-FAMILY: times new roman, new york, times, serif; COLOR: rgb(0,0,0); FONT-SIZE: 16px"><BR></DIV><SPAN
style="FONT-STYLE: italic">SELECT g.rating, ST_X(g.geomout) As lon,
ST_Y(g.geomout) As lat, <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('75 State Street, Boston MA 02109') As g;
</SPAN><BR><BR>
<DIV
style="BACKGROUND-COLOR: transparent; FONT-STYLE: normal; FONT-FAMILY: times new roman, new york, times, serif; COLOR: rgb(0,0,0); FONT-SIZE: 16px"><SPAN>The
search_path for the database geocoder is set to include tiger, tiger_data,
public, topology. Do I need to add those names to the search_path variable
value for database postgres?<BR></SPAN></DIV>
<DIV
style="BACKGROUND-COLOR: transparent; FONT-STYLE: normal; FONT-FAMILY: times new roman, new york, times, serif; COLOR: rgb(0,0,0); FONT-SIZE: 16px"><BR></DIV>
<DIV
style="BACKGROUND-COLOR: transparent; FONT-STYLE: normal; FONT-FAMILY: times new roman, new york, times, serif; COLOR: rgb(0,0,0); FONT-SIZE: 16px">Michael<BR><BR><SPAN></SPAN></DIV>
<DIV
style="BACKGROUND-COLOR: transparent; FONT-STYLE: normal; FONT-FAMILY: times new roman, new york, times, serif; COLOR: rgb(0,0,0); FONT-SIZE: 16px"><SPAN><BR></SPAN></DIV>
<DIV><SPAN></SPAN></DIV>
<DIV><BR></DIV>
<DIV
style="FONT-FAMILY: times new roman, new york, times, serif; FONT-SIZE: 12pt">
<DIV
style="FONT-FAMILY: times new roman, new york, times, serif; FONT-SIZE: 12pt">
<DIV dir=ltr><FONT size=2 face=Arial>
<HR SIZE=1>
<B><SPAN style="FONT-WEIGHT: bold">From:</SPAN></B> Michael Papet
<mpapet@yahoo.com><BR><B><SPAN style="FONT-WEIGHT: bold">To:</SPAN></B>
"postgis-users@lists.osgeo.org" <postgis-users@lists.osgeo.org>
<BR><B><SPAN style="FONT-WEIGHT: bold">Sent:</SPAN></B> Thursday, November 8,
2012 8:04 AM<BR><B><SPAN style="FONT-WEIGHT: bold">Subject:</SPAN></B> Newbie:
census_loader.sql Issue<BR></FONT></DIV><BR>
<DIV id=yiv1558466896>
<DIV>
<DIV
style="BACKGROUND-COLOR: #fff; FONT-FAMILY: times new roman, new york, times, serif; COLOR: #000; FONT-SIZE: 12pt"><SPAN
class=yiv1558466896tab> This is an update on my issues loading Tiger2011
data to postGIS using Debian's Wheezy, Postgresql 9.1. After starting over
from the beginning, I'm down to one error loading data using the included loader
scripts as-is. Table <A href="http://tiger.bg/" rel=nofollow
target=_blank>tiger.bg</A> doesn't get made on my system with no clue that bg
didn't get made unless I check my log file.<BR><BR><BR></SPAN>
<DIV>psql -d geocoder -f
/home/postgis/postgis/postgis-2.1.0SVN/extras/tiger_geocoder/tiger_2011/census_loader.sql
setsearchpathforinstall<BR>------------------------------------------<BR> tiger
has been made primary for install<BR>(1 row)<BR><BR>CREATE
FUNCTION<BR>psql:/home/postgis/postgis/postgis-2.1.0SVN/extras/tiger_geocoder/tiger_2011/census_loader.sql:108:
NOTICE: function loader_generate_census(text[],text) does not exist,
skipping<BR>DROP FUNCTION<BR>CREATE FUNCTION<BR>DELETE 3<BR>INSERT 0 1<BR>INSERT
0 1<BR>INSERT 0 1<BR></DIV>
<DIV
style="BACKGROUND-COLOR: transparent; FONT-STYLE: normal; FONT-FAMILY: times new roman, new york, times, serif; COLOR: rgb(0,0,0); FONT-SIZE: 16px"><BR></DIV>
<DIV
style="BACKGROUND-COLOR: transparent; FONT-STYLE: normal; FONT-FAMILY: times new roman, new york, times, serif; COLOR: rgb(0,0,0); FONT-SIZE: 16px">There's
no bg table made. If I change the stanza from CREATE TABLE bg to CREATE
TABLE tiger.bg, there's still nothing made and no error in postgresql
logs! If I take the bg table query out and put it in an editor without the
RETURN stanza, the table is made.<BR></DIV>
<DIV
style="BACKGROUND-COLOR: transparent; FONT-STYLE: normal; FONT-FAMILY: times new roman, new york,
times, serif; COLOR: rgb(0,0,0); FONT-SIZE: 16px"><BR></DIV>
<DIV
style="BACKGROUND-COLOR: transparent; FONT-STYLE: normal; FONT-FAMILY: times new roman, new york, times, serif; COLOR: rgb(0,0,0); FONT-SIZE: 16px">Here's
the error from the logs using CREATE TABLE bg<BR></DIV>
<DIV
style="BACKGROUND-COLOR: transparent; FONT-STYLE: normal; FONT-FAMILY: times new roman, new york, times, serif; COLOR: rgb(0,0,0); FONT-SIZE: 16px"><BR></DIV>
<DIV
style="BACKGROUND-COLOR: transparent; FONT-STYLE: normal; FONT-FAMILY: times new roman, new york, times, serif; COLOR: rgb(0,0,0); FONT-SIZE: 16px">2012-11-08
07:30:20 PST ERROR: syntax error at or near "RETURN" at character
806<BR>2012-11-08 07:30:20 PST STATEMENT: CREATE TABLE
bg<BR>
(<BR>
gid serial NOT
NULL,<BR>
statefp
varchar(2),<BR>
countyfp
varchar(3),<BR>
tractce
varchar(6),<BR>
blkgrpce
varchar(1),<BR>
bg_id varchar(12) PRIMARY
KEY,<BR>
namelsad
varchar(13),<BR>
mtfcc
varchar(5),<BR>
funcstat
varchar(1),<BR>
aland double
precision,<BR>
awater double
precision,<BR>
intptlat
varchar(11),<BR>
intptlon
varchar(12),<BR>
the_geom
geometry,<BR>
CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) =
2),<BR>
CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) =
'MULTIPOLYGON'::text OR the_geom IS
NULL),<BR>
CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) =
4269)<BR>
);<BR>
COMMENT ON TABLE tiger.bg IS 'block
groups';<BR>
RETURN 'Done creating census tract base tables - $Id: census_loader.sql 10179
2012-08-13 21:45:39Z robe $';</DIV>
<DIV
style="BACKGROUND-COLOR: transparent; FONT-STYLE: normal; FONT-FAMILY: times new roman, new york, times, serif; COLOR: rgb(0,0,0); FONT-SIZE: 16px"><BR></DIV>
<DIV
style="BACKGROUND-COLOR: transparent; FONT-STYLE: normal; FONT-FAMILY: times new roman, new york, times, serif; COLOR: rgb(0,0,0); FONT-SIZE: 16px"><BR><BR></DIV></DIV></DIV></DIV><BR><BR></DIV></DIV></DIV></DIV></DIV>
<META content=on
http-equiv=x-dns-prefetch-control><BR><BR></DIV></DIV></DIV></BODY></HTML>