MichałOkulewicz,AnetaRosłan AplikacjeiusługiSystemówInformacjiGeograﬁcznej

(1)

Bazy danych Indeksy przestrzenne

Aplikacje i usługi Systemów Informacji Geograficznej

Michał Okulewicz, Aneta Rosłan

Wydział Matematyki i Nauk Informacyjnych Politechnika Warszawska

Michał Okulewicz, Aneta Rosłan Aplikacje i usługi GIS

(2)

SQL/MM Spatial i indeksy przestrzenne

1 Bazy danych Typy obiektów Typy danych

Operacje przestrzenne

2 Indeksy przestrzenne k-d drzewo R-drzewo

(3)

Typy obiektów Typy danych Operacje przestrzenne

Przechowywanie danych przestrzennych w bazie danych I

Standardy

• Simple Feature Access (Simple Features) - standard OGC oraz ISO 19125:2004

• SQL Multimedia and Application Packages (SQL/MM) - standard ISO/IEC 13249-3:2011 SQL/MM Spatial

Zawartość

• Definicje sposobu przechowywania danych/reprezentacji danych jako tekst (WKT) i dane binarne (WKB)

• Definicje możliwych operacji przestrzennych

(4)

Przechowywanie danych przestrzennych w bazie danych II

Przykład

• (E)WKB -

0101000020E61000009A9999999999F13F00000000000029C0 Kolejność bajtów (little/big endian), Typ (01 = punkt), Odwzorowanie, Pierwsza współrzędna, Druga współrzędna.

• (E)WKT - SRID=4326;POINT(1.1 -12.5)

(5)

Systemy Zarządzania Bazą Danych implementujące SQL/MM Spatial

• MS SQL Server (od wersji 2008)

• Oracle Spatial (dodatek do wersji Enterprise)

• PostGIS (rozszerzenie PostgreSQL)

• i inne (w tym MySQL czy SQLite)

(6)

Typy obiektów

• Punkty - POINT(-118.4079 33.9434)

• Linie - LINESTRING(-118.4079 33.9434, 2.5559 49.0083)

• Wielokąty - POLYGON((0 0, 10 0, 10 10, 0 10, 0 0),(1 1, 1 2, 2 2, 2 1, 1 1))

• Bryły - POLYHEDRALSURFACE Z ( ((0 0 0, 0 1 0, 1 1 0, 1 0 0, 0 0 0)), ((0 0 0, 0 1 0, 0 1 1, 0 0 1, 0 0 0)), ((0 0 0, 1 0 0, 1 0 1, 0 0 1, 0 0 0)), ((1 1 1, 1 0 1, 0 0 1, 0 1 1, 1 1 1)), ((1 1 1, 1 0 1, 1 0 0, 1 1 0, 1 1 1)), ((1 1 1, 1 1 0, 0 1 0, 0 1 1, 1 1 1)) ))

(7)

Typy danych

• Geometry - obiekty przestrzenne we współrzędnych w danym odwozorowaniu rozpatrywane w kartezjańskim układzie współrzędnych

• Geography - obiekty przestrzenne rozpatrywane na sferze (WGS 84)

(8)

Operacje przestrzenne

Operacje na zbiorach

• Przecięcie

• Suma

• Zawieranie

• Tworzenie buforu

Operacje geometryczne

• Odległość

• Powierzchnia

Inne

• Konwersja z i do KMLa

• Konwersja z i do GMLa

(9)

Przykłady zapytań przestrzennych I

Źródło przykładów 1 Źródło przykładów 2

Tworzenie bazy danych CREATE TABLE augis.dane (

point geometry(Point,4326), line geometry(LineString,4326), geopoint geography(Point)

)

(10)

Przykłady zapytań przestrzennych II

Wstawianie danych

INSERT INTO augis.bus stops (name, point)

VALUES (

’Politechnika 01’,

ST GeometryFromText(’POINT(21.0113 52.2200)’,4326) )

(11)

Przykłady zapytań przestrzennych III

Wyszukiwanie przestrzenne SELECT augis.roads.name FROM augis.roads

WHERE ST Intersects(

(SELECT point

FROM augis.bus stops

WHERE augis.bus stops.name = ’Politechnika 01’), line);

(12)

Przykłady zapytań przestrzennych IV

Pomiary przestrzenne SELECT

augis.roads.name as street,

ST Length(line::geography) as length, augis.bus stops.name as bus stop, ST Distance

(point,

line) as cartesian, ST Distance

(point::geography,

line::geography) as meters FROM augis.roads, augis.bus stops;

(13)

Wybrane operacje na obiektach przestrzennych

• ST Union(geometry),ST Union(geometry A, geometry B), ST Union(geometry[ ]) - suma zbiorów,

• ST Difference(geometry A, geometry B) - różnica zbiorów,

• ST SymDifference(geometry A, geometry B) - różnica symetryczna,

• ST Intersection(geometry A, geometry B) - przecięcie zbiorów,

• ST Buffer(geometry A, float distance) - tworzenie bufora wokół obiektu,

• ST ConvexHull(geometry A) - tworzenie otoczki wypukłej,

• ST Simplify(geometry A, float tolerance) - tworzenie uproszczonej reprezentacji obiektu.

(14)

Wybrane operacje na obiektach przestrzennych

(15)

Wybrane operacje na obiektach przestrzennych

(16)

Wybrane operacje na obiektach przestrzennych

(17)

Wybrane operacje na obiektach przestrzennych

(18)

Wybrane operacje na obiektach przestrzennych

(19)

Wybrane operacje na obiektach przestrzennych

(20)

Wybrane operacje na obiektach geography

• ST AsSVG(geography) - zwraca fragment SVG odpowiadający obiektowi,

• ST AsGML(geography) - zwraca fragment GML odpowiadający obiektowi,

• ST AsKML(geography) - zwraca fragment KML odpowiadający obiektowi,

• ST AsGeoJson(geography) - zwraca fragment JSONa odpowiadający obiektowi,

• ST Distance(geography, geography) - zwraca minimalną odległość między obiektami,

• ST Area(geography) - zwraca obszar obiektu,

• ST Length(geography) - zwraca długość obiektu,

• ST Transform(geography, srid) - zwraca obiekt w odwzorowaniu o kodzie EPSG:srid.

(21)

Wybrane operacje na obiektach geography

(22)

Wybrane operacje na obiektach geography

(23)

Wybrane operacje na obiektach geography

(24)

Wybrane operacje na obiektach geography

(25)

Wybrane operacje na obiektach geography

(26)

Wybrane operacje na obiektach geography

(27)

Wybrane operacje na obiektach geography

(28)

Wybrane zapytania przestrzenne

• ST Intersects(geometry A, geometry B) - sprawdza czy A przecina się z B,

• ST Touches(geometry A, geometry B) - sprawdza czy A ma punkt wspólny wyłącznie z brzegiem B,

• ST Crosses(geometry A, geometry B) - sprawdza czy A przecina B,

• ST Disjoint(geometry A, geometry B) - sprawdza czy A i B są rozłączne,

• ST Contains(geometry A, geometry B) - sprawdza czy A zawiera całkowicie B,

• ST Within(geometry A, geometry B) - sprawdza czy A całkowicie zawiera się w B,

• ST DWithin(geometry A, geometry B, float radius) - sprawdza czy A całkowicie zawiera się w obiekcie B

poszerzonym o radius.

(29)

Wybrane zapytania przestrzenne

(30)

Wybrane zapytania przestrzenne

(31)

Wybrane zapytania przestrzenne

(32)

Wybrane zapytania przestrzenne

(33)

Wybrane zapytania przestrzenne

(34)

Wybrane zapytania przestrzenne

(35)

k-d drzewo R-drzewo

Indeksy przestrzenne

• Dane przestrzenne przenoszą problem efektywnego

wyszukiwania w zbiorach danych w nowy wymiar (dosłownie).

• Jak poprawić operacje przestrzenne bez indeksu przestrzennego?

• Można obliczyć współrzędne ograniczające każdy obszar i na nowych kolumnach zbudować klasyczny indeks.

• Znacznie poprawi operacje na zbiorach, w niewielkim stopniu poprawi wyszukiwanie najbliższych obiektów.

• W praktyce tworzy się do tego zadania specjalne struktury danych: k-d drzewa, R-drzewa, X-drzewa, . . .

(36)

k-d drzewo R-drzewo

Indeksy przestrzenne

(37)

k-d drzewo R-drzewo

Indeksy przestrzenne

(38)

k-d drzewo R-drzewo

Indeksy przestrzenne

(39)

k-d drzewo R-drzewo

Indeksy przestrzenne

(40)

k-d drzewo R-drzewo

k-d drzewo

• Proste w implementacji

• Kolejne podziały są dokonywane wg kolejnych współrzędnych

• Może być mocno niezbalanasowane

Źródło obrazków: en.wikipedia.org

(41)

k-d drzewo R-drzewo

R-drzewo

• Zawsze zbalansowane

• Dodawanie nowego punktu wg zasady najmniejszego powiększania pola prostokątów

• W większych wymiarach wzajemne pokrywanie się hiperkostek psuje jego wydajność

Źródło obrazków: en.wikipedia.org

(42)

k-d drzewo R-drzewo

B-drzewo / B+drzewo

• Uporządkowane elementy na stronach (węzłach)

• Dostosowane do przechowywania na dysku

• Wartości w liściach (B+drzewo)

(43)

k-d drzewo R-drzewo

Operacje na B-drzewie (M = 2) I

Źródło obrazków: de.wikipedia.org

(44)

k-d drzewo R-drzewo

Operacje na B-drzewie (M = 2) II

(45)

k-d drzewo R-drzewo

Cechy R-drzew

• Dostosowane do przechowywania na dysku

• Na stronach przechowuje nieuporządkowane hiperprostokąty ograniczające obiekty przestrzenne

• Wartości w liściach

(46)

k-d drzewo R-drzewo

Operacje na R-drzewie I

Lista operacji

• Wyszukiwanie obiektów przecinających się

• Wstawienie nowego elementu

• Przebudowanie drzewa

Wyszukiwanie obiektów przecinających się

Na każdym poziomie odrzucanie z dalszego przeszukiwania tych hiperprostokątów, które nie przecinają się z hiperprostokątem obiektu wyszukującego

(47)

k-d drzewo R-drzewo

Operacje na R-drzewie II

Wstawienie nowego elementu

Na każdym poziomie nie będącym liściem odnajdujemy

hiperprostopadłościan, którego miara powiększy się jak najmniej po dodaniu do niego nowego elementu.

Przebudowanie drzewa

Ze względu na nieuporządkowanie elementów na stronie jest to potencjalnie najbardziej kosztowna operacja (aczkolwiek

wykonywana rzadko: tylko przy przepełnieniu bądź niedopełnieniu strony).

(48)

k-d drzewo R-drzewo

Podział strony I

Sprawdzenie wszystkich możliwych podziałów

Sprawdzenie wszystkich możliwych podziałów hiperprostokątów na dwa zbiory. Wybór takich, których suma miar hiperprostokątów ograniczających będzie najmniejsza.

Wykładnicza złożoność względem rozmiaru strony.

Wybór najlepszej pary

Sprawdzenie wszystkich par hiperprostokątów. Wybór takich, których miara hiperprostokąta ograniczającego pomnieszona o sumę miar hiperprostokątów wejściowych jest największa.

Przypisanie kolejnych hiperprostokątów wg reguły najmniejszego wzrostu miary.

Kwadratowa złożoność względem rozmiaru strony.

(49)

k-d drzewo R-drzewo

Podział strony II

Uproszczony wybór najlepszej pary

Dla każdego z wymiarów znalezienie takiej pary hiperprostokątów, która jest od siebie względnie najdalej. Wybór tej pary jako pary inicjującej podział (analogicznie jak wcześniej).

Liniowa złożoność względem rozmiaru strony.

(50)

k-d drzewo R-drzewo

Wykorzystanie indeksu w PostgreSQL

Przykład: 100 000 losowych czworokątów.

(51)

k-d drzewo R-drzewo

Wykorzystanie indeksu w PostgreSQL I

Utworzenie tabeli

CREATE TABLE data.wielokaty (

id serial primary key, poly geometry(Polygon) );

Utworzenie indeksu

CREATE INDEX wielokaty gix

ON data.wielokaty USING GIST (poly);

VACUUM ANALYZE data.wielokaty;

(52)

k-d drzewo R-drzewo

Wykorzystanie indeksu w PostgreSQL II

Zapytanie wykorzystujące indeks SELECT wiel1.id

FROM data.wielokaty wiel1, data.wielokaty wiel2 WHERE

wiel1.poly && wiel2.poly AND

ST INTERSECTS(wiel1.poly, wiel2.poly) AND wiel2.id = 300403