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

Mon Sep 11 12:58:53 PDT 2017

BTree = operator

Which is bounding box equality

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf
Of Pierre Racine
Sent: Monday, September 11, 2017 3:54 PM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Subject: Re: [postgis-users] Strange behavior with empty geometries GROUP BY

What is the operator used by GROUP BY?

De : postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] De la part
de Regina Obe
Envoyé : 11 septembre 2017 15:42
À : '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

Hmm interesting.  I was about to say they probably have the same bounding
box but they don't quite according to box2d.  Perhaps our box2d returns
float4 but our internal box is double precision?

I don't know.

----------------------------------------------------------

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 BOX2D(geom)

FROM polys;

BOX(251000 5243500,251250 5243750)

BOX(251000 5243500,251250 5243749.86206897)

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf
Of Pierre Racine
Sent: Monday, September 11, 2017 3:08 PM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org
<mailto:postgis-users at lists.osgeo.org> >
Subject: Re: [postgis-users] Strange behavior with empty geometries GROUP BY

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
<mailto: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/3fe18c23/attachment.html>