<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:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
@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;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle26
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.EmailStyle27
{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 style="font-family:Wingdings;color:#1F497D;mso-fareast-language:EN-US">J</span><span style="color:#1F497D;mso-fareast-language:EN-US">
<span lang="EN-US"><o:p></o:p></span></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D;mso-fareast-language:EN-US">Well if you have some geometric aggregators you have to expect that at some point some crazy people will want to GROUP BY or DISTINCT geometries. A simple example (not
crazy at all) is if I want to search for duplicates geometries:<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>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D;mso-fareast-language:EN-US">SELECT geom, count(*) cnt<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D;mso-fareast-language:EN-US">FROM mytable<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D;mso-fareast-language:EN-US">GROUP BY geom<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D;mso-fareast-language:EN-US">HAVING count(*) > 1;<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>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D;mso-fareast-language:EN-US">Now I know it doesn’t work well enough since two different geometries having the same bounding box will look like duplicates. I should probably GROUP BY ST_AsEWKB(geom)
instead:<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>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D;mso-fareast-language:EN-US">SELECT ST_AsEWKB(geom)::geometry, count(*) cnt<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D;mso-fareast-language:EN-US">FROM mytable<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D;mso-fareast-language:EN-US">GROUP BY ST_AsEWKB(geom)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D;mso-fareast-language:EN-US">HAVING count(*) > 1;<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>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D;mso-fareast-language:EN-US">I guess that a good replacement?<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>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D;mso-fareast-language:EN-US">Pierre<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="EN-US">De :</span></b><span lang="EN-US"> postgis-users [mailto:postgis-users-bounces@lists.osgeo.org]
<b>De la part de</b> Regina Obe<br>
<b>Envoyé :</b> 11 septembre 2017 16:32<br>
<b>À :</b> 'PostGIS Users Discussion' <postgis-users@lists.osgeo.org><br>
<b>Cc :</b> 'PostGIS Development Discussion' <postgis-devel@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"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D">Pierre,<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">Jump on the boat of all those folks who want it changed. You are in crowded company.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D">Only reason I think we haven't done it is it will require a lot of testing to see how it breaks peoples code, any performance bottlenecks, and people relying on that odd behavior for well over 15
years.<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">I personally don't care as why anyone would want to group by something as largish as a geometry always puzzled me<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D">And no definition of geometric equality ever satisfied me to the point where I wouldn't be asked questions<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">How come ST_Equals says these are equal but geom::text = geom::text says they are not.<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">Seems to me like trading one set of problems for another.<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">Thanks,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="color:#1F497D">Regina<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 [</span><a href="mailto:postgis-users-bounces@lists.osgeo.org"><span lang="EN-US">mailto:postgis-users-bounces@lists.osgeo.org</span></a><span lang="EN-US">]
<b>On Behalf Of </b>Pierre Racine<br>
<b>Sent:</b> Monday, September 11, 2017 4:20 PM<br>
<b>To:</b> PostGIS Users Discussion <</span><a href="mailto:postgis-users@lists.osgeo.org"><span lang="EN-US">postgis-users@lists.osgeo.org</span></a><span lang="EN-US">><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">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" 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="EN-US">De :</span></b><span lang="EN-US"> postgis-users [</span><a href="mailto:postgis-users-bounces@lists.osgeo.org"><span lang="EN-US">mailto:postgis-users-bounces@lists.osgeo.org</span></a><span lang="EN-US">]
<b>De la part de</b> Regina Obe<br>
<b>Envoyé :</b> 11 septembre 2017 15:59<br>
<b>À :</b> 'PostGIS Users Discussion' <</span><a href="mailto:postgis-users@lists.osgeo.org"><span lang="EN-US">postgis-users@lists.osgeo.org</span></a><span lang="EN-US">><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"><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">BTree = operator<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">Which is bounding box equality<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 [</span><a href="mailto:postgis-users-bounces@lists.osgeo.org"><span lang="EN-US">mailto:postgis-users-bounces@lists.osgeo.org</span></a><span lang="EN-US">]
<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 <</span><a href="mailto:postgis-users@lists.osgeo.org"><span lang="EN-US">postgis-users@lists.osgeo.org</span></a><span lang="EN-US">><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">What is the operator used by GROUP BY?<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="EN-US">De :</span></b><span lang="EN-US"> postgis-users [</span><a href="mailto:postgis-users-bounces@lists.osgeo.org"><span lang="EN-US">mailto:postgis-users-bounces@lists.osgeo.org</span></a><span lang="EN-US">]
<b>De la part de</b> Regina Obe<br>
<b>Envoyé :</b> 11 septembre 2017 15:42<br>
<b>À :</b> 'PostGIS Users Discussion' <</span><a href="mailto:postgis-users@lists.osgeo.org"><span lang="EN-US">postgis-users@lists.osgeo.org</span></a><span lang="EN-US">><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"><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">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: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 don't know.<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">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">)
<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.0pt"><span lang="EN-US" style="color:#1F497D">SELECT BOX2D(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"><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">BOX(251000 5243500,251250 5243750)<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:72.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: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"><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 [</span><a href="mailto:postgis-users-bounces@lists.osgeo.org"><span lang="EN-US">mailto:postgis-users-bounces@lists.osgeo.org</span></a><span lang="EN-US">]
<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 <</span><a href="mailto:postgis-users@lists.osgeo.org"><span lang="EN-US">postgis-users@lists.osgeo.org</span></a><span lang="EN-US">><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:108.0pt"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:108.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:108.0pt"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US" style="color:#1F497D"> WITH polys AS (<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.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:108.0pt"><span lang="EN-US" style="color:#1F497D"> 251038 5243558,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US" style="color:#1F497D"> 251056 5243527,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US" style="color:#1F497D"> 251057.227272727 5243500,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US" style="color:#1F497D"> 251000 5243500,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US" style="color:#1F497D"> 251000 5243597.61702128)),<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US" style="color:#1F497D"> ((251021 5243690,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US" style="color:#1F497D"> 251000 5243681.87096774,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US" style="color:#1F497D"> 251000 5243750,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US" style="color:#1F497D"> 251250 5243750,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US" style="color:#1F497D"> 251250 5243749.86206897,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US" style="color:#1F497D"> 251196 5243694,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US" style="color:#1F497D"> 251168 5243687,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US" style="color:#1F497D"> 251021 5243690)))') geom<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US" style="color:#1F497D"> UNION ALL<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.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:108.0pt"><span lang="EN-US" style="color:#1F497D"> 251021 5243690,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US" style="color:#1F497D"> 251168 5243687,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US" style="color:#1F497D"> 251196 5243694,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US" style="color:#1F497D"> 251250 5243749.86206897,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US" style="color:#1F497D"> 251250 5243598.15,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US" style="color:#1F497D"> 251167.008064516 5243500,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US" style="color:#1F497D"> 251057.227272727 5243500,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US" style="color:#1F497D"> 251056 5243527,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US" style="color:#1F497D"> 251038 5243558,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US" style="color:#1F497D"> 251000 5243597.61702128,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US" style="color:#1F497D"> 251000 5243681.87096774))') geom<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US" style="color:#1F497D">) SELECT ST_AsText(geom)<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US" style="color:#1F497D"> FROM polys
<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US" style="color:#1F497D"> GROUP BY geom;<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span style="color:#1F497D">Pierre<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.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:108.0pt"><b><span lang="FR">De :</span></b><span lang="FR"> postgis-users [</span><a href="mailto:postgis-users-bounces@lists.osgeo.org"><span lang="FR">mailto:postgis-users-bounces@lists.osgeo.org</span></a><span lang="FR">]
<b>De la part de</b> Pierre Racine<br>
<b>Envoyé :</b> 29 juin 2017 15:42<br>
<b>À :</b> PostGIS Users Discussion <</span><a href="mailto:postgis-users@lists.osgeo.org"><span lang="FR">postgis-users@lists.osgeo.org</span></a><span lang="FR">><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:108.0pt"><o:p> </o:p></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span style="color:#1F497D">Much better…<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.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:108.0pt"><b><span lang="FR">De :</span></b><span lang="FR"> postgis-users [</span><a href="mailto:postgis-users-bounces@lists.osgeo.org"><span lang="FR">mailto:postgis-users-bounces@lists.osgeo.org</span></a><span lang="FR">]
<b>De la part de</b> Regina Obe<br>
<b>Envoyé :</b> 23 juin 2017 23:26<br>
<b>À :</b> 'PostGIS Users Discussion' <</span><a href="mailto:postgis-users@lists.osgeo.org"><span lang="FR">postgis-users@lists.osgeo.org</span></a><span lang="FR">><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:108.0pt"><o:p> </o:p></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US" style="color:#1F497D">Pierre,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:108.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:108.0pt"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><a href="https://trac.osgeo.org/postgis/ticket/3777#comment:17"><span lang="EN-US">https://trac.osgeo.org/postgis/ticket/3777#comment:17</span></a><span lang="EN-US" style="color:#1F497D"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:108.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:108.0pt"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><a href="http://postgis.net/windows_downloads/"><span lang="EN-US">http://postgis.net/windows_downloads/</span></a><span lang="EN-US" style="color:#1F497D"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:108.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:108.0pt"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:108.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:108.0pt"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US" style="color:#1F497D">SELECT postgis_full_version();<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:108.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:108.0pt"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:108.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:108.0pt"><span lang="EN-US" style="color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US" style="color:#1F497D">Thanks,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.0pt"><span lang="EN-US" style="color:#1F497D">Regina<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:108.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:144.0pt"><b><span lang="EN-US">From:</span></b><span lang="EN-US"> postgis-users [</span><a href="mailto:postgis-users-bounces@lists.osgeo.org"><span lang="EN-US">mailto:postgis-users-bounces@lists.osgeo.org</span></a><span lang="EN-US">]
<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' <</span><a href="mailto:postgis-users@lists.osgeo.org"><span lang="EN-US">postgis-users@lists.osgeo.org</span></a><span lang="EN-US">><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:144.0pt"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">Hi,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:144.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:144.0pt"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">SELECT ST_AsText(geom) txt, count(*)<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">FROM (<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">SELECT 'POINT(0 0)'::geometry geom<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">UNION ALL<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">SELECT 'POINT(0 0)'::geometry geom<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">UNION ALL<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">SELECT 'POINT(0 0)'::geometry geom<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">UNION ALL<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">SELECT 'POINT(0 1)'::geometry geom<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">UNION ALL<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.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:144.0pt"><span lang="EN-US">UNION ALL<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">SELECT 'GEOMETRYCOLLECTION EMPTY'::geometry geom<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">UNION ALL<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">SELECT 'POINT EMPTY'::geometry geom<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">) foo<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">GROUP BY geom;<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:144.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:144.0pt"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">txt count<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">POINT(0 0) 2<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">POINT EMPTY 1<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">POINT(0 0) 1<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">LINESTRING(0 0,0 1) 1<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">GEOMETRYCOLLECTION EMPTY 1<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">POINT(0 1) 1<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:144.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:144.0pt"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">SELECT ST_AsText(geom) txt, count(*)<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">FROM (<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">SELECT 'POINT(0 0)'::geometry geom<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">UNION ALL<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">SELECT 'POINT(0 0)'::geometry geom<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">UNION ALL<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">SELECT 'POINT(0 1)'::geometry geom<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">UNION ALL<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.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:144.0pt"><span lang="EN-US">UNION ALL<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">SELECT 'GEOMETRYCOLLECTION EMPTY'::geometry geom<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">UNION ALL<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">SELECT 'POINT EMPTY'::geometry geom<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">) foo<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">GROUP BY geom;<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">Result:<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">txt count<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">POINT(0 0) 2<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">LINESTRING(0 0,0 1) 1<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">POINT EMPTY 2<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">POINT(0 1) 1<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:144.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:144.0pt"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:144.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:144.0pt"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">Thanks,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal" style="margin-left:144.0pt"><span lang="EN-US">Pierre<o:p></o:p></span></p>
</div>
</body>
</html>