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
