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.
These new features have been slated for inclusion in an upcoming MySQL milestone release. The more feedback and testing this code receives, the faster it will be considered stable enough to be merged into the trunk.
[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] Source/Binary packages
Current snapshot builds of the MySQL GIS preview code can be obtained from the MySQL Server Snapshots page at http://labs.mysql.com/ - these packages are updated and published automatically every time the code base has changed (and passes the tests successfully).
[edit] Older preview releases
On 2009-07-29, a new batch of GIS snapshot binaries for a limited number of platforms (generic binary tarballs/ZIP archives for Linux, Windows, Mac OS X and Solaris) including the source packages (based on MySQL 5.1.35) was published at http://downloads.mysql.com/forge/mysql-5.1.35-gis/
This release fixed the bugs mentioned below and included some improvements to the GIS functionality.
The following bugs were fixed in this release:
-
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 -
Bug#40874: Buffer function doesn't work with LINESTRING -
Bug#41481: Buffer function never returns, cpu 100%, system locks up -
Bug#43493: union function returns polygon not multipolygon for non-intersecting polygons -
Bug#44753: nan error in union function
The very first batch of preview builds was released on 2007-10-29 for a number of platforms (Linux, Mac OS X, Solaris, Windows). The packages are still available for download (from http://downloads.mysql.com/forge/mysql-5.1.23-beta-GIS/), but should not be used for testing anymore as they include bugs listed above.
[edit] Source code
The source tree (based on the MySQL 5.1 code base) is available from our public Bazaar trees at https://code.launchpad.net/mysql-server .
You can check out a local copy of the source tree by running the following command (assuming you have bzr installed):
bzr branch lp:~mysql/mysql-server/mysql-5.1-wl1326
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 GIS functionality, please add it to the list below, to avoid duplicate reports by other users. Also check the result of this saved search, which returns all known bug reports in the "Server: GIS" category.
- Bug#45883: Buffer function crashes mysqld
- Bug#46347: Segmentation fault in main.subselect_gis
- Bug#46386: Intersection() and Difference() make the server disconnect
- Bug#46498: Intersection of polygon and multipolygon does not return all intersected geoms
- Bug#47429: Intersection function crashed mysqld
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 may not contain this fix yet! The list of known bugs will be reset, once a new preview release has been published which includes fixes for these bugs.
[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 "gis" and make sure to put them in the "Server: GIS" category.