0

Let's say I have a table of 2D points (geographical coordinates) in database. What is a best practice to organize the data to make search a row with most closest coordinates to the given among this array of data?

I cannot figure out anything better than:

SELECT * FROM `pois` WHERE 1 
ORDER BY ($x-`x`)*($x-`x`) + ($y-`y`)*($y-`y`) ASC LIMIT 1

This approach can be acceptable if the table size is 1000 rows. But it can be terribly slow if the poi DB is 1 million of rows...

Any thoughts?

Katie Kilian
  • 6,815
  • 5
  • 41
  • 64
Roman Matveev
  • 563
  • 1
  • 6
  • 22
  • [Geo Distance Search](http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL)? – Ja͢ck Mar 13 '14 at 05:42
  • possible duplicate of [latitude/longitude find nearest latitude/longitude - complex sql or complex calculation!](http://stackoverflow.com/questions/2234204/latitude-longitude-find-nearest-latitude-longitude-complex-sql-or-complex-calc) – Ja͢ck Mar 13 '14 at 05:44

1 Answers1

0

Instead of searching in a circle, search in a square using BETWEEN so SQL can utilize the indices on X and Y which hopefully you already have.

Then do the full circle check on the subset of data you get back.

StilesCrisis
  • 15,972
  • 4
  • 39
  • 62