How do I query by geolocation in Laravel 5?

Google has a nice API for that. After putting in a location, validate it via API. If you get lat and longitude back it's valid and save those. Any subsequent request use those two fields for reference.

That's how https://myinformeddecision.com implements it. (Old client)

If its not project that require high degree of precision, and you like high performance I recommend to keep things simple.

Store latitude and longitude in your database, use ~0.018 as one mile of distance, and search in square instead of circle so you can do simple WHERE lat < $lat + (10 * 0.018) ...

It looks dirty but at the end:

• its very light for database
• most of applications need low level of geo precision (if its high go with API)
• geo search results are usually returned in square shape maps not circles

You will have better results with a DB:select statement.

Also,

Schema::create('candidates', function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->string('street');
            $table->string('city', 50);
            $table->string('state', 2);
            $table->string('zip', 12);
            $table->string('phone', 30)->nullable();
            $table->float('latitude', 10, 6);
            $table->float('longitude', 10, 6);
            $table->timestamps();
        });

$circle_radius = 3959;
$max_distance = 20;
$lat = {your_lat};
$lng = {your_lng};

 return $candidates = DB::select(
               'SELECT * FROM
                    (SELECT id, name, address, phone, latitude, longitude, (' . $circle_radius . ' * acos(cos(radians(' . $lat . ')) * cos(radians(latitude)) *
                    cos(radians(longitude) - radians(' . $lng . ')) +
                    sin(radians(' . $lat . ')) * sin(radians(latitude))))
                    AS distance
                    FROM candidates) AS distances
                WHERE distance < ' . $max_distance . '
                ORDER BY distance
                OFFSET 0
                LIMIT 20;
            ');

This will get you started..

getvma: Precocious but heavy for database. It would also work slow on large tables.

@leber

I'm about to do this and I like your suggestion. Thanks ;)

Like @leber states, it could get very overwhelming on a very basic server if you have more than just a few thousand records. You should cache your search results for as long as it is relevant to your circumstances.

Of course, I do not know the full extent of your requirements, but the solution i've posted above will provide the specific results you asked for.

If you are looking for a highly optimized environment I recommend you read into http://postgis.net/features/ my db of choice is postgres but i cant imagine that mysql and maria wont have something similar.

You have to consider the boundaries of a circle as opposed to a square and determine if at a 20 mile radius you need that type of geographic precision in your selection.

However, you have both options now in your toolbox.

Thanks squad! I just finished the frontend for allowing users to select location using Google Maps typeahead API. Post is here: https://medium.com/@connorleech/build-google-maps-typeahead-functionality-with-vue-js-and-laravel-5-3-b75986c77df1#.j717fbnuu

@leber would the more simplistic query be:

DB::select('WHERE lat <'. $lat .'+ (10 * 0.018)');

I am going to have the more full featured one but if run into performance issues this is an awesome backup plan. Thank you

@leber

WHERE lat < $lat + (10 * 0.018) 

And what about lng? should that not also be checked?

By the way.

If someone need high performance + high precision:

1. make columns latitude_floor, longitude_floor with index
2. keep latitude_floor = FLOOR(latitude), longitude_floor = FLOOR(longitude)
3. use @getvma formula but add for example WHERE latitude_floor IN(".intval($latitude-1).",".intval($latitude+1).") AND longitude_floor IN(".intval($latitude-1).",".intval($latitude-+1).")

It would eliminate search region before advanced calculation.

@leber

If i want to use km instead of miles, how does that changes the formula? And by the way what exactly is 0.018? Why that value?

Source: http://gis.stackexchange.com/questions/142326/calculating-longitude-length-in-miles

Accordingly 1km would seems to be 0.0117 (1/85).

@leber

Just wanted to say, brilliant answer.

Quick question, One mile was set to 0.018, then you said

"You can also change 0.018 with 0.014 or DEGREES(0.000253) which seems to be more common.".

So is 1 mile (I assume USA mile) = 0.018 or 0.014?

If you want to alleviate some of the heavy lifting on the database side, ElasticSearch can do geo queries: https://www.elastic.co/guide/en/elasticsearch/reference/current/geo-queries.html

@paulw1107: the thing is 1° is different at the equator and different at the north pole.

According to http://www.colorado.edu/geography/gcraft/warmup/aquifer/html/distance.html 1° ≈ 69 miles

1 mile = 1/69 = 0.014.

According to @getvma It works perfect. Just only one thing to know, How to use paginate with this solution. I want to paginate after 10 records. paginate() method is not working here.

try removing the LIMIT and OFFSET from the original query

Building on the solution provided by @leber I would suggest eliminating everything outside the square.

The steps instead of what leber suggested:

1. make columns latitude_int, longitude_int (type integer, put an index on them)
2. store the latitude and longitude with x10^7. I use this number, because most services use 7 decimals.
3. use @getvma formula but add this: $distance_square = ($distance*(10^7)*0.018);

WHERE latitude_int IN(".int(($latitude10^7)-$distance_square).",".int(($latitude10^7)+$distance_square).") AND longitude_int IN(".int(($latitude10^7)-$distance_square).",".int(($latitude10^7)+$distance_square).")

Credits to @leber

I use this version, because the flooring is too inaccurate for me. It's not eliminating enough. This way, everything outside the square gets eliminated.

@angeloj That's a good idea, but what does the final full select statement look like - can you paste any code please?

Also, should the 2nd part of your statement above reference longitude rather than latitude? E.g.

...AND longitude_int IN(".int(($longitude10^7)-$distance_square).",".int(($longitude10^7)+$distance_square).")

Thanks

Logan Henson at tighten has a pretty good and elegant solution for this using Mysql's ST_Distance_Sphere ( MySQL 5.7 )

// Eloquent Scope:
public function scopeCloseTo(Builder $query, $latitude, $longitude)
{
    return $query->whereRaw("
       ST_Distance_Sphere(
            point(longitude, latitude),
            point(?, ?)
        ) * .000621371192 < delivery_max_range
    ", [
        $longitude,
        $latitude,
    ]);
}

// Using the scope:
return Restaurant::closeTo($myLatitude, $myLongitude); 

@sean.tilley good solution, thanks for updating us all

@sean.tilley I like your answer and I think I will implement this function that way. but what is the 0.000621371192 value?

I think it has somethind todo with the circle radius but I don't know exactly. Can you please provide the correct value (or formula) for kilometers?

And one more thing, the 'delivery_max_range' is the max distance? so I should be fine to replace this with a ? and add a third paramter for the prepared statement? Am I correct?

I found the answer by myself.

In the Mysql documentation I found this:

ST_Distance_Sphere(g1, g2 [, radius])

Returns the mimimum spherical distance between two points and/or multipoints on a sphere, in meters...

So the 0.00062... is to convert the meters into miles.