Very similar to how Zillow's mobile app mapping search works, I'm trying to pass in the lat/lng points & radius (meters) of a single user and then, on the fly, generate clusters to enable passing back a subset of points and intensities to represent all the users in the passed in radius (lat, lng, and radius are the three arguments being passed in). As the zoom level increases/decreases, the clusters would rebuild as well.
In a table of 100,000 users, with a geometry point column containing a single point, we're seeing around 6-8 seconds using the following query:
SELECT row_number() over () AS id, ST_NumGeometries(gc), ST_AsText(ST_Centroid(gc)) AS centroid FROM ( SELECT unnest(ST_ClusterWithin(lonlat::geometry, 0.003)) gc FROM users ) f;
What additional functions do I need (ST_DWithin?) to use to pass in a single lat/lng point and radius (in meters), in order to query on the user's location (the above query is just building clusters on all users in the table).
Additionally, we need this query to be a lot faster; ideally less than 3 secs. What else can we do to aid the optimization of this query.
(Note: we already have an index using
gist on the lonlat column.)