Saturday, 20 October 2018

postgis - calculating percent area of intersection in where clause


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?




No comments:

Post a Comment

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...