0

I need to create a query using some sort of spatial clustering algorithm within PostGIS database. Currently, I am using pgAdmin version 4. More specifically, I need to create N number of groups that have 10 elements. I know the output will end up with groups that have less but the groups should not have more than the 10 elements.

Can the variable for 10 be parametrized?

I have read the following post since it seems to provide useful information to achieve what I need but my level of expertise with PostGIS is not much. To only thing that I do not see that is addressed here is that I need to be able to select the number of groups and features to assign per group. It could be for point and/or polygon geometry. Scenario: I have a subset of 388 polygons. I need to make N number of groups with 10 polygons. The 10 polygons cannot be random but they must be adjacent or the closest possible. The output will be used to send inspectors to the field. Since they will be walking, it is preferred that the polygons shapes are adjacent to each other, or the closest to each other as possible. I was able to achieve the following output using a QGIS plugin: ClusterPoints. It works great but it does not allow me to set the predefined number of features per group therefore, I ended up with groups with more than 10 features and others with three or four features assign to them.

Input: enter image description here

Desired Output (this output has the flaws described above): enter image description here

My intention is to have someone point me in the right direction.

| improve this question | | | | |
  • did you see this question ? – pLumo May 17 '18 at 15:44
  • and this ? – pLumo May 17 '18 at 15:46
  • I think the way to do this is to use ST_ClusterDBSCAN which allows you to set the minimum number in each cluster and retrieve the ids in each cluster. Play around with the tolerance distance until you have one cluster that has exactly 10 elements -- the rest will have more. Remove those 10 and repeat, constantly shrinking the distance and removing each cluster of 10, which should be the minimum each time. It would be a bit trial and error. You could put the whole thing in a loop in a plpgsql function, but that is the general idea in pseudocode. – John Powell May 17 '18 at 17:30
  • I´d go for ST_ClusterKMeans and increase the number of clusters until all clusters have no more than 10 geometries. – geozelot May 17 '18 at 20:57
  • I appreciate your replies and suggestions. I will definitely explore them. If it is not an issue, can someone post an example of how to approach this? Like I mention above, I am no expert although if I see an example, I can replicate it with my data. Any help will be appreciated. Thanks again. – Ernesto CD May 17 '18 at 21:06

Your Answer

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

Browse other questions tagged or ask your own question.