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(*) filter (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:
WITH
 r AS (select *, st_area(geom,true) as area FROM osm_poly)
 SELECT c.isolabel_ext,
   count(*) n_poligonos,
   count(distinct r.jtags->>'addr:street') AS n_streetnames,
   count(*) filter (where r.jtags?'addr:housenumber') n_housenum,
   count(*) filter (where r.jtags->>'building' = 'yes') n_buildings,
   round(AVG(r.area)) as avg_m2,
   round(percentile_disc(0.5) within group (order by r.area)) as median_m2
FROM (r inner join vw_vivo_osm_city c ON c.osm_id=r.jurisdiction_osm_id), 
     LATERAL (
       select round(percentile_disc(0.5) within group (order by r.area)) as median_m2
       FROM r
       WHERE jurisdiction_osm_id=c.osm_id AND not(r.jtags?'boundary') 
     ) t2
WHERE  not(r.jtags?'boundary')
GROUP BY 1 ORDER BY 1;

isolabel_ext	via_tipo	n_segs	n_names	km_de_via
BR-PR-PatoBranco	highway	228	21	92
BR-RS-PortoAlegre	highway	3622	295	680
BR-RS-PortoAlegre	railway	50	2	35
BR-SP-Cabreuva	highway	180	26	121

isolabel_ext	n_pts	n_names	n_tagstreet
BR-PR-PatoBranco	898	94	35
BR-RS-PortoAlegre	495344	4172	2768
BR-SP-Cabreuva	308	49	14

isolabel_ext	n_lotes	n_streetnames	n_housenum
BR-PR-PatoBranco	898	21	12
BR-RS-PortoAlegre	495344	862	3058
BR-SP-Cabreuva	308	12	5