Water obstructions for Ireland

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

create schema ireland;
comment on schema ireland is '2012 data from IFI and MI of yellow eel survey';

to change the projection system from irish national grid (29903) to ccm (3035)

ALTER TABLE "ireland".salmon_barriers DROP CONSTRAINT enforce_srid_the_geom;
UPDATE "ireland".salmon_barriers SET the_geom = ST_transform(the_geom, 3035); 
ALTER TABLE "ireland".salmon_barriers ADD CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 3035);

to join the obstructions to a river segment in ccm

DROP TABLE IF EXISTS ireland.salmonbarriers_ccm_300;
CREATE TABLE ireland.salmonbarriers_ccm_300 as (
select distinct on (objectid) objectid, min(distance) as distance,height, score,nbdams ,wso1_id, gid from (
        SELECT objectid,
                wso1_id,
                r.gid,
                distance(r.the_geom, b.the_geom), 
                0 as height,
                1 as nbdams,
                0 as score
                from ireland.salmon_barriers b join ccm21.riversegments r 
                ON ST_DWithin(r.the_geom, b.the_geom,300) 
        order by objectid) as sub
        group by objectid,distance,wso1_id,gid, height, score,nbdams
);