Generate UK state province layer

UK_state_province_layer.txt

# SHP import to PostGIS

shp2pgsql /Users/mar759/Downloads/bdline_essh_gb/Data/GB/european_region_region.shp scotland.european_region_region > /tmp/uk.sql

# SOL script

DROP TABLE "uk_state_province";
CREATE TABLE "uk_state_province" (gid serial, "name" varchar(60));
ALTER TABLE "uk_state_province" ADD PRIMARY KEY (gid);
SELECT AddGeometryColumn('','uk_state_province','geom','0','MULTIPOLYGON',2);
insert into uk_state_province (name, geom) select err.name, ST_Multi(ST_Union(err.geom)) as geom FROM european_region_region err group by err.name;
update uk_state_province set name='Scotland' where name='Scotland Euro Region';
update uk_state_province set name='Wales' where name='Wales Euro Region';
insert into uk_state_province (name, geom) SELECT 'England', ST_Union(ARRAY(SELECT geom FROM uk_state_province where name like '% Region'));
delete from uk_state_province where name like '% Region';


# PostGIS export to SHP

pgsql2shp scotland uk_state_province