I have a presentation next month on MySQL and GIS. MySQL
8.0 has benefited greatly from the three dimensional libraries
from Boost.Geometry. There are many facets to the Geographic Data
world that it is damn near impossible not to lurch down one
rabbit hole into another in an unending spiral of acronyms,
standards, projections, and functions. But thankfully I
have MySQL Workbench to aid me.
Texas
I wanted some test data to use for some GIS exercises and was
very happy to find many useful sets curated by the OpenStreetMaps
folks. Shapefiles are used to hold the various data points of an
item of interest. I had assumed that the data would have
some sort of longitude/latitude pairs but was wondering what I
would need to do to work with that data and what ever came
bundled with it. I download the Texas data and then …
MySQL has added a lot of functionality for Geographical
Information System (GIS) data in the last two releases. This has
given us better ways to save and explore GIS data. MySQL
Workbench is an amazing tool that many do not realize has vastly
upped its game for supporting GIS information. But did you
know you can use MySQL Workbench with OpenStreetMap to plot
locations?
1. Get your Longitude and Latitude
These, believe it or not, is the hard part. Go
to https://www.latlong.net/ and type in the name
of your location. It will report back your longitude and
your latitude. For the example I will be using the
information for the city of Justin, Texas.
| … |
The ST_DISTANCE function has been upgraded in
MySQL 8.0.16 to allow you to specify the unit of measure between
to locations. Previously you had to convert from meters to
what you desired but now you can use the
INFORMATION_SCHEMA.ST_UNITS_OF_MEASURE table to help you get many
of the more popular measurements (foot, yard, statue mile,
nautical mile, fathom) and some ones that are new to me (chain,
link, various feet). However some measures are
omitted (furlong,smoot) that may have some relevance in your
life.
select * from information_schema.ST_UNITS_OF_MEASURE;
Fetching table and column names from `mysql` for
auto-completion... Press ^C to stop.
+--------------------------------------+-----------+---------------------+-------------+
| UNIT_NAME …
The past couple of blog entries have been on Geographic
Information Systems and Geometric Data. Visualizing that
data with MySQL Workbench makes it easier for me to see what the
results really mean.
| Workbench 8.0.15 will draw the polygon with the Spatial View Option |
So how do you get there?
Start Workbench, create a new SQL Tab in your favorite scratch
schema, and crate the table below.
CREATE TABLE `test` (
`id` INT NOT NULL AUTO_INCREMENT,
`geom` GEOMETRY NULL,
PRIMARY KEY (`id`));
Next add some data.
INSERT INTO `test` …
The last blog entry was very popular and there were
lots of requests for some introductory information on the spatial
data types.
Well Known Text Or Binary
I am going to use the GEOMETRY data type over POINT, LINESTRING,
or POLYGON as it can store any of those three while the other
three can only contain data matching their name (so POINT can
holds only point data, etc.). The values are stored in an
internal geometry format but it takes wither WKT or WKB formatted
data.
Those are Well-Known Text (WKT) or Well-Known Binary (WKB)
formats repectively. I am hoping most of your 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.
So let us start with a …
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.
And with 8.0 came the three dimensional or 3D libraries. But how
do you use these features?
I would like to state up front that this is all new to me and
this is an attempt for me to document what it takes to go from
zero knowledge on GIS to something hopefully better. What I
want to do as an exercise is to get the distance between two
places from their longitude and latitude, say two cities near
where I live. So what do we have to do to accomplish
that?
It is actually easy with the functions provided if we have the
longitude and latitude in an SRID 4326 format.
SELECT ST_Distance(
(SELECT loc FROM cities WHERE name =
'Trondheim'),
(SELECT loc FROM cities WHERE …
The big change from MySQL 5.7 to 8.0 when it comes to spatial data, is the support for multiple spatial reference systems and geographic computations. This means that the SRIDs of geometries actually have meaning and affect computations. In 5.7 and earlier, however, the SRIDs are ignored, and all computations are Cartesian.…
There are many changes to spatial functions in MySQL 8.0:
- Old aliases for functions have been removed (after being deprecated in 5.7)
- Functions that don’t support geographic computations raise errors if called with geographic data in their arguments
- Many functions support geographic computations
The first two are failing cases.…
MySQL 8.0.11 comes with a catalog of 5108 spatial reference systems (SRSs). 4628 projections (flat maps), 479 geographic (ellipsoidal) representations of Earth, and one Cartesian all-purpose abstract plane (SRID 0). And if for some reason that isn’t enough, we can create our own.…
MySQL has had spatial indexes for many years, but they have all been Cartesian (X and Y coordinates) indexes. MySQL 8.0 adds support for geographic (latitude-longitude) indexes. In an earlier blog post, I described how the feature works. In this post, we’ll go into the details of how to upgrade from 5.7 to 8.0 if you have spatial indexes.…