[Echobase-commits] r833 - in trunk: echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb echobase-services/src/main/java/fr/ifremer/echobase/services/importdata echobase-ui/src/main/assembly/dist echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/importData

3 Aug 2013

Author: tchemit
Date: 2013-08-03 22:00:59 +0200 (Sat, 03 Aug 2013)
New Revision: 833

Url: http://forge.codelutin.com/projects/echobase/repository/revisions/833

Log:
refs #3037: Spatialisation des donn?\195?\169es pour la production de cartes


Added:
   trunk/echobase-ui/src/main/assembly/dist/help.sql
Modified:
   trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_2.java
   trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdata/AbstractImportConfiguration.java
   trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdata/AbstractImportDataService.java
   trunk/echobase-ui/src/main/assembly/dist/postgis-structure.sql
   trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/importData/AbstractConfigureImport.java

Modified: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_2.java
===================================================================

--- trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_2.java	2013-08-02 17:39:27 UTC (rev 832)
+++ trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_2.java	2013-08-03 20:00:59 UTC (rev 833)
@@ -23,6 +23,9 @@
  * #L%
  */
 
+import org.apache.commons.logging.Log;
+import org.apache.commons.logging.LogFactory;
+import org.nuiton.topia.TopiaContextFactory;
 import org.nuiton.topia.TopiaException;
 import org.nuiton.topia.framework.TopiaContextImplementor;
 import org.nuiton.util.Version;
@@ -36,6 +39,11 @@
  * @since 2.2
  */
 public class MigrationCallBackForVersion2_2 extends MigrationCallBackForVersion {
+
+    /** Logger. */
+    private static final Log log =
+            LogFactory.getLog(MigrationCallBackForVersion2_2.class);
+
     @Override
     public Version getVersion() {
         return new Version("2.2");
@@ -50,6 +58,19 @@
         // remove result category NotNull Constrainst (http://forge.codelutin.com/issues/3029)
         removeResultCategoryNotNullConstrainst(queries);
 
+        // compute all spatial data (http://forge.codelutin.com/issues/3037)
+        String dialect = tx.getHibernateConfiguration().getProperty(TopiaContextFactory.CONFIG_DIALECT);
+
+        if (!dialect.toLowerCase().contains("h2")) {
+            try {
+                // compute all spatial data
+                tx.executeSQL("SELECT echobase_compute_all_spatial_data();");
+            } catch (TopiaException e) {
+                if (log.isErrorEnabled()) {
+                    log.error("Could not update spatial datas", e);
+                }
+            }
+        }
     }
 
     protected void removeResultCategoryNotNullConstrainst(List<String> queries) {

Modified: trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdata/AbstractImportConfiguration.java
===================================================================
--- trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdata/AbstractImportConfiguration.java	2013-08-02 17:39:27 UTC (rev 832)
+++ trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdata/AbstractImportConfiguration.java	2013-08-03 20:00:59 UTC (rev 833)
@@ -62,6 +62,13 @@
      */
     protected ImportType importType;
 
+    /**
+     * Flag to compute spatial data at the end of the import.
+     *
+     * @since 2.2
+     */
+    protected boolean computeSpatialData;
+
     /** Result stats for each file imported. (mainly kept for testing purpose). */
     protected final List<EchoBaseCsvFileImportResult> importResults =
             Lists.newArrayList();
@@ -98,6 +105,14 @@
         this.importType = importType;
     }
 
+    public boolean isComputeSpatialData() {
+        return computeSpatialData;
+    }
+
+    public void setComputeSpatialData(boolean computeSpatialData) {
+        this.computeSpatialData = computeSpatialData;
+    }
+
     public List<EchoBaseCsvFileImportResult> getImportResults() {
         return importResults;
     }

Modified: trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdata/AbstractImportDataService.java
===================================================================
--- trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdata/AbstractImportDataService.java	2013-08-02 17:39:27 UTC (rev 832)
+++ trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdata/AbstractImportDataService.java	2013-08-03 20:00:59 UTC (rev 833)
@@ -54,11 +54,11 @@
 import org.apache.commons.lang3.StringUtils;
 import org.apache.commons.logging.Log;
 import org.apache.commons.logging.LogFactory;
+import org.nuiton.csv.ImportRuntimeException;
 import org.nuiton.topia.TopiaException;
 import org.nuiton.topia.persistence.TopiaDAO;
 import org.nuiton.topia.persistence.TopiaEntity;
 import org.nuiton.util.TimeLog;
-import org.nuiton.csv.ImportRuntimeException;
 
 import java.io.BufferedReader;
 import java.io.FileInputStream;
@@ -118,6 +118,21 @@
             commitTransaction("Could not execute import " +
                               getImportLabel(configuration));
 
+            if (configuration.isComputeSpatialData()) {
+                try {
+                    if (log.isInfoEnabled()) {
+                        log.info("Will try to compute spatial data...");
+                    }
+                    getTransaction().executeSQL("SELECT echobase_fill_cell_spatial_table();");
+                    commitTransaction("Could not compute spatial data for import " +
+                                      getImportLabel(configuration));
+                } catch (Exception e) {
+                    if (log.isErrorEnabled()) {
+                        log.error("Could not compute spatial data", e);
+                    }
+                }
+            }
+
             TIME_LOG.log(s0, "importCommited");
 
             return result;

Added: trunk/echobase-ui/src/main/assembly/dist/help.sql
===================================================================
--- trunk/echobase-ui/src/main/assembly/dist/help.sql	                        (rev 0)
+++ trunk/echobase-ui/src/main/assembly/dist/help.sql	2013-08-03 20:00:59 UTC (rev 833)
@@ -0,0 +1,337 @@
+DROP FUNCTION IF EXISTS echobase_get_cell_data(cell_id VARCHAR );
+
+CREATE OR REPLACE FUNCTION echobase_get_cell_data(
+  cell_id VARCHAR)
+  RETURNS TABLE(cellId VARCHAR, name VARCHAR, value VARCHAR) AS $$
+BEGIN
+  RETURN QUERY SELECT
+                 d.cell,
+                 dm.name,
+                 d.datavalue
+               FROM data d LEFT OUTER JOIN datametadata AS dm
+                   ON d.datametadata = dm.topiaid
+               WHERE d.cell =
+                     cell_id;
+END
+$$ LANGUAGE plpgsql;
+
+DROP FUNCTION IF EXISTS echobase_get_cell_data_by_type(cell_type_id VARCHAR );
+CREATE OR REPLACE FUNCTION echobase_get_cell_data_by_type(
+  cell_type_id VARCHAR)
+  RETURNS TABLE(cellId VARCHAR, name VARCHAR, value VARCHAR) AS $$
+BEGIN
+  RETURN QUERY SELECT
+
+                 d.cell,
+                 dm.name,
+                 d.datavalue
+               FROM data d LEFT OUTER JOIN datametadata AS dm
+                   ON d.datametadata = dm.topiaid
+                 ,
+                 cell c
+               WHERE d.cell = c.topiaid AND c.celltype = cell_type_id;
+END
+$$ LANGUAGE plpgsql;
+
+-- get all esdu
+SELECT
+  *
+FROM cell
+WHERE celltype =
+      'fr.ifremer.echobase.entities.references.CellType#1323141495215#0.7552225719013331';
+
+-- esdu fr.ifremer.echobase.entities.data.Cell#1334269715614#0.1761358511840082
+
+-- get all elementary
+SELECT
+  *
+FROM cell
+WHERE celltype =
+      'fr.ifremer.echobase.entities.references.CellType#1323141495215#0.4040239553899768';
+
+-- elementary S1 fr.ifremer.echobase.entities.data.Cell#1334269715616#0.9952328417444304
+-- elementary F1 fr.ifremer.echobase.entities.data.Cell#1334269715618#0.93378795138463
+-- elementary TOTAL fr.ifremer.echobase.entities.data.Cell#1334269715649#0.3655892238898185
+
+-- add missing data for fr.ifremer.echobase.entities.data.Cell#1334269715616#0.9952328417444304
+INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES (
+  'fr.ifremer.echobase.entities.data.Data#latitude#start',
+  now(),
+  0,
+  'fr.ifremer.echobase.entities.data.Cell#1334269715616#0.9952328417444304',
+  'fr.ifremer.echobase.entities.references.DataMetadata#1323610280601#0.7298992114976368',
+  '4509.876N',
+  'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906'
+);
+INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES (
+  'fr.ifremer.echobase.entities.data.Data#longitude#start',
+  now(),
+  0,
+  'fr.ifremer.echobase.entities.data.Cell#1334269715616#0.9952328417444304',
+  'fr.ifremer.echobase.entities.references.DataMetadata#1323610280597#0.9322615025965237',
+  '00311.583W',
+  'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906'
+);
+INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES (
+  'fr.ifremer.echobase.entities.data.Data#latitude#end',
+  now(),
+  0,
+  'fr.ifremer.echobase.entities.data.Cell#1334269715616#0.9952328417444304',
+  'fr.ifremer.echobase.entities.references.DataMetadata#1323610280609#0.8321851834658549',
+  '4509.876N',
+  'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906'
+);
+INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES (
+  'fr.ifremer.echobase.entities.data.Data#longitude#end',
+  now(),
+  0,
+  'fr.ifremer.echobase.entities.data.Cell#1334269715616#0.9952328417444304',
+  'fr.ifremer.echobase.entities.references.DataMetadata#1323610280607#0.8537654504079055',
+  '00311.583W',
+  'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906'
+);
+
+-- add missing data for fr.ifremer.echobase.entities.data.Cell#1334269715618#0.93378795138463
+INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES (
+  'fr.ifremer.echobase.entities.data.Data#latitude#start2',
+  now(),
+  0,
+  'fr.ifremer.echobase.entities.data.Cell#1334269715618#0.93378795138463',
+  'fr.ifremer.echobase.entities.references.DataMetadata#1323610280601#0.7298992114976368',
+  '4509.876N',
+  'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906'
+);
+INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES (
+  'fr.ifremer.echobase.entities.data.Data#longitude#end2',
+  now(),
+  0,
+  'fr.ifremer.echobase.entities.data.Cell#1334269715618#0.93378795138463',
+  'fr.ifremer.echobase.entities.references.DataMetadata#1323610280607#0.8537654504079055',
+  '00311.583W',
+  'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906'
+);
+INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES (
+  'fr.ifremer.echobase.entities.data.Data#latitude#end2',
+  now(),
+  0,
+  'fr.ifremer.echobase.entities.data.Cell#1334269715618#0.93378795138463',
+  'fr.ifremer.echobase.entities.references.DataMetadata#1323610280609#0.8321851834658549',
+  '4509.876N',
+  'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906'
+);
+INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES (
+  'fr.ifremer.echobase.entities.data.Data#longitude#start2',
+  now(),
+  0,
+  'fr.ifremer.echobase.entities.data.Cell#1334269715618#0.93378795138463',
+  'fr.ifremer.echobase.entities.references.DataMetadata#1323610280597#0.9322615025965237',
+  '00311.583W',
+  'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906'
+);
+
+-- add missing data for fr.ifremer.echobase.entities.data.Cell#1334269715649#0.3655892238898185
+INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES (
+  'fr.ifremer.echobase.entities.data.Data#longitude#end3',
+  now(),
+  0,
+  'fr.ifremer.echobase.entities.data.Cell#1334269715649#0.3655892238898185',
+  'fr.ifremer.echobase.entities.references.DataMetadata#1323610280607#0.8537654504079055',
+  '00311.583W',
+  'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906'
+);
+INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES (
+  'fr.ifremer.echobase.entities.data.Data#latitude#end3',
+  now(),
+  0,
+  'fr.ifremer.echobase.entities.data.Cell#1334269715649#0.3655892238898185',
+  'fr.ifremer.echobase.entities.references.DataMetadata#1323610280609#0.8321851834658549',
+  '4509.876N',
+  'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906'
+);
+INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES (
+  'fr.ifremer.echobase.entities.data.Data#longitude#start3',
+  now(),
+  0,
+  'fr.ifremer.echobase.entities.data.Cell#1334269715649#0.3655892238898185',
+  'fr.ifremer.echobase.entities.references.DataMetadata#1323610280597#0.9322615025965237',
+  '00311.583W',
+  'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906'
+);
+INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES (
+  'fr.ifremer.echobase.entities.data.Data#latitude#start3',
+  now(),
+  0,
+  'fr.ifremer.echobase.entities.data.Cell#1334269715649#0.3655892238898185',
+  'fr.ifremer.echobase.entities.references.DataMetadata#1323610280601#0.7298992114976368',
+  '4509.876N',
+  'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906'
+);
+INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES (
+  'fr.ifremer.echobase.entities.data.Data#depthSurfaceStart',
+  now(),
+  0,
+  'fr.ifremer.echobase.entities.data.Cell#1334269715649#0.3655892238898185',
+  'fr.ifremer.echobase.entities.references.DataMetadata#1323610280604#0.24754673981362407',
+  '50',
+  'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906'
+);
+
+INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES (
+  'fr.ifremer.echobase.entities.data.Data#depthBottomStart',
+  now(),
+  0,
+  'fr.ifremer.echobase.entities.data.Cell#1334269715649#0.3655892238898185',
+  'fr.ifremer.echobase.entities.references.DataMetadata#1323610280606#0.5079595792861521',
+  '50.2',
+  'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906'
+);
+
+-- get all regionSurf
+SELECT
+  *
+FROM cell
+WHERE celltype =
+      'fr.ifremer.echobase.entities.references.CellType#1323141495214#0.9174274554668074';
+-- get all regionCLAS
+SELECT
+  *
+FROM cell
+WHERE celltype =
+      'fr.ifremer.echobase.entities.references.CellType#1323141495214#0.93529014905942';
+
+-- region SURF fr.ifremer.echobase.entities.data.Cell#1334269910921#0.32895739765394183
+
+INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES (
+  'fr.ifremer.echobase.entities.data.Data#regionEnv-0',
+  now(),
+  0,
+  'fr.ifremer.echobase.entities.data.Cell#1334269910921#0.32895739765394183',
+  'fr.ifremer.echobase.entities.references.DataMetadata#1332945937351#0.37425974728165657',
+  '-2 43.8651276 52',
+  'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906'
+);
+
+INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES (
+  'fr.ifremer.echobase.entities.data.Data#regionEnv-1',
+  now(),
+  0,
+  'fr.ifremer.echobase.entities.data.Cell#1334269910921#0.32895739765394183',
+  'fr.ifremer.echobase.entities.references.DataMetadata#1332945937351#0.37425974728165657',
+  '-2.5 43.8651276 52',
+  'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906'
+);
+
+INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES (
+  'fr.ifremer.echobase.entities.data.Data#regionEnv-2',
+  now(),
+  0,
+  'fr.ifremer.echobase.entities.data.Cell#1334269910921#0.32895739765394183',
+  'fr.ifremer.echobase.entities.references.DataMetadata#1332945937351#0.37425974728165657',
+  '-3 43.8651276 52',
+  'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906'
+);
+
+INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES (
+  'fr.ifremer.echobase.entities.data.Data#regionEnv-3',
+  now(),
+  0,
+  'fr.ifremer.echobase.entities.data.Cell#1334269910921#0.32895739765394183',
+  'fr.ifremer.echobase.entities.references.DataMetadata#1332945937351#0.37425974728165657',
+  '-3.5 43.8651276 52',
+  'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906'
+);
+
+INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES (
+  'fr.ifremer.echobase.entities.data.Data#regionEnv-4',
+  now(),
+  0,
+  'fr.ifremer.echobase.entities.data.Cell#1334269910921#0.32895739765394183',
+  'fr.ifremer.echobase.entities.references.DataMetadata#1332945937351#0.37425974728165657',
+  '-2 43.8651276 52',
+  'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906'
+);
+
+-- region CLAS fr.ifremer.echobase.entities.data.Cell#1334269715603#0.9055571201552138
+
+INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES (
+  'fr.ifremer.echobase.entities.data.Data#regionEnv2-0',
+  now(),
+  0,
+  'fr.ifremer.echobase.entities.data.Cell#1334269715603#0.9055571201552138',
+  'fr.ifremer.echobase.entities.references.DataMetadata#1332945937351#0.37425974728165657',
+  '-2 43.8651276 52',
+  'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906'
+);
+
+INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES (
+  'fr.ifremer.echobase.entities.data.Data#regionEnv2-1',
+  now(),
+  0,
+  'fr.ifremer.echobase.entities.data.Cell#1334269715603#0.9055571201552138',
+  'fr.ifremer.echobase.entities.references.DataMetadata#1332945937351#0.37425974728165657',
+  '-2.5 43.8651276 52',
+  'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906'
+);
+
+INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES (
+  'fr.ifremer.echobase.entities.data.Data#regionEnv2-2',
+  now(),
+  0,
+  'fr.ifremer.echobase.entities.data.Cell#1334269715603#0.9055571201552138',
+  'fr.ifremer.echobase.entities.references.DataMetadata#1332945937351#0.37425974728165657',
+  '-3 43.8651276 52',
+  'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906'
+);
+
+INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES (
+  'fr.ifremer.echobase.entities.data.Data#regionEnv2-3',
+  now(),
+  0,
+  'fr.ifremer.echobase.entities.data.Cell#1334269715603#0.9055571201552138',
+  'fr.ifremer.echobase.entities.references.DataMetadata#1332945937351#0.37425974728165657',
+  '-3.5 43.8651276 52',
+  'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906'
+);
+
+INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES (
+  'fr.ifremer.echobase.entities.data.Data#regionEnv2-4',
+  now(),
+  0,
+  'fr.ifremer.echobase.entities.data.Cell#1334269715603#0.9055571201552138',
+  'fr.ifremer.echobase.entities.references.DataMetadata#1332945937351#0.37425974728165657',
+  '-2 43.8651276 52',
+  'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906'
+);
+
+-- get all map
+SELECT
+  *
+FROM cell
+WHERE celltype =
+      'fr.ifremer.echobase.entities.references.CellType#1330625861828#0.5189359744467841'
+LIMIT 1;
+
+DELETE FROM echobase_cell_spatial_temp;
+DELETE FROM echobase_cell_spatial;
+
+INSERT INTO public.echobase_cell_spatial_temp (cellid, celltype) VALUES ('fr.ifremer.echobase.entities.data.Cell#1334269715614#0.1761358511840082', 'ESDU');
+INSERT INTO public.echobase_cell_spatial_temp (cellid, celltype) VALUES ('fr.ifremer.echobase.entities.data.Cell#1334269715616#0.9952328417444304', 'ELEMENTARY');
+INSERT INTO public.echobase_cell_spatial_temp (cellid, celltype) VALUES ('fr.ifremer.echobase.entities.data.Cell#1334269715618#0.93378795138463', 'ELEMENTARY');
+INSERT INTO public.echobase_cell_spatial_temp (cellid, celltype) VALUES ('fr.ifremer.echobase.entities.data.Cell#1334269715649#0.3655892238898185', 'ELEMENTARY');
+INSERT INTO public.echobase_cell_spatial_temp (cellid, celltype) VALUES ('fr.ifremer.echobase.entities.data.Cell#1334269910921#0.32895739765394183', 'REGION');
+INSERT INTO public.echobase_cell_spatial_temp (cellid, celltype) VALUES ('fr.ifremer.echobase.entities.data.Cell#1334269715603#0.9055571201552138', 'REGION');
+INSERT INTO public.echobase_cell_spatial_temp (cellid, celltype) VALUES ((SELECT
+                                                                            topiaid
+                                                                          FROM
+                                                                            cell
+                                                                          WHERE
+                                                                            celltype
+                                                                            =
+                                                                            'fr.ifremer.echobase.entities.references.CellType#1330625861828#0.5189359744467841'
+                                                                          LIMIT 1), 'MAP');
+
+SELECT
+  echobase_fill_cell_spatial_table();
+
+SELECT
+  echobase_compute_all_spatial_data();
\ No newline at end of file

Modified: trunk/echobase-ui/src/main/assembly/dist/postgis-structure.sql
===================================================================
--- trunk/echobase-ui/src/main/assembly/dist/postgis-structure.sql	2013-08-02 17:39:27 UTC (rev 832)
+++ trunk/echobase-ui/src/main/assembly/dist/postgis-structure.sql	2013-08-03 20:00:59 UTC (rev 833)
@@ -23,9 +23,9 @@
 CREATE EXTENSION postgis;
 
 -- creation table de spatialisation
-DROP TABLE IF EXISTS cell_spatial;
-CREATE TABLE cell_spatial (
-  voyageid            VARCHAR(256),
+DROP TABLE IF EXISTS echobase_cell_spatial;
+CREATE TABLE echobase_cell_spatial (
+  voyageid            VARCHAR(256) NOT NULL,
   voyagename          VARCHAR(256),
   transitid           VARCHAR(256),
   transitname         VARCHAR(256),
@@ -35,83 +35,106 @@
   dataAcquisitionname VARCHAR(256),
   dataProcessingid    VARCHAR(256),
   dataProcessingname  VARCHAR(256),
-  celltypeid          VARCHAR(256),
-  celltypename        VARCHAR(256),
-  cellid              VARCHAR(256),
-  cellname            VARCHAR(256),
+  celltypeid          VARCHAR(256) NOT NULL,
+  celltypename        VARCHAR(256) NOT NULL,
+  cellid              VARCHAR(256) PRIMARY KEY,
+  cellname            VARCHAR(256) NOT NULL,
+  lastUpdateDate      TIMESTAMP    NOT NULL,
   coordinate          geography (POINT, 4326),
   coordinate3D        geography (POINTZ, 4326),
-  shape               geography (POLYGON, 4326)
+  shape               geography (POLYGON, 4326),
+  FOREIGN KEY (cellid) REFERENCES cell (topiaid),
+  FOREIGN KEY (celltypeid) REFERENCES celltype (topiaid),
+  FOREIGN KEY (dataacquisitionid) REFERENCES dataacquisition (topiaid),
+  FOREIGN KEY (dataprocessingid) REFERENCES dataprocessing (topiaid),
+  FOREIGN KEY (transectid) REFERENCES transect (topiaid),
+  FOREIGN KEY (transitid) REFERENCES transit (topiaid),
+  FOREIGN KEY (voyageid) REFERENCES voyage (topiaid)
 );
+CREATE INDEX echobase_cell_spatial_coordinate_gix ON echobase_cell_spatial USING GIST (coordinate);
+CREATE INDEX echobase_cell_spatial_coordinate3D_gix ON echobase_cell_spatial USING GIST (coordinate3D);
+CREATE INDEX echobase_cell_spatial_shape_gix ON echobase_cell_spatial USING GIST (shape);
 
 -- creation table des traitements spatiaux a effectuer
-DROP TABLE IF EXISTS cell_spatial_temp;
-CREATE TABLE cell_spatial_temp (
-  cellid     VARCHAR(256),
-  celltypeid VARCHAR(256),
-  celltype   VARCHAR(256)
+DROP TABLE IF EXISTS echobase_cell_spatial_temp;
+CREATE TABLE echobase_cell_spatial_temp (
+  cellid   VARCHAR(256) PRIMARY KEY,
+  celltype VARCHAR(256) NOT NULL,
+  FOREIGN KEY (cellid) REFERENCES cell (topiaid)
 );
 
+--
 -- Trigger qui met à jour la table des traitements spatiaux à faire
+--
+
 CREATE OR REPLACE FUNCTION echobase_fill_spatial_temp_table()
   RETURNS TRIGGER AS $$
 DECLARE
-  cellId     VARCHAR;
-  cellTypeId VARCHAR;
-  cellType   VARCHAR;
-  dataType   VARCHAR;
-  doInsert   BOOLEAN;
+  cell_id      VARCHAR;
+  data_type_id VARCHAR;
+  cellType     VARCHAR;
+  dataType     VARCHAR;
+  doInsert     BOOLEAN;
+  result       RECORD;
 BEGIN
 
+  IF (TG_OP = 'DELETE')
+  THEN
+    result = OLD;
+  ELSE
+    result = NEW;
+  END IF;
+
+  cell_id = result.cell;
+  data_type_id = result.datametadata;
   doInsert = FALSE;
--- recuperation du type de la la cell
-  cellId = NEW.cell;
-  SELECT
-    ct.id,
-    ct.topiaid
-  INTO cellType, cellTypeId
-  FROM celltype ct, cell ce
-  WHERE ce.topiaid = cellId AND ct.topiaId = ce.celltype;
+-- recuperation du type de la cellule
+  cellType = echobase_get_cell_type(cell_id);
 
+  IF (SELECT
+        count(*)
+      FROM echobase_cell_spatial_temp c
+      WHERE c.cellid = cell_id) > 0
+  THEN
+-- la cellule est deja a traitee
+    RETURN result;
+  END IF;
 -- recuperation du type de la la data
   SELECT
     dt.name
   INTO dataType
   FROM datametadata dt
-  WHERE dt.topiaId = NEW.datametadata;
+  WHERE dt.topiaId = data_type_id;
 
   CASE cellType
-    WHEN 'Esdu'
+    WHEN 'ESDU'
     THEN
 -- Cell of type Esdu
--- only accept LatitudeStart - LatitudeBary - LatitudeEnd - LongitudeStart - LongitudeBary - LongitudeEnd data
       CASE dataType
         WHEN 'LatitudeStart', 'LatitudeBary', 'LatitudeEnd', 'LongitudeStart', 'LongitudeBary', 'LongitudeEnd'
         THEN
           doInsert = TRUE;
       END CASE;
-    WHEN 'Elementary'
+    WHEN 'ELEMENTARY'
     THEN
 -- Cell of type Elementary
--- only accept LatitudeStart - LatitudeBary - LatitudeEnd - LongitudeStart - LongitudeBary - LongitudeEnd - DepthRefSurfaceStart - DepthRefSurfaceEnd - DepthRefBottomStart - DepthRefBottomEnd data
       CASE dataType
         WHEN 'LatitudeStart', 'LatitudeBary', 'LatitudeEnd', 'LongitudeStart', 'LongitudeBary', 'LongitudeEnd', 'DepthRefSurfaceStart', 'DepthRefSurfaceEnd', 'DepthRefBottomStart', 'DepthRefBottomEnd'
         THEN
           doInsert = TRUE;
       END CASE;
-    WHEN 'Region', 'RegionSURF', 'RegionCLAS'
+    WHEN 'REGION', 'REGIONSURF', 'REGIONCLAS'
     THEN
 -- Cell of type Region
--- only accept coordinate data (RegionEnvCoordinates)
       CASE dataType
         WHEN 'RegionEnvCoordinates'
         THEN
           doInsert = TRUE;
+          cellType = 'REGION';
       END CASE;
-    WHEN 'Map'
+    WHEN 'MAP'
     THEN
 -- Cell of type Map
--- only accept coordinate data (GridCellLatitude, GridCellLongitude, GridLatitudeLag, GridLongitudeLag)
       CASE dataType
         WHEN 'GridCellLatitude', 'GridCellLongitude', 'GridLatitudeLag', 'GridLongitudeLag'
         THEN
@@ -122,263 +145,118 @@
   IF doInsert = TRUE
   THEN
 ---  Ajout de la cellule dans la table des traitements à effectuer
-    INSERT INTO cell_spatial_temp (cellid, celltypeid, celltype) VALUES (NEW.cellid, cellTypeId, cellType);
-    RAISE NOTICE 'Add cell %s [type %s]to cell_spatial_temp', NEW.cellid, cellType;
-
+    RAISE NOTICE 'Add cell % [type %] to echobase_cell_spatial_temp', cell_id, cellType;
+    INSERT INTO echobase_cell_spatial_temp (cellid, celltype) VALUES (cell_id, cellType);
   END IF;
 
-  RETURN NULL;
+  RETURN result;
 END
 $$
 LANGUAGE 'plpgsql';
 
 DROP TRIGGER IF EXISTS echobase_fill_spatial_work_table_trigger ON data;
-
--- ajout du trigger sur la suppression d'une celle
 CREATE TRIGGER echobase_fill_spatial_work_table_trigger
 AFTER INSERT OR UPDATE ON data
-FOR EACH ROW EXECUTE PROCEDURE echobase_fill_spatial_temp_table();
+FOR EACH ROW WHEN (NEW.cell IS NOT
+                   NULL) EXECUTE PROCEDURE echobase_fill_spatial_temp_table();
 
+--
 -- Trigger qui met à jour la table des traitements spatiaux à faire
+--
+
 CREATE OR REPLACE FUNCTION echobase_delete_cell()
   RETURNS TRIGGER AS $$
 BEGIN
 
-  DELETE FROM cell_spatial
-  WHERE cellid = OLD.cellid;
-  RAISE NOTICE 'Delete cell %s , delete cascade in cell_spatail_table', OLD.cellid;
+  RAISE NOTICE 'Delete cell % , delete cascade in cell_spatial_table', OLD.topiaid;
 
-  RETURN NULL;
+  DELETE FROM echobase_cell_spatial
+  WHERE cellid = OLD.topiaid;
+
+  RETURN OLD;
 END
 $$
 LANGUAGE 'plpgsql';
 
+DROP TRIGGER IF EXISTS echobase_delete_cell ON cell;
+
 CREATE TRIGGER echobase_delete_cell
 BEFORE DELETE ON cell
 FOR EACH ROW EXECUTE PROCEDURE echobase_delete_cell();
 
--- procedure pour creer (ou mettre à jour) une ligne dans cell_spatial
-CREATE OR REPLACE FUNCTION echobase_create_cell_spatial_row(
-  cell_id          VARCHAR,
-  cell_type_id     VARCHAR,
-  coordinateData   GEOGRAPHY,
-  coordinate3dData GEOGRAPHY,
-  shapeData        GEOGRAPHY)
+--
+-- Mettre a jour toutes les données spatiales
+--
+
+CREATE OR REPLACE FUNCTION echobase_compute_all_spatial_data()
   RETURNS VOID AS $$
-DECLARE
-  cellSpatialRow      RECORD;
-  voyageId            VARCHAR;
-  voyageName          VARCHAR;
-  transitId           VARCHAR;
-  transitName         VARCHAR;
-  transectId          VARCHAR;
-  transectName        VARCHAR;
-  dataAcquisitionId   VARCHAR;
-  dataAcquisitionName VARCHAR;
-  dataProcessingId    VARCHAR;
-  dataProcessingName  VARCHAR;
-  cellName            VARCHAR;
-  cellTypeName        VARCHAR;
-  cellParentId        VARCHAR;
-  cellRow             RECORD;
+DECLARE cell_id VARCHAR;
 BEGIN
-  SELECT
-    *
-  INTO cellSpatialRow
-  FROM cell_spatial cs
-  WHERE cs.cellid = cell_id;
-  IF NOT FOUND
-  THEN
--- create row
-    RAISE LOG 'Will create spatial cell %s', cell_id;
-    SELECT
-      cell_id
-    INTO cellParentId;
-    LOOP
-      SELECT
-        topiaid,
-        cell
-      INTO cellRow
-      FROM cell
-      WHERE topiaid = cellParentId;
-      EXIT WHEN cellRow.cell IS NULL;
-      SELECT
-        cellRow.cell
-      INTO cellParentId;
-    END LOOP;
-    RAISE LOG '+++++ Will cell parentId %', cellParentId;
--- get cell infos
-    SELECT
-      c.name,
-      ct.name
-    INTO cellName, cellTypeName
-    FROM cell c, celltype ct
-    WHERE c.topiaid = cell_id AND c.celltype = ct.topiaid;
--- get dataProcessing infos
-    SELECT
-      dp.topiaid,
-      dp.processingdescription
-    INTO dataProcessingId
-    FROM dataprocessing dp, cell c
-    WHERE c.topiaid = cellParentId AND dp.topiaid = c.dataprocessing;
-    IF NOT FOUND
-    THEN
--- no data processing, just use voyage
--- get voyage infos
-      SELECT
-        v.topiaid,
-        v.name
-      INTO voyageId, voyageName
-      FROM voyage v, cell c
-      WHERE c.topiaid = cellParentId AND v.topiaid = c.voyage;
-    ELSE
+  DELETE FROM echobase_cell_spatial;
 
--- get dataAcquisition infos
-      SELECT
-        da.topiaid,
-        da.acousticinstrument
-      INTO dataAcquisitionId, dataAcquisitionName
-      FROM dataacquisition da, dataprocessing dp
-      WHERE dp.topiaId = dataProcessingId AND da.topiaid = dp.dataacquisition;
--- get transect infos
-      SELECT
-        t.topiaid,
-        t.vessel
-      INTO transectId, transitName
-      FROM transect t, dataacquisition da
-      WHERE da.topiaid = dataAcquisitionId AND t.topiaid = da.transect;
--- get transit infos
-      SELECT
-        t.topiaid,
-        (t.starttime || ' - ' || t.endtime)
-      INTO transitId, transitName
-      FROM transit t, transect tt
-      WHERE tt.topiaid = transectId AND t.topiaid = tt.transit;
--- get voyage infos
-      SELECT
-        v.topiaid,
-        v.name
-      INTO voyageId, voyageName
-      FROM voyage v, transit t
-      WHERE t.topiaid = transitId AND v.topiaid = t.voyage;
-    END IF;
-    INSERT INTO cell_spatial (voyageid, voyagename, transitid, transitname, transectid, transectname, dataacquisitionid, dataacquisitionname, dataprocessingid, dataprocessingname, celltypeid, celltypename, cellid, cellname, coordinate, coordinate3D, shape)
-      VALUES (voyageId, voyageName, transitId, transitName, transectId, transectName, dataAcquisitionId, dataAcquisitionName, dataProcessingId, dataProcessingName, cell_type_id, cellTypeName, cell_id, cellName, coordinateData, coordinate3dData, shapeData);
-  ELSE
--- update row
-    RAISE LOG 'Will update spatial cell % ', cell_id;
-    UPDATE cell_spatial
-    SET coordinate = coordinateData, coordinate3d = coordinate3dData, shape= shapeData
-    WHERE cellid = cell_id;
-  END IF;
+  FOR cell_id IN SELECT
+                   topiaid
+                 FROM cell LOOP
+      PERFORM echobase_fill_cell_spatial_row(cell_id,
+                                             echobase_get_cell_type(cell_id));
+  END LOOP;
+
 END
-$$ LANGUAGE plpgsql;
+$$
+LANGUAGE plpgsql;
 
--- procédure qui mets à jour la table cell_spatial depuis cell_spatial_temp
+--
+-- Mettre a jour la table echobase_cell_spatial depuis echobase_cell_spatial_temp
+--
+
 CREATE OR REPLACE FUNCTION echobase_fill_cell_spatial_table()
   RETURNS VOID AS $$
 DECLARE
-  cellRow   RECORD;
-  latitude  RECORD;
-  longitude RECORD;
-  depth     RECORD;
+  cellRow RECORD;
+  depth   RECORD;
 BEGIN
   FOR cellRow IN SELECT
                    *
-                 FROM cell_spatial_temp LOOP
+                 FROM echobase_cell_spatial_temp LOOP
 
-    RAISE NOTICE 'Treat cell %s [type %s] ...', cellRow.cellid, cellRow.celltype;
-    CASE cellRow.celltype
-      WHEN 'Esdu'
-      THEN
-        EXECUTE echobase_fill_esdu_cell_spatial_table(cellRow.cellid);
-      WHEN 'Elementary'
-      THEN
-        EXECUTE echobase_fill_elementary_cell_spatial_table(cellRow.cellid);
-      WHEN 'Region', 'RegionSURF', 'RegionCLAS'
-      THEN
-        EXECUTE echobase_fill_region_cell_spatial_table(cellRow.cellid);
-      WHEN 'Map'
-      THEN
-        EXECUTE echobase_fill_map_cell_spatial_table(cellRow.cellid);
-    END CASE;
+      PERFORM echobase_fill_cell_spatial_row(cellRow.cellid, cellRow.celltype);
+
   END LOOP;
 
-  DELETE FROM cell_spatial_temp;
+  DELETE FROM echobase_cell_spatial_temp;
 
 END
 $$
 LANGUAGE plpgsql;
 
-CREATE OR REPLACE FUNCTION echobase_latitudetext_to_dd(latitude VARCHAR)
-  RETURNS REAL AS $$
-DECLARE
-  degre_  INTEGER;
-  minute_ INTEGER;
-  second_ INTEGER;
-  hemi_   VARCHAR(1);
+CREATE OR REPLACE FUNCTION echobase_fill_cell_spatial_row(cell_id   VARCHAR,
+                                                          cell_type VARCHAR)
+  RETURNS VOID AS $$
 BEGIN
-  SELECT
-    right(latitude, 1),
-    left(latitude, 2) :: INTEGER,
-    substring(latitude FROM 3 FOR 2) :: INTEGER,
-    substring(latitude FROM 6 FOR 3) :: INTEGER
-  INTO hemi_, degre_, minute_, second_;
 
-  RETURN echobase_dms2dd(degre_, minute_, second_, hemi_);
+  RAISE NOTICE 'Treat spatial cell % [type %] ...', cell_id, cell_type;
+  CASE cell_type
+    WHEN 'ESDU'
+    THEN
+      EXECUTE echobase_fill_esdu_cell_spatial_table(cell_id);
+    WHEN 'ELEMENTARY'
+    THEN
+      EXECUTE echobase_fill_elementary_cell_spatial_table(cell_id);
+    WHEN 'REGION'
+    THEN
+      EXECUTE echobase_fill_region_cell_spatial_table(cell_id);
+    WHEN 'MAP'
+    THEN
+      EXECUTE echobase_fill_map_cell_spatial_table(cell_id);
+  ELSE
+    RAISE LOG 'Can not deal with celltype % (cell %)', cell_type, cell_id;
+  END CASE;
 END
-$$ LANGUAGE plpgsql;
+$$
+LANGUAGE plpgsql;
 
-CREATE OR REPLACE FUNCTION echobase_longitudetext_to_dd(longitude VARCHAR)
-  RETURNS REAL AS $$
-DECLARE
-  degre_  INTEGER;
-  minute_ INTEGER;
-  second_ INTEGER;
-  hemi_   VARCHAR(1);
-BEGIN
-  SELECT
-    right(longitude, 1),
-    left(longitude, 3) :: INTEGER,
-    substring(longitude FROM 4 FOR 2) :: INTEGER,
-    substring(longitude FROM 7 FOR 3) :: INTEGER
-  INTO hemi_, degre_, minute_, second_;
-
-  RETURN echobase_dms2dd(degre_, minute_, second_, hemi_);
-END
-$$ LANGUAGE plpgsql;
-
-CREATE OR REPLACE FUNCTION echobase_dms2dd(
-  D INTEGER, M INTEGER, S INTEGER, HEMI CHARACTER VARYING(1)
-)
-  RETURNS DOUBLE PRECISION AS $$
-DECLARE
-  ret DOUBLE PRECISION;
-  dir INTEGER;
-BEGIN
-  dir := 1;
---init to 1 for default positive return
-  ret := 0;
---init to zero.
-     --ONLY S or W will trip this. Any other letter or NULL will result in positive return value
-  IF UPPER(HEMI) = 'S' OR UPPER(HEMI) = 'W'
-  THEN
-    dir := -1; --then southern or western hemisphere
-  END IF;
---SOME data has negative values in minutes and seconds as well as degrees. Use ABS to standardize all three.
-  ret := (ABS(CAST(D AS DOUBLE PRECISION)) + (ABS((CAST(M AS DOUBLE PRECISION) +
-                                                   (ABS((CAST(S AS
-                                                              DOUBLE PRECISION))
-                                                        / 60))) / 60)));
-  ret := ret * dir;
-  RETURN ret;
-
-END;
-$$ LANGUAGE plpgsql;
-
 CREATE OR REPLACE FUNCTION echobase_fill_esdu_cell_spatial_table(
-  cell_id      VARCHAR,
-  cell_type_id VARCHAR)
+  cell_id VARCHAR)
   RETURNS VOID AS $$
 DECLARE
   latitude        VARCHAR;
@@ -388,7 +266,7 @@
   latitudeNumber  REAL;
   longitudeNumber REAL;
 BEGIN
-  RAISE LOG 'Treat esdu cell %s ', cell_id;
+  RAISE DEBUG 'Treat esdu cell % ', cell_id;
 -- test if start / bary / end event
   SELECT
     d.datavalue
@@ -399,7 +277,7 @@
   IF FOUND
   THEN
 -- start data
-    RAISE LOG 'Treat esdu Start cell %s ', cell_id;
+    RAISE DEBUG 'Treat esdu Start cell % ', cell_id;
     SELECT
       d.datavalue
     INTO longitude
@@ -416,7 +294,7 @@
     IF FOUND
     THEN
 -- bary data
-      RAISE LOG 'Treat esdu Bary cell %s', cell_id;
+      RAISE DEBUG 'Treat esdu Bary cell %', cell_id;
       SELECT
         d.datavalue
       INTO longitude
@@ -433,7 +311,7 @@
       IF FOUND
       THEN
 -- end data
-        RAISE LOG 'Treat End cell %s', cell_id;
+        RAISE DEBUG 'Treat End cell %', cell_id;
         SELECT
           d.datavalue
         INTO longitude
@@ -442,7 +320,7 @@
         WHERE d.cell = cell_id AND dm.name = 'LongitudeEnd';
       ELSE
 -- no spatial data
-        RAISE LOG 'Could not find spatial data for cell %s', cell_id;
+        RAISE LOG 'Could not find spatial data for cell %', cell_id;
         RETURN;
       END IF;
     END IF;
@@ -458,23 +336,19 @@
   INTO longitudeNumber;
 
   SELECT
-    'SRID=4326;POINT(' || latitudeNumber || ' ' || longitudeNumber || ')'
+    'SRID=4326;POINT(' || longitudeNumber || ' ' || latitudeNumber || ')'
   INTO spatialText;
-  RAISE LOG '----------------------------------------- spatial data (%,%) for cell %', latitude, longitude, cell_id;
-  RAISE LOG '----------------------------------------- spatial data % for cell %', spatialText, cell_id;
+  RAISE DEBUG 'spatial data % for cell %', spatialText, cell_id;
 
-    PERFORM echobase_create_cell_spatial_row(cell_id,
-                                               cell_type_id,
-                                               ST_GeographyFromText(
-                                                   spatialText),
-                                               NULL,
-                                               NULL);
+    PERFORM echobase_create_echobase_cell_spatial_row(cell_id,
+                                                      spatialText,
+                                                      NULL,
+                                                      NULL);
 END
 $$ LANGUAGE plpgsql;
 
 CREATE OR REPLACE FUNCTION echobase_fill_elementary_cell_spatial_table(
-  cell_id      VARCHAR,
-  cell_type_id VARCHAR)
+  cell_id VARCHAR)
   RETURNS VOID AS $$
 DECLARE
   latitude        VARCHAR;
@@ -484,7 +358,7 @@
   latitudeNumber  REAL;
   longitudeNumber REAL;
 BEGIN
-  RAISE LOG 'Treat elementary cell %s ', cell_id;
+  RAISE DEBUG 'Treat elementary cell % ', cell_id;
 -- try start elementary
   SELECT
     d.datavalue
@@ -495,7 +369,7 @@
   IF FOUND
   THEN
 -- this is a start elementary
-    RAISE LOG 'Treat elementary Start cell %s ', cell_id;
+    RAISE DEBUG 'Treat elementary Start cell % ', cell_id;
 -- get longitude
     SELECT
       d.datavalue
@@ -531,7 +405,7 @@
     IF FOUND
     THEN
 -- this is a bary elementary
-      RAISE LOG 'Treat elementary Bary cell %s', cell_id;
+      RAISE DEBUG 'Treat elementary Bary cell %', cell_id;
       SELECT
         d.datavalue
       INTO longitude
@@ -565,7 +439,7 @@
       IF FOUND
       THEN
 -- this is a end elementary
-        RAISE LOG 'Treat elementary End cell %s', cell_id;
+        RAISE DEBUG 'Treat elementary End cell %', cell_id;
         SELECT
           d.datavalue
         INTO longitude
@@ -590,7 +464,7 @@
         END IF;
       ELSE
 -- no spatial data
-        RAISE LOG 'Could not find spatial data for cell %s', cell_id;
+        RAISE DEBUG 'Could not find spatial data for cell %', cell_id;
         RETURN;
       END IF;
     END IF;
@@ -606,17 +480,396 @@
   INTO longitudeNumber;
 
   SELECT
-    'SRID=4326;POINTZ(' || latitudeNumber || ' ' || longitudeNumber || ' ' ||
+    'SRID=4326;POINTZ(' || longitudeNumber || ' ' || latitudeNumber || ' ' ||
     depth || ')'
   INTO spatialText;
-  RAISE LOG '----------------------------------------- spatial data (%,%,%) for cell %', latitude, longitude, depth, cell_id;
-  RAISE LOG '----------------------------------------- spatial data % for cell %', spatialText, cell_id;
+  RAISE DEBUG 'spatial data % for cell %', spatialText, cell_id;
 
-    PERFORM echobase_create_cell_spatial_row(cell_id,
-                                             cell_type_id,
-                                             NULL,
-                                             ST_GeographyFromText(
-                                                 spatialText),
-                                             NULL);
+    PERFORM echobase_create_echobase_cell_spatial_row(cell_id,
+                                                      NULL,
+                                                      spatialText,
+                                                      NULL);
 END
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION echobase_fill_region_cell_spatial_table(
+  cell_id VARCHAR)
+  RETURNS VOID AS $$
+DECLARE
+  latitude       REAL;
+  longitude      REAL;
+  depth          REAL;
+  dataMetadataId VARCHAR;
+  dataValue      VARCHAR;
+  spatialText    VARCHAR := 'POLYGON((';
+BEGIN
+  RAISE DEBUG 'Treat region cell %', cell_id;
+  SELECT
+    topiaid
+  INTO dataMetadataId
+  FROM datametadata
+  WHERE name = 'RegionEnvCoordinates';
+  FOR dataValue IN SELECT
+                     d.datavalue
+                   FROM data d
+                   WHERE d.cell = cell_id AND d.datametadata = dataMetadataId
+                   ORDER BY d.topiacreatedate LOOP
+-- split dataValue in lat - long - depth
+    SELECT
+      split_part(dataValue, ' ', 1) :: REAL
+    INTO latitude;
+    SELECT
+      split_part(dataValue, ' ', 2) :: REAL
+    INTO longitude;
+    SELECT
+      split_part(dataValue, ' ', 3) :: REAL
+    INTO depth;
+    SELECT
+      spatialText || longitude || ' ' || latitude || ','
+    INTO spatialText;
+  END LOOP;
+  SELECT
+    left(spatialText, -1) || '))'
+  INTO spatialText;
+
+  RAISE DEBUG 'spatial data % for cell %', spatialText, cell_id;
+
+    PERFORM echobase_create_echobase_cell_spatial_row(cell_id,
+                                                      NULL,
+                                                      NULL,
+                                                      spatialText);
+END
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION echobase_fill_map_cell_spatial_table(
+  cell_id VARCHAR)
+  RETURNS VOID AS $$
+DECLARE
+  latitude       REAL;
+  longitude      REAL;
+  deltaLatitude  REAL;
+  deltaLongitude REAL;
+  P0             VARCHAR;
+  P1             VARCHAR;
+  P2             VARCHAR;
+  P3             VARCHAR;
+  spatialText    VARCHAR;
+BEGIN
+  RAISE DEBUG 'Treat map cell % ', cell_id;
+-- get GridCellLatitude
+  SELECT
+    d.datavalue :: REAL
+  INTO latitude
+  FROM data d LEFT OUTER JOIN datametadata AS dm
+      ON d.datametadata = dm.topiaid
+  WHERE d.cell = cell_id AND dm.name = 'GridCellLatitude';
+-- get GridCellLatitude
+  SELECT
+    d.datavalue :: REAL
+  INTO longitude
+  FROM data d LEFT OUTER JOIN datametadata AS dm
+      ON d.datametadata = dm.topiaid
+  WHERE d.cell = cell_id AND dm.name = 'GridCellLongitude';
+-- get GridCellLatitude
+  SELECT
+    d.datavalue :: REAL
+  INTO deltaLatitude
+  FROM data d LEFT OUTER JOIN datametadata AS dm
+      ON d.datametadata = dm.topiaid
+  WHERE d.cell = cell_id AND dm.name = 'GridLatitudeLag';
+-- get GridCellLatitude
+  SELECT
+    d.datavalue :: REAL
+  INTO deltaLongitude
+  FROM data d LEFT OUTER JOIN datametadata AS dm
+      ON d.datametadata = dm.topiaid
+  WHERE d.cell = cell_id AND dm.name = 'GridLongitudeLag';
+
+  IF
+  latitude IS NOT NULL AND longitude IS NOT NULL AND deltaLatitude IS NOT NULL
+  AND deltaLongitude IS NOT NULL
+  THEN
+    SELECT
+      longitude || ' ' || latitude
+    INTO P0;
+    SELECT
+      longitude || ' ' || latitude + deltaLatitude
+    INTO P1;
+    SELECT
+      longitude + deltaLongitude || ' ' || latitude + deltaLatitude
+    INTO P2;
+    SELECT
+      longitude + deltaLongitude || ' ' || latitude
+    INTO P3;
+
+    SELECT
+      'POLYGON((' || p0 || ',' || P1 || ',' || P2 || ',' || P3 || ',' || P0 ||
+      '))'
+    INTO spatialText;
+    RAISE DEBUG 'spatial data % for cell %', spatialText, cell_id;
+
+      PERFORM echobase_create_echobase_cell_spatial_row(cell_id,
+                                                        NULL,
+                                                        NULL,
+                                                        spatialText);
+  END IF;
+END
+$$ LANGUAGE plpgsql;
+
+-- procedure pour creer (ou mettre à jour) une ligne dans echobase_cell_spatial
+
+CREATE OR REPLACE FUNCTION echobase_create_echobase_cell_spatial_row(
+  cell_id          VARCHAR,
+  coordinateText   VARCHAR,
+  coordinate3dText VARCHAR,
+  shapeText        VARCHAR)
+  RETURNS VOID AS $$
+DECLARE
+  cellSpatialRow      RECORD;
+  voyageId            VARCHAR;
+  voyageName          VARCHAR;
+  transitId           VARCHAR;
+  transitName         VARCHAR;
+  transectId          VARCHAR;
+  transectName        VARCHAR;
+  dataAcquisitionId   VARCHAR;
+  dataAcquisitionName VARCHAR;
+  dataProcessingId    VARCHAR;
+  dataProcessingName  VARCHAR;
+  cellName            VARCHAR;
+  cellTypeId          VARCHAR;
+  cellTypeName        VARCHAR;
+  cellParentId        VARCHAR;
+  cellRow             RECORD;
+  coordinateData      GEOGRAPHY;
+  coordinate3dData    GEOGRAPHY;
+  shapeData           GEOGRAPHY;
+BEGIN
+  IF coordinateText IS NULL AND coordinate3dText IS NULL AND
+     shapeText IS NULL
+  THEN
+    RAISE DEBUG 'Could not find spatial data for cell %', cell_id;
+    RETURN;
+  END IF;
+  IF coordinateText IS NOT NULL
+  THEN
+    BEGIN
+      coordinateData = ST_GeographyFromText(coordinateText);
+      EXCEPTION WHEN internal_error
+      THEN
+
+        RAISE LOG 'Could not create coordinate spatial data %', coordinateText;
+        RETURN;
+    END;
+  ELSEIF coordinate3dText IS NOT NULL
+    THEN
+      BEGIN
+        coordinate3dData = ST_GeographyFromText(coordinate3dText);
+        EXCEPTION WHEN internal_error
+        THEN
+
+          RAISE LOG 'Could not create coordinate3D spatial data %', coordinate3dText;
+          RETURN;
+      END;
+  ELSEIF shapeText IS NOT NULL
+    THEN
+      BEGIN
+        shapeData = ST_GeographyFromText(shapeText);
+        EXCEPTION WHEN internal_error
+        THEN
+
+          RAISE LOG 'Could not create shape spatial data %', shapeText;
+          RETURN;
+      END;
+  END IF;
+
+  SELECT
+    *
+  INTO cellSpatialRow
+  FROM echobase_cell_spatial cs
+  WHERE cs.cellid = cell_id;
+  IF NOT FOUND
+  THEN
+-- create row
+    RAISE DEBUG 'Will create spatial cell %', cell_id;
+    SELECT
+      cell_id
+    INTO cellParentId;
+    LOOP
+      IF dataProcessingId IS NULL
+      THEN
+-- try to get dataprocessingId from this cell
+        SELECT
+          dp.topiaid,
+          dp.processingdescription
+        INTO dataProcessingId
+        FROM dataprocessing dp, cell c
+        WHERE c.topiaid = cellParentId AND dp.topiaid = c.dataprocessing;
+      END IF;
+      SELECT
+        topiaid,
+        cell
+      INTO cellRow
+      FROM cell
+      WHERE topiaid = cellParentId;
+      EXIT WHEN cellRow.cell IS NULL;
+      SELECT
+        cellRow.cell
+      INTO cellParentId;
+    END LOOP;
+    RAISE DEBUG 'use cell parentId %', cellParentId;
+-- get cell infos
+    SELECT
+      c.name,
+      ct.name,
+      ct.topiaid
+    INTO cellName, cellTypeName, cellTypeId
+    FROM cell c, celltype ct
+    WHERE c.topiaid = cell_id AND c.celltype = ct.topiaid;
+
+    IF dataProcessingId IS NULL
+    THEN
+-- get voyage infos
+      SELECT
+        v.topiaid,
+        v.name
+      INTO voyageId, voyageName
+      FROM voyage v, cell c
+      WHERE c.topiaid = cellParentId AND v.topiaid = c.voyage;
+    ELSE
+
+-- get dataAcquisition infos
+      SELECT
+        da.topiaid,
+        da.acousticinstrument
+      INTO dataAcquisitionId, dataAcquisitionName
+      FROM dataacquisition da, dataprocessing dp
+      WHERE dp.topiaId = dataProcessingId AND da.topiaid = dp.dataacquisition;
+-- get transect infos
+      SELECT
+        t.topiaid,
+        t.vessel
+      INTO transectId, transitName
+      FROM transect t, dataacquisition da
+      WHERE da.topiaid = dataAcquisitionId AND t.topiaid = da.transect;
+-- get transit infos
+      SELECT
+        t.topiaid,
+        (t.starttime || ' - ' || t.endtime)
+      INTO transitId, transitName
+      FROM transit t, transect tt
+      WHERE tt.topiaid = transectId AND t.topiaid = tt.transit;
+-- get voyage infos
+      SELECT
+        v.topiaid,
+        v.name
+      INTO voyageId, voyageName
+      FROM voyage v, transit t
+      WHERE t.topiaid = transitId AND v.topiaid = t.voyage;
+    END IF;
+    INSERT INTO echobase_cell_spatial (lastUpdateDate, voyageid, voyagename, transitid, transitname, transectid, transectname, dataacquisitionid, dataacquisitionname, dataprocessingid, dataprocessingname, celltypeid, celltypename, cellid, cellname, coordinate, coordinate3D, shape)
+      VALUES (now(), voyageId, voyageName, transitId, transitName, transectId, transectName, dataAcquisitionId, dataAcquisitionName, dataProcessingId, dataProcessingName, cellTypeId, cellTypeName, cell_id, cellName, coordinateData, coordinate3dData, shapeData);
+  ELSE
+-- update row
+    RAISE DEBUG 'Will update spatial cell % ', cell_id;
+    UPDATE echobase_cell_spatial
+    SET coordinate = coordinateData,
+      coordinate3d = coordinate3dData,
+      shape = shapeData
+    WHERE cellid = cell_id;
+  END IF;
+END
+$$ LANGUAGE plpgsql;
+
+-- pour obtenir le type d'une cellule sous forme ESDU - ELEMENTARY - REGION ou MAP
+CREATE OR REPLACE FUNCTION echobase_get_cell_type(cell_id VARCHAR)
+  RETURNS VARCHAR AS $$
+DECLARE result VARCHAR;
+BEGIN
+  SELECT
+    UPPER(ct.id)
+  INTO result
+  FROM celltype ct, cell ce
+  WHERE ce.topiaid = cell_id AND ct.topiaId = ce.celltype;
+  IF 'REGIONCLAS' = result OR 'REGIONSURF' = result
+  THEN
+    result = 'REGION';
+  END IF;
+  RETURN result;
+END
+$$
+LANGUAGE plpgsql;
+
+-- pour convertir des latitudes en dms (+ hemi) en dd
+CREATE OR REPLACE FUNCTION echobase_latitudetext_to_dd(latitude VARCHAR)
+  RETURNS REAL AS $$
+DECLARE
+  degre_  INTEGER;
+  minute_ INTEGER;
+  second_ INTEGER;
+  hemi_   VARCHAR(1);
+BEGIN
+  SELECT
+    right(latitude, 1),
+    left(latitude, 2) :: INTEGER,
+    substring(latitude FROM 3 FOR 2) :: INTEGER,
+    substring(latitude FROM 6 FOR 3) :: INTEGER
+  INTO hemi_, degre_, minute_, second_;
+
+  RETURN echobase_dms2dd(degre_, minute_, second_, hemi_);
+  EXCEPTION WHEN invalid_text_representation
+  THEN
+    RETURN NULL;
+END
+$$ LANGUAGE plpgsql;
+
+-- pour convertir des longitudes en dms (+ hemi) en dd
+CREATE OR REPLACE FUNCTION echobase_longitudetext_to_dd(longitude VARCHAR)
+  RETURNS REAL AS $$
+DECLARE
+  degre_  INTEGER;
+  minute_ INTEGER;
+  second_ INTEGER;
+  hemi_   VARCHAR(1);
+BEGIN
+  SELECT
+    right(longitude, 1),
+    left(longitude, 3) :: INTEGER,
+    substring(longitude FROM 4 FOR 2) :: INTEGER,
+    substring(longitude FROM 7 FOR 3) :: INTEGER
+  INTO hemi_, degre_, minute_, second_;
+
+  RETURN echobase_dms2dd(degre_, minute_, second_, hemi_);
+  EXCEPTION WHEN invalid_text_representation
+  THEN
+    RETURN NULL;
+END
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION echobase_dms2dd(
+  D INTEGER, M INTEGER, S INTEGER, HEMI CHARACTER VARYING(1)
+)
+  RETURNS DOUBLE PRECISION AS $$
+DECLARE
+  ret DOUBLE PRECISION;
+  dir INTEGER;
+BEGIN
+  dir := 1;
+--init to 1 for default positive return
+  ret := 0;
+--init to zero.
+     --ONLY S or W will trip this. Any other letter or NULL will result in positive return value
+  IF UPPER(HEMI) = 'S' OR UPPER(HEMI) = 'W'
+  THEN
+    dir := -1; --then southern or western hemisphere
+  END IF;
+--SOME data has negative values in minutes and seconds as well as degrees. Use ABS to standardize all three.
+  ret := (ABS(CAST(D AS DOUBLE PRECISION)) + (ABS((CAST(M AS DOUBLE PRECISION) +
+                                                   (ABS((CAST(S AS
+                                                              DOUBLE PRECISION))
+                                                        / 60))) / 60)));
+  ret := ret * dir;
+  RETURN ret;
+
+END;
 $$ LANGUAGE plpgsql;
\ No newline at end of file

Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/importData/AbstractConfigureImport.java
===================================================================
--- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/importData/AbstractConfigureImport.java	2013-08-02 17:39:27 UTC (rev 832)
+++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/importData/AbstractConfigureImport.java	2013-08-03 20:00:59 UTC (rev 833)
@@ -23,6 +23,7 @@
  */
 package fr.ifremer.echobase.ui.actions.importData;
 
+import fr.ifremer.echobase.entities.DriverType;
 import fr.ifremer.echobase.io.EchoBaseIOUtil;
 import fr.ifremer.echobase.io.InputFile;
 import fr.ifremer.echobase.services.importdata.AbstractImportConfiguration;
@@ -70,6 +71,8 @@
                 EchoBaseIOUtil.copyFile(inputFile, dataDirectory);
             }
         }
+        // can compute spatial data only for postgresql db
+        model.setComputeSpatialData(DriverType.POSTGRESQL == getEchoBaseSession().getWorkingDbConfiguration().getDriverType());
     }

    

[Echobase-commits] r833 - in trunk: echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb echobase-services/src/main/java/fr/ifremer/echobase/services/importdata echobase-ui/src/main/assembly/dist echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/importData

tchemit＠users.forge.codelutin.com