[mapserver-users] Highly inefficient MakeValid statement in Mapserver-generated spatial queries

[Tamas Szekeres]

I think we can remove MakeValid from the queries entirely.
The problem is that if the table contains invalid geometries, the entire
query will fail.

Best regards,

Tamas


Seth G <sethg at geographika.co.uk> ezt írta (időpont: 2019. szept. 7., Szo,
21:35):

> Hi,
>
> Good question. This is a fairly new change as part of
> https://github.com/mapserver/mapserver/issues/5781 from April this year.
> In SQL Profiler I seem to get GEOM.STIntersects for WFS requests and
> GEOM.MakeVaid().STIntersects for WMS.
> MakeValid does appear to stop the index being used. Do you have a link
> which says this definitively?
>
> Seth
>
> --
> web:http://geographika.co.uk
> twitter: @geographika
>
>
> On Sat, Sep 7, 2019, at 12:02 PM, ikeszei at yahoo.com wrote:
>
> Hello,
>
>
>
> 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:
>
>
>
> SELECT
>
> convert(nvarchar(max), [label]),
>
> [ogr_geometry],
>
> convert(varchar(36), [ogr_fid])
>
> FROM
>
> section
>
> WHERE
>
> ogr_geometry.*MakeValid()*.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
>
>
>
> Here is the data access string from the map file:
>
>
>
> *DATA "ogr_geometry from section USING UNIQUE ogr_fid USING SRID=3857"*
>
>
>
> Earlier I was using a specific HINT for index usage:
>
>
>
> *DATA "ogr_geometry from section WITH (INDEX(section_ogr_geometry_idx))
> USING UNIQUE ogr_fid USING SRID=3857"*
>
>
>
> But since MapServer adds the MakeValid automatically, I am getting the
> following error:
>
>
>
> Msg 8635, Level 16, State 9, Line 1
>
> 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.
>
>
>
> How do I configure mapserver to NOT ADD the MakeValid to every single one
> of its queries ?
>
>
>
> Any response is much appreciated !
>
>
>
> Thanks,
>
> Istvan
>
>
>
>
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/mapserver-users
>
>
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/mapserver-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20190907/b4a862ce/attachment.html>