Menu
I'm trying to design a MySQL schema that can store a list of users with an associated latitude and longitude.
I would then, for a given user, like to build a query which can return the nearest 50 users to him/her and sort those users by distance (with the nearest being presented first).
Given that there may be many thousands of users in this table, what is the most efficient way to store and query this data?
582
To find locations in your markers table that are within a certain radius distance of a given latitude/longitude, you can use a SELECT statement based on the Haversine formula. The Haversine formula is used generally for computing great-circle distances between two pairs of coordinates on a sphere.
Calculating Distance in MySQL To get the distance between two points, you call the function with the two points as the arguments: -- Returns distance in meters.
Try reading this article: Creating a Store Locator with PHP, MySQL & Google Maps That article shows a MySQL solution for the Haversine formula, which is the best way to compute distance given latitude and longitude.
85
Take a look at MySql's spatial indexing.
You can also use the Great Circle Distance, there is a good SO article on it here.
42
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
