2

I want to join two layers, a polygon layer "plan" and a point layer "education point". My goal is to join the data from "education point" layer to the "plan" layer if the point is inside a polygon.

Since some polygons don't intersect with any point, and I still want to keep those polygons visible, I used left join. But the result only shows polygons intersecting with a point.

Here's my query:

SELECT pl.geometry,ed.*
FROM "plan" AS pl
LEFT JOIN "education point" AS 'ed' ON st_intersects(pl.geometry,ed.geometry)
WHERE pl."function" in ('primary school', 'middle school')
AND ed."studyArea"='yes' 
  • 3
    Your WHERE does not permit NULL ed values. – Vince Sep 25 at 4:00
  • 1
    Do you mean that the and will return false, thus giving no result, if any one of two layer is NULL? – David H. J. Sep 25 at 4:12
5

It's because of the last part of the where clause: and ed."studyArea"='yes'

Vince beat me to it.

As soon as you include WHERE criteria like that on the table on the right side of the join it sort of implicitly becomes an inner join. Think about it: if there is no education point to intersect with the plan object then the value of ed."studyArea" wont be 'yes', it will be NULL, which doesn't match your criteria. You can either change the last part so the query reads:

select pl.geometry,ed.*
from "plan" as pl
left JOIN "education point"as'ed' on st_intersects(pl.geometry,ed.geometry)
where pl."function" in ('primary school','middle school') and 
(ed."studyArea"='yes' or ed."studyArea" IS NULL)

Or you can move the filtering condition on the right table to the JOIN clause:

select pl.geometry,ed.*
from "plan" as pl
left JOIN "education point"as'ed' on st_intersects(pl.geometry,ed.geometry)
    AND ed."studyArea"='yes'
where pl."function" in ('primary school','middle school')

I like the second approach myself, it seems to better express your intent, which is to join the plan table to the subset of records in the education point table which have studyArea='yes'

| improve this answer | |
  • 1
    The second query is quite different. If there is a studyArea='no', the original query will return no record at all while the 2nd of your answer will still return the pl record – JGH Sep 25 at 13:38
  • Thanks, good point. – M Bain Sep 25 at 21:34

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.