Use area of intersection as a variable in PostGIS

Question

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
AS
  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))
    /ST_Area(t.geom)
  )

Answer 1

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
AS
  SELECT
    ST_Area(ST_Intersection(t.geom, m.geom)) / ST_Area(t.geom) * pop AS pop_adjusted
    t.*,
    m.*, 
  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.

Answer 2

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

SELECT t.*
       ,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)
GROUP BY t.id