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