I have a table of polygons (census block groups) in Postgres. I want to tag each block group with the town (another polygon table) it mainly resides within. Is this possible?
I'm thinking I'd need to essentially create something like:
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
but this query is taking forever (I've got about 5,000 block groups and 375 towns...). Any suggestions on how to make this query work either at all if it's incorrect, or faster if it is correct?