Linde002::PHP

Finding geographical distances between two points in MySQL 5

CREATE PROCEDURE distance(IN startlat double, IN startlon double)
BEGIN
	SELECT
	alias.*,
	(
		(
			(acos( sin((startlat*pi()/180))*sin((alias.lat*pi()/180))
				+
			cos((startlat*pi()/180))*cos((alias.lat*pi()/180))
				*
			cos(((startlon - alias.lon)*pi()/180))))*180/pi()
		)*60*1.1515*1.609344
	) as distance
	FROM TABLE_WITH_POINTS alias
	WHERE
		alias.lat IS NOT NULL
	AND
		alias.lon IS NOT NULL
	AND
		alias.lat != ''
	AND
		alias.lon != ''
	ORDER BY distance ASC;
END
$$

CREATE PROCEDURE distance(IN startlat double, IN startlon double, IN dist int)
BEGIN
	SELECT
	alias.*,
	(
		(
			(acos( sin((startlat*pi()/180))*sin((alias.lat*pi()/180))
				+
			cos((startlat*pi()/180))*cos((alias.lat*pi()/180))
				*
			cos(((startlon - alias.lon)*pi()/180))))*180/pi()
		)*60*1.1515*1.609344
	) as distance
	FROM TABLE_WITH_POINTS alias
	WHERE
		alias.lat IS NOT NULL
	AND
		alias.lon IS NOT NULL
	AND
		alias.lat != ''
	AND
		alias.lon != ''
	HAVING distance < dist
	ORDER BY distance ASC;
END
$$

4 Comments »

• Web 2.0 Announcer

  Finding geographical distances between two points in MySQL 5…

  [...]For a project I’m working on I needed to find the distances between two points to be able to let users ask the application questions like: “How many points of type X (coworkers, jobs, gas stations, donutshops) are within a radius of point y (m…

  Trackback | October 31, 2008
• Dave

  Have you considered utilizing the spatial features of MySql … there is a Distance() function that would give you the same result.

  Comment | October 31, 2008
• Robert van der Linde

  Yes I have, and the examples I found look promising. However for a variety of reasons this isn’t a feasibile option for the company. This meant I had to solve it the old-fashioned mathematical way

  With some changes you can run this as a regular query but creating a procedure out of it and idexing the lat/lon fields increases the speed a lot. To check one point against 3000 other records takes only 0.003 seconds on my MacBook.

  Comment | October 31, 2008
• Michael

  You can also cache or pre-calculate most of these values and index these pre-calculations. This will make for a huge speed increase.

  I had to write something very similar for work not long ago, I think mines a little different, will try digging it up later and posting back if i can find it.

  Comment | October 31, 2008

RSS feed for comments on this post. TrackBack URL

Leave a comment