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.
The country of Lesotho on Google Maps, which you can see is entirely surrounded by South Africa.
[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.
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.