nearby_hotels.sql

DELIMITER $$

CREATE FUNCTION angle_point(lat double, lon double, deg double, dist double) RETURNS POINT DETERMINISTIC
BEGIN
  DECLARE dist2 double;
  DECLARE rdeg double;
  DECLARE rlat1 double;
  DECLARE rlon1 double;
  DECLARE rlat2 double;
  DECLARE rlon2 double;

  SET dist2 = dist / 6371;
  SET rdeg = RADIANS(deg);
  SET rlat1 = RADIANS(lat);
  SET rlon1 = RADIANS(lon);
  SET rlat2 = ASIN(SIN(rlat1) * COS(dist2) + COS(rlat1) * SIN(dist2) * COS(rdeg));
  SET rlon2 = rlon1 + ATAN2(
    SIN(rdeg) * SIN(dist2) * COS(rlat1),
    COS(dist2) - SIN(rlat1) * SIN(rlat2)
  );
  SET rlon2 = (rlon2 + 3*PI()) % (2*PI()) - PI();

  RETURN GeomFromText(CONCAT('POINT(', DEGREES(rlat2), ' ', DEGREES(rlon2), ')'));
END

$$

DROP PROCEDURE IF EXISTS nearby_hotels

$$

CREATE PROCEDURE nearby_hotels(lat double, lon double, dist double)
BEGIN

select angle_point(lat, lon, 225, dist) into @sw;
select angle_point(lat, lon, 45, dist) into @ne;
select x(@sw) into @lat1;
select x(@ne) into @lat2;
select y(@sw) into @lon1;
select y(@ne) into @lon2;

SELECT id, lat, lon
FROM hotels_two h
WHERE h.lat > @lat1 AND h.lat < @lat2 AND h.lon > @lon1 AND h.lon < @lon2
LIMIT 100;

END

$$

call nearby_hotels(37.774929, -122.419415, 100);