13

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. – dbaston May 28 '14 at 20:02
18

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 spacial indexes on blockgroups.wkb_geometry and towns.wkb_geometry.

  • 1
    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. – dbaston May 28 '14 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. – Alexandre Neto May 28 '14 at 21:35
9

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: enter image description here

  • 1
    I absolutely adore it when the very first issue I encounter with a SO answer is solved by the very next answer. Cheers, @RobinL! – wfgeo Nov 21 '18 at 8:54
-1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.