CODEWITHSUNDEEP
phplaravelsql-order-bylatitude-longitude
aymen medjader
aymen medjader

Reputation: 75

Order by latitude and longitude

I am working on a project used to post adverts, this project is based on Localisation so when user post his adverts the application detect his position so when other people get intersted on his adverts they can finde him on Maps. The problem is that I want to order those adverts from the nearest one to the farest one using latitude and longitude of the adverts and also the latitude and longitude of the one who using the app

public function get_searched(Request $request){
    $annonce =DB::table('annonces')
    ->where('nomAnnonce','like','%'.$request->input("nomAnnonce").'%')
    ->orderby(((('latitude'- $request->input("lat"))*('latitude'-$request->input("lat"))) + (('longitude' - $request->input("lon"))*('longitude' - $request->input("lon")))), 'ASC')
    ->get();
    echo $annonce;}

This is all that I can do and that give me a wrong result:

SQLSTATE[42S22]: Column not found: 1054 Champ '1359.309643206' inconnu dans 
order clause (SQL: select * from `annonces` where `nomAnnonce` like %P% 
order by `1359`.`309643206` asc)

If anybody can help that's going to be a great pleasure

Upvotes: 1

Views: 2295

Answers (3)

Luis felipe De jesus Munoz
Luis felipe De jesus Munoz

Reputation: 7933

Try using this:

public function get_searched(Request $request){
    $annonces = DB::table("annonces")
    ->where('nomAnnonce','like','%'.$request->input("nomAnnonce").'%')
    ->orderBy(DB::raw("3959 * acos( cos( radians({$request->input('lat')}) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(-{$request->input('lon')}) ) + sin( radians({$request->input('lat')}) ) * sin(radians(latitude)) )"), 'ASC')
    ->get()
    dd($annonces);
}

This approach uses the Spherical Law of Cosines to get the distance

Upvotes: 2

calm
calm

Reputation: 400

public function get_searched(Request $request){
    $annonce =DB::table('annonces')
    ->select('*', 
    DB::raw(((('latitude'- $request->input("lat"))*('latitude'-$request->input("lat"))) + (('longitude' - $request->input("lon"))*('longitude' - $request->input("lon")))) 
            as 'distance'
            )
    ->where('nomAnnonce','like','%'.$request->input("nomAnnonce").'%')
    ->orderby('distance', 'ASC')
    ->get();
    echo $annonce;}

Upvotes: 0

kmuenkel
kmuenkel

Reputation: 2789

As it happens, I've worked on some projects that did very similar things, and put together a couple helper functions that can help. There's an algorithm that calculates distances between two sets of coordinates, so these helpers will either execute that for you, or produce the SQL necessary to execute it within the database. The distance_sql() one sounds like what you're looking for. By making the query do the work instead of the script, you get the opportunity to sort and paginate the result-set before it comes back. I'd recommend placing a query-scope method in your model that calls on it.

If you haven't already done so, create an app/helpers.php file and add this to your composer.json file and then run composer install:

"autoload": {
    "files": [
        "app/helpers.php",
        ...
    ],
    ...
},
...

And finally, place these in your helpers.php:

if (!function_exists('distance')) {
    /**
     * Calculate the distance between two sets of coordinates
     *
     * @param float $latitude1
     * @param float $longitude1
     * @param float $latitude2
     * @param float $longitude2
     * @param float $padding
     * @return float
     */
    function distance($latitude1, $longitude1, $latitude2, $longitude2, $padding = 0.0)
    {
        $earthRadiusInMiles = 3959;

        $latitude1 = deg2rad($latitude1);
        $latitude2 = deg2rad($latitude2);
        $longitude1 = deg2rad($longitude1);
        $longitude2 = deg2rad($longitude2);

        $miles = $earthRadiusInMiles * acos(
            cos($latitude1) * cos($latitude2)
            * cos($longitude2 - $longitude1)
            + sin($latitude1) * sin($latitude2)
        );

        $miles += $padding * $miles;

        return round($miles, 3);
    }
}

if (!function_exists('distance_sql')) {
    /**
     * Generate the SQL needed to calculate the distance between two sets of coordinates
     *
     * @param float|string $latitude1
     * @param float|string $longitude1
     * @param float|string $latitude2
     * @param float|string $longitude2
     * @param float $padding
     * @return string
     */
    function distance_sql($latitude1, $longitude1, $latitude2, $longitude2, $padding = 0.0)
    {
        $earthRadiusInMiles = 3959;

        $latitude1 = 'RADIANS('.DB::connection()->getPdo()->quote($latitude1).')';
        $latitude2 = "RADIANS($latitude2)";
        $longitude1 = 'RADIANS('.DB::connection()->getPdo()->quote($longitude1).')';
        $longitude2 = "RADIANS($longitude2)";

        $sql = "$earthRadiusInMiles * ACOS("
            . "COS($latitude1) * COS($latitude2)"
            . " * COS($longitude2 - $longitude1)"
            . " + SIN($latitude1) * SIN($latitude2))";

        $sql .= " + $padding * $sql";

        return $sql;
    }
}

Upvotes: 0

Related Questions