Finding locations within a specific radius using MySQL

Other Questions MySQL
1

Hey everyone, I’m working on a project that involves searching for locations within a certain distance. My database has a table with zip codes as integers and locations stored as points. I’m trying to figure out how to query this data to find all locations within a 100-mile radius of a given point.

Here’s what my data looks like:

zip_code | location
12345    | POINT(40.7128 -74.0060)

I’ve managed to extract the latitude and longitude using this query:

SELECT X(location), Y(location) FROM zip_locations

But I’m stuck on how to use these coordinates to find all points within a specific distance. Is there a straightforward way to do this in MySQL? Any help or pointers would be greatly appreciated!

2

As someone who’s worked extensively with geospatial data in MySQL, I can share a solution that’s worked well for me. The key is using the ST_Distance_Sphere function, which calculates the distance between two points on a sphere.

Here’s a query that should do the trick:

SELECT zip_code, X(location) AS lat, Y(location) AS lng,
ST_Distance_Sphere(
    location,
    POINT(40.7128, -74.0060)
) * 0.000621371 AS distance_miles
FROM zip_locations
HAVING distance_miles <= 100
ORDER BY distance_miles;

This calculates the distance from each point to your reference point (I used NYC coordinates as an example), converts it to miles, and filters for those within 100 miles.

One caveat: This method assumes a spherical Earth, which is good enough for most applications. If you need extreme precision, you might want to look into more complex geodesic calculations.