# 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?

• Sounds like you want to tag the block groups based on maximum overlap? If so, see this answer. If your 'towns' are also Census geographies (MCDs or Places, say) there is probably no need to calculate the overlap percent. May 28, 2014 at 20:02

The way you are doing it will work but it will take too much time, since postgis is trying to create the geometry of the intersection of every "blockgroup vs town" combination, even when they don't even touch.

Add another condition check to your `WHERE` clause to check if the two geometries intercept, and put it before the existing one:

``````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
``````

In SQL if you have a list of conditions in the WHERE clause, they are tested by the order they are writen. If a FALSE is returned in one of early operations the querie will just skip the others conditions checking, as the result will be always FALSE.

Also, make sure you have spatial indexes on `blockgroups.wkb_geometry` and `towns.wkb_geometry`.

• Adding `ST_Intersects` is the right way to go here, but the planner may or may not execute the conditions in the order they're written. See the Postgres docs for details on this. `ST_Intersects` and `ST_Intersection` have the same cost on my install (100), so to be honest I'm not sure what the planner is doing, but it always seems to do the right thing here. May 28, 2014 at 19:57
• Ahh... I did assume the that conditions would be checked like in other languages. But I guess it give the planner another option. May 28, 2014 at 21:35

Adding to Alexandre's very useful answer, if some of your census units may span three of your towns (and therefore you can't guarantee more than 50% falls in any town) you can do this:

``````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;
``````

This basically protects against the following situation - in which the areas in blue would disappear: • I absolutely adore it when the very first issue I encounter with a SO answer is solved by the very next answer. Cheers, @RobinL! Nov 21, 2018 at 8:54

With st_intersects and && operator, you can use this: Calcolo_perc_intersez_postgis