Thanks so much. I just updated it. Tested a few of the points that were returning that error. We were actually working on cleaning it but you had finished first :-)
Thanks again. On Tue, Oct 7, 2014 at 12:10 PM, Paragon Corporation <[email protected]> wrote: > Barry, > > That's pretty much what I changed in the reverse_geocode function to > ignore non-numeric ranges and just treat them as NULL. > > Did you have trouble installing the > revised function noted in > http://trac.osgeo.org/postgis/ticket/2958 (want to make sure if you are > just installing the function, to put a tiger.reverse_geocode so it doesn't > get installed in your default schema) > > Hope that helps, > Regina > > > > ------------------------------ > *From:* [email protected] [mailto: > [email protected]] *On Behalf Of *Barry McCall > *Sent:* Tuesday, October 07, 2014 10:23 AM > > *To:* PostGIS Users Discussion > *Subject:* Re: [postgis-users] reverse_geocode() specific error > atspecificgeom point > > I'm seeing with more clarity now. > After running > > SELECT * FROM addr > WHERE fromhn ~* '[a-zA-Z]' > AND tohn ~* '[a-zA-Z]' > > it returns 381,739 results with chars in fromhn and tohn. Your message > came in where you stated > 'It was caused by a record not being emitted but that gets the function > applied.' > > Being new and still learning the system I'm assuming there is a function > that should be omitting any non integer/numerical values from these columns? > > On Tue, Oct 7, 2014 at 10:07 AM, Barry McCall < > [email protected]> wrote: > >> Check out this lil guy right here :-) >> >> SELECT * FROM tiger_data.nc_addr >> WHERE tlid = '72088154' >> >> >> >> On Tue, Oct 7, 2014 at 10:06 AM, Barry McCall <[email protected]> >> wrote: >> >>> Check out this lil guy :-) >>> >>> >>> On Tue, Oct 7, 2014 at 9:57 AM, Paragon Corporation <[email protected]> wrote: >>> >>>> Barry, >>>> It was caused by a record not being emitted but that gets the function >>>> applied. >>>> >>>> I put in a patch for this issue for 2.1 and 2.2 branch. You just have >>>> to replace your reverse_geocode function with the one referenced in this >>>> ticket >>>> >>>> http://trac.osgeo.org/postgis/ticket/2958 >>>> >>>> On a slgihtly related note, I noticed this address geocodes to >>>> 1,Greenwood for location instead of just Greenwood. >>>> >>>> That is caused because the tiger_data.nc_cousub table has a bunch of 1s >>>> etc to denote township number I guess. Not sure why they did that. >>>> >>>> if you want to get rid of those numbers, update the name field in >>>> tiger_data.nc_cousub. >>>> >>>> I forget why I use cousub instead of something else. >>>> >>>> Hope that helps, >>>> Regina >>>> http://www.postgis.us >>>> http://postgis.net >>>> >>>> >>>> >>>> >>>> >>>> ------------------------------ >>>> *From:* [email protected] [mailto: >>>> [email protected]] *On Behalf Of *Barry McCall >>>> *Sent:* Tuesday, October 07, 2014 8:17 AM >>>> *To:* PostGIS Users Discussion >>>> *Subject:* Re: [postgis-users] reverse_geocode() specific error at >>>> specificgeom point >>>> >>>> The change from >>>> >>>> to_number(fromhn, '999999') As fromhn, to_number(tohn, '999999') As tohn >>>> >>>> to >>>> >>>> fromhn, tohn >>>> >>>> does work when I change it in the debug code. When I update the >>>> function it spits out the error below: >>>> >>>> ERROR: operator does not exist: character varying - character varying >>>> LINE 1: ...ate_Point(var_redge.line, var_pt)*(var_redge.tohn - >>>> var_redg... >>>> ^ >>>> HINT: No operator matches the given name and argument type(s). You >>>> might need to add explicit type casts. >>>> QUERY: SELECT (var_redge.fromhn + ST_Line_Locate_Point(var_redge.line, >>>> var_pt)*(var_redge.tohn - var_redge.fromhn))::numeric(10) >>>> CONTEXT: PL/pgSQL function reverse_geocode(geometry,boolean) line 157 >>>> at assignment >>>> ********** Error ********** >>>> >>>> ERROR: operator does not exist: character varying - character varying >>>> SQL state: 42883 >>>> Hint: No operator matches the given name and argument type(s). You >>>> might need to add explicit type casts. >>>> Context: PL/pgSQL function reverse_geocode(geometry,boolean) line 157 >>>> at assignment >>>> >>>> On Tue, Oct 7, 2014 at 7:24 AM, Paragon Corporation <[email protected]> >>>> wrote: >>>> >>>>> 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:* [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 >>>>> 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 >>>>> >>>> >>>> >>>> _______________________________________________ >>>> postgis-users mailing list >>>> [email protected] >>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >>>> >>> >>> >> > > _______________________________________________ > postgis-users mailing list > [email protected] > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
