[postgis-users] reverse_geocode() specific error at specificgeom point
Barry McCall
barry.mccall+postgis at gmail.com
Tue Oct 7 07:07:39 PDT 2014
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/bfaac040/attachment.html>
More information about the postgis-users
mailing list