CODEWITHSUNDEEP
phpmysqlgeolocationgeospatialgeo
Hunt
Hunt

Reputation: 8435

road map to perform proximity search

I want to find people around X miles let me explain it properly.

I have an application which does following 

1) it shows user's current location on a google map with a radius circle of 3 miles. 

2) when ever user uses this application its current location will get stored into MySQL database
  as follows

   ID          Name       currLat       currLan     radius (in miles )
  --------------------------------------------------------------------
  34334        John       23.039574     72.56602      3

Moreover, when ever a new user will use the same application his current location will also get store in the above table.

so when any user accesses this application , the server side code will check the proximity against it current location to find out whether any other user is around him or not.

i google it but i really don't know what's the approach to match and perform proximity search i have read about some formulas but really don't know about the procedures to perform it.

so far i have used following code into php , which returns max and min lat n lan , but i really don;t know wht to do with it , as m totally new to proximity search so can any one tell me the road map to do that 

    $radius = 600;

    $longitude = (float) $lan;
    $latitude = (float) $lat;


    $lng_min = $longitude - $radius / abs(cos(deg2rad($latitude)) * 69);
    $lng_max = $longitude + $radius / abs(cos(deg2rad($latitude)) * 69);
    $lat_min = $latitude - ($radius / 69);
    $lat_max = $latitude + ($radius / 69);

    $data ["lat"] = $lng_min . '/' . $lng_max . PHP_EOL;
    $data ["lan"] = $lat_min . '/' . $lat_max;

i really hope this time its not a unreal question , if anyone want any further information then feel free to ask

so far i have done

Create a table with a type of POINT field and a SPATIAL index upon it

CREATE TABLE userstatus (
  id varchar(100) NOT NULL,
  userid varchar(100) NOT NULL,
  username varchar(100) NOT NULL,
  currLoc  POINT NOT NULL,
  radius INT(10),
  SPATIAL INDEX(currLoc)

)ENGINE = MYISAM

after that a procedure to calculate distance 

DELIMITER $$
 CREATE FUNCTION distance (a POINT, b POINT) RETURNS double DETERMINISTIC
   BEGIN
     RETURN 6371 * 2 * ASIN(SQRT(POWER(SIN(RADIANS(ABS(X(a)) - ABS(X(b)))), 2) + COS(RADIANS(ABS(X(a)))) * COS(RADIANS(ABS(X(b)))) * POWER(SIN(RADIANS(Y(a) - Y(b))), 2)));
   END  $$
DELIMITER ;

Now i don't know who would i compare my user's lat , lat and radius with above function

i inserted my data using 

$userStatusInsert = "INSERT INTO userstatus (id,userid,username,currLoc,radius) 
                    VALUES('".$id."','".$uid."','".$uname."',GeomFromText('POINT(".$lat." ".$lan.")'),'".$radius."')";

i fired the query in cdist < 10 , value 10 is a radius ?

SELECT userid, username, distance(userstatus.currLoc, GeomFromText('POINT(23.039574  72.56602)')) AS cdist FROM userstatus HAVING cdist < 10 ORDER BY cdist LIMIT 10

Results are as follows but i really don't know what cdist column contains , i mean how this determines that a person is in range 

id      username          cdist
-----------------------------------
1115    John    4.52726116114886
1111    Hunt    6.2734062677772
1112    Raul    7.55266860461263
1113    Nizam   7.55480140608532
1114    John    7.76912596719722

Upvotes: 3

Views: 547

Answers (1)

Cybercartel
Cybercartel

Reputation: 12592

The harvesine formula describe when 2 circle collide. The first circle is your current location and the second circle is any other point of interest. You need to define the radius of the first circle and compare any other point of interest with it. It's possible to use also squares. Or you can use a spatial index like a quadkey or the mysql spatial extension.

Upvotes: 2

Related Questions