SQL Select: toilets within 50KM of a certain position?

Question

I need to find toilets around me, say within 50KM, and I have my position in term of latitude and longitude, toilets in database looks like:

Toilet ID (primary)
Address
Latitude
Longitude

my location: my_lat, my_lon

is it possible to construct a statement that returns all toilets within 50KM of (my_lat, my_lon)? Something like

select * from ToiletTable where 
  SQRT((Latitude - my_lat)^2 + (Longitude - my_lon)^2) < 50

Thanks!

http://johndyer.name/post/LatitudeLongitude-Distance-Calculation-in-SQL-Server.aspx ? — Rich, Jun 05 '11 at 07:24
If only he had ended the question with "Please! I'm desperate!" — , Jun 05 '11 at 07:25
possible duplicate of [Calculating Great-Circle Distance with SQLite](http://stackoverflow.com/questions/2083182/calculating-great-circle-distance-with-sqlite) — Alix Axel, Jun 05 '11 at 08:34
see also http://stackoverflow.com/questions/2096385/formulas-to-calculate-geo-proximity — Alix Axel, Jun 05 '11 at 08:34
@Neil: Even though the actual gender is unknown to most of us, it was really nice of you to presume *him*. — Andriy M, Jun 05 '11 at 10:52

Khepri · Answer 1 · 2011-06-05T08:04:08.027

You are looking for the Haversine formula

Here are two full implementations, one in SQL: Haversine Implementation

EDIT:

Here's a Haversine implementation of a UDF in SQLite. Unfortunately it's against the iPhone, but at least you have the exact implementation you need. Now you just need to determine how to plug it in.

SQL inlined below

CREATE FUNCTION [dbo].[GetDistance]

(
  @lat1 Float(8),
  @long1 Float(8),
  @lat2 Float(8),
  @long2 Float(8)
)
RETURNS Float(8)
AS
BEGIN
  DECLARE @R Float(8);
  DECLARE @dLat Float(8);
  DECLARE @dLon Float(8);
  DECLARE @a Float(8);
  DECLARE @c Float(8);
  DECLARE @d Float(8);
  SET @R = 6371; --This value is 6371 for kilometers, 3960 for miles.
  SET @dLat = RADIANS(@lat2 - @lat1);
  SET @dLon = RADIANS(@long2 - @long1);
  SET @a = SIN(@dLat / 2) * SIN(@dLat / 2) + COS(RADIANS(@lat1))
                    * COS(RADIANS(@lat2)) * SIN(@dLon / 2) * SIN(@dLon / 2);
  SET @c = 2 * ASIN(MIN(SQRT(@a)));
  SET @d = @R * @c;
  RETURN @d;
END
GO

Thanks for your reply, the problem is, I am working on a SQLite database(ie a file) in Android, so I don't know if I can create my own function in addition to the database. Also, even with the function, how do I construct the SQL statement? — Heuristic, Jun 05 '11 at 07:30
This link gives some info for Android development and Haversine. It might point you in the right direction. http://devdiscoveries.wordpress.com/2010/02/01/android-distance-between-two-points-on-the-earth/ — Khepri, Jun 05 '11 at 07:37

score 0 · Accepted Answer · answered Mar 27 '12 at 14:56

0

Assuming you're not in the polar or pacific regions, i'd use:

where pow(2*(latitude - ?), 2) + pow(longitude - ?, 2) < distance

answered Mar 27 '12 at 14:56

Cees Timmerman

17,623
11
91
124

SQL Select: toilets within 50KM of a certain position?

2 Answers2