Calculate distance between 2 points (latitude-longitude) with Mysql ST_Distance_Sphere method

If you want to measure distance between 2 points, you can use Mysql’ ST_Distance_Sphere method – comes with Mysql 5.7+.

Assume that, you want to get closest user to given point: 41.0049823,28.7319855

<?php
$latitude = 41.0049823;
$longitude = 28.7319855;

$sql =  "SELECT users.id, users.username, ST_Distance_Sphere(point(users.longitude, users.latitude),point({$longitude}, {$latitude})) as distance FROM users";

So you’ll get distance as meter unit. Not that, this method uses longitude-latitude pair. Not latitude-longitude. It’s important :) I don’t know why but I always use lat-lng convention.

Let’s make another query: Get users within 100 meters for given point.

<?php
$latitude = 41.0049823;
$longitude = 28.7319855;

$sql =  "SELECT users.id, users.username, ST_Distance_Sphere(point(users.longitude, users.latitude),point({$longitude}, {$latitude})) as distance FROM users WHERE distance < 100"

I was using some sinus, cosinus, tangant and so other functions before ST_Distance_Sphere. That query was running slow and sometimes it wasn’t result correct. Thanks ST_Distance_Sphere for making our life easier :)

Leave a Reply

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