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

r876 - trunk/echobase-ui/src/main/resources/i18n
by tchemit＠users.forge.codelutin.com 31 Oct '13

31 Oct '13

Author: tchemit Date: 2013-11-01 00:22:48 +0100 (Fri, 01 Nov 2013) New Revision: 876 Url: http://forge.codelutin.com/projects/echobase/repository/revisions/876 Log: fix i18n Modified: trunk/echobase-ui/src/main/resources/i18n/echobase-ui_en_GB.properties Modified: trunk/echobase-ui/src/main/resources/i18n/echobase-ui_en_GB.properties =================================================================== --- trunk/echobase-ui/src/main/resources/i18n/echobase-ui_en_GB.properties 2013-10-31 23:21:24 UTC (rev 875) +++ trunk/echobase-ui/src/main/resources/i18n/echobase-ui_en_GB.properties 2013-10-31 23:22:48 UTC (rev 876) @@ -40,7 +40,7 @@ echobase.action.toFrench=French echobase.action.usingEnglish=English echobase.action.usingFrench=French -echobase.action.workingDbconfiguration.addSpatial= +echobase.action.workingDbconfiguration.addSpatial=Make database spatial echobase.action.workingDbconfiguration.connect=Connect echobase.action.workingDbconfiguration.disconnect=Disconnect echobase.action.workingDbconfiguration.downloadDriver=Download jdbc driver

1 0

r875 - trunk/echobase-ui/src/main/resources/i18n
by tchemit＠users.forge.codelutin.com 31 Oct '13

31 Oct '13

Author: tchemit Date: 2013-11-01 00:21:24 +0100 (Fri, 01 Nov 2013) New Revision: 875 Url: http://forge.codelutin.com/projects/echobase/repository/revisions/875 Log: fix i18n Modified: trunk/echobase-ui/src/main/resources/i18n/echobase-ui_en_GB.properties Modified: trunk/echobase-ui/src/main/resources/i18n/echobase-ui_en_GB.properties =================================================================== --- trunk/echobase-ui/src/main/resources/i18n/echobase-ui_en_GB.properties 2013-10-31 23:03:35 UTC (rev 874) +++ trunk/echobase-ui/src/main/resources/i18n/echobase-ui_en_GB.properties 2013-10-31 23:21:24 UTC (rev 875) @@ -67,7 +67,6 @@ echobase.common.dataProcessing=DataProcessing echobase.common.dataProcessingNotes=Data processing notes echobase.common.datum=Datum -echobase.common.db.spatialAware= echobase.common.description=Description echobase.common.digitThreshold=Digitization threshold echobase.common.distributionStatement=Distribution statement @@ -139,7 +138,7 @@ echobase.common.soundSpeedCalculationsME70=Sound speed calculation method (ME70 instrument) echobase.common.sounderConstant=Sounder constant (if relevant) echobase.common.source=Source -echobase.common.spatialAware= +echobase.common.spatialAware=Is database spatial aware ? echobase.common.species=Species echobase.common.startEndDate=Start - End date echobase.common.startEndPort=Start - End port @@ -279,7 +278,7 @@ echobase.info.workingDbconfiguration.created=Working database configuration '%s' created echobase.info.workingDbconfiguration.deleted=Working database configuration '%s' deleted echobase.info.workingDbconfiguration.disconnected=Disconnected from working database '%s' -echobase.info.workingDbconfiguration.spatialStructureAdded= +echobase.info.workingDbconfiguration.spatialStructureAdded=Spatial layout added to working database. echobase.information.mission.created=Mission %s created echobase.information.one.file.required=Please provide at least one import file echobase.label.admin.user.create=User creation in process

1 0

r874 - in trunk: . echobase-domain/src/main/java/fr/ifremer/echobase/persistence echobase-ui/src/main/java/fr/ifremer/echobase/ui
by tchemit＠users.forge.codelutin.com 31 Oct '13

31 Oct '13

Author: tchemit Date: 2013-11-01 00:03:35 +0100 (Fri, 01 Nov 2013) New Revision: 874 Url: http://forge.codelutin.com/projects/echobase/repository/revisions/874 Log: fixes #3664: Updates to topia 3.0-alpha-3.1 Modified: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/EchoBaseEntityHelper.java trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/EchoBaseApplicationContext.java trunk/pom.xml 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-10-31 22:58:56 UTC (rev 873) +++ trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/EchoBaseEntityHelper.java 2013-10-31 23:03:35 UTC (rev 874) @@ -37,7 +37,6 @@ import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; -import java.sql.Statement; /** * helper about topia context and jdbc connections. @@ -56,28 +55,6 @@ return tx; } - public static void releaseH2RootContext(TopiaContext context) throws SQLException { - - TopiaContextImplementor rootContext = ((TopiaContextImplementor) context).getRootContext(); - - String url = (String) rootContext.getConfig().get(TopiaContextFactory.CONFIG_URL); - String login = (String) rootContext.getConfig().get(TopiaContextFactory.CONFIG_USER); - String password = (String) rootContext.getConfig().get(TopiaContextFactory.CONFIG_PASS); - - releaseRootContext(context); - - Connection conn = DriverManager.getConnection(url, login, password); - - try { - Statement statement = conn.createStatement(); - statement.execute("SHUTDOWN;"); - statement.close(); - conn.close(); - } finally { - conn.close(); - } - } - public static void releaseRootContext(TopiaContext context) { TopiaContext rootContext = ((TopiaContextImplementor) context).getRootContext(); Modified: trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/EchoBaseApplicationContext.java =================================================================== --- trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/EchoBaseApplicationContext.java 2013-10-31 22:58:56 UTC (rev 873) +++ trunk/echobase-ui/src/main/java/fr/ifremer/echobase/ui/EchoBaseApplicationContext.java 2013-10-31 23:03:35 UTC (rev 874) @@ -64,7 +64,6 @@ import java.io.BufferedWriter; import java.io.File; import java.io.IOException; -import java.sql.SQLException; import java.util.List; import java.util.Locale; import java.util.Map; @@ -280,13 +279,7 @@ if (internalRootContext != null) { // release internal db - try { - EchoBaseEntityHelper.releaseH2RootContext(internalRootContext); - } catch (SQLException e) { - if (log.isErrorEnabled()) { - log.error("Could not close internal db", e); - } - } + EchoBaseEntityHelper.releaseRootContext(internalRootContext); } } finally { Modified: trunk/pom.xml =================================================================== --- trunk/pom.xml 2013-10-31 22:58:56 UTC (rev 873) +++ trunk/pom.xml 2013-10-31 23:03:35 UTC (rev 874) @@ -140,7 +140,7 @@  <eugenePluginVersion>2.7.1</eugenePluginVersion> - <topiaVersion>3.0-alpha-3</topiaVersion> + <topiaVersion>3.0-alpha-3.1</topiaVersion> <nuitonUtilsVersion>2.7</nuitonUtilsVersion> <nuitonI18nVersion>2.5.1</nuitonI18nVersion> <nuitonWebVersion>1.14</nuitonWebVersion>

1 0

r873 - trunk/echobase-services/src/main/resources
by tchemit＠users.forge.codelutin.com 31 Oct '13

31 Oct '13

Author: tchemit Date: 2013-10-31 23:58:56 +0100 (Thu, 31 Oct 2013) New Revision: 873 Url: http://forge.codelutin.com/projects/echobase/repository/revisions/873 Log: fix spatial script if extension already exist on db Modified: trunk/echobase-services/src/main/resources/postgis-structure.sql Modified: trunk/echobase-services/src/main/resources/postgis-structure.sql =================================================================== --- trunk/echobase-services/src/main/resources/postgis-structure.sql 2013-10-31 21:59:27 UTC (rev 872) +++ trunk/echobase-services/src/main/resources/postgis-structure.sql 2013-10-31 22:58:56 UTC (rev 873) @@ -20,7 +20,7 @@ -- along with this program. If not, see <http://www.gnu.org/licenses/>. -- #L% --- -CREATE EXTENSION postgis; +CREATE EXTENSION IF NOT EXISTS postgis; -------------------------------------------------------------------------------- -- Table spatial pour les cellules ---------------------------------------------

1 0

r872 - in trunk: . echobase-domain echobase-services echobase-services/src/main/java/fr/ifremer/echobase/services echobase-services/src/main/java/fr/ifremer/echobase/services/service/embeddedapplication echobase-services/src/main/java/fr/ifremer/echobase/services/service/importdata echobase-services/src/main/java/fr/ifremer/echobase/services/service/importdb echobase-services/src/main/java/fr/ifremer/echobase/services/service/spatial echobase-services/src/main/resources echobase-services/
by tchemit＠users.forge.codelutin.com 31 Oct '13

31 Oct '13

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

1 0

r871 - trunk/src/site
by tchemit＠users.forge.codelutin.com 31 Oct '13

31 Oct '13

Author: tchemit Date: 2013-10-31 19:16:19 +0100 (Thu, 31 Oct 2013) New Revision: 871 Url: http://forge.codelutin.com/projects/echobase/repository/revisions/871 Log: refs #3660: Ajout des donn?\195?\169es spatiales pour les op?\195?\169rations (partie script sql ok) Removed: trunk/src/site/postgis_echobase_geom.sql Modified: trunk/src/site/postgis-structure.sql Modified: trunk/src/site/postgis-structure.sql =================================================================== --- trunk/src/site/postgis-structure.sql 2013-10-30 18:29:22 UTC (rev 870) +++ trunk/src/site/postgis-structure.sql 2013-10-31 18:16:19 UTC (rev 871) @@ -22,7 +22,10 @@ --- CREATE EXTENSION postgis; --- creation table de spatialisation +-------------------------------------------------------------------------------- +-- Table spatial pour les cellules --------------------------------------------- +-------------------------------------------------------------------------------- + DROP TABLE IF EXISTS echobase_cell_spatial; CREATE TABLE echobase_cell_spatial ( voyageid VARCHAR(256) NOT NULL, @@ -57,7 +60,7 @@ 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 +-- pour stoquer les traitements spatiaux a effectuer DROP TABLE IF EXISTS echobase_cell_spatial_temp; CREATE TABLE echobase_cell_spatial_temp ( cellid VARCHAR(256) PRIMARY KEY, @@ -69,7 +72,10 @@ -- Trigger qui met à jour la table des traitements spatiaux à faire -- -CREATE OR REPLACE FUNCTION echobase_fill_spatial_temp_table() +DROP FUNCTION echobase_fill_spatial_temp_table(); +DROP TRIGGER IF EXISTS echobase_fill_spatial_work_table_trigger ON data; + +CREATE OR REPLACE FUNCTION echobase_fill_cell_spatial_temp_table() RETURNS TRIGGER AS $$ DECLARE cell_id VARCHAR; @@ -164,24 +170,24 @@ $$ LANGUAGE 'plpgsql'; -DROP TRIGGER IF EXISTS echobase_fill_spatial_work_table_trigger ON data; -CREATE TRIGGER echobase_fill_spatial_work_table_trigger +DROP TRIGGER IF EXISTS echobase_fill_cell_spatial_work_table_trigger ON data; +CREATE TRIGGER echobase_fill_cell_spatial_work_table_trigger AFTER INSERT OR UPDATE ON data FOR EACH ROW WHEN (NEW.cell IS NOT - NULL) EXECUTE PROCEDURE echobase_fill_spatial_temp_table(); + NULL) EXECUTE PROCEDURE echobase_fill_cell_spatial_temp_table(); -- --- Trigger qui met à jour la table des traitements spatiaux à faire +-- Trigger qui met à jour la table des traitements spatiaux à faire lors d +-- 'une suppression de cellule -- CREATE OR REPLACE FUNCTION echobase_delete_cell() RETURNS TRIGGER AS $$ BEGIN - RAISE DEBUG 'Delete cell % , delete cascade in cell_spatial_table', OLD.topiaid; + RAISE DEBUG 'Delete cell % , delete cascade in echobase_cell_spatial_table', OLD.topiaid; - DELETE FROM echobase_cell_spatial - WHERE cellid = OLD.topiaid; + DELETE FROM echobase_cell_spatial WHERE cellid = OLD.topiaid; RETURN OLD; END @@ -195,21 +201,18 @@ FOR EACH ROW EXECUTE PROCEDURE echobase_delete_cell(); -- --- Mettre a jour toutes les données spatiales +-- Mettre a jour toutes les données spatiales de cellule -- -CREATE OR REPLACE FUNCTION echobase_compute_all_spatial_data() +CREATE OR REPLACE FUNCTION echobase_compute_all_cell_spatial_data() RETURNS VOID AS $$ DECLARE cell_id VARCHAR; BEGIN DELETE FROM echobase_cell_spatial; - FOR cell_id IN SELECT - topiaid - FROM cell LOOP + FOR cell_id IN SELECT topiaid FROM cell LOOP PERFORM echobase_fill_cell_spatial_row(cell_id); END LOOP; - END $$ LANGUAGE plpgsql; @@ -834,6 +837,240 @@ $$ LANGUAGE plpgsql; +-------------------------------------------------------------------------------- +-- Table spatial pour les operations ------------------------------------------- +-------------------------------------------------------------------------------- + +DROP TABLE IF EXISTS echobase_operation_spatial; +CREATE TABLE echobase_operation_spatial ( + voyageid VARCHAR(256) NOT NULL, + voyagename VARCHAR(256), + transitid VARCHAR(256), + transitname VARCHAR(256), + transectid VARCHAR(256), + transectname VARCHAR(256), + operationid VARCHAR(256), + operationname VARCHAR(256), + lastUpdateDate TIMESTAMP NOT NULL, + FOREIGN KEY (operationid) REFERENCES operation (topiaid), + FOREIGN KEY (transectid) REFERENCES transect (topiaid), + FOREIGN KEY (transitid) REFERENCES transit (topiaid), + FOREIGN KEY (voyageid) REFERENCES voyage (topiaid) +); + +SELECT AddGeometryColumn('echobase_operation_spatial', 'coordinate', 4326, 'POINT',2 ); + +CREATE INDEX echobase_operation_spatial_coordinate_gix ON echobase_operation_spatial USING GIST (coordinate); + +-- pour stoquer les traitements spatiaux a effectuer +DROP TABLE IF EXISTS echobase_operation_spatial_temp; +CREATE TABLE echobase_operation_spatial_temp ( + operationid VARCHAR(256) PRIMARY KEY, + FOREIGN KEY (operationid) REFERENCES operation (topiaid) +); + +-- +-- Trigger qui met à jour la table des traitements spatiaux à faire +-- + +CREATE OR REPLACE FUNCTION echobase_fill_operation_spatial_temp_table() + RETURNS TRIGGER AS $$ +DECLARE + operation_id VARCHAR; + doInsert BOOLEAN; + result RECORD; +BEGIN + + IF (TG_OP = 'DELETE') + THEN + result = OLD; + ELSE + result = NEW; + END IF; + + operation_id = result.topiaid; + doInsert = FALSE; + + IF (SELECT count(*) FROM echobase_operation_spatial_temp c + WHERE c.operationid = operation_id) > 0 + THEN +-- operation est deja a traitee + RETURN result; + END IF; +--- Ajout de l'operation dans la table des traitements à effectuer + RAISE DEBUG 'Add operation % to echobase_operation_spatial_temp', operation_id; + INSERT INTO echobase_operation_spatial_temp (operationid) VALUES (operation_id); + RETURN result; +END +$$ +LANGUAGE 'plpgsql'; + +DROP TRIGGER IF EXISTS echobase_fill_operation_spatial_work_table_trigger ON operation; +CREATE TRIGGER echobase_fill_operation_spatial_work_table_trigger +AFTER INSERT OR UPDATE ON operation +FOR EACH ROW WHEN (NEW.topiaid IS NOT + NULL) EXECUTE PROCEDURE echobase_fill_operation_spatial_temp_table(); + +-- +-- Trigger qui met à jour la table des traitements spatiaux à faire lors de la +-- suppression d'une operation +-- + +CREATE OR REPLACE FUNCTION echobase_delete_operation() + RETURNS TRIGGER AS $$ +BEGIN + + RAISE DEBUG 'Delete operation % , delete cascade in echobase_operation_spatial_table', OLD.topiaid; + + DELETE FROM echobase_operation_spatial WHERE operationid = OLD.topiaid; + + RETURN OLD; +END +$$ +LANGUAGE 'plpgsql'; + +DROP TRIGGER IF EXISTS echobase_delete_operation ON operation; + +CREATE TRIGGER echobase_delete_operation +BEFORE DELETE ON operation +FOR EACH ROW EXECUTE PROCEDURE echobase_delete_operation(); + +-- +-- Mettre a jour toutes les données spatiales d'operation +-- + +CREATE OR REPLACE FUNCTION echobase_compute_all_operation_spatial_data() + RETURNS VOID AS $$ +DECLARE operation_id VARCHAR; +BEGIN + DELETE FROM echobase_operation_spatial; + + FOR operation_id IN SELECT topiaid FROM operation LOOP + PERFORM echobase_fill_operation_spatial_row(operation_id); + END LOOP; +END +$$ +LANGUAGE plpgsql; + +-- +-- Mettre a jour la table echobase_cell_spatial depuis echobase_operation_spatial_temp +-- + +CREATE OR REPLACE FUNCTION echobase_fill_operation_spatial_table() + RETURNS VOID AS $$ +DECLARE + operationRow RECORD; +BEGIN + FOR operationRow IN SELECT * FROM echobase_operation_spatial_temp LOOP + + PERFORM echobase_fill_operation_spatial_row(operationRow.operationid); + END LOOP; + + DELETE FROM echobase_operation_spatial_temp; + +END +$$ +LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION echobase_fill_operation_spatial_row(operation_id VARCHAR) + RETURNS VOID AS $$ + DECLARE + spatialText VARCHAR; + latitudeNumber REAL; + longitudeNumber REAL; +BEGIN + RAISE NOTICE 'Treat spatial operation % ...', operation_id; + SELECT o.midHaulLatitude, o.midHaulLongitude + INTO latitudeNumber, longitudeNumber FROM operation o + WHERE o.topiaid = operation_id; + + SELECT + 'SRID=4326;POINT(' || longitudeNumber || ' ' || latitudeNumber || ')' + INTO spatialText; + RAISE DEBUG 'spatial data % for operation %', spatialText, operation_id; + + PERFORM echobase_create_echobase_operation_spatial_row(operation_id, + spatialText); +END +$$ +LANGUAGE plpgsql; + +-- procedure pour creer (ou mettre à jour) une ligne dans echobase_cell_spatial + +CREATE OR REPLACE FUNCTION echobase_create_echobase_operation_spatial_row( + operation_id VARCHAR, + coordinateText VARCHAR) + RETURNS VOID AS $$ +DECLARE + operationSpatialRow RECORD; + voyageId VARCHAR; + voyageName VARCHAR; + transitId VARCHAR; + transitName VARCHAR; + transectId VARCHAR; + transectName VARCHAR; + operationId VARCHAR; + operationName VARCHAR; + operationRow RECORD; + coordinateData GEOMETRY; +BEGIN + IF coordinateText IS NULL + THEN + RAISE DEBUG 'Could not find spatial data for operation %', operation_id; + RETURN; + END IF; + BEGIN + coordinateData = ST_GeomFromEWKT(coordinateText); + EXCEPTION WHEN internal_error + THEN + RAISE LOG 'Could not create coordinate operation spatial data %', coordinateText; + RETURN; + END; + + SELECT * INTO operationSpatialRow FROM echobase_operation_spatial os + WHERE os.operationid = operation_id; + IF NOT FOUND + THEN +-- create row + RAISE DEBUG 'Will create spatial operation %', operation_id; +-- get transect infos + SELECT + t.topiaid, + t.vessel + INTO transectId, transitName + FROM transect t, operation op + WHERE op.topiaid = operation_id AND t.topiaid = op.transect; +-- get transit infos + SELECT + t.topiaid, + (t.starttime || ' - ' || t.endtime) + INTO transitId, transitName + FROM transit t, transect tt + WHERE tt.topiaid = transectId AND t.topiaid = tt.transit; +-- get voyage infos + SELECT + v.topiaid, + v.name + INTO voyageId, voyageName + FROM voyage v, transit t + WHERE t.topiaid = transitId AND v.topiaid = t.voyage; + + INSERT INTO echobase_operation_spatial (lastUpdateDate, voyageid, voyagename, transitid, transitname, transectid, transectname, operationid, operationname, coordinate) + VALUES (now(), voyageId, voyageName, transitId, transitName, transectId, transectName, operation_id, operationName, coordinateData); + ELSE +-- update row + RAISE DEBUG 'Will update spatial operation % ', operation_id; + UPDATE echobase_operation_spatial + SET coordinate = coordinateData + WHERE operationid = operation_id; + END IF; +END +$$ LANGUAGE plpgsql; + +-------------------------------------------------------------------------------- +-------------------------------------------------------------------------------- +-------------------------------------------------------------------------------- + -- pour convertir des latitudes en dms (+ hemi) en dd CREATE OR REPLACE FUNCTION echobase_latitudetext_to_dd(latitude VARCHAR) RETURNS REAL AS $$ @@ -908,6 +1145,25 @@ END; $$ LANGUAGE plpgsql; +-------------------------------------------------------------------------------- +-------------------------------------------------------------------------------- +-------------------------------------------------------------------------------- +-- +-- Mettre a jour toutes les données spatiales +-- + +CREATE OR REPLACE FUNCTION echobase_compute_all_spatial_data() + RETURNS VOID AS $$ +DECLARE cell_id VARCHAR; +BEGIN + EXECUTE echobase_compute_all_operation_spatial_data(); + EXECUTE echobase_compute_all_cell_spatial_data(); +END +$$ +LANGUAGE plpgsql; + + -- Compute all data -SELECT echobase_compute_all_spatial_data(); \ No newline at end of file +SELECT echobase_compute_all_cell_spatial_data(); +SELECT echobase_compute_all_operation_spatial_data(); \ No newline at end of file Deleted: trunk/src/site/postgis_echobase_geom.sql =================================================================== --- trunk/src/site/postgis_echobase_geom.sql 2013-10-30 18:29:22 UTC (rev 870) +++ trunk/src/site/postgis_echobase_geom.sql 2013-10-31 18:16:19 UTC (rev 871) @@ -1,51 +0,0 @@ --- Pour ajouter des champs geometry en plus -SELECT AddGeometryColumn('echobase_cell_spatial', 'coordinate2', 4326, 'POINT',2 ); -SELECT AddGeometryColumn('echobase_cell_spatial', 'coordinate3D2', 4326, 'POINT',3); -SELECT AddGeometryColumn('echobase_cell_spatial', 'shape2', 4326, 'POLYGON',2 ); - -CREATE INDEX idx_echobase_cell_spatial_coordinate2_gist ON echobase_cell_spatial USING GIST (coordinate2); -CREATE INDEX idx_echobase_cell_spatial_coordinate3D2_gist2 ON echobase_cell_spatial USING GIST (coordinate3D2); -CREATE INDEX idx_echobase_cell_spatial_shape2_gist ON echobase_cell_spatial USING GIST (shape2); - -select * from echobase_cell_spatial where celltypename='Map cell'; - -CREATE OR REPLACE FUNCTION echobase_compute_all_spatial_data2() - RETURNS VOID AS $$ -DECLARE - r RECORD; - spatialText VARCHAR; -BEGIN - - -- transform coordinate - FOR r IN SELECT t.cellid, t.coordinate FROM echobase_cell_spatial t WHERE t.coordinate IS NOT NULL LOOP - - spatialText = 'SRID=4326;' || ST_AsText(r.coordinate) ; - RAISE DEBUG 'cell %, coordinate %s', r.cellid, r.coordinate; - RAISE INFO 'cell %, coordinate2 %', r.cellid, spatialText; - UPDATE echobase_cell_spatial t SET coordinate2 = st_geomfromewkt(spatialText) WHERE t.cellid = r.cellid; - END LOOP; - --- transform coordinate3D - FOR r IN SELECT t.cellid, t.coordinate3D FROM echobase_cell_spatial t WHERE t.coordinate3D IS NOT NULL LOOP - - spatialText = 'SRID=4326;' || ST_AsText(r.coordinate3D) ; - RAISE DEBUG 'cell %, coordinate3D %s', r.cellid, r.coordinate3D; - RAISE NOTICE 'cell %, coordinate3D2 %', r.cellid, spatialText; - UPDATE echobase_cell_spatial t SET coordinate3D2 = st_geomfromewkt(spatialText) WHERE t.cellid = r.cellid; - END LOOP; - --- transform shape - FOR r IN SELECT t.cellid, t.shape FROM echobase_cell_spatial t WHERE t.shape IS NOT NULL LOOP - - spatialText = 'SRID=4326;' || ST_AsText(r.shape) ; - RAISE DEBUG 'cell %, shape %', r.cellid, r.shape; - RAISE NOTICE 'cell %, shape2 %', r.cellid, spatialText; - UPDATE echobase_cell_spatial t SET shape2 = st_geomfromewkt(spatialText) WHERE t.cellid = r.cellid; - - END LOOP; - -END -$$ -LANGUAGE plpgsql; - -select echobase_compute_all_spatial_data2(); \ No newline at end of file

1 0

r870 - trunk/src/site
by tchemit＠users.forge.codelutin.com 30 Oct '13

30 Oct '13

Author: tchemit Date: 2013-10-30 19:29:22 +0100 (Wed, 30 Oct 2013) New Revision: 870 Url: http://forge.codelutin.com/projects/echobase/repository/revisions/870 Log: fixes #3652: Utilisation des types geometry Modified: trunk/src/site/postgis-structure.sql Modified: trunk/src/site/postgis-structure.sql =================================================================== --- trunk/src/site/postgis-structure.sql 2013-10-30 09:52:53 UTC (rev 869) +++ trunk/src/site/postgis-structure.sql 2013-10-30 18:29:22 UTC (rev 870) @@ -40,9 +40,6 @@ 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), FOREIGN KEY (cellid) REFERENCES cell (topiaid), FOREIGN KEY (celltypeid) REFERENCES celltype (topiaid), FOREIGN KEY (dataacquisitionid) REFERENCES dataacquisition (topiaid), @@ -51,8 +48,13 @@ FOREIGN KEY (transitid) REFERENCES transit (topiaid), FOREIGN KEY (voyageid) REFERENCES voyage (topiaid) ); + +SELECT AddGeometryColumn('echobase_cell_spatial', 'coordinate', 4326, 'POINT',2 ); +SELECT AddGeometryColumn('echobase_cell_spatial', 'coordinate3d', 4326, 'POINT',3); +SELECT AddGeometryColumn('echobase_cell_spatial', 'shape', 4326, 'POLYGON',2 ); + CREATE INDEX echobase_cell_spatial_coordinate_gix ON echobase_cell_spatial USING GIST (coordinate); -CREATE INDEX echobase_cell_spatial_coordinate3D_gix ON echobase_cell_spatial USING GIST (coordinate3D); +CREATE INDEX echobase_cell_spatial_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 @@ -153,7 +155,7 @@ IF doInsert = TRUE THEN --- Ajout de la cellule dans la table des traitements à effectuer - RAISE NOTICE 'Add cell % [type %] to echobase_cell_spatial_temp', cell_id, cellType; + RAISE DEBUG 'Add cell % [type %] to echobase_cell_spatial_temp', cell_id, cellType; INSERT INTO echobase_cell_spatial_temp (cellid, celltype) VALUES (cell_id, cellType); END IF; @@ -176,7 +178,7 @@ RETURNS TRIGGER AS $$ BEGIN - RAISE NOTICE 'Delete cell % , delete cascade in cell_spatial_table', OLD.topiaid; + RAISE DEBUG 'Delete cell % , delete cascade in cell_spatial_table', OLD.topiaid; DELETE FROM echobase_cell_spatial WHERE cellid = OLD.topiaid; @@ -205,8 +207,7 @@ FOR cell_id IN SELECT topiaid FROM cell LOOP - PERFORM echobase_fill_cell_spatial_row(cell_id, - echobase_get_cell_type(cell_id)); + PERFORM echobase_fill_cell_spatial_row(cell_id); END LOOP; END @@ -221,7 +222,6 @@ RETURNS VOID AS $$ DECLARE cellRow RECORD; - depth RECORD; BEGIN FOR cellRow IN SELECT * @@ -237,12 +237,14 @@ $$ LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION echobase_fill_cell_spatial_row(cell_id VARCHAR, - cell_type VARCHAR) +CREATE OR REPLACE FUNCTION echobase_fill_cell_spatial_row(cell_id VARCHAR) RETURNS VOID AS $$ + DECLARE cell_type VARCHAR; BEGIN - RAISE LOG 'Treat spatial cell % [type %] ...', cell_id, cell_type; + cell_type = echobase_get_cell_type(cell_id); + + RAISE DEBUG 'Treat spatial cell % [type %] ...', cell_id, cell_type; CASE cell_type WHEN 'ESDU' THEN @@ -510,7 +512,7 @@ END IF; SELECT - 'SRID=4326;POINTZ(' || longitudeNumber || ' ' || latitudeNumber || ' ' || + 'SRID=4326;POINT(' || longitudeNumber || ' ' || latitudeNumber || ' ' || depth || ')' INTO spatialText; RAISE DEBUG 'spatial data % for cell %', spatialText, cell_id; @@ -562,9 +564,11 @@ left(spatialText, -1) || '))' INTO spatialText; + SELECT 'SRID=4326;' || spatialText INTO spatialText; + RAISE DEBUG 'spatial data % for cell %', spatialText, cell_id; - PERFORM echobase_create_echobase_cell_spatial_row(cell_id, + PERFORM echobase_create_echobase_cell_spatial_row(cell_id, NULL, NULL, spatialText); @@ -633,7 +637,7 @@ INTO P3; SELECT - 'POLYGON((' || p0 || ',' || P1 || ',' || P2 || ',' || P3 || ',' || P0 || + 'SRID=4326;POLYGON((' || p0 || ',' || P1 || ',' || P2 || ',' || P3 || ',' || P0 || '))' INTO spatialText; RAISE DEBUG 'spatial data % for cell %', spatialText, cell_id; @@ -671,9 +675,9 @@ cellTypeName VARCHAR; cellParentId VARCHAR; cellRow RECORD; - coordinateData GEOGRAPHY; - coordinate3dData GEOGRAPHY; - shapeData GEOGRAPHY; + coordinateData GEOMETRY; + coordinate3dData GEOMETRY; + shapeData GEOMETRY; BEGIN IF coordinateText IS NULL AND coordinate3dText IS NULL AND shapeText IS NULL @@ -684,7 +688,7 @@ IF coordinateText IS NOT NULL THEN BEGIN - coordinateData = ST_GeographyFromText(coordinateText); + coordinateData = ST_GeomFromEWKT(coordinateText); EXCEPTION WHEN internal_error THEN @@ -694,7 +698,7 @@ ELSEIF coordinate3dText IS NOT NULL THEN BEGIN - coordinate3dData = ST_GeographyFromText(coordinate3dText); + coordinate3dData = ST_GeomFromEWKT(coordinate3dText); EXCEPTION WHEN internal_error THEN @@ -704,7 +708,7 @@ ELSEIF shapeText IS NOT NULL THEN BEGIN - shapeData = ST_GeographyFromText(shapeText); + shapeData = ST_GeomFromEWKT(shapeText); EXCEPTION WHEN internal_error THEN @@ -797,7 +801,7 @@ 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) + 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 @@ -902,4 +906,8 @@ RETURN ret; END; -$$ LANGUAGE plpgsql; \ No newline at end of file +$$ LANGUAGE plpgsql; + + +-- Compute all data +SELECT echobase_compute_all_spatial_data(); \ No newline at end of file

1 0

r869 - trunk/src/site
by tchemit＠users.forge.codelutin.com 30 Oct '13

30 Oct '13

Author: tchemit Date: 2013-10-30 10:52:53 +0100 (Wed, 30 Oct 2013) New Revision: 869 Url: http://forge.codelutin.com/projects/echobase/repository/revisions/869 Log: add sql to create geometry data Added: trunk/src/site/postgis_echobase_geom.sql Added: trunk/src/site/postgis_echobase_geom.sql =================================================================== --- trunk/src/site/postgis_echobase_geom.sql (rev 0) +++ trunk/src/site/postgis_echobase_geom.sql 2013-10-30 09:52:53 UTC (rev 869) @@ -0,0 +1,51 @@ +-- Pour ajouter des champs geometry en plus +SELECT AddGeometryColumn('echobase_cell_spatial', 'coordinate2', 4326, 'POINT',2 ); +SELECT AddGeometryColumn('echobase_cell_spatial', 'coordinate3D2', 4326, 'POINT',3); +SELECT AddGeometryColumn('echobase_cell_spatial', 'shape2', 4326, 'POLYGON',2 ); + +CREATE INDEX idx_echobase_cell_spatial_coordinate2_gist ON echobase_cell_spatial USING GIST (coordinate2); +CREATE INDEX idx_echobase_cell_spatial_coordinate3D2_gist2 ON echobase_cell_spatial USING GIST (coordinate3D2); +CREATE INDEX idx_echobase_cell_spatial_shape2_gist ON echobase_cell_spatial USING GIST (shape2); + +select * from echobase_cell_spatial where celltypename='Map cell'; + +CREATE OR REPLACE FUNCTION echobase_compute_all_spatial_data2() + RETURNS VOID AS $$ +DECLARE + r RECORD; + spatialText VARCHAR; +BEGIN + + -- transform coordinate + FOR r IN SELECT t.cellid, t.coordinate FROM echobase_cell_spatial t WHERE t.coordinate IS NOT NULL LOOP + + spatialText = 'SRID=4326;' || ST_AsText(r.coordinate) ; + RAISE DEBUG 'cell %, coordinate %s', r.cellid, r.coordinate; + RAISE INFO 'cell %, coordinate2 %', r.cellid, spatialText; + UPDATE echobase_cell_spatial t SET coordinate2 = st_geomfromewkt(spatialText) WHERE t.cellid = r.cellid; + END LOOP; + +-- transform coordinate3D + FOR r IN SELECT t.cellid, t.coordinate3D FROM echobase_cell_spatial t WHERE t.coordinate3D IS NOT NULL LOOP + + spatialText = 'SRID=4326;' || ST_AsText(r.coordinate3D) ; + RAISE DEBUG 'cell %, coordinate3D %s', r.cellid, r.coordinate3D; + RAISE NOTICE 'cell %, coordinate3D2 %', r.cellid, spatialText; + UPDATE echobase_cell_spatial t SET coordinate3D2 = st_geomfromewkt(spatialText) WHERE t.cellid = r.cellid; + END LOOP; + +-- transform shape + FOR r IN SELECT t.cellid, t.shape FROM echobase_cell_spatial t WHERE t.shape IS NOT NULL LOOP + + spatialText = 'SRID=4326;' || ST_AsText(r.shape) ; + RAISE DEBUG 'cell %, shape %', r.cellid, r.shape; + RAISE NOTICE 'cell %, shape2 %', r.cellid, spatialText; + UPDATE echobase_cell_spatial t SET shape2 = st_geomfromewkt(spatialText) WHERE t.cellid = r.cellid; + + END LOOP; + +END +$$ +LANGUAGE plpgsql; + +select echobase_compute_all_spatial_data2(); \ No newline at end of file

1 0

r868 - trunk/echobase-services/src/test/resources/import-data/result/map
by tchemit＠users.forge.codelutin.com 30 Oct '13

30 Oct '13

Author: tchemit Date: 2013-10-30 10:52:16 +0100 (Wed, 30 Oct 2013) New Revision: 868 Url: http://forge.codelutin.com/projects/echobase/repository/revisions/868 Log: fix test data Modified: trunk/echobase-services/src/test/resources/import-data/result/map/mapsFish.csv.gz Modified: trunk/echobase-services/src/test/resources/import-data/result/map/mapsFish.csv.gz =================================================================== (Binary files differ)

1 0

[ANN] - EchoBase 2.3 released
by forge.codelutin.com Release Notification 29 Oct '13

29 Oct '13

The EchoBase team is pleased to announce the echobase-2.3 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 Simplify log configuration Issue: 3563. Thanks to Tony Chemit. Resolved by tchemit. o Simplifier la procédure d'installation de l'application Issue: 3551. Thanks to Tony Chemit. Resolved by tchemit. Fixed Bugs: o Impossible de cloner une configuration de base Issue: 3565. Thanks to Tony Chemit. Resolved by tchemit. o Problème d'import des données légendaires Issue: 3576. Thanks to Tony Chemit. Resolved by tchemit. o Impossible de créer une application embarquée Issue: 3504. Thanks to Tony Chemit. Resolved by tchemit. o Impossible de redéployer à chaud l'application Issue: 3550. Thanks to Tony Chemit. Resolved by tchemit. Changes: o Améliorer la documentation d'installation Issue: 3553. Thanks to Tony Chemit. Resolved by tchemit. o Updates struts to 2.3.15.3 Issue: 3552. 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-embedded-2.3.zip - http://forge.codelutin.com/attachments/download/1194 * echobase-full-2.3.war - http://forge.codelutin.com/attachments/download/1195 * echobase-referentiel-2.3.echobase - http://forge.codelutin.com/attachments/download/1196 Maven artifacts --------------- Artifacts are deployed in nuiton maven repository http://maven.nuiton.org/other-releases/ Have fun! -EchoBase team

1 0