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.
Uwolnić dane!
Amerykanie odpalili http://data.gov - site ma być repozytorium danych z różnorakich agencji rządowych.
To jest dobry ruch - częściowa odpowiedź na dokładnie ten sam problem o którym mówią Hans Rosling i Tim Berners Lee.
W skrócie chodzi o to że ogromne pokłady cennych publicznych danych leżą zakopane w bazach konkretnych instytucji, agencji, departamentów... Zamiast pracować dla pożytku społeczności która za nie zapłaciła.
Czekamy na http://data.eu [Unia Europejska].
Czekamy na http://data.un.org/ [ONZ].
Czekamy na http://data.gov.pl [polskie agendy / instytucje rządowe].
Czekamy na http://data.stat.gov.pl [ GUS].
RAW DATA NOW!
czego _nie_ robić z bazami danych
Stare ale jare:
10 Things You Shouldn't Do with SQL Server (Data Access Developer "Don'ts").
Dotyczy .NET i MS SQL ale większość opisanych problemów jest niezależne od konkretnego produktu.
Replikacja w PostgreSQL za pomocą Slony-I – update
W poprzednim artykule opisałem jak zestawić replikację w postgresie za pomocą slon-tools.
Dziś chciałem dodać parę rzeczy których tam zabrakło, a które są często potrzebne w praktycznym zastosowaniu Slony-I.
Dodawanie tabeli do replikacji
Załóżmy że chcemy dodać do replikacji tabelę froobles.
- Tabela musi istnieć na obu serwerach i mieć klucz główny. Możemy utworzyć ją ręcznie, lub wykorzystać skrypt który to zrobi:
slonik_execute_script set1 /path/to/froobles.sql
- W konfigu (plik slon_tools.conf) musimy dodać kolejną publikację (set) Slony-I. Dopisujemy go do hasha $SLONY_SETS.
$SLONY_SETS = { set1 => { set_id => 1, table_id => 1, sequence_id => 1, pkeyedtables => [ 'accounts', 'branches', 'tellers', ], }, # tymczasowy set dla dodawanych tabel set2 => { set_id => 2, table_id => 100, sequence_id => 100, pkeyedtables => [ 'froobles', ], }, }; 1; -
Następnie musimy utworzyć publikację w klastrze.
postgres@merry:~$ slonik_create_set set2 | slonik
-
Utoworzoną publikację (set #2) musimy zasubskrybować do slave'a (node #2):
postgres@merry:~$ slonik_subscribe_set set2 2 | slonik
-
Zawartość tabeli zostanie przekopiowana na serwer slave. Może to potrwać zależnie od wielkości tabeli.
-
Gdy już upewniliśmy się że tabela jest replikowana, możemy złączyć publikacje z powrotem w jedną. Służy do tego polecenie slonik_merge_sets:
postgres@merry:~$ slonik_merge_sets 1 set1 set2 | slonik
-
Uaktualniamy plik slon_tools.conf (patrz wyżej) tak by odzwierciedlał aktualną konfigurację klastra. Nowo dodane tabele powinny się znaleźć w "set1". Robimy to aby uniknąć zamieszania w razie konieczności odtworzenia klastra.
$SLONY_SETS = { set1 => { set_id => 1, table_id => 1, sequence_id => 1, pkeyedtables => [ 'accounts', 'branches', 'tellers', 'froobles', ], }, }; 1;
Usuwanie tabeli z replikacji
Jeśli chcemy dodawać, to pewnie czasem i usuwać.
Przykład: Usuwamy tabelę z publikacji nr 1. Musimy znać też ID tabeli nadane przez slonika; możemy je podejrzeć w bazie w tabeli _webdev.sl_table. W tym przykładzie jest to 100.
postgres@merry:~$ slonik_drop_table 100 set1 | slonik
Podsumowanie
Narzędzia slon-tools są naprawdę proste.
Wszystkie skrypty dostępne w pakiecie rozpoczynają się od prefiksu slonik_ lub slon_. Polecam zapoznanie się z nimi oraz z doumentacją Slony-I dostępną pod adresem http://slony.info.
Powodzenia!