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