[postgis-users] Tiger geocoder - error instead of null when unlocatable

Mon Feb 8 11:10:11 PST 2016

Bug has been created, #3451<https://trac.osgeo.org/postgis/ticket/3451>.

Thanks again.

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Regina Obe
Sent: Monday, February 08, 2016 11:18 AM
To: 'PostGIS Users Discussion'
Subject: Re: [postgis-users] Tiger geocoder - error instead of null when unlocatable

Matthew,

Now that you mention it I have run into the same issue myself especially since county is such a common table name.

Go ahead and enter a ticket to request schema qualifying.  It has been something I've been thinking of doing anyway.

Thanks,
Regina

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Matthew Syphus
Sent: Monday, February 08, 2016 1:09 PM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org<mailto:postgis-users at lists.osgeo.org>>
Subject: Re: [postgis-users] Tiger geocoder - error instead of null when unlocatable

Regina,
Thank you for looking into this.  Before entering a bug I did some other checks and believe I've found the cause.

The function in question creates a query without schema-qualified table names.
ERROR:  column co.statefp does not exist
LINE 2: ...cefp = p.placefp)  LEFT JOIN county co ON ('25' = co.statefp...
                                                             ^
CONTEXT:  PL/pgSQL function geocode_address(norm_addy,integer,geometry) line 383 at FOR over EXECUTE statement
PL/pgSQL function geocode(norm_addy,integer,geometry) line 14 at FOR over SELECT rows
PL/pgSQL function geocode(character varying,integer,geometry) line 26 at RETURN QUERY

I have a county table in another schema (also in the search path).  After renaming that other table it appeared to work.  In fact, it returned locations (not just null) where it errored out before, meaning it isn't just unlocatable addresses; it is affecting all output. Also, it looks like the table names in the tiger and tiger_data schemas are not obscure (a good thing) and therefore may be in use somewhere else in a database (a bad thing).

Can the extension be updated so the generated scripts and all functions always use schema-qualified table names?  I realize that might be a tall order, but it seems very easy for table names to collide within the search path.  Should I still enter this as a bug?

Thanks for the excellent work on geocoding (and PostGIS); the address standardizing, data import, and geocode results are impressive.

Matthew

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Regina Obe
Sent: Sunday, February 07, 2016 8:45 AM
To: 'PostGIS Users Discussion'
Subject: Re: [postgis-users] Tiger geocoder - error instead of null when unlocatable

Matthew,

Can you ticket this one in our ticket tracker - http://postgis.net/support/

Unfortunately I don't have Idaho data loaded in my instance and can't trigger the problem with the data I have just by replacing the state.

It does sound like a bug though if other addresses work.  I'll try to take a look at it next week while I'm looking at other tiger issues.

Thanks,
Regina
http://www.postgis.us
http://postgis.net

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Matthew Syphus
Sent: Sunday, February 07, 2016 12:43 AM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org<mailto:postgis-users at lists.osgeo.org>>
Subject: Re: [postgis-users] Tiger geocoder - error instead of null when unlocatable

Apologies for not including version details originally.

"postgis_full_version"
"POSTGIS="2.2.1 r14555" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.1, released 2013/08/26" LIBXML="2.7.6" LIBJSON="0.11" TOPOLOGY RASTER"

PostgreSQL 9.5.0
CentOS 6.5

mst

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Matthew Syphus
Sent: Saturday, February 06, 2016 9:06 PM
To: postgis-users at lists.osgeo.org<mailto:postgis-users at lists.osgeo.org>
Subject: [postgis-users] Tiger geocoder - error instead of null when unlocatable

After following the instructions (several times) and apparently successfully installing tiger geocoder and the tiger data for Idaho, I keep getting an error when the geocoder can't find an address.

With a locatable address it works just fine.
SELECT * FROM geocode('3300 state st, boise, id 83703')

When an address is not locatable, I assume it should return null (and move on to the next in the batch).
Instead, it returns an error:

SELECT * FROM geocode('3300 state st, neverland, id 9876543210')

---------------------------------------

ERROR:  column co.statefp does not exist
LINE 2: ...cefp = p.placefp)  LEFT JOIN county co ON ('16' = co.statefp...
                                                             ^
QUERY:  SELECT DISTINCT ON (sub.predirabrv,sub.fename,COALESCE(sub.suftypabrv, sub.pretypabrv) ,sub.sufdirabrv,coalesce(p.name,zip.city,cs.name,co.name),s.stusps,sub.zip)    sub.predirabrv   as fedirp,    sub.fename,    COALESCE(sub.suftypabrv, sub.pretypabrv)   as fetype,    sub.sufdirabrv   as fedirs,    coalesce(p.name,zip.city,cs.name,co.name)::varchar as place,    s.stusps as state,    sub.zip as zip,    interpolate_from_address($1, sub.fromhn,    ...

It doesn't seem to matter which address element causes the problem, the error is the same and it kills the query.  Is there some error handling that is getting missed? The examples I've seen look like it just returns null.  Thank you for any help.

mst

________________________________

This email has been scanned for spam and viruses by Proofpoint Essentials cloud email security - click here<https://us1.proofpointessentials.com/index01.php?mod_id=11&mod_option=logitem&mail_id=Z07c%2BrvT0fHg&rid=6780640&report=1> to report this email as spam.

________________________________

This email has been scanned for spam and viruses by Proofpoint Essentials cloud email security - click here<https://us1.proofpointessentials.com/index01.php?mod_id=11&mod_option=logitem&mail_id=fwStnh5A4b6W&rid=6780640&report=1> to report this email as spam.

________________________________

This email has been scanned for spam and viruses by Proofpoint Essentials cloud email security - click here<https://us1.proofpointessentials.com/index01.php?mod_id=11&mod_option=logitem&mail_id=jhne-RR8qQM1&rid=6780640&report=1> to report this email as spam.

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