1

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

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 '17 at 19:29
3

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.

0

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
  • I don't understand what this clause >0 is supposed to do with ST_Intersection(t.geom,m.geom) > 0` – Evan Carroll May 16 '17 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. – Evan Carroll May 16 '17 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 – Jakub Kania May 16 '17 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. – Evan Carroll May 16 '17 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. – Jakub Kania May 16 '17 at 22:29

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.