ERROR in ST_Intersects

[tomasmarek]

I have two tables with one row:

CREATE TABLE public.l (
    nkodfb character varying(254),
    id_uz bigint,
    geom public.geometry(MultiPolygon,5514)
);


ALTER TABLE public.l OWNER TO c_assets_admin;


CREATE TABLE public.p (
    par_id bigint,
    vymera bigint,
    geometry public.geometry(Geometry,5514)
);


COPY public.l (nkodfb, id_uz, geom) FROM stdin;
707112002/3	28012	01060000208A150000010000000103000000070000002B0000003E0AD7A3ED9525C15C8FC235FD1C31C11F85EB51779525C1C3F5285CD51C31C1CDCCCC4C0F9625C1D7A370BD5A1C31C1295C8FC22B9625C1CDCCCC4C411C31C185EB5138BD9525C1AE47E13A221C31C1676666E6719525C18FC2F5A80F1C31C17B14AE47409525C1AE47E1FA071C31C185EB51B8DE9425C1295C8F82FD1B31C148E17A14629425C1E17A142EF21B31C10AD7A3F0019425C1713D0A57E31B31C17B14AEC7E79325C13D0AD7E3E01B31C1B81E85EBA99325C1AE47E1FADB1B31C1C3F528DC929325C19A999919DC1B31C1A4703D8A899325C1333333B3E01B31C1CDCCCC4C4C9325C1666666A6FC1B31C18FC2F528159325C10AD7A3F0131C31C1CB10C75A0C9325C1295C8F72251C31C15DFE43BA0B9325C12DB29DCF2B1C31C1E17A142E139325C1CDCCCC2C2F1C31C15EBA490C299325C1DBF97EAA3A1C31C15839B4884E9325C1A69BC420571C31C1508D97AE849325C1250681357C1C31C1F4FDD4F88B9325C18FC2F5E8801C31C1C21726D39E9325C190A0F881881C31C1BF0E9C33D19325C11973D7F2951C31C1D88173A61F9425C1CC7F48EFA91C31C160764FBE359425C17FD93D59AF1C31C13B014D644C9425C183C0CAC1B61C31C17FD93DD95D9425C16B2BF647BE1C31C1E3361A007A9425C110583904CB1C31C1F7E46121929425C1E3A59BC4DA1C31C1B98D06B0BB9425C1ED0DBEA0FE1C31C14260E510E09425C14260E5D0221D31C1CDCCCCCC0B9525C18716D9CE451D31C1EE7C3FB5229525C19EEFA7C6501D31C10E2DB29D399525C1C976BEFF551D31C10AD7A3B0549525C1508D976E541D31C13E0AD7A36C9525C19EEFA7C6501D31C1295C8FA2859525C1917EFB8A401D31C130BB270F9F9525C117D9CEA72C1D31C166F7E4E1B89525C16519E2B81B1D31C1F697DD13DD9525C1D5E76A8B041D31C13E0AD7A3ED9525C15C8FC235FD1C31C10400000095D409A8809325C188F4DB27691C31C1A60A46A57E9325C1598638D66B1C31C1C976BE9F7A9325C1AE47E17A671C31C195D409A8809325C188F4DB27691C31C104000000AA8251A9F49425C15C8FC275DF1C31C131992AF8EF9425C125068125E31C31C1643BDFEFE99425C110E9B7CFE21C31C1AA8251A9F49425C15C8FC275DF1C31C1040000008104C54F6D9525C111363CAD071D31C1F01648F0659525C1DAACFA5C0B1D31C1772D213F619525C1B07268B10A1D31C18104C54F6D9525C111363CAD071D31C1040000002EFF21BD7E9525C11283C04AC01C31C1516B9AB77A9525C1014D844DC21C31C19D11A55D779525C10A68224CC11C31C12EFF21BD7E9525C11283C04AC01C31C104000000083D9B35989525C18AB0E139761C31C1764F1ED6909525C114D044E8781C31C1D42B65798B9525C146257592781C31C1083D9B35989525C18AB0E139761C31C10400000004E78CA8AD9525C1CE88D27E2C1C31C18BFD65F7A89525C16DC5FE822F1C31C1D7A3709DA59525C176E09C812E1C31C104E78CA8AD9525C1CE88D27E2C1C31C1
\.


COPY public.p (par_id, vymera, geometry) FROM stdin;
2859201304	108	01060000208A1500000100000001030000000100000006000000C3F5285C389625C10AD7A3B00E1D31C13D0AD7A3099625C114AE47A1001D31C13D0AD7A3ED9525C15C8FC235FD1C31C10AD7A370F39525C148E17A14FF1C31C1CDCCCCCCFE9525C15C8FC235031D31C1C3F5285C389625C10AD7A3B00E1D31C1
\.

I run and I get an error:

select par_id, public.ST_Intersection(p.geometry, l.geom) AS geom FROM p JOIN l ON public.ST_Intersects(p.geometry, l.geom);
ERROR:  GEOSIntersects: TopologyException: side location conflict at -707318.82000000007 -1121533.21. This can occur if the input geometry is invalid.

But when I run It is ok:

select par_id, public.ST_Intersection(p.geometry, l.geom) AS geom FROM p JOIN l ON public.ST_Intersection(p.geometry, l.geom) IS NOT NULL;
   par_id   |                                            geom                                            
------------+--------------------------------------------------------------------------------------------
 2859201304 | 01020000208A150000020000003E0AD7A3ED9525C15C8FC235FD1C31C13D0AD7A3ED9525C15C8FC235FD1C31C1

[robe]

This does seem weird that ST_Intersects would trigger an error but ST_Intersection would not.

However I can't replicate your issue with these geometries when running:

POSTGIS="3.3.3dev 3.3.2-47-g76f44dea8" [EXTENSION] PGSQL="160" GEOS="3.12.0dev-CAPI-1.18.0" PROJ="7.2.1" LIBXML="2.9.14" LIBJSON="0.12" LIBPROTOBUF="1.2.1" WAGYU="0.5.0 (Internal)" PostgreSQL 16devel on x86_64-w64-mingw32, compiled by gcc.exe (x86_64-posix-seh-rev0, Built by MinGW-W64 project) 8.1.0, 64-bit

So I suspect it's geos dependent and perhaps an issue that has already been fixed in GEOS.

Can you output what:

SELECT postgis_full_version();

returns

[mdavis]

In GEOS 3.12 intersects has ​changed to use the PreparedIntersects algorithm, since it is faster and more robust.

Older versions of GEOS (likely including all installed versions) use the original intersects algorithm, which does fail on this case (due to numeric precision issues).

[robe]

ah sorry I should have tested a released version of GEOS .

Just tested and yes I see the bug now on:

POSTGIS="3.3.2 3.3.2" [EXTENSION] PGSQL="150" GEOS="3.11.1-CAPI-1.17.1" PROJ="7.2.1" GDAL="GDAL 3.4.3, released 2022/04/22" LIBXML="2.9.9" LIBJSON="0.12" LIBPROTOBUF="1.2.1" WAGYU="0.5.0 (Internal)" RASTER

That said you think this is worth fixing or just close as a "upgrade to 3.12 when it comes out"

[robe]

Spoke to pramsey about this. He said the intersects and intersection are separate code lines. GEOS doesn't plan to fix in lower versions since it would be too destabilizing.

So I'm going to close this out as fixed in GEOS 3.12 (albeit, not yet released), but will be released probably in another 3-5 months.