select within 20 kilometers based on latitude/longitude

by 
SELECT  *
FROM    mytable m
JOIN    mytable mn
ON      ACOS(COS(RADIANS(m.latitude)) * COS(RADIANS(mn.latitude)) * COS(RADIANS(mn.longitude) - RADIANS(m.longitude)) + SIN(RADIANS(m.latitude)) * SIN(radians(mn.latitude))) <= 20 / 6371.0
WHERE   m.name="grey river"

If your table is MyISAM you may want to store your points in a native geometry format and create a SPATIAL index on it:

ALTER TABLE mytable ADD position POINT;

UPDATE  mytable
SET     position = POINT(latitude, longitude);

ALTER TABLE mytable MODIFY position NOT NULL;

CREATE SPATIAL INDEX sx_mytable_position ON mytable (position);

SELECT  *
FROM    mytable m
JOIN    mytable mn
ON      MBRContains
                (
                LineString
                        (
                        Point
                                (
                                X(m.position) - 0.009 * 20,
                                Y(m.position) - 0.009 * 20 / COS(RADIANS(X(m.position)))
                                ),
                        Point
                                (
                                X(m.position) + 0.009 * 20,
                                Y(m.position) + 0.009 * 20 / COS(RADIANS(X(m.position))
                                )
                        ),
                mn.position
                )
        AND ACOS(COS(RADIANS(m.latitude)) * COS(RADIANS(mn.latitude)) * COS(RADIANS(mn.longitude) - RADIANS(m.longitude)) + SIN(RADIANS(m.latitude)) * SIN(radians(mn.latitude))) <= 20 / 6371.0
WHERE   m.name="grey river"

More Related Contents:

  1. How can I properly use a PDO object for a parameterized SELECT query
  2. MySQL: Select Random Entry, but Weight Towards Certain Entries
  3. Notice: Array to string conversion in
  4. Selecting all columns that start with XXX using a wildcard?
  5. SELECT COUNT() vs mysql_num_rows();
  6. Select from mysql table WHERE field=’$array’?
  7. unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING error [duplicate]
  8. How do I set the selected item in a drop down box
  9. Selecting all fields except only one field in mysql [duplicate]
  10. MySQL ORDER BY rand(), name ASC
  11. How to find missing attendance from the database table
  12. How do I use password hashing with PDO to make my code more secure? [closed]
  13. MySQLi prepared statements error reporting [duplicate]
  14. Lost connection to MySQL server at ‘reading initial communication packet’, system error: 0
  15. Warning: mysqli_connect(): (HY000/2002): No such file or directory
  16. How do I import a .sql file in mysql database using PHP?
  17. PHP Fatal error: Class ‘PDO’ not found
  18. Connect to a MySQL server over SSH in PHP
  19. How to bind parameters to a raw DB query in Laravel that’s used on a model?
  20. PHP Warning: mysqli_connect(): (HY000/2002): Connection refused
  21. MYSQL Select from tables based on multiple rows
  22. Is “SET CHARACTER SET utf8” necessary?
  23. Sending HTML Code Through JSON
  24. Do I really need to do mysql_close()
  25. Turn off warnings and errors on PHP and MySQL
  26. Checking for an empty result (PHP, PDO, and MySQL) [duplicate]
  27. MySQL/SQL retrieve first 40 characters of a text field?
  28. How to use mysqli_query() in PHP?
  29. How does {} affect a MySQL Query in PHP? [duplicate]
  30. Limit pagination page number

Leave a Comment