[postgis-users] reverse_geocode() specific error at specific geom point

[Paragon Corporation]

I was able to replicate the error by loading NC data.
 
The problem is with the NULL address range records (coming from those
unnamed streets) I think.  I'll try to have a fix later today.
 
If you take change
 
to_number(fromhn, '999999') As fromhn, to_number(tohn, '999999') As tohn
 
to
 
fromhn, tohn
 
in the debug query 
you should see records come back
 

  _____  

From: postgis-users-bounces at lists.osgeo.org
[mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Barry McCall
Sent: Monday, October 06, 2014 6:03 PM
To: postgis-users at lists.osgeo.org
Subject: [postgis-users] reverse_geocode() specific error at specific geom
point


For some reason when I reverse_geocode geometry point
0101000020AD10000095D4096822CC53C08A1F63EE5AAA4140
it returns error 
ERROR:  invalid input syntax for type numeric: " "
********** Error **********

ERROR: invalid input syntax for type numeric: " "
SQL state: 22P02

I performed UPDATE tiger.geocode_settings SET setting = 'true' WHERE name =
'debug_reverse_geocode'; to try to get a grasp of what was going on;
however, there are so many CTEs in the debug I cant get a grasp of what is
really happening. 

Does anyone familiar with this system know why this is throwing this error?
It seems to only happen with lon,lat around -79.1866 35.3309. 


SELECT r.addy[1] FROM reverse_geocode(ST_GeomFromText('POINT(-79.1866
35.3309)',4269),true) AS r



NOTICE:  Statement 1: 
    WITH ref AS (
        SELECT
'0101000020AD10000095D4096822CC53C08A1F63EE5AAA4140'::geometry As ref_geom )
, 
f AS 
( SELECT faces.* FROM faces  CROSS JOIN ref
WHERE faces.statefp = '37' AND faces.countyfp = '105' 
AND ST_Intersects(faces.the_geom, ref_geom)
    ),
e AS 
( SELECT edges.tlid , edges.statefp, edges.the_geom, CASE WHEN edges.tfidr =
f.tfid THEN 'R' WHEN edges.tfidl = f.tfid THEN 'L' ELSE NULL END::varchar As
eside,
                    ST_ClosestPoint(edges.the_geom,ref_geom) As center_pt,
ref_geom
FROM edges INNER JOIN f ON (f.statefp = edges.statefp AND (edges.tfidr =
f.tfid OR edges.tfidl = f.tfid)) 
    CROSS JOIN ref
WHERE edges.statefp = '37' AND edges.countyfp = '105' 
AND ST_DWithin(edges.the_geom, ref.ref_geom, 0.01) AND (edges.mtfcc LIKE
'S%') --only consider streets and roads
  ) ,
ea AS 
(SELECT e.statefp, e.tlid, a.fromhn, a.tohn, e.center_pt, ref_geom, a.zip,
a.side, e.the_geom
FROM e LEFT JOIN addr As a ON (a.statefp = '37'  AND e.tlid = a.tlid and
e.eside = a.side) 
)
SELECT * 
FROM (SELECT DISTINCT ON(tlid,side)  foo.fullname, foo.streetname,
foo.streettypeabbrev, foo.zip,  foo.center_pt,
  side, to_number(fromhn, '999999') As fromhn, to_number(tohn, '999999') As
tohn, ST_GeometryN(ST_Multi(line),1) As line, 
   dist
FROM 
  (SELECT e.tlid, e.the_geom As line, n.fullname, COALESCE(n.prequalabr || '
','')  || n.name <http://n.name/>  
 AS streetname, n.predirabrv, COALESCE(suftypabrv, pretypabrv) As
streettypeabbrev,
      n.sufdirabrv, e.zip, e.side, e.fromhn, e.tohn , e.center_pt,
 
ST_Distance_Sphere(ST_SetSRID(e.center_pt,4326),ST_SetSRID(ref_geom,4326))
As dist
FROM ea AS e 
LEFT JOIN (SELECT featnames.* FROM featnames 
    WHERE featnames.statefp = '37'   ) AS n ON (n.statefp =  e.statefp AND
n.tlid = e.tlid) 
ORDER BY dist LIMIT 50 ) As foo 
ORDER BY foo.tlid, foo.side,  CASE 0 WHEN 0 THEN 0  WHEN 1 THEN CASE WHEN
foo.fullname ~ '[0-9]+' THEN 0 ELSE 1 END ELSE CASE WHEN foo.fullname > ''
AND NOT (foo.fullname ~ '[0-9]+') THEN 0 ELSE 1 END END ,  foo.fullname ASC
NULLS LAST, dist LIMIT 50) As f ORDER BY f.dist, CASE WHEN fullname > ''
THEN 0 ELSE 1 END
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20141007/1904e7ae/attachment.html>