Clever Geek Handbook

How to quickly get the next 100 points from a million data?

given one point A, get the next 100 points from a million data records

  • MySql database
  • million records of latitude and longitude
  • These dots represent the user's current login position, so they are subject to change.

scenario:

when the user opens the page, displays the nearest top 100 other people.

+3


source to share


2 answers


  • Setting up spatial extensions for your database, if you haven't already.
  • Store latitude / longitude of your 1M locations in a geography type column in the database.
  • Create a spatial index on this column.
  • Run a SELECT query with a WHERE clause based on the distance between your point of interest and locations in the table. The request will use the above index.


Here's a good article on using spatial extension in MySQL 5.6 for this kind of thing.

+2


source


http://en.wikipedia.org/wiki/Geohash might be a quick way to speed up the average case, but the worst behavior will still be bad. This article assumes that you are indexing by geohash and, upon request, retrieve all points in the bounding box that makes up the geohash prefix. If the bounding box is small and you find a match in it closer than any point outside of the bounding box, then you've done it quickly, but none of these things can be true.



0


source






More articles:


All Articles