I am using the postgis ST_ClusterWithin function to cluster my spatial data in a table. I want to get the rows of the table as a result of the query clustering query (maybe the Pk's of each element in each cluster) instead of the geometry collection. Returning geometry collection seems to loose the context of the data by just giving me the geometry and nothing else. Now will I have to query the database with a ST_Contains or ST_Within again on the database to form the clusters using the rows in that table. Is there a different way. I am using a query similar to Spatial clustering with PostGIS


In PostGIS 2.2, your options are to either (a) do a join after-the-fact to get back your IDs or other relevant information, or (b) abuse the Z or M ordinates to sneak some additional information into the geometry objects.

The newer clustering options in PostGIS 2.3 are more flexible; they're implemented as windowing functions and can provide a cluster ID for each row in a query. For more info, check out the docs for ST_ClusterKMeans and ST_ClusterDBSCAN.

  • So now in PostGIS 2.3, is there a better ST_ClusterWithin function implementing windowing functions? – Nicolas Boisteault Jun 2 '17 at 8:12
  • 1
    @NicolasBoisteault Yes, ST_ClusterDBSCAN, when called with minpoints=1, becomes a window function implementation of ST_ClusterWithin. An example is in georezo.net/forum/viewtopic.php?pid=284515 – dbaston Jun 2 '17 at 13:09
  • Thank you very much! My query is now very fast and easier too read. – Nicolas Boisteault Jun 6 '17 at 9:58

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.