group-by-improvement.diff

diff --git a/src/main/java/org/gcube/dataanalysis/ecoengine/evaluation/bioclimate/BioClimateAnalysis.java b/src/main/java/org/gcube/dataanalysis/ecoengine/evaluation/bioclimate/BioClimateAnalysis.java
index 26d0106..d99be5f 100644
--- a/src/main/java/org/gcube/dataanalysis/ecoengine/evaluation/bioclimate/BioClimateAnalysis.java
+++ b/src/main/java/org/gcube/dataanalysis/ecoengine/evaluation/bioclimate/BioClimateAnalysis.java
@@ -360,18 +360,24 @@ public class BioClimateAnalysis {
 				float statusstep = 80f / (float) numbOfTables;
 				// for each table
 				for (int i = 0; i < numbOfTables; i++) {
+					String preparationQuery = "drop table if exists tmpanalysis; create temp table tmpanalysis as select a.faoaream,eezall,lme,count(*) from %1$s as a where a.probability > #THRESHOLD# group by faoaream,eezall,lme;";
+
+					preparationQuery = String.format(preparationQuery, hspecTables[i]);
+					preparationQuery = preparationQuery.replace("#THRESHOLD#", ""+threshold);
+
+					AnalysisLogger.getLogger().trace("Executing query for counting probabilities: "+query);
+					DatabaseFactory.executeSQLQuery(preparationQuery, referencedbConnection);
+					AnalysisLogger.getLogger().trace("Query Executed");
+
 					// for each criterion to apply: fao area, lme etc.
 					for (int j = 0; j < criteriaNames.length; j++) {
-						List<Object> listCriterion = DatabaseFactory.executeSQLQuery(DatabaseUtils.getDinstictElements("hcaf_s", selectionCriteria[j],criteriaFilters[j]),referencedbConnection); 
-						for (Object code: listCriterion){
+						String criteriaQuery = String.format("select %1$s,sum(count) from tmpanalysis group by %1$s;", criteriaNames[j]);
+						List<Object> codeSums = DatabaseFactory.executeSQLQuery(criteriaQuery, referencedbConnection);
+
+						for (Object codeSum : codeSums){
+							Object code = codeSum.get(0);
+							int countPerArea = (codeSum.get(1)==null)?0:Integer.parseInt(""+codeSum.get(1));
 							String code$ = ""+code;
-							String query = String.format(countProbabilityPerArea,hspecTables[i],selectionCriteria[j],code$);
-							query = query.replace("#THRESHOLD#", ""+threshold);
-							AnalysisLogger.getLogger().trace("Executing query for counting probabilities: "+query);
-							List<Object> counts = DatabaseFactory.executeSQLQuery(query, referencedbConnection);
-							AnalysisLogger.getLogger().trace("Query Executed");
-							int countPerArea = (counts==null)?0:Integer.parseInt(""+counts.get(0));
-							
 							String chartName = "Hspec (prob>0.8) for " + criteriaNames[j] + "_" + code$;
 							// put the code and the value in the timeseries associated to the feature name
 							HashMap<String, double[]> submap = GeoMap.get(chartName);