[postgis-users] Newbie: census_loader.sql Issue

Paragon Corporation lr at pcorp.us
Sat Nov 10 22:09:13 PST 2012

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


From: postgis-users-bounces at lists.osgeo.org
[mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Michael Papet
Sent: Friday, November 09, 2012 12:28 PM
To: postgis-users at lists.osgeo.org
Subject: Re: [postgis-users] Newbie: census_loader.sql Issue

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?



From: Michael Papet <mpapet at yahoo.com>
To: "postgis-users at lists.osgeo.org" <postgis-users at lists.osgeo.org> 
Sent: Thursday, November 8, 2012 8:04 AM
Subject: Newbie: census_loader.sql Issue

 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 <http://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
us_loader.sql                    setsearchpathforinstall
 tiger has been made primary for install
(1 row)

/census_loader.sql:108: NOTICE:  function
loader_generate_census(text[],text) does not exist, skipping

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) =
                  CONSTRAINT enforce_geotype_geom CHECK
(geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL),
                  CONSTRAINT enforce_srid_geom CHECK (st_srid(the_geom) =
                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 $';

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20121111/cd8172bf/attachment.html>

More information about the postgis-users mailing list