<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:0in;
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:0in;
mso-margin-bottom-alt:auto;
margin-left:0in;
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-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.25in 1.0in 1.25in;}
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=EN-US link="#0563C1" vlink="#954F72"><div class=WordSection1><p class=MsoNormal><span style='color:#1F497D'>Pierre,<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span 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 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 style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span 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 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 style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span 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 style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>Seems to me like trading one set of problems for another.<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>Thanks,<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D'>Regina<o:p></o:p></span></p><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal style='margin-left:.5in'><b>From:</b> postgis-users [mailto:postgis-users-bounces@lists.osgeo.org] <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 <postgis-users@lists.osgeo.org><br><b>Subject:</b> Re: [postgis-users] Strange behavior with empty geometries GROUP BY<o:p></o:p></p></div></div><p class=MsoNormal style='margin-left:.5in'><o:p> </o:p></p><p class=MsoNormal style='margin-left:.5in'><span 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:.5in'><span style='color:#1F497D'><o:p> </o:p></span></p><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal style='margin-left:.5in'><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:59<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:.5in'><span lang=FR-CA><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='color:#1F497D'>BTree = operator<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='color:#1F497D'>Which is bounding box equality<o:p></o:p></span></p><p class=MsoNormal style='margin-left:.5in'><span style='color:#1F497D'><o:p> </o:p></span></p><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal style='margin-left:1.0in'><b>From:</b> 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></p></div></div><p class=MsoNormal style='margin-left:1.0in'><o:p> </o:p></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'>What is the operator used by GROUP BY?<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'><o:p> </o:p></span></p><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal style='margin-left:1.0in'><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:1.0in'><span lang=FR-CA><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span 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:1.0in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'>I don't know.<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'>----------------------------------------------------------<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'>WITH polys AS (<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'> SELECT 1 id, ST_GeomFromText('MULTIPOLYGON(((251000 5243597.61702128,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'> 251038 5243558,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'> 251056 5243527,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'> 251057.227272727 5243500,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'> 251000 5243500,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'> 251000 5243597.61702128)),<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'> ((251021 5243690,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'> 251000 5243681.87096774,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'> 251000 5243750,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'> 251250 5243750,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'> 251250 5243749.86206897,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'> 251196 5243694,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'> 251168 5243687,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'> 251021 5243690)))') geom<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'> UNION ALL<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'> SELECT 2 id, ST_GeomFromText('POLYGON((251000 5243681.87096774,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'> 251021 5243690,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'> 251168 5243687,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'> 251196 5243694,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'> 251250 5243749.86206897,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'> 251250 5243598.15,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'> 251167.008064516 5243500,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'> 251057.227272727 5243500,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'> 251056 5243527,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'> 251038 5243558,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'> 251000 5243597.61702128,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'> 251000 5243681.87096774))') geom<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'>) <o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'>SELECT BOX2D(geom)<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'>FROM polys;<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'>BOX(251000 5243500,251250 5243750)<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'>BOX(251000 5243500,251250 5243749.86206897)<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.0in'><span style='color:#1F497D'><o:p> </o:p></span></p><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal style='margin-left:1.5in'><b>From:</b> 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></p></div></div><p class=MsoNormal style='margin-left:1.5in'><o:p> </o:p></p><p class=MsoNormal style='margin-left:1.5in'><span 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:1.5in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'> WITH polys AS (<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'> SELECT 1 id, ST_GeomFromText('MULTIPOLYGON(((251000 5243597.61702128,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'> 251038 5243558,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'> 251056 5243527,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'> 251057.227272727 5243500,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'> 251000 5243500,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'> 251000 5243597.61702128)),<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'> ((251021 5243690,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'> 251000 5243681.87096774,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'> 251000 5243750,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'> 251250 5243750,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'> 251250 5243749.86206897,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'> 251196 5243694,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'> 251168 5243687,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'> 251021 5243690)))') geom<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'> UNION ALL<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'> SELECT 2 id, ST_GeomFromText('POLYGON((251000 5243681.87096774,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'> 251021 5243690,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'> 251168 5243687,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'> 251196 5243694,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'> 251250 5243749.86206897,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'> 251250 5243598.15,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'> 251167.008064516 5243500,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'> 251057.227272727 5243500,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'> 251056 5243527,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'> 251038 5243558,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'> 251000 5243597.61702128,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'> 251000 5243681.87096774))') geom<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'>) SELECT ST_AsText(geom)<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'> FROM polys <o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'> GROUP BY geom;<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'>Pierre<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'><o:p> </o:p></span></p><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal style='margin-left:1.5in'><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:1.5in'><span lang=FR-CA><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=FR-CA style='color:#1F497D'>Much better…<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span lang=FR-CA style='color:#1F497D'><o:p> </o:p></span></p><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal style='margin-left:1.5in'><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:1.5in'><span lang=FR-CA><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'>Pierre,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span 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:1.5in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span 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:1.5in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span 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:1.5in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span 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:1.5in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span 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:1.5in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'>If you have a patched version:<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'>SELECT postgis_full_version();<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span 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:1.5in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span 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:1.5in'><span style='color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'>Thanks,<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'>Regina<o:p></o:p></span></p><p class=MsoNormal style='margin-left:1.5in'><span style='color:#1F497D'><o:p> </o:p></span></p><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal style='margin-left:2.0in'><b>From:</b> 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></p></div></div><p class=MsoNormal style='margin-left:2.0in'><o:p> </o:p></p><p class=MsoNormal style='margin-left:2.0in'>Hi,<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'><o:p> </o:p></p><p class=MsoNormal style='margin-left:2.0in'>When I try to count the occurrence of a number of empty geometries like this:<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'><o:p> </o:p></p><p class=MsoNormal style='margin-left:2.0in'>SELECT ST_AsText(geom) txt, count(*)<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>FROM (<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>SELECT 'POINT(0 0)'::geometry geom<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>UNION ALL<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>SELECT 'POINT(0 0)'::geometry geom<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>UNION ALL<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>SELECT 'POINT(0 0)'::geometry geom<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>UNION ALL<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>SELECT 'POINT(0 1)'::geometry geom<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>UNION ALL<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>SELECT 'LINESTRING(0 0,0 1)'::geometry geom<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>UNION ALL<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>SELECT 'GEOMETRYCOLLECTION EMPTY'::geometry geom<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>UNION ALL<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>SELECT 'POINT EMPTY'::geometry geom<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>) foo<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>GROUP BY geom;<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'><o:p> </o:p></p><p class=MsoNormal style='margin-left:2.0in'>The 'GEOMETRYCOLLECTION EMPTY' and the 'POINT EMPTY' do not aggregate and I get:<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'><o:p> </o:p></p><p class=MsoNormal style='margin-left:2.0in'>txt count<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>POINT(0 0) 2<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>POINT EMPTY 1<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>POINT(0 0) 1<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>LINESTRING(0 0,0 1) 1<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>GEOMETRYCOLLECTION EMPTY 1<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>POINT(0 1) 1<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'><o:p> </o:p></p><p class=MsoNormal style='margin-left:2.0in'>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></p><p class=MsoNormal style='margin-left:2.0in'><o:p> </o:p></p><p class=MsoNormal style='margin-left:2.0in'>SELECT ST_AsText(geom) txt, count(*)<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>FROM (<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>SELECT 'POINT(0 0)'::geometry geom<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>UNION ALL<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>SELECT 'POINT(0 0)'::geometry geom<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>UNION ALL<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>SELECT 'POINT(0 1)'::geometry geom<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>UNION ALL<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>SELECT 'LINESTRING(0 0,0 1)'::geometry geom<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>UNION ALL<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>SELECT 'GEOMETRYCOLLECTION EMPTY'::geometry geom<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>UNION ALL<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>SELECT 'POINT EMPTY'::geometry geom<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>) foo<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>GROUP BY geom;<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'><o:p> </o:p></p><p class=MsoNormal style='margin-left:2.0in'>Result:<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'><o:p> </o:p></p><p class=MsoNormal style='margin-left:2.0in'>txt count<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>POINT(0 0) 2<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>LINESTRING(0 0,0 1) 1<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>POINT EMPTY 2<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'>POINT(0 1) 1<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'><o:p> </o:p></p><p class=MsoNormal style='margin-left:2.0in'>Any idea why empty geometries aggregation seem so dependent on what others geoms are in the table?<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'><o:p> </o:p></p><p class=MsoNormal style='margin-left:2.0in'>Sorry I could not build a more simple example.<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'><o:p> </o:p></p><p class=MsoNormal style='margin-left:2.0in'>Thanks,<o:p></o:p></p><p class=MsoNormal style='margin-left:2.0in'><o:p> </o:p></p><p class=MsoNormal style='margin-left:2.0in'>Pierre<o:p></o:p></p></div></body></html>