How to find nearest cities from Latitude and Longitude

https://github.com/grimzy/laravel-mysql-spatial

Level 46

You could use this package. It would involve some mucking about with the database, but would save you from some geometry ...

1 like

Web Confection

https://stackoverflow.com/questions/1006654/fastest-way-to-find-distance-between-two-lat-long-points

Level 3

Best Answer

Level 28

If you already have the latitude and longitudes in your database you might as well query them yourself. This is from an older project (so may not work or be the best method) where I'm doing something similar:

$cities = City::select(DB::raw('*, ( 6367 * acos( cos( radians('.$latitude.') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians('.$longitude.') ) + sin( radians('.$latitude.') ) * sin( radians( latitude ) ) ) ) AS distance'))
    ->having('distance', '<', 25)
    ->orderBy('distance')
    ->get();

5 likes

mdeorue

https://maps.googleapis.com/maps/api/geocode/json?latlng=lat,long

Level 6

If you use google maps api?

Reponse:

json
{
   "results" : [
      {
         "address_components" : [
            {
               "long_name" : "277",
               "short_name" : "277",
               "types" : [ "street_number" ]
            },
            {
               "long_name" : "Bedford Avenue",
               "short_name" : "Bedford Ave",
               "types" : [ "route" ]
            },
            {
               "long_name" : "Williamsburg",
               "short_name" : "Williamsburg",
               "types" : [ "neighborhood", "political" ]
            },
            {
               "long_name" : "Brooklyn",
               "short_name" : "Brooklyn",
               "types" : [ "sublocality", "political" ]
            },
            {
               "long_name" : "Kings",
               "short_name" : "Kings",
               "types" : [ "administrative_area_level_2", "political" ]
            },
            {
               "long_name" : "New York",
               "short_name" : "NY",
               "types" : [ "administrative_area_level_1", "political" ]
            },
            {
               "long_name" : "United States",
               "short_name" : "US",
               "types" : [ "country", "political" ]
            },
            {
               "long_name" : "11211",
               "short_name" : "11211",
               "types" : [ "postal_code" ]
            }
         ],
         "formatted_address" : "277 Bedford Avenue, Brooklyn, NY 11211, USA",
         "geometry" : {
            "location" : {
               "lat" : 40.714232,
               "lng" : -73.9612889
            },
            "location_type" : "ROOFTOP",
            "viewport" : {
               "northeast" : {
                  "lat" : 40.7155809802915,
                  "lng" : -73.9599399197085
               },
               "southwest" : {
                  "lat" : 40.7128830197085,
                  "lng" : -73.96263788029151
               }
            }
         },
         "place_id" : "ChIJd8BlQ2BZwokRAFUEcm_qrcA",
         "types" : [ "street_address" ]
      },

Level 10

very nice @sutherland , but what is 'distance' ? It gave me this error -

 Syntax error or access violation: 1463 Non-grouping field 'distance' is used in HAVING clause

Do I have to have in my table , a field called distance ?

Level 28

The raw query in the select is using AS to calculate the distance. I just copied it from an old Laravel 4 project so I'm not sure what's changed.

Maybe try changing ->having('distance', '<', 30) to ->where('distance', '<', 30)?

Level 10

@sutherland , does have any Column called 'distance' ? what value do I have to insert in this column ? It is giving this error -

Column not found: 1054 Unknown column 'distance' in 'where clause'

Column not found: 1054 Unknown column 'distance' in 'where clause' (SQL: select *, ( 6367 * acos( cos( radians(-21.1774558) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(-47.8063571) ) + sin( radians(-21.1774558) ) * sin( radians( latitude ) ) ) ) AS distance from `loc_neighborhoods` where `distance` < 30 order by `distance` asc)

Level 28

You don't need a distance column, the raw select statement is calculating it for you.

I just tested this on Laravel 5.6 with no issues:

$latitude = 25.000;
$longitude = 25.000;

$cities = City::selectRaw('*, ( 6367 * acos( cos( radians( ? ) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians( ? ) ) + sin( radians( ? ) ) * sin( radians( latitude ) ) ) ) AS distance', [$latitude, $longitude, $latitude])
    ->having('distance', '<', 30)
    ->orderBy('distance')
    ->get();

What database driver are you using?

2 likes

Level 10

Hello sutherland , thanks for the help , But still giving errors -

SQLSTATE[42000]: Syntax error or access violation: 1463 Non-grouping field 'distance' is used in HAVING clause (SQL: select *, ( 6367 * acos( cos( radians( 25 ) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians( 25 ) ) + sin( radians( 25 ) ) * sin( radians( latitude ) ) ) ) AS distance from `cities` having `distance` < 30 order by `distance` asc)

Are you using Mysql ?

Level 28

Yes, MySQL 5.7 and no issues here.

Level 10

@sutherland , now it is working , In my config , database . I changed to -

'strict' => false

It is working now .

Level 28

@murilo strange, I almost suggested that but in my config I had it set to true so I thought it wouldn't matter. I guess maybe I have that configured somewhere else...

Level 10

@sutherland , in this code - City::selectRaw('*, ()) you are getting all columns in the table, do you know how cold I get just those columns -

id , latitude , longitude

in this table ? Thanks