SQL search "near" longitude and latitude

Question

I'm trying to write a search bar that searches near a location. For now it's hard coded. The statement works until I add the like statements for longitude and latitude. Is there a better way to do this?

$searchString = explode(' ', $_GET["searchString"]);
$useLocation = $_GET["useCurrentLocation"]; 
if($useLocation == 1){
    foreach($searchString AS $key){
        $stmt = $db->prepare("SELECT * FROM locations WHERE Name LIKE '%{$key}%' AND Longitude Like '-79.8579' And Latitude Like '43.5237'"); 
        $stmt->execute();
        $result = $stmt->get_result();
        while($row = $result->fetch_assoc()){
            echo $row['Name'];
        }

    }
}

score 0 · Answer 1 · edited May 23 '17 at 10:30

You can find many solution on internet about "how to search by location"

There are some answer on Stackoverflow:

Or on Google:

http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

Here is my last query about that:

I get the longitude/latitude via Google Maps API. Enter an address, and get back the GPS coordinate. After, calculate with SQL the position and the objects around

$longitude_ref = 7.694731; $latitude_ref = 48.560719;

SELECT *
  FROM maps
  WHERE (
    6366 *
    acos(
      cos(radians(48.560719)) -- $latitude_ref
      *
      cos(radians(`latitude`))
      *
      cos(radians(`longitude`) - radians($longitude_ref)) -- $longitude_ref
      +
      sin(radians(48.560719)) * sin(radians(`latitude`)) -- $latitude_ref
    )
  ) <= 2 -- r in Km (here: 2 km)

You can also calculate the distance between two points:

SELECT pg.id, pg.*, pg1.*, 6366 * 2 *
    atan2(
      sqrt(
        SIN((RADIANS(pg1.latitude) - RADIANS(pg.latitude)) / 2) * SIN((RADIANS(pg1.latitude) - RADIANS(pg.latitude)) / 2) + COS(RADIANS(pg.latitude)) * COS(RADIANS(pg1.latitude)) * SIN((RADIANS(pg1.longitude) - RADIANS(pg.longitude)) / 2) * SIN((RADIANS(pg1.longitude) - RADIANS(pg.longitude)) / 2)    
      ),
      sqrt(
        1 - SIN((RADIANS(pg1.latitude) - RADIANS(pg.latitude)) / 2) * SIN((RADIANS(pg1.latitude) - RADIANS(pg.latitude)) / 2) + COS(RADIANS(pg.latitude)) * COS(RADIANS(pg1.latitude)) * SIN((RADIANS(pg1.longitude) - RADIANS(pg.longitude)) / 2) * SIN((RADIANS(pg1.longitude) - RADIANS(pg.longitude)) / 2)
      )
    ) as calculated_distance_final_km
  FROM perso_gps pg
  WHERE pg.calculated_dist IS NULL

Many examples on internet :)

SQL search "near" longitude and latitude

1 Answers1