0

I'm using ST_SnapToGrid: ST_SnapToGrid(somePoint, 0.0000001) and I get strange result on many points, such as:

- POINT (21.619820510769063 41.94186153740355) -> POINT (21.6198205 41.941861499999995)  
- POINT (22.49594094391644 41.20357506925623) -> POINT (22.495940899999997 41.203575099999995)  

What am I doing wrong?

  • Expecting every floating-point value to have an evenly divisible mantissa? There are just some values which can't be manifested by IEEE representation. It would be nice if ST_AsText had an optional precision operand, but it doesn't. – Vince May 6 at 11:07
  • Try the floating point converter in h-schmidt.net/FloatConverter/IEEE754.html with decimal value 0.0000001 and you will see gets changed into 1.0000000116860974230803549289703369140625E-7. – user30184 May 6 at 11:13
  • 1
  • Yes, I know about floating point imprecision problem. I thought (or wrongly assumed) that it will try to round the number in such cases. So I think what I want to do is to round all geometries to given precision in addition to SnapToGrid. Or maybe not even use it, only round the numbers. I not sure now. – kujaw May 6 at 14:57
  • you could try with a ST_AsTWKB roundtrip. – ThingumaBob May 6 at 15:27
0

Turns out that all of the geometries that couldn't be divided by ST_SnapToGrid had form of xx.xxxxxxx9999999x, where x is a digit. That means, that if I want its precision to be centimetric, it's safe to treat such geometries as a number and just round it mathematically, all of them will round to the higher number.
After such process, they matched with the geometries I should get after reducing precision. So, first I snapped the X and Y geometries separately, then rounded them and recreated geometry from concatenated string.

ST_GeomFromText(concat(
'POINT (', 
round(ST_X(ST_SnapToGrid(point, 0.0000001))::numeric, 7), 
' ', 
round(ST_Y(ST_SnapToGrid(point, 0.0000001))::numeric, 7),
')'
))

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.