PHP MySQL and Geolocation

Geo Location Radius Search Using PHP and MySQL

I have gone with below -

SELECT id,
       name,
       lat,
       lng,
       ROUND((6371 * acos(
                       cos(radians($lat)) * cos(radians(lat)) * cos(radians(lng) - radians($lng)) +
                       sin(radians($lat)) * sin(radians(lat)))), (2)) AS distance
FROM jobs
HAVING distance < 50
ORDER BY distance;

I have done benchmarking with data and found this slightly faster than Mukesh's answer and 2x better than @jision's answer.

Geolocation MySQL Query

The problem is the way you are storing data in the database is not suited for the type of task you are performing. Using Point values in Geometry data points is the way to go. Actually coded something 4+ years back for this purpose, but having issues finding it. But this post seems to cover it well.

EDIT Okay, found my old code, but it’s referring to old client data that I obviously cannot share. But the key to speed with coordinates in databases is using POINT data stored in the database table with the type of GEOMETRY. More details here on the official MySQL site. Since I have needed a reason to revisit this type of code—and the concepts—for a while here is a quick MySQL script I whipped up to create a sample table with sample data to convey the basic concepts. Once you understand what is happening, it opens up lots of cool options.

Also found this great/simple explanation of the concept as well.

And found another great assessment of spatial data in MySQL 5.6. Lots of great info on indexes & performance. Specifically regarding MySQL spatial index performance:

MyISAM tables support Spatial indexes, so the above queries will use those indexes.

And on the other side of that:

The InnoDB engine does not support spatial indexes, so those queries will be slow.

And here is my basic MySQL testing scripts to help illustrate the concept:

/* Create the database `spatial_test` */
CREATE DATABASE `spatial_test` CHARACTER SET utf8 COLLATE utf8_general_ci;

/* Create the table `locations` in `spatial_test` */
CREATE TABLE `spatial_test`.`locations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `coordinates` point NOT NULL,
  UNIQUE KEY `id` (`id`),
  SPATIAL KEY `idx_coordinates` (`coordinates`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

/* Insert some test data into it. */
INSERT INTO `spatial_test`.`locations` (`id`, `coordinates`) VALUES (NULL, GeomFromText('POINT(27.174961 78.041822)'));
INSERT INTO `spatial_test`.`locations` (`id`, `coordinates`) VALUES (NULL, GeomFromText('POINT(27.985818 86.923596)'));
INSERT INTO `spatial_test`.`locations` (`id`, `coordinates`) VALUES (NULL, GeomFromText('POINT(44.427963 -110.588455)'));
INSERT INTO `spatial_test`.`locations` (`id`, `coordinates`) VALUES (NULL, GeomFromText('POINT(19.896766 -155.582782)'));
INSERT INTO `spatial_test`.`locations` (`id`, `coordinates`) VALUES (NULL, GeomFromText('POINT(40.748328 -73.985560)'));
INSERT INTO `spatial_test`.`locations` (`id`, `coordinates`) VALUES (NULL, GeomFromText('POINT(40.782710 -73.965310)'));

/* A sample SELECT query that extracts the 'latitude' & 'longitude' */
SELECT x(`spatial_test`.`locations`.`coordinates`) AS latitude, y(`spatial_test`.`locations`.`coordinates`) AS longitude FROM `spatial_test`.`locations`;

/* Another sample SELECT query calculates distance of all items in database based on GLength using another set of coordinates. */
SELECT GLength(LineStringFromWKB(LineString(GeomFromText(astext(PointFromWKB(`spatial_test`.`locations`.`coordinates`))), GeomFromText(astext(PointFromWKB(POINT(40.782710,-73.965310))))))) AS distance
FROM `spatial_test`.`locations`
;

/* Yet another sample SELECT query that selects items by using the Earth’s radius. The 'HAVING distance < 100' equates to a distance of less than 100 miles or kilometers based on what you set the query for. */
/* Earth’s diameter in kilometers: 6371 */
/* Earth’s diameter in miles: 3959 */
SELECT id, (3959 * acos(cos(radians(40.782710)) * cos(radians(x(`spatial_test`.`locations`.`coordinates`))) * cos(radians(y(`spatial_test`.`locations`.`coordinates`)) - radians(-73.965310)) + sin(radians(40.782710)) * sin(radians(x(`spatial_test`.`locations`.`coordinates`))))) AS distance 
FROM `spatial_test`.`locations`
HAVING distance < 100
ORDER BY id
;

How to write SQL JOIN query with geolocation in php

Before your having clause, join to the profile table like so:

Inner join profile on offerings.offering_profile_id = profile.profile_id

Then include at end:

Order by offerings.offering_id

Also make sure to include in your select statement
Profile.fullname

Also change having to where and add this into your where condition:
And offerings.offering_meal_id = $meal_id

Using PHP and MySQL to calculate geolocation distance

Note that circle distance isn't going to be precise enough if you're talking about large distances (thousands of miles, for example), as the earth's surface isn't flat. If you need a better formula for geo-distance calculation, you can use something like this:

$dlat = ((double)$lat) / 57.29577951;
$dlon = ((double)$lon) / 57.29577951;

$sql = "SELECT *
        FROM dirlistings
        WHERE 3963.0 * acos(sin(latitude / 57.29577951) * sin($dlat) + cos(latitude / 57.29577951) * cos($dlat) * cos($dlon - longitude / 57.29577951)) < MAX_DIST
        ORDER BY acos(sin(latitude / 57.29577951) * sin($dlat) + cos(latitude / 57.29577951) * cos($dlat) * cos($dlon - longitude / 57.29577951))
        ";

The distances here are in miles - make sure to specify correct max distance - and this formula will give very close results for distances of even ten thousand miles. Note though that such computation is quite time- and power-intensive and if you are not dealing with large distances (i.e. nothing more than a couple hundred miles), then you should use a quicker approximation.

PHP / MySQL - Find all items in 500 meters radius from actual gps coordinates

Calculating the distance between two coordinates isn't actually that difficult given the haversine formula.

SELECT 
  -- stuff here
  , ( 6371000 * acos( cos( radians(45.815005) ) * cos( radians( stuff.lat ) ) * cos( radians( stuff.lng ) - radians(15.978501) ) + sin( radians(45.815005) ) * sin(radians(stuff.lat)) ) ) AS distance 
FROM 
  stuff
HAVING 
  distance < 500

Referenced Answer

Necessary changes from the original answer:

1. The constant offered in the original answer supplied the values for miles or kilometers. I've changed the constant here to work with meters.

2. The constants have changed to use your coordinates. You might want to adapt the query a little further to make those parameters instead of constants.

3. The having expression changed a little to reflect your desire for 500 meters. Again, this might be something you want to parameterize.

---

Related Topics