- Echobase-commits - mailman.cloud.codelutin.com

r839 - trunk/echobase-ui/src/main/assembly/dist
by tchemit＠users.forge.codelutin.com 07 Aug '13

07 Aug '13

Author: tchemit Date: 2013-08-07 19:23:35 +0200 (Wed, 07 Aug 2013) New Revision: 839 Url: http://forge.codelutin.com/projects/echobase/repository/revisions/839 Log: add missing license header Modified: trunk/echobase-ui/src/main/assembly/dist/help.sql Modified: trunk/echobase-ui/src/main/assembly/dist/help.sql =================================================================== --- trunk/echobase-ui/src/main/assembly/dist/help.sql 2013-08-07 17:23:06 UTC (rev 838) +++ trunk/echobase-ui/src/main/assembly/dist/help.sql 2013-08-07 17:23:35 UTC (rev 839) @@ -1,3 +1,25 @@ +--- +-- #%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(

1 0

r838 - trunk/echobase-ui
by tchemit＠users.forge.codelutin.com 07 Aug '13

07 Aug '13

Author: tchemit Date: 2013-08-07 19:23:06 +0200 (Wed, 07 Aug 2013) New Revision: 838 Url: http://forge.codelutin.com/projects/echobase/repository/revisions/838 Log: use NG release post process Modified: trunk/echobase-ui/pom.xml Modified: trunk/echobase-ui/pom.xml =================================================================== --- trunk/echobase-ui/pom.xml 2013-08-07 17:22:32 UTC (rev 837) +++ trunk/echobase-ui/pom.xml 2013-08-07 17:23:06 UTC (rev 838) @@ -36,12 +36,8 @@ </deployFiles>  + <skipPostRelease>false</skipPostRelease> - <skipReleasePublishAttachments>false</skipReleasePublishAttachments> - <skipReleaseUpdateVersion>false</skipReleaseUpdateVersion> - <skipReleaseSendEmail>false</skipReleaseSendEmail> - <skipReleasePublishNews>false</skipReleasePublishNews> - <skipReleaseNextVersion>false</skipReleaseNextVersion> </properties> <build>

1 0

r837 - trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui
by tchemit＠users.forge.codelutin.com 07 Aug '13

07 Aug '13

Author: tchemit Date: 2013-08-07 19:22:32 +0200 (Wed, 07 Aug 2013) New Revision: 837 Url: http://forge.codelutin.com/projects/echobase/repository/revisions/837 Log: fix memory leak from common-logging Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/EchoBaseApplicationListener.java Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/EchoBaseApplicationListener.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/EchoBaseApplicationListener.java 2013-08-07 17:19:42 UTC (rev 836) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/EchoBaseApplicationListener.java 2013-08-07 17:22:32 UTC (rev 837) @@ -50,6 +50,7 @@ import javax.servlet.ServletContext; import javax.servlet.ServletContextEvent; import javax.servlet.ServletContextListener; +import java.beans.Introspector; import java.io.File; import java.io.IOException; import java.util.Date; @@ -180,6 +181,12 @@ applicationContext.destroyEchoBaseSession(session); } } + + // see http://wiki.apache.org/commons/Logging/FrequentlyAskedQuestions#A_memory_le… + ClassLoader contextClassLoader = Thread.currentThread().getContextClassLoader(); + LogFactory.release(contextClassLoader); + + Introspector.flushCaches(); } }

1 0

r836 - trunk
by tchemit＠users.forge.codelutin.com 07 Aug '13

07 Aug '13

Author: tchemit Date: 2013-08-07 19:19:42 +0200 (Wed, 07 Aug 2013) New Revision: 836 Url: http://forge.codelutin.com/projects/echobase/repository/revisions/836 Log: refs #3049: Updates mavenpom to 4.0 update project description Modified: trunk/pom.xml Modified: trunk/pom.xml =================================================================== --- trunk/pom.xml 2013-08-07 07:50:32 UTC (rev 835) +++ trunk/pom.xml 2013-08-07 17:19:42 UTC (rev 836) @@ -6,7 +6,7 @@ <parent> <groupId>org.nuiton</groupId> <artifactId>mavenpom4redmine</artifactId> - <version>3.9</version> + <version>4.0-SNAPSHOT</version> </parent> <groupId>fr.ifremer</groupId> @@ -21,7 +21,15 @@ </modules> <name>EchoBase</name> - <description>Projet EchoBase</description> + <description> + The French Institute for the Exploitation of the Sea (Ifremer) has designed + an open software suite for storing fisheries acoustic data and computing + acoustic indicators for survey-based ecosystem monitoring. + It comprises a postgreSQL database designed to store acoustic, navigation + and fishing data from ecosystemic surveys (EchoBase) and a suite of R + codes (EchoR) for computing fish population indicators based on Echobase + data. + </description> <inceptionYear>2011</inceptionYear> <url>http://maven-site.forge.codelutin.com/echobase</url>

1 0

[ANN] - EchoBase 2.2-SNAPSHOT released
by forge.codelutin.com Release Notification 07 Aug '13

07 Aug '13

The EchoBase team is pleased to announce the echobase-2.2-SNAPSHOT release! The French Institute for the Exploitation of the Sea (Ifremer) has designed an open software suite for storing fisheries acoustic data and computing acoustic indicators for survey-based ecosystem monitoring. It comprises a postgreSQL database designed to store acoustic, navigation and fishing data from ecosystemic surveys (EchoBase) and a suite of R codes (EchoR) for computing fish population indicators based on Echobase data. Documentation of the project can be found here: http://maven-site.forge.codelutin.com/echobase Changes ------- Changes in this version include: New features: o Utiliser le favicon d'echobase pour les application embarquée Issue: 2876. Thanks to Tony Chemit. Resolved by tchemit. o Permettre l'import séparé des voyages, transits, transects Issue: 3028. Thanks to Tony Chemit. Resolved by tchemit. o Import de cartes avec paramètres non rattachés à une espèce de poissons (physique...) Issue: 3029. Thanks to Tony Chemit. Resolved by tchemit. o Pouvoir se connecter à la base de travail avec un autre utilisateur Issue: 2245. Thanks to Tony Chemit. Resolved by tchemit. Fixed Bugs: o Paramètres manquants dans la doc d'import de données Issue: 2250. Thanks to Mathieu Doray. Resolved by tchemit. Changes: o Normalisation du nom des requètes Issue: 2875. Thanks to Tony Chemit. Resolved by tchemit. Downloads --------- For a manual installation, you can download files here: http://forge.codelutin.com/projects/echobase/files * echobase-referentiel-2.2-SNAPSHOT.echobase - http://forge.codelutin.com/attachments/download/1022 Maven artifacts --------------- Artifacts are deployed in nuiton maven repository http://maven.nuiton.org/other-releases/ Have fun! -EchoBase team

1 0

[ANN] - EchoBase 2.2-SNAPSHOT released
by forge.codelutin.com Release Notification 07 Aug '13

07 Aug '13

The EchoBase team is pleased to announce the echobase-2.2-SNAPSHOT release! The French Institute for the Exploitation of the Sea (Ifremer) has designed an open software suite for storing fisheries acoustic data and computing acoustic indicators for survey-based ecosystem monitoring. It comprises a postgreSQL database designed to store acoustic, navigation and fishing data from ecosystemic surveys (EchoBase) and a suite of R codes (EchoR) for computing fish population indicators based on Echobase data. Documentation of the project can be found here: http://maven-site.forge.codelutin.com/echobase Changes ------- Changes in this version include: New features: o Utiliser le favicon d'echobase pour les application embarquée Issue: 2876. Thanks to Tony Chemit. Resolved by tchemit. o Permettre l'import séparé des voyages, transits, transects Issue: 3028. Thanks to Tony Chemit. Resolved by tchemit. o Import de cartes avec paramètres non rattachés à une espèce de poissons (physique...) Issue: 3029. Thanks to Tony Chemit. Resolved by tchemit. o Pouvoir se connecter à la base de travail avec un autre utilisateur Issue: 2245. Thanks to Tony Chemit. Resolved by tchemit. Fixed Bugs: o Paramètres manquants dans la doc d'import de données Issue: 2250. Thanks to Mathieu Doray. Resolved by tchemit. Changes: o Normalisation du nom des requètes Issue: 2875. Thanks to Tony Chemit. Resolved by tchemit. Downloads --------- For a manual installation, you can download files here: http://forge.codelutin.com/projects/echobase/files * echobase-referentiel-2.2-SNAPSHOT.echobase - http://forge.codelutin.com/attachments/download/1021 Maven artifacts --------------- Artifacts are deployed in nuiton maven repository http://maven.nuiton.org/other-releases/ Have fun! -EchoBase team

1 0

r835 - in trunk: . echobase-ui
by tchemit＠users.forge.codelutin.com 07 Aug '13

07 Aug '13

Author: tchemit Date: 2013-08-07 09:50:32 +0200 (Wed, 07 Aug 2013) New Revision: 835 Url: http://forge.codelutin.com/projects/echobase/repository/revisions/835 Log: use NG release process by mavenpom Modified: trunk/echobase-ui/pom.xml trunk/pom.xml Modified: trunk/echobase-ui/pom.xml =================================================================== --- trunk/echobase-ui/pom.xml 2013-08-05 10:46:02 UTC (rev 834) +++ trunk/echobase-ui/pom.xml 2013-08-07 07:50:32 UTC (rev 835) @@ -34,6 +34,14 @@ ${project.build.directory}/${project.build.finalName}.war, ${project.build.directory}/${project.build.finalName}-embedded.war </deployFiles> + +  + + <skipReleasePublishAttachments>false</skipReleasePublishAttachments> + <skipReleaseUpdateVersion>false</skipReleaseUpdateVersion> + <skipReleaseSendEmail>false</skipReleaseSendEmail> + <skipReleasePublishNews>false</skipReleasePublishNews> + <skipReleaseNextVersion>false</skipReleaseNextVersion> </properties> <build> Modified: trunk/pom.xml =================================================================== --- trunk/pom.xml 2013-08-05 10:46:02 UTC (rev 834) +++ trunk/pom.xml 2013-08-07 07:50:32 UTC (rev 835) @@ -6,7 +6,7 @@ <parent> <groupId>org.nuiton</groupId> <artifactId>mavenpom4redmine</artifactId> - <version>3.4.13</version> + <version>3.9</version> </parent> <groupId>fr.ifremer</groupId>

1 0

r834 - in trunk: . echobase-domain/src/main/java/fr/ifremer/echobase/entities echobase-domain/src/main/java/fr/ifremer/echobase/persistence echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/internalDb echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb echobase-domain/src/main/java/fr/ifremer/echobase/services echobase-services/src/main/java/fr/ifremer/echobase/services/exportquery echobase-services/src/main/java/fr/ifremer/echobase/serv
by tchemit＠users.forge.codelutin.com 05 Aug '13

05 Aug '13

Author: tchemit Date: 2013-08-05 12:46:02 +0200 (Mon, 05 Aug 2013) New Revision: 834 Url: http://forge.codelutin.com/projects/echobase/repository/revisions/834 Log: - use last topia API - improve some service code - use last postgresql dialect for hibernat configuration Modified: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/entities/DriverType.java trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/EchoBaseEntityHelper.java trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/EchoBasePersistenceHelper.java trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/EchobaseTopiaContexts.java trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/internalDb/MigrationCallBackForVersion.java trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/internalDb/MigrationCallBackForVersion2_2.java trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion.java trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_2.java trunk/echobase-domain/src/main/java/fr/ifremer/echobase/services/EchoBaseServiceSupport.java trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/exportquery/GenericSQLQuery.java trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdata/AbstractImportConfiguration.java trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdata/AbstractImportDataService.java trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdb/ImportDbService.java trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdb/ImportService.java trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdb/strategy/AbstractImportDbStrategy.java trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdb/strategy/EchoBaseImportStrategy.java trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/removedata/RemoveDataService.java trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/removedata/strategy/AbstractRemoveDataStrategy.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/EchoBaseInternalDbTransactionFilter.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/importData/AbstractConfigureImport.java trunk/pom.xml trunk/src/site/en/resources/echobase.properties trunk/src/site/en/rst/install.rst trunk/src/site/resources/echobase.properties trunk/src/site/rst/install.rst Modified: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/entities/DriverType.java =================================================================== --- trunk/echobase-domain/src/main/java/fr/ifremer/echobase/entities/DriverType.java 2013-08-03 20:00:59 UTC (rev 833) +++ trunk/echobase-domain/src/main/java/fr/ifremer/echobase/entities/DriverType.java 2013-08-05 10:46:02 UTC (rev 834) @@ -26,7 +26,7 @@ import fr.ifremer.echobase.EchoBaseConfiguration; import fr.ifremer.echobase.I18nAble; import org.hibernate.dialect.H2Dialect; -import org.hibernate.dialect.PostgreSQLDialect; +import org.hibernate.dialect.PostgreSQL82Dialect; import static org.nuiton.i18n.I18n.n_; @@ -48,7 +48,7 @@ } }, POSTGRESQL(org.postgresql.Driver.class, - PostgreSQLDialect.class, + PostgreSQL82Dialect.class, n_("echobase.common.driverType.postgres")) { @Override public String getPilotVersion(EchoBaseConfiguration config) { Modified: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/EchoBaseEntityHelper.java =================================================================== --- trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/EchoBaseEntityHelper.java 2013-08-03 20:00:59 UTC (rev 833) +++ trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/EchoBaseEntityHelper.java 2013-08-05 10:46:02 UTC (rev 834) @@ -31,7 +31,7 @@ import org.nuiton.topia.TopiaContext; import org.nuiton.topia.TopiaContextFactory; import org.nuiton.topia.TopiaException; -import org.nuiton.topia.TopiaRuntimeException; +import org.nuiton.topia.TopiaTransaction; import org.nuiton.topia.framework.TopiaContextImplementor; import java.sql.Connection; @@ -50,13 +50,14 @@ private static final Log log = LogFactory.getLog(EchoBaseEntityHelper.class); public static TopiaContext newTransactionFromRootContext(TopiaContext otherTx) throws TopiaException { - TopiaContext tx = ((TopiaContextImplementor) otherTx).getRootContext().beginTransaction(); + TopiaContextImplementor rootContext = ((TopiaContextImplementor) otherTx).getRootContext(); + TopiaContext tx = rootContext.beginTransaction(); return tx; } public static void releaseRootContext(TopiaContext context) { - TopiaContextImplementor rootContext = ((TopiaContextImplementor) context).getRootContext(); + TopiaContext rootContext = ((TopiaContextImplementor) context).getRootContext(); if (log.isInfoEnabled()) { log.info("release database " + @@ -74,8 +75,8 @@ } public static void releaseContext(TopiaContext rootContext) throws TopiaException { - if (rootContext != null && !rootContext.isClosed()) { - rootContext.closeContext(); + if (rootContext != null && !((TopiaTransaction) rootContext).isClosed()) { + ((TopiaTransaction) rootContext).closeContext(); } } @@ -114,7 +115,7 @@ if (log.isTraceEnabled()) { log.trace("no transaction to close"); } - } else if (transaction.isClosed()) { + } else if (((TopiaTransaction) transaction).isClosed()) { if (log.isTraceEnabled()) { log.trace("transaction " + transaction + " is already closed"); } @@ -124,14 +125,14 @@ } - Transaction tx = ((TopiaContextImplementor) transaction).getHibernate().getTransaction(); + Transaction tx = transaction.getHibernateSession().getTransaction(); if (!tx.wasCommitted() && !tx.wasRolledBack()) { if (log.isDebugEnabled()) { log.debug("rollback transaction!"); } tx.rollback(); } - transaction.closeContext(); + ((TopiaTransaction) transaction).closeContext(); } } @@ -140,13 +141,12 @@ * * @param tx transaction * @return {@code true} if the schema is already created, {@code false} - * otherwise + * otherwise * @throws TopiaException if something was wrong while requesting database */ public static boolean isInternalSchemaCreated(TopiaContext tx) throws TopiaException { - boolean schemaFound = - ((TopiaContextImplementor) tx).isSchemaExist(EchoBaseUserImpl.class); + boolean schemaFound = tx.isTableExists(EchoBaseUserImpl.class); return schemaFound; } @@ -156,13 +156,12 @@ * * @param tx transaction * @return {@code true} if the schema is already created, {@code false} - * otherwise + * otherwise * @throws TopiaException if something was wrong while requesting database */ public static boolean isWorkingDbSchemaCreated(TopiaContext tx) throws TopiaException { - boolean schemaFound = - ((TopiaContextImplementor) tx).isSchemaExist(VoyageImpl.class); + boolean schemaFound = tx.isTableExists(VoyageImpl.class); return schemaFound; } Modified: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/EchoBasePersistenceHelper.java =================================================================== --- trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/EchoBasePersistenceHelper.java 2013-08-03 20:00:59 UTC (rev 833) +++ trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/EchoBasePersistenceHelper.java 2013-08-05 10:46:02 UTC (rev 834) @@ -28,8 +28,6 @@ import fr.ifremer.echobase.entities.EchoBaseEntityEnum; import org.nuiton.topia.TopiaContext; import org.nuiton.topia.TopiaException; -import org.nuiton.topia.TopiaRuntimeException; -import org.nuiton.topia.framework.TopiaContextImplementor; import org.nuiton.topia.persistence.TopiaDAO; import org.nuiton.topia.persistence.TopiaEntity; import org.nuiton.topia.persistence.TopiaPersistenceHelper; @@ -60,7 +58,7 @@ public final void flushTransaction(TopiaContext tx, String errorMessage) { try { - ((TopiaContextImplementor) tx).getHibernate().flush(); + tx.getHibernateSession().flush(); } catch (TopiaException e) { throw new EchoBaseTechnicalException(errorMessage, e); } Modified: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/EchobaseTopiaContexts.java =================================================================== --- trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/EchobaseTopiaContexts.java 2013-08-03 20:00:59 UTC (rev 833) +++ trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/EchobaseTopiaContexts.java 2013-08-05 10:46:02 UTC (rev 834) @@ -34,6 +34,7 @@ import org.nuiton.topia.TopiaContext; import org.nuiton.topia.TopiaContextFactory; import org.nuiton.topia.TopiaException; +import org.nuiton.topia.TopiaPersistenceContext; import org.nuiton.util.RecursiveProperties; import java.io.File; @@ -82,7 +83,7 @@ log.info("Will create schema for " + jdbcConfiguration.getUrl()); } - result.createSchema(); + ((TopiaPersistenceContext) result).createSchema(); } } catch (TopiaException e) { throw new EchoBaseTechnicalException("Could not create db schema", e); @@ -108,7 +109,7 @@ log.info("Will create schema for " + properties.get(TopiaContextFactory.CONFIG_URL)); } - result.createSchema(); + ((TopiaPersistenceContext) result).createSchema(); } } catch (TopiaException e) { throw new EchoBaseTechnicalException("Could not create db schema", e); @@ -203,4 +204,10 @@ properties.put(propertyName, o); properties.remove(propertyNameToRemove); } + + public static boolean canUpdatePostgis(TopiaContext tx) { + String dialect = tx.getHibernateConfiguration().getProperty(TopiaContextFactory.CONFIG_DIALECT); + + return DriverType.POSTGRESQL.getDialectClass().getName().equals(dialect); + } } Modified: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/internalDb/MigrationCallBackForVersion.java =================================================================== --- trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/internalDb/MigrationCallBackForVersion.java 2013-08-03 20:00:59 UTC (rev 833) +++ trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/internalDb/MigrationCallBackForVersion.java 2013-08-05 10:46:02 UTC (rev 834) @@ -24,8 +24,8 @@ */ import org.hibernate.dialect.Dialect; +import org.nuiton.topia.TopiaContext; import org.nuiton.topia.TopiaNotFoundException; -import org.nuiton.topia.framework.TopiaContextImplementor; import org.nuiton.topia.migration.TopiaMigrationCallbackByClassNG; /** @@ -36,7 +36,7 @@ */ public abstract class MigrationCallBackForVersion extends TopiaMigrationCallbackByClassNG.MigrationCallBackForVersion { - protected Dialect getDialect(TopiaContextImplementor tx) throws TopiaNotFoundException { + protected Dialect getDialect(TopiaContext tx) throws TopiaNotFoundException { Dialect dialect = Dialect.getDialect(tx.getHibernateConfiguration().getProperties()); return dialect; Modified: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/internalDb/MigrationCallBackForVersion2_2.java =================================================================== --- trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/internalDb/MigrationCallBackForVersion2_2.java 2013-08-03 20:00:59 UTC (rev 833) +++ trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/internalDb/MigrationCallBackForVersion2_2.java 2013-08-05 10:46:02 UTC (rev 834) @@ -26,8 +26,8 @@ import fr.ifremer.echobase.entities.ExportQueries; import fr.ifremer.echobase.entities.ExportQuery; import fr.ifremer.echobase.entities.ExportQueryImpl; +import org.nuiton.topia.TopiaContext; import org.nuiton.topia.TopiaException; -import org.nuiton.topia.framework.TopiaContextImplementor; import org.nuiton.topia.framework.TopiaSQLQuery; import org.nuiton.util.Version; @@ -53,7 +53,7 @@ "UPDATE exportquery SET name = '%s', topiaversion = topiaversion + 1 WHERE topiaid = '%s'"; @Override - protected void prepareMigrationScript(TopiaContextImplementor tx, + protected void prepareMigrationScript(TopiaContext tx, List<String> queries, boolean showSql, boolean showProgression) throws TopiaException { @@ -69,7 +69,7 @@ queries.add("ALTER TABLE WorkingDbConfiguration DROP COLUMN login"); } - protected void normalizeExportQueryNames(TopiaContextImplementor tx, List<String> queries) { + protected void normalizeExportQueryNames(TopiaContext tx, List<String> queries) { TopiaSQLQuery<ExportQuery> query = new TopiaSQLQuery<ExportQuery>() { @Override Modified: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion.java =================================================================== --- trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion.java 2013-08-03 20:00:59 UTC (rev 833) +++ trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion.java 2013-08-05 10:46:02 UTC (rev 834) @@ -24,8 +24,8 @@ */ import org.hibernate.dialect.Dialect; +import org.nuiton.topia.TopiaContext; import org.nuiton.topia.TopiaNotFoundException; -import org.nuiton.topia.framework.TopiaContextImplementor; import org.nuiton.topia.migration.TopiaMigrationCallbackByClassNG; /** @@ -36,7 +36,7 @@ */ public abstract class MigrationCallBackForVersion extends TopiaMigrationCallbackByClassNG.MigrationCallBackForVersion { - protected Dialect getDialect(TopiaContextImplementor tx) throws TopiaNotFoundException { + protected Dialect getDialect(TopiaContext tx) throws TopiaNotFoundException { Dialect dialect = Dialect.getDialect(tx.getHibernateConfiguration().getProperties()); return dialect; Modified: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_2.java =================================================================== --- trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_2.java 2013-08-03 20:00:59 UTC (rev 833) +++ trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_2.java 2013-08-05 10:46:02 UTC (rev 834) @@ -23,11 +23,12 @@ * #L% */ +import fr.ifremer.echobase.persistence.EchobaseTopiaContexts; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; -import org.nuiton.topia.TopiaContextFactory; +import org.nuiton.topia.TopiaContext; import org.nuiton.topia.TopiaException; -import org.nuiton.topia.framework.TopiaContextImplementor; +import org.nuiton.topia.TopiaSqlSupport; import org.nuiton.util.Version; import java.util.List; @@ -50,7 +51,7 @@ } @Override - protected void prepareMigrationScript(TopiaContextImplementor tx, + protected void prepareMigrationScript(TopiaContext tx, List<String> queries, boolean showSql, boolean showProgression) throws TopiaException { @@ -59,12 +60,20 @@ removeResultCategoryNotNullConstrainst(queries); // compute all spatial data (http://forge.codelutin.com/issues/3037) - String dialect = tx.getHibernateConfiguration().getProperty(TopiaContextFactory.CONFIG_DIALECT); + updatePostgis(tx); + } - if (!dialect.toLowerCase().contains("h2")) { + protected void removeResultCategoryNotNullConstrainst(List<String> queries) { + queries.add("ALTER TABLE Result ALTER COLUMN category DROP NOT NULL;"); + } + + protected void updatePostgis(TopiaContext tx) { + boolean updatePostgis = EchobaseTopiaContexts.canUpdatePostgis(tx); + + if (updatePostgis) { try { // compute all spatial data - tx.executeSQL("SELECT echobase_compute_all_spatial_data();"); + ((TopiaSqlSupport) tx).executeSQL("SELECT echobase_compute_all_spatial_data();"); } catch (TopiaException e) { if (log.isErrorEnabled()) { log.error("Could not update spatial datas", e); @@ -72,9 +81,4 @@ } } } - - protected void removeResultCategoryNotNullConstrainst(List<String> queries) { - queries.add("ALTER TABLE Result ALTER COLUMN category DROP NOT NULL;"); - } - } \ No newline at end of file Modified: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/services/EchoBaseServiceSupport.java =================================================================== --- trunk/echobase-domain/src/main/java/fr/ifremer/echobase/services/EchoBaseServiceSupport.java 2013-08-03 20:00:59 UTC (rev 833) +++ trunk/echobase-domain/src/main/java/fr/ifremer/echobase/services/EchoBaseServiceSupport.java 2013-08-05 10:46:02 UTC (rev 834) @@ -29,10 +29,14 @@ import fr.ifremer.echobase.entities.EchoBaseInternalDAOHelper; import fr.ifremer.echobase.entities.EchobaseInternalEntity; import fr.ifremer.echobase.persistence.EchoBaseDbMeta; +import fr.ifremer.echobase.persistence.EchobaseTopiaContexts; import org.apache.commons.lang3.StringUtils; +import org.apache.commons.logging.Log; +import org.apache.commons.logging.LogFactory; import org.nuiton.topia.TopiaContext; import org.nuiton.topia.TopiaException; -import org.nuiton.topia.framework.TopiaContextImplementor; +import org.nuiton.topia.TopiaSqlSupport; +import org.nuiton.topia.TopiaTransaction; import org.nuiton.topia.persistence.TopiaDAO; import org.nuiton.topia.persistence.TopiaEntity; @@ -46,6 +50,10 @@ */ public class EchoBaseServiceSupport implements EchoBaseService { + /** Logger. */ + private static final Log log = + LogFactory.getLog(EchoBaseServiceSupport.class); + protected EchoBaseServiceContext serviceContext; @Override @@ -197,7 +205,7 @@ public final void commitTransaction(String errorMessage) { try { - getTransaction().commitTransaction(); + ((TopiaTransaction) getTransaction()).commitTransaction(); } catch (TopiaException eee) { throw new EchoBaseTechnicalException(errorMessage, eee); } @@ -205,16 +213,15 @@ public final void commitInternalTransaction(String errorMessage) { try { - getInternalTransaction().commitTransaction(); + ((TopiaTransaction) getInternalTransaction()).commitTransaction(); } catch (TopiaException eee) { throw new EchoBaseTechnicalException(errorMessage, eee); } } protected final void flushTransaction(String errorMessage) { - TopiaContextImplementor tx = (TopiaContextImplementor) getTransaction(); try { - tx.getHibernate().flush(); + getTransaction().getHibernateSession().flush(); } catch (TopiaException e) { throw new EchoBaseTechnicalException(errorMessage, e); } @@ -222,7 +229,7 @@ protected final void clearCache() { try { - getTransaction().clearCache(); + getTransaction().getHibernateSession().clear(); } catch (TopiaException e) { throw new EchoBaseTechnicalException("Could not clear hibernate cache", e); } @@ -238,4 +245,29 @@ return result; } + /** + * To update the {@code echobase_cell_spatial} table from the filled + * table {@code echobase_cell_sptaial_temp}. + * + * @since 2.2 + */ + public void updatePostgisTable() { + + if (EchobaseTopiaContexts.canUpdatePostgis(getTransaction())) { + + // try the update only for postgresql + try { + if (log.isInfoEnabled()) { + log.info("Will try to compute spatial data from temp table..."); + } + ((TopiaSqlSupport) getTransaction()).executeSQL("SELECT echobase_fill_cell_spatial_table();"); + commitTransaction("Could not compute spatial data"); + } catch (Exception e) { + if (log.isErrorEnabled()) { + log.error("Could not compute spatial data", e); + } + } + } + } + } Modified: trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/exportquery/GenericSQLQuery.java =================================================================== --- trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/exportquery/GenericSQLQuery.java 2013-08-03 20:00:59 UTC (rev 833) +++ trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/exportquery/GenericSQLQuery.java 2013-08-05 10:46:02 UTC (rev 834) @@ -27,7 +27,6 @@ import org.apache.commons.logging.LogFactory; import org.nuiton.topia.TopiaContext; import org.nuiton.topia.TopiaException; -import org.nuiton.topia.framework.TopiaContextImplementor; import org.nuiton.topia.framework.TopiaSQLQuery; import org.nuiton.util.PagerBean; import org.nuiton.util.PagerBeanUtil; @@ -75,16 +74,16 @@ public List<Map<String, Object>> getResult(TopiaContext tx) throws TopiaException { List<Map<String, Object>> rows = - findMultipleResult((TopiaContextImplementor) tx); + findMultipleResult(tx); return rows; } public void testQuery(TopiaContext tx) throws TopiaException { - findSingleResult((TopiaContextImplementor) tx); + findSingleResult(tx); } public String[] getColumnNames(TopiaContext tx) throws TopiaException { - findSingleResult((TopiaContextImplementor) tx); + findSingleResult(tx); return columnNames; } Modified: trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdata/AbstractImportConfiguration.java =================================================================== --- trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdata/AbstractImportConfiguration.java 2013-08-03 20:00:59 UTC (rev 833) +++ trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdata/AbstractImportConfiguration.java 2013-08-05 10:46:02 UTC (rev 834) @@ -62,13 +62,6 @@ */ protected ImportType importType; - /** - * Flag to compute spatial data at the end of the import. - * - * @since 2.2 - */ - protected boolean computeSpatialData; - /** Result stats for each file imported. (mainly kept for testing purpose). */ protected final List<EchoBaseCsvFileImportResult> importResults = Lists.newArrayList(); @@ -105,14 +98,6 @@ this.importType = importType; } - public boolean isComputeSpatialData() { - return computeSpatialData; - } - - public void setComputeSpatialData(boolean computeSpatialData) { - this.computeSpatialData = computeSpatialData; - } - public List<EchoBaseCsvFileImportResult> getImportResults() { return importResults; } Modified: trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdata/AbstractImportDataService.java =================================================================== --- trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdata/AbstractImportDataService.java 2013-08-03 20:00:59 UTC (rev 833) +++ trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdata/AbstractImportDataService.java 2013-08-05 10:46:02 UTC (rev 834) @@ -118,21 +118,10 @@ commitTransaction("Could not execute import " + getImportLabel(configuration)); - if (configuration.isComputeSpatialData()) { - try { - if (log.isInfoEnabled()) { - log.info("Will try to compute spatial data..."); - } - getTransaction().executeSQL("SELECT echobase_fill_cell_spatial_table();"); - commitTransaction("Could not compute spatial data for import " + - getImportLabel(configuration)); - } catch (Exception e) { - if (log.isErrorEnabled()) { - log.error("Could not compute spatial data", e); - } - } - } + updatePostgisTable(); + s0 = TIME_LOG.log(s0, "postgis update"); + TIME_LOG.log(s0, "importCommited"); return result; Modified: trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdb/ImportDbService.java =================================================================== --- trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdb/ImportDbService.java 2013-08-03 20:00:59 UTC (rev 833) +++ trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdb/ImportDbService.java 2013-08-05 10:46:02 UTC (rev 834) @@ -68,6 +68,12 @@ } catch (TopiaException e) { throw new ImportException("Could not import db", e); } + + if (ImportDbMode.REFERENTIAL != importDbMode) { + + // let's update postgis table + updatePostgisTable(); + } } } Modified: trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdb/ImportService.java =================================================================== --- trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdb/ImportService.java 2013-08-03 20:00:59 UTC (rev 833) +++ trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdb/ImportService.java 2013-08-05 10:46:02 UTC (rev 834) @@ -30,21 +30,22 @@ import fr.ifremer.echobase.entities.EchoBaseUser; import fr.ifremer.echobase.services.DbEditorService; import fr.ifremer.echobase.services.EchoBaseServiceSupport; +import org.apache.commons.io.IOUtils; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; +import org.nuiton.csv.Import; +import org.nuiton.csv.ImportModel; import org.nuiton.topia.persistence.TopiaEntity; import org.nuiton.topia.persistence.csv.in.CsvImportResult; import org.nuiton.topia.persistence.csv.in.ImportModelFactory; import org.nuiton.topia.persistence.metadata.TableMeta; -import org.nuiton.csv.Import; -import org.nuiton.csv.ImportModel; import java.io.BufferedReader; import java.io.File; import java.io.IOException; /** - * To import datas from csv files into the db. + * To import datas from a csv file into the db. * * @author tchemit <chemit(a)codelutin.com> * @since 0.2 @@ -94,11 +95,15 @@ result.incrementsNumberUpdated(); } } + importer.close(); + } finally { - importer.close(); + IOUtils.closeQuietly(importer); } + bf.close(); + commitTransaction("Could not commit transaction"); } catch (EchoBaseTechnicalException eee) { throw eee; @@ -106,7 +111,7 @@ log.error("Failed to read import file " + importFile.getName(), eee); throw new EchoBaseTechnicalException(eee); } finally { - bf.close(); + IOUtils.closeQuietly(bf); } return result; } Modified: trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdb/strategy/AbstractImportDbStrategy.java =================================================================== --- trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdb/strategy/AbstractImportDbStrategy.java 2013-08-03 20:00:59 UTC (rev 833) +++ trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdb/strategy/AbstractImportDbStrategy.java 2013-08-05 10:46:02 UTC (rev 834) @@ -149,10 +149,13 @@ user, file); + zipFile.close(); + commitTransaction("Could not commit db import from file " + file); } finally { - zipFile.close(); + + IOUtils.closeQuietly(zipFile); } } @@ -223,9 +226,9 @@ flushTransaction("Could not flush " + entryDef + " to db."); TIME_LOG.log(s0, "importFile::flushTransaction"); + reader.close(); } finally { - reader.close(); - + IOUtils.closeQuietly(reader); if (commitAfterEachFile) { commitTransaction( "Could not commit db import from file " + Modified: trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdb/strategy/EchoBaseImportStrategy.java =================================================================== --- trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdb/strategy/EchoBaseImportStrategy.java 2013-08-03 20:00:59 UTC (rev 833) +++ trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdb/strategy/EchoBaseImportStrategy.java 2013-08-05 10:46:02 UTC (rev 834) @@ -25,9 +25,10 @@ import fr.ifremer.echobase.entities.EchoBaseEntityEnum; import fr.ifremer.echobase.persistence.EchoBasePersistenceHelper; +import org.nuiton.csv.Import; +import org.nuiton.csv.ImportToMap; import org.nuiton.topia.TopiaContext; import org.nuiton.topia.TopiaException; -import org.nuiton.topia.framework.TopiaContextImplementor; import org.nuiton.topia.persistence.TopiaDAO; import org.nuiton.topia.persistence.TopiaEntity; import org.nuiton.topia.persistence.TopiaEntityEnum; @@ -38,8 +39,6 @@ import org.nuiton.topia.persistence.csv.in.TopiaCsvImports; import org.nuiton.topia.persistence.metadata.AssociationMeta; import org.nuiton.topia.persistence.metadata.TableMeta; -import org.nuiton.csv.Import; -import org.nuiton.csv.ImportToMap; import java.util.Map; @@ -127,7 +126,7 @@ CsvImportResult<T> csvResult, int nbRowBuffer) throws TopiaException { - TopiaContextImplementor context = dao.getContext(); + TopiaContext context = dao.getTopiaContext(); CsvProgressModel progressModel = csvResult == null ? null : csvResult.getProgressModel(); Modified: trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/removedata/RemoveDataService.java =================================================================== --- trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/removedata/RemoveDataService.java 2013-08-03 20:00:59 UTC (rev 833) +++ trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/removedata/RemoveDataService.java 2013-08-05 10:46:02 UTC (rev 834) @@ -53,7 +53,6 @@ import org.nuiton.decorator.Decorator; import org.nuiton.topia.TopiaException; import org.nuiton.topia.TopiaNotFoundException; -import org.nuiton.topia.framework.TopiaContextImplementor; import org.nuiton.topia.persistence.TopiaDAO; import org.nuiton.topia.persistence.TopiaEntity; import org.nuiton.topia.persistence.TopiaIdFactory; @@ -310,7 +309,7 @@ protected <E extends TopiaEntity> TopiaDAO<E> getDAOFromId(String id) { try { - TopiaIdFactory topiaIdFactory = ((TopiaContextImplementor) getTransaction()).getTopiaIdFactory(); + TopiaIdFactory topiaIdFactory = getTransaction().getTopiaIdFactory(); Class<E> className = topiaIdFactory.getClassName(id); return getDAO(className); } catch (TopiaNotFoundException e) { Modified: trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/removedata/strategy/AbstractRemoveDataStrategy.java =================================================================== --- trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/removedata/strategy/AbstractRemoveDataStrategy.java 2013-08-03 20:00:59 UTC (rev 833) +++ trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/removedata/strategy/AbstractRemoveDataStrategy.java 2013-08-05 10:46:02 UTC (rev 834) @@ -49,7 +49,6 @@ import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.nuiton.topia.TopiaException; -import org.nuiton.topia.framework.TopiaContextImplementor; import org.nuiton.topia.framework.TopiaSQLQuery; import java.sql.Connection; @@ -228,8 +227,7 @@ } }; - List<String> cellIds = query.findMultipleResult( - (TopiaContextImplementor) getTransaction()); + List<String> cellIds = query.findMultipleResult(getTransaction()); // remove all result of cells @@ -273,8 +271,7 @@ } }; try { - Long result = query.findSingleResult( - (TopiaContextImplementor) getTransaction()); + Long result = query.findSingleResult(getTransaction()); return result; } catch (TopiaException e) { throw new EchoBaseTechnicalException(e); @@ -298,8 +295,7 @@ } }; - List<String> cellIds = query.findMultipleResult( - (TopiaContextImplementor) getTransaction()); + List<String> cellIds = query.findMultipleResult(getTransaction()); for (String cellId : cellIds) { Cell cell = cellDAO.findByTopiaId(cellId); @@ -334,8 +330,7 @@ } }; try { - Long result = query.findSingleResult( - (TopiaContextImplementor) getTransaction()); + Long result = query.findSingleResult(getTransaction()); return result; } catch (TopiaException e) { throw new EchoBaseTechnicalException(e); 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-08-03 20:00:59 UTC (rev 833) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/EchoBaseInternalDbTransactionFilter.java 2013-08-05 10:46:02 UTC (rev 834) @@ -93,7 +93,7 @@ // } // // try { -// Transaction tx = ((TopiaContextImplementor) transaction).getHibernate().getTransaction(); +// Transaction tx = transaction.getHibernateSession().getTransaction(); // if (!tx.wasCommitted() && !tx.wasRolledBack()) { // if (log.isDebugEnabled()) { // log.debug("rollback transaction!"); Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/importData/AbstractConfigureImport.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/importData/AbstractConfigureImport.java 2013-08-03 20:00:59 UTC (rev 833) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/importData/AbstractConfigureImport.java 2013-08-05 10:46:02 UTC (rev 834) @@ -23,7 +23,6 @@ */ package fr.ifremer.echobase.ui.actions.importData; -import fr.ifremer.echobase.entities.DriverType; import fr.ifremer.echobase.io.EchoBaseIOUtil; import fr.ifremer.echobase.io.InputFile; import fr.ifremer.echobase.services.importdata.AbstractImportConfiguration; @@ -71,8 +70,6 @@ EchoBaseIOUtil.copyFile(inputFile, dataDirectory); } } - // can compute spatial data only for postgresql db - model.setComputeSpatialData(DriverType.POSTGRESQL == getEchoBaseSession().getWorkingDbConfiguration().getDriverType()); } Modified: trunk/pom.xml =================================================================== --- trunk/pom.xml 2013-08-03 20:00:59 UTC (rev 833) +++ trunk/pom.xml 2013-08-05 10:46:02 UTC (rev 834) @@ -132,7 +132,7 @@  <eugenePluginVersion>2.7</eugenePluginVersion> - <topiaVersion>3.0-alpha-2-SNAPSHOT</topiaVersion> + <topiaVersion>3.0-SNAPSHOT</topiaVersion> <nuitonUtilsVersion>2.7</nuitonUtilsVersion> <nuitonI18nVersion>2.5.1</nuitonI18nVersion> <nuitonWebVersion>1.14</nuitonWebVersion> Modified: trunk/src/site/en/resources/echobase.properties =================================================================== --- trunk/src/site/en/resources/echobase.properties 2013-08-03 20:00:59 UTC (rev 833) +++ trunk/src/site/en/resources/echobase.properties 2013-08-05 10:46:02 UTC (rev 834) @@ -21,7 +21,7 @@ # along with this program. If not, see <http://www.gnu.org/licenses/>. # #L% ### -hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect +hibernate.dialect=org.hibernate.dialect.PostgreSQL82Dialect hibernate.connection.driver_class=org.postgresql.Driver hibernate.connection.url=jdbc:postgresql://localhost/echobase hibernate.connection.username=echobase Modified: trunk/src/site/en/rst/install.rst =================================================================== --- trunk/src/site/en/rst/install.rst 2013-08-03 20:00:59 UTC (rev 833) +++ trunk/src/site/en/rst/install.rst 2013-08-05 10:46:02 UTC (rev 834) @@ -117,7 +117,7 @@ :: - hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect + hibernate.dialect=org.hibernate.dialect.PostgreSQL82Dialect hibernate.connection.driver_class=org.postgresql.Driver hibernate.connection.url=jdbc:postgresql://monNomDeMachine/echobase hibernate.connection.username=echobase Modified: trunk/src/site/resources/echobase.properties =================================================================== --- trunk/src/site/resources/echobase.properties 2013-08-03 20:00:59 UTC (rev 833) +++ trunk/src/site/resources/echobase.properties 2013-08-05 10:46:02 UTC (rev 834) @@ -21,7 +21,7 @@ # along with this program. If not, see <http://www.gnu.org/licenses/>. # #L% ### -hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect +hibernate.dialect=org.hibernate.dialect.PostgreSQL82Dialect hibernate.connection.driver_class=org.postgresql.Driver hibernate.connection.url=jdbc:postgresql://localhost/echobase hibernate.connection.username=echobase Modified: trunk/src/site/rst/install.rst =================================================================== --- trunk/src/site/rst/install.rst 2013-08-03 20:00:59 UTC (rev 833) +++ trunk/src/site/rst/install.rst 2013-08-05 10:46:02 UTC (rev 834) @@ -113,7 +113,7 @@ :: - hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect + hibernate.dialect=org.hibernate.dialect.PostgreSQL82Dialect hibernate.connection.driver_class=org.postgresql.Driver hibernate.connection.url=jdbc:postgresql://monNomDeMachine/echobase hibernate.connection.username=echobase

1 0

r833 - in trunk: echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb echobase-services/src/main/java/fr/ifremer/echobase/services/importdata echobase-ui/src/main/assembly/dist echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/importData
by tchemit＠users.forge.codelutin.com 03 Aug '13

03 Aug '13

Author: tchemit Date: 2013-08-03 22:00:59 +0200 (Sat, 03 Aug 2013) New Revision: 833 Url: http://forge.codelutin.com/projects/echobase/repository/revisions/833 Log: refs #3037: Spatialisation des donn?\195?\169es pour la production de cartes Added: trunk/echobase-ui/src/main/assembly/dist/help.sql Modified: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_2.java trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdata/AbstractImportConfiguration.java trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdata/AbstractImportDataService.java trunk/echobase-ui/src/main/assembly/dist/postgis-structure.sql trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/importData/AbstractConfigureImport.java Modified: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_2.java =================================================================== --- trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_2.java 2013-08-02 17:39:27 UTC (rev 832) +++ trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_2.java 2013-08-03 20:00:59 UTC (rev 833) @@ -23,6 +23,9 @@ * #L% */ +import org.apache.commons.logging.Log; +import org.apache.commons.logging.LogFactory; +import org.nuiton.topia.TopiaContextFactory; import org.nuiton.topia.TopiaException; import org.nuiton.topia.framework.TopiaContextImplementor; import org.nuiton.util.Version; @@ -36,6 +39,11 @@ * @since 2.2 */ public class MigrationCallBackForVersion2_2 extends MigrationCallBackForVersion { + + /** Logger. */ + private static final Log log = + LogFactory.getLog(MigrationCallBackForVersion2_2.class); + @Override public Version getVersion() { return new Version("2.2"); @@ -50,6 +58,19 @@ // remove result category NotNull Constrainst (http://forge.codelutin.com/issues/3029) removeResultCategoryNotNullConstrainst(queries); + // compute all spatial data (http://forge.codelutin.com/issues/3037) + String dialect = tx.getHibernateConfiguration().getProperty(TopiaContextFactory.CONFIG_DIALECT); + + if (!dialect.toLowerCase().contains("h2")) { + try { + // compute all spatial data + tx.executeSQL("SELECT echobase_compute_all_spatial_data();"); + } catch (TopiaException e) { + if (log.isErrorEnabled()) { + log.error("Could not update spatial datas", e); + } + } + } } protected void removeResultCategoryNotNullConstrainst(List<String> queries) { Modified: trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdata/AbstractImportConfiguration.java =================================================================== --- trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdata/AbstractImportConfiguration.java 2013-08-02 17:39:27 UTC (rev 832) +++ trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdata/AbstractImportConfiguration.java 2013-08-03 20:00:59 UTC (rev 833) @@ -62,6 +62,13 @@ */ protected ImportType importType; + /** + * Flag to compute spatial data at the end of the import. + * + * @since 2.2 + */ + protected boolean computeSpatialData; + /** Result stats for each file imported. (mainly kept for testing purpose). */ protected final List<EchoBaseCsvFileImportResult> importResults = Lists.newArrayList(); @@ -98,6 +105,14 @@ this.importType = importType; } + public boolean isComputeSpatialData() { + return computeSpatialData; + } + + public void setComputeSpatialData(boolean computeSpatialData) { + this.computeSpatialData = computeSpatialData; + } + public List<EchoBaseCsvFileImportResult> getImportResults() { return importResults; } Modified: trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdata/AbstractImportDataService.java =================================================================== --- trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdata/AbstractImportDataService.java 2013-08-02 17:39:27 UTC (rev 832) +++ trunk/echobase-services/src/main/java/fr/ifremer/echobase/services/importdata/AbstractImportDataService.java 2013-08-03 20:00:59 UTC (rev 833) @@ -54,11 +54,11 @@ import org.apache.commons.lang3.StringUtils; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; +import org.nuiton.csv.ImportRuntimeException; import org.nuiton.topia.TopiaException; import org.nuiton.topia.persistence.TopiaDAO; import org.nuiton.topia.persistence.TopiaEntity; import org.nuiton.util.TimeLog; -import org.nuiton.csv.ImportRuntimeException; import java.io.BufferedReader; import java.io.FileInputStream; @@ -118,6 +118,21 @@ commitTransaction("Could not execute import " + getImportLabel(configuration)); + if (configuration.isComputeSpatialData()) { + try { + if (log.isInfoEnabled()) { + log.info("Will try to compute spatial data..."); + } + getTransaction().executeSQL("SELECT echobase_fill_cell_spatial_table();"); + commitTransaction("Could not compute spatial data for import " + + getImportLabel(configuration)); + } catch (Exception e) { + if (log.isErrorEnabled()) { + log.error("Could not compute spatial data", e); + } + } + } + TIME_LOG.log(s0, "importCommited"); return result; Added: trunk/echobase-ui/src/main/assembly/dist/help.sql =================================================================== --- trunk/echobase-ui/src/main/assembly/dist/help.sql (rev 0) +++ trunk/echobase-ui/src/main/assembly/dist/help.sql 2013-08-03 20:00:59 UTC (rev 833) @@ -0,0 +1,337 @@ +DROP FUNCTION IF EXISTS echobase_get_cell_data(cell_id VARCHAR ); + +CREATE OR REPLACE FUNCTION echobase_get_cell_data( + cell_id VARCHAR) + RETURNS TABLE(cellId VARCHAR, name VARCHAR, value VARCHAR) AS $$ +BEGIN + RETURN QUERY SELECT + d.cell, + dm.name, + d.datavalue + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = + cell_id; +END +$$ LANGUAGE plpgsql; + +DROP FUNCTION IF EXISTS echobase_get_cell_data_by_type(cell_type_id VARCHAR ); +CREATE OR REPLACE FUNCTION echobase_get_cell_data_by_type( + cell_type_id VARCHAR) + RETURNS TABLE(cellId VARCHAR, name VARCHAR, value VARCHAR) AS $$ +BEGIN + RETURN QUERY SELECT + + d.cell, + dm.name, + d.datavalue + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + , + cell c + WHERE d.cell = c.topiaid AND c.celltype = cell_type_id; +END +$$ LANGUAGE plpgsql; + +-- get all esdu +SELECT + * +FROM cell +WHERE celltype = + 'fr.ifremer.echobase.entities.references.CellType#1323141495215#0.7552225719013331'; + +-- esdu fr.ifremer.echobase.entities.data.Cell#1334269715614#0.1761358511840082 + +-- get all elementary +SELECT + * +FROM cell +WHERE celltype = + 'fr.ifremer.echobase.entities.references.CellType#1323141495215#0.4040239553899768'; + +-- elementary S1 fr.ifremer.echobase.entities.data.Cell#1334269715616#0.9952328417444304 +-- elementary F1 fr.ifremer.echobase.entities.data.Cell#1334269715618#0.93378795138463 +-- elementary TOTAL fr.ifremer.echobase.entities.data.Cell#1334269715649#0.3655892238898185 + +-- add missing data for fr.ifremer.echobase.entities.data.Cell#1334269715616#0.9952328417444304 +INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES ( + 'fr.ifremer.echobase.entities.data.Data#latitude#start', + now(), + 0, + 'fr.ifremer.echobase.entities.data.Cell#1334269715616#0.9952328417444304', + 'fr.ifremer.echobase.entities.references.DataMetadata#1323610280601#0.7298992114976368', + '4509.876N', + 'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906' +); +INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES ( + 'fr.ifremer.echobase.entities.data.Data#longitude#start', + now(), + 0, + 'fr.ifremer.echobase.entities.data.Cell#1334269715616#0.9952328417444304', + 'fr.ifremer.echobase.entities.references.DataMetadata#1323610280597#0.9322615025965237', + '00311.583W', + 'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906' +); +INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES ( + 'fr.ifremer.echobase.entities.data.Data#latitude#end', + now(), + 0, + 'fr.ifremer.echobase.entities.data.Cell#1334269715616#0.9952328417444304', + 'fr.ifremer.echobase.entities.references.DataMetadata#1323610280609#0.8321851834658549', + '4509.876N', + 'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906' +); +INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES ( + 'fr.ifremer.echobase.entities.data.Data#longitude#end', + now(), + 0, + 'fr.ifremer.echobase.entities.data.Cell#1334269715616#0.9952328417444304', + 'fr.ifremer.echobase.entities.references.DataMetadata#1323610280607#0.8537654504079055', + '00311.583W', + 'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906' +); + +-- add missing data for fr.ifremer.echobase.entities.data.Cell#1334269715618#0.93378795138463 +INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES ( + 'fr.ifremer.echobase.entities.data.Data#latitude#start2', + now(), + 0, + 'fr.ifremer.echobase.entities.data.Cell#1334269715618#0.93378795138463', + 'fr.ifremer.echobase.entities.references.DataMetadata#1323610280601#0.7298992114976368', + '4509.876N', + 'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906' +); +INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES ( + 'fr.ifremer.echobase.entities.data.Data#longitude#end2', + now(), + 0, + 'fr.ifremer.echobase.entities.data.Cell#1334269715618#0.93378795138463', + 'fr.ifremer.echobase.entities.references.DataMetadata#1323610280607#0.8537654504079055', + '00311.583W', + 'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906' +); +INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES ( + 'fr.ifremer.echobase.entities.data.Data#latitude#end2', + now(), + 0, + 'fr.ifremer.echobase.entities.data.Cell#1334269715618#0.93378795138463', + 'fr.ifremer.echobase.entities.references.DataMetadata#1323610280609#0.8321851834658549', + '4509.876N', + 'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906' +); +INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES ( + 'fr.ifremer.echobase.entities.data.Data#longitude#start2', + now(), + 0, + 'fr.ifremer.echobase.entities.data.Cell#1334269715618#0.93378795138463', + 'fr.ifremer.echobase.entities.references.DataMetadata#1323610280597#0.9322615025965237', + '00311.583W', + 'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906' +); + +-- add missing data for fr.ifremer.echobase.entities.data.Cell#1334269715649#0.3655892238898185 +INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES ( + 'fr.ifremer.echobase.entities.data.Data#longitude#end3', + now(), + 0, + 'fr.ifremer.echobase.entities.data.Cell#1334269715649#0.3655892238898185', + 'fr.ifremer.echobase.entities.references.DataMetadata#1323610280607#0.8537654504079055', + '00311.583W', + 'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906' +); +INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES ( + 'fr.ifremer.echobase.entities.data.Data#latitude#end3', + now(), + 0, + 'fr.ifremer.echobase.entities.data.Cell#1334269715649#0.3655892238898185', + 'fr.ifremer.echobase.entities.references.DataMetadata#1323610280609#0.8321851834658549', + '4509.876N', + 'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906' +); +INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES ( + 'fr.ifremer.echobase.entities.data.Data#longitude#start3', + now(), + 0, + 'fr.ifremer.echobase.entities.data.Cell#1334269715649#0.3655892238898185', + 'fr.ifremer.echobase.entities.references.DataMetadata#1323610280597#0.9322615025965237', + '00311.583W', + 'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906' +); +INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES ( + 'fr.ifremer.echobase.entities.data.Data#latitude#start3', + now(), + 0, + 'fr.ifremer.echobase.entities.data.Cell#1334269715649#0.3655892238898185', + 'fr.ifremer.echobase.entities.references.DataMetadata#1323610280601#0.7298992114976368', + '4509.876N', + 'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906' +); +INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES ( + 'fr.ifremer.echobase.entities.data.Data#depthSurfaceStart', + now(), + 0, + 'fr.ifremer.echobase.entities.data.Cell#1334269715649#0.3655892238898185', + 'fr.ifremer.echobase.entities.references.DataMetadata#1323610280604#0.24754673981362407', + '50', + 'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906' +); + +INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES ( + 'fr.ifremer.echobase.entities.data.Data#depthBottomStart', + now(), + 0, + 'fr.ifremer.echobase.entities.data.Cell#1334269715649#0.3655892238898185', + 'fr.ifremer.echobase.entities.references.DataMetadata#1323610280606#0.5079595792861521', + '50.2', + 'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906' +); + +-- get all regionSurf +SELECT + * +FROM cell +WHERE celltype = + 'fr.ifremer.echobase.entities.references.CellType#1323141495214#0.9174274554668074'; +-- get all regionCLAS +SELECT + * +FROM cell +WHERE celltype = + 'fr.ifremer.echobase.entities.references.CellType#1323141495214#0.93529014905942'; + +-- region SURF fr.ifremer.echobase.entities.data.Cell#1334269910921#0.32895739765394183 + +INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES ( + 'fr.ifremer.echobase.entities.data.Data#regionEnv-0', + now(), + 0, + 'fr.ifremer.echobase.entities.data.Cell#1334269910921#0.32895739765394183', + 'fr.ifremer.echobase.entities.references.DataMetadata#1332945937351#0.37425974728165657', + '-2 43.8651276 52', + 'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906' +); + +INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES ( + 'fr.ifremer.echobase.entities.data.Data#regionEnv-1', + now(), + 0, + 'fr.ifremer.echobase.entities.data.Cell#1334269910921#0.32895739765394183', + 'fr.ifremer.echobase.entities.references.DataMetadata#1332945937351#0.37425974728165657', + '-2.5 43.8651276 52', + 'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906' +); + +INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES ( + 'fr.ifremer.echobase.entities.data.Data#regionEnv-2', + now(), + 0, + 'fr.ifremer.echobase.entities.data.Cell#1334269910921#0.32895739765394183', + 'fr.ifremer.echobase.entities.references.DataMetadata#1332945937351#0.37425974728165657', + '-3 43.8651276 52', + 'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906' +); + +INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES ( + 'fr.ifremer.echobase.entities.data.Data#regionEnv-3', + now(), + 0, + 'fr.ifremer.echobase.entities.data.Cell#1334269910921#0.32895739765394183', + 'fr.ifremer.echobase.entities.references.DataMetadata#1332945937351#0.37425974728165657', + '-3.5 43.8651276 52', + 'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906' +); + +INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES ( + 'fr.ifremer.echobase.entities.data.Data#regionEnv-4', + now(), + 0, + 'fr.ifremer.echobase.entities.data.Cell#1334269910921#0.32895739765394183', + 'fr.ifremer.echobase.entities.references.DataMetadata#1332945937351#0.37425974728165657', + '-2 43.8651276 52', + 'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906' +); + +-- region CLAS fr.ifremer.echobase.entities.data.Cell#1334269715603#0.9055571201552138 + +INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES ( + 'fr.ifremer.echobase.entities.data.Data#regionEnv2-0', + now(), + 0, + 'fr.ifremer.echobase.entities.data.Cell#1334269715603#0.9055571201552138', + 'fr.ifremer.echobase.entities.references.DataMetadata#1332945937351#0.37425974728165657', + '-2 43.8651276 52', + 'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906' +); + +INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES ( + 'fr.ifremer.echobase.entities.data.Data#regionEnv2-1', + now(), + 0, + 'fr.ifremer.echobase.entities.data.Cell#1334269715603#0.9055571201552138', + 'fr.ifremer.echobase.entities.references.DataMetadata#1332945937351#0.37425974728165657', + '-2.5 43.8651276 52', + 'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906' +); + +INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES ( + 'fr.ifremer.echobase.entities.data.Data#regionEnv2-2', + now(), + 0, + 'fr.ifremer.echobase.entities.data.Cell#1334269715603#0.9055571201552138', + 'fr.ifremer.echobase.entities.references.DataMetadata#1332945937351#0.37425974728165657', + '-3 43.8651276 52', + 'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906' +); + +INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES ( + 'fr.ifremer.echobase.entities.data.Data#regionEnv2-3', + now(), + 0, + 'fr.ifremer.echobase.entities.data.Cell#1334269715603#0.9055571201552138', + 'fr.ifremer.echobase.entities.references.DataMetadata#1332945937351#0.37425974728165657', + '-3.5 43.8651276 52', + 'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906' +); + +INSERT INTO data (topiaid, topiacreatedate, topiaversion, cell, datametadata, datavalue, dataquality) VALUES ( + 'fr.ifremer.echobase.entities.data.Data#regionEnv2-4', + now(), + 0, + 'fr.ifremer.echobase.entities.data.Cell#1334269715603#0.9055571201552138', + 'fr.ifremer.echobase.entities.references.DataMetadata#1332945937351#0.37425974728165657', + '-2 43.8651276 52', + 'fr.ifremer.echobase.entities.references.DataQuality#1323132845182#0.8411761220854906' +); + +-- get all map +SELECT + * +FROM cell +WHERE celltype = + 'fr.ifremer.echobase.entities.references.CellType#1330625861828#0.5189359744467841' +LIMIT 1; + +DELETE FROM echobase_cell_spatial_temp; +DELETE FROM echobase_cell_spatial; + +INSERT INTO public.echobase_cell_spatial_temp (cellid, celltype) VALUES ('fr.ifremer.echobase.entities.data.Cell#1334269715614#0.1761358511840082', 'ESDU'); +INSERT INTO public.echobase_cell_spatial_temp (cellid, celltype) VALUES ('fr.ifremer.echobase.entities.data.Cell#1334269715616#0.9952328417444304', 'ELEMENTARY'); +INSERT INTO public.echobase_cell_spatial_temp (cellid, celltype) VALUES ('fr.ifremer.echobase.entities.data.Cell#1334269715618#0.93378795138463', 'ELEMENTARY'); +INSERT INTO public.echobase_cell_spatial_temp (cellid, celltype) VALUES ('fr.ifremer.echobase.entities.data.Cell#1334269715649#0.3655892238898185', 'ELEMENTARY'); +INSERT INTO public.echobase_cell_spatial_temp (cellid, celltype) VALUES ('fr.ifremer.echobase.entities.data.Cell#1334269910921#0.32895739765394183', 'REGION'); +INSERT INTO public.echobase_cell_spatial_temp (cellid, celltype) VALUES ('fr.ifremer.echobase.entities.data.Cell#1334269715603#0.9055571201552138', 'REGION'); +INSERT INTO public.echobase_cell_spatial_temp (cellid, celltype) VALUES ((SELECT + topiaid + FROM + cell + WHERE + celltype + = + 'fr.ifremer.echobase.entities.references.CellType#1330625861828#0.5189359744467841' + LIMIT 1), 'MAP'); + +SELECT + echobase_fill_cell_spatial_table(); + +SELECT + echobase_compute_all_spatial_data(); \ No newline at end of file Modified: trunk/echobase-ui/src/main/assembly/dist/postgis-structure.sql =================================================================== --- trunk/echobase-ui/src/main/assembly/dist/postgis-structure.sql 2013-08-02 17:39:27 UTC (rev 832) +++ trunk/echobase-ui/src/main/assembly/dist/postgis-structure.sql 2013-08-03 20:00:59 UTC (rev 833) @@ -23,9 +23,9 @@ CREATE EXTENSION postgis; -- creation table de spatialisation -DROP TABLE IF EXISTS cell_spatial; -CREATE TABLE cell_spatial ( - voyageid VARCHAR(256), +DROP TABLE IF EXISTS echobase_cell_spatial; +CREATE TABLE echobase_cell_spatial ( + voyageid VARCHAR(256) NOT NULL, voyagename VARCHAR(256), transitid VARCHAR(256), transitname VARCHAR(256), @@ -35,83 +35,106 @@ dataAcquisitionname VARCHAR(256), dataProcessingid VARCHAR(256), dataProcessingname VARCHAR(256), - celltypeid VARCHAR(256), - celltypename VARCHAR(256), - cellid VARCHAR(256), - cellname VARCHAR(256), + celltypeid VARCHAR(256) NOT NULL, + celltypename VARCHAR(256) NOT NULL, + cellid VARCHAR(256) PRIMARY KEY, + cellname VARCHAR(256) NOT NULL, + lastUpdateDate TIMESTAMP NOT NULL, coordinate geography (POINT, 4326), coordinate3D geography (POINTZ, 4326), - shape geography (POLYGON, 4326) + shape geography (POLYGON, 4326), + FOREIGN KEY (cellid) REFERENCES cell (topiaid), + FOREIGN KEY (celltypeid) REFERENCES celltype (topiaid), + FOREIGN KEY (dataacquisitionid) REFERENCES dataacquisition (topiaid), + FOREIGN KEY (dataprocessingid) REFERENCES dataprocessing (topiaid), + FOREIGN KEY (transectid) REFERENCES transect (topiaid), + FOREIGN KEY (transitid) REFERENCES transit (topiaid), + FOREIGN KEY (voyageid) REFERENCES voyage (topiaid) ); +CREATE INDEX echobase_cell_spatial_coordinate_gix ON echobase_cell_spatial USING GIST (coordinate); +CREATE INDEX echobase_cell_spatial_coordinate3D_gix ON echobase_cell_spatial USING GIST (coordinate3D); +CREATE INDEX echobase_cell_spatial_shape_gix ON echobase_cell_spatial USING GIST (shape); -- creation table des traitements spatiaux a effectuer -DROP TABLE IF EXISTS cell_spatial_temp; -CREATE TABLE cell_spatial_temp ( - cellid VARCHAR(256), - celltypeid VARCHAR(256), - celltype VARCHAR(256) +DROP TABLE IF EXISTS echobase_cell_spatial_temp; +CREATE TABLE echobase_cell_spatial_temp ( + cellid VARCHAR(256) PRIMARY KEY, + celltype VARCHAR(256) NOT NULL, + FOREIGN KEY (cellid) REFERENCES cell (topiaid) ); +-- -- Trigger qui met à jour la table des traitements spatiaux à faire +-- + CREATE OR REPLACE FUNCTION echobase_fill_spatial_temp_table() RETURNS TRIGGER AS $$ DECLARE - cellId VARCHAR; - cellTypeId VARCHAR; - cellType VARCHAR; - dataType VARCHAR; - doInsert BOOLEAN; + cell_id VARCHAR; + data_type_id VARCHAR; + cellType VARCHAR; + dataType VARCHAR; + doInsert BOOLEAN; + result RECORD; BEGIN + IF (TG_OP = 'DELETE') + THEN + result = OLD; + ELSE + result = NEW; + END IF; + + cell_id = result.cell; + data_type_id = result.datametadata; doInsert = FALSE; --- recuperation du type de la la cell - cellId = NEW.cell; - SELECT - ct.id, - ct.topiaid - INTO cellType, cellTypeId - FROM celltype ct, cell ce - WHERE ce.topiaid = cellId AND ct.topiaId = ce.celltype; +-- recuperation du type de la cellule + cellType = echobase_get_cell_type(cell_id); + IF (SELECT + count(*) + FROM echobase_cell_spatial_temp c + WHERE c.cellid = cell_id) > 0 + THEN +-- la cellule est deja a traitee + RETURN result; + END IF; -- recuperation du type de la la data SELECT dt.name INTO dataType FROM datametadata dt - WHERE dt.topiaId = NEW.datametadata; + WHERE dt.topiaId = data_type_id; CASE cellType - WHEN 'Esdu' + WHEN 'ESDU' THEN -- Cell of type Esdu --- only accept LatitudeStart - LatitudeBary - LatitudeEnd - LongitudeStart - LongitudeBary - LongitudeEnd data CASE dataType WHEN 'LatitudeStart', 'LatitudeBary', 'LatitudeEnd', 'LongitudeStart', 'LongitudeBary', 'LongitudeEnd' THEN doInsert = TRUE; END CASE; - WHEN 'Elementary' + WHEN 'ELEMENTARY' THEN -- Cell of type Elementary --- only accept LatitudeStart - LatitudeBary - LatitudeEnd - LongitudeStart - LongitudeBary - LongitudeEnd - DepthRefSurfaceStart - DepthRefSurfaceEnd - DepthRefBottomStart - DepthRefBottomEnd data CASE dataType WHEN 'LatitudeStart', 'LatitudeBary', 'LatitudeEnd', 'LongitudeStart', 'LongitudeBary', 'LongitudeEnd', 'DepthRefSurfaceStart', 'DepthRefSurfaceEnd', 'DepthRefBottomStart', 'DepthRefBottomEnd' THEN doInsert = TRUE; END CASE; - WHEN 'Region', 'RegionSURF', 'RegionCLAS' + WHEN 'REGION', 'REGIONSURF', 'REGIONCLAS' THEN -- Cell of type Region --- only accept coordinate data (RegionEnvCoordinates) CASE dataType WHEN 'RegionEnvCoordinates' THEN doInsert = TRUE; + cellType = 'REGION'; END CASE; - WHEN 'Map' + WHEN 'MAP' THEN -- Cell of type Map --- only accept coordinate data (GridCellLatitude, GridCellLongitude, GridLatitudeLag, GridLongitudeLag) CASE dataType WHEN 'GridCellLatitude', 'GridCellLongitude', 'GridLatitudeLag', 'GridLongitudeLag' THEN @@ -122,263 +145,118 @@ IF doInsert = TRUE THEN --- Ajout de la cellule dans la table des traitements à effectuer - INSERT INTO cell_spatial_temp (cellid, celltypeid, celltype) VALUES (NEW.cellid, cellTypeId, cellType); - RAISE NOTICE 'Add cell %s [type %s]to cell_spatial_temp', NEW.cellid, cellType; - + RAISE NOTICE 'Add cell % [type %] to echobase_cell_spatial_temp', cell_id, cellType; + INSERT INTO echobase_cell_spatial_temp (cellid, celltype) VALUES (cell_id, cellType); END IF; - RETURN NULL; + RETURN result; END $$ LANGUAGE 'plpgsql'; DROP TRIGGER IF EXISTS echobase_fill_spatial_work_table_trigger ON data; - --- ajout du trigger sur la suppression d'une celle CREATE TRIGGER echobase_fill_spatial_work_table_trigger AFTER INSERT OR UPDATE ON data -FOR EACH ROW EXECUTE PROCEDURE echobase_fill_spatial_temp_table(); +FOR EACH ROW WHEN (NEW.cell IS NOT + NULL) EXECUTE PROCEDURE echobase_fill_spatial_temp_table(); +-- -- Trigger qui met à jour la table des traitements spatiaux à faire +-- + CREATE OR REPLACE FUNCTION echobase_delete_cell() RETURNS TRIGGER AS $$ BEGIN - DELETE FROM cell_spatial - WHERE cellid = OLD.cellid; - RAISE NOTICE 'Delete cell %s , delete cascade in cell_spatail_table', OLD.cellid; + RAISE NOTICE 'Delete cell % , delete cascade in cell_spatial_table', OLD.topiaid; - RETURN NULL; + DELETE FROM echobase_cell_spatial + WHERE cellid = OLD.topiaid; + + RETURN OLD; END $$ LANGUAGE 'plpgsql'; +DROP TRIGGER IF EXISTS echobase_delete_cell ON cell; + CREATE TRIGGER echobase_delete_cell BEFORE DELETE ON cell FOR EACH ROW EXECUTE PROCEDURE echobase_delete_cell(); --- procedure pour creer (ou mettre à jour) une ligne dans cell_spatial -CREATE OR REPLACE FUNCTION echobase_create_cell_spatial_row( - cell_id VARCHAR, - cell_type_id VARCHAR, - coordinateData GEOGRAPHY, - coordinate3dData GEOGRAPHY, - shapeData GEOGRAPHY) +-- +-- Mettre a jour toutes les données spatiales +-- + +CREATE OR REPLACE FUNCTION echobase_compute_all_spatial_data() RETURNS VOID AS $$ -DECLARE - cellSpatialRow RECORD; - voyageId VARCHAR; - voyageName VARCHAR; - transitId VARCHAR; - transitName VARCHAR; - transectId VARCHAR; - transectName VARCHAR; - dataAcquisitionId VARCHAR; - dataAcquisitionName VARCHAR; - dataProcessingId VARCHAR; - dataProcessingName VARCHAR; - cellName VARCHAR; - cellTypeName VARCHAR; - cellParentId VARCHAR; - cellRow RECORD; +DECLARE cell_id VARCHAR; BEGIN - SELECT - * - INTO cellSpatialRow - FROM cell_spatial cs - WHERE cs.cellid = cell_id; - IF NOT FOUND - THEN --- create row - RAISE LOG 'Will create spatial cell %s', cell_id; - SELECT - cell_id - INTO cellParentId; - LOOP - SELECT - topiaid, - cell - INTO cellRow - FROM cell - WHERE topiaid = cellParentId; - EXIT WHEN cellRow.cell IS NULL; - SELECT - cellRow.cell - INTO cellParentId; - END LOOP; - RAISE LOG '+++++ Will cell parentId %', cellParentId; --- get cell infos - SELECT - c.name, - ct.name - INTO cellName, cellTypeName - FROM cell c, celltype ct - WHERE c.topiaid = cell_id AND c.celltype = ct.topiaid; --- get dataProcessing infos - SELECT - dp.topiaid, - dp.processingdescription - INTO dataProcessingId - FROM dataprocessing dp, cell c - WHERE c.topiaid = cellParentId AND dp.topiaid = c.dataprocessing; - IF NOT FOUND - THEN --- no data processing, just use voyage --- get voyage infos - SELECT - v.topiaid, - v.name - INTO voyageId, voyageName - FROM voyage v, cell c - WHERE c.topiaid = cellParentId AND v.topiaid = c.voyage; - ELSE + DELETE FROM echobase_cell_spatial; --- get dataAcquisition infos - SELECT - da.topiaid, - da.acousticinstrument - INTO dataAcquisitionId, dataAcquisitionName - FROM dataacquisition da, dataprocessing dp - WHERE dp.topiaId = dataProcessingId AND da.topiaid = dp.dataacquisition; --- get transect infos - SELECT - t.topiaid, - t.vessel - INTO transectId, transitName - FROM transect t, dataacquisition da - WHERE da.topiaid = dataAcquisitionId AND t.topiaid = da.transect; --- get transit infos - SELECT - t.topiaid, - (t.starttime || ' - ' || t.endtime) - INTO transitId, transitName - FROM transit t, transect tt - WHERE tt.topiaid = transectId AND t.topiaid = tt.transit; --- get voyage infos - SELECT - v.topiaid, - v.name - INTO voyageId, voyageName - FROM voyage v, transit t - WHERE t.topiaid = transitId AND v.topiaid = t.voyage; - END IF; - INSERT INTO cell_spatial (voyageid, voyagename, transitid, transitname, transectid, transectname, dataacquisitionid, dataacquisitionname, dataprocessingid, dataprocessingname, celltypeid, celltypename, cellid, cellname, coordinate, coordinate3D, shape) - VALUES (voyageId, voyageName, transitId, transitName, transectId, transectName, dataAcquisitionId, dataAcquisitionName, dataProcessingId, dataProcessingName, cell_type_id, cellTypeName, cell_id, cellName, coordinateData, coordinate3dData, shapeData); - ELSE --- update row - RAISE LOG 'Will update spatial cell % ', cell_id; - UPDATE cell_spatial - SET coordinate = coordinateData, coordinate3d = coordinate3dData, shape= shapeData - WHERE cellid = cell_id; - END IF; + FOR cell_id IN SELECT + topiaid + FROM cell LOOP + PERFORM echobase_fill_cell_spatial_row(cell_id, + echobase_get_cell_type(cell_id)); + END LOOP; + END -$$ LANGUAGE plpgsql; +$$ +LANGUAGE plpgsql; --- procédure qui mets à jour la table cell_spatial depuis cell_spatial_temp +-- +-- Mettre a jour la table echobase_cell_spatial depuis echobase_cell_spatial_temp +-- + CREATE OR REPLACE FUNCTION echobase_fill_cell_spatial_table() RETURNS VOID AS $$ DECLARE - cellRow RECORD; - latitude RECORD; - longitude RECORD; - depth RECORD; + cellRow RECORD; + depth RECORD; BEGIN FOR cellRow IN SELECT * - FROM cell_spatial_temp LOOP + FROM echobase_cell_spatial_temp LOOP - RAISE NOTICE 'Treat cell %s [type %s] ...', cellRow.cellid, cellRow.celltype; - CASE cellRow.celltype - WHEN 'Esdu' - THEN - EXECUTE echobase_fill_esdu_cell_spatial_table(cellRow.cellid); - WHEN 'Elementary' - THEN - EXECUTE echobase_fill_elementary_cell_spatial_table(cellRow.cellid); - WHEN 'Region', 'RegionSURF', 'RegionCLAS' - THEN - EXECUTE echobase_fill_region_cell_spatial_table(cellRow.cellid); - WHEN 'Map' - THEN - EXECUTE echobase_fill_map_cell_spatial_table(cellRow.cellid); - END CASE; + PERFORM echobase_fill_cell_spatial_row(cellRow.cellid, cellRow.celltype); + END LOOP; - DELETE FROM cell_spatial_temp; + DELETE FROM echobase_cell_spatial_temp; END $$ LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION echobase_latitudetext_to_dd(latitude VARCHAR) - RETURNS REAL AS $$ -DECLARE - degre_ INTEGER; - minute_ INTEGER; - second_ INTEGER; - hemi_ VARCHAR(1); +CREATE OR REPLACE FUNCTION echobase_fill_cell_spatial_row(cell_id VARCHAR, + cell_type VARCHAR) + RETURNS VOID AS $$ BEGIN - SELECT - right(latitude, 1), - left(latitude, 2) :: INTEGER, - substring(latitude FROM 3 FOR 2) :: INTEGER, - substring(latitude FROM 6 FOR 3) :: INTEGER - INTO hemi_, degre_, minute_, second_; - RETURN echobase_dms2dd(degre_, minute_, second_, hemi_); + RAISE NOTICE 'Treat spatial cell % [type %] ...', cell_id, cell_type; + CASE cell_type + WHEN 'ESDU' + THEN + EXECUTE echobase_fill_esdu_cell_spatial_table(cell_id); + WHEN 'ELEMENTARY' + THEN + EXECUTE echobase_fill_elementary_cell_spatial_table(cell_id); + WHEN 'REGION' + THEN + EXECUTE echobase_fill_region_cell_spatial_table(cell_id); + WHEN 'MAP' + THEN + EXECUTE echobase_fill_map_cell_spatial_table(cell_id); + ELSE + RAISE LOG 'Can not deal with celltype % (cell %)', cell_type, cell_id; + END CASE; END -$$ LANGUAGE plpgsql; +$$ +LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION echobase_longitudetext_to_dd(longitude VARCHAR) - RETURNS REAL AS $$ -DECLARE - degre_ INTEGER; - minute_ INTEGER; - second_ INTEGER; - hemi_ VARCHAR(1); -BEGIN - SELECT - right(longitude, 1), - left(longitude, 3) :: INTEGER, - substring(longitude FROM 4 FOR 2) :: INTEGER, - substring(longitude FROM 7 FOR 3) :: INTEGER - INTO hemi_, degre_, minute_, second_; - - RETURN echobase_dms2dd(degre_, minute_, second_, hemi_); -END -$$ LANGUAGE plpgsql; - -CREATE OR REPLACE FUNCTION echobase_dms2dd( - D INTEGER, M INTEGER, S INTEGER, HEMI CHARACTER VARYING(1) -) - RETURNS DOUBLE PRECISION AS $$ -DECLARE - ret DOUBLE PRECISION; - dir INTEGER; -BEGIN - dir := 1; ---init to 1 for default positive return - ret := 0; ---init to zero. - --ONLY S or W will trip this. Any other letter or NULL will result in positive return value - IF UPPER(HEMI) = 'S' OR UPPER(HEMI) = 'W' - THEN - dir := -1; --then southern or western hemisphere - END IF; ---SOME data has negative values in minutes and seconds as well as degrees. Use ABS to standardize all three. - ret := (ABS(CAST(D AS DOUBLE PRECISION)) + (ABS((CAST(M AS DOUBLE PRECISION) + - (ABS((CAST(S AS - DOUBLE PRECISION)) - / 60))) / 60))); - ret := ret * dir; - RETURN ret; - -END; -$$ LANGUAGE plpgsql; - CREATE OR REPLACE FUNCTION echobase_fill_esdu_cell_spatial_table( - cell_id VARCHAR, - cell_type_id VARCHAR) + cell_id VARCHAR) RETURNS VOID AS $$ DECLARE latitude VARCHAR; @@ -388,7 +266,7 @@ latitudeNumber REAL; longitudeNumber REAL; BEGIN - RAISE LOG 'Treat esdu cell %s ', cell_id; + RAISE DEBUG 'Treat esdu cell % ', cell_id; -- test if start / bary / end event SELECT d.datavalue @@ -399,7 +277,7 @@ IF FOUND THEN -- start data - RAISE LOG 'Treat esdu Start cell %s ', cell_id; + RAISE DEBUG 'Treat esdu Start cell % ', cell_id; SELECT d.datavalue INTO longitude @@ -416,7 +294,7 @@ IF FOUND THEN -- bary data - RAISE LOG 'Treat esdu Bary cell %s', cell_id; + RAISE DEBUG 'Treat esdu Bary cell %', cell_id; SELECT d.datavalue INTO longitude @@ -433,7 +311,7 @@ IF FOUND THEN -- end data - RAISE LOG 'Treat End cell %s', cell_id; + RAISE DEBUG 'Treat End cell %', cell_id; SELECT d.datavalue INTO longitude @@ -442,7 +320,7 @@ WHERE d.cell = cell_id AND dm.name = 'LongitudeEnd'; ELSE -- no spatial data - RAISE LOG 'Could not find spatial data for cell %s', cell_id; + RAISE LOG 'Could not find spatial data for cell %', cell_id; RETURN; END IF; END IF; @@ -458,23 +336,19 @@ INTO longitudeNumber; SELECT - 'SRID=4326;POINT(' || latitudeNumber || ' ' || longitudeNumber || ')' + 'SRID=4326;POINT(' || longitudeNumber || ' ' || latitudeNumber || ')' INTO spatialText; - RAISE LOG '----------------------------------------- spatial data (%,%) for cell %', latitude, longitude, cell_id; - RAISE LOG '----------------------------------------- spatial data % for cell %', spatialText, cell_id; + RAISE DEBUG 'spatial data % for cell %', spatialText, cell_id; - PERFORM echobase_create_cell_spatial_row(cell_id, - cell_type_id, - ST_GeographyFromText( - spatialText), - NULL, - NULL); + PERFORM echobase_create_echobase_cell_spatial_row(cell_id, + spatialText, + NULL, + NULL); END $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION echobase_fill_elementary_cell_spatial_table( - cell_id VARCHAR, - cell_type_id VARCHAR) + cell_id VARCHAR) RETURNS VOID AS $$ DECLARE latitude VARCHAR; @@ -484,7 +358,7 @@ latitudeNumber REAL; longitudeNumber REAL; BEGIN - RAISE LOG 'Treat elementary cell %s ', cell_id; + RAISE DEBUG 'Treat elementary cell % ', cell_id; -- try start elementary SELECT d.datavalue @@ -495,7 +369,7 @@ IF FOUND THEN -- this is a start elementary - RAISE LOG 'Treat elementary Start cell %s ', cell_id; + RAISE DEBUG 'Treat elementary Start cell % ', cell_id; -- get longitude SELECT d.datavalue @@ -531,7 +405,7 @@ IF FOUND THEN -- this is a bary elementary - RAISE LOG 'Treat elementary Bary cell %s', cell_id; + RAISE DEBUG 'Treat elementary Bary cell %', cell_id; SELECT d.datavalue INTO longitude @@ -565,7 +439,7 @@ IF FOUND THEN -- this is a end elementary - RAISE LOG 'Treat elementary End cell %s', cell_id; + RAISE DEBUG 'Treat elementary End cell %', cell_id; SELECT d.datavalue INTO longitude @@ -590,7 +464,7 @@ END IF; ELSE -- no spatial data - RAISE LOG 'Could not find spatial data for cell %s', cell_id; + RAISE DEBUG 'Could not find spatial data for cell %', cell_id; RETURN; END IF; END IF; @@ -606,17 +480,396 @@ INTO longitudeNumber; SELECT - 'SRID=4326;POINTZ(' || latitudeNumber || ' ' || longitudeNumber || ' ' || + 'SRID=4326;POINTZ(' || longitudeNumber || ' ' || latitudeNumber || ' ' || depth || ')' INTO spatialText; - RAISE LOG '----------------------------------------- spatial data (%,%,%) for cell %', latitude, longitude, depth, cell_id; - RAISE LOG '----------------------------------------- spatial data % for cell %', spatialText, cell_id; + RAISE DEBUG 'spatial data % for cell %', spatialText, cell_id; - PERFORM echobase_create_cell_spatial_row(cell_id, - cell_type_id, - NULL, - ST_GeographyFromText( - spatialText), - NULL); + PERFORM echobase_create_echobase_cell_spatial_row(cell_id, + NULL, + spatialText, + NULL); END +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION echobase_fill_region_cell_spatial_table( + cell_id VARCHAR) + RETURNS VOID AS $$ +DECLARE + latitude REAL; + longitude REAL; + depth REAL; + dataMetadataId VARCHAR; + dataValue VARCHAR; + spatialText VARCHAR := 'POLYGON(('; +BEGIN + RAISE DEBUG 'Treat region cell %', cell_id; + SELECT + topiaid + INTO dataMetadataId + FROM datametadata + WHERE name = 'RegionEnvCoordinates'; + FOR dataValue IN SELECT + d.datavalue + FROM data d + WHERE d.cell = cell_id AND d.datametadata = dataMetadataId + ORDER BY d.topiacreatedate LOOP +-- split dataValue in lat - long - depth + SELECT + split_part(dataValue, ' ', 1) :: REAL + INTO latitude; + SELECT + split_part(dataValue, ' ', 2) :: REAL + INTO longitude; + SELECT + split_part(dataValue, ' ', 3) :: REAL + INTO depth; + SELECT + spatialText || longitude || ' ' || latitude || ',' + INTO spatialText; + END LOOP; + SELECT + left(spatialText, -1) || '))' + INTO spatialText; + + RAISE DEBUG 'spatial data % for cell %', spatialText, cell_id; + + PERFORM echobase_create_echobase_cell_spatial_row(cell_id, + NULL, + NULL, + spatialText); +END +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION echobase_fill_map_cell_spatial_table( + cell_id VARCHAR) + RETURNS VOID AS $$ +DECLARE + latitude REAL; + longitude REAL; + deltaLatitude REAL; + deltaLongitude REAL; + P0 VARCHAR; + P1 VARCHAR; + P2 VARCHAR; + P3 VARCHAR; + spatialText VARCHAR; +BEGIN + RAISE DEBUG 'Treat map cell % ', cell_id; +-- get GridCellLatitude + SELECT + d.datavalue :: REAL + INTO latitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'GridCellLatitude'; +-- get GridCellLatitude + SELECT + d.datavalue :: REAL + INTO longitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'GridCellLongitude'; +-- get GridCellLatitude + SELECT + d.datavalue :: REAL + INTO deltaLatitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'GridLatitudeLag'; +-- get GridCellLatitude + SELECT + d.datavalue :: REAL + INTO deltaLongitude + FROM data d LEFT OUTER JOIN datametadata AS dm + ON d.datametadata = dm.topiaid + WHERE d.cell = cell_id AND dm.name = 'GridLongitudeLag'; + + IF + latitude IS NOT NULL AND longitude IS NOT NULL AND deltaLatitude IS NOT NULL + AND deltaLongitude IS NOT NULL + THEN + SELECT + longitude || ' ' || latitude + INTO P0; + SELECT + longitude || ' ' || latitude + deltaLatitude + INTO P1; + SELECT + longitude + deltaLongitude || ' ' || latitude + deltaLatitude + INTO P2; + SELECT + longitude + deltaLongitude || ' ' || latitude + INTO P3; + + SELECT + 'POLYGON((' || p0 || ',' || P1 || ',' || P2 || ',' || P3 || ',' || P0 || + '))' + INTO spatialText; + RAISE DEBUG 'spatial data % for cell %', spatialText, cell_id; + + PERFORM echobase_create_echobase_cell_spatial_row(cell_id, + NULL, + NULL, + spatialText); + END IF; +END +$$ LANGUAGE plpgsql; + +-- procedure pour creer (ou mettre à jour) une ligne dans echobase_cell_spatial + +CREATE OR REPLACE FUNCTION echobase_create_echobase_cell_spatial_row( + cell_id VARCHAR, + coordinateText VARCHAR, + coordinate3dText VARCHAR, + shapeText VARCHAR) + RETURNS VOID AS $$ +DECLARE + cellSpatialRow RECORD; + voyageId VARCHAR; + voyageName VARCHAR; + transitId VARCHAR; + transitName VARCHAR; + transectId VARCHAR; + transectName VARCHAR; + dataAcquisitionId VARCHAR; + dataAcquisitionName VARCHAR; + dataProcessingId VARCHAR; + dataProcessingName VARCHAR; + cellName VARCHAR; + cellTypeId VARCHAR; + cellTypeName VARCHAR; + cellParentId VARCHAR; + cellRow RECORD; + coordinateData GEOGRAPHY; + coordinate3dData GEOGRAPHY; + shapeData GEOGRAPHY; +BEGIN + IF coordinateText IS NULL AND coordinate3dText IS NULL AND + shapeText IS NULL + THEN + RAISE DEBUG 'Could not find spatial data for cell %', cell_id; + RETURN; + END IF; + IF coordinateText IS NOT NULL + THEN + BEGIN + coordinateData = ST_GeographyFromText(coordinateText); + EXCEPTION WHEN internal_error + THEN + + RAISE LOG 'Could not create coordinate spatial data %', coordinateText; + RETURN; + END; + ELSEIF coordinate3dText IS NOT NULL + THEN + BEGIN + coordinate3dData = ST_GeographyFromText(coordinate3dText); + EXCEPTION WHEN internal_error + THEN + + RAISE LOG 'Could not create coordinate3D spatial data %', coordinate3dText; + RETURN; + END; + ELSEIF shapeText IS NOT NULL + THEN + BEGIN + shapeData = ST_GeographyFromText(shapeText); + EXCEPTION WHEN internal_error + THEN + + RAISE LOG 'Could not create shape spatial data %', shapeText; + RETURN; + END; + END IF; + + SELECT + * + INTO cellSpatialRow + FROM echobase_cell_spatial cs + WHERE cs.cellid = cell_id; + IF NOT FOUND + THEN +-- create row + RAISE DEBUG 'Will create spatial cell %', cell_id; + SELECT + cell_id + INTO cellParentId; + LOOP + IF dataProcessingId IS NULL + THEN +-- try to get dataprocessingId from this cell + SELECT + dp.topiaid, + dp.processingdescription + INTO dataProcessingId + FROM dataprocessing dp, cell c + WHERE c.topiaid = cellParentId AND dp.topiaid = c.dataprocessing; + END IF; + SELECT + topiaid, + cell + INTO cellRow + FROM cell + WHERE topiaid = cellParentId; + EXIT WHEN cellRow.cell IS NULL; + SELECT + cellRow.cell + INTO cellParentId; + END LOOP; + RAISE DEBUG 'use cell parentId %', cellParentId; +-- get cell infos + SELECT + c.name, + ct.name, + ct.topiaid + INTO cellName, cellTypeName, cellTypeId + FROM cell c, celltype ct + WHERE c.topiaid = cell_id AND c.celltype = ct.topiaid; + + IF dataProcessingId IS NULL + THEN +-- get voyage infos + SELECT + v.topiaid, + v.name + INTO voyageId, voyageName + FROM voyage v, cell c + WHERE c.topiaid = cellParentId AND v.topiaid = c.voyage; + ELSE + +-- get dataAcquisition infos + SELECT + da.topiaid, + da.acousticinstrument + INTO dataAcquisitionId, dataAcquisitionName + FROM dataacquisition da, dataprocessing dp + WHERE dp.topiaId = dataProcessingId AND da.topiaid = dp.dataacquisition; +-- get transect infos + SELECT + t.topiaid, + t.vessel + INTO transectId, transitName + FROM transect t, dataacquisition da + WHERE da.topiaid = dataAcquisitionId AND t.topiaid = da.transect; +-- get transit infos + SELECT + t.topiaid, + (t.starttime || ' - ' || t.endtime) + INTO transitId, transitName + FROM transit t, transect tt + WHERE tt.topiaid = transectId AND t.topiaid = tt.transit; +-- get voyage infos + SELECT + v.topiaid, + v.name + INTO voyageId, voyageName + FROM voyage v, transit t + WHERE t.topiaid = transitId AND v.topiaid = t.voyage; + END IF; + INSERT INTO echobase_cell_spatial (lastUpdateDate, voyageid, voyagename, transitid, transitname, transectid, transectname, dataacquisitionid, dataacquisitionname, dataprocessingid, dataprocessingname, celltypeid, celltypename, cellid, cellname, coordinate, coordinate3D, shape) + VALUES (now(), voyageId, voyageName, transitId, transitName, transectId, transectName, dataAcquisitionId, dataAcquisitionName, dataProcessingId, dataProcessingName, cellTypeId, cellTypeName, cell_id, cellName, coordinateData, coordinate3dData, shapeData); + ELSE +-- update row + RAISE DEBUG 'Will update spatial cell % ', cell_id; + UPDATE echobase_cell_spatial + SET coordinate = coordinateData, + coordinate3d = coordinate3dData, + shape = shapeData + WHERE cellid = cell_id; + END IF; +END +$$ LANGUAGE plpgsql; + +-- pour obtenir le type d'une cellule sous forme ESDU - ELEMENTARY - REGION ou MAP +CREATE OR REPLACE FUNCTION echobase_get_cell_type(cell_id VARCHAR) + RETURNS VARCHAR AS $$ +DECLARE result VARCHAR; +BEGIN + SELECT + UPPER(ct.id) + INTO result + FROM celltype ct, cell ce + WHERE ce.topiaid = cell_id AND ct.topiaId = ce.celltype; + IF 'REGIONCLAS' = result OR 'REGIONSURF' = result + THEN + result = 'REGION'; + END IF; + RETURN result; +END +$$ +LANGUAGE plpgsql; + +-- pour convertir des latitudes en dms (+ hemi) en dd +CREATE OR REPLACE FUNCTION echobase_latitudetext_to_dd(latitude VARCHAR) + RETURNS REAL AS $$ +DECLARE + degre_ INTEGER; + minute_ INTEGER; + second_ INTEGER; + hemi_ VARCHAR(1); +BEGIN + SELECT + right(latitude, 1), + left(latitude, 2) :: INTEGER, + substring(latitude FROM 3 FOR 2) :: INTEGER, + substring(latitude FROM 6 FOR 3) :: INTEGER + INTO hemi_, degre_, minute_, second_; + + RETURN echobase_dms2dd(degre_, minute_, second_, hemi_); + EXCEPTION WHEN invalid_text_representation + THEN + RETURN NULL; +END +$$ LANGUAGE plpgsql; + +-- pour convertir des longitudes en dms (+ hemi) en dd +CREATE OR REPLACE FUNCTION echobase_longitudetext_to_dd(longitude VARCHAR) + RETURNS REAL AS $$ +DECLARE + degre_ INTEGER; + minute_ INTEGER; + second_ INTEGER; + hemi_ VARCHAR(1); +BEGIN + SELECT + right(longitude, 1), + left(longitude, 3) :: INTEGER, + substring(longitude FROM 4 FOR 2) :: INTEGER, + substring(longitude FROM 7 FOR 3) :: INTEGER + INTO hemi_, degre_, minute_, second_; + + RETURN echobase_dms2dd(degre_, minute_, second_, hemi_); + EXCEPTION WHEN invalid_text_representation + THEN + RETURN NULL; +END +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION echobase_dms2dd( + D INTEGER, M INTEGER, S INTEGER, HEMI CHARACTER VARYING(1) +) + RETURNS DOUBLE PRECISION AS $$ +DECLARE + ret DOUBLE PRECISION; + dir INTEGER; +BEGIN + dir := 1; +--init to 1 for default positive return + ret := 0; +--init to zero. + --ONLY S or W will trip this. Any other letter or NULL will result in positive return value + IF UPPER(HEMI) = 'S' OR UPPER(HEMI) = 'W' + THEN + dir := -1; --then southern or western hemisphere + END IF; +--SOME data has negative values in minutes and seconds as well as degrees. Use ABS to standardize all three. + ret := (ABS(CAST(D AS DOUBLE PRECISION)) + (ABS((CAST(M AS DOUBLE PRECISION) + + (ABS((CAST(S AS + DOUBLE PRECISION)) + / 60))) / 60))); + ret := ret * dir; + RETURN ret; + +END; $$ LANGUAGE plpgsql; \ No newline at end of file Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/importData/AbstractConfigureImport.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/importData/AbstractConfigureImport.java 2013-08-02 17:39:27 UTC (rev 832) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/actions/importData/AbstractConfigureImport.java 2013-08-03 20:00:59 UTC (rev 833) @@ -23,6 +23,7 @@ */ package fr.ifremer.echobase.ui.actions.importData; +import fr.ifremer.echobase.entities.DriverType; import fr.ifremer.echobase.io.EchoBaseIOUtil; import fr.ifremer.echobase.io.InputFile; import fr.ifremer.echobase.services.importdata.AbstractImportConfiguration; @@ -70,6 +71,8 @@ EchoBaseIOUtil.copyFile(inputFile, dataDirectory); } } + // can compute spatial data only for postgresql db + model.setComputeSpatialData(DriverType.POSTGRESQL == getEchoBaseSession().getWorkingDbConfiguration().getDriverType()); }

1 0

r832 - trunk/echobase-ui/src/main/assembly/dist
by tchemit＠users.forge.codelutin.com 02 Aug '13

02 Aug '13

Author: tchemit Date: 2013-08-02 19:39:27 +0200 (Fri, 02 Aug 2013) New Revision: 832 Url: http://forge.codelutin.com/projects/echobase/repository/revisions/832 Log: refs #3037: Spatialisation des donn?\195?\169es pour la production de cartes Added: trunk/echobase-ui/src/main/assembly/dist/postgis-structure.sql Added: trunk/echobase-ui/src/main/assembly/dist/postgis-structure.sql =================================================================== --- trunk/echobase-ui/src/main/assembly/dist/postgis-structure.sql (rev 0) +++ trunk/echobase-ui/src/main/assembly/dist/postgis-structure.sql 2013-08-02 17:39:27 UTC (rev 832) @@ -0,0 +1,622 @@ +--- +-- #%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; + +-- creation table de spatialisation +DROP TABLE IF EXISTS cell_spatial; +CREATE TABLE cell_spatial ( + voyageid VARCHAR(256), + 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), + celltypename VARCHAR(256), + cellid VARCHAR(256), + cellname VARCHAR(256), + coordinate geography (POINT, 4326), + coordinate3D geography (POINTZ, 4326), + shape geography (POLYGON, 4326) +); + +-- creation table des traitements spatiaux a effectuer +DROP TABLE IF EXISTS cell_spatial_temp; +CREATE TABLE cell_spatial_temp ( + cellid VARCHAR(256), + celltypeid VARCHAR(256), + celltype VARCHAR(256) +); + +-- Trigger qui met à jour la table des traitements spatiaux à faire +CREATE OR REPLACE FUNCTION echobase_fill_spatial_temp_table() + RETURNS TRIGGER AS $$ +DECLARE + cellId VARCHAR; + cellTypeId VARCHAR; + cellType VARCHAR; + dataType VARCHAR; + doInsert BOOLEAN; +BEGIN + + doInsert = FALSE; +-- recuperation du type de la la cell + cellId = NEW.cell; + SELECT + ct.id, + ct.topiaid + INTO cellType, cellTypeId + FROM celltype ct, cell ce + WHERE ce.topiaid = cellId AND ct.topiaId = ce.celltype; + +-- recuperation du type de la la data + SELECT + dt.name + INTO dataType + FROM datametadata dt + WHERE dt.topiaId = NEW.datametadata; + + CASE cellType + WHEN 'Esdu' + THEN +-- Cell of type Esdu +-- only accept LatitudeStart - LatitudeBary - LatitudeEnd - LongitudeStart - LongitudeBary - LongitudeEnd data + CASE dataType + WHEN 'LatitudeStart', 'LatitudeBary', 'LatitudeEnd', 'LongitudeStart', 'LongitudeBary', 'LongitudeEnd' + THEN + doInsert = TRUE; + END CASE; + WHEN 'Elementary' + THEN +-- Cell of type Elementary +-- only accept LatitudeStart - LatitudeBary - LatitudeEnd - LongitudeStart - LongitudeBary - LongitudeEnd - DepthRefSurfaceStart - DepthRefSurfaceEnd - DepthRefBottomStart - DepthRefBottomEnd data + CASE dataType + WHEN 'LatitudeStart', 'LatitudeBary', 'LatitudeEnd', 'LongitudeStart', 'LongitudeBary', 'LongitudeEnd', 'DepthRefSurfaceStart', 'DepthRefSurfaceEnd', 'DepthRefBottomStart', 'DepthRefBottomEnd' + THEN + doInsert = TRUE; + END CASE; + WHEN 'Region', 'RegionSURF', 'RegionCLAS' + THEN +-- Cell of type Region +-- only accept coordinate data (RegionEnvCoordinates) + CASE dataType + WHEN 'RegionEnvCoordinates' + THEN + doInsert = TRUE; + END CASE; + WHEN 'Map' + THEN +-- Cell of type Map +-- only accept coordinate data (GridCellLatitude, GridCellLongitude, GridLatitudeLag, GridLongitudeLag) + CASE dataType + WHEN 'GridCellLatitude', 'GridCellLongitude', 'GridLatitudeLag', 'GridLongitudeLag' + THEN + doInsert = TRUE; + END CASE; + END CASE; + + IF doInsert = TRUE + THEN +--- Ajout de la cellule dans la table des traitements à effectuer + INSERT INTO cell_spatial_temp (cellid, celltypeid, celltype) VALUES (NEW.cellid, cellTypeId, cellType); + RAISE NOTICE 'Add cell %s [type %s]to cell_spatial_temp', NEW.cellid, cellType; + + END IF; + + RETURN NULL; +END +$$ +LANGUAGE 'plpgsql'; + +DROP TRIGGER IF EXISTS echobase_fill_spatial_work_table_trigger ON data; + +-- ajout du trigger sur la suppression d'une celle +CREATE TRIGGER echobase_fill_spatial_work_table_trigger +AFTER INSERT OR UPDATE ON data +FOR EACH ROW EXECUTE PROCEDURE echobase_fill_spatial_temp_table(); + +-- Trigger qui met à jour la table des traitements spatiaux à faire +CREATE OR REPLACE FUNCTION echobase_delete_cell() + RETURNS TRIGGER AS $$ +BEGIN + + DELETE FROM cell_spatial + WHERE cellid = OLD.cellid; + RAISE NOTICE 'Delete cell %s , delete cascade in cell_spatail_table', OLD.cellid; + + RETURN NULL; +END +$$ +LANGUAGE 'plpgsql'; + +CREATE TRIGGER echobase_delete_cell +BEFORE DELETE ON cell +FOR EACH ROW EXECUTE PROCEDURE echobase_delete_cell(); + +-- procedure pour creer (ou mettre à jour) une ligne dans cell_spatial +CREATE OR REPLACE FUNCTION echobase_create_cell_spatial_row( + cell_id VARCHAR, + cell_type_id VARCHAR, + coordinateData GEOGRAPHY, + coordinate3dData GEOGRAPHY, + shapeData GEOGRAPHY) + RETURNS VOID AS $$ +DECLARE + cellSpatialRow RECORD; + voyageId VARCHAR; + voyageName VARCHAR; + transitId VARCHAR; + transitName VARCHAR; + transectId VARCHAR; + transectName VARCHAR; + dataAcquisitionId VARCHAR; + dataAcquisitionName VARCHAR; + dataProcessingId VARCHAR; + dataProcessingName VARCHAR; + cellName VARCHAR; + cellTypeName VARCHAR; + cellParentId VARCHAR; + cellRow RECORD; +BEGIN + SELECT + * + INTO cellSpatialRow + FROM cell_spatial cs + WHERE cs.cellid = cell_id; + IF NOT FOUND + THEN +-- create row + RAISE LOG 'Will create spatial cell %s', cell_id; + SELECT + cell_id + INTO cellParentId; + LOOP + SELECT + topiaid, + cell + INTO cellRow + FROM cell + WHERE topiaid = cellParentId; + EXIT WHEN cellRow.cell IS NULL; + SELECT + cellRow.cell + INTO cellParentId; + END LOOP; + RAISE LOG '+++++ Will cell parentId %', cellParentId; +-- get cell infos + SELECT + c.name, + ct.name + INTO cellName, cellTypeName + FROM cell c, celltype ct + WHERE c.topiaid = cell_id AND c.celltype = ct.topiaid; +-- get dataProcessing infos + SELECT + dp.topiaid, + dp.processingdescription + INTO dataProcessingId + FROM dataprocessing dp, cell c + WHERE c.topiaid = cellParentId AND dp.topiaid = c.dataprocessing; + IF NOT FOUND + THEN +-- no data processing, just use voyage +-- get voyage infos + SELECT + v.topiaid, + v.name + INTO voyageId, voyageName + FROM voyage v, cell c + WHERE c.topiaid = cellParentId AND v.topiaid = c.voyage; + ELSE + +-- get dataAcquisition infos + SELECT + da.topiaid, + da.acousticinstrument + INTO dataAcquisitionId, dataAcquisitionName + FROM dataacquisition da, dataprocessing dp + WHERE dp.topiaId = dataProcessingId AND da.topiaid = dp.dataacquisition; +-- get transect infos + SELECT + t.topiaid, + t.vessel + INTO transectId, transitName + FROM transect t, dataacquisition da + WHERE da.topiaid = dataAcquisitionId AND t.topiaid = da.transect; +-- get transit infos + SELECT + t.topiaid, + (t.starttime || ' - ' || t.endtime) + INTO transitId, transitName + FROM transit t, transect tt + WHERE tt.topiaid = transectId AND t.topiaid = tt.transit; +-- get voyage infos + SELECT + v.topiaid, + v.name + INTO voyageId, voyageName + FROM voyage v, transit t + WHERE t.topiaid = transitId AND v.topiaid = t.voyage; + END IF; + INSERT INTO cell_spatial (voyageid, voyagename, transitid, transitname, transectid, transectname, dataacquisitionid, dataacquisitionname, dataprocessingid, dataprocessingname, celltypeid, celltypename, cellid, cellname, coordinate, coordinate3D, shape) + VALUES (voyageId, voyageName, transitId, transitName, transectId, transectName, dataAcquisitionId, dataAcquisitionName, dataProcessingId, dataProcessingName, cell_type_id, cellTypeName, cell_id, cellName, coordinateData, coordinate3dData, shapeData); + ELSE +-- update row + RAISE LOG 'Will update spatial cell % ', cell_id; + UPDATE cell_spatial + SET coordinate = coordinateData, coordinate3d = coordinate3dData, shape= shapeData + WHERE cellid = cell_id; + END IF; +END +$$ LANGUAGE plpgsql; + +-- procédure qui mets à jour la table cell_spatial depuis cell_spatial_temp +CREATE OR REPLACE FUNCTION echobase_fill_cell_spatial_table() + RETURNS VOID AS $$ +DECLARE + cellRow RECORD; + latitude RECORD; + longitude RECORD; + depth RECORD; +BEGIN + FOR cellRow IN SELECT + * + FROM cell_spatial_temp LOOP + + RAISE NOTICE 'Treat cell %s [type %s] ...', cellRow.cellid, cellRow.celltype; + CASE cellRow.celltype + WHEN 'Esdu' + THEN + EXECUTE echobase_fill_esdu_cell_spatial_table(cellRow.cellid); + WHEN 'Elementary' + THEN + EXECUTE echobase_fill_elementary_cell_spatial_table(cellRow.cellid); + WHEN 'Region', 'RegionSURF', 'RegionCLAS' + THEN + EXECUTE echobase_fill_region_cell_spatial_table(cellRow.cellid); + WHEN 'Map' + THEN + EXECUTE echobase_fill_map_cell_spatial_table(cellRow.cellid); + END CASE; + END LOOP; + + DELETE FROM cell_spatial_temp; + +END +$$ +LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION echobase_latitudetext_to_dd(latitude VARCHAR) + RETURNS REAL AS $$ +DECLARE + degre_ INTEGER; + minute_ INTEGER; + second_ INTEGER; + hemi_ VARCHAR(1); +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_); +END +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION echobase_longitudetext_to_dd(longitude VARCHAR) + RETURNS REAL AS $$ +DECLARE + degre_ INTEGER; + minute_ INTEGER; + second_ INTEGER; + hemi_ VARCHAR(1); +BEGIN + SELECT + right(longitude, 1), + left(longitude, 3) :: INTEGER, + substring(longitude FROM 4 FOR 2) :: INTEGER, + substring(longitude FROM 7 FOR 3) :: INTEGER + INTO hemi_, degre_, minute_, second_; + + RETURN echobase_dms2dd(degre_, minute_, second_, hemi_); +END +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION echobase_dms2dd( + D INTEGER, M INTEGER, S INTEGER, HEMI CHARACTER VARYING(1) +) + RETURNS DOUBLE PRECISION AS $$ +DECLARE + ret DOUBLE PRECISION; + dir INTEGER; +BEGIN + dir := 1; +--init to 1 for default positive return + ret := 0; +--init to zero. + --ONLY S or W will trip this. Any other letter or NULL will result in positive return value + IF UPPER(HEMI) = 'S' OR UPPER(HEMI) = 'W' + THEN + dir := -1; --then southern or western hemisphere + END IF; +--SOME data has negative values in minutes and seconds as well as degrees. Use ABS to standardize all three. + ret := (ABS(CAST(D AS DOUBLE PRECISION)) + (ABS((CAST(M AS DOUBLE PRECISION) + + (ABS((CAST(S AS + DOUBLE PRECISION)) + / 60))) / 60))); + ret := ret * dir; + RETURN ret; + +END; +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION echobase_fill_esdu_cell_spatial_table( + cell_id VARCHAR, + cell_type_id VARCHAR) + RETURNS VOID AS $$ +DECLARE + latitude VARCHAR; + longitude VARCHAR; + depth RECORD; + spatialText VARCHAR; + latitudeNumber REAL; + longitudeNumber REAL; +BEGIN + RAISE LOG 'Treat esdu cell %s ', 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 LOG 'Treat esdu Start cell %s ', 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 LOG 'Treat esdu Bary cell %s', 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 LOG 'Treat End cell %s', 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 %s', cell_id; + RETURN; + END IF; + END IF; + END IF; + +-- convert dms latitude to dd latitude + SELECT + echobase_latitudetext_to_dd(latitude) + INTO latitudeNumber; +-- convert dms longitude to dd longitude + SELECT + echobase_longitudetext_to_dd(longitude) + INTO longitudeNumber; + + SELECT + 'SRID=4326;POINT(' || latitudeNumber || ' ' || longitudeNumber || ')' + INTO spatialText; + RAISE LOG '----------------------------------------- spatial data (%,%) for cell %', latitude, longitude, cell_id; + RAISE LOG '----------------------------------------- spatial data % for cell %', spatialText, cell_id; + + PERFORM echobase_create_cell_spatial_row(cell_id, + cell_type_id, + ST_GeographyFromText( + spatialText), + NULL, + NULL); +END +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION echobase_fill_elementary_cell_spatial_table( + cell_id VARCHAR, + cell_type_id VARCHAR) + RETURNS VOID AS $$ +DECLARE + latitude VARCHAR; + longitude VARCHAR; + depth VARCHAR; + spatialText VARCHAR; + latitudeNumber REAL; + longitudeNumber REAL; +BEGIN + RAISE LOG 'Treat elementary cell %s ', 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 LOG 'Treat elementary Start cell %s ', 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 LOG 'Treat elementary Bary cell %s', 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 LOG 'Treat elementary End cell %s', 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 LOG 'Could not find spatial data for cell %s', cell_id; + RETURN; + END IF; + END IF; + END IF; + +-- convert dms latitude to dd latitude + SELECT + echobase_latitudetext_to_dd(latitude) + INTO latitudeNumber; +-- convert dms longitude to dd longitude + SELECT + echobase_longitudetext_to_dd(longitude) + INTO longitudeNumber; + + SELECT + 'SRID=4326;POINTZ(' || latitudeNumber || ' ' || longitudeNumber || ' ' || + depth || ')' + INTO spatialText; + RAISE LOG '----------------------------------------- spatial data (%,%,%) for cell %', latitude, longitude, depth, cell_id; + RAISE LOG '----------------------------------------- spatial data % for cell %', spatialText, cell_id; + + PERFORM echobase_create_cell_spatial_row(cell_id, + cell_type_id, + NULL, + ST_GeographyFromText( + spatialText), + NULL); +END +$$ LANGUAGE plpgsql; \ No newline at end of file Property changes on: trunk/echobase-ui/src/main/assembly/dist/postgis-structure.sql ___________________________________________________________________ Added: svn:keywords + Author Date Id Revision HeadURL Added: svn:eol-style + native

1 0