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

[Regina Obe]

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: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>
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/20170623/f4138b0b/attachment.html>