Mysql Location Search

location.sql

--
-- Mysql Functions and Idea from http://www.xarg.org/2009/12/people-near-you-with-mysql/
-- Stored procedures from Steve.
-- Not using `point`, could leave out field from location table.
-- 

CREATE TABLE IF NOT EXISTS `location` (
  `vendor_id` INT unsigned NOT NULL,
  `lat` decimal(10,6) NOT NULL,
  `lon` decimal(10,6) NOT NULL,
  `point` geometry NOT NULL,
  `circle` geometry NOT NULL,
  PRIMARY KEY (`vendor_id`),
  SPATIAL KEY `point` (`point`),
  SPATIAL KEY `circle` (`circle`)
) ENGINE=MyISAM;


-- ================================================================================
--  Function: GeoPoint(lat, lon)
--    Create a geometric point based on the latitude and longitude.
-- ================================================================================
DROP FUNCTION IF EXISTS GeoPoint;
DELIMITER $$

CREATE FUNCTION GeoPoint(lat DOUBLE, lon DOUBLE)

  RETURNS geometry DETERMINISTIC
BEGIN  
    RETURN GeomFromText(CONCAT('POINT(', lat, ' ', lon, ')'));
END

$$
DELIMITER ;


-- ================================================================================
--  Function: GeoPolygon(latitude, longitude)
--    Create a 12 sided polygon (to approximate a circle) with a 20km diameter.
-- ================================================================================
DROP FUNCTION IF EXISTS GeoPolygon;
DELIMITER $$

CREATE FUNCTION GeoPolygon(lat DOUBLE, lon DOUBLE)
  RETURNS geometry
    DETERMINISTIC
BEGIN  
  
DECLARE i TINYINT DEFAULT 1;  
DECLARE a, b, c DOUBLE;  
DECLARE res TEXT;
DECLARE radius SMALLINT DEFAULT 20;
DECLARE corner TINYINT  DEFAULT 12;

SET res = CONCAT(lat + radius / 111.12, ' ', lon, ',');  
  
WHILE i < corner  do
    SET c = RADIANS(360 / corner * i);  
    SET a = lat + COS(c) * radius / 111.12;  
    SET b = lon + SIN(c) * radius / (COS(RADIANS(lat + COS(c) * radius / 111.12 / 111.12)) * 111.12);  
    SET res = CONCAT(res, a, ' ', b, ',');  
    SET i = i + 1;  
END WHILE;  
  
RETURN GeomFromText(CONCAT('POLYGON((', res, lat + radius / 111.12, ' ', lon, '))'));  

END
$$
DELIMITER ;


-- ================================================================================
-- Procedure: SetVendorLocation(vendorId, latitude, longitude)
--   Creates (inserts or updates) the location record for a vendor.
-- ================================================================================

DROP PROCEDURE IF EXISTS SetVendorLocation;

DELIMITER $$
CREATE PROCEDURE SetVendorLocation (IN _vendorid_ INT, IN _lat_ DOUBLE, IN _lon_ DOUBLE)
BEGIN

    DECLARE location_id INT UNSIGNED DEFAULT 0;

    SET @thepoint = GeoPoint(_lat_, _lon_);
    SET @thepoly  = GeoPolygon(_lat_, _lon_);

    INSERT INTO 
        location (`vendor_id`, `lat`, `lon`, `point`, `circle`) 
    VALUES
        (_vendorid_, _lat_, _lon_, @thepoint, @thepoly)
    ON DUPLICATE KEY UPDATE
        `point`  = @thepoint,
        `circle` = @thepoly;

    SELECT 1;

END
$$
DELIMITER ;


-- ================================================================================
-- Procedure: FindLocalVendors(latitude, longitude)
--   Finds vendors within a 20km radius.
-- ================================================================================

DROP PROCEDURE IF EXISTS FindLocalVendors;

DELIMITER $$
CREATE PROCEDURE FindLocalVendors (IN _lat_ DOUBLE, IN _lon_ DOUBLE)

BEGIN

SET @thepoint = GeoPoint(_lat_, _lon_);

SELECT 
    vendor_id 
FROM 
    location
WHERE 
    MBRContains(`circle`, @thepoint);
    
END
$$
DELIMITER ;