CODEWITHSUNDEEP
sqldatabasepostgresqlpostgresql-9.2
Grinning Trout
Grinning Trout

Reputation: 119

sql query to get first closest locations by latitude and longitude provided as feilds in table

i have a table that contains latitude and longitude locations i want to sort it as the closest to my location or the location i provide :

id longitude latitude
1 20.2 7.1
2 10.0 20.0
3 20.1 5.2
4 10.1 70.5

lets say i have this table what i want i a query in SQL to return the first 3 closest to lets say longitude : 12.1 , latitude : 8.3

so this is what i expect to be returned

id longitude latitude
2 10.0 20.0
1 20.2 7.1
3 20.1 5.2

i guess, not sure what is the closest but you got the idea

thats what i have tried i have never worked with SQL :)

(
  (SELECT id, number FROM destination WHERE longitude >= 15 ORDER BY longitude LIMIT 1)
  UNION ALL
  (SELECT id, number FROM destination WHERE latitude < 15 ORDER BY latitude DESC LIMIT 1)
) 
ORDER BY abs(15-number) LIMIT 5;```

Upvotes: 0

Views: 396

Answers (1)

Joseph
Joseph

Reputation: 865

Your code in your question isn't doing anything like calculating distance between two points. It would be a good idea to do a little research on how to calculate distance between two points - that's where you'll need to start. You should be able to adapt my answer to your scenario, but it would be a good idea to understand the math involved if you'll be working with coordinates.

There's lots of info on Stack Overflow about calculating the distance between two points, even specifically to PostgreSQL. Here's an example adapted from another Stack Overflow artical (PostgreSQL calculate distance between two points without using PostGIS):

SELECT
    id,
    longitude,
    latitude,
    (select SQRT(POW(69.1 * (latitude::float -  10::float), 2) + 
        POW(69.1 * (longitude::float - 15::float) * COS(latitude::float / 57.3), 2)
    )) AS Distance
FROM destination
ORDER BY Distance

I have hardcoded values of 10 for the latitude of the origin point, and 15 for the longitude. Those will need to be replaced by the lat/long of the point you're trying to compare to.

This will return all of the rows in your destination table, including a new column that is the distance (in miles) of the row from your origin point. It will be ordered by that distance as well, closest to farthest.

Upvotes: 1

Related Questions