:::: I need help for MYSQL and PHP QUERY COMPUTATION and sorting
i have a database table called tbl_city i have attached a sample tbl_city which includes the latitude and the longtitude, w/ this database i wanna compute the distance from my city which is montreal to other cities and sort them ASC and DESC, given the latitude of montreal 45.5 and the longtitude is -73.5833333...
SELECT *, ((degrees(acos(sign(radians(45.5)) * sign(radians(latitude)) + cos(radians(45.5)) * cos(radians(latitude)) * cos(radians(-73.5833333 - longtitude))))) * 60) * 1.1515) * 1.609344 FROM tbl_city;
the answer is in kilometers, i have tried this sqlStmt but there's an error, it gives me null result, and i also want to sort it ascending or descending, can anyone help me with this... thanks....
CREATE TABLE `tbl_city` (
`country_id` char(3) default NULL,
`city` char(75) default NULL,
`accent_city` char(75) default NULL,
`province_id` char(3) default NULL,
`latitude` char(25) default NULL,
`longtitude` char(25) default NULL,
`id` int(11) NOT NULL auto_increment,
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=5538 ;
--
-- Dumping data for table `tbl_city`
--
INSERT INTO `tbl_city` (`country_id`, `city`, `accent_city`, `province_id`, `latitude`, `longtitude`, `id`) VALUES ('CA', 'abbey', 'Abbey', '11', '50.7333333', '-108.75', 1),
('CA', 'abbotsford', 'Abbotsford', '02', '49.05', '-122.3', 2),
('CA', 'abbotsford', 'Abbotsford', '10', '45.4333333', ' -72.8833333', 3),
('CA', 'abbottsford', 'Abbottsford', '02', '49.05', '-122.3', 4),
('CA', 'abee', 'Abee', '01', '54.2333333', '-113.0166667', 5),
('CA', 'aberdeen', 'Aberdeen', '11', '52.3166667', '-106.2833333', 6),
('CA', 'abord a plouffe', 'Abord รข Plouffe', '10', '45.55', ' -73.7333333', 7),
('CA', 'abrahams cove', 'Abrahams Cove', '05', '48.5166667', ' -58.9', 8),
('CA', 'acadia valley', 'Acadia Valley', '01', '51.15', '-110.2', 9),
('CA', 'acme', 'Acme', '01', '51.5', '-113.5', 10),
('CA', 'actinolite', 'Actinolite', '08', '44.55', ' -77.3333333', 11),
('CA', 'acton', 'Acton', '08', '43.6333333', ' -80.0333333', 12),
('CA', 'acton vale', 'Acton Vale', '10', '45.6333333', ' -72.5666667', 13),
('CA', 'adams landing', 'Adams Landing', '01', '58.3833333', '-115.1166667', 14);
Edited 1 time(s). Last edit at 04/26/2006 09:18AM by Bryan de Asis.