Author: tchemit Date: 2014-02-28 15:08:22 +0100 (Fri, 28 Feb 2014) New Revision: 963 Url: http://codelutin.com/projects/echobase/repository/revisions/963 Log: refs #4194 (finalize spatial views) Added: trunk/echobase-domain/src/main/resources/postgis-structure.sql trunk/echobase-domain/src/main/resources/postgis-view.sql Removed: trunk/echobase-services/src/main/resources/postgis-structure.sql trunk/echobase-services/src/main/resources/postgis-view.sql Modified: trunk/echobase-domain/src/main/resources/migration/postgis-view-2.6.sql Modified: trunk/echobase-domain/src/main/resources/migration/postgis-view-2.6.sql =================================================================== --- trunk/echobase-domain/src/main/resources/migration/postgis-view-2.6.sql 2014-02-27 20:41:19 UTC (rev 962) +++ trunk/echobase-domain/src/main/resources/migration/postgis-view-2.6.sql 2014-02-28 14:08:22 UTC (rev 963) @@ -21,7 +21,7 @@ -- #L% --- ----------------------------------------------------------------------------------------------------------------------- ----- DROP EXISTING VIEW - INDEX - FUNCTION - TRIGGER ------------------------------------------------------------------ +---- DROP EXISTING VIEW - INDEX - FUNCTION ---------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------- DROP VIEW IF EXISTS echobase_cell_spatial_result CASCADE; @@ -68,7 +68,7 @@ DROP FUNCTION IF EXISTS echobase_refresh_views() CASCADE; ----------------------------------------------------------------------------------------------------------------------- ----- CREATE FUNCTION - TRIGGER ---------------------------------------------------------------------------------------- +---- CREATE FUNCTION -------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------- CREATE OR REPLACE FUNCTION echobase_to_numeric(string VARCHAR) @@ -82,24 +82,8 @@ $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION echobase_refresh_views() - RETURNS TRIGGER AS $$ -DECLARE - operationtype VARCHAR; - result RECORD; + RETURNS VOID AS $$ BEGIN - - IF (TG_OP = 'DELETE') - THEN - result = OLD; - ELSE - result = NEW; - END IF; - - operationtype = result.entitytype; - - IF (operationtype ILIKE '%Import%' OR operationtype ILIKE '%Removed%') - THEN - -- Can update views RAISE NOTICE 'reload cellEsduViewEchotype'; REFRESH MATERIALIZED VIEW cellEsduViewEchotype; REINDEX INDEX cellEsduViewEchotype_idx; @@ -131,17 +115,10 @@ REFRESH MATERIALIZED VIEW cellmapview; REINDEX INDEX cellmapview_idx; REINDEX INDEX cellmapview_uidx; - END IF; - - RETURN result; END $$ LANGUAGE 'plpgsql'; -CREATE TRIGGER echobase_refresh_views_trigger -AFTER INSERT ON entitymodificationlog -FOR EACH ROW EXECUTE PROCEDURE echobase_refresh_views(); - ----------------------------------------------------------------------------------------------------------------------- ---- CELL RESULT VIEW ------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------- @@ -150,13 +127,12 @@ SELECT c.voyagename, c.coordinate, - c.shape, c.cellname, d.name, echobase_to_numeric(r.resultvalue) as resultvalue, e.name as echotypeName, e.meaning as echotypeMeaning, - r.topiaid AS resultid + r.topiaid AS id FROM echobase_cell_spatial c, result r, @@ -168,6 +144,7 @@ AND r.datametadata = d.topiaid AND r.category = cat.topiaid AND cat.echotype IS NOT NULL + AND cat.speciescategory IS NULL AND cat.echotype = e.topiaid AND c.celltypename = 'Elementary Distance Sampling Unit'; @@ -181,18 +158,17 @@ WHERE v.name = 'NASC'; -CREATE UNIQUE INDEX cellEsduViewEchotypeNasc_uidx ON cellEsduViewEchotypeNasc(resultid); +CREATE UNIQUE INDEX cellEsduViewEchotypeNasc_uidx ON cellEsduViewEchotypeNasc(id); CREATE MATERIALIZED VIEW cellEsduViewSpecies AS SELECT c.voyagename, c.coordinate, - c.shape, c.cellname, d.name, echobase_to_numeric(r.resultvalue) as resultvalue, s.baracoudacode, - r.topiaid AS resultid + r.topiaid AS id FROM echobase_cell_spatial c, result r, @@ -231,7 +207,11 @@ SELECT distinct v2.coordinate, v2.voyagename, - v.* + v.nbResults, + v.cellname, + v.baracoudacode, + v.resultvalue, + v.id FROM cellEsduViewSpeciesResultGrouped v JOIN cellEsduViewSpecies v2 USING (name, baracoudacode, cellname) @@ -244,7 +224,11 @@ SELECT distinct v2.coordinate, v2.voyagename, - v.* + v.nbResults, + v.cellname, + v.baracoudacode, + v.resultvalue, + v.id FROM cellEsduViewSpeciesResultGrouped v JOIN cellEsduViewSpecies v2 USING (name, baracoudacode, cellname) @@ -257,7 +241,11 @@ SELECT distinct v2.coordinate, v2.voyagename, - v.* + v.nbResults, + v.cellname, + v.baracoudacode, + v.resultvalue / v.nbResults as resultvalue, + v.id FROM cellEsduViewSpeciesResultGrouped v JOIN cellEsduViewSpecies v2 USING (name, baracoudacode, cellname) @@ -267,38 +255,18 @@ CREATE UNIQUE INDEX cellEsduViewSpeciesMeanLength_uidx ON cellEsduViewSpeciesMeanLength(id); ----------------------------------------------------------------------------------------------------------------------- ----- CELL DATA VIEW --------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ - -CREATE OR REPLACE VIEW echobase_cell_spatial_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; - ------------------------------------------------------------------------------------------------------------------------ ---- MAP CELL VIEW ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------- CREATE MATERIALIZED VIEW cellmapview AS SELECT c.voyagename, - c.coordinate, c.shape, c.cellname, d.name, s.baracoudacode, echobase_to_numeric(r.resultvalue) as resultvalue, - r.topiaid AS resultid + r.topiaid AS id FROM echobase_cell_spatial c, result r, @@ -315,7 +283,7 @@ AND c.celltypename = 'Map cell'; CREATE INDEX cellmapview_idx ON cellmapview(baracoudacode, name); -CREATE UNIQUE INDEX cellmapview_uidx ON cellmapview(resultid); +CREATE UNIQUE INDEX cellmapview_uidx ON cellmapview(id); CREATE OR REPLACE VIEW meanMapcellBiomassEngrEnc AS SELECT @@ -470,5 +438,5 @@ (SELECT SUM(t.sampleweight) FROM totalsamplemicrpou t WHERE t.operation_id = e.operationname) AS TotalCatchMICRPOU, (SELECT SUM(t.sampleweight) FROM totalsamplescomsco t WHERE t.operation_id = e.operationname) AS TotalCatchSCOMSCO, (SELECT SUM(t.sampleweight) FROM totalsampletractru t WHERE t.operation_id = e.operationname) AS TotalCatchTRACTRU, - e.operationid + e.operationid as id FROM echobase_operation_spatial e; \ No newline at end of file Copied: trunk/echobase-domain/src/main/resources/postgis-structure.sql (from rev 956, trunk/echobase-services/src/main/resources/postgis-structure.sql) =================================================================== --- trunk/echobase-domain/src/main/resources/postgis-structure.sql (rev 0) +++ trunk/echobase-domain/src/main/resources/postgis-structure.sql 2014-02-28 14:08:22 UTC (rev 963) @@ -0,0 +1,1219 @@ +--- +-- #%L +-- EchoBase :: Domain +-- $Id$ +-- $HeadURL$ +-- %% +-- Copyright (C) 2011 - 2014 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 CASCADE; +DROP VIEW IF EXISTS echobase_cell_spatial_data CASCADE; +DROP VIEW IF EXISTS echobase_cell_spatial_result CASCADE; +DROP VIEW IF EXISTS TotalSampleEngrEnc CASCADE; +DROP VIEW IF EXISTS TotalSampleSardPil CASCADE; +DROP VIEW IF EXISTS TotalSampleTracTru CASCADE; +DROP VIEW IF EXISTS TotalSampleSpraSpr CASCADE; +DROP VIEW IF EXISTS TotalSampleScomSco CASCADE; +DROP VIEW IF EXISTS TotalSampleMicrPou CASCADE; +DROP VIEW IF EXISTS cellmapview CASCADE; + +DROP INDEX IF EXISTS echobase_cell_spatial_coordinate_gix CASCADE; +DROP INDEX IF EXISTS echobase_cell_spatial_coordinate3D_gix CASCADE; +DROP INDEX IF EXISTS echobase_cell_spatial_shape_gix CASCADE; +DROP INDEX IF EXISTS echobase_cell_spatial_voyageid_idx CASCADE; +DROP TABLE IF EXISTS echobase_cell_spatial CASCADE; +DROP TABLE IF EXISTS echobase_cell_spatial_temp CASCADE; + +DROP TRIGGER IF EXISTS echobase_fill_cell_spatial_work_table_trigger ON data CASCADE; +DROP FUNCTION IF EXISTS echobase_fill_spatial_temp_table() CASCADE; +DROP FUNCTION IF EXISTS echobase_fill_cell_spatial_temp_table() CASCADE; +DROP TRIGGER IF EXISTS echobase_delete_cell ON cell CASCADE; +DROP FUNCTION IF EXISTS echobase_delete_cell() CASCADE; +DROP FUNCTION IF EXISTS echobase_compute_all_cell_spatial_data() CASCADE; +DROP FUNCTION IF EXISTS echobase_fill_cell_spatial_table() CASCADE; +DROP FUNCTION IF EXISTS echobase_fill_cell_spatial_row(cell_id VARCHAR) CASCADE; +DROP FUNCTION IF EXISTS echobase_fill_esdu_cell_spatial_table(cell_id VARCHAR) CASCADE; +DROP FUNCTION IF EXISTS echobase_fill_elementary_cell_spatial_table(cell_id VARCHAR) CASCADE; +DROP FUNCTION IF EXISTS echobase_fill_region_cell_spatial_table(cell_id VARCHAR) CASCADE; +DROP FUNCTION IF EXISTS echobase_fill_map_cell_spatial_table(cell_id VARCHAR) CASCADE; +DROP FUNCTION IF EXISTS echobase_create_echobase_cell_spatial_row( +cell_id VARCHAR, +coordinateText VARCHAR, +coordinate3dText VARCHAR, +shapeText VARCHAR) CASCADE; +DROP FUNCTION IF EXISTS echobase_get_cell_type(cell_id VARCHAR) CASCADE; + + +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 CASCADE ; +DROP TABLE IF EXISTS echobase_operation_spatial_temp CASCADE; +DROP TRIGGER IF EXISTS echobase_fill_operation_spatial_work_table_trigger ON operation CASCADE; +DROP FUNCTION IF EXISTS echobase_fill_operation_spatial_temp_table() CASCADE; +DROP TRIGGER IF EXISTS echobase_delete_operation ON operation CASCADE; +DROP FUNCTION IF EXISTS echobase_delete_operation() CASCADE; +DROP FUNCTION IF EXISTS echobase_compute_all_operation_spatial_data() CASCADE; +DROP FUNCTION IF EXISTS echobase_fill_operation_spatial_table() CASCADE; +DROP FUNCTION IF EXISTS echobase_fill_operation_spatial_row(operation_id VARCHAR) CASCADE; +DROP FUNCTION IF EXISTS echobase_create_echobase_operation_spatial_row( +operation_id VARCHAR, +coordinateText VARCHAR) CASCADE; +DROP FUNCTION IF EXISTS echobase_latitudetext_to_dd(latitude VARCHAR) CASCADE; +DROP FUNCTION IF EXISTS echobase_longitudetext_to_dd(longitude VARCHAR) CASCADE; +DROP FUNCTION IF EXISTS echobase_dms2dd(D INTEGER, M INTEGER, S INTEGER, HEMI CHARACTER VARYING(1)) CASCADE; +DROP FUNCTION IF EXISTS echobase_compute_all_spatial_data() CASCADE; + +-------------------------------------------------------------------------------- +-- 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, op.topiaid + INTO operationName, operationId + 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, operationId, operationName, coordinateData); + ELSE +-- update row + RAISE DEBUG 'Will update spatial operation % ', operation_id; + UPDATE echobase_operation_spatial eos + SET eos.coordinate = coordinateData + WHERE eos.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_cell_spatial_data(); + EXECUTE echobase_compute_all_operation_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-domain/src/main/resources/postgis-view.sql (from rev 962, trunk/echobase-services/src/main/resources/postgis-view.sql) =================================================================== --- trunk/echobase-domain/src/main/resources/postgis-view.sql (rev 0) +++ trunk/echobase-domain/src/main/resources/postgis-view.sql 2014-02-28 14:08:22 UTC (rev 963) @@ -0,0 +1,442 @@ +--- +-- #%L +-- EchoBase :: Domain +-- $Id$ +-- $HeadURL$ +-- %% +-- Copyright (C) 2011 - 2014 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 EXISTING VIEW - INDEX - FUNCTION ---------------------------------------------------------------------------- +----------------------------------------------------------------------------------------------------------------------- + +DROP VIEW IF EXISTS echobase_cell_spatial_result CASCADE; +DROP VIEW IF EXISTS cellmapview CASCADE; + +DROP VIEW IF EXISTS echobase_cell_spatial_data CASCADE ; +DROP VIEW IF EXISTS meanMapcellBiomassEngrEnc CASCADE; +DROP VIEW IF EXISTS meanMapcellBiomassSardPil CASCADE; +DROP VIEW IF EXISTS meanMapcellBiomassTracTru CASCADE; +DROP VIEW IF EXISTS meanMapcellBiomassSpraSpr CASCADE; +DROP VIEW IF EXISTS meanMapcellBiomassScomSco CASCADE; +DROP VIEW IF EXISTS meanMapcellBiomassMicrPou CASCADE; +DROP VIEW IF EXISTS TotalCatchSpOpWide CASCADE; +DROP VIEW IF EXISTS TotalSampleEngrEnc CASCADE; +DROP VIEW IF EXISTS TotalSampleSardPil CASCADE; +DROP VIEW IF EXISTS TotalSampleTracTru CASCADE; +DROP VIEW IF EXISTS TotalSampleSpraSpr CASCADE; +DROP VIEW IF EXISTS TotalSampleScomSco CASCADE; +DROP VIEW IF EXISTS TotalSampleMicrPou CASCADE; + +DROP INDEX IF EXISTS cellEsduViewSpeciesBiomass_uidx CASCADE; +DROP INDEX IF EXISTS cellEsduViewSpeciesAbundance_uidx CASCADE; +DROP INDEX IF EXISTS cellEsduViewSpeciesMeanLength_uidx CASCADE; +DROP INDEX IF EXISTS cellEsduViewEchotypeNasc_uidx CASCADE; +DROP INDEX IF EXISTS cellEsduViewEchotype_idx CASCADE; +DROP INDEX IF EXISTS cellEsduViewSpecies_idx CASCADE; +DROP INDEX IF EXISTS cellEsduViewSpeciesResultGrouped_idx CASCADE; +DROP INDEX IF EXISTS cellmapview_idx CASCADE; +DROP INDEX IF EXISTS cellmapview_uidx CASCADE; +DROP INDEX IF EXISTS TotalSampleView_idx CASCADE; + +DROP MATERIALIZED VIEW IF EXISTS cellEsduViewEchotypeNasc CASCADE; +DROP MATERIALIZED VIEW IF EXISTS cellEsduViewSpeciesBiomass CASCADE; +DROP MATERIALIZED VIEW IF EXISTS cellEsduViewSpeciesAbundance CASCADE; +DROP MATERIALIZED VIEW IF EXISTS cellEsduViewSpeciesMeanLength CASCADE; +DROP MATERIALIZED VIEW IF EXISTS cellEsduViewEchotype CASCADE; +DROP MATERIALIZED VIEW IF EXISTS cellEsduViewSpeciesResultGrouped CASCADE; +DROP MATERIALIZED VIEW IF EXISTS cellEsduViewSpecies CASCADE; +DROP MATERIALIZED VIEW IF EXISTS cellmapview CASCADE; +DROP MATERIALIZED VIEW IF EXISTS TotalSampleView CASCADE; + +DROP TRIGGER IF EXISTS echobase_refresh_views_trigger ON entitymodificationlog CASCADE; +DROP FUNCTION IF EXISTS echobase_to_numeric(string VARCHAR) CASCADE; +DROP FUNCTION IF EXISTS echobase_refresh_views() CASCADE; + +----------------------------------------------------------------------------------------------------------------------- +---- CREATE FUNCTION -------------------------------------------------------------------------------------------------- +----------------------------------------------------------------------------------------------------------------------- + +CREATE OR REPLACE FUNCTION echobase_to_numeric(string VARCHAR) + RETURNS REAL AS $$ +BEGIN + RETURN string::real; + EXCEPTION WHEN invalid_text_representation + THEN + RETURN NULL; +END +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION echobase_refresh_views() + RETURNS VOID AS $$ +BEGIN + RAISE NOTICE 'reload cellEsduViewEchotype'; + REFRESH MATERIALIZED VIEW cellEsduViewEchotype; + REINDEX INDEX cellEsduViewEchotype_idx; + + RAISE NOTICE 'reload cellEsduViewSpecies'; + REFRESH MATERIALIZED VIEW cellEsduViewSpecies; + REINDEX INDEX cellEsduViewSpecies_idx; + + RAISE NOTICE 'reload cellEsduViewSpeciesResultGrouped'; + REFRESH MATERIALIZED VIEW cellEsduViewSpeciesResultGrouped; + REINDEX INDEX cellEsduViewSpeciesResultGrouped_idx; + + RAISE NOTICE 'reload cellEsduViewEchotypeNasc'; + REFRESH MATERIALIZED VIEW cellEsduViewEchotypeNasc; + + RAISE NOTICE 'reload cellEsduViewSpeciesBiomass'; + REFRESH MATERIALIZED VIEW cellEsduViewSpeciesBiomass; + REINDEX INDEX cellEsduViewSpeciesBiomass_uidx; + + RAISE NOTICE 'reload cellEsduViewSpeciesAbundance'; + REFRESH MATERIALIZED VIEW cellEsduViewSpeciesAbundance; + REINDEX INDEX cellEsduViewSpeciesAbundance_uidx; + + RAISE NOTICE 'reload cellEsduViewSpeciesMeanLength'; + REFRESH MATERIALIZED VIEW cellEsduViewSpeciesMeanLength; + REINDEX INDEX cellEsduViewSpeciesMeanLength_uidx; + + RAISE NOTICE 'reload cellmapview'; + REFRESH MATERIALIZED VIEW cellmapview; + REINDEX INDEX cellmapview_idx; + REINDEX INDEX cellmapview_uidx; +END +$$ +LANGUAGE 'plpgsql'; + +----------------------------------------------------------------------------------------------------------------------- +---- CELL RESULT VIEW ------------------------------------------------------------------------------------------------- +----------------------------------------------------------------------------------------------------------------------- + +CREATE MATERIALIZED VIEW cellEsduViewEchotype AS + SELECT + c.voyagename, + c.coordinate, + c.cellname, + d.name, + echobase_to_numeric(r.resultvalue) as resultvalue, + e.name as echotypeName, + e.meaning as echotypeMeaning, + r.topiaid AS id + FROM + echobase_cell_spatial c, + result r, + datametadata d, + category cat, + echotype e + WHERE + c.cellid = r.cell + AND r.datametadata = d.topiaid + AND r.category = cat.topiaid + AND cat.echotype IS NOT NULL + AND cat.speciescategory IS NULL + AND cat.echotype = e.topiaid + AND c.celltypename = 'Elementary Distance Sampling Unit'; + +CREATE INDEX cellEsduViewEchotype_idx ON cellEsduViewEchotype(name); + +CREATE MATERIALIZED VIEW cellEsduViewEchotypeNasc AS + SELECT + * + FROM + cellEsduViewEchotype v + WHERE + v.name = 'NASC'; + +CREATE UNIQUE INDEX cellEsduViewEchotypeNasc_uidx ON cellEsduViewEchotypeNasc(id); + +CREATE MATERIALIZED VIEW cellEsduViewSpecies AS + SELECT + c.voyagename, + c.coordinate, + c.cellname, + d.name, + echobase_to_numeric(r.resultvalue) as resultvalue, + s.baracoudacode, + r.topiaid AS id + FROM + echobase_cell_spatial c, + result r, + datametadata d, + category cat, + speciescategory scat, + species s + WHERE + c.cellid = r.cell + AND r.datametadata = d.topiaid + AND r.category = cat.topiaid + AND cat.speciescategory IS NOT NULL + AND cat.speciescategory = scat.topiaid + AND scat.species = s.topiaid + AND c.celltypename = 'Elementary Distance Sampling Unit'; + +CREATE INDEX cellEsduViewSpecies_idx ON cellEsduViewSpecies(name); + +CREATE MATERIALIZED VIEW cellEsduViewSpeciesResultGrouped AS + SELECT + count(*) AS nbResults, + name, + cellname, + baracoudacode, + sum(resultvalue) as resultvalue, + cellname || '-' || baracoudacode || '-' || name as id + FROM cellEsduViewSpecies + WHERE + baracoudacode in ('ENGR-ENC', 'SARD-PIL', 'TRAC-TRU', 'SPRA-SPR', 'SCOM-SCO', 'MICR-POU') + AND name in ('Biomass','Abundance','MeanLength') + GROUP BY baracoudacode, cellname, name; + +CREATE INDEX cellEsduViewSpeciesResultGrouped_idx ON cellEsduViewSpecies(baracoudacode, cellname, name); + +CREATE MATERIALIZED VIEW cellEsduViewSpeciesBiomass AS + SELECT + distinct v2.coordinate, + v2.voyagename, + v.nbResults, + v.cellname, + v.baracoudacode, + v.resultvalue, + v.id + FROM + cellEsduViewSpeciesResultGrouped v + JOIN cellEsduViewSpecies v2 USING (name, baracoudacode, cellname) + WHERE + v.name = 'Biomass'; + +CREATE UNIQUE INDEX cellEsduViewSpeciesBiomass_uidx ON cellEsduViewSpeciesBiomass(id); + +CREATE MATERIALIZED VIEW cellEsduViewSpeciesAbundance AS + SELECT + distinct v2.coordinate, + v2.voyagename, + v.nbResults, + v.cellname, + v.baracoudacode, + v.resultvalue, + v.id + FROM + cellEsduViewSpeciesResultGrouped v + JOIN cellEsduViewSpecies v2 USING (name, baracoudacode, cellname) + WHERE + v.name = 'Abundance'; + +CREATE UNIQUE INDEX cellEsduViewSpeciesAbundance_uidx ON cellEsduViewSpeciesAbundance(id); + +CREATE MATERIALIZED VIEW cellEsduViewSpeciesMeanLength AS + SELECT + distinct v2.coordinate, + v2.voyagename, + v.nbResults, + v.cellname, + v.baracoudacode, + v.resultvalue / v.nbResults as resultvalue, + v.id + FROM + cellEsduViewSpeciesResultGrouped v + JOIN cellEsduViewSpecies v2 USING (name, baracoudacode, cellname) + WHERE + v.name = 'MeanLength'; + +CREATE UNIQUE INDEX cellEsduViewSpeciesMeanLength_uidx ON cellEsduViewSpeciesMeanLength(id); + +----------------------------------------------------------------------------------------------------------------------- +---- MAP CELL VIEW ---------------------------------------------------------------------------------------------------- +----------------------------------------------------------------------------------------------------------------------- + +CREATE MATERIALIZED VIEW cellmapview AS + SELECT + c.voyagename, + c.shape, + c.cellname, + d.name, + s.baracoudacode, + echobase_to_numeric(r.resultvalue) as resultvalue, + r.topiaid AS id + FROM + echobase_cell_spatial c, + result r, + datametadata d, + category cat, + speciescategory scat, + species s + WHERE + c.cellid = r.cell + AND r.datametadata = d.topiaid + AND r.category = cat.topiaid + AND cat.speciescategory = scat.topiaid + AND scat.species = s.topiaid + AND c.celltypename = 'Map cell'; + +CREATE INDEX cellmapview_idx ON cellmapview(baracoudacode, name); +CREATE UNIQUE INDEX cellmapview_uidx ON cellmapview(id); + +CREATE OR REPLACE VIEW meanMapcellBiomassEngrEnc AS + SELECT + * + FROM + cellmapview v + WHERE + v.baracoudacode = 'ENGR-ENC' + AND v.name = 'meanMapcellBiomass'; + +CREATE OR REPLACE VIEW meanMapcellBiomassSardPil AS + SELECT + * + FROM + cellmapview v + WHERE + v.baracoudacode = 'SARD-PIL' + AND v.name = 'meanMapcellBiomass'; + +CREATE OR REPLACE VIEW meanMapcellBiomassTracTru AS + SELECT + * + FROM + cellmapview v + WHERE + v.baracoudacode = 'TRAC-TRU' + AND v.name = 'meanMapcellBiomass'; + +CREATE OR REPLACE VIEW meanMapcellBiomassSpraSpr AS + SELECT + * + FROM + cellmapview v + WHERE + v.baracoudacode = 'SPRA-SPR' + AND v.name = 'meanMapcellBiomass'; + +CREATE OR REPLACE VIEW meanMapcellBiomassScomSco AS + SELECT + * + FROM + cellmapview v + WHERE + v.baracoudacode = 'SCOM-SCO' + AND v.name = 'meanMapcellBiomass'; + +CREATE OR REPLACE VIEW meanMapcellBiomassMicrPou AS + SELECT + * + FROM + cellmapview v + WHERE + v.baracoudacode = 'MICR-POU' + AND v.name = 'meanMapcellBiomass'; + +----------------------------------------------------------------------------------------------------------------------- +---- OPERATION VIEW --------------------------------------------------------------------------------------------------- +----------------------------------------------------------------------------------------------------------------------- + +CREATE MATERIALIZED VIEW TotalSampleView 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; + +CREATE INDEX TotalSampleView_idx ON TotalSampleView(baracoudacode, sampletype_name); + +CREATE OR REPLACE VIEW TotalSampleEngrEnc AS + SELECT + * + FROM + TotalSampleView v + WHERE + v.sampletype_name = 'Total' AND + v.baracoudacode = 'ENGR-ENC'; + +CREATE OR REPLACE VIEW TotalSampleSardPil AS + SELECT + * + FROM + TotalSampleView v + WHERE + v.sampletype_name = 'Total' AND + v.baracoudacode = 'SARD-PIL'; + +CREATE OR REPLACE VIEW TotalSampleTracTru AS + SELECT + * + FROM + TotalSampleView v + WHERE + v.sampletype_name = 'Total' AND + v.baracoudacode = 'TRAC-TRU'; + +CREATE OR REPLACE VIEW TotalSampleSpraSpr AS + SELECT + * + FROM + TotalSampleView v + WHERE + v.sampletype_name = 'Total' AND + v.baracoudacode = 'SPRA-SPR'; + +CREATE OR REPLACE VIEW TotalSampleScomSco AS + SELECT + * + FROM + TotalSampleView v + WHERE + v.sampletype_name = 'Total' AND + v.baracoudacode = 'SCOM-SCO'; + +CREATE OR REPLACE VIEW TotalSampleMicrPou AS + SELECT + * + FROM + TotalSampleView v + WHERE + v.sampletype_name = 'Total' AND + v.baracoudacode = 'MICR-POU'; + +-- Create a view with species catches as columns + +CREATE OR REPLACE VIEW TotalCatchSpOpWide AS + SELECT + e.voyagename, + e.operationname, + e.coordinate, + (SELECT SUM(t.sampleweight) FROM totalsampleengrenc t WHERE t.operation_id = e.operationname) AS TotalCatchENGRENC, + (SELECT SUM(t.sampleweight) FROM totalsamplesardpil t WHERE t.operation_id = e.operationname) AS TotalCatchSARDPIL, + (SELECT SUM(t.sampleweight) FROM totalsamplespraspr t WHERE t.operation_id = e.operationname) AS TotalCatchSPRASPR, + (SELECT SUM(t.sampleweight) FROM totalsamplemicrpou t WHERE t.operation_id = e.operationname) AS TotalCatchMICRPOU, + (SELECT SUM(t.sampleweight) FROM totalsamplescomsco t WHERE t.operation_id = e.operationname) AS TotalCatchSCOMSCO, + (SELECT SUM(t.sampleweight) FROM totalsampletractru t WHERE t.operation_id = e.operationname) AS TotalCatchTRACTRU, + e.operationid as id + FROM echobase_operation_spatial e; \ No newline at end of file Deleted: trunk/echobase-services/src/main/resources/postgis-structure.sql =================================================================== --- trunk/echobase-services/src/main/resources/postgis-structure.sql 2014-02-27 20:41:19 UTC (rev 962) +++ trunk/echobase-services/src/main/resources/postgis-structure.sql 2014-02-28 14:08:22 UTC (rev 963) @@ -1,1218 +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; - -DROP VIEW IF EXISTS TotalCatchSpOpWide CASCADE; -DROP VIEW IF EXISTS echobase_cell_spatial_data CASCADE; -DROP VIEW IF EXISTS echobase_cell_spatial_result CASCADE; -DROP VIEW IF EXISTS TotalSampleEngrEnc CASCADE; -DROP VIEW IF EXISTS TotalSampleSardPil CASCADE; -DROP VIEW IF EXISTS TotalSampleTracTru CASCADE; -DROP VIEW IF EXISTS TotalSampleSpraSpr CASCADE; -DROP VIEW IF EXISTS TotalSampleScomSco CASCADE; -DROP VIEW IF EXISTS TotalSampleMicrPou CASCADE; -DROP VIEW IF EXISTS cellmapview CASCADE; - -DROP INDEX IF EXISTS echobase_cell_spatial_coordinate_gix CASCADE; -DROP INDEX IF EXISTS echobase_cell_spatial_coordinate3D_gix CASCADE; -DROP INDEX IF EXISTS echobase_cell_spatial_shape_gix CASCADE; -DROP INDEX IF EXISTS echobase_cell_spatial_voyageid_idx CASCADE; -DROP TABLE IF EXISTS echobase_cell_spatial CASCADE; -DROP TABLE IF EXISTS echobase_cell_spatial_temp CASCADE; - -DROP TRIGGER IF EXISTS echobase_fill_cell_spatial_work_table_trigger ON data CASCADE; -DROP FUNCTION IF EXISTS echobase_fill_spatial_temp_table() CASCADE; -DROP FUNCTION IF EXISTS echobase_fill_cell_spatial_temp_table() CASCADE; -DROP TRIGGER IF EXISTS echobase_delete_cell ON cell CASCADE; -DROP FUNCTION IF EXISTS echobase_delete_cell() CASCADE; -DROP FUNCTION IF EXISTS echobase_compute_all_cell_spatial_data() CASCADE; -DROP FUNCTION IF EXISTS echobase_fill_cell_spatial_table() CASCADE; -DROP FUNCTION IF EXISTS echobase_fill_cell_spatial_row(cell_id VARCHAR) CASCADE; -DROP FUNCTION IF EXISTS echobase_fill_esdu_cell_spatial_table(cell_id VARCHAR) CASCADE; -DROP FUNCTION IF EXISTS echobase_fill_elementary_cell_spatial_table(cell_id VARCHAR) CASCADE; -DROP FUNCTION IF EXISTS echobase_fill_region_cell_spatial_table(cell_id VARCHAR) CASCADE; -DROP FUNCTION IF EXISTS echobase_fill_map_cell_spatial_table(cell_id VARCHAR) CASCADE; -DROP FUNCTION IF EXISTS echobase_create_echobase_cell_spatial_row( -cell_id VARCHAR, -coordinateText VARCHAR, -coordinate3dText VARCHAR, -shapeText VARCHAR) CASCADE; -DROP FUNCTION IF EXISTS echobase_get_cell_type(cell_id VARCHAR) CASCADE; - - -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 CASCADE ; -DROP TABLE IF EXISTS echobase_operation_spatial_temp CASCADE; -DROP TRIGGER IF EXISTS echobase_fill_operation_spatial_work_table_trigger ON operation CASCADE; -DROP FUNCTION IF EXISTS echobase_fill_operation_spatial_temp_table() CASCADE; -DROP TRIGGER IF EXISTS echobase_delete_operation ON operation CASCADE; -DROP FUNCTION IF EXISTS echobase_delete_operation() CASCADE; -DROP FUNCTION IF EXISTS echobase_compute_all_operation_spatial_data() CASCADE; -DROP FUNCTION IF EXISTS echobase_fill_operation_spatial_table() CASCADE; -DROP FUNCTION IF EXISTS echobase_fill_operation_spatial_row(operation_id VARCHAR) CASCADE; -DROP FUNCTION IF EXISTS echobase_create_echobase_operation_spatial_row( -operation_id VARCHAR, -coordinateText VARCHAR) CASCADE; -DROP FUNCTION IF EXISTS echobase_latitudetext_to_dd(latitude VARCHAR) CASCADE; -DROP FUNCTION IF EXISTS echobase_longitudetext_to_dd(longitude VARCHAR) CASCADE; -DROP FUNCTION IF EXISTS echobase_dms2dd(D INTEGER, M INTEGER, S INTEGER, HEMI CHARACTER VARYING(1)) CASCADE; -DROP FUNCTION IF EXISTS echobase_compute_all_spatial_data() CASCADE; - --------------------------------------------------------------------------------- --- 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, op.topiaid - INTO operationName, operationId - 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, operationId, operationName, coordinateData); - ELSE --- update row - RAISE DEBUG 'Will update spatial operation % ', operation_id; - UPDATE echobase_operation_spatial eos - SET eos.coordinate = coordinateData - WHERE eos.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_cell_spatial_data(); - EXECUTE echobase_compute_all_operation_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 Deleted: trunk/echobase-services/src/main/resources/postgis-view.sql =================================================================== --- trunk/echobase-services/src/main/resources/postgis-view.sql 2014-02-27 20:41:19 UTC (rev 962) +++ trunk/echobase-services/src/main/resources/postgis-view.sql 2014-02-28 14:08:22 UTC (rev 963) @@ -1,476 +0,0 @@ ---- --- #%L --- EchoBase :: Services --- $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 EXISTING VIEW - INDEX - FUNCTION - TRIGGER ------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------ - -DROP VIEW IF EXISTS echobase_cell_spatial_result CASCADE; -DROP VIEW IF EXISTS cellmapview CASCADE; - -DROP VIEW IF EXISTS echobase_cell_spatial_data CASCADE ; -DROP VIEW IF EXISTS meanMapcellBiomassEngrEnc CASCADE; -DROP VIEW IF EXISTS meanMapcellBiomassSardPil CASCADE; -DROP VIEW IF EXISTS meanMapcellBiomassTracTru CASCADE; -DROP VIEW IF EXISTS meanMapcellBiomassSpraSpr CASCADE; -DROP VIEW IF EXISTS meanMapcellBiomassScomSco CASCADE; -DROP VIEW IF EXISTS meanMapcellBiomassMicrPou CASCADE; -DROP VIEW IF EXISTS TotalCatchSpOpWide CASCADE; -DROP VIEW IF EXISTS TotalSampleEngrEnc CASCADE; -DROP VIEW IF EXISTS TotalSampleSardPil CASCADE; -DROP VIEW IF EXISTS TotalSampleTracTru CASCADE; -DROP VIEW IF EXISTS TotalSampleSpraSpr CASCADE; -DROP VIEW IF EXISTS TotalSampleScomSco CASCADE; -DROP VIEW IF EXISTS TotalSampleMicrPou CASCADE; - -DROP INDEX IF EXISTS cellEsduViewSpeciesBiomass_uidx CASCADE; -DROP INDEX IF EXISTS cellEsduViewSpeciesAbundance_uidx CASCADE; -DROP INDEX IF EXISTS cellEsduViewSpeciesMeanLength_uidx CASCADE; -DROP INDEX IF EXISTS cellEsduViewEchotypeNasc_uidx CASCADE; -DROP INDEX IF EXISTS cellEsduViewEchotype_idx CASCADE; -DROP INDEX IF EXISTS cellEsduViewSpecies_idx CASCADE; -DROP INDEX IF EXISTS cellEsduViewSpeciesResultGrouped_idx CASCADE; -DROP INDEX IF EXISTS cellmapview_idx CASCADE; -DROP INDEX IF EXISTS cellmapview_uidx CASCADE; -DROP INDEX IF EXISTS TotalSampleView_idx CASCADE; - -DROP MATERIALIZED VIEW IF EXISTS cellEsduViewEchotypeNasc CASCADE; -DROP MATERIALIZED VIEW IF EXISTS cellEsduViewSpeciesBiomass CASCADE; -DROP MATERIALIZED VIEW IF EXISTS cellEsduViewSpeciesAbundance CASCADE; -DROP MATERIALIZED VIEW IF EXISTS cellEsduViewSpeciesMeanLength CASCADE; -DROP MATERIALIZED VIEW IF EXISTS cellEsduViewEchotype CASCADE; -DROP MATERIALIZED VIEW IF EXISTS cellEsduViewSpeciesResultGrouped CASCADE; -DROP MATERIALIZED VIEW IF EXISTS cellEsduViewSpecies CASCADE; -DROP MATERIALIZED VIEW IF EXISTS cellmapview CASCADE; -DROP MATERIALIZED VIEW IF EXISTS TotalSampleView CASCADE; - -DROP TRIGGER IF EXISTS echobase_refresh_views_trigger ON entitymodificationlog CASCADE; -DROP FUNCTION IF EXISTS echobase_to_numeric(string VARCHAR) CASCADE; -DROP FUNCTION IF EXISTS echobase_refresh_views() CASCADE; - ------------------------------------------------------------------------------------------------------------------------ ----- CREATE FUNCTION - TRIGGER ---------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ - -CREATE OR REPLACE FUNCTION echobase_to_numeric(string VARCHAR) - RETURNS REAL AS $$ -BEGIN - RETURN string::real; - EXCEPTION WHEN invalid_text_representation - THEN - RETURN NULL; -END -$$ LANGUAGE plpgsql; - - -CREATE OR REPLACE FUNCTION echobase_refresh_views() - RETURNS TRIGGER AS $$ -DECLARE - operationtype VARCHAR; - result RECORD; -BEGIN - - IF (TG_OP = 'DELETE') - THEN - result = OLD; - ELSE - result = NEW; - END IF; - - operationtype = result.entitytype; - - IF (operationtype ILIKE '%Import%' OR operationtype ILIKE '%Removed%') - THEN - -- Can update views - RAISE NOTICE 'reload cellEsduViewEchotype'; - REFRESH MATERIALIZED VIEW cellEsduViewEchotype; - REINDEX INDEX cellEsduViewEchotype_idx; - - RAISE NOTICE 'reload cellEsduViewSpecies'; - REFRESH MATERIALIZED VIEW cellEsduViewSpecies; - REINDEX INDEX cellEsduViewSpecies_idx; - - RAISE NOTICE 'reload cellEsduViewSpeciesResultGrouped'; - REFRESH MATERIALIZED VIEW cellEsduViewSpeciesResultGrouped; - REINDEX INDEX cellEsduViewSpeciesResultGrouped_idx; - - RAISE NOTICE 'reload cellEsduViewEchotypeNasc'; - REFRESH MATERIALIZED VIEW cellEsduViewEchotypeNasc; - - RAISE NOTICE 'reload cellEsduViewSpeciesBiomass'; - REFRESH MATERIALIZED VIEW cellEsduViewSpeciesBiomass; - REINDEX INDEX cellEsduViewSpeciesBiomass_uidx; - - RAISE NOTICE 'reload cellEsduViewSpeciesAbundance'; - REFRESH MATERIALIZED VIEW cellEsduViewSpeciesAbundance; - REINDEX INDEX cellEsduViewSpeciesAbundance_uidx; - - RAISE NOTICE 'reload cellEsduViewSpeciesMeanLength'; - REFRESH MATERIALIZED VIEW cellEsduViewSpeciesMeanLength; - REINDEX INDEX cellEsduViewSpeciesMeanLength_uidx; - - RAISE NOTICE 'reload cellmapview'; - REFRESH MATERIALIZED VIEW cellmapview; - REINDEX INDEX cellmapview_idx; - REINDEX INDEX cellmapview_uidx; - END IF; - - RETURN result; -END -$$ -LANGUAGE 'plpgsql'; - -CREATE TRIGGER echobase_refresh_views_trigger -AFTER INSERT OR UPDATE ON entitymodificationlog -FOR EACH ROW EXECUTE PROCEDURE echobase_refresh_views(); - ------------------------------------------------------------------------------------------------------------------------ ----- CELL RESULT VIEW ------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ - -CREATE MATERIALIZED VIEW cellEsduViewEchotype AS - SELECT - c.voyagename, - c.coordinate, - c.shape, - c.cellname, - d.name, - echobase_to_numeric(r.resultvalue) as resultvalue, - e.name as echotypeName, - e.meaning as echotypeMeaning, - r.topiaid AS resultid - FROM - echobase_cell_spatial c, - result r, - datametadata d, - category cat, - echotype e - WHERE - c.cellid = r.cell - AND r.datametadata = d.topiaid - AND r.category = cat.topiaid - AND cat.echotype IS NOT NULL - AND cat.echotype = e.topiaid - AND c.celltypename = 'Elementary Distance Sampling Unit'; - -CREATE INDEX cellEsduViewEchotype_idx ON cellEsduViewEchotype(name); - -CREATE MATERIALIZED VIEW cellEsduViewEchotypeNasc AS - SELECT - * - FROM - cellEsduViewEchotype v - WHERE - v.name = 'NASC'; - -CREATE UNIQUE INDEX cellEsduViewEchotypeNasc_uidx ON cellEsduViewEchotypeNasc(resultid); - -CREATE MATERIALIZED VIEW cellEsduViewSpecies AS - SELECT - c.voyagename, - c.coordinate, - c.shape, - c.cellname, - d.name, - echobase_to_numeric(r.resultvalue) as resultvalue, - s.baracoudacode, - r.topiaid AS resultid - FROM - echobase_cell_spatial c, - result r, - datametadata d, - category cat, - speciescategory scat, - species s - WHERE - c.cellid = r.cell - AND r.datametadata = d.topiaid - AND r.category = cat.topiaid - AND cat.speciescategory IS NOT NULL - AND cat.speciescategory = scat.topiaid - AND scat.species = s.topiaid - AND c.celltypename = 'Elementary Distance Sampling Unit'; - -CREATE INDEX cellEsduViewSpecies_idx ON cellEsduViewSpecies(name); - -CREATE MATERIALIZED VIEW cellEsduViewSpeciesResultGrouped AS - SELECT - count(*) AS nbResults, - name, - cellname, - baracoudacode, - sum(resultvalue) as resultvalue, - cellname || '-' || baracoudacode || '-' || name as id - FROM cellEsduViewSpecies - WHERE - baracoudacode in ('ENGR-ENC', 'SARD-PIL', 'TRAC-TRU', 'SPRA-SPR', 'SCOM-SCO', 'MICR-POU') - AND name in ('Biomass','Abundance','MeanLength') - GROUP BY baracoudacode, cellname, name; - -CREATE INDEX cellEsduViewSpeciesResultGrouped_idx ON cellEsduViewSpecies(baracoudacode, cellname, name); - -CREATE MATERIALIZED VIEW cellEsduViewSpeciesBiomass AS - SELECT - distinct v2.coordinate, - v2.voyagename, - v.* - FROM - cellEsduViewSpeciesResultGrouped v - JOIN cellEsduViewSpecies v2 USING (name, baracoudacode, cellname) - WHERE - v.name = 'Biomass'; - -CREATE UNIQUE INDEX cellEsduViewSpeciesBiomass_uidx ON cellEsduViewSpeciesBiomass(id); - -CREATE MATERIALIZED VIEW cellEsduViewSpeciesAbundance AS - SELECT - distinct v2.coordinate, - v2.voyagename, - v.* - FROM - cellEsduViewSpeciesResultGrouped v - JOIN cellEsduViewSpecies v2 USING (name, baracoudacode, cellname) - WHERE - v.name = 'Abundance'; - -CREATE UNIQUE INDEX cellEsduViewSpeciesAbundance_uidx ON cellEsduViewSpeciesAbundance(id); - -CREATE MATERIALIZED VIEW cellEsduViewSpeciesMeanLength AS - SELECT - distinct v2.coordinate, - v2.voyagename, - v.* - FROM - cellEsduViewSpeciesResultGrouped v - JOIN cellEsduViewSpecies v2 USING (name, baracoudacode, cellname) - WHERE - v.name = 'MeanLength'; - -CREATE UNIQUE INDEX cellEsduViewSpeciesMeanLength_uidx ON cellEsduViewSpeciesMeanLength(id); - ------------------------------------------------------------------------------------------------------------------------ ----- CELL DATA VIEW --------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ - -CREATE OR REPLACE VIEW echobase_cell_spatial_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; - ------------------------------------------------------------------------------------------------------------------------ ----- MAP CELL VIEW ---------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ - -CREATE MATERIALIZED VIEW cellmapview AS - SELECT - c.voyagename, - c.coordinate, - c.shape, - c.cellname, - d.name, - s.baracoudacode, - echobase_to_numeric(r.resultvalue) as resultvalue, - r.topiaid AS resultid - FROM - echobase_cell_spatial c, - result r, - datametadata d, - category cat, - speciescategory scat, - species s - WHERE - c.cellid = r.cell - AND r.datametadata = d.topiaid - AND r.category = cat.topiaid - AND cat.speciescategory = scat.topiaid - AND scat.species = s.topiaid - AND c.celltypename = 'Map cell'; - -CREATE INDEX cellmapview_idx ON cellmapview(baracoudacode, name); -CREATE UNIQUE INDEX cellmapview_uidx ON cellmapview(resultid); - -CREATE OR REPLACE VIEW meanMapcellBiomassEngrEnc AS - SELECT - * - FROM - cellmapview v - WHERE - v.baracoudacode = 'ENGR-ENC' - AND v.name = 'meanMapcellBiomass'; - -CREATE OR REPLACE VIEW meanMapcellBiomassSardPil AS - SELECT - * - FROM - cellmapview v - WHERE - v.baracoudacode = 'SARD-PIL' - AND v.name = 'meanMapcellBiomass'; - -CREATE OR REPLACE VIEW meanMapcellBiomassTracTru AS - SELECT - * - FROM - cellmapview v - WHERE - v.baracoudacode = 'TRAC-TRU' - AND v.name = 'meanMapcellBiomass'; - -CREATE OR REPLACE VIEW meanMapcellBiomassSpraSpr AS - SELECT - * - FROM - cellmapview v - WHERE - v.baracoudacode = 'SPRA-SPR' - AND v.name = 'meanMapcellBiomass'; - -CREATE OR REPLACE VIEW meanMapcellBiomassScomSco AS - SELECT - * - FROM - cellmapview v - WHERE - v.baracoudacode = 'SCOM-SCO' - AND v.name = 'meanMapcellBiomass'; - -CREATE OR REPLACE VIEW meanMapcellBiomassMicrPou AS - SELECT - * - FROM - cellmapview v - WHERE - v.baracoudacode = 'MICR-POU' - AND v.name = 'meanMapcellBiomass'; - ------------------------------------------------------------------------------------------------------------------------ ----- OPERATION VIEW --------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------ - -CREATE MATERIALIZED VIEW TotalSampleView 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; - -CREATE INDEX TotalSampleView_idx ON TotalSampleView(baracoudacode, sampletype_name); - -CREATE OR REPLACE VIEW TotalSampleEngrEnc AS - SELECT - * - FROM - TotalSampleView v - WHERE - v.sampletype_name = 'Total' AND - v.baracoudacode = 'ENGR-ENC'; - -CREATE OR REPLACE VIEW TotalSampleSardPil AS - SELECT - * - FROM - TotalSampleView v - WHERE - v.sampletype_name = 'Total' AND - v.baracoudacode = 'SARD-PIL'; - -CREATE OR REPLACE VIEW TotalSampleTracTru AS - SELECT - * - FROM - TotalSampleView v - WHERE - v.sampletype_name = 'Total' AND - v.baracoudacode = 'TRAC-TRU'; - -CREATE OR REPLACE VIEW TotalSampleSpraSpr AS - SELECT - * - FROM - TotalSampleView v - WHERE - v.sampletype_name = 'Total' AND - v.baracoudacode = 'SPRA-SPR'; - -CREATE OR REPLACE VIEW TotalSampleScomSco AS - SELECT - * - FROM - TotalSampleView v - WHERE - v.sampletype_name = 'Total' AND - v.baracoudacode = 'SCOM-SCO'; - -CREATE OR REPLACE VIEW TotalSampleMicrPou AS - SELECT - * - FROM - TotalSampleView v - WHERE - v.sampletype_name = 'Total' AND - v.baracoudacode = 'MICR-POU'; - --- Create a view with species catches as columns - -CREATE OR REPLACE VIEW TotalCatchSpOpWide AS - SELECT - e.voyagename, - e.operationname, - e.coordinate, - (SELECT SUM(t.sampleweight) FROM totalsampleengrenc t WHERE t.operation_id = e.operationname) AS TotalCatchENGRENC, - (SELECT SUM(t.sampleweight) FROM totalsamplesardpil t WHERE t.operation_id = e.operationname) AS TotalCatchSARDPIL, - (SELECT SUM(t.sampleweight) FROM totalsamplespraspr t WHERE t.operation_id = e.operationname) AS TotalCatchSPRASPR, - (SELECT SUM(t.sampleweight) FROM totalsamplemicrpou t WHERE t.operation_id = e.operationname) AS TotalCatchMICRPOU, - (SELECT SUM(t.sampleweight) FROM totalsamplescomsco t WHERE t.operation_id = e.operationname) AS TotalCatchSCOMSCO, - (SELECT SUM(t.sampleweight) FROM totalsampletractru t WHERE t.operation_id = e.operationname) AS TotalCatchTRACTRU, - e.operationid - FROM echobase_operation_spatial e; \ No newline at end of file