ST_Intersects missing rows

[schmiddy]

Hi, I am testing with the following PostGIS version on OS X:

POSTGIS="1.5.3" GEOS="3.3.8-CAPI-1.7.8" PROJ="Rel. 4.8.0, 6 March 2012" LIBXML="2.7.8" USE_STATS (procs from 1.5 r5385 need upgrade)

I am attaching a simplified test dump plus test query which illustrates the problem; a test table is created with four rows with an identical geometry column, but an ST_Intersects query always only returns the first three rows in the table. If I move the first row COPY'ed into the table to the end of the COPY statement, the SELECT will find that row, but not the new first row.

Sorry to be reporting on an older version of PostGIS, I know this bug may have already been fixed in newer versions (perhaps ticket 2675?), but hopefully this test script should be quite easy to verify.

[schmiddy]

Testcase — just load this into a database with PostGIS. Expected result of the SELECT query at the end is four rows.

[pramsey]

The "polygon" is radically invalid (it's the same point repeated 21 times). Our general position is that results are indeterminate in the case of invalidities, and in this case you've exposed that our brute-force calculation provides a different result that our cached calculation does (hence the always-missing first entry). However, since the geometry is so terrible, I don't know that I care

[robe]

I would expect 0 rows returned for this since the geometry as Paul said is so invalid it would fail any intersection matrix test so should always return false. Sometimes we do short-circuit that and not do the intersection matrix check. If you are using

ST_DWithin(poly, testgeom,0)  

you should get the answer you are expecting since that doesn't rely on validity.

What I am a bit concerned about though is I tested your test case on my

POSTGIS="1.5.5" GEOS="3.3.8-CAPI-1.7.8" PROJ="Rel. 4.6.1, 21 August 2008" LIBXML="2.7.8" USE_STATS PostgreSQL 9.0.17, compiled by Visual C++ build 1500, 32-bit

and that returned 0 answers as I would expect.

But when I did the same test on my

POSTGIS="2.1.2 r12389" GEOS="3.4.2-CAPI-1.8.2 r3924" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.0, released 2013/04/24" LIBXML="2.7.8" LIBJSON="UNKNOWN" RASTER

I get the 3 records your are complaining is not enough. I also tested on my

POSTGIS="2.0.6" GEOS="3.4.2-CAPI-1.8.2 r0" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.0, released 2013/04/24" LIBXML="2.7.8" LIBJSON="UNKNOWN" (core procs from "2.0.6" need upgrade) RASTER (raster procs from "2.0.6" need upgrade) PostgreSQL 9.2.4, compiled by Visual C++ build 1600, 64-bit

and got the same 3 answers.

I would chuck this as something with our PostGIS 2.0 or 2.1 cache implementation or short-circuiting that is not present in 1.5, but you are supposedly running a PostGIS older than the 1.5.5 I tested and getting more like 2.0 / 2.1 answers.

Are you absolutely sure you are testing this on 1.5.3. I think my 1.5.5 is right and the only install that gives a sane answer to this ill defined question .

[schmiddy]

Oh geez, pramsey is dead on! A quick check like:

SELECT ST_IsValid(poly) FROM mytest;

gives complaints like:

NOTICE: Too few points in geometry component at or near point -97.967399999999998 26.166599999999999

And robe: yes, that the version string I posted earlier is what SELECT postgis_full_version() tells me, namely:

 POSTGIS="1.5.3" GEOS="3.3.8-CAPI-1.7.8" PROJ="Rel. 4.8.0, 6 March 2012" LIBXML="2.7.8" USE_STATS (procs from 1.5 r5385 need upgrade)

Marking ticket 'invalid' per pramsey's comment.