Author: tchemit Date: 2013-12-16 16:16:01 +0100 (Mon, 16 Dec 2013) New Revision: 908 Url: http://forge.codelutin.com/projects/echobase/repository/revisions/908 Log: fixes #4018: Migration de base en erreur Added: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_5_1.java trunk/echobase-services/src/main/resources/postgis-structure.sql trunk/echobase-services/src/main/resources/postgis-view.sql Removed: trunk/echobase-services/src/main/resources/postgis-structure.sql Added: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_5_1.java =================================================================== --- trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_5_1.java (rev 0) +++ trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_5_1.java 2013-12-16 15:16:01 UTC (rev 908) @@ -0,0 +1,57 @@ +package fr.ifremer.echobase.persistence.migration.workingDb; + +import fr.ifremer.echobase.entities.EchoBasePersistenceContext; +import fr.ifremer.echobase.entities.TopiaEchoBasePersistenceContext; +import fr.ifremer.echobase.io.EchoBaseIOUtil; +import org.nuiton.topia.TopiaContext; +import org.nuiton.topia.TopiaException; +import org.nuiton.util.Version; + +import java.util.List; + +/** + * Created on 12/16/13. + * + * @author Tony Chemit <chemit@codelutin.com> + * @since 2.5.1 + */ +public class MigrationCallBackForVersion2_5_1 extends MigrationCallBackForVersion { + + @Override + public Version getVersion() { + return new Version("2.5.1"); + } + + @Override + protected void prepareMigrationScript(TopiaContext tx, + List<String> queries, + boolean showSql, + boolean showProgression) throws TopiaException { + + boolean spatialAware; + + TopiaEchoBasePersistenceContext persistenceContext = + new TopiaEchoBasePersistenceContext(tx); + try { + + spatialAware = persistenceContext.isSpatialAware(); + } finally { + persistenceContext.rollbackTransaction(); + } + + if (spatialAware) { + + // repass postgis scripts (http://forge.codelutin.com/issues/3672) + updatePostgis(queries); + } + } + + protected void updatePostgis(List<String> queries) { + queries.add(EchoBaseIOUtil.loadScript(EchoBasePersistenceContext.POSTGIS_STRUCTURE_SQL)); + queries.add(EchoBaseIOUtil.loadScript(EchoBasePersistenceContext.POSTGIS_VIEW_SQL)); + + // fix http://forge.codelutin.com/issues/3973 + queries.add("update echobase_operation_spatial set operationname = (select op.id from operation op where op.topiaid = operationid);"); + + } +} Property changes on: trunk/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/MigrationCallBackForVersion2_5_1.java ___________________________________________________________________ Added: svn:keywords + Author Date Id Revision Added: svn:eol-style + native Deleted: trunk/echobase-services/src/main/resources/postgis-structure.sql =================================================================== --- trunk/echobase-services/src/main/resources/postgis-structure.sql 2013-12-16 10:40:24 UTC (rev 907) +++ trunk/echobase-services/src/main/resources/postgis-structure.sql 2013-12-16 15:16:01 UTC (rev 908) @@ -1,1219 +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 IF NOT EXISTS 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); - --- ajout de vues - -DROP VIEW IF EXISTS echobase_spatial_cell_result; -CREATE OR REPLACE VIEW echobase_spatial_cell_result AS -SELECT - s.*, - m.topiaId as metaDataId, - m.name as metaDataName, - r.resultvalue as resultValue, - r.topiaid as resultId -FROM - echobase_cell_spatial s, - result r, - datametadata m -WHERE - s.cellid = r.cell AND - r.datametadata = m.topiaid; - -DROP VIEW IF EXISTS echobase_spatial_cell_data; -CREATE OR REPLACE VIEW echobase_spatial_cell_data AS -SELECT - s.*, - m.topiaId as metaDataId, - m.name as metaDataName, - d.datavalue as dataValue, - d.topiaid as dataId -FROM - echobase_cell_spatial s, - data d, - datametadata m -WHERE - s.cellid = d.cell AND - d.datametadata = m.topiaid; - --- 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); - - 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); - --- ajout de vues - -DROP VIEW IF EXISTS echobase_spatial_operation_data; - ---CREATE OR REPLACE VIEW echobase_spatial_operation_data AS ---SELECT --- echobase_cell_spatial.*, --- datametadata.name, --- data.dataValue --- data.topiaid as dataId ---FROM --- echobase_cell_spatial, --- data, --- datametadata ---WHERE --- echobase_cell_spatial.cellid = data.cell AND --- data.datametadata = datametadata.topiaid; - --- 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 Copied: trunk/echobase-services/src/main/resources/postgis-structure.sql (from rev 906, trunk/echobase-services/src/main/resources/postgis-structure.sql) =================================================================== --- trunk/echobase-services/src/main/resources/postgis-structure.sql (rev 0) +++ trunk/echobase-services/src/main/resources/postgis-structure.sql 2013-12-16 15:16:01 UTC (rev 908) @@ -0,0 +1,1224 @@ +--- +-- #%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 IF NOT EXISTS postgis; + +DROP VIEW IF EXISTS TotalCatchSpOpWide; +DROP VIEW IF EXISTS echobase_spatial_cell_result; +DROP VIEW IF EXISTS echobase_spatial_cell_data; +DROP VIEW IF EXISTS TotalSampleEngrEnc; +DROP VIEW IF EXISTS TotalSampleSardPil; +DROP VIEW IF EXISTS TotalSampleTracTru; +DROP VIEW IF EXISTS TotalSampleSpraSpr; +DROP VIEW IF EXISTS TotalSampleScomSco; +DROP VIEW IF EXISTS TotalSampleMicrPou; +DROP VIEW IF EXISTS cellmapview; + +DROP TABLE IF EXISTS echobase_cell_spatial; +DROP INDEX IF EXISTS echobase_cell_spatial_coordinate_gix; +DROP INDEX IF EXISTS echobase_cell_spatial_coordinate3D_gix; +DROP INDEX IF EXISTS echobase_cell_spatial_shape_gix; +DROP INDEX IF EXISTS echobase_cell_spatial_voyageid_idx; +DROP TABLE IF EXISTS echobase_cell_spatial_temp; + +DROP TRIGGER IF EXISTS echobase_fill_cell_spatial_work_table_trigger ON data; +DROP FUNCTION IF EXISTS echobase_fill_spatial_temp_table(); +DROP FUNCTION IF EXISTS echobase_fill_cell_spatial_temp_table(); + +DROP TRIGGER IF EXISTS echobase_delete_cell ON cell; +DROP FUNCTION IF EXISTS echobase_delete_cell(); +DROP FUNCTION IF EXISTS echobase_compute_all_cell_spatial_data(); +DROP FUNCTION IF EXISTS echobase_fill_cell_spatial_table(); +DROP FUNCTION IF EXISTS echobase_fill_cell_spatial_row(cell_id VARCHAR); +DROP FUNCTION IF EXISTS echobase_fill_esdu_cell_spatial_table(cell_id VARCHAR); +DROP FUNCTION IF EXISTS echobase_fill_elementary_cell_spatial_table(cell_id VARCHAR); +DROP FUNCTION IF EXISTS echobase_fill_region_cell_spatial_table(cell_id VARCHAR); +DROP FUNCTION IF EXISTS echobase_fill_map_cell_spatial_table(cell_id VARCHAR); +DROP FUNCTION IF EXISTS echobase_create_echobase_cell_spatial_row( +cell_id VARCHAR, +coordinateText VARCHAR, +coordinate3dText VARCHAR, +shapeText VARCHAR); + +DROP FUNCTION IF EXISTS echobase_get_cell_type(cell_id VARCHAR); + + + +DROP TABLE IF EXISTS echobase_operation_spatial; +DROP INDEX IF EXISTS echobase_operation_spatial_coordinate_gix; +DROP INDEX IF EXISTS echobase_operation_spatial_voyageid_idx; +DROP TABLE IF EXISTS echobase_operation_spatial_temp; +DROP TRIGGER IF EXISTS echobase_fill_operation_spatial_work_table_trigger ON operation; +DROP FUNCTION IF EXISTS echobase_fill_operation_spatial_temp_table(); +DROP TRIGGER IF EXISTS echobase_delete_operation ON operation; +DROP FUNCTION IF EXISTS echobase_delete_operation(); + +DROP FUNCTION IF EXISTS echobase_compute_all_operation_spatial_data(); +DROP FUNCTION IF EXISTS echobase_fill_operation_spatial_table(); +DROP FUNCTION IF EXISTS echobase_fill_operation_spatial_row(operation_id VARCHAR); + +DROP FUNCTION IF EXISTS echobase_create_echobase_operation_spatial_row( +operation_id VARCHAR, +coordinateText VARCHAR); + +DROP FUNCTION IF EXISTS echobase_latitudetext_to_dd(latitude VARCHAR); +DROP FUNCTION IF EXISTS echobase_longitudetext_to_dd(longitude VARCHAR); +DROP FUNCTION IF EXISTS echobase_dms2dd(D INTEGER, M INTEGER, S INTEGER, HEMI CHARACTER VARYING(1)); +DROP FUNCTION IF EXISTS echobase_compute_all_spatial_data(); + +-------------------------------------------------------------------------------- +-- Table spatial pour les cellules --------------------------------------------- +-------------------------------------------------------------------------------- + +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); +CREATE INDEX echobase_cell_spatial_voyageid_idx ON echobase_cell_spatial USING BTREE(voyageid); + +-- pour stoquer les traitements spatiaux a effectuer + +CREATE TABLE echobase_cell_spatial_temp ( + cellid VARCHAR(256) PRIMARY KEY, + celltype VARCHAR(256) NOT NULL, + FOREIGN KEY (cellid) REFERENCES cell (topiaid) +); + +-- +-- Trigger qui met à jour la table des traitements spatiaux à faire +-- + +CREATE OR REPLACE FUNCTION echobase_fill_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'; + +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'; + +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); + + 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 ------------------------------------------- +-------------------------------------------------------------------------------- + +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) PRIMARY KEY, + operationname VARCHAR(256) NOT NULL, + 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); +CREATE INDEX echobase_operation_spatial_voyageid_idx ON echobase_operation_spatial USING BTREE(voyageid); + +-- pour stoquer les traitements spatiaux a effectuer + +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'; + +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'; + +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 operation infos + SELECT op.id + INTO operationName + FROM operation op + WHERE op.topiaid = 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 Added: trunk/echobase-services/src/main/resources/postgis-view.sql =================================================================== --- trunk/echobase-services/src/main/resources/postgis-view.sql (rev 0) +++ trunk/echobase-services/src/main/resources/postgis-view.sql 2013-12-16 15:16:01 UTC (rev 908) @@ -0,0 +1,269 @@ +-- ajout de vues liés aux tables spatiales + +DROP VIEW IF EXISTS echobase_spatial_cell_result; +CREATE OR REPLACE VIEW echobase_spatial_cell_result AS + SELECT + s.*, + m.topiaId AS metaDataId, + m.name AS metaDataName, + r.resultvalue AS resultValue, + r.topiaid AS resultId + FROM + echobase_cell_spatial s, + result r, + datametadata m + WHERE + s.cellid = r.cell AND + r.datametadata = m.topiaid; + +DROP VIEW IF EXISTS echobase_spatial_cell_data; +CREATE OR REPLACE VIEW echobase_spatial_cell_data AS + SELECT + s.*, + m.topiaId AS metaDataId, + m.name AS metaDataName, + d.datavalue AS dataValue, + d.topiaid AS dataId + FROM + echobase_cell_spatial s, + data d, + datametadata m + WHERE + s.cellid = d.cell AND + d.datametadata = m.topiaid; + +DROP VIEW IF EXISTS TotalSampleEngrEnc; +CREATE OR REPLACE VIEW TotalSampleEngrEnc AS + + SELECT + echobase_operation_spatial.voyageid, + operation.id AS operation_id, + echobase_operation_spatial.coordinate, + sampletype.name AS sampletype_name, + sample.sampleweight, + sizecategory.name AS sizecategory_name, + species.baracoudacode, + sample.topiaid + FROM + echobase_operation_spatial, + operation, + sample, + speciescategory, + sizecategory, + species, + sampletype + WHERE + echobase_operation_spatial.operationid = operation.topiaid AND + operation.topiaid = sample.operation AND + sample.speciescategory = speciescategory.topiaid AND + sample.sampletype = sampletype.topiaid AND + speciescategory.sizecategory = sizecategory.topiaid AND + speciescategory.species = species.topiaid AND + sampletype.name = 'Total' AND + species.baracoudacode = 'ENGR-ENC'; + +DROP VIEW IF EXISTS TotalSampleSardPil; +CREATE OR REPLACE VIEW TotalSampleSardPil AS + SELECT + echobase_operation_spatial.voyageid, + operation.id AS operation_id, + echobase_operation_spatial.coordinate, + sampletype.name AS sampletype_name, + sample.sampleweight, + sizecategory.name AS sizecategory_name, + species.baracoudacode, + sample.topiaid + FROM + echobase_operation_spatial, + operation, + sample, + speciescategory, + sizecategory, + species, + sampletype + WHERE + echobase_operation_spatial.operationid = operation.topiaid AND + operation.topiaid = sample.operation AND + sample.speciescategory = speciescategory.topiaid AND + sample.sampletype = sampletype.topiaid AND + speciescategory.sizecategory = sizecategory.topiaid AND + speciescategory.species = species.topiaid AND + sampletype.name = 'Total' AND + species.baracoudacode = 'SARD-PIL'; + +DROP VIEW IF EXISTS TotalSampleTracTru; +CREATE OR REPLACE VIEW TotalSampleTracTru AS + SELECT + echobase_operation_spatial.voyageid, + operation.id AS operation_id, + echobase_operation_spatial.coordinate, + sampletype.name AS sampletype_name, + sample.sampleweight, + sizecategory.name AS sizecategory_name, + species.baracoudacode, + sample.topiaid + FROM + echobase_operation_spatial, + operation, + sample, + speciescategory, + sizecategory, + species, + sampletype + WHERE + echobase_operation_spatial.operationid = operation.topiaid AND + operation.topiaid = sample.operation AND + sample.speciescategory = speciescategory.topiaid AND + sample.sampletype = sampletype.topiaid AND + speciescategory.sizecategory = sizecategory.topiaid AND + speciescategory.species = species.topiaid AND + sampletype.name = 'Total' AND + species.baracoudacode = 'TRAC-TRU'; + +DROP VIEW IF EXISTS TotalSampleSpraSpr; +CREATE OR REPLACE VIEW TotalSampleSpraSpr AS + SELECT + echobase_operation_spatial.voyageid, + operation.id AS operation_id, + echobase_operation_spatial.coordinate, + sampletype.name AS sampletype_name, + sample.sampleweight, + sizecategory.name AS sizecategory_name, + species.baracoudacode, + sample.topiaid + FROM + echobase_operation_spatial, + operation, + sample, + speciescategory, + sizecategory, + species, + sampletype + WHERE + echobase_operation_spatial.operationid = operation.topiaid AND + operation.topiaid = sample.operation AND + sample.speciescategory = speciescategory.topiaid AND + sample.sampletype = sampletype.topiaid AND + speciescategory.sizecategory = sizecategory.topiaid AND + speciescategory.species = species.topiaid AND + sampletype.name = 'Total' AND + species.baracoudacode = 'SPRA-SPR'; + +DROP VIEW IF EXISTS TotalSampleScomSco; +CREATE OR REPLACE VIEW TotalSampleScomSco AS + SELECT + echobase_operation_spatial.voyageid, + operation.id AS operation_id, + echobase_operation_spatial.coordinate, + sampletype.name AS sampletype_name, + sample.sampleweight, + sizecategory.name AS sizecategory_name, + species.baracoudacode, + sample.topiaid + FROM + echobase_operation_spatial, + operation, + sample, + speciescategory, + sizecategory, + species, + sampletype + WHERE + echobase_operation_spatial.operationid = operation.topiaid AND + operation.topiaid = sample.operation AND + sample.speciescategory = speciescategory.topiaid AND + sample.sampletype = sampletype.topiaid AND + speciescategory.sizecategory = sizecategory.topiaid AND + speciescategory.species = species.topiaid AND + sampletype.name = 'Total' AND + species.baracoudacode = 'SCOM-SCO'; + +DROP VIEW IF EXISTS TotalSampleMicrPou; +CREATE OR REPLACE VIEW TotalSampleMicrPou AS + SELECT + echobase_operation_spatial.voyageid, + operation.id AS operation_id, + echobase_operation_spatial.coordinate, + sampletype.name AS sampletype_name, + sample.sampleweight, + sizecategory.name AS sizecategory_name, + species.baracoudacode, + sample.topiaid + FROM + echobase_operation_spatial, + operation, + sample, + speciescategory, + sizecategory, + species, + sampletype + WHERE + echobase_operation_spatial.operationid = operation.topiaid AND + operation.topiaid = sample.operation AND + sample.speciescategory = speciescategory.topiaid AND + sample.sampletype = sampletype.topiaid AND + speciescategory.sizecategory = sizecategory.topiaid AND + speciescategory.species = species.topiaid AND + sampletype.name = 'Total' AND + species.baracoudacode = 'MICR-POU'; + +-- Create a view with species catches as columns + +DROP VIEW IF EXISTS TotalCatchSpOpWide; +CREATE OR REPLACE VIEW TotalCatchSpOpWide AS + SELECT + echobase_operation_spatial.voyagename, + echobase_operation_spatial.operationname, + echobase_operation_spatial.coordinate, + (SELECT + SUM(totalsampleengrenc.sampleweight) + FROM totalsampleengrenc + WHERE totalsampleengrenc.operation_id = + echobase_operation_spatial.operationname) AS TotalCatchENGRENC, + (SELECT + SUM(totalsamplesardpil.sampleweight) + FROM totalsamplesardpil + WHERE totalsamplesardpil.operation_id = + echobase_operation_spatial.operationname) AS TotalCatchSARDPIL, + (SELECT + SUM(totalsamplespraspr.sampleweight) + FROM totalsamplespraspr + WHERE totalsamplespraspr.operation_id = + echobase_operation_spatial.operationname) AS TotalCatchSPRASPR, + (SELECT + SUM(totalsamplemicrpou.sampleweight) + FROM totalsamplemicrpou + WHERE totalsamplemicrpou.operation_id = + echobase_operation_spatial.operationname) AS TotalCatchMICRPOU, + (SELECT + SUM(totalsamplescomsco.sampleweight) + FROM totalsamplescomsco + WHERE totalsamplescomsco.operation_id = + echobase_operation_spatial.operationname) AS TotalCatchSCOMSCO, + (SELECT + SUM(totalsampletractru.sampleweight) + FROM totalsampletractru + WHERE totalsampletractru.operation_id = + echobase_operation_spatial.operationname) AS TotalCatchTRACTRU, + echobase_operation_spatial.operationid + FROM echobase_operation_spatial; + +DROP VIEW IF EXISTS cellmapview; +CREATE OR REPLACE VIEW cellmapview AS + SELECT + echobase_cell_spatial.voyagename, + echobase_cell_spatial.coordinate, + echobase_cell_spatial.shape, + datametadata.name, + result.resultvalue, + species.baracoudacode, + echobase_cell_spatial.cellname, + result.topiaid AS resultid + FROM echobase_cell_spatial, result, datametadata, category, speciescategory, + species + WHERE echobase_cell_spatial.cellid = result.cell + AND result.datametadata = datametadata.topiaid + AND result.category = category.topiaid + AND category.speciescategory = speciescategory.topiaid + AND speciescategory.species = species.topiaid + AND echobase_cell_spatial.celltypename = 'Map cell'; \ No newline at end of file