ST_DWithin był szybszy w moim teście niż ST_Intersects. Jest to zaskakujące, zwłaszcza że przygotowany algorytm geometrii powinien uruchamiać takie przypadki. Myślę, że jest szansa, że będzie to o wiele szybsze, niż pokazałem tutaj.
Zrobiłem kilka testów i dwie rzeczy prawie 10-krotnie podwoiły prędkość. Najpierw próbowałem na nowszym komputerze, ale wciąż dość zwyczajnym laptopie, może poza dyskami SSD SATA3.
Poniższe zapytanie zajęło 18 sekund zamiast 62 sekund na starym laptopie. Następnie odkryłem, że całkowicie się myliłem, kiedy pisałem, że indeks w tabeli punktów nie jest konieczny. Z tym indeksem ST_Intersects zachowywał się zgodnie z oczekiwaniami i wszystko stało się bardzo szybkie. Zwiększyłem liczbę punktów w tabeli punktów do 1 miliona punktów i zapytanie:
CREATE TABLE points_ct AS
SELECT imported_ct.gid as ct_gid, t.gid as point_id
FROM imported_ct , t WHERE ST_Intersects(imported_ct.geom , t.geom);
działa w ciągu 72 sekund. Ponieważ istnieje 1249 wielokątów, 1249000000 testów wykonuje się w 72 sekundy. To daje około 17000000 testów na sekundę. Lub testowanie prawie 14000 punktów w stosunku do wszystkich wielokątów na sekundę.
Z tego testu twoje 400000000 punktów do przetestowania powinno zająć około 8 godzin bez żadnego problemu z rozłożeniem obciążenia na kilka rdzeni. PostGIS nigdy nie przestaje mnie imponować :-)
Po pierwsze, aby zwizualizować wynik, możesz dodać geometrię punktów do tabeli wynikowej, na przykład otworzyć ją w QGIS i nadać jej unikalne wartości w polu import_ct.
Po drugie, tak, możesz również uzyskać punkty wypadające poza dowolnym wielokątem, używając połączenia prawego (lub lewego) w następujący sposób:
CREATE TABLE points_ct AS
SELECT imported_ct.gid as ct_gid, t.gid as point_id
FROM imported_ct right join t ON ST_Intersects(imported_ct.the_geom , t.geom);
Zrobiłem kilka testów, aby sprawdzić, czy wydaje się to możliwe PostGIS.
Po pierwsze coś, czego nie rozumiem. Masz dwa punkty na rząd. Czy oba punkty są zawsze w tym samym wielokącie? Następnie wystarczy wykonać obliczenia na jednym z punktów. Jeśli mogą znajdować się w dwóch różnych wielokątach, potrzebny będzie sposób połączenia jednego rzędu punktów z dwoma wielokątami.
Z testów wydaje się to wykonalne, ale możesz potrzebować kreatywnego rozwiązania, aby rozłożyć obciążenie na więcej niż jeden rdzeń procesora.
Testowałem na 4-letnim laptopie z dwurdzeniowym procesorem centrino (chyba około 2,2 GHz), 2 GB pamięci RAM. Jeśli masz 48 BG RAM, myślę, że masz też o wiele więcej mocy procesora.
Stworzyłem losową tabelę punktów z 100 000 punktami:
CREATE TABLE t AS
WITH r AS
(SELECT ST_Extent(the_geom)::geometry ext FROM imported_ct)
SELECT ST_Point(x,y) AS geom FROM
(SELECT GENERATE_SERIES(1,100000)) s,
(SELECT ST_Xmin(ext)+(random()*(ST_Xmax(ext)-ST_Xmin(ext))) x, ST_Ymin(ext)+(random()*(ST_Ymax(ext)-ST_Ymin(ext))) y FROM r
) f;
Następnie dodaj gid jak:
ALTER TABLE t ADD COLUMN GID SERIAL;
Następnie uruchom:
CREATE TABLE points_ct AS
SELECT imported_ct.gid as ct_gid, t.gid as point_id FROM imported_ct , t WHERE ST_Dwithin(imported_ct.the_geom , t.geom,0);
zajmuje około 62 sekund (porównaj z wynikiem ArcGIS z taką samą ilością punktów). Rezultatem jest tabela łącząca punkty w mojej tabeli t z gid w tabeli z obszarem spisu.
Przy tej prędkości osiągniesz 200 punktów w ciągu około 34 godzin. Tak więc, jeśli wystarczy sprawdzić jeden punkt, mój stary laptop może to zrobić z jednym rdzeniem.
Ale jeśli musisz sprawdzić oba punkty, może być trudniej.
Następnie można ręcznie rozłożyć obciążenie na więcej niż jeden rdzeń, uruchamiając wiele sesji z bazą danych i uruchamiając różne zapytania.
W moim przykładzie z 50000 punktami i dwoma rdzeniami procesora próbowałem:
CREATE TABLE t1 as
SELECT imported_ct.gid as ct_gid, t.gid as point_id FROM imported_ct , t WHERE t.gid >50000 and ST_Dwithin(imported_ct.the_geom , t.geom,0);
na jednej sesji db w tym samym czasie, co uruchomienie:
CREATE TABLE t2 as
SELECT imported_ct.gid as ct_gid, t.gid as point_id FROM imported_ct , t WHERE t.gid <=50000 and ST_Dwithin(imported_ct.the_geom , t.geom,0);
w innej sesji db.
Zajęło to około 36 sekund, więc jest nieco wolniejsze niż pierwszy przykład, prawdopodobnie w zależności od zapisu na płycie w tym samym czasie. Ale ponieważ rdzenie rdzeniowe działają w tym samym czasie, nie zajęło mi to więcej niż 36 sekund.
Aby połączyć tabelę t1 i t2, spróbuj:
CREATE TABLE t3 AS
SELECT * FROM t1
UNION ALL
SELECT * FROM t2;
używając około pół sekundy.
Tak więc przy świeższym sprzęcie i rozkładzie obciążenia na wiele rdzeni powinno to być absolutnie możliwe, nawet jeśli rzeczywisty świat będzie wolniejszy niż przypadek testowy.
Warto zauważyć, że przykład pochodzi z systemu Linux (Ubuntu). Korzystanie z systemu Windows będzie inną historią. Ale mam wszystkie inne codzienne aplikacje, więc laptop jest dość mocno obciążony. To może symulować obudowę systemu Windows całkiem dobrze, nie otwierając niczego poza pgadmin.