GIS and Spatial Extensions with MySQL

GIS and Spatial Extensions with MySQL.

MySQL 4.1 introduces spatial functionality in MySQL. This article describes some of the uses of spatial extensions in a relational database, how it can be implemented in a relational database, what features are present in MySQL and some simple examples.

I’m hoping to understand the commonalities between this and PostGIS. PostGIS follows the OpenGIS “Simple Features Specification for SQL“. As do the MySQL extensions, apparently. The MySQL pages summarise the extensions as follows:

Data types. There needs to be data types to store the GIS information. This is best illustrated with an example, a POINT in a 2-dimensional system.

Operations. There must be additional operators to support the management of multi-dimensional objects, again, this is best illustrated with an example, a function that computes the AREA of a polygon of any shape.

The ability to input and output GIS data. To make systems interoperable, OGC has specified how contents of GIS objects are represented in binary and text format.

Indexing of spatial data. To use the different operators, some means of indexing of GIS data is needed, or in technical terms, spatial indexing.

I’m currently working on some ideas to prototype a new project (to fill the gap that the completion of SWAD-Europe leaves in my schedule). I’ll be revisiting my Gargonza plan to add a basic SemWeb RDF crawler to personal weblog installations, initially prototyping with Redland addons to WordPress. Ultimately, pure PHP would be better, unless Redland finds its way into the default PHP installation. Since WordPress requires MySQL anyway, it seems worth taking a look at these geo-related extensions. A more thorough investigation would take a look at reflecting GIS SQL concepts into RDF, perhaps exposing them in a SPARQL query environment. But that’s a bit ambitious for now.

What I hope to do for starters is use a blog as a personal SW crawler, scooping up RSS, FOAF, calendar, and photo descriptions from nearby Web sites. It isn’t clear yet exactly how photo metadata should most usefully be structured, but it is clear that we’ll find a way to harvest it into an RDF store. And if that metadata has mappable content, whether basic lat/long tags, richer GML, or something in between, we’ll harvest that too. My working hypothesis is that we’ll need something like MySQL spatial extensions or PostGIS to really make the most of that data, for eg. to expose location-specific, app-centric RSS, KML, etc. feeds such as those available from the flickr-derrived and brainoff flickr.proxy sites. See for one possible client app; Google Earth as KML browser is another.

That’s the plan anyway. So the reading list grows. Fortunately, OGC’s GIS SQL spec at least has some nice diagrams…

GIS datatype hierarchy