road map to perform proximity search

Question

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

you should do it with mysql, not with php code. this problem has been solved many times before. here's an example tutorial: http://vinsol.com/blog/2011/08/30/geoproximity-search-with-mysql/ you can also search for "mysql geo proximity search" to find many others — Ben Lee, Feb 25 '12 at 07:17
I agree. Use the spatial extensions to mysql or postgresql. I actually prefer using postgresql because it doesn't use mbr (minimum bounding rectangles) with geospatial functions like mysql does (at least until v5.6 is available) Like seeing if a point is within an odd shaped polygon. — Ray Perea, Feb 25 '12 at 07:25
@Ben Lee as in your example they are using Polygon is there a way to define circle ? — Hunt, Feb 25 '12 at 09:51

score 2 · Accepted Answer · answered Feb 25 '12 at 07:24

2

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.

answered Feb 25 '12 at 07:24

Micromega

12,486
7
35
72

how can i use circle ( as i don't want a polygon ) – Hunt Feb 25 '12 at 09:50
@Hunt: In the example the closed polygon is only used as a spatial index. If you don't want to use a spatial index you can delete it from the WHERE clause. The user Ray Perea uses a spatial index AND the harvesine formula. – Micromega Feb 25 '12 at 11:03
you mean i should remove the WHERE clause and pass first parameter as a all users store in DB with second parameter as a current user who is requesting this query – Hunt Feb 25 '12 at 11:16
@Hunt: It seems o.k. What is the data type of currLoc? Have you tried to manually insert a geo cood? The prepared statement looks o.k. – Micromega Feb 25 '12 at 13:40
Data type of currLoc is POINT ,ya i even tried to set it manually from PHPAdmin too – Hunt Feb 25 '12 at 14:04
@Hunt: I'm not sure but the procedure is looking strange. What is X and Y means? Did you check the mysql error log? – Micromega Feb 25 '12 at 14:17
1

@Hunt: Look here how it's solved: http://stackoverflow.com/questions/8727717/missing-results-due-to-geo-proximity-formula-store-locator. I don't how to work with mydql point data type. Maybe the procedure is wrong. If you can get the coord out of the point variable. – Micromega Feb 25 '12 at 14:26
i solved the problem by changing select query as you seen in a edits but the thing is what ever cdist column contains i dont know what really it is ? – Hunt Feb 25 '12 at 14:33
@Hunt: cdist is the result of the harvesine formula. It's the distance between 2 object on a sphere. That's because it has this thing 63XX km radius of the earth. When you compare the user current location with all other points you can all point of interest. – Micromega Feb 25 '12 at 14:37
so above result in a cdist indicates the user those are in a range of radius less than 10 ? if yes then does this radius 10 represents kilometers or miles ? – Hunt Feb 25 '12 at 14:39

road map to perform proximity search

1 Answers1