Search code examples
mysqlspatial-query

How to get only one column from MySQL with ST_Distance_Sphere

I have a table with list of cities and GPS coordinates, like: id, name, lat, lon from which I am searching to get a nearest city as a result

select
    name, 
    ST_Distance_Sphere(point(lon, lat), point('20.073602', '49.937056') )/1000 as distance 
    from city 
    order by distance asc LIMIT 1;

but how to receive from this query only a 'city' field (one column) as a result without second column which is distance?

Here is my SQL fiddle: https://www.db-fiddle.com/f/hdshw2KCjNZrXZoKQQgoKR/1

Solution

  • You can put your expression directly in order by clause

    select
    name
from city 
order by ST_Distance_Sphere(point(lon, lat), point('20.073602','49.937056') )/1000 asc
limit 1;

    DEMO