CREATE DATABASE udig;
CREATE EXTENSION dblink;
CREATE EXTENSION postgis; -- assume 2.0+ for TypMod

-- optional setting: schema
-- CREATE SCHEMA hoge;
-- SET search_path TO hoge, public;

-- connect to remote DB
SELECT dblink_connect('
    host=www.refractions.net
    port=5432
    dbname=demo-bc
    user=demo
    password=demo
');

-- border, 5199 rows
-- this srid is 4326, the others 3005)
CREATE TABLE bc_border AS
SELECT * FROM dblink('SELECT * FROM bc_border') t (
    gid int,
    border_id int,
    the_geom geometry(Linestring, 4326));
ALTER TABLE bc_border ADD PRIMARY KEY (gid);

-- hospitals, 44 rows
CREATE TABLE bc_hospitals AS
SELECT * FROM dblink('SELECT * FROM bc_hospitals') t (
    gid int,
    id int2,
    authority varchar(25),
    name varchar(42),
    the_geom geometry(Point, 3005));
ALTER TABLE bc_hospitals ADD PRIMARY KEY (gid);

-- municipality, 162 rows
CREATE TABLE bc_municipality AS
SELECT * FROM dblink('SELECT * FROM bc_municipality') t (
    gid int,
    code int2,
    name varchar(40),
    the_geom geometry(Polygon, 3005));
ALTER TABLE bc_municipality ADD PRIMARY KEY (gid);

-- pubs, 417 rows
CREATE TABLE bc_pubs AS
SELECT * FROM dblink('SELECT * FROM bc_pubs') t (
    gid int,
    id int2,
    name varchar(55),
    address varchar(47),
    city varchar(20),
    province varchar(17),
    postal varchar(7),
    the_geom geometry(Point, 3005));
ALTER TABLE bc_pubs ADD PRIMARY KEY (gid);

-- voting areas, 7986 rows
CREATE TABLE bc_voting_areas AS
SELECT * FROM dblink('SELECT * FROM bc_voting_areas') t (
    gid int,
    code varchar(3),
    id varchar(8),
    riding varchar(29),
    region varchar(29),
    "number" varchar(4),
    ndp int,
    lib int,
    gp int,
    upbc int,
    vtotal int,
    vreject int,
    vregist int,
    the_geom geometry(Multipolygon, 3005));
ALTER TABLE bc_voting_areas ADD PRIMARY KEY (gid);

-- disconnect from remote DB
SELECT dblink_disconnect();