[postgis-devel] Tiger 2010 Geocoder Divide By Zero Error

[Emily Gouge]

Thanks for the explanation and fixing it up quickly.

Emily

On 6/17/2011 8:15 PM, Paragon Corporation wrote:
> Emily,
>
> Good catch.  I really should have revised the least_hn etc to handle
> decimals instead of rejecting them.  I had run into a situation where tiger
> data had a goofy
> Address number of 1.0.0.0 so was breaking the index I was building   So I
> had decided to just reject these things because I wasn't clear what a
> decimal address would mean anyway.  But I forgot
> About that other piece of code that was making everything decimals.
>
> Regarding why the .0 is there  -- it's to make it a numeric instead of an
> integer (since parsed.address always returns an integer).
>
> Unfortunately because least_hn/greatest_hn always return back integers that
> hack wouldn't work anyway.
>
> The code was right when it was there before I converted everything to using
> least_hn/greatest_hn so I accidentally changed the meaning of it.
>
>
> The reason why you want to divide by decimals in SQL is because
>
> SELECT 1/2  =>  0
>
> But
>
> SELECT 1.0/2 =>  0.5
>
>
> Anyrate I've put this in as a bug and will fix in a bit.
> http://trac.osgeo.org/postgis/ticket/1035
>
> Thanks,
> Regina
>
>
> -----Original Message-----
> From: postgis-devel-bounces at postgis.refractions.net
> [mailto:postgis-devel-bounces at postgis.refractions.net] On Behalf Of Emily
> Gouge
> Sent: Friday, June 17, 2011 7:06 PM
> To: postgis-devel at postgis.refractions.net
> Subject: [postgis-devel] Tiger 2010 Geocoder Divide By Zero Error
>
> I have recently been doing some geocoding testing using the Postgis geocoder
> from 2.0.0 branch.  I started getting divide by zero errors today and was
> looking through the source code at the geocode_address function.  After
> figuring out the source of the error I looked in SVN and saw there was a fix
> committed about a week ago (thanks!) but I'm still somewhat confused about
> this part of the code.
>
> The original divide by 0 error is caused by the following partial statement:
>
>    ((1.0 - ' || '(least_hn(' || coalesce(quote_literal(parsed.address ||
> '.0'),'NULL') || ',least_hn(b.fromhn,b.tohn)::text) /' || '
> greatest_hn(' || coalesce(quote_literal(parsed.address || '.0'),'NULL')
> || ',greatest_hn(b.fromhn,b.tohn)::text))' || ') * 5)::integer + 5'
>
> My question is what is the purpose of the '.0' concatenation?
>
> I looked at the greatest_hn function and if any of it's parameters
> contain characters that are not digits then it returns 0.  So by
> concatenating the '.0' I believe you are guaranteeing the first argument
> is going to be treated as 0?  So either the '.0' should not be there or
> the statement can be simplified.  If I'm missing something please
> correct me.
>
> Thanks,
> Emily
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel
>
>
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel