what's is the best approach to do nearby search? (PHP & MYSQL) [duplicate]

This question already has answers here: Closed 11 years ago.

Possible Duplicate:

Get polygons close to a lat,long in MySQL

what is the开发者_运维技巧 best approach to do nearby search for >=5,000,000 rows data (and also can be filter by place category (for example want to search "petrol station" nearby) && filter by keyword), and sort it by distance ASC .

is any tutorial / algorithm to do that? how the database stucture, may be must be do clustering ? because if we do search in one big table, it will takes time.

note : let's say inside the table has 10 field (placename,address,category,long,lat,etc). actually, i've try this http://www.arubin.org/files/geo_search.pdf , but it's still slow.

---

Alternativly if you do want to stick with a pure mysql solution, can use a Spatial Index, to greatly improve 'nearby' searches.

These are well discussed on stackoverflow https://stackoverflow.com/search?q=spatial+index+mysql&submit=search

---

Because you want to do 'filter by keywords' I would recommend using a dedicated engine for it.

My personal favorite is SphinxSearch http://sphinxsearch.com/about/sphinx/ ... its very capable of geo queries too.

A number of threads discussing how to make super fast geo queries http://sphinxsearch.com/forum/search.html?q=tiles&f=1

---

If you want a nearby search you first need geolocation of each position you want to compare and the geolocation of the user you want to compare to. To obtain coordinates of your location, you can use a database of postal codes that comes with geolocation information. There are many available on the web for a very fair price (whole USA is about 90$ in average).

Then you need to geolocate your user, that can be done using a geoip routine or simply done using HTML5 geolocation api.

When you have acquired the location of your user, you can use a "simple" distance calculation function to calculate distance between lat/long 1 and lat/long 2: (Lat1 = user, Lat2 = dbfield of location to compare)

SELECT ((ACOS(SIN($lat * PI() / 180) * SIN(lat * PI() / 180) + COS($lat * PI() / 180) * COS(lat * PI() / 180) * COS(($lon – lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS `distance` FROM `members` HAVING `distance`<=’10′ ORDER BY `distance` ASC

Just adapt the query to your need by specifying a distance limit in the HAVING or by limiting the number of results as you wish.