SQL Query, Select Nearest Places by a Given Coordinates

SQL query, select nearest places by a given coordinates

here’s the PHP formula for calculating the distance between two points:

function getDistanceBetweenPointsNew($latitude1, $longitude1, $latitude2, $longitude2, $unit = 'Mi') 
{
   $theta = $longitude1 - $longitude2;
   $distance = (sin(deg2rad($latitude1)) * sin(deg2rad($latitude2))+
               (cos(deg2rad($latitude1)) * cos(deg2rad($latitude2)) * cos(deg2rad($theta)));
   $distance = acos($distance); $distance = rad2deg($distance); 
   $distance = $distance * 60 * 1.1515;

   switch($unit) 
   { 
     case 'Mi': break;
     case 'Km' : $distance = $distance * 1.609344; 
   } 
   return (round($distance,2)); 
}

then add a query to get all the records with distance less or equal to the one above:

$qry = "SELECT * 
        FROM (SELECT *, (((acos(sin((".$latitude."*pi()/180)) *
        sin((`geo_latitude`*pi()/180))+cos((".$latitude."*pi()/180)) *
        cos((`geo_latitude`*pi()/180)) * cos(((".$longitude."-
        `geo_longitude`)*pi()/180))))*180/pi())*60*1.1515*1.609344) 
        as distance
        FROM `ci_geo`)myTable 
        WHERE distance <= ".$distance." 
        LIMIT 15";

and you can take a look here for similar computations.

and you can read more here

Update:

you have to take in mind that to calculate longitude2 and longitude2 you need to know that:

Each degree of latitude is approximately 69 miles (111 kilometers) apart. The range varies (due to the earth's slightly ellipsoid shape) from 68.703 miles (110.567 km) at the equator to 69.407 (111.699 km) at the poles. This is convenient because each minute (1/60th of a degree) is approximately one mile.

A degree of longitude is widest at the equator at 69.172 miles (111.321) and gradually shrinks to zero at the poles. At 40° north or south the distance between a degree of longitude is 53 miles (85 km).

so to calculate $longitude2 $latitude2 according to 50km then approximately:

$longitude2 = $longitude1 + 0.449; //0.449 = 50km/111.321km
$latitude2 = $latitude1 + 0.450; // 0.450 = 50km/111km

How to find nearest location using latitude and longitude from SQL database?

SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance FROM markers HAVING distance < 25 ORDER BY distance LIMIT 0 , 20;

sql query to get first closest locations by latitude and longitude provided as feilds in table

Your code in your question isn't doing anything like calculating distance between two points. It would be a good idea to do a little research on how to calculate distance between two points - that's where you'll need to start. You should be able to adapt my answer to your scenario, but it would be a good idea to understand the math involved if you'll be working with coordinates.

There's lots of info on Stack Overflow about calculating the distance between two points, even specifically to PostgreSQL. Here's an example adapted from another Stack Overflow artical (PostgreSQL calculate distance between two points without using PostGIS):

SELECT
    id,
    longitude,
    latitude,
    (select SQRT(POW(69.1 * (latitude::float -  10::float), 2) + 
        POW(69.1 * (longitude::float - 15::float) * COS(latitude::float / 57.3), 2)
    )) AS Distance
FROM destination
ORDER BY Distance

I have hardcoded values of 10 for the latitude of the origin point, and 15 for the longitude. Those will need to be replaced by the lat/long of the point you're trying to compare to.

This will return all of the rows in your destination table, including a new column that is the distance (in miles) of the row from your origin point. It will be ordered by that distance as well, closest to farthest.

find the nearest location by latitude and longitude in postgresql

select * from (
SELECT  *,( 3959 * acos( cos( radians(6.414478) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(12.466646) ) + sin( radians(6.414478) ) * sin( radians( lat ) ) ) ) AS distance 
FROM station_location
) al
where distance < 5
ORDER BY distance
LIMIT 20;

How to find near stores from store location (latitude, longitude) from store table?

Spatial queries are better handled using the extension PostGIS. It has loads of really handy functions that make spatial queries very easy to write and to maintain. My suggestion:

Install Postgis (see this other answer)

Add a geometry column to your table, e.g.

SELECT AddGeometryColumn ('public','store','geom',4326,'POINT',2);

Create point geometries based on your latitude and longitude values:

UPDATE store SET geom = ST_MakePoint(longitude,latitude);

Index it (to make queries faster)

CREATE INDEX idx_store_geom ON store USING gist (geom);

After that, this is how a query to list the nearest neighbours of a given point would look like:

SELECT * FROM store
ORDER BY geom <-> ST_SetSRID(ST_MakePoint(92.215,111.12),4326)

Or if you want the nearest store to each store ..

SELECT * FROM store mds,
LATERAL (SELECT store_name,ST_Distance(geom,mds.geom) FROM store
         WHERE id <> mds.id
         ORDER BY geom <-> mds.geom
         LIMIT 1) c (closest_store,distance);

• The operator <-> stands for distance, so using it in the ORDER BY clause with LIMIT 1 selects only the record that is closest to a reference geometry.
• 4326 stands for the spatial reference system WGS84. It might vary depending on your coordinates.

Demo: db<>fiddle

---

Related Topics