In MySQL 5.7.6 we’ve done some major spring cleaning within the GIS function namespace. We have deprecated 66 function names and added 13 new aliases. Please see the release notes for a complete list of all the changes. But why have we done this, and what impact does this have for you?
Standardization
GIS is a growing area, and to keep MySQL up to speed we have made the GIS namespace more SQL/MM compliant. SQL/MM is an ISO standard that defines a set of spatial routines and schemas for handling spatial data. As part of this effort we have adapted the standard naming convention where all spatial functions are prefixed with “ST_“. This also means that it is a lot easier to take your queries from other SQL/MM compliant database systems and run them on your MySQL databases.
Implementation Confusion
Almost all of the spatial functions in MySQL had two versions—one version prefixed with “ST_” and one prefixed with “MBR“. The “ST_” prefixed version does a precise calculation on the geometries, while the “MBR” version uses a minimum bounding rectangle (MBR) instead of the exact shape of the geometries. Because of this, the two versions would often give different answers, as seen in the example below:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
mysql> SET @polygon1 = ST_GeomFromText('POLYGON((28 55,21 50,28 44,15 44,15 55,28 55))'); Query OK, 0 rows affected (0.00 sec) mysql> SET @polygon2 = ST_GeomFromText('POLYGON((28 55,21 50,28 44,28 55))'); Query OK, 0 rows affected (0.00 sec) mysql> SELECT MBRContains(@polygon1, @polygon2); +-----------------------------------+ | MBRContains(@polygon1, @polygon2) | +-----------------------------------+ | 1 | +-----------------------------------+ 1 row in set (0.00 sec) mysql> SELECT ST_Contains(@polygon1, @polygon2); +-----------------------------------+ | ST_Contains(@polygon1, @polygon2) | +-----------------------------------+ | 0 | +-----------------------------------+ 1 row in set (0.00 sec) |
In addition to these two function name variants, a third name without any prefix sometimes existed as well. Which of the calculations does this function perform? Precise or with a minimum bounding rectangle? This wasn’t always clear or consistent, so in order to avoid this kind of confusion, all spatial functions without an “ST_” or “MBR” prefix are now deprecated.
However, according to and in compliance with the SQL/MM standard, exceptions to the above rule are the geometry construction functions: Point, LineString, Polygon, MultiPoint, MultiLineString, MultiPolygon and GeometryCollection. They have the same name as their corresponding column type, and will not be deprecated or receive an “ST_” prefix.
Inconsistencies in Error Reporting
Given all the function variants noted above, we also had some inconsistencies when it came to function naming and error reporting. An error within one of the Equals or MBREqual (yes, without the last s!) functions would report an error containing the string “MBREquals”, even though the function MBREquals did not exist! In addition, the GLength function did not follow any of the naming conventions we used. We have now cleaned all of this up so that the function names follow the SQL/MM standard, and the errors reported print the correct function name(s).
Okay, but What Do I Have to Do?
From MySQL 5.7.6 on all of the deprecated functions will raise a warning, and they will later be removed in a future Server release. Thus we strongly encourage you to ensure that all of your applications are using either the “ST_” prefixed function name variants or the “MBR” prefixed versions in MySQL 5.7. If you are unsure which of the prefixed function variants you should replace your current call(s) with, please take a look at the specific warnings produced in each case as they will tell you exactly which functions you should use instead.
We hope that all of this work helps to simplify your MySQL usage, bring us more into standards compliance, and further pave the way for MySQL playing a prominent part in the Open Source GIS database field.
That’s all for now. As always, THANK YOU for using MySQL!