lixo, endereços Curitiba

Lembretes, para planet 2020 modo default (sem conversão JSONb nas tags).

Functions

-- from old https://github.com/OSMBrasil/simple-osmWd2csv/blob/master/src/step0-1-osmWd_libPub.sql
CREATE or replace FUNCTION array_distinct_sort (
  ANYARRAY,
  p_no_null boolean DEFAULT true
) RETURNS ANYARRAY AS $f$
  SELECT CASE WHEN array_length(x,1) IS NULL THEN NULL ELSE x END -- same as  x='{}'::anyarray
  FROM (
  	SELECT ARRAY(
        SELECT DISTINCT x
        FROM unnest($1) t(x)
        WHERE CASE
          WHEN p_no_null  THEN  x IS NOT NULL
          ELSE  true
          END
        ORDER BY 1
   )
 ) t(x)
$f$ language SQL strict IMMUTABLE;

CREATE or replace FUNCTION array_merge_sort(
  ANYARRAY, ANYARRAY, boolean DEFAULT true
) RETURNS ANYARRAY AS $wrap$
  SELECT array_distinct_sort(array_cat($1,$2),$3)
$wrap$ language SQL IMMUTABLE;

DROP AGGREGATE IF EXISTS array_agg_cat(anyarray) CASCADE;
CREATE AGGREGATE array_agg_cat(anyarray) (
  SFUNC=array_cat,
  STYPE=anyarray,
  INITCOND='{}'
);

Views

CREATE VIEW stable.vw1_ptof_pr_curitiba AS
 SELECT n.osm_id,
    n.way AS geom,
    st_geohash(n.way, 11) AS geohash,
    n.tags,
    'OSMn'::text AS origin,
    0 AS area_m2
   FROM planet_osm_point n,
    ( SELECT planet_osm_polygon.way AS geom
           FROM planet_osm_polygon
          WHERE planet_osm_polygon.osm_id = -297514) t
  WHERE n.way && t.geom AND st_contains(t.geom, n.way) AND n.tags?'addr:street' AND n.tags?'addr:housenumber'
UNION
 SELECT n.osm_id,
    st_centroid(n.way) AS geom,
    st_geohash(st_centroid(n.way), 11) AS geohash,
    n.tags,
    'OSMr'::text AS origin,
    round(st_area(n.way::geography, true))::integer AS area_m2
   FROM planet_osm_polygon n,
    ( SELECT planet_osm_polygon.way AS geom
           FROM planet_osm_polygon
          WHERE planet_osm_polygon.osm_id = '-297514'::integer) t
  WHERE n.way && t.geom AND st_contains(t.geom, n.way) AND n.tags?'addr:street' AND n.tags?'addr:housenumber';

create VIEW stable.vw2_ptof_pr_curitiba AS
 SELECT geohash,
    osm_id,
    tags -> 'addr:street'::text AS logradouro,
    tags -> 'addr:housenumber'::text AS numero,
    regexp_replace(tags -> 'addr:postcode', '^(\d+)(\d\d\d)$', '\1-\2') AS cep,
    area_m2,
    geom
   FROM stable.vw1_ptof_pr_curitiba
  WHERE tags ? 'addr:postcode' AND (origin = 'OSMn' OR area_m2 < 10000)
;

create view stable.vw3_ptof_pr_curitiba as
 SELECT vw2_ptof_pr_curitiba.geohash,
    vw2_ptof_pr_curitiba.logradouro,
    vw2_ptof_pr_curitiba.numero,
    vw2_ptof_pr_curitiba.cep
   FROM stable.vw2_ptof_pr_curitiba
  WHERE NOT (substr(vw2_ptof_pr_curitiba.geohash, 1, 8) IN ( SELECT substr(vw2_ptof_pr_curitiba_1.geohash, 1, 8) AS substr
           FROM stable.vw2_ptof_pr_curitiba vw2_ptof_pr_curitiba_1
          GROUP BY (substr(vw2_ptof_pr_curitiba_1.geohash, 1, 8))
         HAVING count(*) > 1 AND array_length(array_distinct_sort(array_agg(vw2_ptof_pr_curitiba_1.numero)), 1) = 1))
  ORDER BY vw2_ptof_pr_curitiba.geohash
;

---------------
-- AGRO
select geohash, 
       nom_tipo_seglogr || ' '||nom_seglogr AS logradouro, 
       num_endereco AS numero, 
       substr(cep::text,1,5)|| '-'|| substr(cep::text,6)  AS cep, 
       'IBGEagro17' AS fonte  
from  vw_ibge_agro2017_curitiba ;