Cruzando dados novos com os do OSM e correios

Depois de Trazendo dados pertinentes do OSM,

dl03t_main com osm_road, osm_point e osm_city

-- report roads
select c.isolabel_ext, count(*) n, round(sum(st_length(r.geom,true))/1000) as km 
from osm_road r inner join osm_city c  ON c.osm_id=r.jurisdiction_osm_id group by 1;

create view vw_vivo_osm_city AS 
  SELECT * FROM osm_city WHERE isolabel_ext IN ('BR-SP-Cabreuva','BR-RS-PortoAlegre','BR-PR-PatoBranco');

-- relatorio de vias:
select c.isolabel_ext, 
   CASE WHEN r.jtags?'highway' THEN 'highway'
        WHEN r.jtags?'railway' THEN 'railway'
        WHEN r.jtags?'bridge' THEN 'bridge'
        WHEN r.jtags?'viaduct' THEN 'viaduct'
        WHEN r.jtags?'tunnel' THEN 'tunnel'
   END AS via_tipo,
   count(*) n_segs,
   count(distinct r.jtags->>'name') n_names,
   round(sum(st_length(r.geom,true))/1000) as km_de_via
from osm_road r inner join vw_vivo_osm_city  c  ON c.osm_id=r.jurisdiction_osm_id
WHERE (r.jtags?'highway' OR r.jtags?'railway' OR r.jtags?'bridge' OR r.jtags?'viaduct' OR r.jtags?'tunnel')
     AND not(r.jtags?'boundary')
GROUP BY 1,2 ORDER BY 1,2;

-- relatorio de pontos:
select c.isolabel_ext,
   count(*) n_pts,
   count(distinct r.jtags->>'name') n_names,
   count(*) filer (where r.jtags?'addr:street') n_tagstreet
from osm_poly r inner join vw_vivo_osm_city  c  ON c.osm_id=r.jurisdiction_osm_id
WHERE not(r.jtags?'boundary')
GROUP BY 1 ORDER BY 1;

-- relatorio de lotes:
select c.isolabel_ext,
   count(*) n_lotes,
   count(distinct r.jtags->>'name') n_names,
   count(*) filer (where r.jtags?'addr:housenumber') n_housenum
from osm_point r inner join vw_vivo_osm_city  c  ON c.osm_id=r.jurisdiction_osm_id
WHERE (r.jtags?'highway' OR r.jtags?'railway' OR r.jtags?'bridge' OR r.jtags?'viaduct' OR r.jtags?'tunnel')
     AND not(r.jtags?'boundary')
GROUP BY 1 ORDER BY 1;