Home

parece que no README onde temos term_lib seria tlib.

Montagem dos prefixos e abreviações de prefixo

Temos as seguintes definições padronizadas de namespaces:

nscount	nsid	label	description	lang	is_base	fk_partof	kx_regconf	jinfo	created
1	1	vianameprefix	Prefixo de nome de via (tipo de logradouro)	pt	t		portuguese		2020-09-30
2	2	vianame	Nome de via sem prefixo e por extenso	pt	t		portuguese		2020-09-30
3	4	vianameprefix-abv	Abreviação de prefixo de nome de via	pt	f		portuguese		2020-09-30

CREATE or replace FUNCTION tlib.n2c_check(p_term text, p_ns int) RETURNS int AS $wrap$
  select id from tlib.n2c_tab(p_term,p_ns)
$wrap$ language SQL immutable;

CREATE or replace FUNCTION tstore.upsert_normalize(
  p_name text, p_ns integer, p_info jsonb DEFAULT NULL, p_iscanonic boolean DEFAULT false,
  p_fkcanonic integer DEFAULT NULL, p_issuspect boolean DEFAULT false,
  p_iscult boolean DEFAULT NULL, p_ref integer DEFAULT NULL
) RETURNS int AS $wrap$
  SELECT tstore.upsert( tlib.normalizeterm($1), $2, $3, $4, $5, $6, $7, $8 )
$wrap$ language SQL;

CREATE or replace FUNCTION optim.vianame_insert(
  -- falta inser-array para lotes
  p_name text, -- nome completo da via
  p_is_current boolean DEFAULT true
) RETURNS int AS $f$
  WITH afixes AS (
    SELECT aa[2] as prefix_id,  CASE WHEN aa[2] is not null THEN 
        tstore.upsert( array_to_string(p[aa[1]+1:],' '), 2 ) 
        ELSE NULL END as sufix_id
    FROM (
     SELECT p, CASE
       -- not elegant code but supposing compiler optimization (otherwise better is pgPLSQL)
       WHEN tlib.n2c_check(array_to_string(p[1:3],' '),1) is not null THEN array[3, tlib.n2c_check(array_to_string(p[1:3],' '),1) ]
       WHEN tlib.n2c_check(p[1]||' '||p[2],1)  is not null THEN array[2, tlib.n2c_check(p[1]||' '||p[2],1)]
       WHEN tlib.n2c_check(p[1],1)  is not null THEN array[1, tlib.n2c_check(p[1],1)]
       WHEN tlib.n2c_check(p[1],3)   is not null THEN array[1, tlib.n2c_check(p[1],3)]
       ELSE null
       END AS aa
     FROM (SELECT regexp_split_to_array(tlib.normalizeterm(p_name),'\s+')) t1(p) -- or lower(unaccent(trim(p_name)))
    ) t2
  )  -- ,ins1 AS (
     INSERT INTO optim.vianame (tprefix_id, tname_id, is_current) 
        SELECT prefix_id, sufix_id, p_is_current
        FROM afixes
        WHERE prefix_id IS NOT NULL AND sufix_id IS NOT NULL
     ON CONFLICT DO NOTHING
     RETURNING tprefix_id
$f$ language SQL;
-- ex. select optim.vianame_insert(x) FROM (SELECT DISTINCT jtags->>'name' AS x from osm_road where jtags?'name') t;

insert into tstore.source (name) values('addressforall'); -- supposing id=1

create table lixo (viaNamePrefix text,"viaNamePrefix-abv" text, is_pref boolean);
COPY lixo FROM '/tmp/pg_io/nsPair-viaNamePrefix.csv' CSV HEADER;
-- SELECT tlib.normalizeterm("viaNamePrefix-abv") abv, tlib.normalizeterm(vianameprefix) FROM lixo;

SELECT tstore.upsert_normalize(vianameprefix,1,null,true,null,false,true,1) as is_ok
FROM ( SELECT distinct vianameprefix  FROM LIXO order by 1) t;

SELECT tstore.upsert_normalize(
   "viaNamePrefix-abv",4,null, false, tlib.n2c_check(tlib.normalizeterm(vianameprefix),1), not(is_pref), is_pref, 1
  ) as is_ok
FROM lixo where "viaNamePrefix-abv">'';

update tstore.term set fk_ns=1 where fk_ns=4 AND term in ('rodo anel','vicinal');

SELECT tstore.upsert_normalize(
   trim(term,'.'),4,null, false, fk_canonic, is_suspect, is_cult, 1
  ) as is_ok
FROM (select * from tstore.term where fk_ns=4 and trim(term,'.')=replace(term,'.','') order by term) t;

SELECT tstore.upsert_normalize(
   unaccent(term),1,null, false, id, is_suspect, false, 1
  ) as is_ok
FROM (select * from tstore.term where fk_ns=1 and unaccent(term)!=term order by term) t;

DROP table lixo;

Vianame

CREATE TABLE optim.vianame(
   id serial           NOT NULL PRIMARY KEY,
   tprefix_id int      NOT NULL REFERENCES optim.term(id), -- rua, avenida, etc.
   tname_id int        NOT NULL REFERENCES optim.term(id), -- nome mesmo
   is_current boolean  NOT NULL DEFAULT true
   ,UNIQUE(tprefix_id,tname_id)
);
CREATE VIEW optim.vw01_vianame AS
  select v.*, t1.term||' '||t2.term as via_name
  from optim.vianame v
     INNER JOIN tstore.term t1 ON v.tprefix_id=t1.id
     INNER JOIN tstore.term t2 ON v.tname_id=t2.id
;
---
CREATE or replace FUNCTION tlib.n2c_check(p_term text, p_ns int) RETURNS int AS $f$
  select id from tlib.n2c_tab(p_term,p_ns)
$f$ language SQL immutable;

CREATE or replace FUNCTION optim.vianame_insert(
  -- falta inser-array para lotes
  p_name text, -- nome completo da via
  p_is_current boolean DEFAULT true
) RETURNS int AS $f$
  WITH afixes AS (
    SELECT aa[2] as prefix_id,  CASE WHEN aa[2] is not null THEN 
        tstore.upsert( array_to_string(p[aa[1]+1:],' '), 2 ) 
        ELSE NULL END as sufix_id
    FROM (
     SELECT p, CASE
       -- not elegant code but supposing compiler optimization (otherwise better is pgPLSQL)
       WHEN tlib.n2c_check(array_to_string(p[1:3],' '),1) is not null THEN array[3, tlib.n2c_check(array_to_string(p[1:3],' '),1) ]
       WHEN tlib.n2c_check(p[1]||' '||p[2],1)  is not null THEN array[2, tlib.n2c_check(p[1]||' '||p[2],1)]
       WHEN tlib.n2c_check(p[1],1)  is not null THEN array[1, tlib.n2c_check(p[1],1)]
       WHEN tlib.n2c_check(p[1],3)   is not null THEN array[1, tlib.n2c_check(p[1],3)]
       ELSE null
       END AS aa
     FROM (SELECT regexp_split_to_array(tlib.normalizeterm(p_name),'\s+')) t1(p) -- or lower(unaccent(trim(p_name)))
    ) t2
  )  -- ,ins1 AS (
     INSERT INTO optim.vianame (tprefix_id, tname_id, is_current) 
        SELECT prefix_id, sufix_id, p_is_current
        FROM afixes
        WHERE prefix_id IS NOT NULL AND sufix_id IS NOT NULL
     ON CONFLICT DO NOTHING
     RETURNING tprefix_id
$f$ language SQL;
-- ex. select optim.vianame_insert(x) FROM (SELECT DISTINCT jtags->>'name' AS x from osm_road where jtags?'name') t;

select optim.vianame_insert(x) FROM (select distinct logradouro as x from lix) t; -- select * from tlib.n2c_tab('travessa',1); -- tem TV mas não tv. .. ver sinônimos de travessa c2ns

Cargas Enio

CREATE VIEW br_pr_patobranco.v_endereco AS -- mais importante
  SELECT n_lt.gid,
    n_lt.geom,
    n_lt.quadra,
    n_lt.lote,
    e.logradouro,
    e.num_pred
   FROM br_pr_patobranco.v_num_lote n_lt
     JOIN br_pr_patobranco.endereco e ON n_lt.chave = ((e.num_quadra::text || '_'::text) || e.num_lote::text)
;
CREATE VIEW br_pr_patobranco.v_num_lote 
  SELECT n_lt.gid,
    n_lt.geom,
    n_lt.text AS lote,
    v_qd.quadra,
    ((v_qd.quadra::text || '_'::text) || 0) || n_lt.text::text AS chave
   FROM br_pr_patobranco.num_lote n_lt
     JOIN br_pr_patobranco.v_quadra v_qd ON st_intersects(n_lt.geom, v_qd.geom)
;
CREATE VIEW br_pr_patobranco.v_quadra AS
  SELECT qd.gid,
    qd.geom,
    n_qd.quadra
   FROM br_pr_patobranco.quadras qd
     JOIN br_pr_patobranco.v_num_quadra n_qd ON qd.gid = n_qd.qd_gid
;
---------------------

CREATE EXTENSION unaccent;

CREATE TABLE optim.term(
   id serial           NOT NULL PRIMARY KEY,
   term  text          NOT NULL,
    -- term_asc, term_metaphone, etc.
   ns int not null default 1,   -- namespace. 1= logradouro_name, 2=prefixo de logrdouro.
   kx_parts int, -- array_upper(regexp_split_to_array(term,' '),1)
   info jsonb
   ,UNIQUE(ns,term)
);