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
