Where It's At: GeoSpatial MySQL - Part 2

Don Beesing (Alion Science and Technology), John Powell (eMapSite)

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

We will show how to create a fully-featured application using MySQL as a back end and Open Layers as a front end. Open Layers is an open source, cross browser javascript library, similar to Google Maps, but with more features. It includes, but is not limited to, the following functionality: slippy maps (tile-based map display like Google Maps) where the map source is a web map server (WMS), drawing and editing of polygons, pop-up displays, conversion from one coordinate system to another, reading and writing of an number of standard formats including WKT, KML, and JSON. This last feature makes reading/writing directly from a spatially enabled database to javascript arrays in Open Layers particularly easy.

Don Beesing

Alion Science and Technology

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.

Don graduated from Hiram College (Hiram, OH) with a Bachelor’s Degree in Computer Science.

John Powell

eMapSite

An ex-economist who was introduced to programming via excel vba on an investment bank trading floor. Fled banking, took an MSc in GIS. Now a Java and MySQL evangelist at an online UK mapping company.

  • Oracle
  • Monty Program
  • Calpont
  • Facebook
  • Gear6
  • Infobright, Inc
  • JasperSoft
  • Joyent
  • Kickfire
  • NorthScale, Inc.
  • Percona
  • Schooner Information Technology
  • Solid Quality Mentors (SolidQ)
  • Intel
  • Pentaho
  • Linux Pro Magazine

Sponsorship Opportunities

For information on exhibition and sponsorship opportunities at the conference, contact Yvonne Romaine at yromaine@oreilly.com

Download the O'Reilly MySQL Conference & Expo Sponsor/ Exhibitor Prospectus

Media Partner Opportunities

Download the Media & Promotional Partner Brochure (PDF) for information on trade opportunities with O'Reilly conferences or contact mediapartners@ oreilly.com

Press and Media

For media-related inquiries, contact Maureen Jennings at maureen@oreilly.com

O'Reilly MySQL Conference Newsletter

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).

Contact Us

View a complete list of O'Reilly MySQL Conference contacts.