MySQL Forums
Forum List  »  PHP

:::: I need help for MYSQL and PHP QUERY COMPUTATION and sorting
Posted by: Bryan de Asis
Date: April 26, 2006 08:36AM

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.

Options: ReplyQuote


Subject
Written By
Posted
:::: I need help for MYSQL and PHP QUERY COMPUTATION and sorting
April 26, 2006 08:36AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.