MySQL Lat/Lng Boundary with even distribution of results

Question

In a map application i display markers on a map based on the visible map area using a query similar to the following.

SELECT * FROM (`properties`) 
WHERE `lat` > '-11.824448' AND `lat` < '84.124002' AND 
      `lng` > '-152.243398' AND `lng` < '-39.743398'   
LIMIT 20;

Due to the index on the table this results in results appearing usually on the same latitude and appear on a line in the corner of the map. wit the rest of the map being empty.

Given that i can't display all the results on the map due to server load, bandwidth and load time, is there a way i can pull back results across the entire map area?

I guess i could do this by figuring out a proportional distance of x and telling mysql to return results where no result is within x distance of another.

How do other people do it?

Answer 1

'ORDER BY RAND()' seems to do a good enough job without getting too complex

Answer 2

If you will be having thousands of properties, you may want to consider clustering your properties within the database. That is, by defining at which zoom level each property should appear, you can cluster the points to reduce the amount being shown at a time.

One approach could be to use another table, maybe calling it properties_clusters, with the following fields: lat, lng, number_of_points, zoom_level. Make sure that the zoom_level field is covered by a useable index as well.

Whenever you insert or move a property, you should also update the properties_clusters table. You may want to research a bit about the possible clustering algorithms, but if accuracy is not that important, you could probably come up with a simple solution such as tessellating the world in a number of tiles for each zoom level, and then simply cluster accordingly.

Then whenever the visible area or the zoom level of the map changes, you could request data from your properties_clusters table, which will return a smaller number of points the lower the zoom level:

SELECT * FROM (`properties_clusters`) 
WHERE `lat` > '-11.824448' AND `lat` < '84.124002' AND 
      `lng` > '-152.243398' AND `lng` < '-39.743398' AND
      `zoom_level` = 5

You could define a zoom level threshold, from where you will display results directly from the properties table instead. This threshold is normally set to a zoom level where it is possible to interact with the markers on the map (clicking on them, etc).