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

2 files changed, 277 insertions, 3 deletions

diff --git a/import_source.py b/import_source.py
index 46ec1e9..13a8e6c 100644
--- a/import_source.py
+++ b/import_source.py
@@ -376,7 +376,7 @@ def clearLayer(ds : gdal.Dataset, lyr : ogr.Layer) -> None:
     drv = ds.GetDriver()
     if drv.ShortName == 'PostgreSQL':
         # https://www.postgresql.org/docs/15/sql-truncate.html
-        query = 'TRUNCATE TABLE {table} CONTINUE IDENTITY RESTRICT'
+        query = 'TRUNCATE TABLE {table} CONTINUE IDENTITY CASCADE'
         op = 'Truncating'
     else:
         query = 'DELETE FROM {table}'
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;
@@ -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
 --