[postgis-users] reverse_geocode() specific error atspecificgeom point
Barry McCall
barry.mccall+postgis at gmail.com
Tue Oct 7 10:13:15 PDT 2014
Thanks so much. I just updated it. Tested a few of the points that were
returning that error.
We were actually working on cleaning it but you had finished first :-)
Thanks again.
On Tue, Oct 7, 2014 at 12:10 PM, Paragon Corporation <lr at pcorp.us> wrote:
> Barry,
>
> That's pretty much what I changed in the reverse_geocode function to
> ignore non-numeric ranges and just treat them as NULL.
>
> Did you have trouble installing the
> revised function noted in
> http://trac.osgeo.org/postgis/ticket/2958 (want to make sure if you are
> just installing the function, to put a tiger.reverse_geocode so it doesn't
> get installed in your default schema)
>
> Hope that helps,
> Regina
>
>
>
> ------------------------------
> *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 10:23 AM
>
> *To:* PostGIS Users Discussion
> *Subject:* Re: [postgis-users] reverse_geocode() specific error
> atspecificgeom point
>
> 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
>>>>
>>>
>>>
>>
>
> _______________________________________________
> 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/27d0c72a/attachment.html>
More information about the postgis-users
mailing list