Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Fast nearest-location finder for SQL (MySQL, PostgreSQL, SQL Server)
SELECT zip, primary_city,
latitude, longitude, distance
FROM (
SELECT z.zip,
z.primary_city,
z.latitude, z.longitude,
p.radius,
p.distance_unit
* DEGREES(ACOS(LEAST(1.0, COS(RADIANS(p.latpoint))
* COS(RADIANS(z.latitude))
* COS(RADIANS(p.longpoint - z.longitude))
+ SIN(RADIANS(p.latpoint))
* SIN(RADIANS(z.latitude))))) AS distance
FROM zip AS z
JOIN ( /* these are the query parameters */
SELECT 42.81 AS latpoint, -70.81 AS longpoint,
50.0 AS radius, 111.045 AS distance_unit
) AS p ON 1=1
WHERE z.latitude
BETWEEN p.latpoint - (p.radius / p.distance_unit)
AND p.latpoint + (p.radius / p.distance_unit)
AND z.longitude
BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
AND p.longpoint + (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
) AS d
WHERE distance <= radius
ORDER BY distance
LIMIT 15
SELECT z.zip,
z.primary_city,
z.latitude, z.longitude,
p.distance_unit
* DEGREES(ACOS(LEAST(1.0, COS(RADIANS(p.latpoint))
* COS(RADIANS(z.latitude))
* COS(RADIANS(p.longpoint) - RADIANS(z.longitude))
+ SIN(RADIANS(p.latpoint))
* SIN(RADIANS(z.latitude))))) AS distance_in_km
FROM zip AS z
JOIN ( /* these are the query parameters */
SELECT 42.81 AS latpoint, -70.81 AS longpoint,
50.0 AS radius, 111.045 AS distance_unit
) AS p ON 1=1
WHERE z.latitude
BETWEEN p.latpoint - (p.radius / p.distance_unit)
AND p.latpoint + (p.radius / p.distance_unit)
AND z.longitude
BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
AND p.longpoint + (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
ORDER BY distance_in_km
LIMIT 15
@LostInDarkMath

This comment has been minimized.

Copy link

@LostInDarkMath LostInDarkMath commented Apr 11, 2021

Thank you very much for sharing! I have a question: Could I also use a HAVING clause instead of the second SELECT or is it much slower? I mean something like this:

 SELECT z.zip,
        z.primary_city,
        z.latitude, z.longitude,
        p.radius,
        p.distance_unit
                 * DEGREES(ACOS(LEAST(1.0, COS(RADIANS(p.latpoint))
                 * COS(RADIANS(z.latitude))
                 * COS(RADIANS(p.longpoint - z.longitude))
                 + SIN(RADIANS(p.latpoint))
                 * SIN(RADIANS(z.latitude))))) AS distance
  FROM zip AS z
  JOIN (   /* these are the query parameters */
        SELECT  42.81  AS latpoint,  -70.81 AS longpoint,
                50.0 AS radius,      111.045 AS distance_unit
    ) AS p ON 1=1
  WHERE z.latitude
     BETWEEN p.latpoint  - (p.radius / p.distance_unit)
         AND p.latpoint  + (p.radius / p.distance_unit)
    AND z.longitude
     BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
         AND p.longpoint + (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint))))
 HAVING distance <= radius
 ORDER BY distance
 LIMIT 15

What is the better approach?

Edit: This StackOverflow answer says there shouldn't be any difference regarding perfomance.

@joaomarcos96

This comment has been minimized.

Copy link

@joaomarcos96 joaomarcos96 commented Jul 28, 2021

@LostInDarkMath in my case I had to use HAVING to take advantage of the indices.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment