On 02/06/2013 11:50 PM, Ben Madin wrote:
> Thank you to all for your help on this problem. I've summarised the resolution in the hope that it might help someone
else.
>
> With all the advice I have gone forward and discovered that the issue related to a postcode anomaly. A client had
provideda new postbox postcode (the application normally prevents this for postboxes because we can't locate
properties,but because it was new - and our database didn't have a record of it - this check had been bypassed). This
meantthere was no geometry associated with the postcode, and when it was joined to the postcodes table (which has
varcharsfor postcodes because in Australia some postcodes begin with 0, which needs to be printed to allow automatic
sorting)during the distance checking function (which looked like this in pl/pgsql):
>
> round(st_distance_sphere( '$$ || pccentre || $$', post.the_point)/1000)
>
> If a geometry is NULL, the st_distance_sphere postgis function returned NULL.
>
> NULL/1000 = NULL
>
> round(NULL) = NULL
>
> AND NULL < 150 = NULL
>
> so the predicate probably looks like:
>
> AND round(NULL/1000) < 150
>
> AND NULL, so no row returned.
>
> This can't be used in a comparison, so to get around this (thanks Tom) :
>
> coalesce(round(st_distance_sphere( '$$ || pccentre || $$', post.the_point)/1000),0) < $$ || quote_literal(distance);
>
> which works - problem no longer being seen.
>
> My final throught relates to the message:
>
> ERROR: invalid input syntax for integer: ''
>
> The '' suggests (I don't think I was the only one who thought this) that we were looking for a string comparison. I
guessthe NULL value is in there between the quotes.
I tend to doubt that. For one NULL is a valid input for an integer and
two a NULL would not have quotes.
Going back over your original query I found this discrepancy, not sure
if it applies:
In the SELECT list you have:
round(st_distance_sphere('0101000020BB1000008716D9CEF7A36240643BDF4F8DA741C0',
post.the_point::geometry)/1000)
in the AND clause:
round(st_distance_sphere(
'0101000020BB1000008716D9CEF7A36240643BDF4F8DA741C0', post.the_point)/1000)
Note the cast to geometry in the first but not the second call to
st_distance_sphere.
>
> cheers
>
> Ben
>
>
>
>
>
>
>
>
>
--
Adrian Klaver
adrian.klaver@gmail.com
