From 94438a900d3fb933a33aed4d2ffeb8809e966c46 Mon Sep 17 00:00:00 2001 From: Guilhem Moulin Date: Fri, 6 Mar 2026 10:08:01 +0100 Subject: Schema: Create get_counties() and get_municipalities() functions in the public schema. --- schema.sql | 108 ++++++++++++++++++++++++++++++------------------------------- 1 file changed, 54 insertions(+), 54 deletions(-) (limited to 'schema.sql') diff --git a/schema.sql b/schema.sql index d2d8a23..68d479c 100644 --- a/schema.sql +++ b/schema.sql @@ -2,7 +2,7 @@ -- PostgreSQL database dump -- -\restrict rHTR7weUptC3eGdenCfz6Ysl1OTCFSunVCQ1o04oZaTZREF1Hao8q1GgtxWbqF3 +\restrict XTQKLfyJjEgwGyWj4y8ogFznWaICeqXkLOw7GUT2TnBjUqJHf8BrjygbLvfZun2 -- Dumped from database version 17.8 (Debian 17.8-0+deb13u1) -- Dumped by pg_dump version 17.8 (Debian 17.8-0+deb13u1) @@ -29,9 +29,9 @@ CREATE DATABASE webmap WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVID ALTER DATABASE webmap OWNER TO postgres; -\unrestrict rHTR7weUptC3eGdenCfz6Ysl1OTCFSunVCQ1o04oZaTZREF1Hao8q1GgtxWbqF3 +\unrestrict XTQKLfyJjEgwGyWj4y8ogFznWaICeqXkLOw7GUT2TnBjUqJHf8BrjygbLvfZun2 \connect webmap -\restrict rHTR7weUptC3eGdenCfz6Ysl1OTCFSunVCQ1o04oZaTZREF1Hao8q1GgtxWbqF3 +\restrict XTQKLfyJjEgwGyWj4y8ogFznWaICeqXkLOw7GUT2TnBjUqJHf8BrjygbLvfZun2 SET statement_timeout = 0; SET lock_timeout = 0; @@ -242,50 +242,6 @@ CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public; COMMENT ON EXTENSION postgis IS 'Geographic objects support for PostgreSQL'; --- --- Name: get_counties(public.geometry); Type: FUNCTION; Schema: lm_topo250; Owner: postgres --- - -CREATE FUNCTION lm_topo250.get_counties(public.geometry) RETURNS smallint[] - LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE - AS $_$ - SELECT array_agg(DISTINCT t.lanskod ORDER BY t.lanskod) - FROM lm_topo250.lansyta_sub t - -- Make use of spatial 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 lm_topo250.get_counties(public.geometry) OWNER TO postgres; - --- --- Name: get_municipalities(public.geometry); Type: FUNCTION; Schema: lm_topo250; Owner: postgres --- - -CREATE FUNCTION lm_topo250.get_municipalities(public.geometry) RETURNS smallint[] - LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE - AS $_$ - SELECT array_agg(DISTINCT t.kommunkod ORDER BY t.kommunkod) - FROM lm_topo250.kommunyta_sub t - -- Make use of spatial 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 lm_topo250.get_municipalities(public.geometry) OWNER TO postgres; - -- -- Name: kommunyta_subdivide_func(); Type: FUNCTION; Schema: lm_topo250; Owner: webmap_import -- @@ -358,6 +314,50 @@ $$; ALTER FUNCTION ogr_system_tables.event_trigger_function_for_metadata() OWNER TO webmap_import; +-- +-- Name: get_counties(public.geometry); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION public.get_counties(public.geometry) RETURNS smallint[] + LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE + AS $_$ + SELECT array_agg(DISTINCT t.lanskod ORDER BY t.lanskod) + FROM lm_topo250.lansyta_sub t + -- Make use of spatial 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 public.get_counties(public.geometry) OWNER TO postgres; + +-- +-- Name: get_municipalities(public.geometry); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION public.get_municipalities(public.geometry) RETURNS smallint[] + LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE + AS $_$ + SELECT array_agg(DISTINCT t.kommunkod ORDER BY t.kommunkod) + FROM lm_topo250.kommunyta_sub t + -- Make use of spatial 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 public.get_municipalities(public.geometry) OWNER TO postgres; + SET default_tablespace = ''; SET default_table_access_method = heap; @@ -9218,19 +9218,19 @@ GRANT USAGE ON SCHEMA vbk TO webmap_guest; -- --- Name: FUNCTION get_counties(public.geometry); Type: ACL; Schema: lm_topo250; Owner: postgres +-- Name: FUNCTION get_counties(public.geometry); Type: ACL; Schema: public; Owner: postgres -- -GRANT ALL ON FUNCTION lm_topo250.get_counties(public.geometry) TO webmap_import; -GRANT ALL ON FUNCTION lm_topo250.get_counties(public.geometry) TO webmap_guest; +GRANT ALL ON FUNCTION public.get_counties(public.geometry) TO webmap_import; +GRANT ALL ON FUNCTION public.get_counties(public.geometry) TO webmap_guest; -- --- Name: FUNCTION get_municipalities(public.geometry); Type: ACL; Schema: lm_topo250; Owner: postgres +-- Name: FUNCTION get_municipalities(public.geometry); Type: ACL; Schema: public; Owner: postgres -- -GRANT ALL ON FUNCTION lm_topo250.get_municipalities(public.geometry) TO webmap_import; -GRANT ALL ON FUNCTION lm_topo250.get_municipalities(public.geometry) TO webmap_guest; +GRANT ALL ON FUNCTION public.get_municipalities(public.geometry) TO webmap_import; +GRANT ALL ON FUNCTION public.get_municipalities(public.geometry) TO webmap_guest; -- @@ -10234,5 +10234,5 @@ ALTER EVENT TRIGGER ogr_system_tables_event_trigger_for_metadata OWNER TO postgr -- PostgreSQL database dump complete -- -\unrestrict rHTR7weUptC3eGdenCfz6Ysl1OTCFSunVCQ1o04oZaTZREF1Hao8q1GgtxWbqF3 +\unrestrict XTQKLfyJjEgwGyWj4y8ogFznWaICeqXkLOw7GUT2TnBjUqJHf8BrjygbLvfZun2 -- cgit v1.2.3