Skip to content

Instantly share code, notes, and snippets.

@stsibel

stsibel/MySQL_zipcodes_within_radius.sql

Last active September 26, 2022 18:02
Show Gist options
  • Save stsibel/41c3f52b931d948c1548 to your computer and use it in GitHub Desktop.
Save stsibel/41c3f52b931d948c1548 to your computer and use it in GitHub Desktop.
Download ZIP
MySQL query to find zip-codes within given radius from any point (or zip-code)
Raw
MySQL_zipcodes_within_radius.sql
/* Given table 'zipcodes' with columns:
zipcode, latitude, longitude.
Find zipcodes within radius from given zipcode.
EXAMPLE:
Coordinates for zip 91326 and radius 25 mi:
*/
SET @location_lat = 34.2766,
@location_lon = -118.544;
SELECT zipcode, ( 3959 * acos( cos( radians(@location_lat) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(@location_lon) ) + sin( radians(@location_lat) ) * sin( radians( latitude ) ) ) ) AS distance
FROM zipcodes
HAVING distance < 25;
/*
Result:
+-------------+-------------------+
| zipcode | distance |
+-------------+-------------------+
| 90004 | 19.32764527143567 |
| 90005 | 20.34491933480445 |
| 90006 | 21.56930375425860 |
| ... | ... |
+-------------+-------------------+
*/
@ajayvd
Copy link

ajayvd commented Sep 26, 2022 •
edited
Loading

@stsibel SELECT zipcode, ( 3959 * acos( cos( radians(@location_lat) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians(@location_lon) ) + sin( radians(@location_lat) ) * sin( radians( latitude ) ) ) ) AS distance FROM zipcodes HAVING distance < 25;

could you please tell me what value do i need to place in latitude? do we need to pass the same value which we are assigning in set operation.

thanks

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