Strange behavior with empty geometries GROUP BY

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Strange behavior with empty geometries GROUP BY

Pierre Racine-2

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


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Strange behavior with empty geometries GROUP BY

Regina Obe-2

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:[hidden email]] On Behalf Of Pierre Racine
Sent: Tuesday, June 20, 2017 5:42 PM
To: 'PostGIS Users Discussion' <[hidden email]>
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


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Strange behavior with empty geometries GROUP BY

Regina Obe-2
In reply to this post by Pierre Racine-2

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

 

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

 

 

 

From: Regina Obe [mailto:[hidden email]]
Sent: Wednesday, June 21, 2017 8:18 AM
To: 'PostGIS Users Discussion' <[hidden email]>
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 [[hidden email]] On Behalf Of Pierre Racine
Sent: Tuesday, June 20, 2017 5:42 PM
To: 'PostGIS Users Discussion' <[hidden email]>
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


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Strange behavior with empty geometries GROUP BY

Regina Obe-2
In reply to this post by Pierre Racine-2

Pierre,

 

Thanks for pointing out the issue.  Sandro has fixed the problem

 

https://trac.osgeo.org/postgis/ticket/3777#comment:17

 

 

Can you try with your larger workload.  If you happen to have a windows box, 64-bit  pre-compiled binaries here:

 

http://postgis.net/windows_downloads/

 

(I have 32-bit building turned off at moment cause it errors out on the gui tests and haven't had a chance to troubleshoot).

 

 

If you have a patched version:

 

SELECT postgis_full_version();

 

Your version should have  r15450 (or above) for PostGIS 2.4 and r15451 (or above)for PostGIS 2.3.

 

POSTGIS="2.3.3dev r15451" GEOS="3.6.1-CAPI-1.10.1 r4317" PROJ="Rel. 4.9.1, 04 March 2015" GDAL="GDAL 2.1.3, released 2017/20/01" LIBXML="2.7.8" LIBJSON="0.12" RASTER

 

Thanks,

Regina

 

From: postgis-users [mailto:[hidden email]] On Behalf Of Pierre Racine
Sent: Tuesday, June 20, 2017 5:42 PM
To: 'PostGIS Users Discussion' <[hidden email]>
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


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Strange behavior with empty geometries GROUP BY

Pierre Racine-2

Much better…

 

De : postgis-users [mailto:[hidden email]] De la part de Regina Obe
Envoyé : 23 juin 2017 23:26
À : 'PostGIS Users Discussion' <[hidden email]>
Objet : Re: [postgis-users] Strange behavior with empty geometries GROUP BY

 

Pierre,

 

Thanks for pointing out the issue.  Sandro has fixed the problem

 

https://trac.osgeo.org/postgis/ticket/3777#comment:17

 

 

Can you try with your larger workload.  If you happen to have a windows box, 64-bit  pre-compiled binaries here:

 

http://postgis.net/windows_downloads/

 

(I have 32-bit building turned off at moment cause it errors out on the gui tests and haven't had a chance to troubleshoot).

 

 

If you have a patched version:

 

SELECT postgis_full_version();

 

Your version should have  r15450 (or above) for PostGIS 2.4 and r15451 (or above)for PostGIS 2.3.

 

POSTGIS="2.3.3dev r15451" GEOS="3.6.1-CAPI-1.10.1 r4317" PROJ="Rel. 4.9.1, 04 March 2015" GDAL="GDAL 2.1.3, released 2017/20/01" LIBXML="2.7.8" LIBJSON="0.12" RASTER

 

Thanks,

Regina

 

From: postgis-users [[hidden email]] On Behalf Of Pierre Racine
Sent: Tuesday, June 20, 2017 5:42 PM
To: 'PostGIS Users Discussion' <[hidden email]>
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


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Loading...