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 provided a 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 meant there was no geometry associated with the postcode, and when it was joined to the postcodes table (which has varchars for 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 guess the 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@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
