diff options
Diffstat (limited to 'schema.sql')
| -rw-r--r-- | schema.sql | 501 |
1 files changed, 501 insertions, 0 deletions
diff --git a/schema.sql b/schema.sql new file mode 100644 index 0000000..3cb7d18 --- /dev/null +++ b/schema.sql @@ -0,0 +1,501 @@ +-- +-- PostgreSQL database dump +-- + +-- Dumped from database version 15.6 (Debian 15.6-0+deb12u1) +-- Dumped by pg_dump version 15.6 (Debian 15.6-0+deb12u1) + +SET statement_timeout = 0; +SET lock_timeout = 0; +SET idle_in_transaction_session_timeout = 0; +SET client_encoding = 'UTF8'; +SET standard_conforming_strings = on; +SELECT pg_catalog.set_config('search_path', '', false); +SET check_function_bodies = false; +SET xmloption = content; +SET client_min_messages = warning; +SET row_security = off; + +DROP DATABASE webmap; +-- +-- Name: webmap; Type: DATABASE; Schema: -; Owner: postgres +-- + +CREATE DATABASE webmap WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = icu LOCALE = 'sv_SE.UTF-8' ICU_LOCALE = 'sv-SE-x-icu'; + + +ALTER DATABASE webmap OWNER TO postgres; + +\connect webmap + +SET statement_timeout = 0; +SET lock_timeout = 0; +SET idle_in_transaction_session_timeout = 0; +SET client_encoding = 'UTF8'; +SET standard_conforming_strings = on; +SELECT pg_catalog.set_config('search_path', '', false); +SET check_function_bodies = false; +SET xmloption = content; +SET client_min_messages = warning; +SET row_security = off; + +-- +-- Name: DATABASE webmap; Type: COMMENT; Schema: -; Owner: postgres +-- + +COMMENT ON DATABASE webmap IS 'Backend PostGIS database for KlimatanalysNorr tooling'; + + +-- +-- Name: postgis; Type: SCHEMA; Schema: -; Owner: postgres +-- + +CREATE SCHEMA postgis; + + +ALTER SCHEMA postgis OWNER TO postgres; + +-- +-- Name: postgis; Type: EXTENSION; Schema: -; Owner: - +-- + +CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA postgis; + + +-- +-- Name: EXTENSION postgis; Type: COMMENT; Schema: -; Owner: +-- + +COMMENT ON EXTENSION postgis IS 'Geographic objects support for PostgreSQL'; + + +SET default_tablespace = ''; + +SET default_table_access_method = heap; + +-- +-- Name: kommunyta; Type: TABLE; Schema: postgis; Owner: webmap_import +-- + +CREATE TABLE postgis.kommunyta ( + ogc_fid bigint NOT NULL, + objektidentitet uuid, + skapad timestamp without time zone, + kommunkod smallint NOT NULL, + wkb_geometry postgis.geometry(MultiPolygon,3006) +); + + +ALTER TABLE postgis.kommunyta OWNER TO webmap_import; + +-- +-- Name: TABLE kommunyta; Type: COMMENT; Schema: postgis; Owner: webmap_import +-- + +COMMENT ON TABLE postgis.kommunyta IS 'Sveriges kommuner (Lantmäteriet)'; + + +-- +-- Name: kommunyta_ogc_fid_seq; Type: SEQUENCE; Schema: postgis; Owner: webmap_import +-- + +CREATE SEQUENCE postgis.kommunyta_ogc_fid_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE postgis.kommunyta_ogc_fid_seq OWNER TO webmap_import; + +-- +-- Name: kommunyta_ogc_fid_seq; Type: SEQUENCE OWNED BY; Schema: postgis; Owner: webmap_import +-- + +ALTER SEQUENCE postgis.kommunyta_ogc_fid_seq OWNED BY postgis.kommunyta.ogc_fid; + + +-- +-- Name: lansyta; Type: TABLE; Schema: postgis; Owner: webmap_import +-- + +CREATE TABLE postgis.lansyta ( + ogc_fid bigint NOT NULL, + objektidentitet uuid, + skapad timestamp without time zone, + lanskod smallint NOT NULL, + wkb_geometry postgis.geometry(MultiPolygon,3006) +); + + +ALTER TABLE postgis.lansyta OWNER TO webmap_import; + +-- +-- Name: TABLE lansyta; Type: COMMENT; Schema: postgis; Owner: webmap_import +-- + +COMMENT ON TABLE postgis.lansyta IS 'Sveriges län (Lantmäteriet)'; + + +-- +-- Name: lansyta_ogc_fid_seq; Type: SEQUENCE; Schema: postgis; Owner: webmap_import +-- + +CREATE SEQUENCE postgis.lansyta_ogc_fid_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE postgis.lansyta_ogc_fid_seq OWNER TO webmap_import; + +-- +-- Name: lansyta_ogc_fid_seq; Type: SEQUENCE OWNED BY; Schema: postgis; Owner: webmap_import +-- + +ALTER SEQUENCE postgis.lansyta_ogc_fid_seq OWNED BY postgis.lansyta.ogc_fid; + + +-- +-- Name: sks:AvverkAnm; Type: TABLE; Schema: postgis; Owner: webmap_import +-- + +CREATE TABLE postgis."sks:AvverkAnm" ( + ogc_fid bigint NOT NULL, + "OBJECTID" integer NOT NULL, + "Beteckn" character(12) NOT NULL, + "ArendeAr" smallint NOT NULL, + "Avverktyp" character varying(254) NOT NULL, + "Skogstyp" character varying(254) NOT NULL, + "Inkomdatum" date NOT NULL, + "AnmaldHa" real NOT NULL, + "SkogsodlHa" real NOT NULL, + "NatforHa" real NOT NULL, + "AvvSasong" character varying(254) NOT NULL, + "ArendeStat" character varying(254) NOT NULL, + "AvvHa" real, + "Avverkning" character varying(254) NOT NULL, + wkb_geometry postgis.geometry(MultiPolygon,3006) +); + + +ALTER TABLE postgis."sks:AvverkAnm" OWNER TO webmap_import; + +-- +-- Name: TABLE "sks:AvverkAnm"; Type: COMMENT; Schema: postgis; Owner: webmap_import +-- + +COMMENT ON TABLE postgis."sks:AvverkAnm" IS 'Avverkningsanmälningar (Skogsstyrelsen)'; + + +-- +-- Name: sks:AvverkAnm_ogc_fid_seq; Type: SEQUENCE; Schema: postgis; Owner: webmap_import +-- + +CREATE SEQUENCE postgis."sks:AvverkAnm_ogc_fid_seq" + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE postgis."sks:AvverkAnm_ogc_fid_seq" OWNER TO webmap_import; + +-- +-- Name: sks:AvverkAnm_ogc_fid_seq; Type: SEQUENCE OWNED BY; Schema: postgis; Owner: webmap_import +-- + +ALTER SEQUENCE postgis."sks:AvverkAnm_ogc_fid_seq" OWNED BY postgis."sks:AvverkAnm".ogc_fid; + + +-- +-- Name: sks:UtfordAvverk; Type: TABLE; Schema: postgis; Owner: webmap_import +-- + +CREATE TABLE postgis."sks:UtfordAvverk" ( + ogc_fid bigint NOT NULL, + "OBJECTID" integer NOT NULL, + "Beteckn" character(12) NOT NULL, + "ArendeAr" smallint NOT NULL, + "Avverktyp" character varying(254) NOT NULL, + "Skogstyp" character varying(254) NOT NULL, + "AnmaldHa" real, + "SkogsodlHa" real NOT NULL, + "NatforHa" real NOT NULL, + "Avvdatum" date NOT NULL, + "KallaDatum" date, + "KallaAreal" character varying(62), + "Forebild" character varying(62), + "Efterbild" character varying(62), + "ArealHa" real NOT NULL, + wkb_geometry postgis.geometry(MultiPolygon,3006) +); + + +ALTER TABLE postgis."sks:UtfordAvverk" OWNER TO webmap_import; + +-- +-- Name: TABLE "sks:UtfordAvverk"; Type: COMMENT; Schema: postgis; Owner: webmap_import +-- + +COMMENT ON TABLE postgis."sks:UtfordAvverk" IS 'Utförd avverkning (Skogsstyrelsen)'; + + +-- +-- Name: sks:UtfordAvverk_ogc_fid_seq; Type: SEQUENCE; Schema: postgis; Owner: webmap_import +-- + +CREATE SEQUENCE postgis."sks:UtfordAvverk_ogc_fid_seq" + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE postgis."sks:UtfordAvverk_ogc_fid_seq" OWNER TO webmap_import; + +-- +-- Name: sks:UtfordAvverk_ogc_fid_seq; Type: SEQUENCE OWNED BY; Schema: postgis; Owner: webmap_import +-- + +ALTER SEQUENCE postgis."sks:UtfordAvverk_ogc_fid_seq" OWNED BY postgis."sks:UtfordAvverk".ogc_fid; + + +-- +-- Name: kommunyta ogc_fid; Type: DEFAULT; Schema: postgis; Owner: webmap_import +-- + +ALTER TABLE ONLY postgis.kommunyta ALTER COLUMN ogc_fid SET DEFAULT nextval('postgis.kommunyta_ogc_fid_seq'::regclass); + + +-- +-- Name: lansyta ogc_fid; Type: DEFAULT; Schema: postgis; Owner: webmap_import +-- + +ALTER TABLE ONLY postgis.lansyta ALTER COLUMN ogc_fid SET DEFAULT nextval('postgis.lansyta_ogc_fid_seq'::regclass); + + +-- +-- Name: sks:AvverkAnm ogc_fid; Type: DEFAULT; Schema: postgis; Owner: webmap_import +-- + +ALTER TABLE ONLY postgis."sks:AvverkAnm" ALTER COLUMN ogc_fid SET DEFAULT nextval('postgis."sks:AvverkAnm_ogc_fid_seq"'::regclass); + + +-- +-- Name: sks:UtfordAvverk ogc_fid; Type: DEFAULT; Schema: postgis; Owner: webmap_import +-- + +ALTER TABLE ONLY postgis."sks:UtfordAvverk" ALTER COLUMN ogc_fid SET DEFAULT nextval('postgis."sks:UtfordAvverk_ogc_fid_seq"'::regclass); + + +-- +-- Name: kommunyta kommunyta_kommunkod_key; Type: CONSTRAINT; Schema: postgis; Owner: webmap_import +-- + +ALTER TABLE ONLY postgis.kommunyta + ADD CONSTRAINT kommunyta_kommunkod_key UNIQUE (kommunkod); + + +-- +-- Name: kommunyta kommunyta_objektidentitet_key; Type: CONSTRAINT; Schema: postgis; Owner: webmap_import +-- + +ALTER TABLE ONLY postgis.kommunyta + ADD CONSTRAINT kommunyta_objektidentitet_key UNIQUE (objektidentitet); + + +-- +-- Name: kommunyta kommunyta_pkey; Type: CONSTRAINT; Schema: postgis; Owner: webmap_import +-- + +ALTER TABLE ONLY postgis.kommunyta + ADD CONSTRAINT kommunyta_pkey PRIMARY KEY (ogc_fid); + + +-- +-- Name: lansyta lansyta_lanskod_key; Type: CONSTRAINT; Schema: postgis; Owner: webmap_import +-- + +ALTER TABLE ONLY postgis.lansyta + ADD CONSTRAINT lansyta_lanskod_key UNIQUE (lanskod); + + +-- +-- Name: lansyta lansyta_objektidentitet_key; Type: CONSTRAINT; Schema: postgis; Owner: webmap_import +-- + +ALTER TABLE ONLY postgis.lansyta + ADD CONSTRAINT lansyta_objektidentitet_key UNIQUE (objektidentitet); + + +-- +-- Name: lansyta lansyta_pkey; Type: CONSTRAINT; Schema: postgis; Owner: webmap_import +-- + +ALTER TABLE ONLY postgis.lansyta + ADD CONSTRAINT lansyta_pkey PRIMARY KEY (ogc_fid); + + +-- +-- Name: sks:AvverkAnm sks:AvverkAnm_Beteckn_key; Type: CONSTRAINT; Schema: postgis; Owner: webmap_import +-- + +ALTER TABLE ONLY postgis."sks:AvverkAnm" + ADD CONSTRAINT "sks:AvverkAnm_Beteckn_key" UNIQUE ("Beteckn"); + + +-- +-- Name: sks:AvverkAnm sks:AvverkAnm_OBJECTID_key; Type: CONSTRAINT; Schema: postgis; Owner: webmap_import +-- + +ALTER TABLE ONLY postgis."sks:AvverkAnm" + ADD CONSTRAINT "sks:AvverkAnm_OBJECTID_key" UNIQUE ("OBJECTID"); + + +-- +-- Name: sks:AvverkAnm sks:AvverkAnm_pkey; Type: CONSTRAINT; Schema: postgis; Owner: webmap_import +-- + +ALTER TABLE ONLY postgis."sks:AvverkAnm" + ADD CONSTRAINT "sks:AvverkAnm_pkey" PRIMARY KEY (ogc_fid); + + +-- +-- Name: sks:UtfordAvverk sks:UtfordAvverk_pkey; Type: CONSTRAINT; Schema: postgis; Owner: webmap_import +-- + +ALTER TABLE ONLY postgis."sks:UtfordAvverk" + ADD CONSTRAINT "sks:UtfordAvverk_pkey" PRIMARY KEY (ogc_fid); + + +-- +-- Name: kommunyta_wkb_geometry_geom_idx; Type: INDEX; Schema: postgis; Owner: webmap_import +-- + +CREATE INDEX kommunyta_wkb_geometry_geom_idx ON postgis.kommunyta USING gist (wkb_geometry); + + +-- +-- Name: lansyta_wkb_geometry_geom_idx; Type: INDEX; Schema: postgis; Owner: webmap_import +-- + +CREATE INDEX lansyta_wkb_geometry_geom_idx ON postgis.lansyta USING gist (wkb_geometry); + + +-- +-- Name: sks:AvverkAnm_wkb_geometry_geom_idx; Type: INDEX; Schema: postgis; Owner: webmap_import +-- + +CREATE INDEX "sks:AvverkAnm_wkb_geometry_geom_idx" ON postgis."sks:AvverkAnm" USING gist (wkb_geometry); + + +-- +-- Name: sks:UtfordAvverk_wkb_geometry_geom_idx; Type: INDEX; Schema: postgis; Owner: webmap_import +-- + +CREATE INDEX "sks:UtfordAvverk_wkb_geometry_geom_idx" ON postgis."sks:UtfordAvverk" USING gist (wkb_geometry); + + +-- +-- Name: DATABASE webmap; Type: ACL; Schema: -; Owner: postgres +-- + +GRANT CONNECT ON DATABASE webmap TO webmap_import; +GRANT CONNECT ON DATABASE webmap TO webmap_guest; + + +-- +-- Name: SCHEMA postgis; Type: ACL; Schema: -; Owner: postgres +-- + +GRANT USAGE ON SCHEMA postgis TO webmap_import; +GRANT USAGE ON SCHEMA postgis TO webmap_guest; + + +-- +-- Name: TABLE geography_columns; Type: ACL; Schema: postgis; Owner: postgres +-- + +GRANT SELECT ON TABLE postgis.geography_columns TO webmap_guest; + + +-- +-- Name: TABLE geometry_columns; Type: ACL; Schema: postgis; Owner: postgres +-- + +GRANT SELECT ON TABLE postgis.geometry_columns TO webmap_guest; + + +-- +-- Name: TABLE kommunyta; Type: ACL; Schema: postgis; Owner: webmap_import +-- + +GRANT SELECT ON TABLE postgis.kommunyta TO webmap_guest; + + +-- +-- Name: SEQUENCE kommunyta_ogc_fid_seq; Type: ACL; Schema: postgis; Owner: webmap_import +-- + +GRANT SELECT,USAGE ON SEQUENCE postgis.kommunyta_ogc_fid_seq TO webmap_guest; + + +-- +-- Name: TABLE lansyta; Type: ACL; Schema: postgis; Owner: webmap_import +-- + +GRANT SELECT ON TABLE postgis.lansyta TO webmap_guest; + + +-- +-- Name: SEQUENCE lansyta_ogc_fid_seq; Type: ACL; Schema: postgis; Owner: webmap_import +-- + +GRANT SELECT,USAGE ON SEQUENCE postgis.lansyta_ogc_fid_seq TO webmap_guest; + + +-- +-- Name: TABLE "sks:AvverkAnm"; Type: ACL; Schema: postgis; Owner: webmap_import +-- + +GRANT SELECT ON TABLE postgis."sks:AvverkAnm" TO webmap_guest; + + +-- +-- Name: SEQUENCE "sks:AvverkAnm_ogc_fid_seq"; Type: ACL; Schema: postgis; Owner: webmap_import +-- + +GRANT SELECT,USAGE ON SEQUENCE postgis."sks:AvverkAnm_ogc_fid_seq" TO webmap_guest; + + +-- +-- Name: TABLE "sks:UtfordAvverk"; Type: ACL; Schema: postgis; Owner: webmap_import +-- + +GRANT SELECT ON TABLE postgis."sks:UtfordAvverk" TO webmap_guest; + + +-- +-- Name: SEQUENCE "sks:UtfordAvverk_ogc_fid_seq"; Type: ACL; Schema: postgis; Owner: webmap_import +-- + +GRANT SELECT,USAGE ON SEQUENCE postgis."sks:UtfordAvverk_ogc_fid_seq" TO webmap_guest; + + +-- +-- Name: TABLE spatial_ref_sys; Type: ACL; Schema: postgis; Owner: postgres +-- + +GRANT SELECT ON TABLE postgis.spatial_ref_sys TO webmap_guest; + + +-- +-- PostgreSQL database dump complete +-- + |