Highly inefficient MakeValid statement in Mapserver-generated spatial queries

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Highly inefficient MakeValid statement in Mapserver-generated spatial queries

ikeszei

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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Highly inefficient MakeValid statement in Mapserver-generated spatial queries

Seth G-2
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, [hidden email] 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
https://lists.osgeo.org/mailman/listinfo/mapserver-users


_______________________________________________
mapserver-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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

--
twitter: @geographika


On Sat, Sep 7, 2019, at 12:02 PM, [hidden email] 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 mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users

_______________________________________________
mapserver-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Highly inefficient MakeValid statement in Mapserver-generated spatial queries

ikeszei
We have real large geospatial tables.  Not having an option to turn MakeValid off will kill our applications’ performance as indexes are essential. 

For a simple query, the disk reads are 100x more for a query including the MakeValid. Hence, the cpu resources required are multifold too.

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. 

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. 

Any help is appreciated!

Thank you,
Istvan


On 2019. Sep 7., at 23:55, Tamas Szekeres <[hidden email]> wrote:

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 <[hidden email]> 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

--
twitter: @geographika


On Sat, Sep 7, 2019, at 12:02 PM, [hidden email] 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 mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users
_______________________________________________
mapserver-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users

_______________________________________________
mapserver-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Highly inefficient MakeValid statement in Mapserver-generated spatial queries

Seth G-2
Hi Istvan,

Unfortunately you won't be able to turn it off without recompiling the MSSQL driver.
I've added a pull request removing these at https://github.com/mapserver/mapserver/pull/5856
This will be merged into master assuming there are no objections.
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. 
Thanks for reporting this issue,

Seth

--
web:http://geographika.co.uk
twitter: @geographika


On Sun, Sep 8, 2019, at 12:30 AM, Istvan Keszei wrote:

We have real large geospatial tables.  Not having an option to turn MakeValid off will kill our applications’ performance as indexes are essential. 

For a simple query, the disk reads are 100x more for a query including the MakeValid. Hence, the cpu resources required are multifold too.

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. 

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. 

Any help is appreciated!

Thank you,
Istvan


On 2019. Sep 7., at 23:55, Tamas Szekeres <[hidden email]> wrote:
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 <[hidden email]> 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

--
twitter: @geographika


On Sat, Sep 7, 2019, at 12:02 PM, [hidden email] 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 mailing list
_______________________________________________
mapserver-users mailing list


_______________________________________________
mapserver-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Highly inefficient MakeValid statement in Mapserver-generated spatial queries

ikeszei

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?

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.

Much appreciated,

Istvan

 

From: Seth G <[hidden email]>
Sent: Sunday, September 8, 2019 11:06 PM
To: Istvan Keszei <[hidden email]>; Tamas Szekeres <[hidden email]>
Cc: MapserverList OSGEO <[hidden email]>
Subject: Re: [mapserver-users] Highly inefficient MakeValid statement in Mapserver-generated spatial queries

 

Hi Istvan,

 

Unfortunately you won't be able to turn it off without recompiling the MSSQL driver.

I've added a pull request removing these at https://github.com/mapserver/mapserver/pull/5856

This will be merged into master assuming there are no objections.

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. 

Thanks for reporting this issue,

 

Seth

 

--

web:http://geographika.co.uk

twitter: @geographika

 

 

On Sun, Sep 8, 2019, at 12:30 AM, Istvan Keszei wrote:

 

We have real large geospatial tables.  Not having an option to turn MakeValid off will kill our applications’ performance as indexes are essential. 

 

For a simple query, the disk reads are 100x more for a query including the MakeValid. Hence, the cpu resources required are multifold too.

 

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. 

 

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. 

 

Any help is appreciated!

 

Thank you,

Istvan

 

 

On 2019. Sep 7., at 23:55, Tamas Szekeres <[hidden email]> wrote:

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 <[hidden email]> 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

 

--

twitter: @geographika

 

 

On Sat, Sep 7, 2019, at 12:02 PM, [hidden email] 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 mailing list

_______________________________________________

mapserver-users mailing list

 


_______________________________________________
mapserver-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users
Reply | Threaded
Open this post in threaded view
|

Re: Highly inefficient MakeValid statement in Mapserver-generated spatial queries

jratike80
In reply to this post by Seth G-2

Hi,

 

Fortunately not the entire MS4W community is affected, just those who work with MS SQL server.

 

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.

 

-Jukka Rahkonen-

 

Lähettäjä: mapserver-users <[hidden email]> Puolesta [hidden email]
Lähetetty: maanantai 9. syyskuuta 2019 15.20
Vastaanottaja: 'Seth G' <[hidden email]>; 'Tamas Szekeres' <[hidden email]>
Kopio: 'MapserverList OSGEO' <[hidden email]>
Aihe: Re: [mapserver-users] Highly inefficient MakeValid statement in Mapserver-generated spatial queries

 

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?

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.

Much appreciated,

Istvan

 

From: Seth G <[hidden email]>
Sent: Sunday, September 8, 2019 11:06 PM
To: Istvan Keszei <[hidden email]>; Tamas Szekeres <[hidden email]>
Cc: MapserverList OSGEO <[hidden email]>
Subject: Re: [mapserver-users] Highly inefficient MakeValid statement in Mapserver-generated spatial queries

 

Hi Istvan,

 

Unfortunately you won't be able to turn it off without recompiling the MSSQL driver.

I've added a pull request removing these at https://github.com/mapserver/mapserver/pull/5856

This will be merged into master assuming there are no objections.

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. 

Thanks for reporting this issue,

 

Seth

 

--

web:http://geographika.co.uk

twitter: @geographika

 

 

On Sun, Sep 8, 2019, at 12:30 AM, Istvan Keszei wrote:

 

We have real large geospatial tables.  Not having an option to turn MakeValid off will kill our applications’ performance as indexes are essential. 

 

For a simple query, the disk reads are 100x more for a query including the MakeValid. Hence, the cpu resources required are multifold too.

 

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. 

 

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. 

 

Any help is appreciated!

 

Thank you,

Istvan

 

 

On 2019. Sep 7., at 23:55, Tamas Szekeres <[hidden email]> wrote:

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 <[hidden email]> 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

 

--

twitter: @geographika

 

 

On Sat, Sep 7, 2019, at 12:02 PM, [hidden email] 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 mailing list

_______________________________________________

mapserver-users mailing list

 


_______________________________________________
mapserver-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/mapserver-users