Actually 3.6.0 already has the fix, so only the stable 3.5 branch in conjunction with GEOS 3.13+ with display the issue.
P. On Thu, Oct 16, 2025 at 9:25 AM Paul Ramsey <[email protected]> wrote: > Arg, found it. How old am I? Old enough not to remember this fix from, not > YEARS ago, but only months ago. > > https://trac.osgeo.org/postgis/ticket/5938 > > The interaction between the new "cacheable relate" code and the need for > stable operand order for the relate pattern match function generates these > issues, and it's not platform specific, just version specific. > Unfortunately the version of PostGIS 3.5 that fixes this issue... is not > yet released. 3.5.4 will include this fix. As will 3.6.1. My habit of > running and testing off the latest stable branch bit me here, if I was > testing your exact release I would have seen the error immediately. > > P. > > On Wed, Oct 15, 2025 at 8:25 PM Regina Obe <[email protected]> wrote: > >> Antonio, >> >> >> >> I can replicate your issue with PG17 on windows >> >> >> >> -------------------------------------------------------------- >> >> POSTGIS="3.5.3 3.5.3" [EXTENSION] PGSQL="170" GEOS="3.13.1-CAPI-1.19.2" >> PROJ="8.2.1 NETWORK_ENABLED=OFF URL_ENDPOINT= >> USER_WRITABLE_DIRECTORY=C:\Windows\ServiceProfiles\NetworkService\AppData\Local/proj" >> (compiled against PROJ 8.2.1) LIBXML="2.12.5 >> >> " LIBJSON="0.12" LIBPROTOBUF="1.2.1" WAGYU="0.5.0 (Internal)" >> >> >> >> However the PG18 3.6.0 windows package behaves correctly: >> >> >> >> PG18 3.6.0 - POSTGIS="3.6.0 3.6.0" [EXTENSION] PGSQL="180" >> GEOS="3.14.0-CAPI-1.20.4" SFCGAL="SFCGAL 2.2.0, CGAL 6.0.1, BOOST 1.88.0" >> PROJ="8.2.1 NETWORK_ENABLED=OFF URL_ENDPOINT= >> USER_WRITABLE_DIRECTORY=C:\Users\Administrator\AppData\Local/proj" >> (compiled again >> st PROJ 8.2.1) GDAL="GDAL 3.9.2, released 2024/08/13 GDAL_DATA not found" >> LIBXML="2.12.5" LIBJSON="0.12" LIBPROTOBUF="1.2.1" WAGYU="0.5.0 (Internal)" >> TOPOLOGY RASTER >> >> >> >> There are many things changed between the building of the windows 3.5.3 >> and the 3.6.0 packages >> >> >> >> e.g. the GEOS was upgraded, I’m compiling with a newer GCC version , and >> of course PostGIS is newer. >> >> >> >> I’m planning to release PostGIS 3.6.1 for PostgreSQL 17, but skipping >> PostGIS 3.6.0 since I have some minor packaging issues to address first and >> PostGIS 3.6.1 is probably less than a month away. >> >> >> >> But we’ll try to figure out what piece is the culprit here. >> >> >> >> *From:* Paul Ramsey via postgis-users <[email protected]> >> *Sent:* Wednesday, October 15, 2025 6:32 PM >> *To:* Antonio Valanzano <[email protected]> >> *Cc:* [email protected] >> *Subject:* Re: different results for ST_Relate with 3 parameters >> compared to those for ST_Relate with 2 parameters >> >> >> >> One more sorry! >> >> >> >> SELECT >> ST_Relate(a.geom, b.geom), ST_Relate(a.geom, b.geom, '1FF00F212') >> FROM >> (VALUES >> ('LINESTRING (170 290, 205 272)'), >> ('LINESTRING (120 215, 176 197)'), >> ('LINESTRING (170 290, 205 272)'), >> ('LINESTRING (120 215, 176 197)')) AS a(geom), >> (VALUES ('POLYGON ((100 200, 140 230, 180 310, 280 310, 390 270, 400 210, >> 320 140, 215 141, 150 170, 100 200))')) AS b(geom); >> >> >> >> On Wed, Oct 15, 2025 at 3:11 PM Antonio Valanzano <[email protected]> >> wrote: >> >> Here are the results >> >> >> >> SELECT >> ST_Relate(a.geom, b.geom), >> ST_Relate(a.geom, b.geom, '1FF00F212') >> FROM >> (VALUES ('LINESTRING (170 290, 205 272)'),('LINESTRING (120 215, 176 >> 197)')) AS a(geom), >> (VALUES ('POLYGON ((100 200, 140 230, 180 310, 280 310, 390 270, 400 210, >> 320 140, 215 141, 150 170, 100 200))')) AS b(geom); >> -- 2 rows >> "st_relate" "st_relate-2" >> "1FF00F212" true >> "1FF00F212" false >> >> >> >> Il giorno mer 15 ott 2025 alle ore 23:22 Paul Ramsey < >> [email protected]> ha scritto: >> >> Thanks for continuing to try stuff. What does this example return? >> >> >> >> SELECT >> ST_Relate(a.geom, b.geom), ST_Relate(a.geom, b.geom, '1FF00F212') >> FROM >> (VALUES ('LINESTRING (170 290, 205 272)'),('LINESTRING (120 215, 176 >> 197)')) AS a(geom), >> (VALUES ('POLYGON ((100 200, 140 230, 180 310, 280 310, 390 270, 400 210, >> 320 140, 215 141, 150 170, 100 200))')) AS b(geom); >> >> >> >> >> >> On Wed, Oct 15, 2025 at 1:29 PM Antonio Valanzano <[email protected]> >> wrote: >> >> Dear Paul >> >> here are the results with the new linestrings as you suggested >> >> >> >> SELECT >> d.id, >> ST_Relate(d.geom, l.geom) as patternMatrix >> FROM docks as d, lakes as l >> WHERE ST_Relate(d.geom, l.geom, '1FF00F212') = true; >> >> -- 1 row >> >> >> >> "id" "patternmatrix" >> 7 "1FF00F212" >> >> >> >> SELECT >> d.id, >> ST_Relate(d.geom, l.geom) as patternMatrix >> FROM docks as d, lakes as l >> WHERE ST_Relate(d.geom, l.geom) = '1FF00F212'; >> >> -- 4 rows >> >> "id" "patternmatrix" >> 7 "1FF00F212" >> 8 "1FF00F212" >> 12 "1FF00F212" >> 13 "1FF00F212" >> >> >> >> As you can see nothing has changed. >> >> >> >> I was wondering which version of PostGIS (and on which platform) has >> been used for producing the material reported into the tutorial (which >> shows 2 rows as a correct result). >> >> >> >> I understand that it is difficult to find the reason for different >> results on different platforms but this shouldn't happen otherwise users >> are confused.. and not sure about the correct results. >> >> >> >> When in the near future I will upgrade to PostgreSQL 18 and PostGIS >> 3.6.0 I will try again the same two queries and let you know if the >> results will be the same or not. >> >> Thanks for the time you have spent on this matter. >> >> >> >> Antonio >> >> >> >> >> >> Il giorno mer 15 ott 2025 alle ore 22:03 Paul Ramsey < >> [email protected]> ha scritto: >> >> >> >> >> >> On Wed, Oct 15, 2025 at 12:53 PM Antonio Valanzano <[email protected]> >> wrote: >> >> Hi Paul >> >> I have upgraded to PosGIS 3.5.3 and GEOS 3.13.1 as you can see from the >> following output >> >> >> >> "postgis_full_version" >> "POSTGIS=""3.5.3 3.5.3"" [EXTENSION] PGSQL=""170"" >> GEOS=""3.13.1-CAPI-1.19.2"" PROJ=""8.2.1 NETWORK_ENABLED=OFF URL_ENDPOINT= >> https://cdn.proj.org >> USER_WRITABLE_DIRECTORY=C:\Windows\ServiceProfiles\NetworkService\AppData\Local/proj >> DATABASE_PATH=C:\Program >> Files\PostgreSQL\17\share\contrib\postgis-3.5\proj\proj.db"" (compiled >> against PROJ 8.2.1) LIBXML=""2.12.5"" LIBJSON=""0.12"" >> LIBPROTOBUF=""1.2.1"" WAGYU=""0.5.0 (Internal)"" (core procs from ""3.5.2 >> 3.5.2"" need upgrade)" >> >> but the results are the same with one row for a query and 2 rows for the >> other query. >> >> >> >> Is this a known bug or no other user has already reported this behaviour ? >> >> >> >> Not reported, and I'm afraid not solvable unless you can figure out the >> specific thing about your install vs mine that is giving you a different >> answer. (Windows is one possibility, though not one I particularly like, >> platform differences are incredibly hard to isolate.) >> >> >> >> Seeing if the problem is ordering based and number of entries based might >> be interesting. >> >> >> >> DELETE FROM docsk; >> >> INSERT INTO docks ( geom, good ) >> VALUES >> ('LINESTRING (170 290, 205 272)',true), >> ('LINESTRING (120 215, 176 197)',true), >> ('LINESTRING (290 260, 340 250)',false), >> ('LINESTRING (350 300, 400 320)',false), >> ('LINESTRING (370 230, 420 240)',false), >> ('LINESTRING (170 290, 205 272)',true), >> ('LINESTRING (120 215, 176 197)',true), >> ('LINESTRING (370 180, 390 160)',false); >> >> >> >> P >> >> >> >> >> >> Antonio >> >> >> >> >> >> >> >> >> >> Il giorno mer 15 ott 2025 alle ore 20:59 Paul Ramsey < >> [email protected]> ha scritto: >> >> Sorry, I still cannot replicate. My 3.5 build still returns both results. >> Maybe update to PostGIS 3.5.4 and GEOS 3.13.1 ? >> >> P. >> >> >> >> On Wed, Oct 15, 2025 at 11:25 AM Antonio Valanzano <[email protected]> >> wrote: >> >> Here is the details of my installation: >> >> >> >> "postgis_full_version" >> "POSTGIS=""3.5.2 3.5.2"" [EXTENSION] PGSQL=""170"" >> GEOS=""3.13.0-CAPI-1.19.0"" PROJ=""8.2.1 NETWORK_ENABLED=OFF URL_ENDPOINT= >> https://cdn.proj.org >> USER_WRITABLE_DIRECTORY=C:\Windows\ServiceProfiles\NetworkService\AppData\Local/proj >> DATABASE_PATH=C:\Program >> Files\PostgreSQL\17\share\contrib\postgis-3.5\proj\proj.db"" (compiled >> against PROJ 8.2.1) LIBXML=""2.12.5"" LIBJSON=""0.12"" >> LIBPROTOBUF=""1.2.1"" WAGYU=""0.5.0 (Internal)""" >> >> >> >> Antonio >> >> >> >> >> >> >> >> Il giorno mer 15 ott 2025 alle ore 19:28 Paul Ramsey < >> [email protected]> ha scritto: >> >> Maybe you have found an old bug? Running exactly the same SQL as you, I >> get two rows from each query. >> >> >> >> postgis=# SELECT >> >> d.id, >> >> ST_Relate(d.geom, l.geom) as patternMatrix >> >> FROM docks as d, lakes as l >> >> WHERE ST_Relate(d.geom, l.geom, >> '1FF00F212'); >> id | patternmatrix >> ----+--------------- >> 1 | 1FF00F212 >> 2 | 1FF00F212 >> (2 rows) >> >> postgis=# SELECT >> postgis-# d.id, >> postgis-# ST_Relate(d.geom, l.geom) as patternMatrix >> postgis-# FROM docks as d, lakes as l >> postgis-# WHERE ST_Relate(d.geom, l.geom) = '1FF00F212'; >> id | patternmatrix >> ----+--------------- >> 1 | 1FF00F212 >> 2 | 1FF00F212 >> (2 rows) >> >> postgis=# >> postgis=# select postgis_full_version(); >> >> >> >> postgis_full_version >> >> >> >> >> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- >> POSTGIS="3.7.0dev 3.6.0rc2-134-g5dc95f1bc" [EXTENSION] PGSQL="180" >> GEOS="3.15.0dev-CAPI-1.21.0" PROJ="9.6.2 NETWORK_ENABLED=ON URL_ENDPOINT= >> https://cdn.proj.org >> USER_WRITABLE_DIRECTORY=/Users/pramsey/Library/Application Support/proj >> DATABASE_PATH=/opt/homebrew/Cellar/proj/9.6.2/share/proj/proj.db" (compiled >> against PROJ 9.6.2) LIBXML="2.9.13" LIBJSON="0.18" LIBPROTOBUF="1.5.2" >> WAGYU="0.5.0 (Internal)" (core procs from "3.7.0dev >> 3.6.0rc2-125-g747d7732b" need upgrade) >> >> >> >> On Wed, Oct 15, 2025 at 9:22 AM Antonio Valanzano <[email protected]> >> wrote: >> >> I am following the "Introduction to PostGIS " tutorial at >> https://postgis.net/workshops/postgis-intro/ >> >> and for chapter 26 "Dimensionally Extended 9-Intersection Model" I am >> trying to replicate the examples. >> >> >> >> If I use the two different versions of ST_Relate I do not obtain the same >> result >> >> >> >> SELECT >> d.id, >> ST_Relate(d.geom, l.geom) as patternMatrix >> FROM docks as d, lakes as l >> WHERE ST_Relate(d.geom, l.geom, '1FF00F212') = true; >> -- 1 row >> "id" "patternmatrix" >> 1 "1FF00F212" >> >> >> >> SELECT >> d.id, >> ST_Relate(d.geom, l.geom) as patternMatrix >> FROM docks as d, lakes as l >> WHERE ST_Relate(d.geom, l.geom) = '1FF00F212'; >> -- 2 rows >> "id" "patternmatrix" >> 1 "1FF00F212" >> 2 "1FF00F212" >> >> >> >> Could someone give me an explanation of such a difference ? >> >> >> >> >> >> >> >> >> >>
