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