1

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.

Community
  • 1
  • 1
fosmaki
  • 11
  • 1
  • fosmaki, welcome to SO. One question: Do you really think you're the first who asks this here on SO? Please read the [FAQ](http://stackoverflow.com/faq). – hakre Oct 04 '11 at 12:31

3 Answers3

2

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

Community
  • 1
  • 1
barryhunter
  • 20,886
  • 3
  • 30
  • 43
1

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

barryhunter
  • 20,886
  • 3
  • 30
  • 43
0

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.

Mathieu Dumoulin
  • 12,126
  • 7
  • 43
  • 71
  • While this may work, its very inefficent. The 'distance' has to be computed for each and every row. Thats a complex calculation to run 5 million times. – barryhunter Oct 04 '11 at 12:37