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