I have a issue with simple PostGIS query.
Here is a query:
SELECT
id,
location,
ST_Distance_Sphere(location, ST_MakePoint(21.012228699999998, 52.2296756)) AS distance
FROM pins
WHERE
ST_Distance_Sphere(location, ST_MakePoint(21.012228699999998, 52.2296756)) > 6008000.1836031
ORDER BY distance ASC, id ASC
LIMIT 11;
And here is a result:
id | location | distance
-----+----------------------------------------------------+------------------
58 | 0101000020E6100000E253B4CB61C1E13FB7ED0CC45974EF3F | 6008000.1836031
162 | 0101000020E6100000DE133C027D95E03F3C15FD84A6BAEF3F | 6008157.46639754
186 | 0101000020E6100000D339D99403BFE33F541C1E242AD5EE3F | 6008257.4084038
55 | 0101000020E6100000B8042A79A500E63F775E82BA1908EE3F | 6008878.66960698
98 | 0101000020E6100000CD48F884CD1BED3FAD51BA15F017EC3F | 6008923.81556693
56 | 0101000020E6100000EECE76EEFE68E63F1681C811377DED3F | 6010325.52640522
195 | 0101000020E6100000EB87B04B4DC0E33F0AEAE22BECADED3F | 6012118.48976013
100 | 0101000020E6100000603039EEF27FD83FE70E98797AAEEF3F | 6012318.05377066
108 | 0101000020E610000045BA52BB3EA0E23F4B96A28081D5ED3F | 6012631.63019378
117 | 0101000020E61000003ABE6BC7FAD9D43F4D1CF4D130EEEF3F | 6013175.7742802
91 | 0101000020E610000093B62A082965ED3F911CF0DA8FBFEA3F | 6013178.70288832
For some reason row with id = 58 is returned and its distance is 6008000.1836031
despite I've used where filter ST_Distance_Sphere(location, ST_MakePoint(21.012228699999998, 52.2296756)) > 6008000.1836031
I guess Postgis calculates distance with greater precision?
How I can avoid this situation?
Best Answer
Fixed by converting distance to
numeric
and rounding:I've found it here http://postgis.net/docs/manual-1.4/ST_Distance_Sphere.html, under "Examples".