I have a database of 5 million locations in the U.S. I would like to support _fast_ distance / proximity searches. In other words the user enters an address, my app geocodes the location and then returns nearby points of interest. The user can specify the proximity which might be something like 0.1 miles - 250 miles. The results should be ordered and paged. Are there best practices for supporting this kind of functionality?
1) Should I store my locations in a projected or geodetic coordinate system? It is my understanding that proximity searches are slow for geodetic coord systems because you are limited to geodjango queries like point__distance_lte=(point, d) that use the postgis query st_distance_sphere (or st_distance_spheroid), which doesn't use indexes, and therefore results in entire database scans. On the other hand, with a projected coord system you can use queries (like st_within) that are indexed and fast, but the problem is finding a coord system that is accurate for your entire search area. Which brings me to my next question: 2) Is there a projected coord system that is accurate for the whole US? Or at least something that people generally use and agree is accurate enough? 3) If not, how are other people solving this problem? Distance searches must be a very common use case for geodjango / postgis users. The approach I am considering: http://atomized.org/2008/11/optimizing-distance-calculations/. I think the idea is: use a geodetic coord system (I'm thinking srid=4326), create lat/longs indexes, write a custom sql query that uses average lat/long distances to approximate a search area, use the index to find points within that area, then scan the resulting points using st_distance_sphere. Are there better alternatives? Is there a geodjango solution that doesn't include writing custom queries? --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/django-users?hl=en -~----------~----~----~----~------~----~------~--~---
