MySQL Provider upgrade to use ST_INTERSECTS()

[kshippey]

The MySQL FDO Provider in version 3.8.0 uses the function MBR_Intersects() on line 277 of \MySQL\Fdo\FdoRdbmsMySqlFilterProcessor.cpp . The function ST_Intersects() is available in MySQL 5.6 and offers more geometric precision for lines and polygons than MBR.

[jng]

Patch to enable full spatial operation capabilities for MySQL >= 5.6

[jng]

In 7856:

Implement the full suite of spatial operations for the MySQL FDO provider if the MySQL version is >= 5.6

New capability tests have been added to verify the appropriate set of supported operations is returned based on the MySQL version.

Fixes #893

[jng]

In 7858:

Merged revision(s) 7856 from trunk:
Implement the full suite of spatial operations for the MySQL FDO provider if the MySQL version is >= 5.6

New capability tests have been added to verify the appropriate set of supported operations is returned based on the MySQL version.

Fixes #893
........

This merge removes some C++11-isms which were present in the original commit being merged from as 4.1 branch does not mandate the use of a C++11-compiliant compiler like trunk does.

[kshippey]

A pseudo-bug on this MySQL Provider update:

It appears that before querying against the spatial operation, there is an initial query to obtain the SRID for all rows of the table. For large MySQL spatial tables, this initial SRID query is a very expensive operation that ultimately renders it unusable above a certain threshold, perhaps 1 million rows, due to timeout. Unfortunately for use on a dynamic MySQL table, that is not a very large threshold. Even for smaller tables this is an unnecessary query without any applied LIMIT.

One improvement might be to call the GetSRID() with a LIMIT to just one single row, and assume the rest of the table follows suit. Once past that SRID call, the spatial index in MySQL is very effective at returning relevant results even on a very large dataset.

[jng]

Yeah, I'd say this is a bug. Other RDMBS providers get SRID from sampling the first row (I know SQL Server does this), so we should do the same here.

[jng]

Patch to sample only the first SRID in a mysql table

[jng]

Could you give this patched MySQL provider a go? Use with 64-bit MGOS 3.1.2

​http://download.osgeo.org/mapguide/patches/MySQLProvider_MGOS312/893_MySQLProvider_LimitOneSrid.zip

This puts a limit 1 on any SRID sampling SQL query. Let me know if this improves the situation.

[kshippey]

Quick turnaround and nicely done!

Previously, the MySQL table where I was noticing the most significant problem was taking over 600 seconds to query the SRID for the whole table. This new patch with a "LIMIT 1" is now refreshing that same layer as quickly as any other table or data file, so it appears entirely resolved now.

[jng]

In 7959:

Add "limit 1" for SRID sampling query in MySQL provider when determining the spatial context of a geometric column, the resulting query reader is only going to be read once anyway.

Fixes #893 again

[jng]

In 7960:

Merged revision(s) 7959 from branches/4.1:

Add "limit 1" for SRID sampling query in MySQL provider when determining the spatial context of a geometric column, the resulting query reader is only going to be read once anyway.

Fixes #893 again