Skip to content

Testes e exemplos de uso

Jump to bottom
Peter edited this page Oct 3, 2020 · 11 revisions

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_source int DEFAULT NULL,
  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,null,false,null,false,null,p_source),
            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,2) FROM (SELECT DISTINCT jtags->>'name' AS x from osm_road where jtags?'name') t;
-- ex. select optim.vianame_insert(x,3) 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
;
---------------------
-- SOURCE  =
--  1 | addressforall     |       | 2020-10-01
--  3 | osm2020-09        |       | 2020-10-01
--  4 | pref. pato branco |       | 2020-10-01
--  6 | pref. porto alegre |       | 2020-10-02

-- lix contem br_pr_patobranco.v_endereco
select optim.vianame_insert(x,4) 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,3) FROM (SELECT DISTINCT jtags->>'name' AS x FROM osm_road WHERE jurisdiction_osm_id=297599 AND jtags?'name') t;

select optim.vianame_insert(x,6) FROM (select distinct concat(cdidecat, ' ', nmidepre, ' ', nmidelog)  as x from lix_poa order by 1) t;
select optim.vianame_insert(x,3) FROM (SELECT DISTINCT jtags->>'name' AS x FROM osm_road WHERE jurisdiction_osm_id=242397 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:

drop view tstore.vw_ns2term_pair_accent ;
create view tstore.vw_ns2term_pair_accent AS
 select t1.id id1, t1.fk_canonic canonic1, unaccent(t1.term)=t1.term AS is_not_accented1,
        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 -- so t1.term!=t2.term
      AND t1.fk_canonic is null and t2.fk_canonic is null
      AND unaccent(t1.term)=unaccent(t2.term)  
 order by t1.term, t2.term
;

-- copy (select * from tstore.vw_ns2term_pair_accent) to '/tmp/nomesAcent.csv' CSV HEADER;
UPDATE tstore.term
SET is_canonic=true
FROM tstore.vw_ns2term_pair_accent a
WHERE term.fk_ns=2 
      AND ((a.is_not_accented1 AND a.id2=term.id) OR (NOT(a.is_not_accented1) AND a.id1=term.id))
; -- 117
UPDATE tstore.term
SET fk_canonic= CASE WHEN a.is_not_accented1  THEN a.id2 ELSE a.id1 END
FROM tstore.vw_ns2term_pair_accent a
WHERE term.fk_ns=2 AND is_canonic=false
      AND ((a.is_not_accented1 AND a.id1=term.id) OR (NOT(a.is_not_accented1) AND a.id2=term.id))
; -- 117
--- QUANDO dois possuem acento, ver abaixo

-----
drop view tstore.vw_ns2term_pair_metaphone;
create view tstore.vw_ns2term_pair_metaphone AS
 select t1.id id1, t1.fk_source[1] src1, t1.fk_canonic canonic1, unaccent(t1.term)=t1.term AS is_not_accented1,
        t1.term term1, t2.term term2, t2.id id2, t2.fk_canonic canonic2, t2.fk_source[1] src2, length(t1.kx_metaphone)
 from tstore.term t1, tstore.term t2
 where t1.fk_ns=2 AND t2.fk_ns=2
      AND t1.term !~ '^[\d_\-\.,: ]+$'
      AND t2.term !~ '^[\d_\-\.,: ]+$'
      AND t1.id>t2.id -- so t1.term!=t2.term
      AND t1.fk_canonic is null and t2.fk_canonic is null
      AND t1.kx_metaphone=t2.kx_metaphone
      AND length(t1.kx_metaphone) > 2
 order by length(t1.kx_metaphone), t1.term, t2.term
;
-- copy (select * from tstore.vw_ns2term_pair_metaphone) to '/tmp/nomesMetaphone.csv' CSV HEADER;

Quando dois itens possuem acento, por exemplo o resíduo da consulta tstore.vw_ns2term_pair_accent:

id1 canonic1 is_not_accented1 term1 term2 id2 canonic2
14266 f padre joão batista réus padre joão batista reus 14265
14547 f sepé tiarajú sepé tiaraju 14546

É preciso fazer as correções locais (eleger o melhor ou criar um terceiro correto) e depois propagar por outros. Neste caso ambos na coluna term1 serão canônicos.

UPDATE tstore.term SET is_canonic=true
FROM tstore.vw_ns2term_pair_accent a
WHERE term.fk_ns=2  AND id IN (14266 , 14547 );

UPDATE tstore.term SET is_canonic=false, fk_canonic=14266 WHERE id=14265;
UPDATE tstore.term SET is_canonic=false, fk_canonic=14547 WHERE id=14546;
-- SELECT id, term, fk_canonic from tstore.term WHERE fk_canonic IN ( 14265,14546);
--  9964 | padre joao batista reus |      14265
-- 10735 | sepe tiaraju            |      14546

UPDATE tstore.term SET is_canonic=false, fk_canonic=14266 WHERE id=9964 ;
UPDATE tstore.term SET is_canonic=false, fk_canonic=14547 WHERE id=10735 ;

Abreviações residuais de POA

BC=beco
BV=boulevard
CA=cais
CICL=ciclovia
CN=?
DIR=diretriz (vias planejadas)
ESP=esplanada
GAL=galeria
I=ilha
JAR=jardim
LAGO=lago
LE=limite (está bagunçado, representa alguns perímetros de grandes propriedadaes)
LG=largo
MER=mercado
PCA=praça
PRQ=parque
PSL=passarela
RIO=rio
RP=rua particular (aparece em becos de favelas e ruelas de peq. condomínios)
RTL=rótula
TERM=terminal
TRAV=travessa
TRVS=travessia
VA=viela
VDT=viaduto
VP=via de pedestre
VTC=via de trânsito coletivo (?) (informais/favelas)

Correções

Insert de termo não pode permitir null, '', '-', números puros. !~ '^[\d_\-\.,: ]+$'

  • eng. josé ângelo bettega cassol | eng jose angelo bettega cassol
  • josé vieiro | jose viero
  • golda meier | golda meir
  • luiz luz | luis luz
  • ney remedi | nei remedi
  • souza mello | souza melo
  • urussunga | urussanga
  • upamaroty | upamaroti
  • dário damiane | dario damiani
  • atilio bettio | attilio bettio
  • cleveland | clevelândia
  • indianópolis | indianapolis
  • jaime telles | jaime teles
  • jaime telles | jayme telles
  • jayme telles | jaime teles
  • josé balis | jose bahlis
  • pierre bordieu | pierre bourdieu

Conferindo espacialmente

  • segmentos de via com interseção sobre buffer alheio (de ~2m sobre elemento contínuo de mesmo código de logradouro) resultando em segmento maior que 50%.
  • quando supostamente mesma via, indicar que são sinônimos...

Clone this wiki locally