[postgis-users] reverse_geocode() specific error at specificgeom point

[Stephen Woodbridge]

cousub in the north east and other areas is also the town name. It 
varies across the country.

-Steve

On 10/7/2014 9:57 AM, Paragon Corporation 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:* postgis-users-bounces at lists.osgeo.org
> [mailto:postgis-users-bounces at lists.osgeo.org] *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 <lr at pcorp.us
> <mailto:lr at pcorp.us>> 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:* postgis-users-bounces at lists.osgeo.org
>     <mailto:postgis-users-bounces at lists.osgeo.org>
>     [mailto:postgis-users-bounces at lists.osgeo.org
>     <mailto:postgis-users-bounces at lists.osgeo.org>] *On Behalf Of *Barry
>     McCall
>     *Sent:* Monday, October 06, 2014 6:03 PM
>     *To:* postgis-users at lists.osgeo.org
>     <mailto:postgis-users at lists.osgeo.org>
>     *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
>     postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
>     http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>