obliczanie procentowej powierzchni przecięcia w klauzuli where


15

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?


Czy chcesz oznaczyć grupy bloków na podstawie maksymalnego nakładania się? Jeśli tak, zobacz tę odpowiedź . Jeśli twoje „miasta” to także geografia spisu powszechnego (powiedzmy MCD lub miejsca), prawdopodobnie nie ma potrzeby obliczania procentu nakładania się.
dbaston

Odpowiedzi:


23

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.


1
Dodawanie ST_Intersectsjest właściwą drogą, aby przejść tutaj, ale planista może wykonać warunki w kolejności, w jakiej zostały zapisane. Szczegółowe informacje na ten temat można znaleźć w dokumentacji Postgres . ST_Intersectsi ST_Intersectionmam taki sam koszt mojej instalacji (100), więc szczerze mówiąc, nie jestem pewien, co robi planista, ale zawsze wydaje się, że robi to dobrze.
dbaston

Ahh ... Zakładałem, że warunki będą sprawdzane jak w innych językach. Ale myślę, że daje to planistom inną opcję.
Alexandre Neto

10

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: wprowadź opis zdjęcia tutaj


1
Uwielbiam to, gdy pierwszy problem, na który napotykam odpowiedź SO, jest rozwiązany przez następną odpowiedź. Pozdrawiam, @RobinL!
wfgeo,

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.