0

I recently posted this question: getting distance between two points using google gps api with php

and I received a reply which was awesome however I had some questions about the person's response and due to my low/new status to this site I wasn't able to comment back. In response to my question the other user posted this query **

SELECT a.*, 3956 * 2 * ASIN(SQRT( POWER(SIN(($lat - lat) * pi()/180 / 2), 2) + COS($lat * pi()/180) * COS(lat * pi()/180) *
POWER(SIN(($long - longi) * pi()/180 / 2), 2) )) as distance 
FROM table
GROUP BY id HAVING distance <= 500 
ORDER by distance ASC**

I had a few questions about this query and was hoping somebody could help.

1.What is the a.*? I'm not super advanced in sql but pretty efficient and have never seen something like this. I don't know if it is supposed to represent an arbritrary field or an actual field in my table

2.Since I'm doing this in php the query will go in quotes which will then make this query syntax slightly different. I was wondering if someone knew what the query would look like in quotes.

3.There is also "GROUP BY id" in this query. I do have an id field in my table that I'm querying from. Is this "id" associated with my id field in my table?

any help would be awesome.

Community
  • 1
  • 1
Derek Dub
  • 53
  • 2
  • 11

1 Answers1

0

Your query should be like this :- a is table alias here. 

SELECT a.*, 3956 * 2 * ASIN(SQRT( POWER(SIN(($lat - lat) * pi()/180 / 2), 2) + COS($lat * pi()/180) * COS(lat * pi()/180) *
POWER(SIN(($long - longi) * pi()/180 / 2), 2) )) as distance 
FROM table AS a
GROUP BY id HAVING distance <= 500 
ORDER by distance ASC

Or you can use simple 

SELECT *, 3956 * 2 * ASIN(SQRT( POWER(SIN(($lat - lat) * pi()/180 / 2), 2) + COS($lat * pi()/180) * COS(lat * pi()/180) *
POWER(SIN(($long - longi) * pi()/180 / 2), 2) )) as distance 
FROM table
GROUP BY id HAVING distance <= 500 
ORDER by distance ASC
Roopendra
  • 7,674
  • 16
  • 65
  • 92
  • so after plugging this in, just to clarify, $long and $lat are the variables that I'm comparing and lat and longi are the values from those fields in my table right? in my case since my fields are latitude and longitude, lat and longi effectively become latitude and longitude? Also since I have an id field called userID, the id would be my userID field? – Derek Dub Feb 22 '13 at 04:33
  • Correct. id replace with userID and lat and long become latitude and longitude here. – Roopendra Feb 22 '13 at 04:38