I have two geojson files - NYCSample and censusTracts. Each census tract contains a population attribute (integer). My objective is to multiply the population attribute by the % area of intersection / total area of tract that NYCSample intersects. For example, we see that the sample layer intersects 6 census tracts. If each of the census tracts contain 100 people, then the output should be 100 * (% of intersection).

Using the st_area(st_intersection()) functions in PostGIS, how would one output a table with population adjusted for % of area intersected?

So far, I've written this query but am not sure how to create a separate pop_adjusted variable

CREATE TABLE test_join
  SELECT t.*, m.*
  FROM censusTracts AS t , NYCSample AS m
  WHERE ST_Intersects(t.geom, m.geom)
  AND (
    ST_Area(ST_Intersection(t.geom, m.geom))

enter image description here

  • 1
    you need to add the (st_area(st_intersection(t.geom, m.geom))/st_area(t.geom)) up in the select part of the query
    – ziggy
    May 16, 2017 at 19:29

2 Answers 2


I think you're confusing some stuff. You want all rows in which there is an intersection carried over. That means that only the call to ST_Intersects should be in the join-condition or where-clause.

I've modified the table to use explicit joins.

CREATE TABLE test_join
    ST_Area(ST_Intersection(t.geom, m.geom)) / ST_Area(t.geom) * pop AS pop_adjusted
  FROM censusTracts AS t
  JOIN NYCSample AS m
    ON ST_Intersects(t.geom, m.geom)

It's also somewhat worth nothing that this needn't be a seperate table. You can create a view (see CREATE VIEW) just the same way, and qgis should be able to display it for you.


The ST_Area in the WHERE clause is overcomplicated, it will evaluate to false only if the result of the division is zero st_area(st_intersection(t.geom, m.geom)) >0 would suffice. Also explicit join might be better

       ,m.population * SUM(st_area(st_intersection(t.geom, m.geom))/st_area(t.geom)) as pod_adjusted
FROM censusTracts AS t 
  JOIN NYCSample AS m
    ON st_intersects(t.geom, m.geom)
  • I don't understand what this clause >0 is supposed to do with ST_Intersection(t.geom,m.geom) > 0` May 16, 2017 at 21:58
  • I'm still not sure what that does. You're just writing another slower version of ST_Intersects that you already have. May 16, 2017 at 22:07
  • 1
    @EvanCarroll Well, I just simplified the original query. TBH I haven't used postgis in a bit and I'm not really sure if intersection always has an area greater than zero May 16, 2017 at 22:19
  • 1
    you're right to say that. ST_Intersects will return true if they ST_Touches. Though I would write that as not ST_Touches() or something more explicit. May 16, 2017 at 22:22
  • @EvanCarroll You may be right about the explicity, though ST_Touches requires another function call while this only reuses what's already there. Though with Sum() it doesn't really matter...I'll guess I'll remove it. May 16, 2017 at 22:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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