From 59351acf88eda2d07ba41e2f7f70a2fba41b89d2 Mon Sep 17 00:00:00 2001
From: Guilhem Moulin <guilhem@fripost.org>
Date: Mon, 26 May 2025 01:31:12 +0200
Subject: Schema: Add functions to get a list of municipality and county codes.

We subdivide administrative polygons to speed up things, cf.
https://symphony.is/about-us/blog/boosting-postgis-performance
---
 schema.sql | 278 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++-
 1 file changed, 276 insertions(+), 2 deletions(-)

(limited to 'schema.sql')

diff --git a/schema.sql b/schema.sql
index c9079f9..91a4631 100644
--- a/schema.sql
+++ b/schema.sql
@@ -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;
@@ -230,6 +320,40 @@ ALTER TABLE postgis.kommunyta_ogc_fid_seq OWNER TO webmap_import;
 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
 --
@@ -273,6 +397,40 @@ ALTER TABLE postgis.lansyta_ogc_fid_seq OWNER TO webmap_import;
 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
 --
@@ -3068,6 +3226,13 @@ ALTER TABLE ONLY postgis.gigafactories ALTER COLUMN ogc_fid SET DEFAULT nextval(
 ALTER TABLE ONLY postgis.kommunyta ALTER COLUMN ogc_fid SET DEFAULT nextval('postgis.kommunyta_ogc_fid_seq'::regclass);
 
 
+--
+-- 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
 --
@@ -3075,6 +3240,13 @@ ALTER TABLE ONLY postgis.kommunyta ALTER COLUMN ogc_fid SET DEFAULT nextval('pos
 ALTER TABLE ONLY postgis.lansyta ALTER COLUMN ogc_fid SET DEFAULT nextval('postgis.lansyta_ogc_fid_seq'::regclass);
 
 
+--
+-- 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
 --
@@ -4474,6 +4646,20 @@ CREATE INDEX dammar_wkb_geometry_geom_idx ON postgis.dammar USING gist (wkb_geom
 CREATE INDEX gigafactories_wkb_geometry_geom_idx ON postgis.gigafactories USING gist (wkb_geometry);
 
 
+--
+-- 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
 --
@@ -4481,6 +4667,20 @@ CREATE INDEX gigafactories_wkb_geometry_geom_idx ON postgis.gigafactories USING
 CREATE INDEX kommunyta_wkb_geometry_geom_idx ON postgis.kommunyta USING gist (wkb_geometry);
 
 
+--
+-- 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
 --
@@ -4866,6 +5066,36 @@ CREATE INDEX "vbk:projekteringsomraden_wkb_geometry_geom_idx" ON postgis."vbk:pr
 CREATE INDEX "vbk:vindkraftverk_wkb_geometry_geom_idx" ON postgis."vbk:vindkraftverk" USING gist (wkb_geometry);
 
 
+--
+-- 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
 --
@@ -4882,6 +5112,22 @@ GRANT USAGE ON SCHEMA postgis TO webmap_import;
 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
 --
@@ -4938,6 +5184,20 @@ GRANT SELECT ON TABLE postgis.kommunyta TO webmap_guest;
 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
 --
@@ -4952,6 +5212,20 @@ GRANT SELECT ON TABLE postgis.lansyta TO webmap_guest;
 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
 --
-- 
cgit v1.2.3

