obliczanie procentowej powierzchni przecięcia w klauzuli where

Mam tabelę wielokątów (grupy bloków spisowych) w postgresie. Chcę oznaczyć każdą grupę bloków miastem (inną tabelą wielokątów), w której głównie się znajduje. czy to możliwe? Myślę, że muszę zasadniczo stworzyć coś takiego:

select b.*,t.name  
from blockgroups b, towns t  
where (st_area(st_intersection(b.wkb_geometry, t.wkb_geometry))/st_area(b.wkb_geometry)) > .5  

ale to zapytanie trwa wiecznie (mam około 5000 grup bloków i 375 miast ...). Wszelkie sugestie, jak sprawić, by to zapytanie działało w ogóle, jeśli jest niepoprawne, lub szybciej, jeśli jest poprawne?

Sposób, w jaki to robisz, będzie działał, ale zajmie to zbyt dużo czasu, ponieważ postgis próbuje stworzyć geometrię przecięcia każdej kombinacji „grupy blokowej z miastem”, nawet jeśli nawet się nie dotykają.

Dodaj kolejny warunek do klauzuli WHERE, aby sprawdzić, czy dwie geometrie przechwytują i umieść go przed istniejącą:

select b.*,t.name
from blockgroups b, towns t
where st_intersects(b.wkb_geometry, t.wkb_geometry) and    
    (st_area(st_intersection(b.wkb_geometry, t.wkb_geometry))/st_area(b.wkb_geometry)) > .5

W SQL, jeśli masz listę warunków w klauzuli WHERE, są one testowane według kolejności, w jakiej zostały zapisane. Jeśli FAŁSZ zostanie zwrócony w jednej z wczesnych operacji, zapytanie po prostu pominie sprawdzanie pozostałych warunków, ponieważ wynik będzie zawsze FAŁSZ.

Upewnij się także, że masz indeksy przestrzenne na blockgroups.wkb_geometry i towns.wkb_geometry.

Dodając do bardzo przydatnej odpowiedzi Alexandre'a, jeśli niektóre z twoich jednostek spisowych mogą obejmować trzy z twoich miast (a zatem nie możesz zagwarantować więcej niż 50% upadków w żadnym mieście), możesz to zrobić:

select distinct on (b.id)
b.*,t.name,
(st_area(st_intersection(b.wkb_geometry, t.wkb_geometry))/st_area(b.wkb_geometry)) as proportion
from blockgroups b, towns t
where st_intersects(b.wkb_geometry, t.wkb_geometry) 
order by b.id, proportion desc;

To w zasadzie chroni przed następującą sytuacją - w której obszary w kolorze niebieskim zniknęłyby:

Za pomocą operatora st_intersects i && możesz użyć tego: Calcolo_perc_intersez_postgis