Spatial SQL: Who Needs a Traditional GIS?

John Powell (eMapSite)
Business and Case Studies, Java
Location: Ballroom B
Average rating: ***..
(3.33, 3 ratings)

Introduction

  • An introduction to OpenGIS spatial data types as implemented in MySQL and the importance of standards.
  • An introduction to OpenGIS spatial functions that operate on spatial data types as implemented in MySQL.
  • R-tree spatial indexing, which allows for rapid retrieval of data based on two-dimensional location will be explained.
  • Loading tips and common gotchas.
  • Comment about current/future development New spatial features at MySQL forge

Business example 1: Monitoring of ships in an offshore windfarm construction site for health and safety considerations.

Very brief explanation of AIS (ship GIS) messages: dynamic indicating ship’s position, speed, name, destination, rate of turn, MMSI (id), etc.; static indicating destination, weight, draught, etc. Such info is sent every 2 seconds by a moving ship.
Spatio-temporal indexing, ie, spatial index for ship’s position plus a time stamp for the ais signal.

Example queries that can be written against such an index for business support and health and safety:

  • Show current/past position of each ship inside a particular search window for current/past display purposes.
  • Get static info about ship, ie, destination, ship type, draught, either current or historical. Reasonably complex spatio-temporal query involving a table self-join and an outer join.
  • Query to get the ships trail. Due to frequency of ship messages, as often as every two seconds depending on speed, a trail showing ship’s positions for the last, say, hour in a busy shipping channel could yield many thousands of rows. Query to demonstrate how to retrieve position for every minute/two minutes to make query/rendering more real-time. Also, as ships appear and disappear as they go out of radio range, restrict query to ships that are in view at the end of the query time, be it current or historical.
  • Generation of backdrop bathymetry (sea depth) mapping on which the ships positions are displayed. The polygons and lines that make up the depth areas and contours of a bathymetry chart are also stored using spatial data types. Will demonstrate how polygons with inner rings (very common for this kind of mapping) can be rendered on the fly very quickly using a spatial query and Java graphics library and shape interface, using depth attribute to color each area differently.

Business example 2: Business location.

Using a number of different databases maintained by our company, demonstrate how spatial and non-spatial attributes can be used together with spatial functions such as contains, distance, and area to answer a wide range of business location questions. For example find all parcels of land greater than 500 hectares, within 10km of a town with a population of at least 20,000, not within 5km of a site of scientific special interest, more than 50m above sea level, where no chemical spills have occurred in the last 50 years and group this by local authority. Query parameters to be tweaked and output shown in a Java front end to show the speed and power of these kinds of business location queries.

Briefly mention other areas where spatial data types and functions enable similar analysis, for example, epidemiology, crime monitoring, animal monitoring in wildlife reserves.

Conclusion:
Vast quantities of spatial data and accompanying attributes can easily be stored and rapidly retrieved using MySQL’s spatial functions and indexing. Numerous spatial functions allow this data to be analysed in ways that have been traditionally the preserve of expensive, proprietary GIS systems. Some very sophisticated analysis can be done directly using SQL functions, however, the open standards supported by MySQL make it easy to import/export spatial data for visualization or further analysis. A programming language like Java, with good libraries in areas such as graphics, XML, and threading in conjunction with a MySQL backend can be used to build sophisticated, distributed, customized applications with all of the capabilities of a traditional GIS system.

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. Have spent last five years as a Java and MySQL evangelist at an online UK mapping company.

Co-presented By:

O'Reilly Media MySQL/Sun Microsystems
  • Kickfire
  • Virident
  • Infobright, Inc
  • JasperSoft
  • Intel
  • Advanced Micro Devices
  • BIRT Exchange by Actuate
  • Calpont
  • Canonical
  • Continuent
  • Dolphin Interconnect Solutions
  • Facebook
  • HiT Software, Inc.
  • IBM
  • iDashboards
  • Oracle
  • Pentaho
  • R1Soft
  • Schooner Information Technology
  • SQLstream
  • Ticketmaster
  • Zmanda, Inc.
  • Linux Journal

Sponsorship Opportunities

For information on exhibition and sponsorship opportunities at the conference, contact Sharon Cordesse at scordesse@oreilly.com

Download the MySQL 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

MySQL Conference Newsletter

To stay abreast of conference news and to receive email notification when registration opens, please sign up for the MySQL Conference newsletter.

Contact Us

View a complete list of MySQL contacts.