Geospatial Analysis: Calculating Distances in SQL

"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.

WITH distances AS (
    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
)
SELECT *
FROM distances
WHERE distance_km < 10;

This query filters for locations within 10 km. The CTE matters because many SQL engines do not let you reference distance_km in WHERE in the same query block where the alias is created.

Choosing the Right Distance Strategy

The Haversine formula is a strong default when:

• coordinates are stored as latitude and longitude
• you want "within X miles/km" filtering
• you are working in a regular SQL warehouse without a dedicated GIS extension

It is not always the best answer. A rough guide:

• Very small local distances and simple demos: a flat approximation can be acceptable.
• Global user-facing proximity search: use Haversine or a spatial extension.
• Heavy geospatial workloads with polygons, routes, or indexing needs: use PostGIS, BigQuery GIS, Snowflake geospatial, or your database's native spatial features.

The key is not memorizing one formula. It is matching the precision and performance level to the product requirement.

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

This pattern works because cheap filters reduce the candidate set before expensive trigonometry runs. On large tables, that can be the difference between a practical query and an unusable one.

In production, the workflow is usually:

1. filter by a latitude/longitude box
2. calculate the exact distance for the reduced result set
3. sort by distance
4. keep only the nearest rows with LIMIT

If you skip step 1 on a large dataset, you are asking the database to perform trig math on every row.

Practical Data Quality Checks

Distance queries are surprisingly sensitive to bad input. Before trusting the result, validate:

• latitude is between -90 and 90
• longitude is between -180 and 180
• both coordinates use the same unit and geographic reference
• null coordinates are excluded explicitly
• the chosen earth radius matches your output unit (6371 km or 3959 miles)

Also watch out for swapped fields. A reversed latitude/longitude pair can still look numeric, but it produces nonsense distances.

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.)

A More Realistic "Nearby Search" Pattern

Most proximity features need more than just the distance number. They usually need ranking and a final top-N result:

WITH candidate_locations AS (
    SELECT *
    FROM stores
    WHERE lat BETWEEN 40.60 AND 40.80
      AND lon BETWEEN -74.10 AND -73.90
),
scored_locations AS (
    SELECT
        store_id,
        store_name,
        6371 * acos(
            cos(radians(40.71)) * cos(radians(lat)) *
            cos(radians(lon) - radians(-74.00)) +
            sin(radians(40.71)) * sin(radians(lat))
        ) AS distance_km
    FROM candidate_locations
)
SELECT *
FROM scored_locations
WHERE distance_km <= 10
ORDER BY distance_km
LIMIT 20;

This is the shape you should keep in mind for store locators, nearby drivers, local delivery coverage, and "find the closest office" features.

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.

Related Articles

• Why Your SQL Queries Are Slow (And How to Fix Them) for the broader performance thinking behind bounding boxes and expensive function calls.
• Time Series Analysis with SQL for another class of analytics queries where range filters and ordering strategy matter.
• Reading SQL Execution Plans for the next step when you need to verify how the database actually executes a proximity query.