back to saving and loading the database
back to top ..

E:
CD E:\IAV\eda\ireland\elviras_new_files\
shp2pgsql -W LATIN1 -I -s 29903 rock_aquifer_gwchem_ifi.shp ireland.ra>"ireland.ra.sql"
psql -U postgres -d eda2 -h 93.20.247.238 -f "ireland.ra.sql"

alter table ireland.ra drop constraint enforce_srid_the_geom;
update ireland.ra set the_geom=st_transform(the_geom,3035);--26739
alter table ireland.ra add constraint enforce_srid_the_geom CHECK (st_srid(the_geom) = 3035);
-- need to update geometry_column before loading in Qgis, could have done that manually
update geometry_columns set srid=3035 where f_table_name='ra';--1
-- creating a gist index on aquifer
CREATE INDEX indexirelandra ON ireland.ra
  USING GIST ( the_geom GIST_GEOMETRY_OPS ); 
-- renaming column for consistency
select distinct on (gwchem_06) gwchem_06 from ireland.ra;
update ireland.ra set gwchem_06='non-calc' where gwchem_06='NON-Calc';
update ireland.ra set gwchem_06='very-calc' where gwchem_06='Very-Calc';
update ireland.ra set gwchem_06='mod-calc' where gwchem_06='Mod-Calc';

-- here some struggle to try to put at least Irish catchments on my server.
-- downloaded from the ccm Cant read .gdb from home. Tried to get gdal running, latests version unavailable...
-- didn't manage the ogr2ogr from within qgis. Seems fwtools is no longer maintained and shouldn't be downloaded
-- Downloading 0SGEO4W
--  finally trying to restore local version of eda2
-- :-) I now have a localhost version of eda2.

BEGIN;
DROP TABLE IF EXISTS ireland.clippedra;
CREATE TABLE ireland.clippedra AS
SELECT intersected.ragid, intersected.wso1_id, gwchem_06,the_geom
FROM (SELECT ra.gid as ragid, sub1.wso1_id,gwchem_06, ST_Multi(ST_Intersection(ra.the_geom, sub1.the_geom)) the_geom
        FROM  ireland.ra ra
        INNER JOIN (
                SELECT gid, wso1_id,c.the_geom FROM ccm21.catchments c where wso1_id IN (
                        SELECT wso1_id FROM ccm21.catchments c WHERE c.wso_id IN (SELECT wso_id FROM europe.wso WHERE area='Eastern') )
                        
        )AS sub1
        ON  ST_Intersects (sub1.the_geom,ra.the_geom)
       )  AS intersected;    
COMMIT; --
BEGIN;

ALTER TABLE ireland.clippedra ADD column id serial PRIMARY KEY;
alter table ireland.clippedra add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
alter table ireland.clippedra add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL);
alter table ireland.clippedra add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
CREATE INDEX indexclippedra ON ireland.clippedra
  USING GIST ( the_geom GIST_GEOMETRY_OPS ); 
COMMIT;

CREATE INDEX indexwso1clippedra   ON ireland.clippedra   USING btree (wso1_id);


-- RUNNING EVERYTHING WITH BEGIN COMMIT CAUSES CRASH FROM THE SERVER. SO I HAVE SENT BIT BY BIT AND IT WENT SMOOTH & QUICK

INSERT INTO ireland.clippedra
SELECT intersected.ragid, intersected.wso1_id, gwchem_06,the_geom
FROM (SELECT ra.gid as ragid, sub1.wso1_id,gwchem_06, ST_Multi(ST_Intersection(ra.the_geom, sub1.the_geom)) the_geom
        FROM  ireland.ra ra
        INNER JOIN (
                SELECT gid, wso1_id,c.the_geom FROM ccm21.catchments c where wso1_id IN (
                        SELECT wso1_id FROM ccm21.catchments c WHERE c.wso_id IN (SELECT wso_id FROM europe.wso WHERE area='Western') )
                        
        )AS sub1
        ON  ST_Intersects (sub1.the_geom,ra.the_geom)
       )  AS intersected;    --11887

INSERT INTO ireland.clippedra
SELECT intersected.ragid, intersected.wso1_id, gwchem_06,the_geom
FROM (SELECT ra.gid as ragid, sub1.wso1_id,gwchem_06, ST_Multi(ST_Intersection(ra.the_geom, sub1.the_geom)) the_geom
        FROM  ireland.ra ra
        INNER JOIN (
                SELECT gid, wso1_id,c.the_geom FROM ccm21.catchments c where wso1_id IN (
                        SELECT wso1_id FROM ccm21.catchments c WHERE c.wso_id IN (SELECT wso_id FROM europe.wso WHERE area='Neagh Bann') )
                        
        )AS sub1
        ON  ST_Intersects (sub1.the_geom,ra.the_geom)
       )  AS intersected;  --2351  

INSERT INTO ireland.clippedra
SELECT intersected.ragid, intersected.wso1_id, gwchem_06,the_geom
FROM (SELECT ra.gid as ragid, sub1.wso1_id,gwchem_06, ST_Multi(ST_Intersection(ra.the_geom, sub1.the_geom)) the_geom
        FROM  ireland.ra ra
        INNER JOIN (
                SELECT gid, wso1_id,c.the_geom FROM ccm21.catchments c where wso1_id IN (
                        SELECT wso1_id FROM ccm21.catchments c WHERE c.wso_id IN (SELECT wso_id FROM europe.wso WHERE area='North Eastern') )
                        
        )AS sub1
        ON  ST_Intersects (sub1.the_geom,ra.the_geom)
       )  AS intersected;    0

INSERT INTO ireland.clippedra
SELECT intersected.ragid, intersected.wso1_id, gwchem_06,the_geom
FROM (SELECT ra.gid as ragid, sub1.wso1_id,gwchem_06, ST_Multi(ST_Intersection(ra.the_geom, sub1.the_geom)) the_geom
        FROM  ireland.ra ra
        INNER JOIN (
                SELECT gid, wso1_id,c.the_geom FROM ccm21.catchments c where wso1_id IN (
                        SELECT wso1_id FROM ccm21.catchments c WHERE c.wso_id IN (SELECT wso_id FROM europe.wso WHERE area='North Western') )
                        
        )AS sub1
        ON  ST_Intersects (sub1.the_geom,ra.the_geom)
       )  AS intersected;    --7650

INSERT INTO ireland.clippedra
SELECT intersected.ragid, intersected.wso1_id, gwchem_06,the_geom
FROM (SELECT ra.gid as ragid, sub1.wso1_id,gwchem_06, ST_Multi(ST_Intersection(ra.the_geom, sub1.the_geom)) the_geom
        FROM  ireland.ra ra
        INNER JOIN (
                SELECT gid, wso1_id,c.the_geom FROM ccm21.catchments c where wso1_id IN (
                        SELECT wso1_id FROM ccm21.catchments c WHERE c.wso_id IN (SELECT wso_id FROM europe.wso WHERE area='Shannon') )
                        
        )AS sub1
        ON  ST_Intersects (sub1.the_geom,ra.the_geom)
       )  AS intersected;    --10833

INSERT INTO ireland.clippedra
SELECT intersected.ragid, intersected.wso1_id, gwchem_06,the_geom
FROM (SELECT ra.gid as ragid, sub1.wso1_id,gwchem_06, ST_Multi(ST_Intersection(ra.the_geom, sub1.the_geom)) the_geom
        FROM  ireland.ra ra
        INNER JOIN (
                SELECT gid, wso1_id,c.the_geom FROM ccm21.catchments c where wso1_id IN (
                        SELECT wso1_id FROM ccm21.catchments c WHERE c.wso_id IN (SELECT wso_id FROM europe.wso WHERE area='South Eastern') )
                        
        )AS sub1
        ON  ST_Intersects (sub1.the_geom,ra.the_geom)
       )  AS intersected;    --10542

INSERT INTO ireland.clippedra
SELECT intersected.ragid, intersected.wso1_id, gwchem_06,the_geom
FROM (SELECT ra.gid as ragid, sub1.wso1_id,gwchem_06, ST_Multi(ST_Intersection(ra.the_geom, sub1.the_geom)) the_geom
        FROM  ireland.ra ra
        INNER JOIN (
                SELECT gid, wso1_id,c.the_geom FROM ccm21.catchments c where wso1_id IN (
                        SELECT wso1_id FROM ccm21.catchments c WHERE c.wso_id IN (SELECT wso_id FROM europe.wso WHERE area='South Western') )
                        
        )AS sub1
        ON  ST_Intersects (sub1.the_geom,ra.the_geom)
       )  AS intersected;    --6764

--------------------------------------
--------------------------------------
--MERGING
--------------------------------------
--------------------------------------
DROP TABLE IF EXISTS ireland.clippedra1;
CREATE TABLE ireland.clippedra1 AS (
SELECT wso1_id,gwchem_06,
           ST_Multi(ST_Collect(f.the_geom)) as the_geom
         FROM (SELECT wso1_id, gwchem_06,(ST_Dump(the_geom)).geom As the_geom
                                FROM
                                 ireland.clippedra
                                ) As f 
GROUP BY wso1_id,gwchem_06);--11062 10s




ALTER TABLE ireland.clippedra1 add column id serial PRIMARY KEY;
alter table ireland.clippedra1 add CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2);
alter table ireland.clippedra1 add  CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON'::text OR the_geom IS NULL);
alter table ireland.clippedra1 add  CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 3035);
CREATE INDEX indexirelandclippedra1 ON ireland.clippedra1
  USING GIST ( the_geom GIST_GEOMETRY_OPS );
ALTER TABLE ireland.clippedra1 add constraint c_ck_uk_irelandra1  UNIQUE(wso1_id,gwchem_06); -- unique constraint
--------------------------------------
--------------------------------------
--AREA
--------------------------------------
--------------------------------------
ALTER TABLE ireland.clippedra1 add column area numeric;
UPDATE ireland.clippedra1 set area=ST_Area(the_geom); --11062
--------------------------------------
--------------------------------------
--AREA PER COLUMN FOR ireland TYPE (agregation)
--------------------------------------
--------------------------------------
SELECT wso1_id,gwchem_06, id,round(area) as area FROM ireland.clippedra1 order by wso1_id, gwchem_06 limit 10;
DROP TABLE IF EXISTS ireland.surf_area_ra;
CREATE TABLE ireland.surf_area_ra AS (
SELECT DISTINCT ON (init.wso1_id) init.wso1_id,
        non_calc_surf,
        very_calc_surf,
        mod_calc_surf,
        na_calc_surf
         FROM (
        SELECT  wso1_id from ireland.clippedra1    ) as init        
        FULL OUTER JOIN (SELECT wso1_id,sum(area) AS non_calc_surf FROM ireland.clippedra1 WHERE 
                        gwchem_06='non-calc' 
                        GROUP BY wso1_id) AS tab_non_calc
                       on init.wso1_id =tab_non_calc.wso1_id         
         FULL OUTER JOIN (SELECT wso1_id,sum(area) AS very_calc_surf FROM ireland.clippedra1 WHERE 
                        gwchem_06='very-calc' 
                        GROUP BY wso1_id) AS tab_very_calc
                       on init.wso1_id =tab_very_calc.wso1_id
       FULL OUTER JOIN (SELECT wso1_id,sum(area) AS mod_calc_surf FROM ireland.clippedra1 WHERE 
                        gwchem_06='mod-calc' 
                        GROUP BY wso1_id) AS tab_mod_calc
                       on init.wso1_id =tab_mod_calc.wso1_id   
      FULL OUTER JOIN (SELECT wso1_id,sum(area) AS na_calc_surf FROM ireland.clippedra1 WHERE 
                        gwchem_06='n.a.' 
                        GROUP BY wso1_id) AS tab_na_calc
                       on init.wso1_id =tab_na_calc.wso1_id);      --7835    
ALTER TABLE ireland.surf_area_ra ADD CONSTRAINT c_pk_wso1_id_surf_area_ra PRIMARY KEY (wso1_id);
SELECT * FROM ireland.surf_area_ra;
--------------------------------------
--------------------------------------
--REMOVING ZEROS 
--------------------------------------
--------------------------------------
update ireland.surf_area_ra set non_calc_surf =case when non_calc_surf is null then 0 else non_calc_surf/1e6 end; --7835
update ireland.surf_area_ra set very_calc_surf =case when very_calc_surf is null then 0 else very_calc_surf/1e6 end; --7835
update ireland.surf_area_ra set mod_calc_surf =case when mod_calc_surf is null then 0 else mod_calc_surf/1e6 end; --7835
update ireland.surf_area_ra set na_calc_surf =case when na_calc_surf is null then 0 else na_calc_surf/1e6 end; --7835