Skip to content

Home

Jump to bottom
Peter edited this page Oct 1, 2020 · 9 revisions

parece que no README onde temos term_lib seria tlib.

Montagem dos prefixos e abreviações de prefixo

Usando o sh make.sh para a base DL03. 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 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;

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 optim.vianame_insert(
  -- falta insert-array para conjuntos
  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 
        COALESCE(  tstore.upsert( array_to_string(p[aa[1]+1:],' '), 2 ),    tlib.n2c_check(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],4)  is not null THEN array[1, tlib.n2c_check(p[1],4)]
       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;
-- ex. select optim.vianame_insert(x) FROM (select distinct logradouro as x from lix order by 1) t;

Cargas Enio

osm_id parent_abbrev name jurisd_local_id
242397 RS Porto Alegre 4314902
297599 PR Pato Branco 4118501
298426 SP Cabreúva 3508405 
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
;
---------------------
-- lix contem br_pr_patobranco.v_endereco
select optim.vianame_insert(x) FROM (select distinct logradouro as x from lix order by 1) t; -- ok
-- SELECT DISTINCT jtags->>'name' AS x FROM osm_road WHERE jurisdiction_osm_id=297599 AND jtags?'name';
-- SELECT tstore.upsert_normalize('Perimetral',1,null,true,null,false,true,1) as is_ok;
select optim.vianame_insert(x) FROM (SELECT DISTINCT jtags->>'name' AS x FROM osm_road WHERE jurisdiction_osm_id=297599 AND jtags?'name') t;

Canonizando

A canonização pode ocorrer posteriormente ao registro da rua, e pode ser de dois tipos:

  • canonização terminológica: por exemplo formas acentuada e sem acento.
    neste caso a resolução é feita na base terminológica, e o ID precisa ser modificado no registro de via.

  • canonização específica: por exemplo nomes abreviados "João M. Silva" pode ser "João Maria Silva" numa cidade e "João Merlin Silva" na outra.
    Neste caso a entrada do sinônimo é dada em optim.vianame_synonym e o dado original de optim.vianame modificado.

O correto é canonizar sinônimos depois de registrada a via, ou seja, é o algoritmo mais complexo... Por hora avaliemos apenas a canonização terminológica a priori:

create view tstore.vw_ns2term_pair_accent AS
 select t1.id id1, t1.fk_canonic canonic1, t1.term term1, t2.term term2, t2.id id2, t2.fk_canonic canonic2
 from tstore.term t1, tstore.term t2
 where t1.fk_ns=2 AND t2.fk_ns=2
      AND t1.id>t2.id
      AND unaccent(t1.term)=unaccent(t2.term) AND t1.term!=t2.term AND t1.fk_canonic is null and t2.fk_canonic is null
 order by t1.term, t2.term
;
-- copy (select * from tstore.vw_ns2term_pair_accent) to '/tmp/nomesAcent.csv' CSV HEADER;

Clone this wiki locally