"Find the nearest coffee shop." "Show me all customers within 10 miles of the warehouse."
These are common questions. While tools like PostGIS make this trivial, you can actually do robust geospatial analysis in standard SQL using just math.
The Challenge: The Earth is Curved
If the earth were flat, we could use the Pythagorean theorem ($a^2 + b^2 = c^2$) to find the distance between two points $(x_1, y_1)$ and $(x_2, y_2)$.
But since we are on a sphere, we need the Haversine Formula.
The SQL Implementation
Here is the Haversine formula translated into SQL. It calculates the distance in kilometers between two points: (lat1, lon1) and (lat2, lon2).
Note: 6371 is the radius of the Earth in km.
SELECT
id,
name,
(
6371 * acos(
cos(radians(lat1)) * cos(radians(lat2)) * cos(radians(lon2) - radians(lon1)) +
sin(radians(lat1)) * sin(radians(lat2))
)
) AS distance_km
FROM locations
WHERE distance_km < 10;
This query filters for locations within 10km.
Optimization: The Bounding Box
Trigonometric functions (sin, cos, acos) are CPU-intensive. If you have 10 million rows, running acos() on all of them is slow.
A pro tip is to first filter by a Bounding Box—a rough square around your center point.
-- Roughly 1 degree of latitude ~= 111 km
WHERE lat BETWEEN target_lat - 0.1 AND target_lat + 0.1
AND lon BETWEEN target_lon - 0.1 AND target_lon + 0.1
-- THEN run the expensive math on the remaining few rows
AND ( ... haversine formula ... ) < 10
Interactive Playground
In this example, we calculate the "Manhattan Distance" (approximation) to find the closest driver to a user.
(Note: Full trigonometric functions like ACOS are often missing in lightweight SQL environments, so we use a simplified approximation here for demonstration.)
Conclusion
You don't always need a heavy GIS extension for basic location features. By understanding the math behind coordinates, you can build powerful "Store Loacator" or "Nearby Search" features using standard SQL.