<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
p.msonormal0, li.msonormal0, div.msonormal0
{mso-style-name:msonormal;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
span.EmailStyle18
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.EmailStyle19
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle20
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle21
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle22
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle23
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle24
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle25
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 90.0pt 72.0pt 90.0pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="FR-CA" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D;mso-fareast-language:EN-US">Any reason why it’s not something more precise then the equality of the bounding boxes? Is it redefinable?<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span lang="FR">De :</span></b><span lang="FR"> postgis-users [mailto:postgis-users-bounces@lists.osgeo.org]
<b>De la part de</b> Regina Obe<br>
<b>Envoyé :</b> 11 septembre 2017 15:59<br>
<b>À :</b> 'PostGIS Users Discussion' <postgis-users@lists.osgeo.org><br>
<b>Objet :</b> Re: [postgis-users] Strange behavior with empty geometries GROUP BY<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D">BTree = operator<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D">Which is bounding box equality<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal" style="margin-left:36.0pt"><b><span lang="EN-US">From:</span></b><span lang="EN-US"> postgis-users [<a href="mailto:postgis-users-bounces@lists.osgeo.org">mailto:postgis-users-bounces@lists.osgeo.org</a>]
<b>On Behalf Of </b>Pierre Racine<br>
<b>Sent:</b> Monday, September 11, 2017 3:54 PM<br>
<b>To:</b> PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>><br>
<b>Subject:</b> Re: [postgis-users] Strange behavior with empty geometries GROUP BY<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D">What is the operator used by GROUP BY?<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal" style="margin-left:36.0pt"><b><span lang="FR">De :</span></b><span lang="FR"> postgis-users [<a href="mailto:postgis-users-bounces@lists.osgeo.org">mailto:postgis-users-bounces@lists.osgeo.org</a>]
<b>De la part de</b> Regina Obe<br>
<b>Envoyé :</b> 11 septembre 2017 15:42<br>
<b>À :</b> 'PostGIS Users Discussion' <<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>><br>
<b>Objet :</b> Re: [postgis-users] Strange behavior with empty geometries GROUP BY<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal" style="margin-left:36.0pt"><o:p> </o:p></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D">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?<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D">I don't know.<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D">----------------------------------------------------------<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D">WITH polys AS (<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D"> SELECT 1 id, ST_GeomFromText('MULTIPOLYGON(((251000 5243597.61702128,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D"> 251038 5243558,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D"> 251056 5243527,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D"> 251057.227272727 5243500,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D"> 251000 5243500,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D"> 251000 5243597.61702128)),<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D"> ((251021 5243690,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D"> 251000 5243681.87096774,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D"> 251000 5243750,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D"> 251250 5243750,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D"> 251250 5243749.86206897,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D"> 251196 5243694,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D"> 251168 5243687,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D"> 251021 5243690)))') geom<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D"> UNION ALL<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D"> SELECT 2 id, ST_GeomFromText('POLYGON((251000 5243681.87096774,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D"> 251021 5243690,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D"> 251168 5243687,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D"> 251196 5243694,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D"> 251250 5243749.86206897,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D"> 251250 5243598.15,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D"> 251167.008064516 5243500,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D"> 251057.227272727 5243500,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D"> 251056 5243527,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D"> 251038 5243558,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D"> 251000 5243597.61702128,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D"> 251000 5243681.87096774))') geom<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D">)
<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D">SELECT BOX2D(geom)<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D">FROM polys;<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D">BOX(251000 5243500,251250 5243750)<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D">BOX(251000 5243500,251250 5243749.86206897)<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:36.0pt"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal" style="margin-left:72.0pt"><b><span lang="EN-US">From:</span></b><span lang="EN-US"> postgis-users [<a href="mailto:postgis-users-bounces@lists.osgeo.org">mailto:postgis-users-bounces@lists.osgeo.org</a>]
<b>On Behalf Of </b>Pierre Racine<br>
<b>Sent:</b> Monday, September 11, 2017 3:08 PM<br>
<b>To:</b> PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>><br>
<b>Subject:</b> Re: [postgis-users] Strange behavior with empty geometries GROUP BY<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D">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?<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"> WITH polys AS (<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"> SELECT 1 id, ST_GeomFromText('MULTIPOLYGON(((251000 5243597.61702128,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"> 251038 5243558,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"> 251056 5243527,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"> 251057.227272727 5243500,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"> 251000 5243500,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"> 251000 5243597.61702128)),<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"> ((251021 5243690,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"> 251000 5243681.87096774,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"> 251000 5243750,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"> 251250 5243750,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"> 251250 5243749.86206897,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"> 251196 5243694,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"> 251168 5243687,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"> 251021 5243690)))') geom<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"> UNION ALL<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"> SELECT 2 id, ST_GeomFromText('POLYGON((251000 5243681.87096774,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"> 251021 5243690,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"> 251168 5243687,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"> 251196 5243694,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"> 251250 5243749.86206897,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"> 251250 5243598.15,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"> 251167.008064516 5243500,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"> 251057.227272727 5243500,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"> 251056 5243527,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"> 251038 5243558,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"> 251000 5243597.61702128,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"> 251000 5243681.87096774))') geom<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D">) SELECT ST_AsText(geom)<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"> FROM polys
<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"> GROUP BY geom;<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D">Pierre<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal" style="margin-left:72.0pt"><b><span lang="FR">De :</span></b><span lang="FR"> postgis-users [<a href="mailto:postgis-users-bounces@lists.osgeo.org">mailto:postgis-users-bounces@lists.osgeo.org</a>]
<b>De la part de</b> Pierre Racine<br>
<b>Envoyé :</b> 29 juin 2017 15:42<br>
<b>À :</b> PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>><br>
<b>Objet :</b> Re: [postgis-users] Strange behavior with empty geometries GROUP BY<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal" style="margin-left:72.0pt"><o:p> </o:p></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span style="color:#1F497D">Much better…<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span style="color:#1F497D"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal" style="margin-left:72.0pt"><b><span lang="FR">De :</span></b><span lang="FR"> postgis-users [<a href="mailto:postgis-users-bounces@lists.osgeo.org">mailto:postgis-users-bounces@lists.osgeo.org</a>]
<b>De la part de</b> Regina Obe<br>
<b>Envoyé :</b> 23 juin 2017 23:26<br>
<b>À :</b> 'PostGIS Users Discussion' <<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>><br>
<b>Objet :</b> Re: [postgis-users] Strange behavior with empty geometries GROUP BY<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal" style="margin-left:72.0pt"><o:p> </o:p></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D">Pierre,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D">Thanks for pointing out the issue. Sandro has fixed the problem<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"><a href="https://trac.osgeo.org/postgis/ticket/3777#comment:17">https://trac.osgeo.org/postgis/ticket/3777#comment:17</a><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D">Can you try with your larger workload. If you happen to have a windows box, 64-bit pre-compiled binaries here:<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"><a href="http://postgis.net/windows_downloads/">http://postgis.net/windows_downloads/</a><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D">(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).<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D">If you have a patched version:<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D">SELECT postgis_full_version();<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D">Your version should have r15450 (or above) for PostGIS 2.4 and r15451 (or above)for PostGIS 2.3.<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D">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<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D">Thanks,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D">Regina<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal" style="margin-left:108.0pt"><b><span lang="EN-US">From:</span></b><span lang="EN-US"> postgis-users [<a href="mailto:postgis-users-bounces@lists.osgeo.org">mailto:postgis-users-bounces@lists.osgeo.org</a>]
<b>On Behalf Of </b>Pierre Racine<br>
<b>Sent:</b> Tuesday, June 20, 2017 5:42 PM<br>
<b>To:</b> 'PostGIS Users Discussion' <<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>><br>
<b>Subject:</b> [postgis-users] Strange behavior with empty geometries GROUP BY<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">Hi,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">When I try to count the occurrence of a number of empty geometries like this:<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">SELECT ST_AsText(geom) txt, count(*)<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">FROM (<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">SELECT 'POINT(0 0)'::geometry geom<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">UNION ALL<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">SELECT 'POINT(0 0)'::geometry geom<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">UNION ALL<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">SELECT 'POINT(0 0)'::geometry geom<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">UNION ALL<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">SELECT 'POINT(0 1)'::geometry geom<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">UNION ALL<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">SELECT 'LINESTRING(0 0,0 1)'::geometry geom<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">UNION ALL<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">SELECT 'GEOMETRYCOLLECTION EMPTY'::geometry geom<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">UNION ALL<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">SELECT 'POINT EMPTY'::geometry geom<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">) foo<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">GROUP BY geom;<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">The 'GEOMETRYCOLLECTION EMPTY' and the 'POINT EMPTY' do not aggregate and I get:<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">txt count<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">POINT(0 0) 2<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">POINT EMPTY 1<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">POINT(0 0) 1<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">LINESTRING(0 0,0 1) 1<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">GEOMETRYCOLLECTION EMPTY 1<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">POINT(0 1) 1<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">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:<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">SELECT ST_AsText(geom) txt, count(*)<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">FROM (<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">SELECT 'POINT(0 0)'::geometry geom<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">UNION ALL<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">SELECT 'POINT(0 0)'::geometry geom<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">UNION ALL<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">SELECT 'POINT(0 1)'::geometry geom<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">UNION ALL<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">SELECT 'LINESTRING(0 0,0 1)'::geometry geom<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">UNION ALL<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">SELECT 'GEOMETRYCOLLECTION EMPTY'::geometry geom<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">UNION ALL<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">SELECT 'POINT EMPTY'::geometry geom<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">) foo<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">GROUP BY geom;<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">Result:<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">txt count<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">POINT(0 0) 2<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">LINESTRING(0 0,0 1) 1<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">POINT EMPTY 2<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">POINT(0 1) 1<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">Any idea why empty geometries aggregation seem so dependent on what others geoms are in the table?<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">Sorry I could not build a more simple example.<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">Thanks,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US">Pierre<o:p></o:p></span></p>
</div>
</body>
</html>