With the advent of open source tools possessing extensive and easy to use APIs like Google Maps, mapping and geospatial tools have gone from an optional feature in a website or software project to an essential component. Today, databases need to adjust and become adept at storing, finding, and calculating all things geospatial. While this presentation will cover the current shortcomings of the GA release, it will focus more on the work being done on the MySQL GIS distribution from the MySQL forge site.
Traditionally GIS has been a niche area, dominated by a few expensive, proprietary systems. The storage, analysis and display of geographic/spatial data was done within these monolithic systems. The last few years have seen an explosive growth in the availability and use of spatial data. Much of this has been driven by visually appealing client-side tools, such as Google Earth/Maps and Open Layers, as well as by open standards such as KML, GML and WMS.
In order to store and analyze the ever increasing quantity of spatial data, a spatially enabled database is necessary. MySQL is one such database. A standard for data storage and manipulation has been created by the OGC and MySQL’s implementation of the spatial standard is fully compliant. Although the initial MySQL implementation of the spatial predicate operations (such as the ‘contains’ and ‘intersects’ functions so often used in spatial analysis) only worked with minimum bounding rectangles, recent work has extended these and other spatial functions to work with polygons as well. This means that MySQL can now be used not only for spatial storage, but for serious spatial analysis as well.
This tutorial will be presented in three parts, starting with the basics and ending with two complete applications. Data sets will be provided and participants will be expected to work hands-on with this data.
Part 1: Spatial basics
Definition of spatial data types: from point to geometry collection
Internal storage of spatial data: well-known binary (WKB) and well-known text (WKT)
Spatial (R-tree) indexes: how they are structured, how to create them, how to ensure they are used
Loading spatial data: there are various methods, from loading a csv and running an update to create a geometry column, to inserts from client apps
Spatial functions that return information about geometries and transform geometries: There are numerous useful functions in this category, from returning the nth inner ring of a polygon to buffering a geometry
Spatial predicates and spatial queries: this is where the real power of spatial sql shows – functions such as intersects or contains, combined with functions that combine geometries, such as intersection, difference and union, allow powerful GIS analysis to be carried out.
Part 2: Case study
This study will show how basic geospatial analysis can be done within MySQL in order to assign emergency response teams during an earthquake. The response will be done over a diverse terrain that precludes the simple assignment of teams based on distance from incidents. A wide range of MySQL geospatial functionality will be demonstrated to participants. The response will be visualized using Google Maps and will showcase some of the differences between true OpenGIS functionality versus the simplified functionality of minimum bounding rectangles (MBR). This study will also show the performance enhancements that geospatial indexing can have on a data set.
Part 3: Application using MySQL with Open Layers
Don Beesing is a Senior Programmer for Alion Science and Technology. He has six years experience in the fields of database administration, database programming, and web development.
Don has also co-authored a paper titled “Simulation vs. Stimulation” that was presented at the Interservice/Industry Training, Simulation and Education Conference (IITSEC) in December of 2008 and has spoken at the 2009 MySQL users’ conference.
Don graduated from Hiram College (Hiram, OH) with a Bachelor’s Degree in Computer Science. He currently resides in Suffolk, VA with his wife and two sons.
Originally trained as an economist, I fled the City of London to work in GIS. For the past 6 years, I have been a GeoSpatial developer and open source enthusiast working for a UK-based mapping company. I live in Barcelona, where I devote what time I don’t spend in front of a computer to my wife and kids and to outdoor pursuits, particularly skiing and cycling.
Comments on this page are now closed.
For information on exhibition and sponsorship opportunities at the conference, contact Yvonne Romaine at firstname.lastname@example.org
For media-related inquiries, contact Maureen Jennings at email@example.com
To stay abreast of conference news and to receive email notification when registration opens, please sign up for the O'Reilly MySQL Conference newsletter (login required).
View a complete list of O'Reilly MySQL Conference contacts.