Closest companies, MySQL

Closest companies

#MySQL 5.7.12
#please drop objects you've created at the end of the script 
#or check for their existance before creating
#'\\' is a delimiter

DROP TABLE IF EXISTS geocodes ;
DROP TABLE IF EXISTS company_has_address ;

CREATE TABLE geocodes 
(
    address_id integer not null primary key, 
    geocodes_latitude float, 
    geocodes_longitude float
) ;
CREATE TABLE company_has_address 
(
    companys_id integer not null, 
    address_id integer not null references geocodes(address_id)
) ;

INSERT INTO geocodes
VALUES
(1, 33.20, -86.78),
(2, 33.20, -86.79),
(3, 33.21, -86.80),
(4, 33.19, -86.80),
(5, 33.19, -86.80) ;

INSERT INTO company_has_address
VALUES
(1, 1),
(2, 2),
(2, 3),
(2, 4),
(1, 5) ;

SELECT
	companys_id, min(distance) AS distance
FROM
(
	SELECT      
	     company_has_address.companys_id,     
	     (3959 * ACOS(COS(RADIANS(33.2003486)) * COS(RADIANS(geocodes_latitude)) * COS(RADIANS(geocodes_longitude) - RADIANS(-86.7852465)) + SIN(RADIANS(33.2003486)) * SIN(RADIANS(geocodes_latitude)))) AS distance
	FROM
	     geocodes
	     LEFT JOIN company_has_address ON company_has_address.address_id = geocodes.address_id
	WHERE
	     company_has_address.companys_id != 5884
) AS s0
GROUP BY companys_id
ORDER BY distance ASC
LIMIT 50 ;

DROP TABLE company_has_address ;
DROP TABLE geocodes ;


run \| edit \| history \| help	0