From 59351acf88eda2d07ba41e2f7f70a2fba41b89d2 Mon Sep 17 00:00:00 2001 From: Guilhem Moulin 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