Opened 10 years ago

Closed 5 years ago

Last modified 5 years ago

#893 closed enhancement (fixed)

MySQL Provider upgrade to use ST_INTERSECTS()

Reported by: kshippey Owned by: jng
Priority: major Milestone: 3.8.0
Component: MySQL Provider Version: 3.8.0
Severity: 3 Keywords: ST_INTERSECTS
Cc: External ID:

Description

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.

Attachments (2)

893.patch (17.4 KB ) - added by jng 5 years ago.
Patch to enable full spatial operation capabilities for MySQL >= 5.6
LimitOneSrid.patch (867 bytes ) - added by jng 5 years ago.
Patch to sample only the first SRID in a mysql table

Download all attachments as: .zip

Change History (11)

by jng, 5 years ago

Attachment: 893.patch added

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

comment:1 by jng, 5 years ago

Owner: changed from gregboone to jng

comment:2 by jng, 5 years ago

Resolution: fixed
Status: newclosed

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

comment:3 by jng, 5 years ago

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.

comment:4 by kshippey, 5 years ago

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.

comment:5 by jng, 5 years ago

Resolution: fixed
Status: closedreopened

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.

by jng, 5 years ago

Attachment: LimitOneSrid.patch added

Patch to sample only the first SRID in a mysql table

comment:6 by jng, 5 years ago

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.

comment:7 by kshippey, 5 years ago

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.

comment:8 by jng, 5 years ago

Resolution: fixed
Status: reopenedclosed

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

comment:9 by jng, 5 years ago

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

Note: See TracTickets for help on using tickets.