[postgis-users] Strange behavior with empty geometries GROUP BY

[Regina Obe]

For completeness, I ticketed this issue.  Feel free to add to it.

 

https://trac.osgeo.org/postgis/ticket/3777

 

 

 

From: Regina Obe [mailto:lr at pcorp.us] 
Sent: Wednesday, June 21, 2017 8:18 AM
To: 'PostGIS Users Discussion' <postgis-users at lists.osgeo.org>
Subject: RE: [postgis-users] Strange behavior with empty geometries GROUP BY

 

Pierre,

 

Very interesting.  Does seem to be a bug somewhere.

Seems almost like in one case the POINT(0 0) is getting a box and in other
cases it's not.  Perhaps same thing is happening with POINT EMPTY.

I know we had a bug a while back where all the points were getting cached
boxes.  This might be left over from that issue when we fixed that is my
only guess.

 

Luckily I still have a server (about to destroy) running PostGIS 1.5.5 which
should pre-date this bug and gave this query a try and it gave the below
output. 

I think because there was a time way back for some reason POINT(0 0 ) was  a
stand in for POINT EMPTY and ST_AsText couldn't represent POINT EMPTY so
output GEOMETRYCOLLECTION EMPTY.

Thus you see 2 instances of GEOMETRY COLLECTION (one presumably aggregating
the true GEOMETRYCOLLECTION EMPTY and another aggregating the POINT(0 0)
with the POINT EMPTY.

Perhaps we've got bits of our translation somewhere that sort of still
thinks that at whim.

 

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;

 

Output: 

 

           txt            | count

--------------------------+-------

GEOMETRYCOLLECTION EMPTY |     4

LINESTRING(0 0,0 1)      |     1

GEOMETRYCOLLECTION EMPTY |     1

POINT(0 1)               |     1

(4 rows)

 

Flash back to the present PostGIS 2.3/PostGIS 2.4 which is what you are
seeing.

Right now EMPTY's should have null boxes and POINTS and 2 point lines should
have no boxes, so they are aggregated the same.

Thus the answer should be along lines of below query.

----

 

I would have expected the result to be something along the lines of:

 

SELECT ST_AsText(geom) txt, count(*)

FROM (

SELECT Box2D('POINT(0 0)'::geometry)::geometry AS geom

UNION ALL

SELECT Box2D('POINT(0 0)'::geometry)::geometry  AS  geom

UNION ALL

SELECT Box2D('POINT(0 0)'::geometry)::geometry  AS  geom

UNION ALL

SELECT Box2D('POINT(0 1)'::geometry)::geometry  AS  geom

UNION ALL

SELECT Box2D('LINESTRING(0 0,0 1)'::geometry)::geometry  AS geom

UNION ALL

SELECT Box2D('GEOMETRYCOLLECTION EMPTY'::geometry)  AS geom

UNION ALL

SELECT Box2D('POINT EMPTY'::geometry)  AS geom

) foo

GROUP BY geom;

 

 

And as you pointed out, it is not.

 

         txt         | count

---------------------+-------

POINT(0 0)          |     3

LINESTRING(0 0,0 1) |     1

POINT(0 1)          |     1

NULL                |     2

(4 rows)

 

Don't know what to say Pierre.  You've found a gremlin lurking in the woods.

 

Regina

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf
Of Pierre Racine
Sent: Tuesday, June 20, 2017 5:42 PM
To: 'PostGIS Users Discussion' <postgis-users at lists.osgeo.org
<mailto:postgis-users at lists.osgeo.org> >
Subject: [postgis-users] Strange behavior with empty geometries GROUP BY

 

Hi,

 

When I try to count the occurrence of a number of empty geometries like
this:

 

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;

 

The 'GEOMETRYCOLLECTION EMPTY' and the 'POINT EMPTY' do not aggregate and I
get:

 

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

 

If I remove any geometry other than the two EMPTY ones like this (I removed
the first one 'POINT(0 0)'), the two EMPTY geometries DO aggregate:

 

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 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;

 

Result:

 

txt                                          count

POINT(0 0)                          2

LINESTRING(0 0,0 1)       1

POINT EMPTY                    2

POINT(0 1)                          1

 

Any idea why empty geometries aggregation seem so dependent on what others
geoms are in the table?

 

Sorry I could not build a more simple example.

 

Thanks,

 

Pierre

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170621/57f073c8/attachment.html>