back to saving and loading the database with elvira?
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);