Spatial SQL: Who Needs a Traditional GIS?

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.