#3777 closed defect (fixed)
POINT EMPTY, POINT 0 0 anomaly
Reported by: | robe | Owned by: | strk |
---|---|---|---|
Priority: | medium | Milestone: | PostGIS 2.3.3 |
Component: | postgis | Version: | 2.3.x |
Keywords: | Cc: |
Description (last modified by )
As Pierre pointed out in:
https://lists.osgeo.org/pipermail/postgis-users/2017-June/042203.html
We've got what appears to be a bug with our handling of POINT EMPTY, POINT 0 0.
Tested on PostGIS 2.3 and 2.4
SELECT ST_AsText(geom) txt, count(*) FROM ( SELECT 'POINT(0 0)'::geometry geom UNION ALL SELECT 'POINT(0 0)'::geometry geom UNION ALL SELECT 'POINT(0 0)'::geometry geom UNION ALL SELECT 'POINT(0 1)'::geometry geom UNION ALL SELECT 'LINESTRING(0 0,0 1)'::geometry geom UNION ALL SELECT 'GEOMETRYCOLLECTION EMPTY'::geometry geom UNION ALL SELECT 'POINT EMPTY'::geometry geom ) foo GROUP BY geom;
Yields:
txt | count --------------------------+------- POINT(0 0) | 2 POINT EMPTY | 1 POINT(0 0) | 1 LINESTRING(0 0,0 1) | 1 GEOMETRYCOLLECTION EMPTY | 1 POINT(0 1) | 1
when it should be yielding something along the lines of:
txt | count --------------------------+------- POINT(0 0) | 3 LINESTRING(0 0,0 1) | 1 GEOMETRYCOLLECTION EMPTY | 2 POINT(0 1) | 1
Since POINT EMPTY and GEOMETRYCOLLECTION have null boxes they should aggregate together and since points and 2 point lines should have no boxes, they should be taken literally.
I did the same exercise in PostGIS 1.5.5 and got this:
txt | count --------------------------+------- GEOMETRYCOLLECTION EMPTY | 4 LINESTRING(0 0,0 1) | 1 GEOMETRYCOLLECTION EMPTY | 1 POINT(0 1) | 1 (4 rows)
which I attributed to the fact that at one point of time, since we had no concept of POINT EMPTY we always output the representation as GEOMETRYCOLLECTION EMPTY and I think there was one function that used POINT(0 0) as rep for POINT EMPTY. Though I could be mistaken.
Change History (17)
comment:1 Changed 8 months ago by
Description: | modified (diff) |
---|
comment:2 Changed 8 months ago by
comment:3 Changed 8 months ago by
GROUP BY uses btree opclass, which uses bounding boxes, any EMPTY has the same bounding box (null).
comment:4 Changed 8 months ago by
strk yes, but that's not the point. The bug is the empties are not collapsing into null and neither are the POINT(0 0) when you do a group by. Why is one POINT(0 0) not the same as the other POINT(0 0)?
txt | count --------------------------+------- POINT(0 0) | 2 POINT EMPTY | 1 POINT(0 0) | 1 LINESTRING(0 0,0 1) | 1 GEOMETRYCOLLECTION EMPTY | 1 POINT(0 1) | 1
comment:5 Changed 8 months ago by
okay strk per your suggestion, here is what UNION does:
SELECT ST_AsText(geom) txt FROM ( SELECT 'POINT(0 0)'::geometry geom UNION SELECT 'POINT(0 0)'::geometry geom UNION SELECT 'POINT(0 0)'::geometry geom UNION SELECT 'POINT(0 1)'::geometry geom UNION SELECT 'LINESTRING(0 0,0 1)'::geometry geom UNION SELECT 'GEOMETRYCOLLECTION EMPTY'::geometry geom UNION SELECT 'POINT EMPTY'::geometry geom ) foo;
In 2.4.0dev/2.3.2 it returns:
txt -------------------------- POINT(0 0) POINT EMPTY POINT(0 0) LINESTRING(0 0,0 1) GEOMETRYCOLLECTION EMPTY POINT(0 1) (6 rows)
In my PostGIS 1.5.5 returns
txt -------------------------- GEOMETRYCOLLECTION EMPTY LINESTRING(0 0,0 1) GEOMETRYCOLLECTION EMPTY POINT(0 1) (4 rows)
comment:6 Changed 8 months ago by
We must have had this problem for sometime. PostGIS 1.5.5 is doing something different as well when change order of items. Check this out:
SELECT ST_AsText(geom) txt FROM ( SELECT 'GEOMETRYCOLLECTION EMPTY'::geometry geom UNION SELECT 'POINT EMPTY'::geometry geom UNION SELECT 'POINT(0 0)'::geometry geom UNION SELECT 'POINT(0 0)'::geometry geom UNION SELECT 'POINT(0 0)'::geometry geom UNION SELECT 'POINT(0 1)'::geometry geom UNION SELECT 'LINESTRING(0 0,0 1)'::geometry geom ) foo;
In 2.3.2 and 2.4.0dev
txt -------------------------- POINT(0 0) GEOMETRYCOLLECTION EMPTY POINT(0 0) POINT EMPTY POINT(0 0) LINESTRING(0 0,0 1) POINT(0 1) (7 rows)
In 1.5.5
txt --------------------- POINT(0 0) LINESTRING(0 0,0 1) POINT(0 1) (3 rows)
comment:7 Changed 8 months ago by
Milestone: | PostGIS 2.3.3 → PostGIS 2.4.0 |
---|
Since this issue has been around sometime and no solution in sight, pushing to 2.4 to clear way for 2.3.3.
comment:8 Changed 8 months ago by
I took a look a the btree code, many functions simply don't check for EMPTY case, so they rely on uninitialized values in analyzed BBOXes. I'm trying a fix (would require a testcase, not sure this one would make it).
comment:10 Changed 8 months ago by
Milestone: | PostGIS 2.4.0 → PostGIS 2.3.3 |
---|
See how you like it now Regina. I'll backport to 2.3.3
comment:12 Changed 8 months ago by
Resolution: | fixed |
---|---|
Status: | closed → reopened |
I think there is still something wrong here. I'm testing on
PostgreSQL 9.6.1 on x86_64-w64-mingw32, compiled by gcc.exe (x86_64-win32-seh-rev4, Built by MinGW-W64 project) 4.9.2, 64-bit POSTGIS="2.3.3dev r15449" GEOS="3.6.1-CAPI-1.10.1 r4317" PROJ="Rel. 4.9.1, 04 March 2015" GDAL="GDAL 2.2.0, released 2017/04/28" LIBXML="2.7.8" LIBJSON="0.12" RASTER
The first query returns the right answer now:
SELECT ST_AsText(geom) txt, count(*) FROM ( SELECT 'POINT(0 0)'::geometry geom UNION ALL SELECT 'POINT(0 0)'::geometry geom UNION ALL SELECT 'POINT(0 0)'::geometry geom UNION ALL SELECT 'POINT(0 1)'::geometry geom UNION ALL SELECT 'LINESTRING(0 0,0 1)'::geometry geom UNION ALL SELECT 'GEOMETRYCOLLECTION EMPTY'::geometry geom UNION ALL SELECT 'POINT EMPTY'::geometry geom ) foo GROUP BY geom;
Yields right answer:
txt | count --------------------------+------- POINT(0 0) | 3 LINESTRING(0 0,0 1) | 1 POINT(0 1) | 1 GEOMETRYCOLLECTION EMPTY | 2 (4 rows)
But if I switch the order of geometries, I get a different answer and again POINT(0 0) is not fully aggregated.
SELECT ST_AsText(geom) txt, count(*) FROM ( SELECT 'POINT(0 0)'::geometry geom UNION ALL SELECT 'POINT(0 0)'::geometry geom UNION ALL SELECT 'POINT EMPTY'::geometry geom UNION ALL SELECT 'POINT(0 0)'::geometry geom UNION ALL SELECT 'POINT(0 1)'::geometry geom UNION ALL SELECT 'LINESTRING(0 0,0 1)'::geometry geom UNION ALL SELECT 'GEOMETRYCOLLECTION EMPTY'::geometry geom ) foo GROUP BY geom;
Gives:
txt | count --------------------------+------- POINT(0 0) | 2 POINT EMPTY | 1 POINT(0 0) | 1 GEOMETRYCOLLECTION EMPTY | 1 LINESTRING(0 0,0 1) | 1 POINT(0 1) | 1 (6 rows) Time: 4.227 ms
comment:13 Changed 8 months ago by
Owner: | changed from pramsey to strk |
---|---|
Status: | reopened → new |
to remove ST_AsText out of the equation I did this:
In PostGIS 2.3.2 and PostgreSQL 10beta1 on x86_64-w64-mingw32, compiled by gcc.exe (x86_64-win32-seh-rev1, Built by MinGW-W64 project) 4.8.3, 64-bit POSTGIS="2.4.0dev r15435" GEOS="3.7.0dev-CAPI-1.11.0 8fe2ce6" PROJ="Rel. 4.9.1, 04 March 2015" GDAL="GDAL 2.2.0, released 2017/04/28" LIBXML="2.7.8" LIBJSON="0.12" LIBPROTOBUF="1.2.1" RASTER
In PostgreSQL 9.0.17, compiled by Visual C++ build 1500, 32-bit 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 (procs from 1.5 r7360 need upgrade), this returns: