imitatio creatio co we łbie piszczy

Import zbiorów TERYT do bazy danych SQL

Główny Urząd Statystyczny administruje bazą danych podziału administracyjnego Polski, z dokładnością do ulic i nazw miejscowych.

Baza ta nazywa się TERYT i jest udostępniana publicznie w formie plików XML, w serwisie stat.gov.pl.

W tym artykule pokażę jak zaimportować te dane do bazy relacyjnej (na przykładzie PostgreSQL oraz Microsoft SQL Server).

Pobieranie

Najpierw musimy pobrać aktualną wersję plików TERYT. Poniżej polecenia wget, oczywiście można też uzyć przeglądarki i zapisać pliki pod odpowiednimi nazwami.

wget "http://www.stat.gov.pl/broker/access/prefile/downloadPreFile.jspa?id=147" -O teryt_wmrodz.zip
wget "http://www.stat.gov.pl/broker/access/prefile/downloadPreFile.jspa?id=203" -O teryt_terc.zip
wget "http://www.stat.gov.pl/broker/access/prefile/downloadPreFile.jspa?id=205" -O teryt_simc.zip
wget "http://www.stat.gov.pl/broker/access/prefile/downloadPreFile.jspa?id=222" -O teryt_ulic.zip

Transformacja do CSV

Następnie należy przetłumaczyć te zbiory do formatu tabelarycznego.
Możemy użyć do tego celu skryptu w języku sed z tej strony.

Uruchomienie (bash):

for zb in wmrodz terc simc ulic; do
    unzip -p teryt_$zb.zip | sed -n -f teryt.sed > teryt_$zb.csv
done

Powinno to stworzyć 4 pliki CSV.

Utworzenie tabel

Chcemy zaimportować dane do relacyjnej bazy danych, więc na początek musimy ją stworzyć :-)

Poniżej jest DDL tworzący odpowiednie tabele. Starałem się używać ANSI SQL żeby kod działał w różnych bazach. Starałem się też, aby wszystkie tabele miały odpowiednie klucze (główne i obce).

create table teryt_wmrodz(
        rm varchar(2) not null primary key,
        nazwa_rm varchar(30) not null unique,
        stan_na varchar(10) not null
);
create table teryt_terc(
        wojewodztwo varchar(2) not null,
        powiat varchar(2),
        gmina varchar(2),
        rodz varchar(2),
        nazwa varchar(100) not null,
        nazdod varchar(100) not null,
        stan_na varchar(10) not null,
        constraint teryt_terc_key unique ( wojewodztwo, powiat, gmina, rodz ),
        constraint teryt_terc_key2 unique ( wojewodztwo, powiat, gmina, nazdod )
);
create table teryt_simc(
        wojewodztwo varchar(2) not null,
        powiat varchar(2) not null,
        gmina varchar(2) not null,
        rodz_gmi varchar(2) not null,
        RM varchar(2) not null,
        MZ varchar(2) not null,
        nazwa varchar(100) not null,
        sym varchar(10) not null primary key,
        sympod varchar(10) not null references teryt_simc,
        stan_na varchar(10) not null
);
create table teryt_ulic(
        wojewodztwo varchar(2) not null,
        powiat varchar(2) not null,
        gmina varchar(2) not null,
        rodz_gmi varchar(2) not null,
        symbol varchar(10) not null references teryt_simc (sym),
        sym_ul varchar(10) not null,
        cecha varchar(10) not null,
        NAZWA_1 varchar(100) not null,
        NAZWA_2 varchar(100),
        stan_na varchar(10)  not null,
        constraint teryt_ulic_pkey primary key (symbol,sym_ul),
        constraint teryt_ulic_fkey_teryt_terc foreign key (wojewodztwo,powiat,gmina,rodz_gmi) references teryt_terc (wojewodztwo, powiat, gmina, rodz)
);

Ładowanie danych do bazy

Można użyć dowolnej bazy relacyjnej i narzędzia do ładowania; ja testowałem to na dwóch:

PostgreSQL

Import przez zwykłe COPY, poniżej polecenia:

truncate teryt_terc, teryt_wmrodz, teryt_ulic, teryt_simc;
SET client_encoding TO 'UTF-8';
\copy teryt_wmrodz from 'teryt_wmrodz.csv' with csv delimiter '|'
\copy teryt_terc from 'teryt_terc.csv' with csv delimiter '|'
\copy teryt_simc from 'teryt_simc.csv' with csv delimiter '|'
\copy teryt_ulic from 'teryt_ulic.csv' with csv delimiter '|'

MSSQL

Import za pomocą DTSWizard, dostępny spod SSMS.

Nie będę tu wrzucał screenów z SSMS, import jest prosty, jednak po nim trzeba upewnić się, że kodowanie znaków jest poprawne.

Celebracja

Voilà! Możemy się "cieszyć" danymi TERYT w formie relacyjnej. Chcesz wiedzieć jakie są dzielnice Krakowa? Ile jest w Polsce ulic imienia Antoniego Malczewskiego? Ile miejscowości o nazwie na literę K jest w województwie zachodniopomorskim? Proszę bardzo! Toż to dziecinnie proste, wystarczy znać SQL.

---

Update (2010-09-11): link na int3.pl jest niedostępny. Załączam lokalne kopie skryptów: teryt.sed, teryt2.sed.