197 lines
4.4 KiB
SQL
197 lines
4.4 KiB
SQL
--
|
|
-- PostgreSQL database dump
|
|
--
|
|
|
|
--
|
|
-- Name: postgis; Type: EXTENSION; Schema: -; Owner: -
|
|
--
|
|
|
|
CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public;
|
|
|
|
|
|
--
|
|
-- Name: EXTENSION postgis; Type: COMMENT; Schema: -; Owner:
|
|
--
|
|
|
|
COMMENT ON EXTENSION postgis IS 'PostGIS geometry and geography spatial types and functions';
|
|
|
|
|
|
SET default_tablespace = '';
|
|
|
|
SET default_table_access_method = heap;
|
|
|
|
--
|
|
-- Name: boucles; Type: TABLE; Schema: public; Owner: opendata_nm_grafana
|
|
--
|
|
|
|
CREATE TABLE public.boucles (
|
|
id integer NOT NULL,
|
|
name character varying,
|
|
geolocalisation public.geometry(Point)
|
|
);
|
|
|
|
|
|
ALTER TABLE public.boucles OWNER TO opendata_nm_grafana;
|
|
|
|
--
|
|
-- Name: boucles_id_seq; Type: SEQUENCE; Schema: public; Owner: opendata_nm_grafana
|
|
--
|
|
|
|
CREATE SEQUENCE public.boucles_id_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
ALTER TABLE public.boucles_id_seq OWNER TO opendata_nm_grafana;
|
|
|
|
--
|
|
-- Name: boucles_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: opendata_nm_grafana
|
|
--
|
|
|
|
ALTER SEQUENCE public.boucles_id_seq OWNED BY public.boucles.id;
|
|
|
|
|
|
--
|
|
-- Name: comptages; Type: TABLE; Schema: public; Owner: opendata_nm_grafana
|
|
--
|
|
|
|
CREATE TABLE public.comptages (
|
|
id integer NOT NULL,
|
|
id_boucle integer,
|
|
date timestamp with time zone,
|
|
count integer,
|
|
holiday boolean,
|
|
week_day integer
|
|
);
|
|
|
|
|
|
ALTER TABLE public.comptages OWNER TO opendata_nm_grafana;
|
|
|
|
--
|
|
-- Name: comptages_id_seq; Type: SEQUENCE; Schema: public; Owner: opendata_nm_grafana
|
|
--
|
|
|
|
CREATE SEQUENCE public.comptages_id_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
ALTER TABLE public.comptages_id_seq OWNER TO opendata_nm_grafana;
|
|
|
|
--
|
|
-- Name: comptages_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: opendata_nm_grafana
|
|
--
|
|
|
|
ALTER SEQUENCE public.comptages_id_seq OWNED BY public.comptages.id;
|
|
|
|
|
|
--
|
|
-- Name: unaccounted_comptage; Type: TABLE; Schema: public; Owner: opendata_nm_grafana
|
|
--
|
|
|
|
CREATE TABLE public.unaccounted_comptage (
|
|
id integer NOT NULL,
|
|
id_boucle integer,
|
|
date timestamp with time zone,
|
|
count integer,
|
|
holiday boolean,
|
|
week_day integer
|
|
);
|
|
|
|
|
|
ALTER TABLE public.unaccounted_comptage OWNER TO opendata_nm_grafana;
|
|
|
|
--
|
|
-- Name: unaccounted_comptage_id_seq; Type: SEQUENCE; Schema: public; Owner: opendata_nm_grafana
|
|
--
|
|
|
|
CREATE SEQUENCE public.unaccounted_comptage_id_seq
|
|
AS integer
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
NO MINVALUE
|
|
NO MAXVALUE
|
|
CACHE 1;
|
|
|
|
|
|
ALTER TABLE public.unaccounted_comptage_id_seq OWNER TO opendata_nm_grafana;
|
|
|
|
--
|
|
-- Name: unaccounted_comptage_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: opendata_nm_grafana
|
|
--
|
|
|
|
ALTER SEQUENCE public.unaccounted_comptage_id_seq OWNED BY public.unaccounted_comptage.id;
|
|
|
|
|
|
--
|
|
-- Name: boucles id; Type: DEFAULT; Schema: public; Owner: opendata_nm_grafana
|
|
--
|
|
|
|
ALTER TABLE ONLY public.boucles ALTER COLUMN id SET DEFAULT nextval('public.boucles_id_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- Name: comptages id; Type: DEFAULT; Schema: public; Owner: opendata_nm_grafana
|
|
--
|
|
|
|
ALTER TABLE ONLY public.comptages ALTER COLUMN id SET DEFAULT nextval('public.comptages_id_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- Name: unaccounted_comptage id; Type: DEFAULT; Schema: public; Owner: opendata_nm_grafana
|
|
--
|
|
|
|
ALTER TABLE ONLY public.unaccounted_comptage ALTER COLUMN id SET DEFAULT nextval('public.unaccounted_comptage_id_seq'::regclass);
|
|
|
|
|
|
--
|
|
-- Name: boucles boucles_pkey; Type: CONSTRAINT; Schema: public; Owner: opendata_nm_grafana
|
|
--
|
|
|
|
ALTER TABLE ONLY public.boucles
|
|
ADD CONSTRAINT boucles_pkey PRIMARY KEY (id);
|
|
|
|
|
|
--
|
|
-- Name: comptages comptages_pkey; Type: CONSTRAINT; Schema: public; Owner: opendata_nm_grafana
|
|
--
|
|
|
|
ALTER TABLE ONLY public.comptages
|
|
ADD CONSTRAINT comptages_pkey PRIMARY KEY (id);
|
|
|
|
|
|
--
|
|
-- Name: unaccounted_comptage unaccounted_comptage_pkey; Type: CONSTRAINT; Schema: public; Owner: opendata_nm_grafana
|
|
--
|
|
|
|
ALTER TABLE ONLY public.unaccounted_comptage
|
|
ADD CONSTRAINT unaccounted_comptage_pkey PRIMARY KEY (id);
|
|
|
|
|
|
--
|
|
-- Name: idx_boucles_geolocalisation; Type: INDEX; Schema: public; Owner: opendata_nm_grafana
|
|
--
|
|
|
|
CREATE INDEX idx_boucles_geolocalisation ON public.boucles USING gist (geolocalisation);
|
|
|
|
|
|
--
|
|
-- Name: comptages comptages_id_boucle_fkey; Type: FK CONSTRAINT; Schema: public; Owner: opendata_nm_grafana
|
|
--
|
|
|
|
ALTER TABLE ONLY public.comptages
|
|
ADD CONSTRAINT comptages_id_boucle_fkey FOREIGN KEY (id_boucle) REFERENCES public.boucles(id);
|
|
|
|
|
|
--
|
|
-- PostgreSQL database dump complete
|
|
--
|