PostGIS – Different Results from Docker Image and Local Installation on Windows 10

dockerpostgispostgresql

I've got a monster of a query that at the end will spit out some lines (4326) and before the final lines are spit out, the ones that run into each other are combined. This works great locally;

enter image description here

I group by orientation and here, orientation 3 (origin_table 3) has only 3 lines in the final result. But when I run the exact same query in the docker PostGIS image the lines are not combined:

enter image description here

I have tried postgis/postgis:latest , postgis/postgis:10-3.2, and postgis/postgis:10-3.2-alpine and have gotten the same result. My local version is:

"POSTGIS=""3.2.0 3.2.0"" [EXTENSION] PGSQL=""130"" GEOS=""3.10.1-CAPI-1.16.0"" PROJ=""7.2.1"" LIBXML=""2.9.9"" LIBJSON=""0.12"" LIBPROTOBUF=""1.2.1"" WAGYU=""0.5.0 (Internal)"" TOPOLOGY"

I've narrowed it down to this query:

             create temp table pre_qgis_combine_3 as select len, st_makeline((ST_LineInterpolatePoint((st_makeline(ST_PointN(ST_ExteriorRing(geom), 1), ST_PointN(ST_ExteriorRing(geom), 2))), 0.5)),
                                                     (ST_LineInterpolatePoint((st_makeline(ST_PointN(ST_ExteriorRing(geom), 3), ST_PointN(ST_ExteriorRing(geom), 4))), 0.5))) as nl from squarepoints;

which takes the geom from a grid of cells (just like this) and makes lines that bisect the grid cells. I can take the grid from the docker PostGIS and put it as input in my local PostGIS and the lines combine just fine. so the grid is not the problem, its after this query in the docker PostGIS when the lines output differ enough that they cannot merge together.

The lines that go into this query are slightly different in the PostGIS docker, here are a few example pairs:

'LINESTRING(-101.94556711378249 31.239157887172595,-101.94551393628788 31.24020518701191)',
'LINESTRING(-101.94556711378232 31.239157887172716,-101.94551393628771 31.24020518701203)'

'LINESTRING(-101.94551393628788 31.24020518701191,-101.94546075879327 31.24125248685121)',
'LINESTRING(-101.94551393628771 31.24020518701203,-101.9454607587931 31.24125248685133)'

'LINESTRING(-101.94546075879327 31.24125248685121,-101.94540758129867 31.242299786690538)',
'LINESTRING(-101.94546075879309 31.24125248685133,-101.94540758129848 31.24229978669066)'

'LINESTRING(-101.94540758129867 31.242299786690538,-101.94535440380406 31.243347086529823)',
'LINESTRING(-101.94540758129847 31.24229978669066,-101.94535440380386 31.243347086529944)'

'LINESTRING(-101.94535440380406 31.243347086529823,-101.94530122630945 31.24439438636915)',
'LINESTRING(-101.94535440380386 31.243347086529944,-101.94530122630924 31.244394386369272)'

The top one of each pair is from my local PostGIS, the bottom one is from docker. if I run the cluster intersecting query on the top ones then they combine into fewer lines, not so with the bottom ones.

Any idea on why my result would be different for the same query?

Best Answer

This is a really interesting problem to me. Since the differences that you are seeing are so small it leads me to suspect that this has to do with the way your systems are handling floating point math. Here is a paper on the "Pitfalls of verifying floating point computations". Basically diffent CPU architectures or operating systems can result in different floating point math results. Generally these differences are minuscule but, as you've seen, the differences can result in errors.

Your time might be better spent looking into building some kind of tolerance into your code (Like @tbob suggested).

Out of interest: are you running different OS's (like Windows and Docker on Windows?) for the containers vs localhost? Are you running different CPU architectures on Docker vs localhost (the server vs your laptop)?

Related Question