GEO calculations with MySQL

With the rise of location-based apps like Foursquare, applications that use users’ locations have become increasingly common. I’m currently working on an app that uses users’ geo-coordinates for certain calculations.

Originally, my plan was to store the geo-coordinates in the database as strings and perform the calculations at the application level. However, after some research, I discovered a better approach.

These are the functionalities I wanted to achieve:

• Find the distance between two points using latitude and longitude.
• Find places of interest within a specific radius (e.g., all the coffee shops within a 1 KM radius).

Basics

We’ll start with a table called places, which will store the geo-coordinates (latitude and longitude) of various locations.

Typically, one might create two string (VARCHAR) fields for latitude and longitude and then perform all calculations in the application code. However, MySQL provides a spatial data type designed specifically for this purpose. By using it, we can offload the calculations to the database instead of handling them in the application layer.

Let’s update our places table to include a field named coord with the data type POINT, where we’ll store both latitude and longitude:

ALTER TABLE places ADD coord POINT NOT NULL;
CREATE SPATIAL INDEX coord ON places (coord);

Now, let’s insert some location data into the table using the format POINT(X Y) — or POINT(latitude longitude). In this example, X represents latitude and Y represents longitude. Technically, it doesn’t matter which order you use as long as you stay consistent when writing and reading the data.

INSERT INTO places (coord) VALUES (GeomFromText('POINT(6.91867187086 79.861366946)'));

To retrieve those coordinates:

SELECT X(coord) AS latitude, Y(coord) AS longitude FROM places;

Calculating Distance Between Two Points

Let’s assume my current location is 6.905235, 79.862687 (approximately 1 KM away from the places in the table). Here’s what the table looks like now:

mysql> SELECT name, X(coord) AS latitude, Y(coord) AS longitude FROM places;
+----------------------+---------------+---------------+
| name                 | latitude      | longitude     |
+----------------------+---------------+---------------+
| APIIT                | 6.91867187086 | 79.861366946  |
| Access Towers        | 6.92020089776 | 79.8574130003 |
| Gloria Jean's Coffee | 6.9178845525  | 79.8631847629 |
+----------------------+---------------+---------------+
3 rows in set (0.00 sec)

We can calculate the distance between these places and my current location directly in MySQL using the following query:

SELECT
  name,
  GLength(
    LineStringFromWKB(
      LineString(
        coord, 
        GeomFromText('POINT(6.905235 79.862687)')
      )
    )
  ) AS distance
FROM places
ORDER BY distance ASC;

Here’s an example output:

mysql> SELECT name, (GLength(LineStringFromWKB(LineString(coord, GeomFromText('POINT(6.905235 79.862687)'))))) * 100 AS distance FROM places ORDER BY distance ASC;
+----------------------+--------------------+
| name                 | distance           |
+----------------------+--------------------+
| Gloria Jean's Coffee | 1.265934225601339  |
| APIIT                | 1.3501556986925545 |
| Access Towers        | 1.5867991952301663 |
+----------------------+--------------------+
3 rows in set (0.00 sec)

In the query above, I multiplied by 100 to roughly convert the distance into kilometers.

There’s a lot more you can do with spatial data and MySQL, but this should give you a good starting point. If you’re interested in exploring further, check out this tutorial by Robert.