diff options
Diffstat (limited to 'schema.sql')
| -rw-r--r-- | schema.sql | 278 |
1 files changed, 276 insertions, 2 deletions
@@ -2,8 +2,8 @@ -- PostgreSQL database dump -- --- Dumped from database version 15.12 (Debian 15.12-0+deb12u2) --- Dumped by pg_dump version 15.12 (Debian 15.12-0+deb12u2) +-- Dumped from database version 15.13 (Debian 15.13-0+deb12u1) +-- Dumped by pg_dump version 15.13 (Debian 15.13-0+deb12u1) SET statement_timeout = 0; SET lock_timeout = 0; @@ -69,6 +69,96 @@ CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA postgis; COMMENT ON EXTENSION postgis IS 'Geographic objects support for PostgreSQL'; +-- +-- Name: get_counties(postgis.geometry); Type: FUNCTION; Schema: postgis; Owner: postgres +-- + +CREATE FUNCTION postgis.get_counties(postgis.geometry) RETURNS smallint[] + LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE + AS $_$ + SELECT array_agg(DISTINCT t.lanskod ORDER BY t.lanskod) + FROM postgis.lansyta_sub t + -- Make use of spactial indices: filter out non-overlapping envelopes first. + -- This function works for curves and points too, as the interior of a curve + -- is the set of points between its endpoints, and the interior of a point is + -- the point itself. It also works for 3D input geometries (in that case the + -- Z component is dropped). + -- TODO we might want to check '*T*******' too (so a line exactly on a + -- boundary segment would belong to both counties) + WHERE t.wkb_geometry && $1 AND ST_Relate(t.wkb_geometry, $1, 'T********') + $_$; + + +ALTER FUNCTION postgis.get_counties(postgis.geometry) OWNER TO postgres; + +-- +-- Name: get_municipalities(postgis.geometry); Type: FUNCTION; Schema: postgis; Owner: postgres +-- + +CREATE FUNCTION postgis.get_municipalities(postgis.geometry) RETURNS smallint[] + LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE + AS $_$ + SELECT array_agg(DISTINCT t.kommunkod ORDER BY t.kommunkod) + FROM postgis.kommunyta_sub t + -- Make use of spactial indices: filter out non-overlapping envelopes first. + -- This function works for curves and points too, as the interior of a curve + -- is the set of points between its endpoints, and the interior of a point is + -- the point itself. It also works for 3D input geometries (in that case the + -- Z component is dropped). + -- TODO we might want to check '*T*******' too (so a line exactly on a + -- boundary segment would belong to both municipalities) + WHERE t.wkb_geometry && $1 AND ST_Relate(t.wkb_geometry, $1, 'T********') + $_$; + + +ALTER FUNCTION postgis.get_municipalities(postgis.geometry) OWNER TO postgres; + +-- +-- Name: kommunyta_subdivide_func(); Type: FUNCTION; Schema: postgis; Owner: webmap_import +-- + +CREATE FUNCTION postgis.kommunyta_subdivide_func() RETURNS trigger + LANGUAGE plpgsql + AS $$ + BEGIN + -- Propagate changes to postgis.kommunyta to postgis.kommunyta_sub + IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') THEN + DELETE FROM postgis.kommunyta_sub WHERE kommunkod = OLD.kommunkod; + END IF; + IF (TG_OP = 'UPDATE' OR TG_OP = 'INSERT') THEN + INSERT INTO postgis.kommunyta_sub(kommunkod, wkb_geometry) VALUES + (NEW.kommunkod, postgis.ST_Subdivide(NEW.wkb_geometry, 10)); + END IF; + RETURN NULL; + END; + $$; + + +ALTER FUNCTION postgis.kommunyta_subdivide_func() OWNER TO webmap_import; + +-- +-- Name: lansyta_subdivide_func(); Type: FUNCTION; Schema: postgis; Owner: webmap_import +-- + +CREATE FUNCTION postgis.lansyta_subdivide_func() RETURNS trigger + LANGUAGE plpgsql + AS $$ + BEGIN + -- Propagate changes to postgis.lansyta to postgis.lansyta_sub + IF (TG_OP = 'DELETE' OR TG_OP = 'UPDATE') THEN + DELETE FROM postgis.lansyta_sub WHERE lanskod = OLD.lanskod; + END IF; + IF (TG_OP = 'UPDATE' OR TG_OP = 'INSERT') THEN + INSERT INTO postgis.lansyta_sub(lanskod, wkb_geometry) VALUES + (NEW.lanskod, postgis.ST_Subdivide(NEW.wkb_geometry, 10)); + END IF; + RETURN NULL; + END; + $$; + + +ALTER FUNCTION postgis.lansyta_subdivide_func() OWNER TO webmap_import; + SET default_tablespace = ''; SET default_table_access_method = heap; @@ -231,6 +321,40 @@ ALTER SEQUENCE postgis.kommunyta_ogc_fid_seq OWNED BY postgis.kommunyta.ogc_fid; -- +-- Name: kommunyta_sub; Type: TABLE; Schema: postgis; Owner: webmap_import +-- + +CREATE TABLE postgis.kommunyta_sub ( + ogc_fid bigint NOT NULL, + kommunkod smallint NOT NULL, + wkb_geometry postgis.geometry(Polygon,3006) +); + + +ALTER TABLE postgis.kommunyta_sub OWNER TO webmap_import; + +-- +-- Name: kommunyta_sub_ogc_fid_seq; Type: SEQUENCE; Schema: postgis; Owner: webmap_import +-- + +CREATE SEQUENCE postgis.kommunyta_sub_ogc_fid_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE postgis.kommunyta_sub_ogc_fid_seq OWNER TO webmap_import; + +-- +-- Name: kommunyta_sub_ogc_fid_seq; Type: SEQUENCE OWNED BY; Schema: postgis; Owner: webmap_import +-- + +ALTER SEQUENCE postgis.kommunyta_sub_ogc_fid_seq OWNED BY postgis.kommunyta_sub.ogc_fid; + + +-- -- Name: lansyta; Type: TABLE; Schema: postgis; Owner: webmap_import -- @@ -274,6 +398,40 @@ ALTER SEQUENCE postgis.lansyta_ogc_fid_seq OWNED BY postgis.lansyta.ogc_fid; -- +-- Name: lansyta_sub; Type: TABLE; Schema: postgis; Owner: webmap_import +-- + +CREATE TABLE postgis.lansyta_sub ( + ogc_fid bigint NOT NULL, + lanskod smallint NOT NULL, + wkb_geometry postgis.geometry(Polygon,3006) +); + + +ALTER TABLE postgis.lansyta_sub OWNER TO webmap_import; + +-- +-- Name: lansyta_sub_ogc_fid_seq; Type: SEQUENCE; Schema: postgis; Owner: webmap_import +-- + +CREATE SEQUENCE postgis.lansyta_sub_ogc_fid_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE postgis.lansyta_sub_ogc_fid_seq OWNER TO webmap_import; + +-- +-- Name: lansyta_sub_ogc_fid_seq; Type: SEQUENCE OWNED BY; Schema: postgis; Owner: webmap_import +-- + +ALTER SEQUENCE postgis.lansyta_sub_ogc_fid_seq OWNED BY postgis.lansyta_sub.ogc_fid; + + +-- -- Name: mrr:bearbetningskoncessioner_ansokta; Type: TABLE; Schema: postgis; Owner: webmap_import -- @@ -3069,6 +3227,13 @@ ALTER TABLE ONLY postgis.kommunyta ALTER COLUMN ogc_fid SET DEFAULT nextval('pos -- +-- Name: kommunyta_sub ogc_fid; Type: DEFAULT; Schema: postgis; Owner: webmap_import +-- + +ALTER TABLE ONLY postgis.kommunyta_sub ALTER COLUMN ogc_fid SET DEFAULT nextval('postgis.kommunyta_sub_ogc_fid_seq'::regclass); + + +-- -- Name: lansyta ogc_fid; Type: DEFAULT; Schema: postgis; Owner: webmap_import -- @@ -3076,6 +3241,13 @@ ALTER TABLE ONLY postgis.lansyta ALTER COLUMN ogc_fid SET DEFAULT nextval('postg -- +-- Name: lansyta_sub ogc_fid; Type: DEFAULT; Schema: postgis; Owner: webmap_import +-- + +ALTER TABLE ONLY postgis.lansyta_sub ALTER COLUMN ogc_fid SET DEFAULT nextval('postgis.lansyta_sub_ogc_fid_seq'::regclass); + + +-- -- Name: mrr:bearbetningskoncessioner_ansokta ogc_fid; Type: DEFAULT; Schema: postgis; Owner: webmap_import -- @@ -4475,6 +4647,20 @@ CREATE INDEX gigafactories_wkb_geometry_geom_idx ON postgis.gigafactories USING -- +-- Name: kommunyta_sub_kommunkod_idx; Type: INDEX; Schema: postgis; Owner: webmap_import +-- + +CREATE INDEX kommunyta_sub_kommunkod_idx ON postgis.kommunyta_sub USING btree (kommunkod); + + +-- +-- Name: kommunyta_sub_wkb_geometry_geom_idx; Type: INDEX; Schema: postgis; Owner: webmap_import +-- + +CREATE INDEX kommunyta_sub_wkb_geometry_geom_idx ON postgis.kommunyta_sub USING gist (wkb_geometry); + + +-- -- Name: kommunyta_wkb_geometry_geom_idx; Type: INDEX; Schema: postgis; Owner: webmap_import -- @@ -4482,6 +4668,20 @@ CREATE INDEX kommunyta_wkb_geometry_geom_idx ON postgis.kommunyta USING gist (wk -- +-- Name: lansyta_sub_lanskod_idx; Type: INDEX; Schema: postgis; Owner: webmap_import +-- + +CREATE INDEX lansyta_sub_lanskod_idx ON postgis.lansyta_sub USING btree (lanskod); + + +-- +-- Name: lansyta_sub_wkb_geometry_geom_idx; Type: INDEX; Schema: postgis; Owner: webmap_import +-- + +CREATE INDEX lansyta_sub_wkb_geometry_geom_idx ON postgis.lansyta_sub USING gist (wkb_geometry); + + +-- -- Name: lansyta_wkb_geometry_geom_idx; Type: INDEX; Schema: postgis; Owner: webmap_import -- @@ -4867,6 +5067,36 @@ CREATE INDEX "vbk:vindkraftverk_wkb_geometry_geom_idx" ON postgis."vbk:vindkraft -- +-- Name: kommunyta kommunyta_subdivide_trigger; Type: TRIGGER; Schema: postgis; Owner: webmap_import +-- + +CREATE TRIGGER kommunyta_subdivide_trigger AFTER INSERT OR DELETE OR UPDATE ON postgis.kommunyta FOR EACH ROW EXECUTE FUNCTION postgis.kommunyta_subdivide_func(); + + +-- +-- Name: lansyta lansyta_subdivide_trigger; Type: TRIGGER; Schema: postgis; Owner: webmap_import +-- + +CREATE TRIGGER lansyta_subdivide_trigger AFTER INSERT OR DELETE OR UPDATE ON postgis.lansyta FOR EACH ROW EXECUTE FUNCTION postgis.lansyta_subdivide_func(); + + +-- +-- Name: kommunyta_sub kommunyta_sub_kommunkod_fkey; Type: FK CONSTRAINT; Schema: postgis; Owner: webmap_import +-- + +ALTER TABLE ONLY postgis.kommunyta_sub + ADD CONSTRAINT kommunyta_sub_kommunkod_fkey FOREIGN KEY (kommunkod) REFERENCES postgis.kommunyta(kommunkod) ON DELETE CASCADE; + + +-- +-- Name: lansyta_sub lansyta_sub_lanskod_fkey; Type: FK CONSTRAINT; Schema: postgis; Owner: webmap_import +-- + +ALTER TABLE ONLY postgis.lansyta_sub + ADD CONSTRAINT lansyta_sub_lanskod_fkey FOREIGN KEY (lanskod) REFERENCES postgis.lansyta(lanskod) ON DELETE CASCADE; + + +-- -- Name: DATABASE webmap; Type: ACL; Schema: -; Owner: postgres -- @@ -4883,6 +5113,22 @@ GRANT USAGE ON SCHEMA postgis TO webmap_guest; -- +-- Name: FUNCTION get_counties(postgis.geometry); Type: ACL; Schema: postgis; Owner: postgres +-- + +GRANT ALL ON FUNCTION postgis.get_counties(postgis.geometry) TO webmap_import; +GRANT ALL ON FUNCTION postgis.get_counties(postgis.geometry) TO webmap_guest; + + +-- +-- Name: FUNCTION get_municipalities(postgis.geometry); Type: ACL; Schema: postgis; Owner: postgres +-- + +GRANT ALL ON FUNCTION postgis.get_municipalities(postgis.geometry) TO webmap_import; +GRANT ALL ON FUNCTION postgis.get_municipalities(postgis.geometry) TO webmap_guest; + + +-- -- Name: TABLE dammar; Type: ACL; Schema: postgis; Owner: webmap_import -- @@ -4939,6 +5185,20 @@ GRANT SELECT,USAGE ON SEQUENCE postgis.kommunyta_ogc_fid_seq TO webmap_guest; -- +-- Name: TABLE kommunyta_sub; Type: ACL; Schema: postgis; Owner: webmap_import +-- + +GRANT SELECT ON TABLE postgis.kommunyta_sub TO webmap_guest; + + +-- +-- Name: SEQUENCE kommunyta_sub_ogc_fid_seq; Type: ACL; Schema: postgis; Owner: webmap_import +-- + +GRANT SELECT,USAGE ON SEQUENCE postgis.kommunyta_sub_ogc_fid_seq TO webmap_guest; + + +-- -- Name: TABLE lansyta; Type: ACL; Schema: postgis; Owner: webmap_import -- @@ -4953,6 +5213,20 @@ GRANT SELECT,USAGE ON SEQUENCE postgis.lansyta_ogc_fid_seq TO webmap_guest; -- +-- Name: TABLE lansyta_sub; Type: ACL; Schema: postgis; Owner: webmap_import +-- + +GRANT SELECT ON TABLE postgis.lansyta_sub TO webmap_guest; + + +-- +-- Name: SEQUENCE lansyta_sub_ogc_fid_seq; Type: ACL; Schema: postgis; Owner: webmap_import +-- + +GRANT SELECT,USAGE ON SEQUENCE postgis.lansyta_sub_ogc_fid_seq TO webmap_guest; + + +-- -- Name: TABLE "mrr:bearbetningskoncessioner_ansokta"; Type: ACL; Schema: postgis; Owner: webmap_import -- |