sbaaaang - 1 year ago 36

SQL Question

I have

`$latitude = 29.6815400`

`$longitude = 64.3647100`

`SELECT *`

FROM places

WHERE latitude BETWEEN($latitude - 1, $latitude + 1)

AND longitude BETWEEN($longitude - 1, $logintude + 1)

LIMIT 15;

Do you think it's correct or do you suggest something else?

How to do the

`BEETWEEN`

I forgot to say that I can also use PHP for do anything before to run the query.

Note:

Answer

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
```