MySQL Workbench and OpenStreetMap Data Visualization

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 loaded it into the database.

You will need a copy of the ogr2org utility This wonderful program reads the raw shapefile and converts it into SQL. You may want to get the source but hopefully a version may be available for you Linux distribution.  There is a copy of the ogr2ogr program that comes with MySQL Workbench but I have not been able to get it to convert the data and load that data into a MySQL instance, with either Windows, Fedora, or Ubuntu.

$ogr2ogr -overwrite -progress -f "MySQL" mysql:texas,user=dave,password=****** gis_osm_natural_a_free_1.shp
0...10...20...30...40...50...60...70...80...90...100 - done.
$ ogr2ogr -overwrite -progress -f "MySQL" mysql:texas,user=dave,password=***** gis_osm_natural_free_1.shp
0...10...20...30...40...50...60...70...80...90...100 - done.

The Data

The ogr2org utility reads the shape files and puts all the attributes into a a table. 

mysql> DESC gis_osm_natural_free_1;
+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| OGR_FID | int(11)      | NO   | PRI | NULL    | auto_increment |
| SHAPE   | geometry     | NO   | MUL | NULL    |                |
| osm_id  | varchar(10)  | YES  |     | NULL    |                |
| code    | decimal(4,0) | YES  |     | NULL    |                |
| fclass  | varchar(28)  | YES  |     | NULL    |                |
| name    | varchar(100) | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

And a sample line from that table.

mysql> select OGR_FID,ST_ASTEXT(SHAPE) as Shape, osm_id, code, fclass, name FROM gis_osm_natural_free_1 limit 1;

+---------+-------------------------------+----------+------+--------+--------------------+

| OGR_FID | Shape                         | osm_id   | code | fclass | name               |

+---------+-------------------------------+----------+------+--------+--------------------+

|       1 | POINT(29.3060929 -94.7679897) | 80029566 | 4141 | beach  | Stewart Beach Park |

+---------+-------------------------------+----------+------+--------+--------------------+

1 row in set (0.00 sec)

The SHAPE column has the desired longitude and latitude. So now we have this data but I had zero clue to where Stewart Beach Park was located.

MySQL Workbench to the Rescue

Workbench makes it easy to 'see' where the data was.  You can see in the below example that Stewart Beach Park is the first row returned.

MySQL Workbench has an amazing number of features including the ability to help display GIS data.

If you right click on the 'BLOB' graphic under the shape column you will see an option to Show Point In Browser.  

The location of Stewart Beach Park on the Galveston Sea Wall

So Stewart Beach Park is right on the Galveston Sea Wall and around the corner from the cruise ship terminals. 

Mid-Atlantic Developer's Conference

I will be speaking on MySQL & GIS at the Mid-Atlantic Developer's Conference and you can still attend but tickets are going quickly. 

Plot your Location MySQL Workbench and OpenStreetMap

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.

Using www.longlat.net to find the location of Justin, Texas

2. Create Table and Add Data 

Now create a table using MySQL Workbench in your favorite test schema to store your data.

CREATE TABLE city (id int unsigned auto_increment primary key, 
             name char(25), 
  loc point);

And then add the information and as before the example is for my home town.

INSERT INTO city (name,loc) VALUES 
('Justin', ST_GeomFromText('point(33.084843 -97.296127)',4326));

3. Query With Workbench

Now query your data from workbench.  If you only have the one record SELECT * FROM city; will suffice or use SELECT loc FROM city WHERE name='<yourcitynamehere>';

Running the query to get the longitude and latitude of Jsutin, Texas using MySQL Workbench. Note the 'BLOB' under the loc column

4. Open In a Browser

If you right click on the 'BLOB' icon under the 'loc' column, one of the choice is show point in browser. Pick that choice and you will get redirected to OpenStreetMap.org

The OpenStreetMap.org map of Justin, Texas

MySQL 8.0 GIS Units of Measure - Meter, foot, Clarke's yard, or Indian Foot

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                            | UNIT_TYPE | CONVERSION_FACTOR   | DESCRIPTION |
+--------------------------------------+-----------+---------------------+-------------+
| metre                                | LINEAR    |                   1 |             |
| millimetre                           | LINEAR    |               0.001 |             |
| centimetre                           | LINEAR    |                0.01 |             |
| German legal metre                   | LINEAR    |        1.0000135965 |             |
| foot                                 | LINEAR    |              0.3048 |             |
| US survey foot                       | LINEAR    | 0.30480060960121924 |             |
| Clarke's yard                        | LINEAR    |        0.9143917962 |             |
| Clarke's foot                        | LINEAR    |        0.3047972654 |             |
| British link (Sears 1922 truncated)  | LINEAR    |          0.20116756 |             |
| nautical mile                        | LINEAR    |                1852 |             |
| fathom                               | LINEAR    |              1.8288 |             |
| US survey chain                      | LINEAR    |   20.11684023368047 |             |
| US survey link                       | LINEAR    |  0.2011684023368047 |             |
| US survey mile                       | LINEAR    |  1609.3472186944375 |             |
| Indian yard                          | LINEAR    |  0.9143985307444408 |             |
| kilometre                            | LINEAR    |                1000 |             |
| Clarke's chain                       | LINEAR    |       20.1166195164 |             |
| Clarke's link                        | LINEAR    |      0.201166195164 |             |
| British yard (Benoit 1895 A)         | LINEAR    |           0.9143992 |             |
| British yard (Sears 1922)            | LINEAR    |  0.9143984146160288 |             |
| British foot (Sears 1922)            | LINEAR    |  0.3047994715386762 |             |
| Gold Coast foot                      | LINEAR    |  0.3047997101815088 |             |
| British chain (Sears 1922)           | LINEAR    |  20.116765121552632 |             |
| yard                                 | LINEAR    |              0.9144 |             |
| British link (Sears 1922)            | LINEAR    |  0.2011676512155263 |             |
| British foot (Benoit 1895 A)         | LINEAR    |  0.3047997333333333 |             |
| Indian foot (1962)                   | LINEAR    |           0.3047996 |             |
| British chain (Benoit 1895 A)        | LINEAR    |          20.1167824 |             |
| chain                                | LINEAR    |             20.1168 |             |
| British link (Benoit 1895 A)         | LINEAR    |         0.201167824 |             |
| British yard (Benoit 1895 B)         | LINEAR    |  0.9143992042898124 |             |
| British foot (Benoit 1895 B)         | LINEAR    | 0.30479973476327077 |             |
| British chain (Benoit 1895 B)        | LINEAR    |  20.116782494375872 |             |
| British link (Benoit 1895 B)         | LINEAR    |  0.2011678249437587 |             |
| British foot (1865)                  | LINEAR    | 0.30480083333333335 |             |
| Indian foot                          | LINEAR    | 0.30479951024814694 |             |
| Indian foot (1937)                   | LINEAR    |          0.30479841 |             |
| Indian foot (1975)                   | LINEAR    |           0.3047995 |             |
| British foot (1936)                  | LINEAR    |        0.3048007491 |             |
| Indian yard (1937)                   | LINEAR    |          0.91439523 |             |
| Indian yard (1962)                   | LINEAR    |           0.9143988 |             |
| Indian yard (1975)                   | LINEAR    |           0.9143985 |             |
| Statute mile                         | LINEAR    |            1609.344 |             |
| link                                 | LINEAR    |            0.201168 |             |
| British yard (Sears 1922 truncated)  | LINEAR    |            0.914398 |             |
| British foot (Sears 1922 truncated)  | LINEAR    | 0.30479933333333337 |             |
| British chain (Sears 1922 truncated) | LINEAR    |           20.116756 |             |
+--------------------------------------+-----------+---------------------+-------------+
47 rows in set (0.0019 sec)

MySQL Workbench Spatial Viewer or How to See your GEOMETRY Data

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`
  (`geom`)
VALUES
  (st_geomfromtext
  ('polygon((0 0,0 3,3 0, 2 2,0 0),(1 1,1 2,2 1,2 2, 1 1))')
   );

The run the query.

select geom from test;

However the result will default to the Result Grid. Look off to the right hand side of the results window to see a series of stacked icons and the default is the Result Grid.  And that 'BLOB' is the result of the query.  But that result is not exactly visually stunning.

The 'BLOB' is the result of the query.

Click on the Form Editor icon. It is directly below the Result Grid Icon

Select the Form Editor Icon

And you should see the image from the top of this blog entry.

Bonus!

Now scroll down below the Form Editor icon and select Spatial View.

Spatial View of the Query

MySQL 8.0 GIS -- Inserting Data & Fun Functions

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 simple table.

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 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.

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)

If you do a SELECT * FROM geom; you will get the g column output in binary.  Thankfully we can use ST_AsText() to provide us with something more readable.


mysql> select type, st_astext(g) from geom;
+------------+----------------------------------------------------------+
| type       | st_astext(g)                                             |
+------------+----------------------------------------------------------+
| point      | POINT(1 1)                                               |
| linestring | LINESTRING(0 0,1 1,2 2)                                  |
| polygon    | POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7,5 5)) |
+------------+----------------------------------------------------------+
3 rows in set (0.00 sec)

mysql>


Put the 'fun' in Function

There are functions that can be use with each of these type to provide information., For instance there are the x & Y coordinates for a point.

mysql> select type, st_x(g), st_y(g) from geom where type='point';
+-------+---------+---------+
| type  | st_x(g) | st_y(g) |
+-------+---------+---------+
| point |       1 |       1 |
+-------+---------+---------+
1 row in set (0.00 sec)

We can even use our linestring data to get the minimal bounding rectangle for the given coordinates (basically if we had to put an envelope around the points the results are the coordinates points to enclose those x,y coordinates)

mysql> select st_astext(st_envelope(g)) 
       from geom where type='linestring';
+--------------------------------+
| st_astext(st_envelope(g))      |
+--------------------------------+
| POLYGON((0 0,2 0,2 2,0 2,0 0)) |
+--------------------------------+
1 row in set (0.00 sec)

And we can get the area of a polygon too.

mysql> select type, st_area((g)) from geom where type='polygon';
+---------+--------------+
| type    | st_area((g)) |
+---------+--------------+
| polygon |           96 |
+---------+--------------+
1 row in set (0.00 sec)

And find the mathematical center of that polygon.

mysql> select type, 
       st_astext(st_centroid(g)) 
       from geom where type='polygon';
+---------+--------------------------------------------+
| type    | st_astext(st_centroid(g))                  |
+---------+--------------------------------------------+
| polygon | POINT(4.958333333333333 4.958333333333333) |
+---------+--------------------------------------------+
1 row in set (0.00 sec)

Plus we can get the linestring data if we wanted to draw out polygon.

mysql> select type, 
       st_astext(st_exteriorring(g)) 
       from geom where type='polygon';
+---------+-------------------------------------+
| type    | st_astext(st_exteriorring(g))       |
+---------+-------------------------------------+
| polygon | LINESTRING(0 0,10 0,10 10,0 10,0 0) |
+---------+-------------------------------------+
1 row in set (0.00 sec)

MySQL 8.0 Geographic Information Systems (GIS) - How to get the distance between two cities

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 name = 'San Francisco')
) AS distance_in_meters;

Okay, but what is an SRID 4326 format, where do I get them, and how do I get data into that format? So let us dig deeper.

I have to thank Norvald Ryeng who is a senior software manager at Oracle for that query roughly transposed by me from his MySQL GIS 8.0 Overview.

Harrison's Clocks were the first successful one to be used by the Royal Navy for navigation and can be seen at the Royal Observatory, Greenwich, United Kingdom. Very interesting story of a man on the cutting edge of his technology for the time who had to overcome so many technical and political problems.

Geometry Class: Background

Geometric data is stored in a geometry data type. Each geometry has a type, a SRID, coordinates, and some other attributes that will be ignored for now. And we will mainly deal with the POINT class.  Think of a point as someplace on a map. We will skip CURVE, LineString, Surface, Polygons, GeometryCollection, MultiPoint, MultiCurve, MultiLineString, MultiSurface, and MultiPolygon.

Columns with a spatial data type have a SRID attribute to indicate the spatial reference system, or SRS, used to store data in that column.  There are SRSs for projecting a globe onto a flat surface (think Mercator Projection maps of the world), non projected representing longitude-latitude on an ellipsoid. In MySQL a SRID value of 0 represents an infinite flat Cartesian plane with no units assigned to the axes and is the default.  Now a group named the European Petroleum Survey Group has their own system with a value of 4326, which you will see later.

Coordinates are represented as double-precision (8-byte) numbers.  These number pairs are either planar (flat) or geodetic (think Earth's surface) and the same pair values from one system can be wildly different than the other.

In the query above we used a column named loc as the point where the cities were located.  You can see the details in Norvald's excellent slides or take my word that these queries were used in a column defined as loc POINT SRID 4326 NOT NULL for now.

INSERT INTO cities (name, loc) VALUES (
 'Trondheim',
 ST_GeomFromText('POINT(64.43048320193547 10.394972698312927)', 4326)
);
INSERT INTO cities (name, loc) VALUES (
 'San Francisco',
 ST_GeomFromText('POINT(37.272615666666667 -122.4425455194445)', 4326)
);

Now Wikipedia says Trondheim is at 63 degrees 25"47'N and 10 degrees 23"36'E which looks pretty close to the numbers above.  And the Nidaros Cathedral is listed as 63.4305 degrees North latitude and 10.3951 degrees East longitude. So saying POINT('latitude' 'longitude') seems to be what I am looking for to designate a location.

To peel back another layer of the onion, lets look at what ST_GeomFromText() does.  The states thusly that ST_GeomFromText 'Constructs a geometry value of any type using its WKT representation and SRID.'

Okay, that cleared up nothing. So what the heck is a WKT representation? The MySQL Manual has a section on Functions That Create Geometry Values from WKT Values.  The Well-Known Text (WKT) representation of geometry values is designed for exchanging geometry data in ASCII form. The OpenGIS specification provides a Backus-Naur grammar that specifies the formal production rules for writing WKT values.  So 'POINT(15 20)' is a WKT.

This is not getting easier, is it?  And we still do not know how to get the distance between two sets of longitude and latitude.

Getting Some Data

I was able to find a very nice list of US Zip Codes (postal codes) at https://gist.github.com/erichurst/7882666/ and used the MySQL Workbench data import agent to create a table. Just three columns ZIP for the Zip Code, LAT for latitude, and LNG for longitude. I stored the ZIP filed as TEXT (leading zeros that are significant disappear if the column in an INT) and the other two fields as REALs.  And there are 33,144 rows of data.

Lets look at the distance between the lovely cities of Justin and Trophy Club, both in the state of Texas. Justin is zip code 76247 and Trophy Club is 76262

 mysql> select * from data where ZIP in (76247, 76262);
+-------+-----------+------------+
| ZIP   | LAT       | LNG        | +-------+-----------+------------+
| 76247 | 33.099993 | -97.340499 |
| 76262 | 33.009335 |  -97.22672 | +-------+-----------+------------+
2 rows in set (0.03 sec)
 
And lets try a query.


mysql> select 
 st_distance(
  st_geomfromtext('point(33.099993 -97.34099)',4326),                     
  st_geomfromtext('point(33.009335 -97.22672)',4326)
  )  as distance;

+--------------------+

| distance           |
+--------------------+
| 14662.554615102901 |
+--------------------+
1 row in set (0.00 sec)


So there is roughly 15,000 meters between the cities of Justin and Trophy Club. And please note this is a flat model not one that takes in the curvature of the earth.  But we can get that easily enough by using ST_Distance_Sphere.

mysql> select st_distance_sphere(
    st_geomfromtext('point(33.099993 -97.34099)',4326),
    st_geomfromtext('point(33.009335 -97.22672)',4326)) 
   as sphere,
    st_distance(
     st_geomfromtext('point(33.099993 -97.34099)',4326),
     st_geomfromtext('point(33.009335 -97.22672)',4326)) 
    as flat;
+--------------------+--------------------+
| sphere             | flat               |
+--------------------+--------------------+
| 14664.184945803418 | 14662.554615102901 |
+--------------------+--------------------+
1 row in set (0.00 sec)






So even in the fairly flat area of North Texas, there is some difference between a spherical and flat world.