1

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.)

  • there are several guides out there about performance. This one helped me: linux.com/learn/configuring-postgresql-pretty-good-performance ... ST_DWithin is recommended because it can be indexed and uses a radius, so it will be the best use for your case. – kttii Jul 8 '16 at 21:01
  • We're currently using ST_DWithin (with an index), but the issue there is that it's taking too to return all 100,000 users (~13 secs). This is why we need to use clustering so that we're not returning as many user objects. – PelagicDev Jul 9 '16 at 16:11
  • your question doesn't show how you are using ST_DWithin. – kttii Jul 11 '16 at 13:44
1

If you want to take advantage of ST_DWithin, you'll need to specify a cutoff radius to limit the expansion of clusters. (It's possible to have a set of inputs where a user's cluster is the entire table).

Say we have a user at (-80, 40), and we want to limit the search radius to 15km. That can be done with this modification:

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
  WHERE ST_DWithin('POINT (-80 40)'::geography, lonlat, 15000)
) f;

As an aside, 13 seconds sounds slow to cluster the entire table. If you're not using PostGIS 2.2.0, you should upgrade to the latest patch release for some significant performance improvements.

  • Okay, this looks like exactly what I need @dbaston. One further question though: Would it be helpful to add in GeoHashing to the query, add a sort to the GeoHash in order to further speed up the query? – PelagicDev Jul 11 '16 at 14:46
  • Oh, and we are using PostGIS 2.2.2. – PelagicDev Jul 11 '16 at 14:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.