Get results that fall within marker radiuses from database

by

For solving this you need to understand equation of circle, which is something like this
For any point (x,y) to fall within circle with center (x1, y1) and radius r units is

(x-x1)^2 + (y - y1)^2 <= r^2

where a^b = a to the power b

Here in your case User B’s (latitude, longitude) are the center of circle, User A’s (latitude, longitude) are the points (x,y) and radius = 2kms.

But basic problem is of changing degrees of latitudes to longitudes, so here is solution, 1 degree = 111.12 km. So to keep units same on both side of equation, we will convert it to Kms

So our final equation becomes:

((x-x1)*111.12)^2 + ((y-y1)*111.12)^2 = 4      (=2^2)

SQL statement for the same should look something like this

SELECT A.user_id, A.radius_id, A.latitude, A.logitude
FROM UserA AS A, 
     (SELECT user_id, latitude, longitude 
       FROM UserB 
       WHERE user_id = 8) AS B
WHERE (POW((A.latitude-B.latitude)*111.12, 2) + POW((A.longitude - B.longitude)*111.12, 2)) <= 4
/* **Edit** Here I have used (A.longitude - B.longitude)*111.12, for more accurate results one can replace it with (A.longitude - B.longitude)*111.12*cos(A.latitude)) or (A.longitude - B.longitude)*111.12*cos(B.latitude)) 

And, as i have suggested in the comments that first filter some records based on approximation, so whether one uses A.latitude or B.latitude it will not make much difference */

Hope this will help…

More Related Contents:

  1. How to use GROUP_CONCAT in a CONCAT in MySQL
  2. MySQL query / clause execution order
  3. How can I tell when a MySQL table was last updated?
  4. Operand Should Contain 1 Column – MySQL NOT IN
  5. Error Code: 1005. Can’t create table ‘…’ (errno: 150)
  6. Does adding ‘LIMIT 1’ to MySQL queries make them faster when you know there will only be 1 result?
  7. Is it possible to query a tree structure table in MySQL in a single query, to any depth?
  8. How to use LOAD_FILE to load a file into a MySQL blob?
  9. MySQL: Error Code: 1118 Row size too large (> 8126). Changing some columns to TEXT or BLOB
  10. On duplicate key ignore? [duplicate]
  11. What’s the difference between comma separated joins and join on syntax in MySQL? [duplicate]
  12. Determine Rank based on Multiple Columns in MySQL
  13. List of Stored Procedures/Functions Mysql Command Line
  14. mySQL longitude and latitude query for other rows within x mile radius
  15. How to design a multi tenant mysql database
  16. How to get the count of each distinct value in a column? [duplicate]
  17. How do I repair an InnoDB table?
  18. Implementing a hierarchical data structure in a database
  19. How to lock a single row
  20. MySQL date format
  21. How to get current date & time in MySQL?
  22. Laravel join with 3 Tables
  23. phpMyAdmin – can’t connect – invalid setings – ever since I added a root password – locked out
  24. How to join two tables using a comma-separated-list in the join field
  25. How do I split the output from mysqldump into smaller files?
  26. How to regex in a MySQL query
  27. How to remove duplicate entries from a mysql db?
  28. MySQL Group By Hours
  29. Syntax error on MERGE statement
  30. “General error: 1005 Can’t create table” Using Laravel Schema Build and Foreign Keys

Leave a Comment