download and prepare LT address data

lt-update.sh

echo 'Apskritys geometries (from geojson) to lt.adr_gra_apskritys'

ogr2ogr -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -lco overwrite=yes -lco schema=lt -lco launder=yes -lco geometry_name=geometry -lco fid=oid -nln adr_gra_apskritys -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_apskritys.json -progress

echo 'Apskritys props (from csv) to lt.adr_apskritys'

ogr2ogr -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -lco overwrite=yes -lco schema=lt -lco launder=yes -lco fid=oid -nln adr_apskritys https://www.registrucentras.lt/aduomenys/?byla=adr_apskritys.csv

echo 'Savivaldybės geometries (from geojson) to lt.adr_gra_savivaldybes'

ogr2ogr -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -lco overwrite=yes -lco schema=lt -lco launder=yes -lco geometry_name=geometry -lco fid=oid -nln adr_gra_savivaldybes -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_savivaldybes.json -progress

echo 'Savivaldybės props (from csv) to lt.adr_apskritys'

ogr2ogr -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -lco overwrite=yes -lco schema=lt -lco launder=yes -lco fid=oid -nln adr_savivaldybes https://www.registrucentras.lt/aduomenys/?byla=adr_savivaldybes.csv

echo 'Seniūnijas geometries (from geojson) to lt.adr_gra_seniunijos'

ogr2ogr -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -lco overwrite=yes -lco schema=lt -lco launder=yes -lco geometry_name=geometry -lco fid=oid -nln adr_gra_seniunijos -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_seniunijos.json -progress

echo 'Seniūnijas props (from csv) to lt.adr_seniunijos'

ogr2ogr -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -lco overwrite=yes -lco schema=lt -lco launder=yes -lco fid=oid -nln adr_seniunijos https://www.registrucentras.lt/aduomenys/?byla=adr_seniunijos.csv

echo 'Residential areas geometris to lt.adr_gra_gyvenamosios_vietoves'

ogr2ogr -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -lco overwrite=yes -lco schema=lt -lco launder=yes -lco geometry_name=geometry -lco fid=oid -nln adr_gra_gyvenamosios_vietoves -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_gyvenamosios_vietoves.json -progress

echo 'Residential areas props (from csv) to lt.adr_gyvenamosios_vietoves'

ogr2ogr -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -lco overwrite=yes -lco schema=lt -lco launder=yes -lco fid=oid -nln adr_gyvenamosios_vietoves https://www.registrucentras.lt/aduomenys/?byla=adr_gyvenamosios_vietoves.csv

echo 'Streetline geometries to lt.adr_gra_gatves'

ogr2ogr -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -lco overwrite=yes -lco schema=lt -lco launder=yes -lco geometry_name=geometry -lco fid=oid -nln adr_gra_gatves -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_gatves.json -progress

echo 'Streetline props (from csv) to lt.adr_gatves'

ogr2ogr -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -lco overwrite=yes -lco schema=lt -lco launder=yes -lco fid=oid -nln adr_gatves https://www.registrucentras.lt/aduomenys/?byla=adr_gatves.csv

echo 'Address point props (from csv) to lt.adr_stat_lr'

ogr2ogr -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -lco overwrite=yes -lco schema=lt -lco launder=yes -lco fid=oid -nln adr_stat_lr https://www.registrucentras.lt/aduomenys/?byla=adr_stat_lr.csv

echo 'AP subdivision, e.g. apartmentnumbers props (from csv) to lt.adr_pat_lr'

ogr2ogr -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -lco overwrite=yes -lco schema=lt -lco launder=yes -lco fid=oid -nln adr_pat_lr https://www.registrucentras.lt/aduomenys/?byla=adr_pat_lr.csv


echo 'Address point geometries to lt.adr_gra'

ogr2ogr -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -lco overwrite=yes -lco schema=lt -lco launder=yes -lco geometry_name=geometry -lco fid=oid -nln adr_gra -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_11.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_12.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_13.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_15.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_18.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_19.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_21.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_23.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_25.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_27.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_29.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_30.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_32.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_33.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_34.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_36.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_38.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_39.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_41.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_42.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_43.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_45.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_46.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_47.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_48.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_49.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_52.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_53.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_54.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_55.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_56.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_57.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_58.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_59.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_61.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_62.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_63.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_65.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_66.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_67.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_68.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_69.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_71.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_72.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_73.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_74.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_75.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_77.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_78.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_79.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_81.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_82.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_84.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_85.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_86.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_87.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_88.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_89.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_91.json -progress
ogr2ogr -update -append -f PostgreSQL "PG:host=localhost user=postgres dbname=postgres" -nln 'lt.adr_gra' -a_srs epsg:3346 -t_srs epsg:3346 https://www.registrucentras.lt/aduomenys/?byla=adr_gra_94.json -progress

echo 'Done import. Postprocessing...'


psql -h localhost -U postgres -d postgres -f postprocessing.sql

postprocessing.sql

alter table lt.adr_apskritys alter column adm_kodas type bigint using adm_kodas::bigint;
create unique index if not exists uidx__adr_apskritys on lt.adr_apskritys (adm_kodas);

alter table lt.adr_savivaldybes alter column adm_kodas type bigint using adm_kodas::bigint;
alter table lt.adr_savivaldybes alter column sav_kodas type bigint using sav_kodas::bigint;
create unique index if not exists uidx__adr_savivaldybes on lt.adr_savivaldybes (sav_kodas);
create index if not exists idx__adr_apskritys__adm_kodas on lt.adr_savivaldybes (adm_kodas);

alter table lt.adr_seniunijos alter column sen_kodas type bigint using sen_kodas::bigint;
alter table lt.adr_seniunijos alter column sav_kodas type bigint using sav_kodas::bigint;
create unique index if not exists uidx__adr_seniunijos on lt.adr_seniunijos (sen_kodas);
create index if not exists idx__adr_seniunijos__sav_kodas on lt.adr_seniunijos (sav_kodas);

alter table lt.adr_gyvenamosios_vietoves alter column gyv_kodas type bigint using gyv_kodas::bigint;
alter table lt.adr_gyvenamosios_vietoves alter column sen_kodas type bigint using nullif(sen_kodas, '')::bigint;
alter table lt.adr_gyvenamosios_vietoves alter column sav_kodas type bigint using sav_kodas::bigint;
create unique index if not exists uidx__adr_gyvenamosios_vietoves on lt.adr_gyvenamosios_vietoves (gyv_kodas);
create index if not exists idx__adr_gyvenamosios_vietoves__sav_kodas on lt.adr_gyvenamosios_vietoves (sav_kodas);
create index if not exists idx__adr_gyvenamosios_vietoves__sen_kodas on lt.adr_gyvenamosios_vietoves (sen_kodas);


alter table lt.adr_gatves alter column gat_kodas type bigint using gat_kodas::bigint;
alter table lt.adr_gatves alter column gyv_kodas type bigint using gyv_kodas::bigint;
create unique index if not exists uidx__adr_gatves on lt.adr_gatves (gat_kodas);
create index if not exists idx__adr_gatves__gyv_kodas on lt.adr_gatves (gyv_kodas);

alter table lt.adr_stat_lr alter column aob_kodas type bigint using aob_kodas::bigint;
alter table lt.adr_stat_lr alter column gyv_kodas type bigint using nullif(gyv_kodas, '')::bigint;
alter table lt.adr_stat_lr alter column gat_kodas type bigint using nullif(gat_kodas, '')::bigint;
alter table lt.adr_stat_lr alter column sav_kodas type bigint using nullif(sav_kodas, '')::bigint;
create unique index if not exists uidx__adr_stat_lr on lt.adr_stat_lr (aob_kodas);
create index if not exists idx__adr_stat_lr__sav_kodas on lt.adr_stat_lr (sav_kodas);
create index if not exists idx__adr_stat_lr__gyv_kodas on lt.adr_stat_lr (gyv_kodas);
create index if not exists idx__adr_stat_lr__gat_kodas on lt.adr_stat_lr (gat_kodas);

-- otherwise unique index creation fails.
-- these are duplicate rows here.
delete from lt.adr_pat_lr 
where oid in (
	select min(oid) as oid
	from lt.adr_pat_lr
	group by pat_kodas, sav_kodas, aob_kodas, patalpos_nr, nuo, pat_nuo
	having count(1)>1
);

alter table lt.adr_pat_lr alter column aob_kodas type bigint using aob_kodas::bigint;
alter table lt.adr_pat_lr alter column pat_kodas type bigint using pat_kodas::bigint;
alter table lt.adr_pat_lr alter column sav_kodas type bigint using nullif(sav_kodas, '')::bigint;
create unique index if not exists uidx__adr_pat_lr on lt.adr_pat_lr (pat_kodas);
create index if not exists idx__adr_pat_lr__sav_kodas on lt.adr_pat_lr (sav_kodas);
create index if not exists idx__adr_pat_lr__aob_kodas on lt.adr_pat_lr (aob_kodas);

/* .. and now for something completely different */



drop table if exists lt.lt_address;
create table lt.lt_address as
select 
    f.*, 
	case 
	    when f.l5_street is not null then
	        f.l5_street||' '||f.l7_housenumber
		else
		    f.l3_settlement||' '||f.l7_housenumber
	end as short_address, 
	array_to_string(
		case
		    when f.l5_street is not null then
        		array[f.l5_street||' '||f.l7_housenumber, f.l3_settlement, f.lx_ward, f.l2_municipality, f.l1_county]
		    else
        		array[f.l3_settlement||' '||f.l7_housenumber, f.lx_ward, f.l2_municipality, f.l1_county]
		end,
		', '
    ) as full_address
from (
	select
		ap.e_koord as longitude, ap.n_koord as latitude, nullif(trim(ap.pasto_koda), '') as postal_code,
	    7 as a_level, 
		aps.adm_kodas as l1_county_code, aps.vardas_k ||' '|| aps.tipo_santrumpa as l1_county,
		sav.sav_kodas as l2_municipality_code, sav.vardas_k ||' '|| sav.tipo_santrumpa as l2_municipality,
		gyv.gyv_kodas as l3_settlement_code, gyv.vardas ||' '|| gyv.tipo_santrumpa as l3_settlement,
		gat.gat_kodas as l5_street_code, gat.vardas_k ||' '|| gat.tipo_santrumpa as l5_street,
		s.aob_kodas as l7_housenumber_code, array_to_string(array[s.nr, 'K'||nullif(korpuso_nr, '')], ' ') as l7_housenumber,
		null::bigint as l8_apartmentnumber_code, null::text as l8_apartmentnumber,
		sen.sen_kodas as lx_ward_code, sen.vardas_k ||' '||sen.tipo_santrumpa as lx_ward
	from 
		lt.adr_stat_lr s 
			left join lt.adr_gra ap on ap.aob_kodas = s.aob_kodas
			left join lt.adr_gatves gat on gat.gat_kodas = s.gat_kodas
			left join lt.adr_gyvenamosios_vietoves gyv on gyv.gyv_kodas = s.gyv_kodas
			left join lt.adr_seniunijos sen on sen.sen_kodas = gyv.sen_kodas
			left join lt.adr_savivaldybes sav on sav.sav_kodas = s.sav_kodas
			left join lt.adr_apskritys aps on aps.adm_kodas = sav.adm_kodas
) f
;


insert into lt.lt_address (
    longitude, latitude, postal_code, a_level, l1_county_code, l1_county, l2_municipality_code, l2_municipality, 
	l3_settlement_code, l3_settlement, l5_street_code, l5_street, l7_housenumber_code, l7_housenumber, 
	l8_apartmentnumber_code, l8_apartmentnumber, lx_ward_code, lx_ward, short_address, full_address)
select 
    f.*, 
	case 
	    when f.l5_street is not null then
	        f.l5_street||' '||f.l7_housenumber||'-'||f.l8_apartmentnumber
		else
		    f.l3_settlement||' '||f.l7_housenumber||'-'||f.l8_apartmentnumber
	end as short_address, 
	array_to_string(
		case
		    when f.l5_street is not null then
        		array[f.l5_street||' '||f.l7_housenumber||'-'||f.l8_apartmentnumber, f.l3_settlement, f.lx_ward, f.l2_municipality, f.l1_county]
		    else
        		array[f.l3_settlement||' '||f.l7_housenumber||'-'||f.l8_apartmentnumber, f.lx_ward, f.l2_municipality, f.l1_county]
		end,
		', '
    ) as full_address
from (
	select
		ap.e_koord as longitude, ap.n_koord as latitude, nullif(trim(ap.pasto_koda), '') as postal_code, 
	    8 as a_level, 
		aps.adm_kodas as l1_county_code, aps.vardas_k ||' '|| aps.tipo_santrumpa as l1_county,
		sav.sav_kodas as l2_municipality_code, sav.vardas_k ||' '|| sav.tipo_santrumpa as l2_municipality,
		gyv.gyv_kodas as l3_settlement_code, gyv.vardas ||' '|| gyv.tipo_santrumpa as l3_settlement,
		gat.gat_kodas as l5_street_code, gat.vardas_k ||' '|| gat.tipo_santrumpa as l5_street,
		s.aob_kodas as l7_housenumber_code, array_to_string(array[s.nr, 'K'||nullif(korpuso_nr, '')], ' ') as l7_housenumber,
		p.pat_kodas as l8_apartmentnumber_code, p.patalpos_nr as l8_apartmentnumber,
		sen.sen_kodas as lx_ward_code, sen.vardas_k ||' '||sen.tipo_santrumpa as lx_ward
	from
   		lt.adr_pat_lr p,
	    lt.adr_stat_lr s
    		left join lt.adr_gra ap on ap.aob_kodas = s.aob_kodas
			left join lt.adr_gatves gat on gat.gat_kodas = s.gat_kodas
			left join lt.adr_gyvenamosios_vietoves gyv on gyv.gyv_kodas = s.gyv_kodas
			left join lt.adr_seniunijos sen on sen.sen_kodas = gyv.sen_kodas
			left join lt.adr_savivaldybes sav on sav.sav_kodas = s.sav_kodas
			left join lt.adr_apskritys aps on aps.adm_kodas = sav.adm_kodas
    where s.aob_kodas = p.aob_kodas
) f
;


insert into lt.lt_address (
    longitude, latitude, a_level, l1_county_code, l1_county, l2_municipality_code, l2_municipality, 
	l3_settlement_code, l3_settlement, l5_street_code, l5_street, lx_ward_code, lx_ward, 
	short_address, full_address)
select 
    f.*, 
	f.l5_street as short_address, 
	array_to_string(
		array[f.l5_street, f.l3_settlement, f.lx_ward, f.l2_municipality, f.l1_county], 
		', '
    ) as full_address
from (
	select 
	    st_x(geom) as longitude, st_y(geom) as latitude, a_level,
	    l1_county_code, l1_county, l2_municipality_code, l2_municipality, 
	    l3_settlement_code, l3_settlement, l5_street_code, l5_street, 
	    lx_ward_code, lx_ward
    from (	
		select
			/*ap.e_koord as longitude, ap.n_koord as latitude, */ 
			st_transform(
				case 
				    when geometrytype(g.geometry) = 'MULTILINESTRING' then st_centroid(g.geometry) 
				    else st_lineinterpolatepoint(g.geometry, 0.5) 
			    end,
				4326
			) as geom , 5 as a_level, 
			aps.adm_kodas as l1_county_code, aps.vardas_k ||' '|| aps.tipo_santrumpa as l1_county,
			sav.sav_kodas as l2_municipality_code, sav.vardas_k ||' '|| sav.tipo_santrumpa as l2_municipality,
			gyv.gyv_kodas as l3_settlement_code, gyv.vardas ||' '|| gyv.tipo_santrumpa as l3_settlement,
			gat.gat_kodas as l5_street_code, gat.vardas_k ||' '|| gat.tipo_santrumpa as l5_street,
			sen.sen_kodas as lx_ward_code, sen.vardas_k ||' '||sen.tipo_santrumpa as lx_ward
		from
			lt.adr_gra_gatves g,
			lt.adr_gatves gat
				left join lt.adr_gyvenamosios_vietoves gyv on gyv.gyv_kodas = gat.gyv_kodas
				left join lt.adr_seniunijos sen on sen.sen_kodas = gyv.sen_kodas
				left join lt.adr_savivaldybes sav on sav.sav_kodas = gyv.sav_kodas
				left join lt.adr_apskritys aps on aps.adm_kodas = sav.adm_kodas
		where g.gat_kodas = gat.gat_kodas
	) z
) f
;

insert into lt.lt_address (
    longitude, latitude, a_level, l1_county_code, l1_county, l2_municipality_code, l2_municipality, 
	l3_settlement_code, l3_settlement, lx_ward_code, lx_ward, 
	full_address)
select 
    f.*, 
	array_to_string(
		array[f.l3_settlement, f.lx_ward, f.l2_municipality, f.l1_county], 
		', '
    ) as full_address
from (
	select 
	    st_x(geom) as longitude, st_y(geom) as latitude, a_level,
	    l1_county_code, l1_county, l2_municipality_code, l2_municipality, 
	    l3_settlement_code, l3_settlement,
	    lx_ward_code, lx_ward
    from (	
		select
			/*ap.e_koord as longitude, ap.n_koord as latitude, */ 
			st_transform(st_centroid(g.geometry), 4326) as geom , 3 as a_level, 
			aps.adm_kodas as l1_county_code, aps.vardas_k ||' '|| aps.tipo_santrumpa as l1_county,
			sav.sav_kodas as l2_municipality_code, sav.vardas_k ||' '|| sav.tipo_santrumpa as l2_municipality,
			gyv.gyv_kodas as l3_settlement_code, gyv.vardas ||' '|| gyv.tipo_santrumpa as l3_settlement,
			sen.sen_kodas as lx_ward_code, sen.vardas_k ||' '||sen.tipo_santrumpa as lx_ward
		from
			lt.adr_gra_gyvenamosios_vietoves g,
		    lt.adr_gyvenamosios_vietoves gyv
				left join lt.adr_seniunijos sen on sen.sen_kodas = gyv.sen_kodas
				left join lt.adr_savivaldybes sav on sav.sav_kodas = gyv.sav_kodas
				left join lt.adr_apskritys aps on aps.adm_kodas = sav.adm_kodas
		where g.gyv_kodas = gyv.gyv_kodas
	) z
) f
;


insert into lt.lt_address (
    longitude, latitude, a_level, l1_county_code, l1_county, l2_municipality_code, l2_municipality, 
	full_address)
select 
    f.*, 
	array_to_string(
		array[f.l2_municipality, f.l1_county], 
		', '
    ) as full_address
from (
	select 
	    st_x(geom) as longitude, st_y(geom) as latitude, a_level,
	    l1_county_code, l1_county, l2_municipality_code, l2_municipality
    from (	
		select
			/*ap.e_koord as longitude, ap.n_koord as latitude, */ 
			st_transform(st_centroid(g.geometry), 4326) as geom , 2 as a_level, 
			aps.adm_kodas as l1_county_code, aps.vardas_k ||' '|| aps.tipo_santrumpa as l1_county,
			sav.sav_kodas as l2_municipality_code, sav.vardas_k ||' '|| sav.tipo_santrumpa as l2_municipality
		from
			lt.adr_gra_savivaldybes g,
			lt.adr_savivaldybes sav 
				left join lt.adr_apskritys aps on aps.adm_kodas = sav.adm_kodas
		where g.sav_kodas::bigint = sav.sav_kodas
	) z
) f
;


insert into lt.lt_address (
    longitude, latitude, a_level, l1_county_code, l1_county,
	full_address)
select 
    f.*, 
	array_to_string(
		array[f.l1_county], 
		', '
    ) as full_address
from (
	select 
	    st_x(geom) as longitude, st_y(geom) as latitude, a_level,
	    l1_county_code, l1_county
    from (	
		select
			/*ap.e_koord as longitude, ap.n_koord as latitude, */ 
			st_transform(st_centroid(g.geometry), 4326) as geom , 1 as a_level, 
			aps.adm_kodas as l1_county_code, aps.vardas_k ||' '|| aps.tipo_santrumpa as l1_county
		from
			lt.adr_gra_apskritys g,
			lt.adr_apskritys aps 
		where g.aps_kodas::bigint = aps.adm_kodas
	) z
) f
;

alter table lt.lt_address add column geometry geometry(point, 3346);
update lt.lt_address set geometry = st_transform(st_setsrid(st_point(longitude, latitude), 4326), 3346);

create index idx__lt_address__l8_apartmentnumber_code on lt.lt_address (l8_apartmentnumber_code);
create index idx__lt_address__l7_housenumber_code on lt.lt_address (l7_housenumber_code);
create index idx__lt_address__l5_street_code on lt.lt_address (l5_street_code);
create index idx__lt_address__l3_settlement_code on lt.lt_address (l3_settlement_code);
create index idx__lt_address__l2_municipality_code on lt.lt_address (l2_municipality_code);
create index idx__lt_address__l1_county_code on lt.lt_address (l1_county_code);
create index sidx__lt_address on lt.lt_address using gist(geometry);

select count(1) from lt.lt_address a
where a_level = 8 and not exists ( 
select 1 from lt.lt_address f
where f.a_level = 7 and f.l7_housenumber_code = a.l7_housenumber_code)
;
-- 0 means all L8 objects have parent at L7. THIS is a must-be!

select count(1) from lt.lt_address a
where 
    a_level = 7 and 
	l5_street is not null and 
	not exists ( 
        select 1 from lt.lt_address f
        where 
            f.a_level = 5 and f.l5_street_code = a.l5_street_code
);
-- 0 means alla referenced L5 streets are there

insert into lt.lt_address(
    longitude, latitude, a_level,
	l1_county_code, l1_county, l2_municipality_code, l2_municipality,
	l3_settlement_code, l3_settlement,
	lx_ward_code, lx_ward,
	full_address
)
select 
    distinct on (l1_county_code, l2_municipality_code, l3_settlement_code)
    longitude, latitude, 3 as a_level,
	l1_county_code, l1_county, l2_municipality_code, l2_municipality,
	l3_settlement_code, l3_settlement,
	lx_ward_code, lx_ward,
	array_to_string(array[l3_settlement, lx_ward, l2_municipality, l1_county], ', ') as full_address
from lt.lt_address a
where 
    a_level = 7 and 
	l5_street is null and 
	not exists ( 
        select 1 from lt.lt_address f
        where 
            f.a_level = 3 and f.l3_settlement_code = a.l3_settlement_code
);

select count(1)
from lt.lt_address a
where 
    a_level = 7 and 
	l5_street is null and 
	not exists ( 
        select 1 from lt.lt_address f
        where 
            f.a_level = 3 and f.l3_settlement_code = a.l3_settlement_code
);
-- 0 means all referenced L3 settlements are there if AP doesn't have a street ref

select * from lt.adr_gatves v left join lt.adr_gra ap on ap.gat_kodas = v.gat_kodas
where not exists (select 1 from lt.adr_gra_gatves g 
where g.gat_kodas = v.gat_kodas)
;
-- 8 streets without geometries and none of these have any address points associated :(

select count(1) from lt.adr_gatves v 
where not exists (select 1 from lt.lt_address g 
where g.a_level = 5 and g.l5_street_code = v.gat_kodas)
;
-- the same result here: 8 missing. Since we have no place to pull coordinates from
-- (no geometries nor addresspoints for these streets)
-- we'll need to skip these. Could use residential area coords but these would stack them up 
-- in the same location. 

select count(1) from lt.adr_gra_gyvenamosios_vietoves v
where not exists (select 1 from lt.lt_address a where a.a_level = 3 and a.l3_settlement_code = v.gyv_kodas)
;
-- 0 means we have all resid. areas with geometries

select count(1) from lt.adr_gyvenamosios_vietoves v
where not exists (select 1 from lt.lt_address a where a.a_level = 3 and a.l3_settlement_code = v.gyv_kodas)
;
-- 42 resid areas missing. BUT since none of these have any address points nor do they have a geometry
-- described in lt.adr_gra_gyvenamosios_vietoves we'll skip these because we need coordinates for geocoding.

select * from lt.adr_gyvenamosios_vietoves v 
where not exists (select 1 from lt.adr_gra_gyvenamosios_vietoves g 
where g.gyv_kodas = v.gyv_kodas)
;
-- 46 resid areas. 4 of these got inserted before using address point locations

select count(1) from lt.adr_savivaldybes v
where not exists (select 1 from lt.lt_address ap where ap.a_level = 2 and ap.l2_municipality_code = v.sav_kodas)
;
-- 0 means all savivaldybes present

select count(1) from lt.adr_apskritys v
where not exists (select 1 from lt.lt_address ap where ap.a_level = 1 and ap.l1_county_code = v.adm_kodas)
;
-- 0 means all apskritys present

-- fixing missing postcodes as
-- a) if a single postcode is available for addresspoints in this l3_settlement, 
-- then most probs the missing one will be the same

with f as (
    select l3_settlement_code, (array_agg(distinct postal_code ))[1] postal_code 
    from lt.lt_address
    where a_level = 7 and postal_code is not null
    group by l3_settlement_code
    having count(distinct postal_code) = 1
)
update lt.lt_address set
    postal_code = f.postal_code
from f
where 
    lt_address.a_level in (7, 8) and 
    lt_address.postal_code is null and 
	lt_address.l3_settlement_code = f.l3_settlement_code
;
-- 5712

-- a) if a single postcode is available for addresspoints in this l3_settlement, 
-- then most probs the missing one will be the same

with f as (
    select 
	    l3_settlement_code, coalesce(l5_street_code, -1) as l5_street_code, 
	    (array_agg(distinct postal_code ))[1] postal_code 
    from lt.lt_address
    where a_level = 7 and postal_code is not null
    group by l3_settlement_code, l5_street_code
    having count(distinct postal_code) = 1
)
update lt.lt_address set
    postal_code = f.postal_code
from f
where 
    lt_address.a_level in (7, 8) and 
    lt_address.postal_code is null and 
	lt_address.l3_settlement_code = f.l3_settlement_code and
	coalesce(lt_address.l5_street_code, 1) = f.l5_street_code
;

select count(1) from lt.lt_address where a_level = 7 and postal_code is null;
select count(1) from lt.lt_address where a_level = 8 and postal_code is null;

drop table if exists lt.zip_by_nn;
create table lt.zip_by_nn as
select ap.l7_housenumber_code, ap.l3_settlement, ap.l5_street, ap.l7_housenumber, f.postal_code
from lt.lt_address ap
join lateral (
	select postal_code 
	from lt.lt_address r 
	where 
	    r.postal_code is not null and 
	    ap.l3_settlement_code = r.l3_settlement_code and 
	    coalesce(ap.l5_street_code, -1) = coalesce(r.l5_street_code, '-1')
	order by ap.geometry <-> r.geometry
	limit 1
) f on true
where ap.a_level = 7 and ap.postal_code is null;

create unique index uidx__zip_by_nn__l7_housenumber_code on lt.zip_by_nn (l7_housenumber_code);

update lt.lt_address set 
    postal_code = f.postal_code
from lt.zip_by_nn f
where 
    f.l7_housenumber_code = lt_address.l7_housenumber_code and 
    lt_address.postal_code is null
;


drop table if exists lt.zip_by_nn_w;
create table lt.zip_by_nn_w as
select ap.l7_housenumber_code, ap.l3_settlement, ap.l7_housenumber, f.postal_code
from lt.lt_address ap
join lateral (
	select postal_code 
	from lt.lt_address r 
	where 
	    r.postal_code is not null and 
	    r.lx_ward_code = ap.lx_ward_code
	order by ap.geometry <-> r.geometry
	limit 1
) f on true
where ap.l5_street is null and ap.a_level = 7 and ap.postal_code is null;

create unique index uidx__zip_by_nn_w__l7_housenumber_code on lt.zip_by_nn_w (l7_housenumber_code);

update lt.lt_address set 
    postal_code = f.postal_code
from lt.zip_by_nn_w f
where 
    f.l7_housenumber_code = lt_address.l7_housenumber_code and 
    lt_address.postal_code is null
;


drop table if exists lt.zip_by_nn_street;
create table lt.zip_by_nn_street as
select ap.l7_housenumber_code, ap.l3_settlement, ap.l5_street, ap.l7_housenumber, f.postal_code,
st_distance(ap.geometry, f.geometry) as distance
from lt.lt_address ap
join lateral (
	select postal_code, geometry
	from lt.lt_address r 
	where 
	    r.postal_code is not null and 
	    ap.l3_settlement_code = r.l3_settlement_code
	order by ap.geometry <-> r.geometry
	limit 1
) f on true
where ap.a_level = 7 and ap.postal_code is null;

create unique index uidx__zip_by_nn_street__l7_housenumber_code on lt.zip_by_nn_street (l7_housenumber_code);

update lt.lt_address set
    postal_code = f.postal_code
from (
	select l3_settlement, l5_street, (array_agg(postal_code order by d))[1] as postal_code
	from (
	select l3_settlement, l5_street, postal_code, min(distance) as d
	from lt.zip_by_nn_street
	group by l3_settlement, l5_street, postal_code
	) f
	group by l3_settlement, l5_street
	having count(1)=1
) f
where 
    lt_address.postal_code is null and 
    lt_address.a_level in (7,8) and 
	lt_address.l3_settlement = f.l3_settlement and
	lt_address.l5_street = f.l5_street
;

update lt.lt_address set
    postal_code = f.postal_code
from (
	select l3_settlement, l5_street, (array_agg(postal_code order by d))[1] as postal_code,
	array_agg(d order by d) as distances
	from (
	select l3_settlement, l5_street, postal_code, min(distance) as d
	from lt.zip_by_nn_street
	group by l3_settlement, l5_street, postal_code
	) f
	group by l3_settlement, l5_street
	having count(1)>1
) f
where 
    lt_address.postal_code is null and 
    lt_address.a_level in (7,8) and 
	lt_address.l3_settlement = f.l3_settlement and
	lt_address.l5_street = f.l5_street
;


drop table if exists lt.zip_by_nn_street_ward;
create table lt.zip_by_nn_street_ward as
select ap.l7_housenumber_code, ap.l3_settlement, ap.lx_ward, ap.l5_street, ap.l7_housenumber, f.postal_code,
st_distance(ap.geometry, f.geometry) as distance
from lt.lt_address ap
join lateral (
	select postal_code, geometry
	from lt.lt_address r 
	where 
	    r.postal_code is not null and 
	    ap.lx_ward_code = r.lx_ward_code
	order by ap.geometry <-> r.geometry
	limit 1
) f on true
where ap.a_level = 7 and ap.postal_code is null;


update lt.lt_address set
    postal_code = f.postal_code
from (
	select lx_ward, l5_street, (array_agg(postal_code order by d))[1] as postal_code,
	array_agg(d order by d) as distances
	from (
	select lx_ward, l5_street, postal_code, min(distance) as d
	from lt.zip_by_nn_street_ward
	group by lx_ward, l5_street, postal_code
	) f
	group by lx_ward, l5_street
	having count(1)= 1
) f
where 
    lt_address.postal_code is null and 
    lt_address.a_level in (7,8) and 
	lt_address.lx_ward = f.lx_ward and
	lt_address.l5_street = f.l5_street
;

update lt.lt_address set
    postal_code = f.postal_code
from (
	select lx_ward, l5_street, (array_agg(postal_code order by d))[1] as postal_code,
	array_agg(d order by d) as distances
	from (
	select lx_ward, l5_street, postal_code, min(distance) as d
	from lt.zip_by_nn_street_ward
	group by lx_ward, l5_street, postal_code
	) f
	group by lx_ward, l5_street
	having count(1)> 1
) f
where 
    lt_address.postal_code is null and 
    lt_address.a_level in (7,8) and 
	lt_address.lx_ward = f.lx_ward and
	lt_address.l5_street = f.l5_street
;


-- just simply the closest 

update lt.lt_address set
    postal_code = f.postal_code 
from (
	select ap.l7_housenumber_code, ap.l3_settlement, ap.l5_street, ap.l7_housenumber, f.postal_code,
	st_distance(ap.geometry, f.geometry) as distance
	from lt.lt_address ap
	join lateral (
		select postal_code, geometry
		from lt.lt_address r 
		where 
			r.postal_code is not null and 
			ap.l2_municipality_code = r.l2_municipality_code
		order by ap.geometry <-> r.geometry
		limit 1
	) f on true
	where ap.a_level = 7 and ap.postal_code is null
) f
where lt_address.postal_code is null and lt_address.l7_housenumber_code = f.l7_housenumber_code
;
--updates 1 last row in the neighbourhood of the powerplant.

select * from lt.lt_address where postal_code is null and a_level in( 7,8);
-- returns now 0 rows, weeee!

-- removing duplicates. should have looked at this already in the veeeery beginning.
update lt.lt_address set 
   l3_settlement_code = d.the_code
from (
	select 
		min(l3_settlement_code) as the_code, 
		array_agg(l3_settlement_code order by l3_settlement_code) as codes, 
		full_address, postal_code, a_level, count(1)
	from lt.lt_address
	group by full_address, postal_code, a_level having count(1)>1
) d
where 
    lt_address.a_level > 3 and 
	lt_address.l3_settlement_code = any(d.codes) and lt_address.l3_settlement_code != d.the_code
;


delete from lt.lt_address
where 
    a_level = 3 and
	exists (
		select 1 from (
			select 
				min(l3_settlement_code) as the_code, 
				array_agg(l3_settlement_code order by l3_settlement_code) as codes, 
				full_address, postal_code, a_level, count(1)
			from lt.lt_address
			group by full_address, postal_code, a_level having count(1)>1	
		) d 
		where 
		    lt_address.l3_settlement_code = any(d.codes) and
		    lt_address.l3_settlement_code != d.the_code
	)
;

-- full_address, postal_code combinations should be unique!
select full_address, postal_code, count(1)
from lt.lt_address 
group by full_address, postal_code
having count(1)>1
;