Barry, I don't have North Carolina data loaded in my db -- what happens if you run the below query the debug outputs -- does it error or give you results. Gives me nothing and reverse geocode just gives me North Carolina since I don't have NC state data loaded. If it does and returns results then I suspect the issue is it's trying to cast the address number part to a number,and its probably blank or not a number. Though I thought I had safeguards for that. Which postgis geocoder version are you working with, and which tiger data set. I had upgraded PostGIS 2.2 to use Tiger 2014 but hadn't finished doing testing on it. Thanks, Regina http://www.postgis.us http://postgis.net
_____ From: [email protected] [mailto:[email protected]] On Behalf Of Barry McCall Sent: Monday, October 06, 2014 6:03 PM To: [email protected] 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
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
