<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=utf-8">
<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:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p.MsoNoSpacing, li.MsoNoSpacing, div.MsoNoSpacing
{mso-style-priority:1;
margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
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:11.0pt;
font-family:"Calibri",sans-serif;}
p.qt-gmail-m8701938780220094206qt-msonormal, li.qt-gmail-m8701938780220094206qt-msonormal, div.qt-gmail-m8701938780220094206qt-msonormal
{mso-style-name:qt-gmail-m_8701938780220094206qt-msonormal;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
span.colour
{mso-style-name:colour;}
span.highlight
{mso-style-name:highlight;}
span.font
{mso-style-name:font;}
span.size
{mso-style-name:size;}
span.Shkpostityyli24
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.Shkpostityyli26
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.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="FI" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><span style="mso-fareast-language:EN-US">Hi,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US">Fortunately not the entire MS4W community is affected, just those who work with MS SQL server.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US">When makevalid will be turned off MapServer admin will have another option to deal with invalid geometries: select data in mapfile with IsValid=true. Then the faulty geometries will
not show on the map but the whole layer will not fail.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US">-Jukka Rahkonen-<o:p></o:p></span></p>
<p class="MsoNormal"><span style="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>Lähettäjä:</b> mapserver-users <mapserver-users-bounces@lists.osgeo.org>
<b>Puolesta </b>ikeszei@yahoo.com<br>
<b>Lähetetty:</b> maanantai 9. syyskuuta 2019 15.20<br>
<b>Vastaanottaja:</b> 'Seth G' <sethg@geographika.co.uk>; 'Tamas Szekeres' <szekerest@gmail.com><br>
<b>Kopio:</b> 'MapserverList OSGEO' <mapserver-users@lists.osgeo.org><br>
<b>Aihe:</b> Re: [mapserver-users] Highly inefficient MakeValid statement in Mapserver-generated spatial queries<o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span lang="EN-US">Is there any chance that an “unstable” version could be released sooner so that we don’t have to wait another 3-6 months for the next release?<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">This performance issue affects the entire MS4W user community for all WMS requests. It would be great if an interim version could be released sooner.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Much appreciated,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Istvan<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="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">From:</span></b><span lang="EN-US"> Seth G <<a href="mailto:sethg@geographika.co.uk">sethg@geographika.co.uk</a>>
<br>
<b>Sent:</b> Sunday, September 8, 2019 11:06 PM<br>
<b>To:</b> Istvan Keszei <<a href="mailto:ikeszei@yahoo.com">ikeszei@yahoo.com</a>>; Tamas Szekeres <<a href="mailto:szekerest@gmail.com">szekerest@gmail.com</a>><br>
<b>Cc:</b> MapserverList OSGEO <<a href="mailto:mapserver-users@lists.osgeo.org">mapserver-users@lists.osgeo.org</a>><br>
<b>Subject:</b> Re: [mapserver-users] Highly inefficient MakeValid statement in Mapserver-generated spatial queries<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<div>
<p class="MsoNormal"><span lang="EN-US">Hi Istvan,<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US">Unfortunately you won't be able to turn it off without recompiling the MSSQL driver.
<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US">I've added a pull request removing these at
<a href="https://github.com/mapserver/mapserver/pull/5856">https://github.com/mapserver/mapserver/pull/5856</a><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US">This will be merged into master assuming there are no objections.
<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US">I assume you are using ms4w as you mention a 4.0.1 release? You'll need to see when a new release of that may be available with the update.
<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US">Thanks for reporting this issue,<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US">Seth<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
</div>
<div id="sig62266145">
<div>
<p class="MsoNormal"><span lang="EN-US">--<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US">web:http://geographika.co.uk<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US">twitter: @geographika<o:p></o:p></span></p>
</div>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US">On Sun, Sep 8, 2019, at 12:30 AM, Istvan Keszei wrote:<o:p></o:p></span></p>
</div>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt" id="qt">
<div>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US">We have real large geospatial tables. Not having an option to turn MakeValid off will kill our applications’ performance as indexes are essential. <o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US">For a simple query, the disk reads are 100x more for a query including the MakeValid. Hence, the cpu resources required are multifold too.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US">Yes, please turn it off or make it optional. Can this be done manually somehow? We have waited long months for 4.0.1 to come out (with the opacity fix) and now this issue prevents the upgrade. <o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US">I see the invalid geometries more of a data maintenance responsibility rather than a query-side-responsibility. I understand for some people this is convenient, so that is why I suggest to make this optional. <o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US">Any help is appreciated!<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US">Thank you,<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US">Istvan<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
</div>
<div>
<div>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US">On 2019. Sep 7., at 23:55, Tamas Szekeres <<a href="mailto:szekerest@gmail.com">szekerest@gmail.com</a>> wrote:<o:p></o:p></span></p>
</div>
</div>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<div>
<div>
<div>
<p class="MsoNormal"><span lang="EN-US">I think we can remove MakeValid from the queries entirely. <o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US">The problem is that if the table contains invalid geometries, the entire query will fail.<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US">Best regards,<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US">Tamas<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
</div>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
</div>
<div>
<div>
<p class="MsoNormal"><span lang="EN-US">Seth G <<a href="mailto:sethg@geographika.co.uk">sethg@geographika.co.uk</a>> ezt írta (időpont: 2019. szept. 7., Szo, 21:35):<o:p></o:p></span></p>
</div>
<blockquote style="border:none;border-left:solid #CCCCCC 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0cm;margin-bottom:5.0pt">
<div>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
</div>
<div>
<div>
<p class="MsoNormal"><span lang="EN-US">Hi,<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US">Good question. This is a fairly new change as part of
<a href="https://github.com/mapserver/mapserver/issues/5781">https://github.com/mapserver/mapserver/issues/5781</a> from April this year.
<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US">In SQL Profiler I seem to get GEOM.STIntersects for WFS requests and GEOM.MakeVaid().STIntersects for WMS.
<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US">MakeValid does appear to stop the index being used. Do you have a link which says this definitively?<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US">Seth<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
</div>
<div id="qt-gmail-m_8701938780220094206sig62266145">
<div>
<p class="MsoNormal"><span lang="EN-US">--<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US">web:<a href="http://geographika.co.uk">http://geographika.co.uk</a><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US">twitter: @geographika<o:p></o:p></span></p>
</div>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US">On Sat, Sep 7, 2019, at 12:02 PM, <a href="mailto:ikeszei@yahoo.com">
ikeszei@yahoo.com</a> wrote:<o:p></o:p></span></p>
</div>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt" id="qt-gmail-m_8701938780220094206qt">
<div>
<p class="qt-gmail-m8701938780220094206qt-msonormal"><span lang="EN-US">Hello,<o:p></o:p></span></p>
<p class="qt-gmail-m8701938780220094206qt-msonormal"><span lang="EN-US"> <o:p></o:p></span></p>
<p class="qt-gmail-m8701938780220094206qt-msonormal"><span lang="EN-US">I noticed that when MapServer issues the query to MS SQL Server, it appends a .MakeValid() tag to the geometry field, which makes data access highly inefficient as no spatial indexes can
be used when the MakeValid() is used. Here is the query that is being produced:<o:p></o:p></span></p>
<p class="qt-gmail-m8701938780220094206qt-msonormal"><span lang="EN-US"> <o:p></o:p></span></p>
<p class="qt-gmail-m8701938780220094206qt-msonormal"><span lang="EN-US">SELECT<o:p></o:p></span></p>
<p class="qt-gmail-m8701938780220094206qt-msonormal" style="text-indent:36.0pt"><span lang="EN-US">convert(nvarchar(max), [label]),<o:p></o:p></span></p>
<p class="qt-gmail-m8701938780220094206qt-msonormal" style="text-indent:36.0pt"><span lang="EN-US">[ogr_geometry],<o:p></o:p></span></p>
<p class="qt-gmail-m8701938780220094206qt-msonormal" style="text-indent:36.0pt"><span lang="EN-US">convert(varchar(36), [ogr_fid])<o:p></o:p></span></p>
<p class="qt-gmail-m8701938780220094206qt-msonormal"><span lang="EN-US">FROM<o:p></o:p></span></p>
<p class="qt-gmail-m8701938780220094206qt-msonormal" style="text-indent:36.0pt"><span lang="EN-US">section<o:p></o:p></span></p>
<p class="qt-gmail-m8701938780220094206qt-msonormal"><span lang="EN-US">WHERE<o:p></o:p></span></p>
<p class="qt-gmail-m8701938780220094206qt-msonormal" style="text-indent:36.0pt"><span lang="EN-US">ogr_geometry.<span class="colour"><b><span style="color:red">MakeValid()</span></b></span>.STIntersects(geometry::STGeomFromText('POLYGON((-10973271.1167343 5605636.0681215,-10963019.0003155
5605636.0681215,-10963019.0003155 5614459.76757417,-10973271.1167343 5614459.76757417,-10973271.1167343 5605636.0681215))',3857)) = 1<o:p></o:p></span></p>
<p class="qt-gmail-m8701938780220094206qt-msonormal"><span lang="EN-US"> <o:p></o:p></span></p>
<p class="qt-gmail-m8701938780220094206qt-msonormal"><span lang="EN-US">Here is the data access string from the map file:<o:p></o:p></span></p>
<p class="qt-gmail-m8701938780220094206qt-msonormal"><span lang="EN-US"> <o:p></o:p></span></p>
<p class="qt-gmail-m8701938780220094206qt-msonormal"><b><span lang="EN-US">DATA "ogr_geometry from section USING UNIQUE ogr_fid USING SRID=3857"</span></b><span lang="EN-US"><o:p></o:p></span></p>
<p class="qt-gmail-m8701938780220094206qt-msonormal"><span lang="EN-US"> <o:p></o:p></span></p>
<p class="qt-gmail-m8701938780220094206qt-msonormal"><span lang="EN-US">Earlier I was using a specific HINT for index usage:<o:p></o:p></span></p>
<p class="qt-gmail-m8701938780220094206qt-msonormal"><span lang="EN-US"> <o:p></o:p></span></p>
<p class="qt-gmail-m8701938780220094206qt-msonormal"><b><span lang="EN-US">DATA "ogr_geometry from section WITH (INDEX(section_ogr_geometry_idx)) USING UNIQUE ogr_fid USING SRID=3857"</span></b><span lang="EN-US"><o:p></o:p></span></p>
<p class="qt-gmail-m8701938780220094206qt-msonormal"><span lang="EN-US"> <o:p></o:p></span></p>
<p class="qt-gmail-m8701938780220094206qt-msonormal"><span lang="EN-US">But since MapServer adds the MakeValid automatically, I am getting the following error:<o:p></o:p></span></p>
<p class="qt-gmail-m8701938780220094206qt-msonormal"><span lang="EN-US"> <o:p></o:p></span></p>
<p class="qt-gmail-m8701938780220094206qt-msonormal"><span class="size"><span lang="EN-US" style="font-size:9.5pt;font-family:Consolas;color:black;background:white">Msg 8635, Level 16, State 9, Line 1</span></span><span lang="EN-US"><o:p></o:p></span></p>
<p class="qt-gmail-m8701938780220094206qt-msonormal"><span class="size"><span lang="EN-US" style="font-size:9.5pt;font-family:Consolas;color:black;background:white">The query processor could not produce a query plan for a query with a spatial index hint. Reason:
Could not find required binary spatial method in a condition. Try removing the index hints or removing SET FORCEPLAN.</span></span><span lang="EN-US"><o:p></o:p></span></p>
<p class="qt-gmail-m8701938780220094206qt-msonormal"><span lang="EN-US"> <o:p></o:p></span></p>
<p class="qt-gmail-m8701938780220094206qt-msonormal"><span lang="EN-US">How do I configure mapserver to NOT ADD the MakeValid to every single one of its queries ?<o:p></o:p></span></p>
<p class="qt-gmail-m8701938780220094206qt-msonormal"><span lang="EN-US"> <o:p></o:p></span></p>
<p class="qt-gmail-m8701938780220094206qt-msonormal"><span lang="EN-US">Any response is much appreciated !<o:p></o:p></span></p>
<p class="qt-gmail-m8701938780220094206qt-msonormal"><span lang="EN-US"> <o:p></o:p></span></p>
<p class="qt-gmail-m8701938780220094206qt-msonormal"><span lang="EN-US">Thanks,<o:p></o:p></span></p>
<p class="qt-gmail-m8701938780220094206qt-msonormal"><span lang="EN-US">Istvan<o:p></o:p></span></p>
<p class="qt-gmail-m8701938780220094206qt-msonormal"><span lang="EN-US"> <o:p></o:p></span></p>
<p class="qt-gmail-m8701938780220094206qt-msonormal"><span lang="EN-US"> <o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US">_______________________________________________<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US">mapserver-users mailing list<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US"><a href="mailto:mapserver-users@lists.osgeo.org">mapserver-users@lists.osgeo.org</a><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US"><a href="https://lists.osgeo.org/mailman/listinfo/mapserver-users">https://lists.osgeo.org/mailman/listinfo/mapserver-users</a><o:p></o:p></span></p>
</div>
</blockquote>
<div>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
</div>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US">_______________________________________________<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US">mapserver-users mailing list<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US"><a href="mailto:mapserver-users@lists.osgeo.org">mapserver-users@lists.osgeo.org</a><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US"><a href="https://lists.osgeo.org/mailman/listinfo/mapserver-users">https://lists.osgeo.org/mailman/listinfo/mapserver-users</a><o:p></o:p></span></p>
</div>
</blockquote>
</div>
</div>
</blockquote>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<div>
<div>
<p class="MsoNormal"><span lang="EN-US">_______________________________________________<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US">mapserver-users mailing list<o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US"><a href="mailto:mapserver-users@lists.osgeo.org">mapserver-users@lists.osgeo.org</a><o:p></o:p></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US"><a href="https://lists.osgeo.org/mailman/listinfo/mapserver-users">https://lists.osgeo.org/mailman/listinfo/mapserver-users</a><o:p></o:p></span></p>
</div>
</div>
</blockquote>
</blockquote>
<div>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
</div>
</div>
</body>
</html>