8

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?

Andrew J
  • 1,951
  • 1
  • 14
  • 16
  • have you considered looking into PostgreSQL? it natively supports spatial data types, and has a bunch of comparision operators for proximity, enclosure, etc... – HorusKol Jun 30 '10 at 23:33
  • MongoDB supports out-of-the-box Geospatial Index; the core use case is same one you have summarized (e.g., your exemplary queries are nearly identical to the ones in the Mongo summary page on G/I. – doug Nov 14 '11 at 10:52

2 Answers2

5

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
4

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.

Community
  • 1
  • 1
Abe Miessler
  • 82,532
  • 99
  • 305
  • 486