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

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

-- report roads de todos os tipos:
CREATE VIEW vw01rpt_osm_roads AS
 SELECT iif(isolabel_ext=(lag(isolabel_ext) over ()),'',isolabel_ext) AS isolabel_ext,
       via_tipo, n_segs, n_names, km_de_via
 FROM (
	select c.isolabel_ext, 
	   CASE WHEN r.jtags?'highway' THEN 'highway'
			WHEN r.jtags?'railway' THEN 'railway'
			WHEN r.jtags?'waterway' THEN 'waterway'
			ELSE 'lixo?'
	   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 (SELECT * FROM osm_road UNION SELECT * FROM osm_road_quadra) r 
		 inner join osm_city  c  ON c.osm_id=r.jurisdiction_osm_id
	WHERE not(r.jtags?'boundary')
	GROUP BY 1,2 
	ORDER BY 1,2
 ) t;

-- 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

Comparando

osm_id	name
242397	Porto Alegre
297599	Pato Branco
298426	Cabreúva

COPY (
  select distinct logradouro from br_pr_patobranco.endereco order by 1
 ) to '/tmp/pr_pato_logradouros.csv' CSV HEADER;
COPY (
  select distinct upper(jtags->>'name') as logradouro 
  from osm_road where jurisdiction_osm_id=297599 order by 1;
)  to '/tmp/pr_pato_logradouros-osm.csv' CSV HEADER;