-
Notifications
You must be signed in to change notification settings - Fork 5
prepare02 lib StableFuncsOriginal.sql
Peter edited this page May 16, 2020
·
2 revisions
prepare02-lib-StableFuncsOriginal.sql
--
-- PostgreSQL database dump
--
-- Dumped from database version 10.12 (Ubuntu 10.12-1.pgdg16.04+1)
-- Dumped by pg_dump version 10.12 (Ubuntu 10.12-1.pgdg16.04+1)
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
--
-- Name: csvranges_to_int4ranges(text); Type: FUNCTION; Schema: stable; Owner: postgres
--
CREATE FUNCTION stable.csvranges_to_int4ranges(p_range text) RETURNS int4range[]
LANGUAGE sql IMMUTABLE
AS $_$
SELECT ('{'||
regexp_replace( translate(regexp_replace($1,'\][;, ]+\[','],[','g'),' -',',') , '\[(\d+),(\d+)\]', '"[\1,\2]"', 'g')
|| '}')::int4range[];
$_$;
ALTER FUNCTION stable.csvranges_to_int4ranges(p_range text) OWNER TO postgres;
--
-- Name: element_properties(bigint, character); Type: FUNCTION; Schema: stable; Owner: postgres
--
CREATE FUNCTION stable.element_properties(p_osm_id bigint, p_osm_type character DEFAULT NULL::bpchar) RETURNS jsonb
LANGUAGE sql IMMUTABLE
AS $_$
SELECT CASE
WHEN ($2 IS NULL AND $1<0) OR $2='r' THEN stable.rel_properties($1)
ELSE stable.way_properties($1)
END
$_$;
ALTER FUNCTION stable.element_properties(p_osm_id bigint, p_osm_type character) OWNER TO postgres;
--
-- Name: getcity_line_geom(text, text); Type: FUNCTION; Schema: stable; Owner: postgres
--
CREATE FUNCTION stable.getcity_line_geom(p_cod_ibge text, p_admin_level text DEFAULT '8'::text) RETURNS public.geometry
LANGUAGE sql IMMUTABLE
AS $_$
SELECT way
FROM planet_osm_line
WHERE -osm_id = stable.getcity_rels_id(p_cod_ibge,$2)
$_$;
ALTER FUNCTION stable.getcity_line_geom(p_cod_ibge text, p_admin_level text) OWNER TO postgres;
--
-- Name: getcity_polygon_geom(text, text); Type: FUNCTION; Schema: stable; Owner: postgres
--
CREATE FUNCTION stable.getcity_polygon_geom(p_cod_ibge text, p_admin_level text DEFAULT '8'::text) RETURNS public.geometry
LANGUAGE sql IMMUTABLE
AS $_$
SELECT way
FROM planet_osm_polygon
WHERE osm_id = -stable.getcity_rels_id(p_cod_ibge,$2)
$_$;
ALTER FUNCTION stable.getcity_polygon_geom(p_cod_ibge text, p_admin_level text) OWNER TO postgres;
--
-- Name: getcity_rels_id(bigint, text); Type: FUNCTION; Schema: stable; Owner: postgres
--
CREATE FUNCTION stable.getcity_rels_id(p_cod_ibge bigint, p_admin_level text DEFAULT '8'::text) RETURNS bigint
LANGUAGE sql IMMUTABLE
AS $_$
SELECT stable.getcity_rels_id($1::text,$2)
$_$;
ALTER FUNCTION stable.getcity_rels_id(p_cod_ibge bigint, p_admin_level text) OWNER TO postgres;
--
-- Name: getcity_rels_id(text, text); Type: FUNCTION; Schema: stable; Owner: postgres
--
CREATE FUNCTION stable.getcity_rels_id(p_cod_ibge text, p_admin_level text DEFAULT '8'::text) RETURNS bigint
LANGUAGE sql IMMUTABLE
AS $$
SELECT id
FROM planet_osm_rels
WHERE tags->>'admin_level'=p_admin_level AND CASE
WHEN substr(p_cod_ibge,1,1)='Q' THEN p_cod_ibge=tags->>'wikidata'
WHEN substr(p_cod_ibge,3,1) IN (':','-',';') THEN (
SELECT ibge_id::text FROM brcodes_city
WHERE upper(substr(p_cod_ibge,1,2))=uf AND substr(lower(p_cod_ibge),4)=lexLabel
) = tags->>'IBGE:GEOCODIGO'
WHEN substr(p_cod_ibge,3,1)='/' THEN (
SELECT ibge_id::text FROM brcodes_city
WHERE upper(substr(p_cod_ibge,1,2))=uf AND substr(p_cod_ibge,4)=stable.lexname_to_path(lexLabel)
) = tags->>'IBGE:GEOCODIGO'
WHEN length(p_cod_ibge)=7 THEN p_cod_ibge=tags->>'IBGE:GEOCODIGO'
ELSE p_cod_ibge::bigint = (tags->>'IBGE:GEOCODIGO')::bigint
END
$$;
ALTER FUNCTION stable.getcity_rels_id(p_cod_ibge text, p_admin_level text) OWNER TO postgres;
--
-- Name: id_ibge2uf(text); Type: FUNCTION; Schema: stable; Owner: postgres
--
CREATE FUNCTION stable.id_ibge2uf(p_id text) RETURNS text
LANGUAGE sql IMMUTABLE
AS $_$
-- deveria ser construida depois pelos datasets... gambi.
-- Using official codes of 2018, lookup-table, from IBGE code to UF abbreviation.
-- for general city-codes use stable.id_ibge2uf(substr(id,1,2))
SELECT ('{
"12":"AC", "27":"AL", "13":"AM", "16":"AP", "29":"BA", "23":"CE",
"53":"DF", "32":"ES", "52":"GO", "21":"MA", "31":"MG", "50":"MS",
"51":"MT", "15":"PA", "25":"PB", "26":"PE", "22":"PI", "41":"PR",
"33":"RJ", "24":"RN", "11":"RO", "14":"RR", "43":"RS", "42":"SC",
"28":"SE", "35":"SP", "17":"TO"
}'::jsonb)->>$1
$_$;
ALTER FUNCTION stable.id_ibge2uf(p_id text) OWNER TO postgres;
--
-- Name: int4ranges_to_csvranges(int4range[]); Type: FUNCTION; Schema: stable; Owner: postgres
--
CREATE FUNCTION stable.int4ranges_to_csvranges(p_range int4range[]) RETURNS text
LANGUAGE sql IMMUTABLE
AS $_$
SELECT translate($1::text,',{}"',' ');
$_$;
ALTER FUNCTION stable.int4ranges_to_csvranges(p_range int4range[]) OWNER TO postgres;
--
-- Name: jsonb_members_old(jsonb); Type: FUNCTION; Schema: stable; Owner: postgres
--
CREATE FUNCTION stable.jsonb_members_old(jsonb) RETURNS jsonb
LANGUAGE sql IMMUTABLE
AS $_$
SELECT jsonb_object_agg(tg,val)
FROM ( -- outer-r, outer-w, admin_center-n, etc.
SELECT v ||'-'||tg as tg, jsonb_agg(val) as val
FROM (
SELECT k, v, substr(k,2)::bigint val, substr(k,1,1) as tg
FROM jsonb_each_text($1) t(k,v)
ORDER BY 3,2
) kv
GROUP BY 1
) t
$_$;
ALTER FUNCTION stable.jsonb_members_old(jsonb) OWNER TO postgres;
--
-- Name: lexlabel_to_path(text); Type: FUNCTION; Schema: stable; Owner: postgres
--
CREATE FUNCTION stable.lexlabel_to_path(p_lexname text) RETURNS text
LANGUAGE sql IMMUTABLE
AS $$
SELECT string_agg(initcap(t),'')
FROM regexp_split_to_table(p_lexname, E'[\\.\\s]+') t
$$;
ALTER FUNCTION stable.lexlabel_to_path(p_lexname text) OWNER TO postgres;
--
-- Name: lexname_to_path(text); Type: FUNCTION; Schema: stable; Owner: postgres
--
CREATE FUNCTION stable.lexname_to_path(p_lexname text) RETURNS text
LANGUAGE sql IMMUTABLE
AS $$
SELECT string_agg(initcap(t),'')
FROM regexp_split_to_table(p_lexname, E'[\\.\\s]+') t
$$;
ALTER FUNCTION stable.lexname_to_path(p_lexname text) OWNER TO postgres;
--
-- Name: member_key(bigint, boolean); Type: FUNCTION; Schema: stable; Owner: postgres
--
CREATE FUNCTION stable.member_key(p_rel_id bigint, p_no_rel boolean DEFAULT false) RETURNS text
LANGUAGE sql IMMUTABLE
AS $$
SELECT stable.member_key(members,p_no_rel)
FROM planet_osm_rels WHERE id=p_rel_id
$$;
ALTER FUNCTION stable.member_key(p_rel_id bigint, p_no_rel boolean) OWNER TO postgres;
--
-- Name: member_key(jsonb, boolean, character); Type: FUNCTION; Schema: stable; Owner: postgres
--
CREATE FUNCTION stable.member_key(p_members jsonb, p_no_rel boolean DEFAULT false, p_w_char character DEFAULT 'w'::bpchar) RETURNS text
LANGUAGE sql IMMUTABLE
AS $_$
SELECT p_w_char || COALESCE($1->>'w_md5','') || CASE
WHEN p_no_rel THEN ''
ELSE 'r' || COALESCE($1->>'r_md5','')
END
$_$;
ALTER FUNCTION stable.member_key(p_members jsonb, p_no_rel boolean, p_w_char character) OWNER TO postgres;
--
-- Name: members_pack(bigint); Type: FUNCTION; Schema: stable; Owner: postgres
--
CREATE FUNCTION stable.members_pack(p_owner_id bigint) RETURNS jsonb
LANGUAGE sql IMMUTABLE
AS $_$
SELECT jsonb_object_agg(osm_type,member_types)
|| jsonb_object_agg(osm_type||'_size',n_osm_ids)
|| jsonb_object_agg(osm_type||'_md5', substr(osm_ids_md5,0,17)) --
FROM (
SELECT osm_type, SUM(n_osm_ids) n_osm_ids,
jsonb_object_agg(member_type,osm_ids) member_types,
md5(array_distinct_sort(array_agg_cat(osm_ids_md5))::text) osm_ids_md5
FROM (
SELECT osm_type, member_type,
count(*) as n_osm_ids,
jsonb_agg(osm_id ORDER BY osm_id) as osm_ids,
array_agg(osm_id) as osm_ids_md5
FROM stable.member_of
WHERE osm_owner=-$1
GROUP BY 1,2
) t1
GROUP BY 1
) t2
$_$;
ALTER FUNCTION stable.members_pack(p_owner_id bigint) OWNER TO postgres;
--
-- Name: name2lex(text, boolean, boolean, boolean); Type: FUNCTION; Schema: stable; Owner: postgres
--
CREATE FUNCTION stable.name2lex(p_name text, p_normalize boolean DEFAULT true, p_cut boolean DEFAULT true, p_flag boolean DEFAULT true) RETURNS text
LANGUAGE sql IMMUTABLE
AS $_$
SELECT CASE WHEN p_flag THEN urn ELSE urn END
FROM (
SELECT trim(replace(
regexp_replace(
CASE
WHEN p_normalize AND p_flag THEN stable.normalizeterm2(unaccent($1),p_cut)
WHEN p_normalize THEN stable.normalizeterm2($1,p_cut)
ELSE $1
END,
E' d[aeo] | d[oa]s | com | para |^d[aeo] | / .+| [aeo]s | [aeo] |[\-\' ]',
'.',
'g'
),
'..',
'.'
),'.')
) t(urn)
$_$;
ALTER FUNCTION stable.name2lex(p_name text, p_normalize boolean, p_cut boolean, p_flag boolean) OWNER TO postgres;
--
-- Name: normalizeterm(text, text, integer, text); Type: FUNCTION; Schema: stable; Owner: postgres
--
CREATE FUNCTION stable.normalizeterm(text, text DEFAULT ' '::text, integer DEFAULT 0, p_sep2 text DEFAULT ' , '::text) RETURNS text
LANGUAGE sql IMMUTABLE
AS $_$
SELECT substring(
LOWER(TRIM( regexp_replace( -- for review: regex(regex()) for ` , , ` remove
trim(regexp_replace($1,E'[\\n\\r \\+/,;:\\(\\)\\{\\}\\[\\]="\\s ]*[\\+/,;:\\(\\)\\{\\}\\[\\]="]+[\\+/,;:\\(\\)\\{\\}\\[\\]="\\s ]*|[\\s ]+[–\\-][\\s ]+',
p_sep2, 'g'),' ,'), -- s*ps*|s-s
E'[\\s ;\\|"]+[\\.\'][\\s ;\\|"]+|[\\s ;\\|"]+', -- s.s|s
$2,
'g'
), $2 )),
1,
CASE WHEN $3<=0 OR $3 IS NULL THEN char_length($1) ELSE $3 END
);
$_$;
ALTER FUNCTION stable.normalizeterm(text, text, integer, p_sep2 text) OWNER TO postgres;
--
-- Name: normalizeterm2(text, boolean); Type: FUNCTION; Schema: stable; Owner: postgres
--
CREATE FUNCTION stable.normalizeterm2(text, boolean DEFAULT true) RETURNS text
LANGUAGE sql IMMUTABLE
AS $_$
SELECT ( stable.normalizeterm(
CASE WHEN $2 THEN substring($1 from '^[^\(\)\/;]+' ) ELSE $1 END,
' ',
255,
' / '
));
$_$;
ALTER FUNCTION stable.normalizeterm2(text, boolean) OWNER TO postgres;
--
-- Name: osm_to_jsonb_remove_prefix(jsonb, text); Type: FUNCTION; Schema: stable; Owner: postgres
--
CREATE FUNCTION stable.osm_to_jsonb_remove_prefix(jsonb, text DEFAULT 'name:'::text) RETURNS text[]
LANGUAGE sql IMMUTABLE
AS $_$
SELECT COALESCE((
SELECT array_agg(t)
FROM jsonb_object_keys($1) t
WHERE position($2 in t)=1
), '{}'::text[])
$_$;
ALTER FUNCTION stable.osm_to_jsonb_remove_prefix(jsonb, text) OWNER TO postgres;
--
-- Name: osmmembers_pack(jsonb); Type: FUNCTION; Schema: stable; Owner: postgres
--
CREATE FUNCTION stable.osmmembers_pack(jsonb) RETURNS jsonb
LANGUAGE sql IMMUTABLE
AS $_$
SELECT jsonb_object_agg(osm_type,gtids)
FROM (
SELECT osm_type, jsonb_object_agg(gtype,osm_ids) gtids
FROM (
SELECT kv.osm_type, kv.gtype, jsonb_agg(kv.osm_id) osm_ids
FROM (
SELECT gtype, substr(k,1,1) as osm_type, substr(k,2)::bigint osm_id
FROM jsonb_each_text($1) t(k,gtype)
ORDER BY 2,3
) kv INNER JOIN stable.element_exists e
ON e.is_node=(kv.gtype='n')
AND kv.osm_id=CASE WHEN gtype='r' THEN -e.osm_id ELSE e.osm_id END
GROUP BY 1,2
ORDER BY 1,2
) t2
GROUP BY 1
ORDER BY 1
) t3
$_$;
ALTER FUNCTION stable.osmmembers_pack(jsonb) OWNER TO postgres;
--
-- Name: osmmembers_seems_pack(jsonb); Type: FUNCTION; Schema: stable; Owner: postgres
--
CREATE FUNCTION stable.osmmembers_seems_pack(jsonb) RETURNS boolean
LANGUAGE sql IMMUTABLE
AS $_$
SELECT CASE WHEN jsonb_typeof($1)='object' AND (
SELECT bool_and( jsonb_typeof($1->t) = 'object' )
FROM (
VALUES ('n'), ('w'), ('r')
) t(t)
) THEN true ELSE false END
$_$;
ALTER FUNCTION stable.osmmembers_seems_pack(jsonb) OWNER TO postgres;
--
-- Name: osmmembers_seems_unpack(jsonb, integer); Type: FUNCTION; Schema: stable; Owner: postgres
--
CREATE FUNCTION stable.osmmembers_seems_unpack(p_to_test jsonb, p_limit_tests integer DEFAULT 5) RETURNS boolean
LANGUAGE sql IMMUTABLE
AS $_$
SELECT substr(k,1,1)~'^[nwr]$' AND substr(k,2)~'^[0-9]+$'
FROM jsonb_each_text(p_to_test) t(k,gtype) LIMIT p_limit_tests
$_$;
ALTER FUNCTION stable.osmmembers_seems_unpack(p_to_test jsonb, p_limit_tests integer) OWNER TO postgres;
--
-- Name: rel_dup_properties(bigint, character, bigint, jsonb, text); Type: FUNCTION; Schema: stable; Owner: postgres
--
CREATE FUNCTION stable.rel_dup_properties(p_osm_id bigint, p_osm_type character, p_members_md5_int bigint, p_members jsonb, p_kname text DEFAULT 'dup_geoms'::text) RETURNS jsonb
LANGUAGE sql IMMUTABLE
AS $$
-- (atualmente 0,1% dos casos pode não estar duplicando relation...)
SELECT CASE
WHEN p_kname IS NULL OR x IS NULL THEN x -- ? x nunca é null
ELSE jsonb_build_object(p_kname,x)
END
FROM (
-- array de duplicados, eliminando lista de ways e relations já que é duplicada
SELECT jsonb_agg(x #- '{members,w}' #- '{members,r}') x
FROM (
(
SELECT stable.rel_properties(id) || jsonb_build_object('id','R'||id) x
FROM planet_osm_rels
WHERE p_osm_type='r' AND (
(id != abs(p_osm_id) AND members_md5_int=p_members_md5_int)
OR
id = ( -- check case of super-realation of one relation
SELECT (members->'r'->jsonb_object_1key(members->'r')->>0)::bigint
FROM planet_osm_rels
WHERE id=abs(p_osm_id) AND members->>'r_size'='1' AND not(members?'w')
) -- /=
) -- /AND
) -- /select
UNION
SELECT stable.way_properties(id) || jsonb_build_object('id','W'||id) x
FROM planet_osm_ways
WHERE
(p_osm_type='w' AND id != p_osm_id AND nodes_md5_int=p_members_md5_int)
OR
id = ( -- check case of realation of one way
SELECT (members->'w'->jsonb_object_1key(members->'w')->>0)::bigint
FROM planet_osm_rels
WHERE p_osm_type='r' AND id=abs(p_osm_id)
AND members->>'w_size'='1' AND not(members?'r')
) -- /=
) t1
) t2
$$;
ALTER FUNCTION stable.rel_dup_properties(p_osm_id bigint, p_osm_type character, p_members_md5_int bigint, p_members jsonb, p_kname text) OWNER TO postgres;
--
-- Name: rel_properties(bigint); Type: FUNCTION; Schema: stable; Owner: postgres
--
CREATE FUNCTION stable.rel_properties(p_osm_id bigint) RETURNS jsonb
LANGUAGE sql IMMUTABLE
AS $$
SELECT tags || jsonb_build_object('members',members)
FROM planet_osm_rels r
WHERE id = abs(p_osm_id)
$$;
ALTER FUNCTION stable.rel_properties(p_osm_id bigint) OWNER TO postgres;
--
-- Name: save_city_test_names(text); Type: FUNCTION; Schema: stable; Owner: postgres
--
CREATE FUNCTION stable.save_city_test_names(p_root text DEFAULT '/tmp/'::text) RETURNS TABLE(city_name text, osm_id bigint, filename text)
LANGUAGE sql IMMUTABLE
AS $$
SELECT t1.name_path, t1.id,
file_put_contents(p_root||replace(t1.name_path,'/','-')||'.json', jsonb_pretty((
SELECT
ST_AsGeoJSONb( (SELECT ST_SimplifyPreserveTopology(way,0) FROM planet_osm_polygon WHERE osm_id=-r1.id), 6, 1, 'R'||r1.id::text,
jsonb_strip_nulls(stable.rel_properties(r1.id)
|| COALESCE(stable.rel_dup_properties(r1.id,'r',r1.members_md5_int,r1.members),'{}'::jsonb) )
)
FROM planet_osm_rels r1 where r1.id=t1.id
)) ) -- /selct /pretty /file
FROM (
SELECT *, stable.getcity_rels_id(name_path) id from stable.city_test_names
) t1, LATERAL (
SELECT * FROM planet_osm_rels r WHERE r.id=t1.id
) t2;
$$;
ALTER FUNCTION stable.save_city_test_names(p_root text) OWNER TO postgres;
--
-- Name: std_geojsonb(bigint, bigint, jsonb); Type: FUNCTION; Schema: stable; Owner: postgres
--
CREATE FUNCTION stable.std_geojsonb(p_id bigint, p_members_md5_int bigint, p_members jsonb) RETURNS jsonb
LANGUAGE sql IMMUTABLE
AS $$
SELECT ST_AsGeoJSONb(
(SELECT way FROM planet_osm_polygon WHERE osm_id=-p_id),
6,
1,
'R'||p_id::text,
jsonb_strip_nulls(stable.rel_properties(p_id) || COALESCE(stable.rel_dup_properties(
p_id, 'r', p_members_md5_int, p_members), '{}'::jsonb
) )
)
$$;
ALTER FUNCTION stable.std_geojsonb(p_id bigint, p_members_md5_int bigint, p_members jsonb) OWNER TO postgres;
--
-- Name: tags_split_prefix(jsonb, text); Type: FUNCTION; Schema: stable; Owner: postgres
--
CREATE FUNCTION stable.tags_split_prefix(jsonb, text DEFAULT 'name:'::text) RETURNS jsonb
LANGUAGE sql IMMUTABLE
AS $_$
-- transforma objeto com prefixos em objeto com sub-objectos.
SELECT ($1-stable.osm_to_jsonb_remove_prefix($1)) || jsonb_build_object($2,(
SELECT jsonb_object_agg(substr(t1,t2.l+1),$1->t1)
FROM jsonb_object_keys($1) t1, (select length($2) l) t2
WHERE position($2 in t1)=1
))
$_$;
ALTER FUNCTION stable.tags_split_prefix(jsonb, text) OWNER TO postgres;
--
-- Name: tags_to_csv(jsonb, text); Type: FUNCTION; Schema: stable; Owner: postgres
--
CREATE FUNCTION stable.tags_to_csv(p_input jsonb, p_sep text DEFAULT '; '::text) RETURNS text
LANGUAGE sql IMMUTABLE
AS $_$
SELECT array_to_string(array_agg(key||'='||value),p_sep)
FROM jsonb_each_text($1)
$_$;
ALTER FUNCTION stable.tags_to_csv(p_input jsonb, p_sep text) OWNER TO postgres;
--
-- Name: way_properties(bigint); Type: FUNCTION; Schema: stable; Owner: postgres
--
CREATE FUNCTION stable.way_properties(p_osm_id bigint) RETURNS jsonb
LANGUAGE sql IMMUTABLE
AS $$
SELECT tags || jsonb_build_object(
'nodes',nodes,
'nodes_md5',LPAD(to_hex(nodes_md5_int),16,'0')
)
FROM planet_osm_ways r
WHERE id = p_osm_id
$$;