基于经纬度计算用户所选城市与其他城市距离的SQL查询需求

Let's break this down into a straightforward SQL query that gets the job done. First, I'll assume your table is named cities with columns: city_name (the city's name), latitude, and longitude (the geographic coordinates). Adjust the table/column names if yours are different!

The Full Query #

WITH athens_coords AS (
    SELECT 
        longitude AS athens_long,
        latitude AS athens_lat
    FROM cities
    WHERE city_name = 'Athens'
    LIMIT 1 -- In case there are duplicate entries for Athens
)
SELECT
    c.city_name,
    -- Calculate distance in meters (default for st_distance_sphere)
    st_distance_sphere(
        POINT(c.longitude, c.latitude),
        POINT(ac.athens_long, ac.athens_lat)
    ) AS distance_meters,
    -- Optional: Convert to kilometers for readability
    ROUND(st_distance_sphere(
        POINT(c.longitude, c.latitude),
        POINT(ac.athens_long, ac.athens_lat)
    ) / 1000, 2) AS distance_km
FROM cities c
CROSS JOIN athens_coords ac
WHERE c.city_name != 'Athens' -- Exclude Athens itself
ORDER BY distance_km ASC; -- Sort closest cities first

Key Details to Note #

• CTE (athens_coords): This first pulls the exact longitude and latitude of Athens from your table. The LIMIT 1 ensures we only get one set of coordinates even if there are duplicate "Athens" entries.
• st_distance_sphere Usage: Remember that POINT() expects longitude first, then latitude—this is standard for GIS functions, so don't mix up the order! The function returns the distance in meters by default.
• Optional Conversion: The distance_km column converts meters to kilometers and rounds to 2 decimal places for easier reading.
• Filter & Sort: We exclude Athens from the results (since its distance to itself is 0) and sort by distance to see the closest cities first.

If your table uses different column names (like long instead of longitude, or city instead of city_name), just swap those out in the query to match your schema.

内容的提问来源于stack exchange，提问作者min2bro