Jak przyspieszyć zapytania dotyczące baz danych rastrowych?

Mam bazę danych rastrowych w postgresql / postgis z tymi kolumnami:

(ID, rast, data_of_data) .

„rast” to kolumna zawierająca pliki rastrowe w formacie WKT. Przykładowe zapytanie dotyczące znalezienia wartości DN punktu w systemie WGS84 (30.424, -1.66) i dla 2002-01-09 jest następujące:

SELECT 
     st_value(rast,(st_GeomFromText('POINT(30.424 -1.66)', 4326))) as val
FROM 
     my_table
WHERE
     date_of_data='2002-01-09'

Czy istnieje metoda (np. Indeks przestrzenny) w celu przyspieszenia tego rodzaju zapytań?

— f.ashouri
źródło

Być może mógłbyś nam pomóc, podając więcej szczegółów: Ile rekordów znajduje się w mojej tabeli? Jak duże są dane w kolumnie rastrowej? Ile różnych dat masz w date_of_data?

— dwurf

Dodaj do tego: jaki jest SRID kolumny rastowej?

— dwurf

Odpowiedzi:

To ekscytujące pytanie! Jak duży jest raster, którego chcesz zapytać? WKTRaster jest przechowywany w bazie danych jako BLOB . Aby znaleźć wartość w określonym punkcie, ze znanego (x_0, y_0) narożnego współrzędnego indeksy / kolumny (i, j) oblicza się za pomocą kroków (dx, dy) i obrotu. Przy znanym (i, j) funkcja ST_Value () może uzyskać dostęp do rzeczywistych danych z prawidłowym przesunięciem bajtu.

Oznacza to, że DB musi odczytać średnio co najmniej połowę obiektu blob danych podczas odpowiadania na zapytanie o punkt (w zależności od implementacji może faktycznie odczytać wszystkie dane przez cały czas). Chciałbym zatem odgadnąć , która cierpi z wydajnością WKTRaster gdy BLOB danych zbyt duże. Układanie zestawu danych powinno przyspieszyć zapytania. Zobacz, jak obsługiwane są dane SRTM (pochodzące z porcji 6000 x 6000 pikseli) w tym samouczku . Faktycznie dzielą dane na naprawdę małe 50 x 50 pikseli, co jest wyraźną wskazówką, że moje domysły mogą nie być zbyt dalekie od prawdy.

Przestrzenne indeksowanie danych rastrowych prawdopodobnie po prostu zindeksuje obwiednię, co nie jest prawdziwą pomocą dla twojego problemu.

— bhell
źródło

Płytka wydaje się być właściwą drogą - zobacz ten link . Musisz także dodać taki indeks: CREATE INDEX srtm_tiled_rast_gist_idx ON srtm_tiled USING GIST (ST_ConvexHull(rast));( źródło )

— dwurf

Dwa aspekty, które znalazłem, przyspieszyły moje obliczenia rastrowe PostGIS, wykorzystywały wartości całkowite w rastrze i korzystały z rastrów wielopasmowych tam, gdzie to możliwe. W takim przypadku, czy wartość DN można zapisać jako liczby całkowite, jeśli jeszcze tego nie zrobiono?

Inną myślą (i nie jestem pewien, czy ma to znaczenie w tym przypadku) jest użycie rastrów wielopasmowych. Na przykład, jeśli przeglądasz miesięczne wycinki danych, każdy miesiąc może być warstwą rastrową. Następnie możesz pobrać wiele wartości punktu w różnych przedziałach czasowych, sprawdzając raster warstwowy. Przekonałem się, że takie podejście jest znacznie szybsze niż sprawdzanie osobnych rastrów.

Wreszcie, gdy ładujesz swoje dane, pojawia się -tflaga dla TILE_SIZE . Możesz sprawdzić, czy rozmiar kafelka, którego używasz, działa dobrze w zapytaniu.

— djq
źródło

Rastry wielopasmowe najprawdopodobniej pomogą, jeśli będziesz musiał sprawdzać wartość tego samego piksela przez kilka miesięcy w tym samym czasie (aby trzymać się przykładu), np. W celu analizy szeregów czasowych. Zapytanie w pytaniu pobiera tylko jedną określoną datę. Gdyby data była zawarta w jednym paśmie, DBMS musiałby również odczytać wszystkie inne pasma, nawet jeśli nie są one zainteresowane odpowiedzią na zapytanie. Prawdopodobnie pogorszyłoby to wydajność.

— bhel

Zgadzam się - być może nie podkreśliłem, że jest to przydatne tylko wtedy, gdy potrzebnych jest kilka wartości jednocześnie; Wyjaśnię to.

— djq,

W zależności od dystrybucji danych możesz uzyskać bardzo dobre przyspieszenie, po prostu indeksując date_of_data kolumnę.

Możesz użyć składni EXPLAIN ANALYZE, aby dowiedzieć się, czy indeksy są używane, czy nie.

— dwurf
źródło

jaki rodzaj indeksu? możesz być bardziej dokładny?

— f.ashouri,

Wystarczy standardowy wskaźnik btree: create index tbl_name_date_idx on tbl_name (date_of_data). Jeśli masz wiele różnych dat, to drastycznie zmniejszy ilość danych, które PostGIS musi przetworzyć.

— dwurf

Dziękuję, ale to nie zadziałało w przypadku mojego zapytania.

— f.ashouri,

Jak to nie zadziałało? Brak zauważalnego wzrostu wydajności lub inne problemy? Jeśli masz kolumnę tabeli, która regularnie pojawia się w WHEREklauzuli, zawsze powinieneś rozważyć jej zaindeksowanie. Pomoże to nie tylko w tym przypadku, jeśli masz wiele różnych dat (tj. Domena o dużej wartości), ale także, jeśli masz dużą liczbę rekordów w tabeli.

— bhell

Czy zapytanie korzysta z indeksu? Czy możesz wkleić dane wyjściowe

explain analyze SELECT st_value(rast,(st_GeomFromText('POINT(30.424 -1.66)', 4326))) as val from my_table where date_of_data='2002-01-09'

— dwurf