Author: tchemit Date: 2013-10-30 10:52:53 +0100 (Wed, 30 Oct 2013) New Revision: 869 Url: http://forge.codelutin.com/projects/echobase/repository/revisions/869 Log: add sql to create geometry data Added: trunk/src/site/postgis_echobase_geom.sql Added: trunk/src/site/postgis_echobase_geom.sql =================================================================== --- trunk/src/site/postgis_echobase_geom.sql (rev 0) +++ trunk/src/site/postgis_echobase_geom.sql 2013-10-30 09:52:53 UTC (rev 869) @@ -0,0 +1,51 @@ +-- 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