[postgis-users] PostgreSQL + PostGIS 2.1.0 Tiger Geocoding notworking

[René Fournier]

Hi Regina,

Thanks for the quick the reply — and thanks for all your work on PostGIS, I use it every day in various applications and have come to really appreciate it. Thanks!

Regarding the loader script bug, is there a way I munge the generated bash script in order to make it work? From PostGIS 2.1.0, here's the problem area I think you describe:

  | ...
  | ${PSQL} -c "DROP SCHEMA IF EXISTS tiger_staging CASCADE;"
  | ${PSQL} -c "CREATE SCHEMA IF NOT EXISTS tiger_staging;"
  | ${PSQL} -c "CREATE SCHEMA IF NOT EXISTS tiger_data;"
  | ...

In my attempt, I had actually removed the IF NOT EXISTS, but still the result doesn't work.

${PSQL} -c "DROP SCHEMA IF EXISTS tiger_staging CASCADE;"
${PSQL} -c "CREATE SCHEMA tiger_staging;"
${PSQL} -c "CREATE SCHEMA tiger_data;"

When you say use 2.1.1 or 2.2.0dev, are you just referring to the generated loader script? I would compile 2.1.1 but I'm somewhat committed to MacPorts to keep my life sane, and the maintainer for PostGIS kindly revved the port to 2.1.0 per my request — I'd rather not bother him again if I can make 2.1.0 work.

On 2013-08-26, at 4:48 AM, Paragon Corporation <lr at pcorp.us> wrote:

> There was a bug in the 2.1.0 one that made it not work in  PostgresQL 9.2 and below since I accidentaly used 9.3 features.
>  
> http://trac.osgeo.org/postgis/ticket/2441 
>  
> and I describe a bit here http://www.postgresonline.com/journal/archives/317-CREATE-SCHEMA-IF-NOT-EXISTS-in-9.3-and-tiger-geocoder.html
>  
>  
> Sorry about that. Can you try the one packaged in the latest 2.1.1.  If you can't build on your own, its packaged in the latest windows 2.1.1dev (just copy the postgis_tiger_geocoder .* files in share/extension folder)
>  
>  http://postgis.net/windows_downloads (you might need to get rid of the windows line breaks)
>  
> If you want you can even use the 2.2.0dev one which does Tiger 2013 (data just came out a couple of days ago) instead of Tiger 2012.  I've been using it the 2013 for MA and so far so good but need to stress test some more.
>  
> Hope that helps,
> Regina
> http://www.postgis.us
> http://postgis.net
>  
>  
> 
> From: postgis-users-bounces at lists.osgeo.org [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Tom C
> Sent: Monday, August 26, 2013 1:58 AM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] PostgreSQL + PostGIS 2.1.0 Tiger Geocoding notworking
> 
> Did you add the fuzzy string match extension?
> 
> On Monday, August 26, 2013, René Fournier wrote:
> So… I've done a clean/fresh install of PostgreSQL 9.2 and PostGIS 2.1.0 via MacPorts (thanks Vincent). Installed the requisite extensions:
> 
> CREATE EXTENSION postgis;
> CREATE EXTENSION postgis_topology;
> CREATE EXTENSION postgis_tiger_geocoder;
> 
> Tested the basics:
> 
> gc5=# SELECT na.address, na.streetname,na.streettypeabbrev, na.zip
> gc5-# FROM normalize_address('1 Devonshire Place, Boston, MA 02109') AS na;
>  address | streetname | streettypeabbrev |  zip  
> ---------+------------+------------------+-------
>        1 | Devonshire | Pl               | 02109
> (1 row)
> 
> Then, to get the data (Tiger 2012), generated the loader script and ran it:
> 
> gc5=# SELECT loader_generate_script(ARRAY['MA','RI'], 'sh') AS result;
> 
> Bash script downloaded and added Massachusetts and Rhode Island without complaint, also ran:
> 
> SELECT install_missing_indexes();
> 
> But when I try to geocode or reverse geocode, I get nothing:
> 
> gc5=# 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;
>  rating | lon | lat | stno | street | styp | city | st | zip 
> --------+-----+-----+------+--------+------+------+----+-----
> 
> There's clearly a lot of data in my geocoder DB:
> 
>  gc5         | postgres | UTF8     | en_CA.UTF-8 | en_CA.UTF-8 |                       | 1194 MB | pg_default | 
> 
> But I can't get anything from it… My previous 1.5.x PostGIS tiger geocoder was working fine… I must be missing something obvious. Can anyone point it out for me?
> 
> …Rene
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users

Best regards,
René Fournier

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