<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; line-break: after-white-space;" class="">Several questions here...<br class=""><div><br class=""><blockquote type="cite" class=""><div class="">On Jan 31, 2019, at 9:04 AM, Ditzel, Lars <<a href="mailto:lars.ditzel@here.com" class="">lars.ditzel@here.com</a>> wrote:</div><br class="Apple-interchange-newline"><div class=""><div class="WordSection1" style="page: WordSection1; caret-color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none;"><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class="">Hello All,<o:p class=""></o:p></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><o:p class=""> </o:p></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class="">We use a generic service to store spatial data in a Postgres backend.<o:p class=""></o:p></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class="">The service defines a generic table structure with a 3D geometry column as follows:<o:p class=""></o:p></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""> geometry(GeometryZ,4326)<o:p class=""></o:p></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class="">with a spatial index of<o:p class=""></o:p></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif; text-indent: 0.5in;" class="">"table" gist (geo)<o:p class=""></o:p></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><o:p class=""> </o:p></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class="">However, from a use case perspective we only have 2D data. So the data in the table e.g. looks like this:<o:p class=""></o:p></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif; text-indent: 0.5in;" class="">LINESTRING Z (24.2062447 47.7438004 nan,24.2072655 47.7446771 nan, …)<o:p class=""></o:p></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class="">with a bounding box of<o:p class=""></o:p></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif; text-indent: 0.5in;" class="">BOX3D(-71.14481 42.74781 nan,-71.14431 42.7482 nan)<o:p class=""></o:p></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><o:p class=""> </o:p></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class="">After users had reported slow queries we came to know that the analyze command fails for the table with:<o:p class=""></o:p></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""> NOTICE: no non-null/empty features, unable to compute statistics<o:p class=""></o:p></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><o:p class=""> </o:p></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class="">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 class=""></o:p></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class="">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 class=""></o:p></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><o:p class=""> </o:p></div><div style="margin: 0in 0in 0.0001pt 0.5in; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><span style="font-size: 9.5pt; font-family: Consolas; color: blue;" class="">static</span><span style="font-size: 9.5pt; font-family: Consolas;" class=""><span class="Apple-converted-space"> </span></span><span style="font-size: 9.5pt; font-family: Consolas; color: blue;" class="">void</span><span style="font-size: 9.5pt; font-family: Consolas;" class=""><o:p class=""></o:p></span></div><div style="margin: 0in 0in 0.0001pt 0.5in; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><span style="font-size: 9.5pt; font-family: Consolas;" class="">compute_gserialized_stats(VacAttrStats *</span><span style="font-size: 9.5pt; font-family: Consolas; color: gray;" class="">stats</span><span style="font-size: 9.5pt; font-family: Consolas;" class="">, AnalyzeAttrFetchFunc<span class="Apple-converted-space"> </span></span><span style="font-size: 9.5pt; font-family: Consolas; color: gray;" class="">fetchfunc</span><span style="font-size: 9.5pt; font-family: Consolas;" class="">,<o:p class=""></o:p></span></div><div style="margin: 0in 0in 0.0001pt 0.5in; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><span style="font-size: 9.5pt; font-family: Consolas;" class=""> <span class="Apple-converted-space"> </span></span><span style="font-size: 9.5pt; font-family: Consolas; color: blue;" class="">int</span><span style="font-size: 9.5pt; font-family: Consolas;" class=""><span class="Apple-converted-space"> </span></span><span style="font-size: 9.5pt; font-family: Consolas; color: gray;" class="">sample_rows</span><span style="font-size: 9.5pt; font-family: Consolas;" class="">,<span class="Apple-converted-space"> </span></span><span style="font-size: 9.5pt; font-family: Consolas; color: blue;" class="">double</span><span style="font-size: 9.5pt; font-family: Consolas;" class=""><span class="Apple-converted-space"> </span></span><span style="font-size: 9.5pt; font-family: Consolas; color: gray;" class="">total_rows</span><span style="font-size: 9.5pt; font-family: Consolas;" class="">)<o:p class=""></o:p></span></div><div style="margin: 0in 0in 0.0001pt 0.5in; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><span style="font-size: 9.5pt; font-family: Consolas;" class="">{<o:p class=""></o:p></span></div><div style="margin: 0in 0in 0.0001pt 0.5in; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><span style="font-size: 9.5pt; font-family: Consolas;" class=""> <span class="Apple-converted-space"> </span></span><span style="font-size: 9.5pt; font-family: Consolas; color: green;" class="">/* 2D Mode */</span><span style="font-size: 9.5pt; font-family: Consolas;" class=""><o:p class=""></o:p></span></div><div style="margin: 0in 0in 0.0001pt 0.5in; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><span style="font-size: 9.5pt; font-family: Consolas;" class=""> compute_gserialized_stats_mode(</span><span style="font-size: 9.5pt; font-family: Consolas; color: gray;" class="">stats</span><span style="font-size: 9.5pt; font-family: Consolas;" class="">,<span class="Apple-converted-space"> </span></span><span style="font-size: 9.5pt; font-family: Consolas; color: gray;" class="">fetchfunc</span><span style="font-size: 9.5pt; font-family: Consolas;" class="">,<span class="Apple-converted-space"> </span></span><span style="font-size: 9.5pt; font-family: Consolas; color: gray;" class="">sample_rows</span><span style="font-size: 9.5pt; font-family: Consolas;" class="">,<span class="Apple-converted-space"> </span></span><span style="font-size: 9.5pt; font-family: Consolas; color: gray;" class="">total_rows</span><span style="font-size: 9.5pt; font-family: Consolas;" class="">, 2);<o:p class=""></o:p></span></div><div style="margin: 0in 0in 0.0001pt 0.5in; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><span style="font-size: 9.5pt; font-family: Consolas;" class=""> <span class="Apple-converted-space"> </span></span><span style="font-size: 9.5pt; font-family: Consolas; color: green;" class="">/* ND Mode */</span><span style="font-size: 9.5pt; font-family: Consolas;" class=""><o:p class=""></o:p></span></div><div style="margin: 0in 0in 0.0001pt 0.5in; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><span style="font-size: 9.5pt; font-family: Consolas;" class=""> compute_gserialized_stats_mode(</span><span style="font-size: 9.5pt; font-family: Consolas; color: gray;" class="">stats</span><span style="font-size: 9.5pt; font-family: Consolas;" class="">,<span class="Apple-converted-space"> </span></span><span style="font-size: 9.5pt; font-family: Consolas; color: gray;" class="">fetchfunc</span><span style="font-size: 9.5pt; font-family: Consolas;" class="">,<span class="Apple-converted-space"> </span></span><span style="font-size: 9.5pt; font-family: Consolas; color: gray;" class="">sample_rows</span><span style="font-size: 9.5pt; font-family: Consolas;" class="">,<span class="Apple-converted-space"> </span></span><span style="font-size: 9.5pt; font-family: Consolas; color: gray;" class="">total_rows</span><span style="font-size: 9.5pt; font-family: Consolas;" class="">, 0);<o:p class=""></o:p></span></div><div style="margin: 0in 0in 0.0001pt 0.5in; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><span style="font-size: 9.5pt; font-family: Consolas;" class="">}<o:p class=""></o:p></span></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><o:p class=""> </o:p></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class="">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 class=""></o:p></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><o:p class=""> </o:p></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class="">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?</div></div></div></blockquote><div><blockquote type="cite" class=""><div class="WordSection1" style="page: WordSection1;"><div class="" style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;">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 class=""></o:p></div></div></blockquote><div class=""><div class="WordSection1" style="page: WordSection1;"><div class="" style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;"><br class=""></div></div></div></div><div>We don’t “need” it per se, but the stats are gathered per-column, not per-operator, so we have to gather stats suitable for all the operators that might be called on it. In theory we could only gather an n-d histogram, but in practice having separate 2d stats works “better”. I cannot remember why. </div><div><br class=""></div><div>Working around… yeah, having the z ordinates be valid numbers certainly wouldn’t hurt. I cannot honestly thing of another way. Just store 2D features? Why have the Z stored if you’re just storing junk in it?</div><br class=""><blockquote type="cite" class=""><div class="WordSection1" style="page: WordSection1; caret-color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none;"><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><o:p class=""></o:p></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><span style="font-size: 11pt;" class="">Wouldn’t it be preferable to treat failure of ND mode independent of success of 2D mode?</span></div></div></blockquote><div><br class=""></div><div>Yes, I think that’s a reasonable enhancement. There are also folks with ZM shape files where the app has filled in the values with REALLY HUGE NUMBER, and that too stymies the stats gatherer.</div><div><br class=""></div><div>ATB,</div><div><br class=""></div><div>P.</div><br class=""><blockquote type="cite" class=""><div class="WordSection1" style="page: WordSection1; caret-color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none;"><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><o:p class=""></o:p></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><o:p class=""> </o:p></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class="">Thanks in advance,<o:p class=""></o:p></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class="">Lars<o:p class=""></o:p></div><div style="margin: 0in 0in 0.0001pt; font-size: 11pt; font-family: Calibri, sans-serif;" class=""><o:p class=""> </o:p></div></div><span style="caret-color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none; float: none; display: inline !important;" class="">_______________________________________________</span><br style="caret-color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none;" class=""><span style="caret-color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none; float: none; display: inline !important;" class="">postgis-devel mailing list</span><br style="caret-color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none;" class=""><a href="mailto:postgis-devel@lists.osgeo.org" style="color: rgb(149, 79, 114); text-decoration: underline; font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px;" class="">postgis-devel@lists.osgeo.org</a><br style="caret-color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration: none;" class=""><a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" style="color: rgb(149, 79, 114); text-decoration: underline; font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px;" class="">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a></blockquote></div><br class=""></body></html>