[postgis-users] reverse_geocode() specific error at specificgeom point
Barry McCall
barry.mccall+postgis at gmail.com
Tue Oct 7 07:23:10 PDT 2014
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 <
barry.mccall+postgis at gmail.com> 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 <barry.mccall at gmail.com>
> wrote:
>
>> Check out this lil guy :-)
>>
>>
>> On Tue, Oct 7, 2014 at 9:57 AM, Paragon Corporation <lr at pcorp.us> 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> 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
>>>>
>>>
>>>
>>> _______________________________________________
>>> 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/3bffeb9c/attachment.html>
More information about the postgis-users
mailing list