1

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?

0

Fixed by converting distance to numeric and rounding:

round(ST_Distance_Sphere(location, ST_MakePoint(21.012228699999998, 52.2296756))::numeric, 2)

I've found it here http://postgis.net/docs/manual-1.4/ST_Distance_Sphere.html, under "Examples".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.