Exportação de dados em CSV OpenAddress

Conforme issue 20, essa é uma demanda de interoperabilidade.

Foi realizado alter table ingest.layer_file add column country_id integer para controlar o ID ISO de país já na ingestão, e posteriormente incluir no pack_id por conversão decimal. Por exemplo se o pack_ID do Brasil é 35, e o ID do Brasil é 76, então o novo pack_id será (lpad(country_id::text,3,'0')||lpad(pack_id::text,6,'0'))::int...

Feitos UPDATE ingest.layer_file SET country_id=170 WHERE pck_id IN (1.001, 2.001, 3.001); e UPDATE ingest.layer_file SET country_id=76 WHERE country_id IS NULL;.

Mais gambiarras:

CREATE TABLE ingest.pack_jurisdiction (
  country_id int not null,
  pack_id int not null,
  REGION text,
  CITY text not null,
  DISTRICT text,
  Country text not null,
  UNIQUE (country_id, pack_id)
);
INSERT INTO ingest.pack_jurisdiction VALUES 
  (170,1,'DC','Bogota','','CO'),
  (170,2,'ANT','Medellin','','CO')
;

DROP VIEW ingest.out01asis_CO_geoaddress;
CREATE VIEW ingest.out01asis_CO_geoaddress AS
  SELECT round(ST_X(ft.geom),7) as lon,  round(ST_Y(ft.geom),7) as lat,
         ft.properties->>'house_number' AS number,
         CASE 
           WHEN ft.properties?'via' THEN ft.properties->>'via'
           ELSE ft.properties->>'pdonvial' END AS street,
         j.CITY,j.DISTRICT,j.REGION,NULL AS POSTCODE, ft.feature_id AS ID, null as HASH
  FROM
    ingest.feature_asis ft
    INNER JOIN ingest.vw03full_layer_file lf ON lf.file_id = ft.file_id
    INNER JOIN ingest.pack_jurisdiction j ON j.country_id=lf.country_id AND lf.pck_id::int=j.pack_id
  WHERE lf.country_id=170 AND substr(lf.ftname,1,10)='geoaddress' 
        AND lf.pck_id::int IN (1,2) AND  GeometryType(ft.geom)='POINT'
;
COPY (select * from ingest.out01asis_CO_geoaddress where city='Bogota')
  TO '/tmp/CO-DC-Bogota2021.csv' CSV HEADER; -- 1793783
COPY (select * from ingest.out01asis_CO_geoaddress where city='Medellin')
  TO '/tmp/CO-DC-Medellin2021.csv' CSV HEADER; -- 474520

IGAC

country_id	pck_id	file_id	ftname	file_type	file
170	3.001	7	geoaddress_full	shp	/tmp/sandbox/_pk3_001/shp/U_NOMENCLATURA_DOMICILIARIA.shp
170	3.001	8	geoaddress_full	shp	/tmp/sandbox/_pk3_001/shp/R_NOMENCLATURA_DOMICILIARIA.shp

DROP VIEW ingest.out02asis_CO_geoaddress;
CREATE VIEW ingest.out02asis_CO_geoaddress AS
  SELECT round(ST_X(geom),7) as lon,  round(ST_Y(geom),7) as lat,
  -- casas SELECT round(ST_X(geom),9) as lon,  round(ST_Y(geom),9) as lat,
  -- cuidado! SELECT round(ST_Y(geom),9) as lon,  round(ST_X(geom),9) as lat,
         properties->>'house_number' AS number,
         NULL AS street,
         null as CITY, null as DISTRICT, null as REGION, null AS POSTCODE,
         CASE WHEN file_id=8 THEN (-1)*feature_id ELSE feature_id END AS gid,
         null as HASH
  FROM (
   SELECT ft.file_id, ft.feature_id, ft.properties, st_centroid(ft.geom) as geom, lf.ftname, lf.country_id
   FROM
     ingest.feature_asis ft
     INNER JOIN ingest.vw03full_layer_file lf ON lf.file_id = ft.file_id
   WHERE lf.country_id=170 AND substr(lf.ftname,1,10)='geoaddress' AND lf.file_id IN (7,8)
 ) t;

COPY (
  SELECT lon, lat, number, street, city, district, region, postcode, 
  ROW_NUMBER () OVER () AS id, hash
  FROM (
    select * from ingest.out01asis_CO_geoaddress
    UNION 
    select * from ingest.out02asis_CO_geoaddress
  ) t
) TO '/tmp/CO2021full.openaddress.tsv'
  CSV HEADER DELIMITER E'\t'; -- 5383062 lines

Lembretes

Padronização de nomes de VIEW: todas iniciam por vw, sintaxe vw{number}{label} são padronizadas para múltiplos usos, vwfast para consulta rápida de perfil dos dos no terminal, e demais vw_* são especializadas ou em análise.

As views abaixo deveriam estar definidas em ingest-step1-ini.sql, exceto por ingest.vwfast_layer_file.

CREATE VIEW ingest.vw01info_feature_type AS 
 SELECT f.ftid,
    f.ftname,    f.geomtype,
    f.need_join, f.description,
    COALESCE(f.info, '{}'::jsonb) || ((
           SELECT to_jsonb(t2.*) AS to_jsonb
           FROM ( SELECT c.ftid AS class_ftid,
                    c.ftname AS class_ftname,
                    c.description AS class_description,
                    c.info AS class_info
                   FROM ingest.feature_type c
                  WHERE c.geomtype = 'class'::text 
                        AND c.ftid::float = (10::float * round((f.ftid / 10)::float))) t2)) AS info
   FROM ingest.feature_type f
   WHERE f.geomtype <> 'class';

DROP  VIEW ingest.vw03full_layer_file;
CREATE VIEW ingest.vw03full_layer_file AS
 SELECT lf.*,
    ft.ftname,              ft.geomtype,
    ft.need_join,           ft.description,
    ft.info AS ft_info
   FROM ingest.layer_file lf
     JOIN ingest.vw01info_feature_type ft ON lf.ftid = ft.ftid;

CREATE VIEW ingest.vwfast_layer_file AS
 SELECT country_id, pck_id,    file_id,
    ftname,        file_type,
    file_meta ->> 'file' AS file
   FROM ingest.vw03full_layer_file
  ORDER BY vw03full_layer_file.pck_id, vw03full_layer_file.file_id;