different results for ST_Relate with 3 parameters compared to those for ST_Relate with 2 parameters
Paul Ramsey
pramsey at cleverelephant.ca
Thu Oct 16 09:47:43 PDT 2025
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 <pramsey at cleverelephant.ca>
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 <lr at pcorp.us> 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 <postgis-users at lists.osgeo.org>
>> *Sent:* Wednesday, October 15, 2025 6:32 PM
>> *To:* Antonio Valanzano <anvalanz at gmail.com>
>> *Cc:* postgis-users at lists.osgeo.org
>> *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 <anvalanz at gmail.com>
>> 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 <
>> pramsey at cleverelephant.ca> 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 <anvalanz at gmail.com>
>> 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 <
>> pramsey at cleverelephant.ca> ha scritto:
>>
>>
>>
>>
>>
>> On Wed, Oct 15, 2025 at 12:53 PM Antonio Valanzano <anvalanz at gmail.com>
>> 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 <
>> pramsey at cleverelephant.ca> 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 <anvalanz at gmail.com>
>> 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 <
>> pramsey at cleverelephant.ca> 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 <anvalanz at gmail.com>
>> 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 ?
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20251016/6bb2258d/attachment.htm>
More information about the postgis-users
mailing list