14 \ May 2019 \ http://www.phparch.comFEATUREMySQL 8.0 Geographic Information
System or How Did I Get to This Point?
Dave Stokes, MySQL Community Manager
MySQL before version 5.7 had less than stellar Geographic Information Systems (GIS) support.
In version 5.7, the Boost.Geometry two dimensional or 2D libraries were added. The past
several years have seen explosive growth in GIS demand, and the pre 5.7 software was not up
to what the customer base was demanding. So the decision was made to switch to the Boost.
Geometry libraries. This refactoring was a significant investment for the engineering team to
move to a third party library and they have also become contributors to the Boost.Geometry
project. Also, with 8.0 came the three dimensional or 3D libraries, but how do you use these
features?The Boost.org project provides C++ libraries for many
functions defining concepts, primitives, and algorithms for
solving geometry problems. These libraries are peer-reviewed
and free. They are robust, fast, and have a dynamic commu-
nity behind them.
Hopefully at the end of this short article you will have an
overview of how to start using the new GIS features found in
MySQL 8.0 and have a better appreciation of how marvelous
it is to be able to use your cell phone to find out where to
get a pizza nearby, calculate mileage between two cities for
a business trip, or write your own, personalized version of
Google maps.Well Known Text Or Binary
I am going to use the GEOMETRY data type over POINT, LINE-
STRING, or POLYGON as it can store any of those three while the
other three can only contain data matching their name (so
POINT can hold only point data, etc.). The values are stored in
an internal geometry binary format, but it takes wither WKT or
WKB formatted data.
Those are Well-Known Text (WKT) or Well-Known Binary
(WKB) formats respectively. I am hoping most of you are
better with text than binary, so the following examples
demonstrate how to insert geometry values into a table by
converting WKT values to internal geometry format. And yes,
you can use the functions that support binary formats if
you prefer. You can insert X, Y values (rise and run) for
simple graphs or longitude and latitude for mapping appli-
cation into a POINT or GEOMETRY columns, either in
text or binary. These functions are not limited to real-world
maps; you could set the origin, x, and y-axis to reflect a
map of your office building, mars, or even Westeros.
So let us start with a simple table holding arbitrary data
in various geometric forms. It’s used to show how MySQL
stores simple geometric data. We’ll go from simple, made
up data to real locations with longitude and latitude but they
all start with basic geometric data types.mysql> CREATE TABLE geom (type TEXT, g GEOMETRY);Query OK, 0 rows affected (0.04 sec)We can use the ST_GeomFromText function to take some
strings and convert into the internal binary format.mysql> INSERT INTO geom VALUES (
'point', ST_GeomFromText('point(1 1)')
);
Query OK, 1 row affected (0.01 sec)mysql> INSERT INTO geom VALUES (
'linestring',
st_geomfromtext('linestring(0 0,1 1, 2 2)')
);Query OK, 1 row affected (0.01 sec)There are type-specific functions for POINT, LINESTRING, and
POLYGON that we can also take advantage of for this work. For
an example, refer to Listing 1.Listing 1- mysql> SET @g =
- 'POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5))';
- Query OK, 0 rows affected (0.00 sec)
- mysql> INSERT INTO geom
- VALUES ('polygon',ST_PolygonFromText(@g))
- Query OK, 1 row affected (0.00 sec)