Author: tchemit Date: 2013-10-31 22:59:27 +0100 (Thu, 31 Oct 2013) New Revision: 872 Url: http://forge.codelutin.com/projects/echobase/repository/revisions/872 Log: fixes #3662: Updates pogresql driver to 9.2-1003-jdbc4 fixes #3663: L'application embarqu?\195?\169e ne d?\195?\169marre plus fixes #3660: Ajout des donn?\195?\169es spatiales pour les op?\195?\169rations fixes #3661: Simplifier la mise en place d'une base spatiale Added: trunk/echobase-services/src/main/resources/postgis-structure.sql trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/AddSpatial.java Removed: trunk/echobase-ui/src/main/assembly/dist/help.sql trunk/src/site/postgis-structure.sql Modified: trunk/echobase-domain/pom.xml trunk/echobase-services/pom.xml trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/DefaultEchoBaseServiceContext.java trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/EchoBaseServiceContext.java trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/EchoBaseServiceSupport.java trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/service/embeddedapplication/EmbeddedApplicationService.java trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/service/importdata/AbstractImportDataService.java trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/service/importdb/ImportDbService.java trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/service/spatial/SpatialService.java trunk/echobase-services/src/main/resources/embedded/echobase.properties trunk/echobase-services/src/test/java/fr/ifremer/echobase/services/FakeEchoBaseServiceContext.java trunk/echobase-tools/pom.xml trunk/echobase-tools/src/test/java/fr/ifremer/echobase/tools/AbstractToolTest.java trunk/echobase-ui/pom.xml trunk/echobase-ui/src/main/assembly/dist/echobase.properties trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/EchoBaseApplicationContext.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/EchoBaseInternalDbTransactionFilter.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/spatial/ShowSpatialData.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/Information.java trunk/echobase-ui/src/main/resources/config/struts-spatial.xml trunk/echobase-ui/src/main/resources/config/struts-workingDb.xml trunk/echobase-ui/src/main/resources/i18n/echobase-ui_en_GB.properties trunk/echobase-ui/src/main/resources/i18n/echobase-ui_fr_FR.properties trunk/echobase-ui/src/main/webapp/WEB-INF/jsp/spatial/showData.jsp trunk/echobase-ui/src/main/webapp/WEB-INF/jsp/workingDb/information.jsp trunk/echobase-ui/src/main/webapp/js/images/layers-2x.png trunk/echobase-ui/src/main/webapp/js/images/layers.png trunk/echobase-ui/src/main/webapp/js/images/marker-icon-2x.png trunk/echobase-ui/src/main/webapp/js/images/marker-icon.png trunk/echobase-ui/src/main/webapp/js/images/marker-shadow.png trunk/echobase-ui/src/main/webapp/js/leaflet-src.js trunk/pom.xml trunk/src/site/rst/db.rst Modified: trunk/echobase-domain/pom.xml =================================================================== --- trunk/echobase-domain/pom.xml 2013-10-31 18:16:19 UTC (rev 871) +++ trunk/echobase-domain/pom.xml 2013-10-31 21:59:27 UTC (rev 872) @@ -207,7 +207,7 @@ </dependency> <dependency> - <groupId>postgresql</groupId> + <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <scope>provided</scope> </dependency> Modified: trunk/echobase-services/pom.xml =================================================================== --- trunk/echobase-services/pom.xml 2013-10-31 18:16:19 UTC (rev 871) +++ trunk/echobase-services/pom.xml 2013-10-31 21:59:27 UTC (rev 872) @@ -173,7 +173,7 @@ </dependency> <dependency> - <groupId>postgresql</groupId> + <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <scope>test</scope> </dependency> Modified: trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/DefaultEchoBaseServiceContext.java =================================================================== --- trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/DefaultEchoBaseServiceContext.java 2013-10-31 18:16:19 UTC (rev 871) +++ trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/DefaultEchoBaseServiceContext.java 2013-10-31 21:59:27 UTC (rev 872) @@ -26,7 +26,6 @@ import fr.ifremer.echobase.config.EchoBaseConfiguration; import fr.ifremer.echobase.entities.TopiaEchoBaseInternalPersistenceContext; import fr.ifremer.echobase.entities.TopiaEchoBasePersistenceContext; -import fr.ifremer.echobase.entities.spatial.SpatialDataCache; import fr.ifremer.echobase.persistence.EchoBaseDbMeta; import org.apache.commons.lang3.time.DateUtils; import org.nuiton.topia.TopiaContextFactory; @@ -53,33 +52,35 @@ protected EchoBaseDbMeta dbMeta; - private SpatialDataCache spatialCache; +// private SpatialDataCache spatialCache; public static EchoBaseServiceContext newContext( Locale locale, EchoBaseConfiguration configuration, - EchoBaseDbMeta dbMeta, - SpatialDataCache spatialCache) { + EchoBaseDbMeta dbMeta) { +// , +// SpatialDataCache spatialCache) { return new DefaultEchoBaseServiceContext(locale, configuration, - dbMeta, - spatialCache); + dbMeta); +// , +// spatialCache); } protected DefaultEchoBaseServiceContext(Locale locale, EchoBaseConfiguration configuration, - EchoBaseDbMeta dbMeta, - SpatialDataCache spatialCache) { + EchoBaseDbMeta dbMeta) { +// SpatialDataCache spatialCache) { this.locale = locale; this.configuration = configuration; this.dbMeta = dbMeta; - this.spatialCache = spatialCache; +// this.spatialCache = spatialCache; } - @Override - public SpatialDataCache getSpatialDataCache() { - return spatialCache; - } +// @Override +// public SpatialDataCache getSpatialDataCache() { +// return spatialCache; +// } @Override public TopiaEchoBasePersistenceContext getEchoBasePersistenceContext() { Modified: trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/EchoBaseServiceContext.java =================================================================== --- trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/EchoBaseServiceContext.java 2013-10-31 18:16:19 UTC (rev 871) +++ trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/EchoBaseServiceContext.java 2013-10-31 21:59:27 UTC (rev 872) @@ -42,7 +42,7 @@ */ public interface EchoBaseServiceContext { - SpatialDataCache getSpatialDataCache(); +// SpatialDataCache getSpatialDataCache(); TopiaEchoBasePersistenceContext getEchoBasePersistenceContext(); Modified: trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/EchoBaseServiceSupport.java =================================================================== --- trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/EchoBaseServiceSupport.java 2013-10-31 18:16:19 UTC (rev 871) +++ trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/EchoBaseServiceSupport.java 2013-10-31 21:59:27 UTC (rev 872) @@ -25,18 +25,17 @@ import fr.ifremer.echobase.config.EchoBaseConfiguration; import fr.ifremer.echobase.entities.TopiaEchoBaseInternalPersistenceContext; import fr.ifremer.echobase.entities.TopiaEchoBasePersistenceContext; -import fr.ifremer.echobase.entities.spatial.SpatialDataCache; import fr.ifremer.echobase.persistence.EchoBaseDbMeta; -import fr.ifremer.echobase.services.service.exportdb.ExportDbService; -import fr.ifremer.echobase.services.service.exportdb.ExportService; -import fr.ifremer.echobase.services.service.exportquery.ExportQueryService; -import fr.ifremer.echobase.services.service.importdb.ImportDbService; -import fr.ifremer.echobase.services.service.removedata.RemoveDataService; import fr.ifremer.echobase.services.service.DbEditorService; import fr.ifremer.echobase.services.service.DecoratorService; import fr.ifremer.echobase.services.service.InternalDbPersistenceService; import fr.ifremer.echobase.services.service.UserService; import fr.ifremer.echobase.services.service.WorkingDbPersistenceService; +import fr.ifremer.echobase.services.service.exportdb.ExportDbService; +import fr.ifremer.echobase.services.service.exportdb.ExportService; +import fr.ifremer.echobase.services.service.exportquery.ExportQueryService; +import fr.ifremer.echobase.services.service.importdb.ImportDbService; +import fr.ifremer.echobase.services.service.removedata.RemoveDataService; import fr.ifremer.echobase.services.service.spatial.SpatialService; import java.util.Date; @@ -75,9 +74,9 @@ return serviceContext.getWorkgingDbUrl(); } - protected final SpatialDataCache getSpatialDataCache() { - return serviceContext.getSpatialDataCache(); - } +// protected final SpatialDataCache getSpatialDataCache() { +// return serviceContext.getSpatialDataCache(); +// } protected final Locale getLocale() { return serviceContext.getLocale(); Modified: trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/service/embeddedapplication/EmbeddedApplicationService.java =================================================================== --- trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/service/embeddedapplication/EmbeddedApplicationService.java 2013-10-31 18:16:19 UTC (rev 871) +++ trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/service/embeddedapplication/EmbeddedApplicationService.java 2013-10-31 21:59:27 UTC (rev 872) @@ -38,6 +38,7 @@ import fr.ifremer.echobase.services.DefaultEchoBaseServiceContext; import fr.ifremer.echobase.services.EchoBaseServiceContext; import fr.ifremer.echobase.services.EchoBaseServiceSupport; +import fr.ifremer.echobase.services.service.UserService; import fr.ifremer.echobase.services.service.exportdb.ExportDbConfiguration; import fr.ifremer.echobase.services.service.exportdb.ExportDbMode; import fr.ifremer.echobase.services.service.exportquery.ExportQueryInvalidNameException; @@ -47,7 +48,6 @@ import fr.ifremer.echobase.services.service.importdb.ImportDbConfiguration; import fr.ifremer.echobase.services.service.importdb.ImportDbMode; import fr.ifremer.echobase.services.service.importdb.ImportDbService; -import fr.ifremer.echobase.services.service.UserService; import fr.ifremer.echobase.services.service.workingDb.WorkingDbConfigurationAlreadyExistException; import org.apache.commons.io.FileUtils; import org.apache.commons.io.IOUtils; @@ -112,8 +112,9 @@ DefaultEchoBaseServiceContext.newContext( getLocale(), getConfiguration(), - getDbMeta(), - getSpatialDataCache()); + getDbMeta()); +// , +// getSpatialDataCache()); EchoBaseUser admin; try { @@ -364,7 +365,7 @@ } public static void copyEmbeddedBinaryFile(String resourceName, - File targetDirectory) throws IOException { + File targetDirectory) throws IOException { String resourcePath = EMBEDDED_PATH + resourceName; InputStream inputStream = EmbeddedApplicationService.class.getResourceAsStream(resourcePath); Modified: trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/service/importdata/AbstractImportDataService.java =================================================================== --- trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/service/importdata/AbstractImportDataService.java 2013-10-31 18:16:19 UTC (rev 871) +++ trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/service/importdata/AbstractImportDataService.java 2013-10-31 21:59:27 UTC (rev 872) @@ -117,10 +117,10 @@ // do commit getEchoBasePersistenceContext().commitTransaction(); - // revoke spatial data memory cache - getSpatialDataCache().onVoyageChanged( - serviceContext.getWorkgingDbUrl(), - configuration.getVoyageId()); +// // revoke spatial data memory cache +// getSpatialDataCache().onVoyageChanged( +// serviceContext.getWorkgingDbUrl(), +// configuration.getVoyageId()); // update sql spatial data getSpatialService().updatePostgisTable(); Modified: trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/service/importdb/ImportDbService.java =================================================================== --- trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/service/importdb/ImportDbService.java 2013-10-31 18:16:19 UTC (rev 871) +++ trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/service/importdb/ImportDbService.java 2013-10-31 21:59:27 UTC (rev 872) @@ -71,9 +71,9 @@ if (ImportDbMode.REFERENTIAL != importDbMode) { - // revoke all spatial data cache for all this db - getSpatialDataCache().onDbChanged( - serviceContext.getWorkgingDbUrl()); +// // revoke all spatial data cache for all this db +// getSpatialDataCache().onDbChanged( +// serviceContext.getWorkgingDbUrl()); // let's update postgis table getSpatialService().updatePostgisTable(); Modified: trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/service/spatial/SpatialService.java =================================================================== --- trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/service/spatial/SpatialService.java 2013-10-31 18:16:19 UTC (rev 871) +++ trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/service/spatial/SpatialService.java 2013-10-31 21:59:27 UTC (rev 872) @@ -23,6 +23,7 @@ * #L% */ +import com.google.common.base.Charsets; import com.google.common.base.Function; import com.google.common.base.Preconditions; import com.google.common.base.Predicate; @@ -32,6 +33,7 @@ import com.google.common.collect.Maps; import com.google.common.collect.Sets; import fr.ifremer.echobase.EchoBaseFunctions; +import fr.ifremer.echobase.EchoBaseTechnicalException; import fr.ifremer.echobase.entities.TopiaEchoBasePersistenceContext; import fr.ifremer.echobase.entities.data.Category; import fr.ifremer.echobase.entities.data.CellDAO; @@ -45,14 +47,16 @@ import fr.ifremer.echobase.entities.spatial.CellPoint; import fr.ifremer.echobase.entities.spatial.SpatialConfiguration; import fr.ifremer.echobase.entities.spatial.SpatialData; -import fr.ifremer.echobase.entities.spatial.SpatialDataCache; import fr.ifremer.echobase.services.EchoBaseServiceSupport; import org.apache.commons.collections.CollectionUtils; +import org.apache.commons.io.IOUtils; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.nuiton.topia.persistence.TopiaEntities; import org.nuiton.util.TimeLog; +import java.io.IOException; +import java.io.InputStream; import java.util.List; import java.util.Map; import java.util.Set; @@ -70,12 +74,46 @@ public static final TimeLog TILE_LOG = new TimeLog(SpatialService.class); + public static final String POSTGIS_STRUCTURE_SQL = "postgis-structure.sql"; + + public void addSpatialStructure() { + // get sql file + + InputStream inputStream = + getClass().getResourceAsStream("/" + POSTGIS_STRUCTURE_SQL); + + Preconditions.checkNotNull( + inputStream, + "Could not find resource in classpath " + POSTGIS_STRUCTURE_SQL); + + String sql; + try { + sql = IOUtils.toString(inputStream, Charsets.UTF_8); + } catch (IOException e) { + throw new EchoBaseTechnicalException( + "Could not load sql file " + POSTGIS_STRUCTURE_SQL, e); + } + + if (log.isInfoEnabled()) { + log.info("Will execute sql file " + POSTGIS_STRUCTURE_SQL); + } + + try { + getEchoBasePersistenceContext().executeSQL(sql); + getEchoBasePersistenceContext().commitTransaction(); + } catch (Exception e) { + throw new EchoBaseTechnicalException( + "Could not execute sql file " + POSTGIS_STRUCTURE_SQL, e); + } + } + public Set<SpatialData> getSpatialData(SpatialConfiguration model) { - SpatialDataCache cache = getSpatialDataCache(); +// SpatialDataCache cache = getSpatialDataCache(); +// +// Set<SpatialData> result = cache.get(model); + Set<SpatialData> result = null; - Set<SpatialData> result = cache.get(model); - if (result == null) { String voyageId = model.getVoyageId(); @@ -126,7 +164,7 @@ result = getSpatialData(voyage, dataMetadata, cellType, acceptPredicate); - cache.put(model, result); +// cache.put(model, result); } return result; @@ -228,6 +266,11 @@ return result; } + public boolean canUpdatePostgis() { + boolean result = getEchoBasePersistenceContext().canUpdatePostgis(); + return result; + } + /** * To update the {@code echobase_cell_spatial} table from the filled * table {@code echobase_cell_sptaial_temp}. @@ -241,8 +284,12 @@ // try the update only for postgresql try { if (log.isInfoEnabled()) { - log.info("Will try to compute spatial data from temp table..."); + log.info("Will try to compute operation spatial data from temp table..."); } + getEchoBasePersistenceContext().executeSQL("SELECT echobase_fill_operation_spatial_table();"); + if (log.isInfoEnabled()) { + log.info("Will try to compute cell spatial data from temp table..."); + } getEchoBasePersistenceContext().executeSQL("SELECT echobase_fill_cell_spatial_table();"); getEchoBasePersistenceContext().commitTransaction(); } catch (Exception e) { Modified: trunk/echobase-services/src/main/resources/embedded/echobase.properties =================================================================== --- trunk/echobase-services/src/main/resources/embedded/echobase.properties 2013-10-31 18:16:19 UTC (rev 871) +++ trunk/echobase-services/src/main/resources/embedded/echobase.properties 2013-10-31 21:59:27 UTC (rev 872) @@ -25,8 +25,5 @@ # Where to find data echobase.data.directory=. -# Where to find war -echobase.war.location=${echobase.data.directory}/echobase-ui-${echobase.version}-embedded.war - # To create embedded working db echobase.embedded=true Copied: trunk/echobase-services/src/main/resources/postgis-structure.sql (from rev 871, trunk/src/site/postgis-structure.sql) =================================================================== --- trunk/echobase-services/src/main/resources/postgis-structure.sql (rev 0) +++ trunk/echobase-services/src/main/resources/postgis-structure.sql 2013-10-31 21:59:27 UTC (rev 872) @@ -0,0 +1,1167 @@ +--- +-- #%L +-- EchoBase :: UI +-- $Id$ +-- $HeadURL$ +-- %% +-- Copyright (C) 2011 - 2013 Ifremer, Codelutin +-- %% +-- This program is free software: you can redistribute it and/or modify +-- it under the terms of the GNU Affero General Public License as published by +-- the Free Software Foundation, either version 3 of the License, or +-- (at your option) any later version. +-- +-- This program is distributed in the hope that it will be useful, +-- but WITHOUT ANY WARRANTY; without even the implied warranty of +-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +-- GNU General Public License for more details. +-- +-- You should have received a copy of the GNU Affero General Public License +-- along with this program. If not, see <http://www.gnu.org/licenses/>. +-- #L% +--- +CREATE EXTENSION postgis; + +-------------------------------------------------------------------------------- +-- Table spatial pour les cellules --------------------------------------------- +-------------------------------------------------------------------------------- + +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), + transectid VARCHAR(256), + transectname VARCHAR(256), + dataAcquisitionid VARCHAR(256), + dataAcquisitionname VARCHAR(256), + dataProcessingid VARCHAR(256), + dataProcessingname 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, + 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) +); + +SELECT AddGeometryColumn('echobase_cell_spatial', 'coordinate', 4326, 'POINT',2 ); +SELECT AddGeometryColumn('echobase_cell_spatial', 'coordinate3d', 4326, 'POINT',3); +SELECT AddGeometryColumn('echobase_cell_spatial', 'shape', 4326, 'POLYGON',2 ); + +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); + +-- pour stoquer les traitements spatiaux a effectuer +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 +-- + +DROP FUNCTION IF EXISTS echobase_fill_spatial_temp_table(); +DROP TRIGGER IF EXISTS echobase_fill_spatial_work_table_trigger ON data; + +CREATE OR REPLACE FUNCTION echobase_fill_cell_spatial_temp_table() + RETURNS TRIGGER AS $$ +DECLARE + 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 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 = data_type_id; + + CASE cellType + WHEN 'ESDU' + THEN +-- Cell of type Esdu + CASE dataType + WHEN 'LatitudeStart', 'LatitudeBary', 'LatitudeEnd', 'LongitudeStart', 'LongitudeBary', 'LongitudeEnd' + THEN + doInsert = TRUE; + ELSE + doInsert = FALSE; + END CASE; + WHEN 'ELEMENTARY' + THEN +-- Cell of type Elementary + CASE dataType + WHEN 'LatitudeStart', 'LatitudeBary', 'LatitudeEnd', 'LongitudeStart', 'LongitudeBary', 'LongitudeEnd', 'DepthRefSurfaceStart', 'DepthRefSurfaceEnd', 'DepthRefBottomStart', 'DepthRefBottomEnd' + THEN + doInsert = TRUE; + ELSE + doInsert = FALSE; + END CASE; + WHEN 'REGION', 'REGIONSURF', 'REGIONCLAS' + THEN +-- Cell of type Region + CASE dataType + WHEN 'RegionEnvCoordinates' + THEN + doInsert = TRUE; + cellType = 'REGION'; + ELSE + doInsert = FALSE; + END CASE; + WHEN 'MAP' + THEN +-- Cell of type Map + CASE dataType + WHEN 'GridCellLatitude', 'GridCellLongitude', 'GridLatitudeLag', 'GridLongitudeLag' + THEN + doInsert = TRUE; + ELSE + doInsert = FALSE; + END CASE; + END CASE; + + IF doInsert = TRUE + THEN +--- Ajout de la cellule dans la table des traitements à effectuer + RAISE DEBUG '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 result; +END +$$ +LANGUAGE 'plpgsql'; + +DROP TRIGGER IF EXISTS echobase_fill_cell_spatial_work_table_trigger ON data; +CREATE TRIGGER echobase_fill_cell_spatial_work_table_trigger +AFTER INSERT OR UPDATE ON data +FOR EACH ROW WHEN (NEW.cell IS NOT + NULL) EXECUTE PROCEDURE echobase_fill_cell_spatial_temp_table(); + +-- +-- Trigger qui met à jour la table des traitements spatiaux à faire lors d +-- 'une suppression de cellule +-- + +CREATE OR REPLACE FUNCTION echobase_delete_cell() + RETURNS TRIGGER AS $$ +BEGIN + + RAISE DEBUG 'Delete cell % , delete cascade in echobase_cell_spatial_table', OLD.topiaid; + + 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(); + +-- +-- Mettre a jour toutes les données spatiales de cellule +-- + +CREATE OR REPLACE FUNCTION echobase_compute_all_cell_spatial_data() + RETURNS VOID AS $$ +DECLARE cell_id VARCHAR; +BEGIN + DELETE FROM echobase_cell_spatial; + + FOR cell_id IN SELECT topiaid FROM cell LOOP + PERFORM echobase_fill_cell_spatial_row(cell_id); + END LOOP; +END +$$ +LANGUAGE plpgsql; + +-- +-- 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; +BEGIN + FOR cellRow IN SELECT + * + FROM echobase_cell_spatial_temp LOOP + + PERFORM echobase_fill_cell_spatial_row(cellRow.cellid, cellRow.celltype); + + END LOOP; + + DELETE FROM echobase_cell_spatial_temp; + +END +$$ +LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION echobase_fill_cell_spatial_row(cell_id VARCHAR) + RETURNS VOID AS $$ + DECLARE cell_type VARCHAR; +BEGIN + + cell_type = echobase_get_cell_type(cell_id); + + RAISE DEBUG '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; + +CREATE OR REPLACE FUNCTION echobase_fill_esdu_cell_spatial_table( + cell_id VARCHAR) + RETURNS VOID AS $$ +DECLARE + latitude VARCHAR; + longitude VARCHAR; + depth RECORD; + spatialText VARCHAR; + latitudeNumber REAL; + longitudeNumber REAL; +BEGIN + RAISE DEBUG 'Treat esdu cell % ', cell_id; +-- test if start / bary / end event + SELECT + d.datavalue + INTO latitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'LatitudeStart'; + IF FOUND + THEN +-- start data + RAISE DEBUG 'Treat esdu Start cell % ', cell_id; + SELECT + d.datavalue + INTO longitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'LongitudeStart'; + ELSE + SELECT + d.datavalue + INTO latitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'LatitudeBary'; + IF FOUND + THEN +-- bary data + RAISE DEBUG 'Treat esdu Bary cell %', cell_id; + SELECT + d.datavalue + INTO longitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'LongitudeBary'; + ELSE + SELECT + d.datavalue + INTO latitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'LatitudeEnd'; + IF FOUND + THEN +-- end data + RAISE DEBUG 'Treat End cell %', cell_id; + SELECT + d.datavalue + INTO longitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'LongitudeEnd'; + ELSE +-- no spatial data + RAISE LOG 'Could not find spatial data for cell %', cell_id; + RETURN; + END IF; + END IF; + END IF; + +-- convert dms latitude to dd latitude + SELECT + echobase_latitudetext_to_dd(latitude) + INTO latitudeNumber; + + IF latitude IS NOT NULL AND latitudeNumber IS NULL + THEN + latitudeNumber := latitude :: REAL; + END IF; + +-- convert dms longitude to dd longitude + SELECT + echobase_longitudetext_to_dd(longitude) + INTO longitudeNumber; + + IF longitude IS NOT NULL AND longitudeNumber IS NULL + THEN + longitudeNumber := longitude :: REAL; + END IF; + + SELECT + 'SRID=4326;POINT(' || longitudeNumber || ' ' || latitudeNumber || ')' + INTO spatialText; + RAISE DEBUG 'spatial data % for cell %', spatialText, cell_id; + + 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) + RETURNS VOID AS $$ +DECLARE + latitude VARCHAR; + longitude VARCHAR; + depth VARCHAR; + spatialText VARCHAR; + latitudeNumber REAL; + longitudeNumber REAL; +BEGIN + RAISE DEBUG 'Treat elementary cell % ', cell_id; +-- try start elementary + SELECT + d.datavalue + INTO latitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'LatitudeStart'; + IF FOUND + THEN +-- this is a start elementary + RAISE DEBUG 'Treat elementary Start cell % ', cell_id; +-- get longitude + SELECT + d.datavalue + INTO longitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'LongitudeStart'; +-- get depth (try first surface one) + SELECT + d.datavalue + INTO depth + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'DepthRefSurfaceStart'; + IF NOT FOUND + THEN +-- try then bottom + SELECT + d.datavalue + INTO depth + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'DepthRefBottomStart'; + END IF; + ELSE +-- try bary elementary + SELECT + d.datavalue + INTO latitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'LatitudeBary'; + IF FOUND + THEN +-- this is a bary elementary + RAISE DEBUG 'Treat elementary Bary cell %', cell_id; + SELECT + d.datavalue + INTO longitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'LongitudeBary'; + SELECT + d.datavalue + INTO depth + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'DepthRefSurfaceBary'; + IF NOT FOUND + THEN +-- use depth bottom + SELECT + d.datavalue + INTO depth + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'DepthRefBottomBary'; + END IF; + ELSE +-- try end elementary + SELECT + d.datavalue + INTO latitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'LatitudeEnd'; + IF FOUND + THEN +-- this is a end elementary + RAISE DEBUG 'Treat elementary End cell %', cell_id; + SELECT + d.datavalue + INTO longitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'LongitudeEnd'; + SELECT + d.datavalue + INTO depth + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'DepthRefSurfaceEnd'; + IF NOT FOUND + THEN +-- use depth bottom + SELECT + d.datavalue + INTO depth + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'DepthRefBottomEnd'; + END IF; + ELSE +-- no spatial data + RAISE DEBUG 'Could not find spatial data for cell %', cell_id; + RETURN; + END IF; + END IF; + END IF; + +-- convert dms latitude to dd latitude + SELECT + echobase_latitudetext_to_dd(latitude) + INTO latitudeNumber; + + IF latitude IS NOT NULL AND latitudeNumber IS NULL + THEN + latitudeNumber := latitude :: REAL; + END IF; + +-- convert dms longitude to dd longitude + SELECT + echobase_longitudetext_to_dd(longitude) + INTO longitudeNumber; + + IF longitude IS NOT NULL AND longitudeNumber IS NULL + THEN + longitudeNumber := longitude :: REAL; + END IF; + + SELECT + 'SRID=4326;POINT(' || longitudeNumber || ' ' || latitudeNumber || ' ' || + depth || ')' + INTO spatialText; + RAISE DEBUG 'spatial data % for cell %', spatialText, cell_id; + + 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; + + SELECT 'SRID=4326;' || spatialText 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 + 'SRID=4326;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 GEOMETRY; + coordinate3dData GEOMETRY; + shapeData GEOMETRY; +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_GeomFromEWKT(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_GeomFromEWKT(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_GeomFromEWKT(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; + +-------------------------------------------------------------------------------- +-- Table spatial pour les operations ------------------------------------------- +-------------------------------------------------------------------------------- + +DROP TABLE IF EXISTS echobase_operation_spatial; +CREATE TABLE echobase_operation_spatial ( + voyageid VARCHAR(256) NOT NULL, + voyagename VARCHAR(256), + transitid VARCHAR(256), + transitname VARCHAR(256), + transectid VARCHAR(256), + transectname VARCHAR(256), + operationid VARCHAR(256), + operationname VARCHAR(256), + lastUpdateDate TIMESTAMP NOT NULL, + FOREIGN KEY (operationid) REFERENCES operation (topiaid), + FOREIGN KEY (transectid) REFERENCES transect (topiaid), + FOREIGN KEY (transitid) REFERENCES transit (topiaid), + FOREIGN KEY (voyageid) REFERENCES voyage (topiaid) +); + +SELECT AddGeometryColumn('echobase_operation_spatial', 'coordinate', 4326, 'POINT',2 ); + +CREATE INDEX echobase_operation_spatial_coordinate_gix ON echobase_operation_spatial USING GIST (coordinate); + +-- pour stoquer les traitements spatiaux a effectuer +DROP TABLE IF EXISTS echobase_operation_spatial_temp; +CREATE TABLE echobase_operation_spatial_temp ( + operationid VARCHAR(256) PRIMARY KEY, + FOREIGN KEY (operationid) REFERENCES operation (topiaid) +); + +-- +-- Trigger qui met à jour la table des traitements spatiaux à faire +-- + +CREATE OR REPLACE FUNCTION echobase_fill_operation_spatial_temp_table() + RETURNS TRIGGER AS $$ +DECLARE + operation_id VARCHAR; + result RECORD; +BEGIN + + IF (TG_OP = 'DELETE') + THEN + result = OLD; + ELSE + result = NEW; + END IF; + + operation_id = result.topiaid; + + IF (SELECT count(*) FROM echobase_operation_spatial_temp c + WHERE c.operationid = operation_id) > 0 + THEN +-- operation est deja a traitee + RETURN result; + END IF; +--- Ajout de l'operation dans la table des traitements à effectuer + RAISE DEBUG 'Add operation % to echobase_operation_spatial_temp', operation_id; + INSERT INTO echobase_operation_spatial_temp (operationid) VALUES (operation_id); + RETURN result; +END +$$ +LANGUAGE 'plpgsql'; + +DROP TRIGGER IF EXISTS echobase_fill_operation_spatial_work_table_trigger ON operation; +CREATE TRIGGER echobase_fill_operation_spatial_work_table_trigger +AFTER INSERT OR UPDATE ON operation +FOR EACH ROW WHEN (NEW.topiaid IS NOT + NULL) EXECUTE PROCEDURE echobase_fill_operation_spatial_temp_table(); + +-- +-- Trigger qui met à jour la table des traitements spatiaux à faire lors de la +-- suppression d'une operation +-- + +CREATE OR REPLACE FUNCTION echobase_delete_operation() + RETURNS TRIGGER AS $$ +BEGIN + + RAISE DEBUG 'Delete operation % , delete cascade in echobase_operation_spatial_table', OLD.topiaid; + + DELETE FROM echobase_operation_spatial WHERE operationid = OLD.topiaid; + + RETURN OLD; +END +$$ +LANGUAGE 'plpgsql'; + +DROP TRIGGER IF EXISTS echobase_delete_operation ON operation; + +CREATE TRIGGER echobase_delete_operation +BEFORE DELETE ON operation +FOR EACH ROW EXECUTE PROCEDURE echobase_delete_operation(); + +-- +-- Mettre a jour toutes les données spatiales d'operation +-- + +CREATE OR REPLACE FUNCTION echobase_compute_all_operation_spatial_data() + RETURNS VOID AS $$ +DECLARE operation_id VARCHAR; +BEGIN + DELETE FROM echobase_operation_spatial; + + FOR operation_id IN SELECT topiaid FROM operation LOOP + PERFORM echobase_fill_operation_spatial_row(operation_id); + END LOOP; +END +$$ +LANGUAGE plpgsql; + +-- +-- Mettre a jour la table echobase_cell_spatial depuis echobase_operation_spatial_temp +-- + +CREATE OR REPLACE FUNCTION echobase_fill_operation_spatial_table() + RETURNS VOID AS $$ +DECLARE + operationRow RECORD; +BEGIN + FOR operationRow IN SELECT * FROM echobase_operation_spatial_temp LOOP + + PERFORM echobase_fill_operation_spatial_row(operationRow.operationid); + END LOOP; + + DELETE FROM echobase_operation_spatial_temp; + +END +$$ +LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION echobase_fill_operation_spatial_row(operation_id VARCHAR) + RETURNS VOID AS $$ + DECLARE + spatialText VARCHAR; + latitudeNumber REAL; + longitudeNumber REAL; +BEGIN + RAISE NOTICE 'Treat spatial operation % ...', operation_id; + SELECT o.midHaulLatitude, o.midHaulLongitude + INTO latitudeNumber, longitudeNumber FROM operation o + WHERE o.topiaid = operation_id; + + SELECT + 'SRID=4326;POINT(' || longitudeNumber || ' ' || latitudeNumber || ')' + INTO spatialText; + RAISE DEBUG 'spatial data % for operation %', spatialText, operation_id; + + PERFORM echobase_create_echobase_operation_spatial_row(operation_id, + spatialText); +END +$$ +LANGUAGE plpgsql; + +-- procedure pour creer (ou mettre à jour) une ligne dans echobase_cell_spatial + +CREATE OR REPLACE FUNCTION echobase_create_echobase_operation_spatial_row( + operation_id VARCHAR, + coordinateText VARCHAR) + RETURNS VOID AS $$ +DECLARE + operationSpatialRow RECORD; + voyageId VARCHAR; + voyageName VARCHAR; + transitId VARCHAR; + transitName VARCHAR; + transectId VARCHAR; + transectName VARCHAR; + operationId VARCHAR; + operationName VARCHAR; + operationRow RECORD; + coordinateData GEOMETRY; +BEGIN + IF coordinateText IS NULL + THEN + RAISE DEBUG 'Could not find spatial data for operation %', operation_id; + RETURN; + END IF; + BEGIN + coordinateData = ST_GeomFromEWKT(coordinateText); + EXCEPTION WHEN internal_error + THEN + RAISE LOG 'Could not create coordinate operation spatial data %', coordinateText; + RETURN; + END; + + SELECT * INTO operationSpatialRow FROM echobase_operation_spatial os + WHERE os.operationid = operation_id; + IF NOT FOUND + THEN +-- create row + RAISE DEBUG 'Will create spatial operation %', operation_id; +-- get transect infos + SELECT + t.topiaid, + t.vessel + INTO transectId, transitName + FROM transect t, operation op + WHERE op.topiaid = operation_id AND t.topiaid = op.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; + + INSERT INTO echobase_operation_spatial (lastUpdateDate, voyageid, voyagename, transitid, transitname, transectid, transectname, operationid, operationname, coordinate) + VALUES (now(), voyageId, voyageName, transitId, transitName, transectId, transectName, operation_id, operationName, coordinateData); + ELSE +-- update row + RAISE DEBUG 'Will update spatial operation % ', operation_id; + UPDATE echobase_operation_spatial + SET coordinate = coordinateData + WHERE operationid = operation_id; + END IF; +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; + +-------------------------------------------------------------------------------- +-------------------------------------------------------------------------------- +-------------------------------------------------------------------------------- + +-- +-- Mettre a jour toutes les données spatiales +-- + +CREATE OR REPLACE FUNCTION echobase_compute_all_spatial_data() + RETURNS VOID AS $$ +DECLARE cell_id VARCHAR; +BEGIN + EXECUTE echobase_compute_all_operation_spatial_data(); + EXECUTE echobase_compute_all_cell_spatial_data(); +END +$$ +LANGUAGE plpgsql; + + +-- Compute all data +SELECT echobase_compute_all_cell_spatial_data(); +SELECT echobase_compute_all_operation_spatial_data(); \ No newline at end of file Modified: trunk/echobase-services/src/test/java/fr/ifremer/echobase/services/FakeEchoBaseServiceContext.java =================================================================== --- trunk/echobase-services/src/test/java/fr/ifremer/echobase/services/FakeEchoBaseServiceContext.java 2013-10-31 18:16:19 UTC (rev 871) +++ trunk/echobase-services/src/test/java/fr/ifremer/echobase/services/FakeEchoBaseServiceContext.java 2013-10-31 21:59:27 UTC (rev 872) @@ -29,7 +29,6 @@ import fr.ifremer.echobase.config.EchoBaseConfigurationOption; import fr.ifremer.echobase.entities.TopiaEchoBaseInternalPersistenceContext; import fr.ifremer.echobase.entities.TopiaEchoBasePersistenceContext; -import fr.ifremer.echobase.entities.spatial.SpatialDataCache; import fr.ifremer.echobase.persistence.EchoBaseDbMeta; import fr.ifremer.echobase.persistence.EchobaseTopiaContexts; import fr.ifremer.echobase.persistence.JdbcConfiguration; @@ -192,10 +191,10 @@ return result; } - @Override - public SpatialDataCache getSpatialDataCache() { - return null; - } +// @Override +// public SpatialDataCache getSpatialDataCache() { +// return null; +// } @Override public Locale getLocale() { Modified: trunk/echobase-tools/pom.xml =================================================================== --- trunk/echobase-tools/pom.xml 2013-10-31 18:16:19 UTC (rev 871) +++ trunk/echobase-tools/pom.xml 2013-10-31 21:59:27 UTC (rev 872) @@ -92,7 +92,7 @@ </dependency> <dependency> - <groupId>postgresql</groupId> + <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <scope>runtime</scope> </dependency> Modified: trunk/echobase-tools/src/test/java/fr/ifremer/echobase/tools/AbstractToolTest.java =================================================================== --- trunk/echobase-tools/src/test/java/fr/ifremer/echobase/tools/AbstractToolTest.java 2013-10-31 18:16:19 UTC (rev 871) +++ trunk/echobase-tools/src/test/java/fr/ifremer/echobase/tools/AbstractToolTest.java 2013-10-31 21:59:27 UTC (rev 872) @@ -88,10 +88,10 @@ serviceContext = DefaultEchoBaseServiceContext.newContext( Locale.getDefault(), configuration, - EchoBaseDbMeta.newDbMeta(), - null - - ); + EchoBaseDbMeta.newDbMeta()); +// , +// null +// ); serviceContext.setEchoBasePersistenceContext(new TopiaEchoBasePersistenceContext(topiaContext.beginTransaction())); } Modified: trunk/echobase-ui/pom.xml =================================================================== --- trunk/echobase-ui/pom.xml 2013-10-31 18:16:19 UTC (rev 871) +++ trunk/echobase-ui/pom.xml 2013-10-31 21:59:27 UTC (rev 872) @@ -131,8 +131,8 @@ <jvmArgs>-Xms512m -Xmx2048m</jvmArgs> <systemProperties> <systemProperty> - <name>echobase.log.dir</name> - <value>${project.build.directory}/echobase.log</value> + <name>echobase.data.dir</name> + <value>${project.build.directory}/echobase-data</value> </systemProperty> </systemProperties> </configuration> @@ -146,9 +146,9 @@ <uriEncoding>UTF-8</uriEncoding> <!--<fork>true</fork>--> <systemProperties> - <echobase.log.dir> - ${project.build.directory}/echobase.log - </echobase.log.dir> + <echobase.data.dir> + ${project.build.directory}/echobase-data + </echobase.data.dir> <JAVA_OPTS>-Xms512m -Xmx2048m -XX:MaxPermSize=256m</JAVA_OPTS> </systemProperties> </configuration> @@ -161,9 +161,9 @@ <path>/echobase</path> <uriEncoding>UTF-8</uriEncoding> <systemProperties> - <echobase.log.dir> - ${project.build.directory}/echobase.log - </echobase.log.dir> + <echobase.data.dir> + ${project.build.directory}/echobase-data + </echobase.data.dir> <JAVA_OPTS>-Xms512m -Xmx2048m -XX:MaxPermSize=256m</JAVA_OPTS> </systemProperties> </configuration> @@ -267,7 +267,7 @@ </dependency> <dependency> - <groupId>postgresql</groupId> + <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <scope>runtime</scope> </dependency> @@ -539,7 +539,7 @@ </build> </profile> - <profile> + <!--profile> <id>add-log-to-jetty</id> <activation> <file> @@ -559,8 +559,8 @@ <stopPort>1269</stopPort> <systemProperties> <systemProperty> - <name>echobase.log.dir</name> - <value>/var/local/echobase/logs</value> + <name>echobase.data.dir</name> + <value>/var/local/echobase</value> </systemProperty> </systemProperties> </configuration> @@ -570,7 +570,7 @@ </build> - </profile> + </profile--> </profiles> </project> Modified: trunk/echobase-ui/src/main/assembly/dist/echobase.properties =================================================================== --- trunk/echobase-ui/src/main/assembly/dist/echobase.properties 2013-10-31 18:16:19 UTC (rev 871) +++ trunk/echobase-ui/src/main/assembly/dist/echobase.properties 2013-10-31 21:59:27 UTC (rev 872) @@ -29,9 +29,6 @@ # Where to find data echobase.data.directory=. -# Where to find war -echobase.war.location=${echobase.data.directory}/echobase-ui-${echobase.version}-embedded.war - # To create embedded working db echobase.embedded=true Deleted: trunk/echobase-ui/src/main/assembly/dist/help.sql =================================================================== --- trunk/echobase-ui/src/main/assembly/dist/help.sql 2013-10-31 18:16:19 UTC (rev 871) +++ trunk/echobase-ui/src/main/assembly/dist/help.sql 2013-10-31 21:59:27 UTC (rev 872) @@ -1,361 +0,0 @@ ---- --- #%L --- EchoBase :: UI --- $Id$ --- $HeadURL$ --- %% --- Copyright (C) 2011 - 2013 Ifremer, Codelutin --- %% --- This program is free software: you can redistribute it and/or modify --- it under the terms of the GNU Affero General Public License as published by --- the Free Software Foundation, either version 3 of the License, or --- (at your option) any later version. --- --- This program is distributed in the hope that it will be useful, --- but WITHOUT ANY WARRANTY; without even the implied warranty of --- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the --- GNU General Public License for more details. --- --- You should have received a copy of the GNU Affero General Public License --- along with this program. If not, see <http://www.gnu.org/licenses/>. --- #L% ---- -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 - --- esdu fr.ifremer.echobase.entities.data.Cell#1348330016210#0.934653007293087 - --- 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/java/fr/ifremer/echobase/ui/EchoBaseApplicationContext.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/EchoBaseApplicationContext.java 2013-10-31 18:16:19 UTC (rev 871) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/EchoBaseApplicationContext.java 2013-10-31 21:59:27 UTC (rev 872) @@ -33,7 +33,6 @@ import fr.ifremer.echobase.entities.EchoBaseUser; import fr.ifremer.echobase.entities.TopiaEchoBaseInternalPersistenceContext; import fr.ifremer.echobase.entities.TopiaEchoBasePersistenceContext; -import fr.ifremer.echobase.entities.spatial.SpatialDataCache; import fr.ifremer.echobase.persistence.EchoBaseDbMeta; import fr.ifremer.echobase.persistence.EchoBaseEntityHelper; import fr.ifremer.echobase.persistence.EchobaseTopiaContexts; @@ -131,16 +130,16 @@ */ protected boolean defaultUsersCreated; - /** - * A simple cache of spatial data. - * - * @since 2.2 - */ - protected final SpatialDataCache spatialDataCache; +// /** +// * A simple cache of spatial data. +// * +// * @since 2.2 +// */ +// protected final SpatialDataCache spatialDataCache; public EchoBaseApplicationContext() { - spatialDataCache = new SpatialDataCache(); +// spatialDataCache = new SpatialDataCache(); } public Set<EchoBaseSession> getEchoBaseSessions() { @@ -213,8 +212,9 @@ DefaultEchoBaseServiceContext.newContext( Locale.getDefault(), getConfiguration(), - getDbMeta(), - getSpatialDataCache()); + getDbMeta()); +// , +// getSpatialDataCache()); // init database (and create minimal admin user if required) initInternalDatabase(serviceContext); @@ -243,9 +243,9 @@ return defaultUsersCreated; } - public SpatialDataCache getSpatialDataCache() { - return spatialDataCache; - } +// public SpatialDataCache getSpatialDataCache() { +// return spatialDataCache; +// } public EchoBaseServiceContext newServiceContext(Locale locale, TopiaContext topiaInternalContext, @@ -255,8 +255,9 @@ DefaultEchoBaseServiceContext.newContext( locale, configuration, - dbMeta, - spatialDataCache); + dbMeta); +// , +// spatialDataCache); TopiaEchoBaseInternalPersistenceContext internalPersistenceContext = new TopiaEchoBaseInternalPersistenceContext(topiaInternalContext); @@ -275,7 +276,7 @@ public void close() { try { - spatialDataCache.clear(); +// spatialDataCache.clear(); if (internalRootContext != null) { // release internal db @@ -454,16 +455,18 @@ EmbeddedApplicationService.copyEmbeddedBinaryFile(pilotFileName, libDirectory); } - // copy embedded war - File warLocation = configuration.getWarLocation(); - File embeddedWarDirectory = warLocation.getParentFile(); - String embeddedWarFileName = warLocation.getName(); + if (!getConfiguration().isEmbedded()) { + // copy embedded war + File warLocation = configuration.getWarLocation(); + File embeddedWarDirectory = warLocation.getParentFile(); + String embeddedWarFileName = warLocation.getName(); - // copy it from class-path - if (log.isInfoEnabled()) { - log.info("Copy embedded war " + embeddedWarFileName + - " to directory " + embeddedWarDirectory); + // copy it from class-path + if (log.isInfoEnabled()) { + log.info("Copy embedded war " + embeddedWarFileName + + " to directory " + embeddedWarDirectory); + } + EmbeddedApplicationService.copyEmbeddedBinaryFile(embeddedWarFileName, embeddedWarDirectory); } - EmbeddedApplicationService.copyEmbeddedBinaryFile(embeddedWarFileName, embeddedWarDirectory); } } Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/EchoBaseInternalDbTransactionFilter.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/EchoBaseInternalDbTransactionFilter.java 2013-10-31 18:16:19 UTC (rev 871) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/EchoBaseInternalDbTransactionFilter.java 2013-10-31 21:59:27 UTC (rev 872) @@ -77,17 +77,20 @@ return transaction; } -// /** -// * Hook method to close the topia transaction of the request at the end of -// * the request when all filter has been consumed. -// * -// * @param transaction the transaction to close (can be null if transaction -// * was not required while the current request) -// * @since 1.0 -// */ + /** + * Hook method to close the topia transaction of the request at the end of + * the request when all filter has been consumed. + * + * @param transaction the transaction to close (can be null if transaction + * was not required while the current request) + * @since 1.0 + */ // protected void onCloseTransaction(TopiaContext transaction) { // //FIXME Check if this necessary any longer ? -// EchoBaseEntityHelper.closeConnection(transaction); +// if (transaction!=null) { +// transaction.rollbackTransaction(); +// } +//// EchoBaseEntityHelper.closeConnection(transaction); // } // public static void closeConnection(TopiaContext transaction) { Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/spatial/ShowSpatialData.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/spatial/ShowSpatialData.java 2013-10-31 18:16:19 UTC (rev 871) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/spatial/ShowSpatialData.java 2013-10-31 21:59:27 UTC (rev 872) @@ -49,6 +49,8 @@ return model; } + protected boolean canAddSpatial; + /** Universe of existing voyages. */ protected Map<String, String> voyages; @@ -63,6 +65,10 @@ return cellTypes; } + public boolean isCanAddSpatial() { + return canAddSpatial; + } + @Override public void prepare() throws Exception { voyages = workingDbPersistenceService.loadSortAndDecorate(Voyage.class); @@ -70,6 +76,10 @@ model.setJdbcUrl(getServiceContext().getWorkgingDbUrl()); model.setWithSpatial(spatialService.isSpatialAware()); + + // can add postgis if working db is postgresql and has still no echobase + // spatial db + canAddSpatial = !model.isWithSpatial() && spatialService.canUpdatePostgis(); } @Override Added: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/AddSpatial.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/AddSpatial.java (rev 0) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/AddSpatial.java 2013-10-31 21:59:27 UTC (rev 872) @@ -0,0 +1,58 @@ +package fr.ifremer.echobase.ui.actions.workingDb; + +/* + * #%L + * EchoBase :: UI + * $Id$ + * $HeadURL$ + * %% + * Copyright (C) 2011 - 2013 Ifremer, Codelutin + * %% + * This program is free software: you can redistribute it and/or modify + * it under the terms of the GNU Affero General Public License as published by + * the Free Software Foundation, either version 3 of the License, or + * (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU General Public License for more details. + * + * You should have received a copy of the GNU Affero General Public License + * along with this program. If not, see <http://www.gnu.org/licenses/>. + * #L% + */ + +import fr.ifremer.echobase.services.service.spatial.SpatialService; + +/** + * To add echobase spatial sutff to curerent working db. + * <p/> + * Created on 10/31/13. + * + * @author Tony Chemit <chemit@codelutin.com> + * @since 2.4 + */ +public class AddSpatial extends AbstractWorkingDbAction { + + private static final long serialVersionUID = 1L; + + @Override + public String execute() throws Exception { + + spatialService.addSpatialStructure(); + + addFlashMessage(_("echobase.info.workingDbconfiguration.spatialStructureAdded")); + return SUCCESS; + } + + //------------------------------------------------------------------------// + //-- Injected objects // + //------------------------------------------------------------------------// + + protected transient SpatialService spatialService; + + public void setSpatialService(SpatialService spatialService) { + this.spatialService = spatialService; + } +} Property changes on: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/AddSpatial.java ___________________________________________________________________ Added: svn:keywords + Author Date Id Revision HeadURL Added: svn:eol-style + native Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/Information.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/Information.java 2013-10-31 18:16:19 UTC (rev 871) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/workingDb/Information.java 2013-10-31 21:59:27 UTC (rev 872) @@ -25,6 +25,7 @@ import fr.ifremer.echobase.config.EchoBaseConfiguration; import fr.ifremer.echobase.persistence.JdbcConfiguration; +import fr.ifremer.echobase.services.service.spatial.SpatialService; import fr.ifremer.echobase.ui.actions.EchoBaseActionSupport; /** @@ -41,6 +42,10 @@ protected String pilotVersion; + protected boolean canAddSpatial; + + protected boolean spatialAware; + public JdbcConfiguration getDbConfiguration() { return dbConfiguration; } @@ -49,6 +54,14 @@ return pilotVersion; } + public boolean isCanAddSpatial() { + return canAddSpatial; + } + + public boolean isSpatialAware() { + return spatialAware; + } + @Override public String execute() throws Exception { @@ -66,6 +79,21 @@ pilotVersion = dbConfiguration.getDriverType().getPilotVersion(configuration); + spatialAware = spatialService.isSpatialAware(); + + // can add postgis if working db is postgresql and has still no echobase + // spatial db + canAddSpatial = !spatialAware && spatialService.canUpdatePostgis(); return SUCCESS; } + + //------------------------------------------------------------------------// + //-- Injected objects // + //------------------------------------------------------------------------// + + protected transient SpatialService spatialService; + + public void setSpatialService(SpatialService spatialService) { + this.spatialService = spatialService; + } } Modified: trunk/echobase-ui/src/main/resources/config/struts-spatial.xml =================================================================== --- trunk/echobase-ui/src/main/resources/config/struts-spatial.xml 2013-10-31 18:16:19 UTC (rev 871) +++ trunk/echobase-ui/src/main/resources/config/struts-spatial.xml 2013-10-31 21:59:27 UTC (rev 872) @@ -47,6 +47,15 @@ </result> </action> + <!-- Add echobase spatial tables, functions and triggers to working db --> + <action name="addSpatial" + class="fr.ifremer.echobase.ui.actions.workingDb.AddSpatial"> + <result type="redirectAction"> + <param name="actionName">configure</param> + <param name="namespace">/spatial</param> + </result> + </action> + <!-- Show spatial map from the given selected data --> <action name="showSpatial" class="fr.ifremer.echobase.ui.actions.spatial.ShowSpatialData"> Modified: trunk/echobase-ui/src/main/resources/config/struts-workingDb.xml =================================================================== --- trunk/echobase-ui/src/main/resources/config/struts-workingDb.xml 2013-10-31 18:16:19 UTC (rev 871) +++ trunk/echobase-ui/src/main/resources/config/struts-workingDb.xml 2013-10-31 21:59:27 UTC (rev 872) @@ -112,6 +112,15 @@ <result type="redirectToShowList"/> </action> + <!-- Add echobase spatial tables, functions and triggers to working db --> + <action name="addSpatial" + class="fr.ifremer.echobase.ui.actions.workingDb.AddSpatial"> + <result type="redirectAction"> + <param name="actionName">information</param> + <param name="namespace">/workingDb</param> + </result> + </action> + <!-- Get db modifications --> <action name="logs" class="fr.ifremer.echobase.ui.actions.EchoBaseActionSupport"> Modified: trunk/echobase-ui/src/main/resources/i18n/echobase-ui_en_GB.properties =================================================================== --- trunk/echobase-ui/src/main/resources/i18n/echobase-ui_en_GB.properties 2013-10-31 18:16:19 UTC (rev 871) +++ trunk/echobase-ui/src/main/resources/i18n/echobase-ui_en_GB.properties 2013-10-31 21:59:27 UTC (rev 872) @@ -40,6 +40,7 @@ echobase.action.toFrench=French echobase.action.usingEnglish=English echobase.action.usingFrench=French +echobase.action.workingDbconfiguration.addSpatial= echobase.action.workingDbconfiguration.connect=Connect echobase.action.workingDbconfiguration.disconnect=Disconnect echobase.action.workingDbconfiguration.downloadDriver=Download jdbc driver @@ -66,6 +67,7 @@ echobase.common.dataProcessing=DataProcessing echobase.common.dataProcessingNotes=Data processing notes echobase.common.datum=Datum +echobase.common.db.spatialAware= echobase.common.description=Description echobase.common.digitThreshold=Digitization threshold echobase.common.distributionStatement=Distribution statement @@ -137,6 +139,7 @@ echobase.common.soundSpeedCalculationsME70=Sound speed calculation method (ME70 instrument) echobase.common.sounderConstant=Sounder constant (if relevant) echobase.common.source=Source +echobase.common.spatialAware= echobase.common.species=Species echobase.common.startEndDate=Start - End date echobase.common.startEndPort=Start - End port @@ -276,6 +279,7 @@ echobase.info.workingDbconfiguration.created=Working database configuration '%s' created echobase.info.workingDbconfiguration.deleted=Working database configuration '%s' deleted echobase.info.workingDbconfiguration.disconnected=Disconnected from working database '%s' +echobase.info.workingDbconfiguration.spatialStructureAdded= echobase.information.mission.created=Mission %s created echobase.information.one.file.required=Please provide at least one import file echobase.label.admin.user.create=User creation in process Modified: trunk/echobase-ui/src/main/resources/i18n/echobase-ui_fr_FR.properties =================================================================== --- trunk/echobase-ui/src/main/resources/i18n/echobase-ui_fr_FR.properties 2013-10-31 18:16:19 UTC (rev 871) +++ trunk/echobase-ui/src/main/resources/i18n/echobase-ui_fr_FR.properties 2013-10-31 21:59:27 UTC (rev 872) @@ -40,6 +40,7 @@ echobase.action.toFrench=En français echobase.action.usingEnglish=Version anglaise echobase.action.usingFrench=Version française +echobase.action.workingDbconfiguration.addSpatial=Spatialiser la base echobase.action.workingDbconfiguration.connect=Sélectionner une base de travail echobase.action.workingDbconfiguration.disconnect=Se déconnecter echobase.action.workingDbconfiguration.downloadDriver=Télécharger le pilote jdbc @@ -138,6 +139,7 @@ echobase.common.soundSpeedCalculationsME70=Méthode de calcul de la célérité du son (ME70) echobase.common.sounderConstant=Constante sondeur (si besoin) echobase.common.source=Source +echobase.common.spatialAware=Base spatialisée ? echobase.common.species=Espèce echobase.common.startEndDate=Date de début - fin echobase.common.startEndPort=Port de départ - arrivé @@ -277,6 +279,7 @@ echobase.info.workingDbconfiguration.created=Configuration à la base de travail '%s' créée echobase.info.workingDbconfiguration.deleted=Configuration à la base de travail '%s' supprimée echobase.info.workingDbconfiguration.disconnected=Déconnexion de la base de travail '%s' +echobase.info.workingDbconfiguration.spatialStructureAdded=La base a été spatialisée (ajout des tables echobase_cell_spatial et echobase_operation_spatial) echobase.information.mission.created=La mission %s a été créée echobase.information.one.file.required=Veuillez fournir au moins un fichiers à importer echobase.label.admin.user.create=Création d'un utilisateur Modified: trunk/echobase-ui/src/main/webapp/WEB-INF/jsp/spatial/showData.jsp =================================================================== --- trunk/echobase-ui/src/main/webapp/WEB-INF/jsp/spatial/showData.jsp 2013-10-31 18:16:19 UTC (rev 871) +++ trunk/echobase-ui/src/main/webapp/WEB-INF/jsp/spatial/showData.jsp 2013-10-31 21:59:27 UTC (rev 872) @@ -155,6 +155,20 @@ <p> <s:text name="echobase.message.no.spatial.database.support"/> </p> + + <s:if test="canAddSpatial"> + <br/> + <s:form id="createForm" namespace="/spatial"> + <ul class="toolbar floatLeft"> + + <li> + <s:submit theme="simple" action="addSpatial" + key="echobase.action.workingDbconfiguration.addSpatial"/> + </li> + + </ul> + </s:form> + </s:if> </s:else> Modified: trunk/echobase-ui/src/main/webapp/WEB-INF/jsp/workingDb/information.jsp =================================================================== --- trunk/echobase-ui/src/main/webapp/WEB-INF/jsp/workingDb/information.jsp 2013-10-31 18:16:19 UTC (rev 871) +++ trunk/echobase-ui/src/main/webapp/WEB-INF/jsp/workingDb/information.jsp 2013-10-31 21:59:27 UTC (rev 872) @@ -31,7 +31,7 @@ $(document).ready(function () { - $('label[id^="dbConfiguration"]').css({'background-color':'white', 'color':'black', 'padding-right':'2px', 'padding-left':'2px' }).click(function () { + $('label[id^="dbConfiguration"]').css({'background-color': 'white', 'color': 'black', 'padding-right': '2px', 'padding-left': '2px' }).click(function () { var el = $(this)[0]; if (/textarea/i.test(el.tagName) || (/input/i.test(el.tagName) && /text/i.test(el.type))) { el.select(); @@ -63,10 +63,14 @@ label='%{getText("echobase.common.jdbcLogin")} (*)'/> <s:label key="dbConfiguration.password" template="label_with_no_space" label='%{getText("echobase.common.jdbcPassword")} (*)'/> - <s:label key="dbConfiguration.driverType.driverClass.name" template="label_with_no_space" + <s:label key="dbConfiguration.driverType.driverClass.name" + template="label_with_no_space" label='%{getText("echobase.common.jdbcDriver")} (*)'/> <s:label key="pilotVersion" template="label_with_no_space" label='%{getText("echobase.common.pilotVersion")}'/> + + <s:checkbox key="spatialAware" disabled="true" + label='%{getText("echobase.common.spatialAware")}'/> <br/> <div class="cleanBoth help"> @@ -88,6 +92,12 @@ <s:submit theme="simple" action="disconnect" key="echobase.action.workingDbconfiguration.disconnect"/> </li> + <s:if test="canAddSpatial"> + <li> + <s:submit theme="simple" action="addSpatial" + key="echobase.action.workingDbconfiguration.addSpatial"/> + </li> + </s:if> </ul> </s:form> Property changes on: trunk/echobase-ui/src/main/webapp/js/images/layers-2x.png ___________________________________________________________________ Added: svn:keywords + Author Date Id Revision HeadURL Property changes on: trunk/echobase-ui/src/main/webapp/js/images/layers.png ___________________________________________________________________ Added: svn:keywords + Author Date Id Revision HeadURL Property changes on: trunk/echobase-ui/src/main/webapp/js/images/marker-icon-2x.png ___________________________________________________________________ Added: svn:keywords + Author Date Id Revision HeadURL Property changes on: trunk/echobase-ui/src/main/webapp/js/images/marker-icon.png ___________________________________________________________________ Added: svn:keywords + Author Date Id Revision HeadURL Property changes on: trunk/echobase-ui/src/main/webapp/js/images/marker-shadow.png ___________________________________________________________________ Added: svn:keywords + Author Date Id Revision HeadURL Modified: trunk/echobase-ui/src/main/webapp/js/leaflet-src.js =================================================================== --- trunk/echobase-ui/src/main/webapp/js/leaflet-src.js 2013-10-31 18:16:19 UTC (rev 871) +++ trunk/echobase-ui/src/main/webapp/js/leaflet-src.js 2013-10-31 21:59:27 UTC (rev 872) @@ -2,7 +2,7 @@ * #%L * EchoBase :: UI * $Id$ - * $HeadURL:$ + * $HeadURL$ * %% * Copyright (C) 2011 - 2013 Ifremer, Codelutin * %% Property changes on: trunk/echobase-ui/src/main/webapp/js/leaflet-src.js ___________________________________________________________________ Modified: svn:keywords - Author Date Id Revision + Author Date Id Revision HeadURL Modified: trunk/pom.xml =================================================================== --- trunk/pom.xml 2013-10-31 18:16:19 UTC (rev 871) +++ trunk/pom.xml 2013-10-31 21:59:27 UTC (rev 872) @@ -148,7 +148,7 @@ <struts2Version>2.3.15.3</struts2Version> <jqueryPluginVersion>3.6.0</jqueryPluginVersion> <h2Version>1.3.173</h2Version> - <postgresqlVersion>9.1-901-1.jdbc4</postgresqlVersion> + <postgresqlVersion>9.2-1003-jdbc4</postgresqlVersion> <slf4jVersion>1.7.5</slf4jVersion> <jackcessVersion>1.2.13</jackcessVersion> @@ -283,7 +283,7 @@ <!-- base postgres --> <dependency> - <groupId>postgresql</groupId> + <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <version>${postgresqlVersion}</version> </dependency> @@ -534,39 +534,6 @@ </plugins> </reporting> - <build> - - <plugins> - - <plugin> - - <artifactId>maven-antrun-plugin</artifactId> - <executions> - - <!-- on recopie le changelog.txt vers le site pour faire la release note --> - <execution> - <id>copy sql to site</id> - <phase>pre-site</phase> - <inherited>false</inherited> - <configuration> - <!-- TODO chemit 2011-02-24 Change to 'target' when using ant-run 1.6 in mavenpom4labs... --> - <tasks> - <echo message="Copy sql files to site" /> - <copy verbose="${maven.verbose}" failonerror="false" overwrite="true" todir="${project.reporting.outputDirectory}/sql"> - <fileset dir="${basedir}/src/site/"> - <include name="*.sql" /> - </fileset> - </copy> - </tasks> - </configuration> - <goals> - <goal>run</goal> - </goals> - </execution> - </executions> - </plugin> - </plugins> - </build> </profile> <!-- do not generate site if no src/site is found for module --> Deleted: trunk/src/site/postgis-structure.sql =================================================================== --- trunk/src/site/postgis-structure.sql 2013-10-31 18:16:19 UTC (rev 871) +++ trunk/src/site/postgis-structure.sql 2013-10-31 21:59:27 UTC (rev 872) @@ -1,1169 +0,0 @@ ---- --- #%L --- EchoBase :: UI --- $Id$ --- $HeadURL$ --- %% --- Copyright (C) 2011 - 2013 Ifremer, Codelutin --- %% --- This program is free software: you can redistribute it and/or modify --- it under the terms of the GNU Affero General Public License as published by --- the Free Software Foundation, either version 3 of the License, or --- (at your option) any later version. --- --- This program is distributed in the hope that it will be useful, --- but WITHOUT ANY WARRANTY; without even the implied warranty of --- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the --- GNU General Public License for more details. --- --- You should have received a copy of the GNU Affero General Public License --- along with this program. If not, see <http://www.gnu.org/licenses/>. --- #L% ---- -CREATE EXTENSION postgis; - --------------------------------------------------------------------------------- --- Table spatial pour les cellules --------------------------------------------- --------------------------------------------------------------------------------- - -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), - transectid VARCHAR(256), - transectname VARCHAR(256), - dataAcquisitionid VARCHAR(256), - dataAcquisitionname VARCHAR(256), - dataProcessingid VARCHAR(256), - dataProcessingname 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, - 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) -); - -SELECT AddGeometryColumn('echobase_cell_spatial', 'coordinate', 4326, 'POINT',2 ); -SELECT AddGeometryColumn('echobase_cell_spatial', 'coordinate3d', 4326, 'POINT',3); -SELECT AddGeometryColumn('echobase_cell_spatial', 'shape', 4326, 'POLYGON',2 ); - -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); - --- pour stoquer les traitements spatiaux a effectuer -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 --- - -DROP FUNCTION echobase_fill_spatial_temp_table(); -DROP TRIGGER IF EXISTS echobase_fill_spatial_work_table_trigger ON data; - -CREATE OR REPLACE FUNCTION echobase_fill_cell_spatial_temp_table() - RETURNS TRIGGER AS $$ -DECLARE - 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 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 = data_type_id; - - CASE cellType - WHEN 'ESDU' - THEN --- Cell of type Esdu - CASE dataType - WHEN 'LatitudeStart', 'LatitudeBary', 'LatitudeEnd', 'LongitudeStart', 'LongitudeBary', 'LongitudeEnd' - THEN - doInsert = TRUE; - ELSE - doInsert = FALSE; - END CASE; - WHEN 'ELEMENTARY' - THEN --- Cell of type Elementary - CASE dataType - WHEN 'LatitudeStart', 'LatitudeBary', 'LatitudeEnd', 'LongitudeStart', 'LongitudeBary', 'LongitudeEnd', 'DepthRefSurfaceStart', 'DepthRefSurfaceEnd', 'DepthRefBottomStart', 'DepthRefBottomEnd' - THEN - doInsert = TRUE; - ELSE - doInsert = FALSE; - END CASE; - WHEN 'REGION', 'REGIONSURF', 'REGIONCLAS' - THEN --- Cell of type Region - CASE dataType - WHEN 'RegionEnvCoordinates' - THEN - doInsert = TRUE; - cellType = 'REGION'; - ELSE - doInsert = FALSE; - END CASE; - WHEN 'MAP' - THEN --- Cell of type Map - CASE dataType - WHEN 'GridCellLatitude', 'GridCellLongitude', 'GridLatitudeLag', 'GridLongitudeLag' - THEN - doInsert = TRUE; - ELSE - doInsert = FALSE; - END CASE; - END CASE; - - IF doInsert = TRUE - THEN ---- Ajout de la cellule dans la table des traitements à effectuer - RAISE DEBUG '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 result; -END -$$ -LANGUAGE 'plpgsql'; - -DROP TRIGGER IF EXISTS echobase_fill_cell_spatial_work_table_trigger ON data; -CREATE TRIGGER echobase_fill_cell_spatial_work_table_trigger -AFTER INSERT OR UPDATE ON data -FOR EACH ROW WHEN (NEW.cell IS NOT - NULL) EXECUTE PROCEDURE echobase_fill_cell_spatial_temp_table(); - --- --- Trigger qui met à jour la table des traitements spatiaux à faire lors d --- 'une suppression de cellule --- - -CREATE OR REPLACE FUNCTION echobase_delete_cell() - RETURNS TRIGGER AS $$ -BEGIN - - RAISE DEBUG 'Delete cell % , delete cascade in echobase_cell_spatial_table', OLD.topiaid; - - 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(); - --- --- Mettre a jour toutes les données spatiales de cellule --- - -CREATE OR REPLACE FUNCTION echobase_compute_all_cell_spatial_data() - RETURNS VOID AS $$ -DECLARE cell_id VARCHAR; -BEGIN - DELETE FROM echobase_cell_spatial; - - FOR cell_id IN SELECT topiaid FROM cell LOOP - PERFORM echobase_fill_cell_spatial_row(cell_id); - END LOOP; -END -$$ -LANGUAGE plpgsql; - --- --- 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; -BEGIN - FOR cellRow IN SELECT - * - FROM echobase_cell_spatial_temp LOOP - - PERFORM echobase_fill_cell_spatial_row(cellRow.cellid, cellRow.celltype); - - END LOOP; - - DELETE FROM echobase_cell_spatial_temp; - -END -$$ -LANGUAGE plpgsql; - -CREATE OR REPLACE FUNCTION echobase_fill_cell_spatial_row(cell_id VARCHAR) - RETURNS VOID AS $$ - DECLARE cell_type VARCHAR; -BEGIN - - cell_type = echobase_get_cell_type(cell_id); - - RAISE DEBUG '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; - -CREATE OR REPLACE FUNCTION echobase_fill_esdu_cell_spatial_table( - cell_id VARCHAR) - RETURNS VOID AS $$ -DECLARE - latitude VARCHAR; - longitude VARCHAR; - depth RECORD; - spatialText VARCHAR; - latitudeNumber REAL; - longitudeNumber REAL; -BEGIN - RAISE DEBUG 'Treat esdu cell % ', cell_id; --- test if start / bary / end event - SELECT - d.datavalue - INTO latitude - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'LatitudeStart'; - IF FOUND - THEN --- start data - RAISE DEBUG 'Treat esdu Start cell % ', cell_id; - SELECT - d.datavalue - INTO longitude - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'LongitudeStart'; - ELSE - SELECT - d.datavalue - INTO latitude - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'LatitudeBary'; - IF FOUND - THEN --- bary data - RAISE DEBUG 'Treat esdu Bary cell %', cell_id; - SELECT - d.datavalue - INTO longitude - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'LongitudeBary'; - ELSE - SELECT - d.datavalue - INTO latitude - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'LatitudeEnd'; - IF FOUND - THEN --- end data - RAISE DEBUG 'Treat End cell %', cell_id; - SELECT - d.datavalue - INTO longitude - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'LongitudeEnd'; - ELSE --- no spatial data - RAISE LOG 'Could not find spatial data for cell %', cell_id; - RETURN; - END IF; - END IF; - END IF; - --- convert dms latitude to dd latitude - SELECT - echobase_latitudetext_to_dd(latitude) - INTO latitudeNumber; - - IF latitude IS NOT NULL AND latitudeNumber IS NULL - THEN - latitudeNumber := latitude :: REAL; - END IF; - --- convert dms longitude to dd longitude - SELECT - echobase_longitudetext_to_dd(longitude) - INTO longitudeNumber; - - IF longitude IS NOT NULL AND longitudeNumber IS NULL - THEN - longitudeNumber := longitude :: REAL; - END IF; - - SELECT - 'SRID=4326;POINT(' || longitudeNumber || ' ' || latitudeNumber || ')' - INTO spatialText; - RAISE DEBUG 'spatial data % for cell %', spatialText, cell_id; - - 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) - RETURNS VOID AS $$ -DECLARE - latitude VARCHAR; - longitude VARCHAR; - depth VARCHAR; - spatialText VARCHAR; - latitudeNumber REAL; - longitudeNumber REAL; -BEGIN - RAISE DEBUG 'Treat elementary cell % ', cell_id; --- try start elementary - SELECT - d.datavalue - INTO latitude - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'LatitudeStart'; - IF FOUND - THEN --- this is a start elementary - RAISE DEBUG 'Treat elementary Start cell % ', cell_id; --- get longitude - SELECT - d.datavalue - INTO longitude - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'LongitudeStart'; --- get depth (try first surface one) - SELECT - d.datavalue - INTO depth - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'DepthRefSurfaceStart'; - IF NOT FOUND - THEN --- try then bottom - SELECT - d.datavalue - INTO depth - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'DepthRefBottomStart'; - END IF; - ELSE --- try bary elementary - SELECT - d.datavalue - INTO latitude - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'LatitudeBary'; - IF FOUND - THEN --- this is a bary elementary - RAISE DEBUG 'Treat elementary Bary cell %', cell_id; - SELECT - d.datavalue - INTO longitude - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'LongitudeBary'; - SELECT - d.datavalue - INTO depth - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'DepthRefSurfaceBary'; - IF NOT FOUND - THEN --- use depth bottom - SELECT - d.datavalue - INTO depth - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'DepthRefBottomBary'; - END IF; - ELSE --- try end elementary - SELECT - d.datavalue - INTO latitude - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'LatitudeEnd'; - IF FOUND - THEN --- this is a end elementary - RAISE DEBUG 'Treat elementary End cell %', cell_id; - SELECT - d.datavalue - INTO longitude - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'LongitudeEnd'; - SELECT - d.datavalue - INTO depth - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'DepthRefSurfaceEnd'; - IF NOT FOUND - THEN --- use depth bottom - SELECT - d.datavalue - INTO depth - FROM data d LEFT OUTER JOIN datametadata AS dm - ON d.datametadata = dm.topiaid - WHERE d.cell = cell_id AND dm.name = 'DepthRefBottomEnd'; - END IF; - ELSE --- no spatial data - RAISE DEBUG 'Could not find spatial data for cell %', cell_id; - RETURN; - END IF; - END IF; - END IF; - --- convert dms latitude to dd latitude - SELECT - echobase_latitudetext_to_dd(latitude) - INTO latitudeNumber; - - IF latitude IS NOT NULL AND latitudeNumber IS NULL - THEN - latitudeNumber := latitude :: REAL; - END IF; - --- convert dms longitude to dd longitude - SELECT - echobase_longitudetext_to_dd(longitude) - INTO longitudeNumber; - - IF longitude IS NOT NULL AND longitudeNumber IS NULL - THEN - longitudeNumber := longitude :: REAL; - END IF; - - SELECT - 'SRID=4326;POINT(' || longitudeNumber || ' ' || latitudeNumber || ' ' || - depth || ')' - INTO spatialText; - RAISE DEBUG 'spatial data % for cell %', spatialText, cell_id; - - 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; - - SELECT 'SRID=4326;' || spatialText 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 - 'SRID=4326;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 GEOMETRY; - coordinate3dData GEOMETRY; - shapeData GEOMETRY; -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_GeomFromEWKT(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_GeomFromEWKT(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_GeomFromEWKT(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; - --------------------------------------------------------------------------------- --- Table spatial pour les operations ------------------------------------------- --------------------------------------------------------------------------------- - -DROP TABLE IF EXISTS echobase_operation_spatial; -CREATE TABLE echobase_operation_spatial ( - voyageid VARCHAR(256) NOT NULL, - voyagename VARCHAR(256), - transitid VARCHAR(256), - transitname VARCHAR(256), - transectid VARCHAR(256), - transectname VARCHAR(256), - operationid VARCHAR(256), - operationname VARCHAR(256), - lastUpdateDate TIMESTAMP NOT NULL, - FOREIGN KEY (operationid) REFERENCES operation (topiaid), - FOREIGN KEY (transectid) REFERENCES transect (topiaid), - FOREIGN KEY (transitid) REFERENCES transit (topiaid), - FOREIGN KEY (voyageid) REFERENCES voyage (topiaid) -); - -SELECT AddGeometryColumn('echobase_operation_spatial', 'coordinate', 4326, 'POINT',2 ); - -CREATE INDEX echobase_operation_spatial_coordinate_gix ON echobase_operation_spatial USING GIST (coordinate); - --- pour stoquer les traitements spatiaux a effectuer -DROP TABLE IF EXISTS echobase_operation_spatial_temp; -CREATE TABLE echobase_operation_spatial_temp ( - operationid VARCHAR(256) PRIMARY KEY, - FOREIGN KEY (operationid) REFERENCES operation (topiaid) -); - --- --- Trigger qui met à jour la table des traitements spatiaux à faire --- - -CREATE OR REPLACE FUNCTION echobase_fill_operation_spatial_temp_table() - RETURNS TRIGGER AS $$ -DECLARE - operation_id VARCHAR; - doInsert BOOLEAN; - result RECORD; -BEGIN - - IF (TG_OP = 'DELETE') - THEN - result = OLD; - ELSE - result = NEW; - END IF; - - operation_id = result.topiaid; - doInsert = FALSE; - - IF (SELECT count(*) FROM echobase_operation_spatial_temp c - WHERE c.operationid = operation_id) > 0 - THEN --- operation est deja a traitee - RETURN result; - END IF; ---- Ajout de l'operation dans la table des traitements à effectuer - RAISE DEBUG 'Add operation % to echobase_operation_spatial_temp', operation_id; - INSERT INTO echobase_operation_spatial_temp (operationid) VALUES (operation_id); - RETURN result; -END -$$ -LANGUAGE 'plpgsql'; - -DROP TRIGGER IF EXISTS echobase_fill_operation_spatial_work_table_trigger ON operation; -CREATE TRIGGER echobase_fill_operation_spatial_work_table_trigger -AFTER INSERT OR UPDATE ON operation -FOR EACH ROW WHEN (NEW.topiaid IS NOT - NULL) EXECUTE PROCEDURE echobase_fill_operation_spatial_temp_table(); - --- --- Trigger qui met à jour la table des traitements spatiaux à faire lors de la --- suppression d'une operation --- - -CREATE OR REPLACE FUNCTION echobase_delete_operation() - RETURNS TRIGGER AS $$ -BEGIN - - RAISE DEBUG 'Delete operation % , delete cascade in echobase_operation_spatial_table', OLD.topiaid; - - DELETE FROM echobase_operation_spatial WHERE operationid = OLD.topiaid; - - RETURN OLD; -END -$$ -LANGUAGE 'plpgsql'; - -DROP TRIGGER IF EXISTS echobase_delete_operation ON operation; - -CREATE TRIGGER echobase_delete_operation -BEFORE DELETE ON operation -FOR EACH ROW EXECUTE PROCEDURE echobase_delete_operation(); - --- --- Mettre a jour toutes les données spatiales d'operation --- - -CREATE OR REPLACE FUNCTION echobase_compute_all_operation_spatial_data() - RETURNS VOID AS $$ -DECLARE operation_id VARCHAR; -BEGIN - DELETE FROM echobase_operation_spatial; - - FOR operation_id IN SELECT topiaid FROM operation LOOP - PERFORM echobase_fill_operation_spatial_row(operation_id); - END LOOP; -END -$$ -LANGUAGE plpgsql; - --- --- Mettre a jour la table echobase_cell_spatial depuis echobase_operation_spatial_temp --- - -CREATE OR REPLACE FUNCTION echobase_fill_operation_spatial_table() - RETURNS VOID AS $$ -DECLARE - operationRow RECORD; -BEGIN - FOR operationRow IN SELECT * FROM echobase_operation_spatial_temp LOOP - - PERFORM echobase_fill_operation_spatial_row(operationRow.operationid); - END LOOP; - - DELETE FROM echobase_operation_spatial_temp; - -END -$$ -LANGUAGE plpgsql; - -CREATE OR REPLACE FUNCTION echobase_fill_operation_spatial_row(operation_id VARCHAR) - RETURNS VOID AS $$ - DECLARE - spatialText VARCHAR; - latitudeNumber REAL; - longitudeNumber REAL; -BEGIN - RAISE NOTICE 'Treat spatial operation % ...', operation_id; - SELECT o.midHaulLatitude, o.midHaulLongitude - INTO latitudeNumber, longitudeNumber FROM operation o - WHERE o.topiaid = operation_id; - - SELECT - 'SRID=4326;POINT(' || longitudeNumber || ' ' || latitudeNumber || ')' - INTO spatialText; - RAISE DEBUG 'spatial data % for operation %', spatialText, operation_id; - - PERFORM echobase_create_echobase_operation_spatial_row(operation_id, - spatialText); -END -$$ -LANGUAGE plpgsql; - --- procedure pour creer (ou mettre à jour) une ligne dans echobase_cell_spatial - -CREATE OR REPLACE FUNCTION echobase_create_echobase_operation_spatial_row( - operation_id VARCHAR, - coordinateText VARCHAR) - RETURNS VOID AS $$ -DECLARE - operationSpatialRow RECORD; - voyageId VARCHAR; - voyageName VARCHAR; - transitId VARCHAR; - transitName VARCHAR; - transectId VARCHAR; - transectName VARCHAR; - operationId VARCHAR; - operationName VARCHAR; - operationRow RECORD; - coordinateData GEOMETRY; -BEGIN - IF coordinateText IS NULL - THEN - RAISE DEBUG 'Could not find spatial data for operation %', operation_id; - RETURN; - END IF; - BEGIN - coordinateData = ST_GeomFromEWKT(coordinateText); - EXCEPTION WHEN internal_error - THEN - RAISE LOG 'Could not create coordinate operation spatial data %', coordinateText; - RETURN; - END; - - SELECT * INTO operationSpatialRow FROM echobase_operation_spatial os - WHERE os.operationid = operation_id; - IF NOT FOUND - THEN --- create row - RAISE DEBUG 'Will create spatial operation %', operation_id; --- get transect infos - SELECT - t.topiaid, - t.vessel - INTO transectId, transitName - FROM transect t, operation op - WHERE op.topiaid = operation_id AND t.topiaid = op.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; - - INSERT INTO echobase_operation_spatial (lastUpdateDate, voyageid, voyagename, transitid, transitname, transectid, transectname, operationid, operationname, coordinate) - VALUES (now(), voyageId, voyageName, transitId, transitName, transectId, transectName, operation_id, operationName, coordinateData); - ELSE --- update row - RAISE DEBUG 'Will update spatial operation % ', operation_id; - UPDATE echobase_operation_spatial - SET coordinate = coordinateData - WHERE operationid = operation_id; - END IF; -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; - --------------------------------------------------------------------------------- --------------------------------------------------------------------------------- --------------------------------------------------------------------------------- - --- --- Mettre a jour toutes les données spatiales --- - -CREATE OR REPLACE FUNCTION echobase_compute_all_spatial_data() - RETURNS VOID AS $$ -DECLARE cell_id VARCHAR; -BEGIN - EXECUTE echobase_compute_all_operation_spatial_data(); - EXECUTE echobase_compute_all_cell_spatial_data(); -END -$$ -LANGUAGE plpgsql; - - --- Compute all data -SELECT echobase_compute_all_cell_spatial_data(); -SELECT echobase_compute_all_operation_spatial_data(); \ No newline at end of file Modified: trunk/src/site/rst/db.rst =================================================================== --- trunk/src/site/rst/db.rst 2013-10-31 18:16:19 UTC (rev 871) +++ trunk/src/site/rst/db.rst 2013-10-31 21:59:27 UTC (rev 872) @@ -124,22 +124,16 @@ - Il faut ensuite ne pas oublier de créer une configuration de base de travail pour pouvoir accéder à cette base. -Rendre une base postgresql spatiale ------------------------------------ +Spatialiser une base postgresql +------------------------------- -Avant de rendre spatiale une base, il faut au moins vous y être connecté via -l'interface web qui va créé le schéma de la base. +Pour rendre la base spatiale, connectez-vous via l'application et cliquer sur +le bouton **Spatialiser la base**. -Pour pouvoir utiliser postgis sur la base, il faut exécuter le script sql -suivant `postgis-structure.sql`_. Si des données existent, elles seront -spatialisées (le script peut prendre un certain temps à crééer toutes les -données spatiales). +*Et voila!*. -:: +A noter que cette action est aussi disponible sur la page de visualisation des +données spatiales. - psql -h localhost -U userName dbName < postgis-structure.sql - - .. _page d'installation: ./install.html#Installation_de_la_base_de_donnes -.. _postgis-structure.sql: ./sql/postgis-structure.sql