--
-- Script SQL pour mabasegeo - tutoriel GeoKettle, OGRS 2009
--

SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;


CREATE TABLE masimpletable (
    id_masimpletable integer NOT NULL,
    nom_client text,
    date_naissance date,
    adresse text,
    montant_achats numeric(10,2)
);


INSERT INTO masimpletable (id_masimpletable, nom_client, date_naissance, adresse, montant_achats) VALUES (1, 'Jacques Duval', '1973-03-23', '64 rue de la Traverse, Québec QC G1K 2L2, Canada', 1437.23);
INSERT INTO masimpletable (id_masimpletable, nom_client, date_naissance, adresse, montant_achats) VALUES (2, 'Marie Lanctot', '1977-04-18', '88 avenue Moustache, 69612 LYON, France', 835.97);
INSERT INTO masimpletable (id_masimpletable, nom_client, date_naissance, adresse, montant_achats) VALUES (3, 'Donald Duck', '1947-05-12', '34 Disney ave, Orlando FL 32801, USA', 175176.76);
INSERT INTO masimpletable (id_masimpletable, nom_client, date_naissance, adresse, montant_achats) VALUES (4, 'Johnny Tremblay', '1955-12-07', '55 rue Sainte-Germaine, Chicoutimi QC G0V 1L3, Canada', 82.17);
INSERT INTO masimpletable (id_masimpletable, nom_client, date_naissance, adresse, montant_achats) VALUES (5, 'Karim Nassam', '1982-11-01', '2853 boul Saint-Laurent, Montréal QC H1R 2N2, Canada', 678.24);
INSERT INTO masimpletable (id_masimpletable, nom_client, date_naissance, adresse, montant_achats) VALUES (6, 'Jacqueline Hébert', '1957-10-05', '785 rue Saint-Louis, Rimouski QC G5L 1N6, Canada', 2854.12);
INSERT INTO masimpletable (id_masimpletable, nom_client, date_naissance, adresse, montant_achats) VALUES (7, 'Maurice Dupont', '1932-02-14', '772 rue du Havre, 35400 SAINT-MALO, France ', 1877.02);


ALTER TABLE ONLY masimpletable
    ADD CONSTRAINT masimpletable_pkey PRIMARY KEY (id_masimpletable);


CREATE TABLE routes_et_divisions (
    geom_route geometry,
    idn text,
    idsegmrte integer NOT NULL,
    datecre text,
    daterev text,
    classroute text,
    numroute1 text,
    numroute2 text,
    numroute3 text,
    nomrte1fr text,
    nomrte2fr text,
    nomrte1an text,
    nomrte2an text,
    numsortie text,
    nbrvoies integer,
    division_code integer NOT NULL,
    division_nom text
);


ALTER TABLE ONLY routes_et_divisions
    ADD CONSTRAINT routes_et_divisions_pkey PRIMARY KEY (idsegmrte, division_code);

	
CREATE TABLE rrn (
    the_geom geometry,
    idn text,
    idsegmrte double precision NOT NULL,
    idintervad text,
    nomjeudonn text,
    versnormes text,
    "precision" double precision,
    techacq text,
    fournissr text,
    datecre text,
    daterev text,
    couvermeta text,
    classroute text,
    numroute1 text,
    numroute2 text,
    numroute3 text,
    numroute4 text,
    numroute5 text,
    nomrte1fr text,
    nomrte2fr text,
    nomrte3fr text,
    nomrte4fr text,
    nomrte1an text,
    nomrte2an text,
    nomrte3an text,
    nomrte4an text,
    numsortie text,
    nbrvoies double precision,
    etatrev text,
    typerev text,
    typenonrev text,
    idstruct text,
    typestruct text,
    nomstrucfr text,
    nomstrucan text,
    adrsens_g text,
    nump_g double precision,
    numd_g double precision,
    nomrue_c_g text,
    nomlieu_g text,
    adrsens_d text,
    nump_d double precision,
    numd_d double precision,
    nomrue_c_d text,
    nomlieu_d text
);


ALTER TABLE ONLY rrn
    ADD CONSTRAINT rrn_pkey PRIMARY KEY (idsegmrte);

	CREATE TABLE rrn_region_quebec (
    the_geom geometry,
    idn text,
    idsegmrte integer NOT NULL,
    datecre text,
    daterev text,
    classroute text,
    numroute1 text,
    numroute2 text,
    numroute3 text,
    nomrte1fr text,
    nomrte2fr text,
    nomrte1an text,
    nomrte2an text,
    numsortie text,
    nbrvoies integer
);


ALTER TABLE ONLY rrn_region_quebec
    ADD CONSTRAINT rrn_region_quebec_pkey PRIMARY KEY (idsegmrte);


INSERT INTO geometry_columns (f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, type) VALUES ('', 'public', 'rrn', 'the_geom', 2, 4140, 'MULTILINESTRING');
INSERT INTO geometry_columns (f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, type) VALUES ('', 'public', 'rrn_region_quebec', 'the_geom', 2, 4140, 'MULTILINESTRING');
INSERT INTO geometry_columns (f_table_catalog, f_table_schema, f_table_name, f_geometry_column, coord_dimension, srid, type) VALUES ('', 'public', 'routes_et_divisions', 'geom_route', 2, 4140, 'MULTILINESTRING');