aboutsummaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorGuilhem Moulin <guilhem@fripost.org>2024-06-11 20:04:59 +0200
committerGuilhem Moulin <guilhem@fripost.org>2024-06-11 20:51:44 +0200
commitf91fbc6972eb7f1451ea604b232eff8506d2c6ab (patch)
tree53c59c013fb1a197789fb7189edfa5d246438123
parentee95a54e4f70fd7f911e8a2cacfb342c85213937 (diff)
Add schema.sql.
This is useful to bootstrap the database without having to run GRANT CREATE ON SCHEMA postgis TO webmap_import; after DROPping the database or re-creating the cluster.
-rw-r--r--schema.sql501
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
+--
+