[postgis-tickets] [PostGIS] #2958: reverse_geocode erroring ERROR: invalid input syntax for type numeric: " "

[PostGIS]

#2958: reverse_geocode erroring ERROR:  invalid input syntax for type numeric: " "
----------------------------+-----------------------------------------------
 Reporter:  robe            |       Owner:  robe         
     Type:  defect          |      Status:  new          
 Priority:  high            |   Milestone:  PostGIS 2.1.5
Component:  tiger geocoder  |     Version:  2.1.x        
 Keywords:                  |  
----------------------------+-----------------------------------------------
 This seems to happen with a particular North Carolina area.  haven't been
 able to replicate with other data I have.

 http://lists.osgeo.org/pipermail/postgis-users/2014-October/039645.html

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

 the debug query when you turn on debugging also errors with same notice:

 {{{
  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
  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
 }}}

 of course only happens if you have North Carolina data loaded.

-- 
Ticket URL: <http://trac.osgeo.org/postgis/ticket/2958>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.