I have two geojson files -
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).
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
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) )