OurSQL Episode 183: Map Our Way Around

This week we discuss MySQL and Geographical Information Systems (GIS). Ear candy is how MVCC in InnoDB relates to undo logging and how you can write better queries based on this information. At the Movies is a demo of GIS.

MySQL and MariaDB geographic information
Geographic features in MariaDB
Spatial Extensions in MySQL
The SQL part of the OpenGIS Implementation Standard for geographic information, from the Open Geospatial Consortium's website.

Flatland the short novel - the Wikipedia link includes links to where you can download the text online, as it is in the public domain.

MySQL manual page on Geometry Hierarchy
MariaDB knowledgebase on Geometry Hierarchy

Geometry Class MySQL manual page
Minimum bounding rectangle, or MBR, at Wikipedia

The country of Lesotho on Google Maps, which you can see is entirely surrounded by South Africa.

MariaDB Geometry Constructors

[WKT and WKB formats]
MySQL manual page on well-known text and well-known binary formats (e.g. WKT and WKB)
MariaDB knowledgebase on well-known text format
MariaDB knowledgebase on well-known binary format
example of well-known binary format.

Sample table with a GEOMETRY column

CREATE TABLE example ( id INTEGER NOT NULL PRIMARY KEY, name VARCHAR(64), shape GEOMETRY ) engine=InnoDB;
INSERT INTO example values (1, 'first shape', LineFromText('LINESTRING(10 20, 30 55)'));

To add an index:
ALTER TABLE example ADD SPATIAL INDEX (shape);
Note that only MyISAM tables will create an R-Tree index, other engines, like InnoDB, will create the spatial index as a B-Tree index.

OpenJump, a site to interpret geographical data

Ear Candy
The Basics of InnoDB Undo Logging and History System

At the movies
This week in At the Movies, we present Daniel Bartholomew of SkySQL running a short demo about the MariaDB GIS features.

Where you can see us
Sheeri will be at the June Boston MySQL User Group on Monday, June 9th, where we'll have Amrith Kumar talking about DB as a service using trove on openstack.

Gerry will be at the June Seattle MySQL User Group on Monday, June 9th, where we'll talk about WebScaleSQL.

Feedback
Facebook group
Google+ page
e-mail: podcast at technocation.org
voicemail using phone/Skype: +1-617-674-2369
twitter: @oursqlcast
or Tweet about @oursqlcast