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

[Barry McCall]

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> 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] *On Behalf Of *Barry McCall
> *Sent:* Monday, October 06, 2014 6:03 PM
> *To:* 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
>  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
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20141007/198f6d24/attachment.html>