I need help because I am always in front of a problem in PostgreSQL since several weeks:

I have the following table (extract):

enter image description here

  • Id for the identifier
  • geom for the geometry of the polygon
  • burndate for the date of combustion of a surface (polygon)
  • year and month of the combustion

Here is my objective:

I wish to group polygons according to following both conditions:

  • they have to be distant from more than a certain fixed distance (this works with st_clusterwithin, I fix here the distance in 250m)

  • the difference between burndate values of groups polygons have to be inferior to 7 (that's the problem)

  • finally I would like to add to my generated table the minimum burn date of each polygons group

Here's my SQL Code:

DROP TABLE IF EXISTS view_eurasie;
CREATE TABLE view_eurasie AS
SELECT unnest(st_clusterWithin(tab.geom, 250)) AS cluster_field,
CAST (tab.annee AS integer) as annee,
tab.mois as mois,
FROM public."2003_08" as tab
ALTER TABLE public."view_eurasie" ADD COLUMN "identifiant" BIGSERIAL 

Here's the generated table (extract):

enter image description here

  • I think you need to create a burndate attribute first, and then call ST_ClusterWithin multiple times for each group where the burndate < 7. The minimum burndate you can retrieve afterwards, with a separate query using ST_Intersects on the returned clusters and along with the min function and limit. It would be helpful if you could provide a sample of your data? The other could be to use Cluster_DBScan which is implemented as a window function, which means that you can return additional information about each cluster. – John Powell Oct 26 '17 at 9:19
  • 1
    As far as "the difference between burndate values of groups polygons have to be inferior to 7", how would the algorithm know how these rows should be clustered? If the burn dates are 1, 3, 5, 7, and 9, what should the two groups be? – dbaston Oct 26 '17 at 13:21

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.