GIS Functions
Contents |
[edit] Introduction
While MySQL already provides some functionality to store and operate on geospatial data, the functionality leaves quite a lot to be desired and is far from providing full OpenGIS compatibility.
Most notably is that all functions that query spatial data only operate on MBRs (minimum bounding rectangles), to simplify the operations.
Thanks to MySQL Developer Alexey "Holyfoot" Botchkov from Izhevsk, Russia, some of the spatial relation functions like INTERSECTS and WITHIN now work in the way they are described by OpenGIS and not by using MBR's as it used to be. He has been working on improving the GIS functionality as a side project and the work has now reached a level at which he is ready to give it public testing and solicit feedback about it.
Currently these new features have not been slated for inclusion in any upcoming MySQL major release. But the more feedback and testing this code receives, the faster it will reach a maturity level that makes it a potential feature candidate for new versions of MySQL.
[edit] References
This work is documented as WorkLog task WL#1326 "Precise spatial operations", which is a subtask of WL#2377 "Add all missing GIS features to MySQL" - feel free to review and comment on these specifications and make sure to test the new functionality!
In addition to improving already existing functionality, Holyfoot also implemented some new precise geospatial functions - the following functions are available now and use precise operations instead of MBRs.
- BUFFER(g1 geometry, d numeric) returns a Geometry defined by buffering a distance d around g1 where d is the distance units for the Spatial Reference of g1
- DIFFERENCE(g1 geometry, g2 geometry) returns a Geometry that is the closure of the set difference of g1 and g2
- DISTANCE(g1 geometry, g2 geometry) returns distance between g1 and g2
- INTERSECTION(g1 geometry, g2 geometry) returns a Geometry that is the set intersection of g1 and g2
- SYMDIFFERENCE(g1 geometry, g2 geometry) returns a Geometry that is the closure of the set symmetric difference of g1 and g2 (logical XOR of space)
- UNION(g1 geometry, g2 geometry) returns a Geometry that is the set union of g1 and g2
Note: The previous existing functions have been prefixed with MBR, to indicate the difference in operation. For example, the original Intersects() function is now called MBRIntersects()
[edit] Download
[edit] Binary packages
On 2007-10-29 a first batch of preview builds has been released for a number of platforms (Linux, Mac OS X, Solaris, Windows). The packages are available for download from http://downloads.mysql.com/forge/mysql-5.1.23-beta-GIS/
Note: these packages still include the bugs reported below! Currently, you need to compile from the source tree to work around these.
[edit] Sources
The source tree (based on the MySQL 5.1 code base) is now available from our public BitKeeper trees at http://mysql.bkbits.net/ - please consult the reference manual for more information on how to build a MySQL binary from a source tree.
[edit] Known/reported Bugs
If you discovered and reported a bug in the new functionality, please add it to the list below, to avoid duplicate reports by other users:
-
Bug#31753: Buffer/area functions only return first row of set -
Bug#32032: Contains() does not work on MultiPolygons, may force a disconnect and/or result in extremely long query times. -
Bug#32100: contains, intersects functions never return. Query disconnects or times out. -
Bug#33035: Intersection function returns 'Function doesn't exist' error
Note: If a bug has been striked through, it means that is has been fixed and commited to the source tree already. However, the latest preview binaries don't contain this fix yet! The list of known bugs will be reset, once a new preview release has been published.
[edit] Contact
To discuss the new GIS functionality, please use our GIS Forum and help us by Reporting Bugs at our public Bug Database! For convenience, please tag your Bug reports with "openGIS extensions".