GEO calculations with MySQL

Geo-enabled or applications using user’s locations are increasing dramatically. I am currently working on an application which collects user’s location data, which led me to do more research and find an efficient methods to achieve the common tasks in a geo-enabled application.

Some of the common tasks in a geo-enabled or a social networking application are:

  • Finding distance between 2 points using latitude and longitude.
  • Finding places of interest in a radius. (Example: All the coffee shops in 1 KM radius.

Basics:

We will start with a table called “places” in which we will store the geo-coordinates (latitude, longitude) of the places. Habitually one would just create 2 string (varchar) fields and store the latitude and longitude in respective fields. Afterwards they would just pull of the latitude and longitude data and do the calculations in the application script. By using spatial data type, we can avoid the calculating part in the script and do it in the database.

Getting back to our table “places”, we will add a field called coord with the data type POINT, in which we can store both latitude and longitude.

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

Insert some location data into the table in terms of POINT(X Y) or POINT(latitude longitude). In this case X is latitude and Y is longitude. Technically it doesn’t matter how you store them as long as you follow the same convention while reading it back.

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

Get those X, Y coordinates back from table.

SELECT X(coord) as latitude, Y(coord) as longitude FROM places

Calculate distance between 2 points:

Lets assume my current location is 6.905235 79.862687 (which is about 1 KM from the places) and I would like to calculate the distance between the places in my table with my current location. The following table shows what I have right 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)

I can easily calculate the distance between those places and my current location by using a query, and order it by the distance.

SELECT
name,
(GLength(
LineStringFromWKB(
  LineString(
    coord, 
    GeomFromText('POINT(6.905235 79.862687)')
  )
 )
))
AS distance
FROM places
ORDER BY distance ASC
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 have used *100 to convert the distance to KM.

There’s a lot more you can do with Spatial Data and MySQL, but this is all for now. Have a look at this tutorial by Robert If you are interested in reading more on the topic.

Leave a Reply

Your email address will not be published. Required fields are marked *