Baza teryt – system TERC (część 1.)

 

(kliknij w obrazek, żeby zobaczyć pliki źródłowe)

Czym jest TERYT ?

TERYT – Krajowy Rejestr Urzędowy Podziału Terytorialnego Kraju – prowadzony przez Główny Urząd Statystyczny, rejestr urzędowy podziału terytorialnego Polski. Prościej – w Polsce istnieje instytucja, która prowadzi spis jednostek terytorialnych i za darmo udostępnia to w Internecie. Zbiory rejestru teryt udostępniane są w formacie pliku XML lub CSV oraz za pomocą usługi sieciowej wystawiającej swoje API.

Rejestr TERYT dzieli się na bazy składowych systemów:

  • TERC – identyfikatorów i nazw jednostek podziału terytorialnego,
  • SIMC – identyfikatorów i nazw miejscowości,
  • BREC – rejonów statystycznych i obwodów spisowych,
  • NOBC – identyfikacji adresowej ulic, nieruchomości, budynków i mieszkań (w ramach systemu prowadzony jest Centralny Katalog Ulic – ULIC).

Po co mi te dane?

To bardzo dobre pytanie. W wielu systemach komercyjnych, gdzie gromadzone są dane klientów, użytkowników, pracowników itd. – zachodzi potrzeba walidacji danych. W dużym uproszczeniu walidacja to proces mający na celu sprawdzenie, czy dane, które znajdują się w systemie są poprawne. Do określenia prawności potrzebujemy wzorca, danych, do których będziemy mogli je odnieść.

Dodatkowo, dzięki takiemu rejestrowi możemy utworzyć własny zbiór danych, swoisty słownik, z którego będziemy mogli korzystać prezentując wyniki naszych badań. W związku z tym, że dane udostępniane są przez instytucję państwową istnieje bardzo duże prawdopodobieństwo, iż zbiór jest kompletny, a dane poprawne.

Profity z posiadania danych rejestru TERYT

Z pewnością niejeden czytelnik zadał sobie pytanie: “Co ja będę miał z tego, że posiadam taki zbiór?”. Poza zaletami podanymi powyżej, które możemy zaliczyć raczej do korzyści intelektualnych, istnieją także korzyści biznesowe, a co za tym idzie finansowe. O ile sam zbiór udostępniany jest całkowicie za darmo, o tyle żadna instytucja państwowa nie zadba o aktualizację danych w naszych aplikacjach i systemach. Dodatkowo, dane udostępniane są w konkretnych formatach o ściśle określonej strukturze. W rzeczywistości, w większości przypadków, dane z rejestru TERYT przechowuje się w systemach bazodanowych traktując je jako słowniki.

Podczas serii artykułów poświęconych rejestrowi TERYT pokażę jak parsować udostępniane dane oraz jak tworzyć przyjazne struktury danych, które mogą być wykorzystane w dowolnym projekcie. Zdobytą wiedzę możesz wykorzystać w swoich aplikacjach lub w swojej pracy. Możesz pokazać swojemu pracodawcy, że jesteś w stanie zaoszczędzić kilka tysięcy złotych, samodzielnie implementując rozwiązanie pomijając zewnętrznych dostawców. OSZCZĘDNOŚĆ – to słowo działa na przełożonych jak feromony 🙂

Implementacja parsowania plików

Mechanizm parsowania oryginalnych danych GUS można napisać za pomocą różnych języków programowania. Jednak w związku z tym, że chciałbym pokazać różne zastosowania języka R, oraz przy okazji dając możliwość lepszego poznania go – ostatecznie skrypt został przygotowany w tym języku. Do dzieła!

Zaczynamy od załadowania niezbędnych bibliotek:

#load required libraries
library(XML)
library(methods)
library(RSQLite)

Następnie tworzymy niezbędne zmienne. Pierwsza z nich (xmlFilePath) określa ścieżkę do pliku XML pobranego ze strony GUS dostępnego tutaj:
Pobierz pliki TERYT


xmlFilePath <- "input_files/TERC_Urzedowy_2018-04-08.XML"
xmlDoc <- xmlParse(xmlFilePath) #xml R object
allNodes <- xmlRoot(xmlDoc)[["catalog"]]#set root tag in XML file
dataset <- xmlToDataFrame(nodes = xmlChildren(allNodes)) #parse and load XML to data.frame object

Kolejnym krokiem jest zadbanie o poprawną obsługę wartości nieznanych. Plik XML jest tak skonstruowany, że w komórkach, w których nie ma żadnej wartości nie widoczne są jakby zawierały pusty ciąg znaków. Jest to o tyle niewygodna sytuacja, że środowisko programistyczne nie interpretuje tych komórek jako posiadające wartość nieznaną “NA”.
Przygladając się naszemu zbiorowi danych jesteśmy w stanie ustalić, że dla poszczególnych typów jednostek terytorialnych taka sytuacja zachodzi dla kolumn:

  • typ “województwo” – kolumny od 2 do 4;
  • typ “powiat” – kolumny od 3 do 4;
  • typ “miasto stołeczne, na prawach powiatu” – kolumny od 3 do 4;
  • typ “miasto na prawach powiatu” – kolumny od 3 do 4.

W związku z tym w wyznaczonych kolumnach ustawiamy wartość “NA”:

dataset[which(dataset$NAZWA_DOD=="województwo"),2:4]<- NA 
dataset[which(dataset$NAZWA_DOD=="powiat"),3:4]<- NA
dataset[which(dataset$NAZWA_DOD=="miasto stołeczne, na prawach powiatu"),3:4]<- NA
dataset[which(dataset$NAZWA_DOD=="miasto na prawach powiatu"),3:4]<- NA

Następnie należy zadbać o to, by każdej kolumnie nadać odpowiedni typ danych. W naszym przypadku w większości przypadków będzie to typ znakowy – “character” w związku z tym, że chcemy zachować oryginalne typy danych. W tym miejscu każdy może dowolnie, według własnych potrzeb, zaimplementować własne rozwiązania. Później, podczas generowania skryptów SQL dla bazy ORACLE, przekonwertuję zmienne reprezentujące datę na typ DATE.

#Set correct (expected) data types in data.frame
dataset$WOJ <- as.character(dataset$WOJ)
dataset$POW <- as.character(dataset$POW)
dataset$GMI <- as.character(dataset$GMI)
dataset$RODZ<- as.character(dataset$RODZ)
dataset$NAZWA <- as.character(dataset$NAZWA)
dataset$NAZWA_DOD <- as.factor(dataset$NAZWA_DOD)
dataset$STAN_NA <- as.character(dataset$STAN_NA)

Dla własnej wygody poruszania się po zbiorze danych – dodaję nową kolumnę “POWIAT_PELNY”, która jest złączeniem kolumn “WOJ” oraz “POW”. Połączenie właśnie tych dwóch kolumn pozwala nam na jednoznaczne wskazanie jednostki terytorialnej, np. powiatu. Stąd też, zamiast odwoływać się później do wartości w dwóch kolumnach, będziemy mogli to robić za pomocą jednej. Ustawienie wartości “NA” w nowo dodajnej kolumnie (dla wierszy gdzie typem jednostki terytorialnej jest “województwo”) podyktowane jest tym, że województwo jest największą jednostką terytorialną. Bez takiej operacji wartość w kolumnie “POWIAT_PELNY” dla tego przypadku składałaby się wyłącznie z pola “WOJ”, co jest zjawiskiem niepożądanym. Nie jest to krok konieczny.

#Create additional column
dataset$POWIAT_PELNY <- paste(dataset$WOJ,dataset$POW,sep = "")
#Set 'Na' values into each row where NAZWA_DOD is like "województwo"
dataset[which(dataset$NAZWA_DOD=="województwo"),8]<- NA

W kolejnym kroku zostaną utworzone mniejsze podzbiory danych z jednego dużego: “dataset” . Sposób i kryteria podziału są subiektywne i również można ich dokonać według własnego pomysłu, potrzeby. Ja przyjąłem klucz:

  • zbiór województw;
  • zbiór powiatów;
  • zbiór miast na prawach powiatu oraz miasto stołeczne;
  • zbiór pozostałych, mniejszych, jednostek terytorialnych.

Czynię to wykonując poniższy fragment kodu:

#Create smaller datasets (divide into subsets)
provinces <- dataset[which(dataset$NAZWA_DOD=="województwo"),]
districts <- dataset[which(dataset$NAZWA_DOD=="powiat"),]
citiesWithPoviatRightsAndCapital <- dataset[which(dataset$NAZWA_DOD=="miasto na prawach powiatu" 
                                                  | dataset$NAZWA_DOD=="miasto stołeczne, na prawach powiatu"),]

territorialUnits <- dataset[which(dataset$NAZWA_DOD!="powiat" 
                                            & dataset$NAZWA_DOD!="województwo"
                                            & dataset$NAZWA_DOD!="miasto na prawach powiatu" 
                                            & dataset$NAZWA_DOD!="miasto stołeczne, na prawach powiatu"),]

Import do bazy danych

Na tym moglibyśmy zakończyć pracę nad podstawowym przetwarzaniem plików rejestru TERYT w środowisku R. Jednak w kolejnej części artykułu pokażę jak wykorzystać pozyskane dane importując je do systemów bazodanowych.

SQLite

Zaczynamy od utworzenia zmiennych, które wskazują jak będą nazywały się przyszłe obiekty bazodanowe.

#--- SQLite Database handler
dbSchema <- "joksch"
dbTableNameProvinses <- "wojewodztwa"
dbTableNameDistricts <- "powiaty"
dbTableNameCitiesWithPoviatRightsAndCapital  <- "miasta_na_prawach_powiatu_stolica"
dbTableNameTerritorialUnits <- "jednostki_terytorialne"

Następnie wskazujemy z jakiego sterownika baz danych będziemy korzystali, w naszym przypadku jest to SQLite. Jest to system baz danych przechowujący informacje w plikach płaskich. W drugiej linijce kodu, tego fragmentu, ustanawiamy połączenie z bazą oraz podajemy jej nazwę – u mnie: “teryt.db”:

drv <- dbDriver("SQLite") #Choose database driver
con <- dbConnect(drv = drv, dbname = "teryt.db") #establish database connection

Teraz należy przenieść dane z konkretnego obiektu data.frame do wskazanej tabeli w naszej bazie. Do tego służy funkcja dbWriteTable().

dbWriteTable(con, paste(dbSchema,".",dbTableNameProvinses,sep=""), 
             provinces[,c("WOJ","NAZWA","STAN_NA")], 
             overwrite = TRUE, append = FALSE
             )

Przyglądając się temu fragmentowi kodu, widzimy, że funkcja dbWriteTable() przyjmuje w tym wypadku 5 argumentów:

  • obiekt połączenia do bazy:
    con
    
  • nazwę tabeli:
    paste(dbSchema,".",dbTableNameProvinses,sep="")
    
  • nazwę źródłowego obiektu “data.frame”:
    provinces[,c("WOJ","NAZWA","STAN_NA")]
    

    Dodatkowo, wskazuję, które kolumny z obiektu data.frame chcę uzwględnić w nowej tabeli.

  • flagę “overwrite”:
    overwrite = TRUE
    

    Dzięki takiemu ustawieniu, podczas kolejnej kompilacji, skrypt nadpisze stare dane w tabeli SQLite.

  • flagę “append”:
    append = FALSE
    

    Dzięki takiemu ustawieniu, podczas kolejnej kompilacji, skrypt nie będzie rozszerzał tabeli o nowe dane. W połączeniu z flaga “overwrite” – dane zostaną nadpisane.

Dla pozostałych obiektów data.frame kod będzie wyglądał następująco:

dbWriteTable(con, paste(dbSchema,".",dbTableNameDistricts,sep=""), 
             districts[,c("WOJ","POW","NAZWA","STAN_NA","POWIAT_PELNY")], 
             overwrite = TRUE, append = FALSE
)

dbWriteTable(con, paste(dbSchema,".",dbTableNameCitiesWithPoviatRightsAndCapital,sep=""), 
             citiesWithPoviatRightsAndCapital[,c("WOJ","POW","NAZWA","NAZWA_DOD","STAN_NA","POWIAT_PELNY")], 
             overwrite = TRUE, append = FALSE
)

dbWriteTable(con, paste(dbSchema,".",dbTableNameTerritorialUnits,sep=""), 
             territorialUnits[,c("WOJ","POW","GMI","RODZ","NAZWA","NAZWA_DOD","STAN_NA","POWIAT_PELNY")], 
             overwrite = TRUE, append = FALSE
)

Zgodnie ze sztuką, po zakończeniu prac z bazą SQLite (jak i każdą inną operacją działąnia na plikach), należy zamknąć połączenie z bazą (plikiem):

dbDisconnect(con) #close database connection

W ten sposób utworzyliśmy właśnie bazę danych SQLite z własnymi danymi. Możemy wszystko podejrzeć za pomocą dowolnego programu obsługującego pliki SQLite – ja używam SQLiteStudio. Poniżej screen:

ORACLE SQL

W przypadku dostarczenia danych do bazy danych ORACLE zmienimy nieco podejście. Mianowicie, utworzymy pliki skryptów, które należy wykonać bezpośrednio na bazie danych. Równie dobrze będziemy mogli otworzyć wygenerowane przez nas pliki, skopiować wszystkie komendy, wkleić je do środowiska bazodanowego oraz uruchomić ich wykonywanie.

Przed przystąpieniem do programowania w R, należy przygotować swoją bazę tworząc odpowiednie tabele oraz (w moim przypadku) nowego użytkownika,  sekwencję i trigger:


-- Uncomment if You want to drop table
--DROP TABLE joksch_pl.wojewodztwa;
--DROP TABLE joksch_pl.powiaty;
--DROP TABLE joksch_pl.miasta_stolica;
--DROP TABLE joksch_pl.jednostki_terytorialne;
--DROP TRIGGER joksch_pl.jednostki_terytorialne_trg;
--DROP SEQUENCE joksch_pl.jednostki_terytorialne_seq;

-- create additional DB user
CREATE USER joksch_pl IDENTIFIED BY joksch;

-- add admin privileges to own user
GRANT dba TO joksch_pl WITH ADMIN OPTION;

--to remove all data from tables
--TRUNCATE TABLE joksch_pl.wojewodztwa;
--TRUNCATE TABLE joksch_pl.powiaty;
--TRUNCATE TABLE joksch_pl.miasta_stolica;
--TRUNCATE TABLE joksch_pl.jednostki_terytorialne;

CREATE TABLE joksch_pl.wojewodztwa(
woj VARCHAR2(2) NOT NULL,
nazwa VARCHAR2(32),
stan_na DATE,
constraint joksch_pl_wojewodztwa_pk primary key (woj)
);

CREATE TABLE joksch_pl.powiaty(
woj VARCHAR2(2) NOT NULL,
pow VARCHAR2(2) NOT NULL,
nazwa VARCHAR2(32),
stan_na DATE,
powiat_pelny VARCHAR2(8) NOT NULL,
constraint joksch_pl_powiaty_pk primary key (powiat_pelny)
);

CREATE TABLE joksch_pl.miasta_stolica(
woj VARCHAR2(2) NOT NULL,
pow VARCHAR2(2) NOT NULL,
nazwa VARCHAR2(32),
nazwa_dod VARCHAR2(40),
stan_na DATE,
powiat_pelny VARCHAR2(8) NOT NULL,
constraint joksch_pl_miasta_stolica_pk primary key (powiat_pelny)
);

CREATE TABLE joksch_pl.jednostki_terytorialne(
id NUMBER(10) NOT NULL,
woj VARCHAR2(2) NOT NULL,
pow VARCHAR2(2) NOT NULL,
gmi VARCHAR2(2) NOT NULL,
rodz VARCHAR2(2) NOT NULL,
nazwa VARCHAR2(32),
nazwa_dod VARCHAR2(40),
stan_na DATE,
powiat_pelny VARCHAR2(8) NOT NULL,
constraint joksch_pl_jedn_terytorialne_pk primary key (id)
);

CREATE SEQUENCE joksch_pl.jednostki_terytorialne_seq START WITH 1;

CREATE OR REPLACE TRIGGER joksch_pl.jednostki_terytorialne_trg
BEFORE INSERT ON joksch_pl.jednostki_terytorialne
FOR EACH ROW

BEGIN
SELECT joksch_pl.jednostki_terytorialne_seq.NEXTVAL
INTO :new.id
FROM dual;
END;
/

Przejdźmy teraz do naszego skryptu w R. Naszym celem jest stworzenie 4 plików zawierających odpowiednie instrukcje SQL. Wcześniej przygotujemy właściwie skonstruowane obiekty “data.frame”, których zawartość zapiszemy do pliku z rozszerzeniem “.SQL”. Każdy kolejny wiersz obiektu “data.frame” będzie reprezentował kolejną linijkę pliku.

W skrypcie wykorzystamy instrukcję INSERT ALL , która pozwoli wstawić do naszej tabeli, jednocześnie, wiele rekordów do wskazanej tabeli. O szczegółach tej instrukcji można doczytać pod linkiem: DOKUMENTACJA ORACLE

Tworzymy obiekt “data.frame”:

scriptFile1 <- data.frame(content=character())

Dalej, wskazujemy co ma zawierać pierwsza linijka pliku (teraz nasz “data.frame”):

#The beginning of multi-insert statement
oneLine <- list(content="INSERT ALL")

oraz dodajemy tę linijkę do obiektu “data.frame”:

scriptFile1 = rbind(scriptFile1,oneLine, stringsAsFactors=FALSE)

Dalszej części skryptu, do obiektu “scriptFile1” dodajemy tak wiele rekordów ile posiada obiekt “provinces”, który zawiera spis wszystkich województw. Wykonujemy to za pomocą pętli for, sam warunek ograniczający liczbę obiegów pętli wygląda tak:

(i in 1:nrow(provinces))

Natomiast tak wygląda już cała pętla:

#add to script body data.frame correctly built query for each row of data,frame
for(i in 1:nrow(provinces)){
  oneLine <- list(content=
                    paste("INTO wojewodztwa (woj, nazwa, stan_na) VALUES (",
                          "\'",provinces$WOJ[i],"\',",
                          "\'",provinces$NAZWA[i],"\',",
                          "\'",provinces$STAN_NA[i],"\'",
                          ")",
                          sep = "")
                  )
  
  scriptFile1 = rbind(scriptFile1,oneLine, stringsAsFactors=FALSE)
}

Należy zauważyć, że poza oczekiwanymi wartościami, jakie będziemy chcieli dodać do nowo powstającej tabeli w ORACLE, dodajemy także wymagane instrukcję INTO. Jest to wymagane, żeby zachować prawidłową składnię SQL.

Po wyjściu z pętli należy jeszcze odpowiednio zakończyć instrukcję ORACLE, dodając:

#Ending of multi-insert statement
oneLine <- list(content="SELECT * FROM dual;")
scriptFile1 = rbind(scriptFile1,oneLine, stringsAsFactors=FALSE)

Ostatnim krokiem jest zapisanie przygotowanego obiektu “data.frame” do pliku. Możemy to wykonać za pomocą funkcji “write.table()”, uwzględniając w niej odpowiednie parametry:

#Write criptFile1 data.frame as '.sql' script file 
write.table(scriptFile1, file = "script1.sql", append = FALSE, row.names=FALSE, 
            col.names = FALSE, quote = FALSE, fileEncoding="UTF-8", sep = "\r\n")

Tym sposobem przygotowaliśmy pierwszy plik skryptu. Dla pozostałych tabel wykonujemy podobne czynności, wskazane poniżej:

#--- start script2 -----------------------------------------------

scriptFile2 <- data.frame(content=character())

oneLine <- list(content="INSERT ALL")
scriptFile2 = rbind(scriptFile2,oneLine, stringsAsFactors=FALSE)

for(i in 1:nrow(districts)){
  oneLine <- list(content=
                    paste("INTO powiaty (woj, pow, nazwa, stan_na, powiat_pelny) VALUES (",
                          "\'",districts$WOJ[i],"\',",
                          "\'",districts$POW[i],"\',",
                          "\'",districts$NAZWA[i],"\',",
                          "\'",districts$STAN_NA[i],"\',",
                          "\'",districts$POWIAT_PELNY[i],"\'",
                          ")",
                          sep = "")
  )
  
  scriptFile2 = rbind(scriptFile2,oneLine, stringsAsFactors=FALSE)
}

oneLine <- list(content="SELECT * FROM dual;")
scriptFile2 = rbind(scriptFile2,oneLine, stringsAsFactors=FALSE)

write.table(scriptFile2, file = "script2.sql", append = FALSE, row.names=FALSE, 
            col.names = FALSE, quote = FALSE, fileEncoding="UTF-8", sep = "\r\n")

#--- end script2 -------------------------------------------------

#--- start script3 -----------------------------------------------

scriptFile3 <- data.frame(content=character())

oneLine <- list(content="INSERT ALL")
scriptFile3 = rbind(scriptFile3,oneLine, stringsAsFactors=FALSE)

for(i in 1:nrow(citiesWithPoviatRightsAndCapital)){
  oneLine <- list(content=
                    paste("INTO miasta_stolica (woj, pow, nazwa, nazwa_dod, stan_na, powiat_pelny) VALUES (",
                          "\'",citiesWithPoviatRightsAndCapital$WOJ[i],"\',",
                          "\'",citiesWithPoviatRightsAndCapital$POW[i],"\',",
                          "\'",citiesWithPoviatRightsAndCapital$NAZWA[i],"\',",
                          "\'",citiesWithPoviatRightsAndCapital$NAZWA_DOD[i],"\',",
                          "\'",citiesWithPoviatRightsAndCapital$STAN_NA[i],"\',",
                          "\'",citiesWithPoviatRightsAndCapital$POWIAT_PELNY[i],"\'",
                          ")",
                          sep = "")
  )
  
  scriptFile3 = rbind(scriptFile3,oneLine, stringsAsFactors=FALSE)
}

oneLine <- list(content="SELECT * FROM dual;")
scriptFile3 = rbind(scriptFile3,oneLine, stringsAsFactors=FALSE)

write.table(scriptFile3, file = "script3.sql", append = FALSE, row.names=FALSE, 
            col.names = FALSE, quote = FALSE, fileEncoding="UTF-8", sep = "\r\n")

#--- end script3 -------------------------------------------------

#--- start script4 -----------------------------------------------

scriptFile4 <- data.frame(content=character())

oneLine <- list(content="INSERT ALL")
scriptFile4 = rbind(scriptFile4,oneLine, stringsAsFactors=FALSE)

for(i in 1:nrow(territorialUnits)){
  oneLine <- list(content=
                    paste("INTO jednostki_terytorialne (woj, pow, gmi, rodz, nazwa, nazwa_dod, stan_na, powiat_pelny) VALUES (",
                          "\'",territorialUnits$WOJ[i],"\',",
                          "\'",territorialUnits$POW[i],"\',",
                          "\'",territorialUnits$GMI[i],"\',",
                          "\'",territorialUnits$RODZ[i],"\',",
                          "\'",territorialUnits$NAZWA[i],"\',",
                          "\'",territorialUnits$NAZWA_DOD[i],"\',",
                          "\'",territorialUnits$STAN_NA[i],"\',",
                          "\'",territorialUnits$POWIAT_PELNY[i],"\'",
                          ")",
                          sep = "")
  )
  
  scriptFile4 = rbind(scriptFile4,oneLine, stringsAsFactors=FALSE)
}

oneLine <- list(content="SELECT * FROM dual;")
scriptFile4 = rbind(scriptFile4,oneLine, stringsAsFactors=FALSE)

write.table(scriptFile4, file = "script4.sql", append = FALSE, row.names=FALSE, 
            col.names = FALSE, quote = FALSE, fileEncoding="UTF-8", sep = "\r\n")

#--- end script4 -------------------------------------------------

Teraz pozostało nam tylko wykonanie instrukcji zawartych w tych plikach, ja do codziennej pracy na prywatnym komputerze wykorzystuję oficjalne oprogramowanie dostarczane przez ORACLE: SQLDeveloper.

Podsumowanie

Tym sposobem dotarliśmy do końca artykułu. Udało nam się (mam nadzieję, ze wszystkim zainteresowanym) sparsować pliki z rejestru TERYT oraz przygotować je do wykorzystania we własnych projektach. Kompletny kod dostępny dostępny na platformie bitbucket: KOMPLETNE KODY – repozytorium

Dodaj komentarz