[SOLVED] Efficiently Querying Nearby Warehouses Based on Latitude and Longitude Within a Specified Radius

Efficiently Querying Nearby Warehouses Based on Latitude and Longitude Within a Specified Radius

I have a table named warehouses with the following schema:

CREATE TABLE warehouses (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    latitude FLOAT,
    longitude FLOAT
);

I need to find nearby warehouses from my current location, which is specified by latitude and longitude, within a given radius. This operation must be performed using an SQL query rather than manual calculations or functions.

Requirements:

Input Parameters:

current_latitude (FLOAT): Latitude of the current location. current_longitude (FLOAT): Longitude of the current location. radius (FLOAT): The radius within which to search for nearby warehouses (in kilometers).

Output:

A list of warehouses within the specified radius from the current location.

Constraints:

The query should be efficient and capable of handling a large dataset. The calculation should account for the Earth's curvature.

Questions:

Query Structure:

How can I structure an SQL query to find all warehouses within the specified radius from the current location? What mathematical formula should be used to calculate the distance between two geographical points (latitude and longitude) within the query?

Solution

For those who might need it, I have solved this problem.

$lati = (float)$request->latitude;
$longt = (float)$request->longtitude;
$rad = (int)$request->radius;



$distance_result = "(6371 * acos(cos(radians($lati))
* cos(radians(latit))
* cos(radians(longt)
- radians($longt))
+ sin(radians($lati))
* sin(radians(latit))))";


$warhouses = Warhouse::select('id', 'name')
    ->selectRaw("{$distance_result} AS distance")
    ->whereRaw("{$distance_result} < ?", [$rad])
    ->get();



return view('test')->with('nearbys', $warhouses);