<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=us-ascii" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 10.00.9200.17088"></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=329311711-07102014><FONT color=#0000ff
size=2 face=Arial>I was able to replicate the error by loading NC
data.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=329311711-07102014><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=329311711-07102014><FONT color=#0000ff
size=2 face=Arial>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.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=329311711-07102014><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=329311711-07102014><FONT color=#0000ff
size=2 face=Arial>If you take change</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV>to_number(fromhn, '999999') As fromhn, to_number(tohn, '999999') As
tohn</DIV>
<DIV> </DIV>
<DIV><SPAN class=329311711-07102014></SPAN><FONT color=#0000ff size=2
face=Arial><SPAN class=329311711-07102014>to</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff size=2 face=Arial><SPAN
class=329311711-07102014></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff size=2 face=Arial><SPAN
class=329311711-07102014>fromhn, tohn</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff size=2 face=Arial><SPAN
class=329311711-07102014></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff size=2 face=Arial><SPAN class=329311711-07102014>in the
debug query </SPAN></FONT></DIV>
<DIV><FONT color=#0000ff size=2 face=Arial><SPAN class=329311711-07102014>you
should see records come back</SPAN></FONT></DIV>
<DIV><FONT face=Arial><FONT color=#0000ff><FONT size=2><SPAN
class=329311711-07102014></SPAN></FONT></FONT></FONT> </DIV>
<DIV><BR></DIV>
<DIV lang=en-us class=OutlookMessageHeader dir=ltr align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B> postgis-users-bounces@lists.osgeo.org
[mailto:postgis-users-bounces@lists.osgeo.org] <B>On Behalf Of </B>Barry
McCall<BR><B>Sent:</B> Monday, October 06, 2014 6:03 PM<BR><B>To:</B>
postgis-users@lists.osgeo.org<BR><B>Subject:</B> [postgis-users]
reverse_geocode() specific error at specific geom point<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV dir=ltr>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif">For some reason when
I reverse_geocode geometry point
0101000020AD10000095D4096822CC53C08A1F63EE5AAA4140</DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif">it returns
error </DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif">
<DIV>ERROR: invalid input syntax for type numeric: " "</DIV>
<DIV>********** Error **********</DIV>
<DIV><BR></DIV>
<DIV>ERROR: invalid input syntax for type numeric: " "</DIV>
<DIV>SQL state: 22P02</DIV></DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><BR></DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif">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. </DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><BR></DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif">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. </DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><BR></DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><BR></DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif">SELECT r.addy[1]
FROM reverse_geocode(ST_GeomFromText('POINT(-79.1866 35.3309)',4269),true) AS
r<BR></DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><BR></DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><BR></DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif">NOTICE:
Statement 1: </DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN> WITH ref AS (</DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN> SELECT
'0101000020AD10000095D4096822CC53C08A1F63EE5AAA4140'::geometry As ref_geom )
, </DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>f AS </DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>( SELECT faces.* FROM faces CROSS
JOIN ref</DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>WHERE faces.statefp = '37' AND
faces.countyfp = '105' </DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>AND ST_Intersects(faces.the_geom,
ref_geom)</DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN> ),</DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>e AS </DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>( 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,</DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif">
ST_ClosestPoint(edges.the_geom,ref_geom) As center_pt, ref_geom</DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>FROM edges INNER JOIN f ON (f.statefp =
edges.statefp AND (edges.tfidr = f.tfid OR edges.tfidl = f.tfid)) </DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN> CROSS JOIN ref</DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>WHERE edges.statefp = '37' AND
edges.countyfp = '105' </DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>AND ST_DWithin(edges.the_geom,
ref.ref_geom, 0.01) AND (edges.mtfcc LIKE 'S%') --only consider streets and
roads</DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN> )<SPAN
style="WHITE-SPACE: pre-wrap"> </SPAN>,</DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>ea AS </DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>(SELECT e.statefp, e.tlid, a.fromhn,
a.tohn, e.center_pt, ref_geom, a.zip, a.side, e.the_geom</DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>FROM e LEFT JOIN addr As a ON (a.statefp =
'37' AND e.tlid = a.tlid and e.eside = a.side) </DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>)</DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>SELECT * </DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>FROM (SELECT DISTINCT ON(tlid,side)
foo.fullname, foo.streetname, foo.streettypeabbrev, foo.zip,
foo.center_pt,</DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN> side, to_number(fromhn,
'999999') As fromhn, to_number(tohn, '999999') As tohn,
ST_GeometryN(ST_Multi(line),1) As line, </DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN> dist</DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>FROM </DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN> (SELECT e.tlid, e.the_geom As
line, n.fullname, COALESCE(n.prequalabr || ' ','') || <A
href="http://n.name/" target=_blank>n.name</A>
<DIV style="HEIGHT: 16px; DISPLAY: inline-block; WIDTH: 16px"></DIV> AS
streetname, n.predirabrv, COALESCE(suftypabrv, pretypabrv) As
streettypeabbrev,</DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN> n.sufdirabrv,
e.zip, e.side, e.fromhn, e.tohn , e.center_pt,</DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>
ST_Distance_Sphere(ST_SetSRID(e.center_pt,4326),ST_SetSRID(ref_geom,4326))
As dist</DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>FROM ea AS e </DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>LEFT JOIN (SELECT featnames.* FROM
featnames </DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN> WHERE featnames.statefp
= '37' ) AS n ON (n.statefp = e.statefp AND n.tlid =
e.tlid) </DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>ORDER BY dist LIMIT 50 ) As foo </DIV>
<DIV style="FONT-SIZE: 13px; FONT-FAMILY: arial,sans-serif"><SPAN
style="WHITE-SPACE: pre-wrap"></SPAN>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</DIV></DIV></BODY></HTML>