Scott Tiger Tech Blog

Blog technologiczny firmy Scott Tiger S.A.

Archiwum: 'Bazy danych' Kategorie

Państwowy Rejestr Granic: Punkty adresowe

Autor: Piotr Karpiuk o 29. marca 2016

Centralny Ośrodek Dokumentacji Geodezyjnej i Kartograficznej (CODGiK) w dziale Dane bez opłat udostępnia za darmo bazę punktów adresowych, dość często aktualizowaną (nawet co kilka dni).

Najważniejsza jest w niej tabela PunktAdresowy (przeszło 7 mln rekordów), o następującej strukturze (wymieniłem najciekawsze kolumny):

PunktAdresowy
wojewodztwo
powiat
gmina
miejscowosc
ulica
numerPorzadkowy numer budynku
kodPocztowy wypełniony w ok. 75% rekordów
status wartości: istniejacy (97,64%), prognozowany (1,9%), wTrakcieBudowy (0,46%)
gps współrzędne punktu na mapie (w układzie EPSG:2180)

W bazie jednak jest więcej. Każdy punkt adresowy zawiera dowiązania do rekordów w tabelach JednostkaAdministracyjna (kraj/województwo/powiat/gmina), Miejscowosc oraz Ulica. Struktura tych tabel jest następująca (pola wypełnione są w 100% chyba że napisano inaczej):

JednostkaAdministracyjna
nazwa
idTERYT kod TERC
poziom 1: państwo, 2: województwo, 3: powiat, 4: gmina
Miejscowosc
nazwa
idTERYT kod SIMC
gps współrzędne punktu na mapie, wypełnione w ok. 11% rekordów
Ulica
idTERYT kod ULIC
przedrostek1Czesc np. "pl.", "al."
przedrostek2Czesc np. "ks."
nazwaCzesc np. "Jerzego"
nazwaGlownaCzesc np. "Popiełuszki"
posList lista punktów GPS wyznaczających przebieg ulicy na mapie; wypełnione w ok. 6%

Ujęty w rekordach tabel JednostkaAdministracyjna i Miejscowosc podział administracyjny kraju możemy sobie wyobrazić jako drzewo (w korzeniu będzie rekord POLSKA, potem województwa, powiaty, gminy i miejscowości). Ten model nie rozciąga się jednak na ulice. Każda ulica w kraju jest reprezentowana w tabeli Ulica jako jeden obiekt. Jedna ulica może przebiegać przez kilka miejscowości (sąsiadujących ze sobą). Punkt adresowy oczywiście ma dowiązanie do jednej miejscowości i (zwykle) do jednej ulicy (na wsiach adres pocztowy często zawiera tylko nazwę miejscowości i numer domu).

Przetwarzanie bazy

Do pobrania ze strony CODGiK jest duży (ok. 700 MB), spakowany plik punkty_adr.zip zawierający 16 plików XML (dokładniej: GML) po jednym dla każdego województwa, po rozpakowaniu ok. 16 GB. Format XML dobrze się nadaje do udostępniania zbioru danych innym, ale gdyby ktoś chciał zaimportować dane do relacyjnej bazy danych, to może skorzystać z efektów mojej pracy. Napisałem program w Javie, który konwertuje pliki XML do jednego pliku bazy danych SQLite o objętości ok. 3 GB.

Program wywołujemy poleceniem

./run.sh KATALOG

Gdzie KATALOG to nazwa katalogu, w którym rozpakowaliśmy pliki XML. Wynik po kilkunastu minutach pojawia się w pliku PRG.db, który możemy eksplorować za pomocą standardowego klienta SQLite:

sqlite3 PRG.db

Tags:
Napisany w Bazy danych, Uncategorized | 1 Komentarz »

Państwowy Rejestr Nazw Geograficznych: nazwy miejscowości

Autor: Piotr Karpiuk o 14. grudnia 2015

Państwowy Rejestr Nazw Geograficzny (PRNG) jest do pobrania ze strony CODGiKu w formatach shapefile, XLS (MS Excel) lub GML. Składa się z dwóch części: nazwy miejscowości i nazwy obiektów fizjograficznych. Dzisiaj przyjrzymy się zawartości pliku z nazwami miejscowości. Mamy tam 124.801 rekordów, a ich struktura przedstawiona została poniżej.

Uwagi: Jeśli jakiejś kolumny nie wymieniono, to znaczy że jest pusta. Zawsze są podane współrzędne geograficzne punktu centralnego każdego obiektu.

Nazwa Typ % wypełnienia Uwagi Przykłady
idPRNG INT 100,00 unikalny identyfikator 57
nazwa_glowna TEXT 100,00 w mianowniku Abramowice Kościelne
rodzaj_obiektu TEXT 100,00
wieś 43097
część wsi 41938
przysiółek wsi 11698
część miasta 10230
osada 5125
kolonia wsi 4068
osada leśna 2404
kolonia 2190
leśniczówka 1319
miasto 919
osada wsi 787
część kolonii 280
osada leśna wsi 240
przysiółek 144
przysiółek osady 81
część osady 74
przysiółek kolonii 73
schronisko turystyczne 45
kolonia kolonii 29
osada osady 15
kolonia osady 14
osiedle 11
osiedle wsi 9
część przysiółka 5
osada kolonii 4
osada kolejowa 1
osada młyńska 1
obiekt_nadrzedny TEXT 65,50 miejscowość nadrzędna Sulejów
funkcja_administracyjna TEXT 1,86
siedziba władz gminy wiejskiej 1405
siedziba władz gminy miejsko-wiejskiej 520
siedziba władz powiatu 269
siedziba władz gminy miejskiej 62
siedziba władz miasta na prawach powiatu 47
siedziba wojewody 14
miasto stołeczne/siedziba władz powiatu 1
siedziba sejmiku województwa 1
siedziba wojewody/siedziba sejmiku województwa/siedziba władz miasta na prawach powiatu 1
siedziba władz powiatu/siedziba sejmiku województwa 1
siedziba władz powiatu/siedziba władz miasta na prawach powiatu 1
dopelniacz TEXT 99,99 -wic -nych
przymiotnik TEXT 35,27 abramowicki
uwagi TEXT 10,90 do 2006 – wieś
zrodlo TEXT 99,99 571 różnych wartości Mapa topograficzna w skali 1:10000
element_rozrozniajacy TEXT 63,56 „Część nazwy niebędąca elementem rodzajowym; w przypadku nazw obiektów fizjograficznych mających na początku element rodzajowy jest podawany wyłącznie stojący za nim element rozróżniający, w pozostałych przypadkach jest powtórzona cała nazwa główna podana w mianowniku”
element_rodzajowy TEXT 4,09 „Część nazwy będąca rzeczownikiem pospolitym określającym nazwany obiekt geograficzny przez wskazanie jego cech; podawany wyłącznie wtedy, gdy znajduje się na początku nazwy obiektu fizjograficznego”

Osiedle 3213
Kolonia 1624
Gajówka 113
Leśniczówka 81
Osada 42
Wieś 13
Dzielnica 5
Schronisko 4
Nadleśnictwo 2
Folwark 1
gpslat, gpslon TEXT 100,00 Współrzędne GPS 51°21’10”
y, x REAL 100,00 Współrzędne EPSG:2180 387690.69
data_modyfikacji DATE 100,00 YYYY-MM-DD 2005-03-02
system_zewnetrzny TEXT 81,30 Słowo „TERYT” TERYT
identyfikator_zewnetrzny TEXT 81,30 Kod TERYT/SIMC (7 cyfr) 0712344
identyfikator_iip TEXT 100,00 unikalny identyfikator 00000000-0000-0000-0000-000000106599
status_nazwy TEXT 100,00 „Stopień wiarygodności, jaki można przypisać danej nazwie głównej”

urzędowa 101464
niestandaryzowana 23337
urzędowa
nazwa_dodatkowa TEXT 1,86 Bajdlówka
kod_jezyka_nazwy_dodatkowej TEXT 1,55
PL 754
CBS 725
DEU 352
LTU 30
BLR 26
PL/ 15
/ 11
SLA 9
/PL 4
CBS/ 2
/CBS 1
PL/CBS 1
PL
jezyk_nazwy_dodatkowej TEXT 1,55
polski 754
kaszubski 725
niemiecki 352
litewski 30
białoruski 26
polski/ 15
/ 11
łemkowski 9
/polski 4
kaszubski/ 2
/kaszubski 1
polski/kaszubski 1
polski
latynizacja_nazwy_dodatkowej TEXT 0,06 Antonovo
nazwa_historyczna TEXT 1,85 Bajerka
nazwa oboczna TEXT 5,52 „Nazwa lub nazwy oboczne funkcjonujące obecnie w języku polskim, inne niż przyjęta nazwa główna danego obiektu, podane w mianowniku” Banasiowa Rola
uwagi_nazw_dodatkowych TEXT 0,23 do 1006 r.
uwagi_nazw_historycznych TEXT 1,83 do 2002 r.
uwagi_nazw_obocznych TEXT 0,05 dawna gajówka
wojewodztwo TEXT 100,00 nazwa województwa podkarpackie
powiat TEXT 100,00 nazwa powiatu leżajski
gmina TEXT 100,00 nazwa gminy Leżajsk-gmina miejska
identyfikator_jedn._podz._teryt._kraju TEXT 2,49 Kod TERYT/TERC gminy (7 cyfr) 0616045
data_wprowadzenia DATE 100,00 Data wprowadzenia do PRNG: YYYY-MM-DD 2004-04-07
przestrzen_nazw TEXT 100,00 Zawsze „PL.PZGiK.204.PRNG” PL.PZGiK.204.PRNG

Tags:
Napisany w Bazy danych, GIS | Brak komentarzy »

Podstawy PostGIS

Autor: Piotr Karpiuk o 4. grudnia 2015

PostgreSQL został zaprojektowany z myślą o łatwej rozbudowie. Twórcy rozszerzeń mogą definiować własne typy danych, typy indeksów (korzystając z tzw. Generic Index Structure), jak również dodawać nowe funkcje. Rozszerzenie PostGIS korzysta z tych możliwości pełnymi garściami, aby uzupełnić relacyjną bazę danych o obsługę danych geograficznych, dzięki czemu duet PostgreSQL+PostGIS stał się podstawą systemów GISowych w świecie open source.

Co tam zatem mamy?

  • bogactwo funkcji do przeszukiwania, analizy, konwersji i zarządzania danymi przestrzennymi,
  • wsparcie zarówno dla danych wektorowych, jak i rastrowych,
  • zgodność ze standardami Open Geospatial Consortium,
  • PostGIS obrósł szeregiem korzystających z niego projektów takich jak Proj4 (do konwersji między odwzorowaniami kartograficznymi), GEOS, czy GDAL.

Tak jak powszechnie używanym interfejsem graficznym dla PostgreSQL jest pgAdmin, tak dla PostGISa jest to Quantum GIS (qGIS).

Tworzenie bazy z obiektami przestrzennymi jest proste:

$ createdb -U postgres Real-State
$ psql -d Real-Estate -U postgres
Real-State# CREATE EXTENSION postgis;

Tabela przestrzenna (ang. spatial table) to tabela zawierająca pole typu geometry do danych dwuwymiarowych (istnieje też typ geography do danych 4D używany do reprezentowania dużych odległości, z mniejszą ilością operujących na nim funkcji, ale uwzględniający różnice wysokości n.p.m. i krzywiznę Ziemi; ponadto mamy też typ danych raster dla danych rastrowych).

Real-State# CREATE TABLE tbl_landmarks ( ..., the_geom geometry, ... );

Wartości typu geometry możemy zapisywać w notacji Well-Known text (WKT):

INSERT INTO tbl_landmarks (the_geom) VALUES (ST_GeomFromText('POINT(-0.116190, 51.556173)'));

Oprócz POINT może być np. MULTIPOINT (zbiór punktów), LINESTRING (łamana), MULTILINESTRING (zbiór łamanych), POLYGON (wielokąt), MULTIPOLYGON, itp.

W praktyce dobrze jest dla każdej wartości typu geometry definiować odwzorowanie kartograficzne, i dlatego zamiast notacji WKT lepiej użyć tzw. Extended WKT (tutaj użyjemy odwzorowania WGS84, czyli SRID 4326 – używany w nawigacji satelitarnej i Google Maps):

INSERT INTO tbl_landmarks (the_geom) VALUES (ST_GeomFromEWKT('SRID=4326;POINT(51.556173, -0.116190)'));

Bardzo często chcemy zaimportować do bazy zawartość pliku *.shp. Do konwersji takiego pliku na polecenia INSERT do PostgreSQLa możemy użyć narzędzia shp2pgsql wchodzącego w skład PostGISu:

$ shp2pgsql -g the_geom points_file.shp tbl_landmarks > landmarks.sql

Czytaj więcej »

Tags: ,
Napisany w Bazy danych, GIS | Brak komentarzy »

Mapa BTSów

Autor: Piotr Karpiuk o 4. listopada 2015

BTSearch to serwis pokazujący rozmieszczenie masztów operatorów telefonii komórkowej (ang. Base Transceiver Station, BTS). Osoby które zastanawiają się którego operatora wybrać na wsi i czy da się tam korzystać z LTE, mogą sprawdzić gdzie jest najbliższy maszt danego typu i operatora.

Napisany w Bazy danych | Brak komentarzy »

Apache HBase

Autor: Piotr Karpiuk o 9. marca 2015

HBase to otwartoźródłowa, zaimplementowana w Javie, rozproszona baza danych inspirowana Googlowym BigTable. Jest częścią projektu Apache Hadoop i przechowuje dane w HDFS (Hadoop Distributed FileSystem). Jest to przykład tzw. kolumnowej NoSQLowej bazy danych, pozwalającej na przechowywanie w sposób odporny na awarie dużej ilości danych rzadkich (ang. sparse data) – które w relacyjnej bazie wymagałyby tabel z dużą ilością przeważnie pustych kolumn.

Pierwotnie HBase był tworzony z myślą o przetwarzaniu języka naturalnego, a od czasu przejęcia projektu przez Apache z bazy korzysta obecnie szereg firm (w tym Twitter, Stumbleupon, eBay, Yahoo!), a bodaj najbardziej spektakularne jest użycie tej bazy przez Facebooka do implementacji komunikatora internetowego Facebook Messenger.

Powszechna wiedza głosi, że sens użycia HBase pojawia się dopiero przy przetwarzaniu co najmniej 100 GB danych przy użyciu min. 5 maszyn w klastrze.

Autorzy dokumentacji używają pojęć nawiązujących do modelu relacyjnego: tabela, wiersz, kolumna – ale jest to bardzo kontrowersyjny pomysł. Osobie przyzwyczajonej do świata relacyjnych baz danych w miarę wgryzania się w HBase oczy będą się coraz bardziej otwierać ze zdumienia. W rzeczy samej, HBase wobec Oracla jest jak zły brat bliźniak, Bizarro, mr. Hyde czy Frankenstein.

Spójrzmy na poniższy rysunek, objaśniający model danych omawianej dzisiaj bazy:

W tabeli mamy dowolnie wiele wierszy, każdy identyfikowany unikalnym kluczem. Kolumny są pogrupowane w rodziny kolumn (zwane też superkolumnami). O ile rodzin kolumn zwykle jest kilka, o tyle samych kolumn mogą być miliony. HBase nie jest transakcyjna i zapewnia atomowość na poziomie rekordu (wiersza), przy czym mamy wersjonowanie: pamiętane są domyślnie 3 ostatnie wersje każdego rekordu opatrzone stemplami czasowymi. Sens istnienia superkolumn jest taki, że dla każdej z nich można zdefiniować inne parametry bazy danych, np. rodzaj kompresji danych (GZ, LZO), poziom redundancji, czas po jakim dane mają być usuwane, czy wersjonowanie. Modyfikacja parametrów superkolumny jest kosztowna – pociąga za sobą utworzenie nowej superkolumny z nową specyfikacją i skopiowanie wszystkich danych – dlatego warto ustawić parametry na docelowe zanim zacznie się wstawiać dane. Wartości kolumn nie mają typów i są traktowane jako ciągi bajtów.

W zasadzie, być może zamiast kurczowo trzymać się nomenklatury nazewniczej z relacyjnych baz danych lepiej byłoby spojrzeć na model danych HBase jak na 4-poziomową mapę asocjacyjną (kolejne poziomy to tabela, klucz, superkolumna i kolumna).

Nazwę kolumny określa się mianem kwalifikatora kolumny (ang. column qualifier). Połączenie klucza wiersza i pełnej nazwy kolumny (wraz z nazwą rodziny) zapisuje się w postaci table/family:qualifier.

Rekordy w bazie są posortowane wedle klucza i podzielone na rozłączne regiony, przy czym za każdy region odpowiedzialna jest inna maszyna klastra.

HBase nie posiada języka zapytań, nie ma indeksów. Zapewnione jest tylko skanowanie całej tabeli i bardzo szybki dostęp do rekordu po kluczu i do wartości kolumny w rekordzie, jak również Hadoopowy mechanizm MapReduce.

Facebook używa HBase jako głównego komponentu swojej infrastruktury komunikatów, zarówno do przechowywania komunikatów jak i odwróconego indeksu (ang. inverted index) na potrzeby wyszukiwania.
W tabeli implementującej indeks:

  • Kluczem wiersza jest ID użytkownika.
  • Kwalifikatorami kolumn są słowa występujące w komunikatach tego użytkownika.
  • Stemple czasowe są identyfikatorami komunikatów zawierających to słowo.

W ten sposób wykorzystywane jest wersjonowanie.

W przykładach poniżej będziemy używać powłoki napisanej w języku JRuby, ale są też inne sposoby komunikacji z bazą (najpopularniejszy jest Thrift):

Nazwa Metoda połączenia Dojrzałość
Shell Bezpośrednia Tak
Java API Bezpośrednia Tak
Thrift Protokół binarny Tak
REST HTTP Tak
Avro Protokół binarny Nie

Czytaj więcej »

Tags: , ,
Napisany w Bazy danych, Cloud computing | Brak komentarzy »