database.sql

-- DEFINICION DE LA TABLA mercados
CREATE TABLE markers(
    id      int(11)         NOT NULL    PRIMARY KEY AUTO_INCREMENT, 
    name    varchar(60)     NOT NULL,
    address varchar(80)     NOT NULL,
    lat     float(10,6)     NOT NULL,
    lng     float(10,6)     NOT NULL
);


-- function que calcula la distancia
CREATE FUNCTION distance_markers (lat float, lng float, my_lat float, my_lng float )
RETURNS float
DETERMINISTIC
READS SQL DATA

RETURN ( 
    3959 
    * acos( 
        cos( radians(my_lat) )
        * cos( radians( lat ) ) 
        * cos( radians( lng ) - radians(my_lng) ) 
        + sin( radians(my_lat) ) 
        * sin( radians( lat ) ) 
        ) 
    );



-- SEED DE LA TABLA mercados
id     name     address     lat     lng 
INSERT INTO markers (name, address, lat,lng) VALUES
    ("Frankie Johnnie & Luigo Too 939",         "W El Camino Real, Mountain View, CA",      37.386337,     -122.085823),
    ("Round Table Pizza: Mountain View 570",    "N Shoreline Blvd, Mountain View, CA",      37.402653,     -122.079353),
    ("Amici's East Coast Pizzeria 790",         "Castro St, Mountain View, CA",             37.387138,     -122.083237),
    ("Tony & Alba\'s Pizza & Pasta 619",        "Escuela Ave, Mountain View, CA",           37.394012,     -122.095528),
    ("Round Table Pizza: Mountain View 570",    "N Shoreline Blvd, Mountain View, CA",      37.402653,     -122.079353),
    ("Oregano's Wood-Fired Pizza4546",          "El Camino Real, Los Altos, CA",            37.401726,     -122.114647);

query.sql

-- Columna     Tipo         Nulo     Primario 
-- id       int(11)         No      Sí 
-- name     varchar(60)     No                    
-- address  varchar(80)     No                    
-- lat      float(10,6)     No                    
-- lng      float(10,6)     No     


SELECT 
    m.*, 
    distance_markers(m.lat, m.lng, 37.0, -122.0) AS distance
FROM markers m
    HAVING distance < 28

ORDER BY distance

LIMIT 0 , 20;