Calculating distance using MySQL

Calculating distance using MySQL” is a very useful blog post for everyone who works with geographical location data and MySQL. It shows a simple example of how to calculate the distance between two coordinates on a sphere (Earth in particular) within the MySQL itself.

SELECT ST_Distance_Sphere(
    point(-87.6770458, 41.9631174),
    point(-73.9898293, 40.7628267)
);

The above will return 1148978.6738241839, which is the distance between the two points in meters.

This functionality is available since MySQL 5.7. Have a look at the documentation of the spacial convenience functions.

MariaDB has similar functionality, but with a slightly different function names. Use ST_DISTANCE() instead of ST_Distance_Sphere(). Have a look at this blog post for more details.

UUIDs in MySQL are really not random

Jouke Waleson points out to an interesting fact about UUIDs in MySQL, which you might have missed in the documentation:

Warning: Although UUID() values are intended to be unique, they are not necessarily unguessable or unpredictable. If unpredictability is required, UUID values should be generated some other way.

Make a note!

MySQL High Availability at GitHub

Shlomi Noach, GitHub’s Senior Infrastructure Engineer, shares some details on both the current and future high availability setup of MySQL databases at GitHub.

This is probably way too far out for most people using MySQL for their web applications.  But it does highlight the technical complexity of running high load web applications, and how some of the issues can be solved or worked around.

Pretty fascinating stuff there … 

mycli – MySQL command line client with auto-completion


mycli is a MySQL / MariaDB / Pecona database command line client with syntax highlighting and auto-completion.  Have a look at some of the screenshots and documentation for features.

Found via this StackOverflow discussion.

Metabase – Open Source business intelligence and analytics


Metabase is an Open Source business intelligence and analytics tool.  It supports a variety of databases and services as sources for data, and provides a number of data querying and processing tools.  Have a look at the GitHub repository as well.

And if you want a few alternatives or complimenting tools, I found this list quite useful.