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
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()); }
participants (1)
-
tchemit＠users.forge.codelutin.com