ST_SnapToGrid returns not correct data

[tmazuga]

Function ST_SnapToGrid returns not correct data (PostGIS >= 3.2.3)

Query

SELECT ST_AsText(ST_SnapToGrid(st_setsrid(ST_GeomFromText('LINESTRING(674169.89 198051.38,674197.7 198065.55,674200.36 198052.38)'),2180), 0.001));

Return with 3.3.3 and 3.2.3

LINESTRING(674169.89 198051.38,674197.7000000001 198065.55000000002,674200.36 198052.38)

With PostGIS 3.0.1

LINESTRING(674169.89 198051.38,674197.7 198065.55,674200.36 198052.38)

POSTGIS="3.3.3 3.3.3" [EXTENSION] PGSQL="150" GEOS="3.11.2-CAPI-1.17.2" PROJ="8.2.1" LIBXML="2.9.14" LIBJSON="0.16" LIBPROTOBUF="1.2.1" WAGYU="0.5.0 (Internal)" (core procs from "3.3.2 3.3.2" need upgrade) PostgreSQL 15.3, compiled by Visual C++ build 1914, 64-bit

Several versions of PostgreSQL (14, 15) on Windows and Ubuntu were also checked.

[strk]

It looks like it's the text representation having gotten more detail. If you grid to 0.001 you probably only want 3 decimal digits at most, anyway. In this case even the asking for 9 give the answer you expect (it takes 10 decimal digits to show the unexpected digits, showing up at the 16th figure)

Simplified testcase:

test5425.sql

SELECT ST_AsText(ST_SnapToGrid('LINESTRING(674169.89 198051.38,674197.7 198065.55,674200.36 198052.38)', 0.001));

test5425_expected

LINESTRING(674169.89 198051.38,674197.7 198065.55,674200.36 198052.38)

The test succeeds with 3.0.8 The test succeeds with 3.0.8-13-g72385411f (current tip of stable-3.0 branch) The test fails with 3.1.0 The test fails with 3.1.9-7-g0d745994f (current tip of stable-3.1 branch)

Note that using ST_Equals shows that the actual point isn't as round as reported by ST_AsText even in 3.0. The following query will return true in both 3.0 and 3.1, while changing those rightmost digits ends up reporting false.

SELECT ST_Equals(
  ST_PointN(
    ST_SnapToGrid(g, 0.001),
    2
  ),
  --'POINT(674197.7 198065.55)' -- this gives false
  'POINT(674197.7000000001 198065.55000000002)' -- this gives true
  --'POINT(674197.7000000001 198065.55000000001)' -- this also gives true
)
FROM inp;

The issue of points not being on the expected grid has always existed and is a numerical robustness.

ST_AsText giving more detail seems to be an enhancement, not a degradation.

For this reason I'm retargetting this ticket to the Fund Me milestone.

[mdavis]

See ​​https://github.com/libgeos/geos/pull/956 for a fix for this issue.

[mdavis]

Also see #5520, #3929.

Also ​https://gis.stackexchange.com/questions/465485/postgis-reduce-precision-in-linestring

[mdavis]

Here is a reproducer for the problem reported in ​https://lists.osgeo.org/pipermail/postgis-users/2006-January/010861.html

SELECT ST_AsText( ST_SnapToGrid(
                       ST_SnapToGrid('POINT(1.23456789 9.87654321)'::geometry, 0.001),
                    0.000001) );
          
--------------------------------
 POINT(1.235 9.876999999999999)