ST_SnapToGrid doesn't work properly, e.g. 41.94186153740355 -> 41.941861499999995

Question

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?

Answer 1

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),
')'
))

Answer 2

I had the same problem. I use the an other snapToGrid function:

geometry ST_SnapToGrid(geometry geomA, float originX, float originY, float sizeX, float sizeY);

see also: https://postgis.net/docs/ST_SnapToGrid.html

use as originX and originY an Point near the middle of your geometry rounded to an integer

in your case: something like this:

select st_astext(st_snaptogrid('POINT (21.619820510769063 41.94186153740355)'::geometry,21,41,0.0000001,0.0000001));

with polygons or lines do something like that

update mytable set the_geom = st_snaptogrid(the_geom,st_x(st_centroid(the_geom))::int,st_y(st_centroid(the_geom))::int,0.01,0.01)) ;

Answer 3

This is a PostGIS bug. See ticket 5425. There is a proposed fix, but unfortunately there's no current schedule for it to be implemented.