0

I want to select the closest place to a given longitude, latitude values so I have created this query 

SELECT *
FROM   "Places"
WHERE  2 * atan2(
sqrt(
sin((radians(41.647780 - latitude))/2)*sin((radians(41.647780 - latitude))/2) + 
sin((radians(25.295330 - longitude))/2)*sin((radians(25.295330 - longitude))/2) * 
cos(radians(latitude)) * cos(radians(41.647780))
),
sqrt(1 - 
sin((radians(41.647780 - latitude))/2)*sin((radians(41.647780 - latitude))/2) + 
sin((radians(25.295330 - longitude))/2)*sin((radians(25.295330 - longitude))/2) * 
cos(radians(latitude)) * cos(radians(41.647780))
)) * 6371 IS MINIMAL
LIMIT 1

The main idea is that this formula returns the distances between all points and my hardcoded one I have tried the formula in select query and it's working.

I want to get "the minimal" distance and I need something like "IS MINIMAL". I know the above is wrong (maybe very wrong and stupid) but I'm new in these areas. I think I only have to add or change a word or two and it will work, but I don't know the correct ones.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Yoan Dinkov
  • 531
  • 1
  • 5
  • 17

1 Answers1

1

What you are looking for is ORDER BY .. LIMIT 1:

SELECT *
FROM   "Places"
ORDER  BY atan2(
   sqrt(
   sin((radians(41.647780 - latitude))/2)*sin((radians(41.647780 - latitude))/2) + 
   sin((radians(25.295330 - longitude))/2)*sin((radians(25.295330 - longitude))/2) * 
   cos(radians(latitude)) * cos(radians(41.647780))
   ),
   sqrt(1 - 
   sin((radians(41.647780 - latitude))/2)*sin((radians(41.647780 - latitude))/2) + 
   sin((radians(25.295330 - longitude))/2)*sin((radians(25.295330 - longitude))/2) * 
   cos(radians(latitude)) * cos(radians(41.647780))
   ))
LIMIT  1;

You might be interested in PostGis or this related question:
How can I get results from a JPA entity ordered by distance?

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • At the end , i made with with order by, but isn't this a bit slow we select all places and then order them but there isn't any good way. Yes i know about PostGis and i think it's cool, but for this university project i'm using heroku postgres dev (the basic free version) and there is not included PostGis. Thanks for the answer. – Yoan Dinkov Nov 15 '13 at 09:09