[postgis-users] Newbie: census_loader.sql Issue

[Michael Papet]

 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 tiger.bg doesn't get made on my system with no clue that bg didn't get made unless I check my log file.

psql -d geocoder  -f /home/postgis/postgis/postgis-2.1.0SVN/extras/tiger_geocoder/tiger_2011/census_loader.sql                    setsearchpathforinstall
------------------------------------------
 tiger has been made primary for install
(1 row)

CREATE FUNCTION
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
DROP FUNCTION
CREATE FUNCTION
DELETE 3
INSERT 0 1
INSERT 0 1
INSERT 0 1

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.

Here's the error from the logs using CREATE TABLE bg

2012-11-08 07:30:20 PST ERROR:  syntax error at or near "RETURN" at character 806
2012-11-08 07:30:20 PST STATEMENT:  CREATE TABLE bg
                (
                  gid serial NOT NULL,
                  statefp varchar(2),
                  countyfp varchar(3),
                  tractce varchar(6),
                  blkgrpce varchar(1),
                  bg_id varchar(12) PRIMARY KEY,
                  namelsad varchar(13),
                  mtfcc varchar(5),
                  funcstat varchar(1),
                  aland double precision,
                  awater double precision,
                  intptlat varchar(11),
                  intptlon varchar(12),
                  the_geom geometry,
                  CONSTRAINT enforce_dims_geom CHECK (st_ndims(the_geom) = 2),
                  CONSTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
                  CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) = 4269)
                );
                COMMENT ON TABLE tiger.bg IS 'block groups';
                RETURN 'Done creating census tract base tables - $Id: census_loader.sql 10179 2012-08-13 21:45:39Z robe $';

[Michael Papet]

I discovered another table not made tiger.tract.  Again, no error.  It just doesn't get made.

I used the included script to load Massachusetts data to use the documentation examples and am having more issues.

#1: Select Function pprint_addy example does not work.  Function pprint_addy is present as pprint_addy(tiger.norm_addy).   I can run SELECT normalize_address('202 East Fremont Street, Las Vegas, Nevada 89101')) As pretty_address; and it returns a formatted address.

 SELECT pprint_addy(normalize_address('202 East Fremont Street, Las Vegas, Nevada 89101')) As pretty_address;
2012-11-09 08:36:50 PST ERROR:  function pprint_addy(norm_addy) does not exist at character 8
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.

#2 the geocode example returns nothing.  No error.  Just no results.

SELECT g.rating, ST_X(g.geomout) As lon, ST_Y(g.geomout) As lat,
    (addy).address As stno, (addy).streetname As street,
    (addy).streettypeabbrev As styp, (addy).location As city, (addy).stateabbrev As st,(addy).zip
    FROM geocode('75 State Street, Boston MA 02109') As g;

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?

Michael

---

From: Michael Papet <mpa...@yahoo.com>
To: "postgi...@lists.osgeo.org" <postgi...@lists.osgeo.org>
Sent: Thursday, November 8, 2012 8:04 AM
Subject: Newbie: census_loader.sql Issue

[Michael Papet]

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.

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. 

Attempts to use the geocode example from here http://postgis.refractions.net/docs/Geocode.html with Tiger2011 data return nothing.  No errors.

---

From: Michael Papet <mpa...@yahoo.com>
To: "postgi...@lists.osgeo.org" <postgi...@lists.osgeo.org>

Sent: Friday, November 9, 2012 9:28 AM
Subject: Re: Newbie: census_loader.sql Issue

[Paragon Corporation]

Mike,

 

Hard to tell what the issue is here.  So here are a couple of comments reading from your prior posts

 

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.

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.

 

So verify you don't have any tables in your public schema with same name as those in tiger schema.

 

3) Your search_path issue.  How exactly did you set your search path?

here are a couple of mistakes we've seen

 

a) Not setting the search path of the database

Did you use the remarked out line in create_geocoder

 

ALTER DATABASE geocder SET search_path=public, tiger;

 

If you simply use:

SET search_path=public,tiger;

 

That only lasts for the life of the session, but is a good way to verify you have things at least installed correctly.

 

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

 

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

for the individual user (at the server level) and even for specific user at the database level.

 

Youc an do something as crazy as this:

ALTER ROLE postgres IN DATABASE geocoder SET search_path = "$user", public;

 

and if you have such as setting, your ALTER DATABASE .. SET search_path will be powerless when the postgres user logs in.

 

You can also set search path at the server level for a user

 

Anyway check if yo uhave any of these issues.

 

Hope that helps,

Regina

http://www.postgis.us

 

 

 

---

From: postgis-us...@lists.osgeo.org [mailto:postgis-us...@lists.osgeo.org] On Behalf Of Michael Papet
Sent: Friday, November 09, 2012 12:46 PM
To: postgi...@lists.osgeo.org
Subject: [postgis-users] Newbie: search_path variable

[Paragon Corporation]

Strange. Are you using tiger packaged with 2.0 or 2.1?  I now 2.0.1 had issues iwth tract/bg load which are fixed in 2.1 and I think partly fixed in 2.0.2

 

Thanks,

Regina

---

From: postgis-us...@lists.osgeo.org [mailto:postgis-us...@lists.osgeo.org] On Behalf Of Michael Papet

Sent: Friday, November 09, 2012 12:28 PM
To: postgi...@lists.osgeo.org
Subject: Re: [postgis-users] Newbie: census_loader.sql Issue