We're building a mapping application for voter data using PostGIS and Leaflet. It has a web interface. Our voter file will have several million entries. In our app you can select a subset of the voter file and display it.
It works fine when you're zoomed in because the result set is small. The challenge is to figure out what to do when the result set is large. As I see it, we have multiple options:
Just bail out when result sets exceed X records. The user would receive an error message: "Too many records to display. Zoom in or narrow your search criteria." This isn't ideal.
Try to do client-side clustering and just display the clusters. This won't work because it's not possible to transfer very large result sets to the browser quickly. No amount of compression will get a million records down to an acceptable size.
Try to do server-side clustering. We've done that using the techniques found here: Spatial clustering with PostGIS? The difficulty is, again, speed; you can't aggregate millions of locations and calculate cluster centroids and counts quickly. Maybe we're doing it wrong.
Do some kind of aggregation using zip codes. This is faster; essentially you do a
select zipcode, count(*) from voters where (some predicate) group by zipcode. You then look up the zipcode centroid separately. It's faster because it's a covered query which can usually be answered by hitting indexes alone. But zipcodes don't display well because they're dense in a city and not so dense elsewhere.
Aggregate based on some other field, maybe something related to tiles. I haven't fully thought this one through.
Exploit some property of the GiST index on the location. This is theoretically possible; a GiST index already "clusters" points internally in the R Tree. I don't know where to start with this idea.
How do other people solve the problem of summarizing millions of points, dynamically, quickly, for presentation to an end user?