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

[Pierre Racine]

I get a similar bug but now with full pledged geometries. Those two polygons, though very different, GROUP BY together. Which mysterious PostGIS operator is used by GROUP BY and make those two polygons equivalent?

  WITH polys AS (
  SELECT 1 id, ST_GeomFromText('MULTIPOLYGON(((251000 5243597.61702128,
                                               251038 5243558,
                                               251056 5243527,
                                               251057.227272727 5243500,
                                               251000 5243500,
                                               251000 5243597.61702128)),
                                             ((251021 5243690,
                                               251000 5243681.87096774,
                                               251000 5243750,
                                               251250 5243750,
                                               251250 5243749.86206897,
                                               251196 5243694,
                                               251168 5243687,
                                               251021 5243690)))') geom
  UNION ALL
  SELECT 2 id, ST_GeomFromText('POLYGON((251000 5243681.87096774,
                                         251021 5243690,
                                         251168 5243687,
                                         251196 5243694,
                                         251250 5243749.86206897,
                                         251250 5243598.15,
                                         251167.008064516 5243500,
                                         251057.227272727 5243500,
                                         251056 5243527,
                                         251038 5243558,
                                         251000 5243597.61702128,
                                         251000 5243681.87096774))') geom
) SELECT ST_AsText(geom)
  FROM polys
  GROUP BY geom;

Pierre

De : postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] De la part de Pierre Racine
Envoyé : 29 juin 2017 15:42
À : PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Objet : Re: [postgis-users] Strange behavior with empty geometries GROUP BY

Much better...

De : postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] De la part de Regina Obe
Envoyé : 23 juin 2017 23:26
À : 'PostGIS Users Discussion' <postgis-users at lists.osgeo.org<mailto:postgis-users at lists.osgeo.org>>
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 [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/20170911/a661024e/attachment.html>