CODEWITHSUNDEEP
mysqlsql
Paul Peelen
Paul Peelen

Reputation: 10329

Select nearest longitude/latitude values from DB

I have a database with a few thousand positions. These are busstops. What I would like to do is to get the X amount of nearest values to an LON/LAT position.

This is the layout of my table:

CREATE TABLE IF NOT EXISTS `buss_StopPoints` (
  `Name` varchar(50) NOT NULL,
  `LocationNorthing` varchar(30) NOT NULL,
  `LocationEasting` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

These are some example rows:

INSERT INTO `buss_StopPoints` (`Name`, `LocationNorthing`, `LocationEasting`) VALUES ('Some station', '59.3195952318672', '18.0717401337168');
INSERT INTO `buss_StopPoints` (`Name`, `LocationNorthing`, `LocationEasting`) VALUES ('Some station 2', '59.3195772927918', '18.0717389396547');
INSERT INTO `buss_StopPoints` (`Name`, `LocationNorthing`, `LocationEasting`) VALUES ('Some station 3', '59.3234014331742', '18.0671617033088');
INSERT INTO `buss_StopPoints` (`Name`, `LocationNorthing`, `LocationEasting`) VALUES ('Some station 4', '59.3233921590479', '18.0671786573678');
INSERT INTO `buss_StopPoints` (`Name`, `LocationNorthing`, `LocationEasting`) VALUES ('Some station 5', '59.3313179695727', '18.061677395945');

My question: How can I make a select statement that selects the nearest X amount of rows from the DB, both in negative and positive direction. (e.g. lets say I ask for 59.3234014331742,18.0671617033088 and want the nearest 4 stations, which is this case should return all).

Upvotes: 0

Views: 471

Answers (1)

manuskc
manuskc

Reputation: 794

LAT = latitude value
LON = longitude value

SELECT Name, (6371 * acos( cos( radians(LAT) ) * cos( radians( LocationNorthing ) ) * cos( radians( LON ) - radians(LocationEasting) ) + sin( radians(LAT) ) * sin( radians(LocationNorthing) ) )) AS distance order by distance

Upvotes: 2

Related Questions