<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=us-ascii">
<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;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 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:11.0pt;
font-family:"Calibri",sans-serif;}
span.EmailStyle18
{mso-style-type:personal;
font-family:"Calibri",sans-serif;}
span.EmailStyle19
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
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">Hello All,<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">We use a generic service to store spatial data in a Postgres backend.<o:p></o:p></p>
<p class="MsoNormal">The service defines a generic table structure with a 3D geometry column as follows:<o:p></o:p></p>
<p class="MsoNormal"> geometry(GeometryZ,4326)<o:p></o:p></p>
<p class="MsoNormal">with a spatial index of<o:p></o:p></p>
<p class="MsoNormal" style="text-indent:.5in">"table" gist (geo)<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">However, from a use case perspective we only have 2D data. So the data in the table e.g. looks like this:<o:p></o:p></p>
<p class="MsoNormal" style="text-indent:.5in">LINESTRING Z (24.2062447 47.7438004 nan,24.2072655 47.7446771 nan, …)<o:p></o:p></p>
<p class="MsoNormal">with a bounding box of<o:p></o:p></p>
<p class="MsoNormal" style="text-indent:.5in">BOX3D(-71.14481 42.74781 nan,-71.14431 42.7482 nan)<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">After users had reported slow queries we came to know that the analyze command fails for the table with:<o:p></o:p></p>
<p class="MsoNormal"> NOTICE: no non-null/empty features, unable to compute statistics<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">By looking at the source code it became clear that all samples from the table are considered to have broken geometry, hence statistics creation fails.<o:p></o:p></p>
<p class="MsoNormal">Without debug information available we are not sure were exactly execution fails, but there are two calls to the method which does the actual work, once for 2D and once for ND modes:<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal" style="margin-left:.5in;text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas;color:blue">static</span><span style="font-size:9.5pt;font-family:Consolas;color:black">
</span><span style="font-size:9.5pt;font-family:Consolas;color:blue">void</span><span style="font-size:9.5pt;font-family:Consolas;color:black"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:.5in;text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas;color:black">compute_gserialized_stats(VacAttrStats *</span><span style="font-size:9.5pt;font-family:Consolas;color:gray">stats</span><span style="font-size:9.5pt;font-family:Consolas;color:black">,
AnalyzeAttrFetchFunc </span><span style="font-size:9.5pt;font-family:Consolas;color:gray">fetchfunc</span><span style="font-size:9.5pt;font-family:Consolas;color:black">,<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:.5in;text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas;color:black">
</span><span style="font-size:9.5pt;font-family:Consolas;color:blue">int</span><span style="font-size:9.5pt;font-family:Consolas;color:black">
</span><span style="font-size:9.5pt;font-family:Consolas;color:gray">sample_rows</span><span style="font-size:9.5pt;font-family:Consolas;color:black">,
</span><span style="font-size:9.5pt;font-family:Consolas;color:blue">double</span><span style="font-size:9.5pt;font-family:Consolas;color:black">
</span><span style="font-size:9.5pt;font-family:Consolas;color:gray">total_rows</span><span style="font-size:9.5pt;font-family:Consolas;color:black">)<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:.5in;text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas;color:black">{<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:.5in;text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas;color:black">
</span><span style="font-size:9.5pt;font-family:Consolas;color:green">/* 2D Mode */</span><span style="font-size:9.5pt;font-family:Consolas;color:black"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:.5in;text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas;color:black"> compute_gserialized_stats_mode(</span><span style="font-size:9.5pt;font-family:Consolas;color:gray">stats</span><span style="font-size:9.5pt;font-family:Consolas;color:black">,
</span><span style="font-size:9.5pt;font-family:Consolas;color:gray">fetchfunc</span><span style="font-size:9.5pt;font-family:Consolas;color:black">,
</span><span style="font-size:9.5pt;font-family:Consolas;color:gray">sample_rows</span><span style="font-size:9.5pt;font-family:Consolas;color:black">,
</span><span style="font-size:9.5pt;font-family:Consolas;color:gray">total_rows</span><span style="font-size:9.5pt;font-family:Consolas;color:black">, 2);<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:.5in;text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas;color:black">
</span><span style="font-size:9.5pt;font-family:Consolas;color:green">/* ND Mode */</span><span style="font-size:9.5pt;font-family:Consolas;color:black"><o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:.5in;text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas;color:black"> compute_gserialized_stats_mode(</span><span style="font-size:9.5pt;font-family:Consolas;color:gray">stats</span><span style="font-size:9.5pt;font-family:Consolas;color:black">,
</span><span style="font-size:9.5pt;font-family:Consolas;color:gray">fetchfunc</span><span style="font-size:9.5pt;font-family:Consolas;color:black">,
</span><span style="font-size:9.5pt;font-family:Consolas;color:gray">sample_rows</span><span style="font-size:9.5pt;font-family:Consolas;color:black">,
</span><span style="font-size:9.5pt;font-family:Consolas;color:gray">total_rows</span><span style="font-size:9.5pt;font-family:Consolas;color:black">, 0);<o:p></o:p></span></p>
<p class="MsoNormal" style="margin-left:.5in"><span style="font-size:9.5pt;font-family:Consolas;color:black">}<o:p></o:p></span></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">I assume that invocation for 2D mode is fine, but within ND mode it fails, because the bounding boxes have a Z value of NaN.<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">So far, so good. But what I do not get here is, why do we need the ND mode here at all, as the spatial index is 2D only, as per PostGIS documentation?<o:p></o:p></p>
<p class="MsoNormal">What is the reasoning behind this implementation, and what would be our options to work around that failure, other than writing 0 values as Z ordinates?<o:p></o:p></p>
<p class="MsoNormal">Wouldn’t it be preferable to treat failure of ND mode independent of success of 2D mode?<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal">Thanks in advance,<o:p></o:p></p>
<p class="MsoNormal">Lars<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
</body>
</html>