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