-- vytvoření prostorového rozšíření POSTGIS na dané DB
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;

-- přidání záznamu do spatial_ref_sys o S-TSK (EPSG 5514) dle http://freegis.fsv.cvut.cz/gwiki/S-JTSK
INSERT INTO "spatial_ref_sys" ("srid","auth_name","auth_srid","srtext","proj4text") VALUES(5514,'EPSG',5514,'PROJCS["S-JTSK_Krovak_East_North",GEOGCS["GCS_S_JTSK",DATUM["D_S_JTSK",SPHEROID["Bessel_1841",6377397.155,299.1528128]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]],PROJECTION["Krovak"],PARAMETER["False_Easting",0],PARAMETER["False_Northing",0],PARAMETER["Pseudo_Standard_Parallel_1",78.5],PARAMETER["Scale_Factor",0.9999],PARAMETER["Azimuth",30.28813975277778],PARAMETER["Longitude_Of_Center",24.83333333333333],PARAMETER["Latitude_Of_Center",49.5],PARAMETER["X_Scale",-1],PARAMETER["Y_Scale",1],PARAMETER["XY_Plane_Rotation",90],UNIT["Meter",1]]','+proj=krovak +lat_0=49.5 +lon_0=24.83333333333333 +alpha=30.28813972222222 +k=0.9999 +x_0=0 +y_0=0 +ellps=bessel +pm=greenwich +units=m +no_defs +towgs84=570.8,85.7,462.8,4.998,1.587,5.261,3.56');

-- nahrání RES
-- vytvoření áčkové tabulky res
CREATE TABLE public.res_a
(
  icof bigint,
  ddatvzn character varying(20),
  ddatzan character varying(20),
  zpzanf integer,
  ddatpakt character varying(20),
  formaf integer,
  nacef character varying(6),
  katpof integer,
  iczujf integer,
  okreslauf character varying(7),
  firma character varying(254),
  rosformaf integer,
  isektorf integer,
  ciss2010f integer,
  kodadm character varying(10)
);
COMMENT ON TABLE public.res_a
  IS 'Kompletní áčková tabulka RES v její původní podobě, živé + mrtvé ič';
  ALTER TABLE res_a ADD PRIMARY KEY (icof);
-- tabulka vytvořená, včetně primárního klíče, připravená pro import dat (například přes gui pgAdmina)

-- vytvoření céčkové tabulky RES (s adresami) - kvůli geokódování
CREATE TABLE public.res_c 
(
  icof bigint, 
  textadr character varying(256),
  psc character varying(6),
  obec character varying(60),
  cobce character varying(50), 
  ulice character varying(50), 
  typcdom character varying(1), 
  cdom character varying(6), 
  cor character varying(4) 
  );
COMMENT ON TABLE public.res_c
  IS 'céčková adresní tabulka, původní podoba';
ALTER TABLE res_c ADD PRIMARY KEY (icof);
-- a je připravená na import

--nahrání RUIAN - popsáno v textu, pomocí konzolového ruian2pgsql
--příkaz pro příkazový řádek:
java -cp ruian.jar:postgresql-9.3-1102.jdbc4.jar com.fordfrog.ruian2pgsql.App --convert-to-ewkt --linearize e-wkt  --create-tables --db-connection-url "jdbc:postgresql://localhost/gis?user=uzivatel&password=heslo" --input-dir /korenova/cesta/ke/slozce
--konec příkazu
-- nápověda viz https://github.com/fordfrog/ruian2pgsql/blob/master/README.cs.md


--geokódování
--první fáze - příprava dat, extrakce adresních informací do odpovídajících atributů

--vymazání řetězce ', CZ'
update res_c SET textadr=replace(textadr, ', CZ', '');
--uložení PSČ (různé varianty z textu)
update res_c SET psc=(regexp_matches(textadr, '\d{5}'))[1] where textadr!='';
update res_c SET psc=replace((regexp_matches(textadr, '\d{3} \d{2}'))[1], ' ', '') where textadr!='';
update res_c SET psc=replace((regexp_matches(textadr, '\d{3} \d{2}'))[1], ' ', '') where textadr!='';
--vymazání PSČ z celého řetězce
UPDATE res_c SET textadr =regexp_replace(textadr, '\d{5}', '') where textadr!='';
UPDATE res_c SET textadr =regexp_replace(textadr, '\d{3} \d{2}', '') where textadr!='';
UPDATE res_c SET textadr =regexp_replace(textadr, '\d{3} \d{2}', '') where textadr!='';
--uložení názvu obcí, pokud je to to jediné, co je v textadr uloženo (=přímá rovnost atributu textadr s názvy obcí v rúian)
update res_c set obec = textadr where textadr in (select nazev from rn_obec);
--a vymazání celého řetězce ať už to nemate
update res_c set textadr='' where textadr in (select nazev from rn_obec);
--vymazání řetězců okres nazevokresu - ve všech různých možných variantách
update res_c SET textadr=regexp_replace(textadr, 'okres [a-zA-Z\-ňěšďčřžýáíéúůĎŇÚŮĚŠČŘŽÝÁÍÉťŤ ]{1,}', '') where textadr!='' and textadr not ilike 'nedefi%' and textadr ilike '%okres%';
update res_c SET textadr=regexp_replace(textadr, 'okr. [a-zA-Z\-ňěšďčřžýáíéúůĎŇÚŮĚŠČŘŽÝÁÍÉťŤ ]{1,}', '') where textadr!='' and textadr not ilike 'nedefi%' and textadr ilike '%okr. %';
update res_c SET textadr=regexp_replace(textadr, 'okr.[a-zA-Z\-ňěšďčřžýáíéúůĎŇÚŮĚŠČŘŽÝÁÍÉťŤ ]{1,}', '') where textadr!='' and textadr not ilike 'nedefi%' and textadr ilike '%okr.%';
update res_c SET textadr=regexp_replace(textadr, 'okres [a-zA-Z\-ňěšďčřžýáíéúůĎŇÚŮĚŠČŘŽÝÁÍÉťŤ ]{$}', '') where textadr!='' and textadr not ilike 'nedefi%' and textadr ilike '%okres%';
update res_c SET textadr=regexp_replace(textadr, 'okr. [a-zA-Z\-ňěšďčřžýáíéúůĎŇÚŮĚŠČŘŽÝÁÍÉťŤ ]{$}', '') where textadr!='' and textadr not ilike 'nedefi%' and textadr ilike '%okr. %';
update res_c SET textadr=regexp_replace(textadr, 'okr.[a-zA-Z\-ňěšďčřžýáíéúůĎŇÚŮĚŠČŘŽÝÁÍÉťŤ ]{$}', '') where textadr!='' and textadr not ilike 'nedefi%' and textadr ilike '%okr.%';
update res_c SET textadr=regexp_replace(textadr, 'OKRES [a-zA-Z\-ňěšďčřžýáíéúůĎŇÚŮĚŠČŘŽÝÁÍÉťŤ ]{1,}', '') where textadr!='' and textadr not ilike 'nedefi%' and textadr ilike '%okres%';
update res_c SET textadr=regexp_replace(textadr, 'OKR. [a-zA-Z\-ňěšďčřžýáíéúůĎŇÚŮĚŠČŘŽÝÁÍÉťŤ ]{1,}', '') where textadr!='' and textadr not ilike 'nedefi%' and textadr ilike '%okr. %';
update res_c SET textadr=regexp_replace(textadr, 'OKR.[a-zA-Z\-ňěšďčřžýáíéúůĎŇÚŮĚŠČŘŽÝÁÍÉťŤ ]{1,}', '') where textadr!='' and textadr not ilike 'nedefi%' and textadr ilike '%okr.%';
update res_c SET textadr=regexp_replace(textadr, 'OKRES [a-zA-Z\-ňěšďčřžýáíéúůĎŇÚŮĚŠČŘŽÝÁÍÉťŤ ]{$}', '') where textadr!='' and textadr not ilike 'nedefi%' and textadr ilike '%okres%';
update res_c SET textadr=regexp_replace(textadr, 'OKR. [a-zA-Z\-ňěšďčřžýáíéúůĎŇÚŮĚŠČŘŽÝÁÍÉťŤ ]{$}', '') where textadr!='' and textadr not ilike 'nedefi%' and textadr ilike '%okr. %';
update res_c SET textadr=regexp_replace(textadr, 'OKR.[a-zA-Z\-ňěšďčřžýáíéúůĎŇÚŮĚŠČŘŽÝÁÍÉťŤ ]{$}', '') where textadr!='' and textadr not ilike 'nedefi%' and textadr ilike '%okr.%';
--vymazání řetězce 'PSČ'
update res_c SET textadr=replace(textadr, 'PSČ', '') where textadr!='';
--převod textadr na malá písmena
update res_c SET textadr = lower(textadr) where textadr != '';
--úpravy známých pletoucích se tvarů v názvu ulic na jednotný styl (v atributu textadr)
update res_c SET textadr=replace(textadr, 'nám. ', 'náměstí ') where textadr!='';
update res_c SET textadr=replace(textadr, 'nám.', 'náměstí ') where textadr!='';
--ty samé úpravy budou nutné potom provést i v rúian, POZOR, předtím tuto tabulku zazálohovat
update rn_ulice SET textadr=replace(nazev, 'nám. ', 'náměstí ');
update rn_ulice SET textadr=replace(nazev, 'nám.', 'náměstí ');
--velký výběr z textadr, vyhledávání možných ulic i obcí (tam kde mohou být podle kódu obce v tabulce ulic)
update res_c SET obec=tabulka.obec, ulice=tabulka.ulice FROM (SELECT rn_ulice.nazev as ulice, rn_obec.nazev as obec FROM rn_ulice JOIN rn_obec ON rn_ulice.obec_kod=rn_obec.kod) tabulka WHERE textadr ilike '%' || tabulka.obec || '%' AND textadr ilike '%' || tabulka.ulice || '%' AND textadr != 'Nedefinováno';
--ať jsou ve stejném tvaru i ty názvy ulic, které nebyly podrobeny hledání (záznamy RES s kompletní adresou rozepsanou do atributů) - do nového atributu
ALTER TABLE res_c ADD column ulice_male character varying(60);
UPDATE res_c set ulice_male = lower(ulice) where textadr = '';
--a i pro tyto ulice je třeba provést dané sjednocující úpravy
update res_c SET ulice_male=replace(ulice_male, 'nám. ', 'náměstí ') where ulice!='';
update res_c SET ulice_male=replace(ulice_male, 'nám.', 'náměstí ') where ulice!='';
--vyprázdnění textadr tam, kde už jsou vyextrahované informace
UPDATE res_c set textadr = '' where obec !='' AND ulice !='' and textadr !='';
--na závěr ještě vyhledání pouze názvů obcí (seřazeno podle délky názvu od nejdelšího)
update res_c SET obec=tabulka.nazev FROM (SELECT nazev from rn_obec order by length(nazev) DESC) tabulka WHERE textadr ilike '%' || tabulka.nazev || '%' AND obec='';

--příprava atributů pro geokódování
ALTER TABLE res_c ADD column loc geometry;
ALTER TABLE res_c ADD column pozn character varying(254);
--nyní přizpůsobit a poté spustit skript geocoding.php
--výsledkem je naplněná tabulka res_c se souřadnicemi centroidu ulice kde to jde, centroidu obce u zbytku.
--přidáme atribut kvality geokódování gcqual - integer, hodnoty 1 (jednoznačná přesnost na obec + ulici nebo obec bez ulic), 2 (jednoznačně identifikovaná obec), 3 (nejednoznačnost) a 4 (neprovedeno)
--provedeno běžným update na základě poznámky vzniklé při geokódování a dotazem do RÚIAN která obec má/nemá ulice
--další činnosti v první případové studii