11

What is the right way to calculate the difference between two layers? I tried to use the following approach:

SELECT ST_Difference(river.geom, lakes.geom) 
FROM river LEFT JOIN lakes ON ST_Intersects(river.geom, lakes.geom)

But in the output, I lose the geometries of the river layer which do not intersect any geometries in lakes. It looks like left join does not work as expected.

Currently, I'm using another approach, but I'm not sure this is correct:

SELECT ST_Difference(river.geom, lakes.geom) 
FROM river JOIN lakes ON ST_Intersects(river.geom, lakes.geom) 
UNION 
SELECT river.geom 
FROM river JOIN lakes ON NOT ST_Intersects(river.geom, lakes.geom)
jpmc26
  • 1,709
  • 1
  • 13
  • 30
drnextgis
  • 6,938
  • 2
  • 29
  • 49

2 Answers2

17

Just do this:

SELECT COALESCE(ST_Difference(river.geom, lakes.geom), river.geom) As river_geom 
FROM river LEFT JOIN lakes ON ST_Intersects(river.geom, lakes.geom);

reverse

SELECT COALESCE(ST_Difference(river.geom, lakes.geom), lakes.geom) As lake_geom 
FROM lakes LEFT JOIN river ON ST_Intersects(river.geom, lakes.geom);

That is what COALESCE exists for. I much prefer keeping semantics of PostGIS the way they are. It's consistent with relational DB accepted technology and if we make consessions for this, we need to do it for all functions and then the results will be unpredictable.

RyanKDalton
  • 23,068
  • 17
  • 110
  • 178
Regina Obe
  • 10,503
  • 1
  • 23
  • 28
  • I don't think stackexchange is taking my edits. What I meant to say was COALESCE(ST_Difference(river.geom, lakes.geom), river.geom) As river_geom ... river LEFT JOIN lakes. But hopefully you get the idea – Regina Obe Jun 30 '11 at 02:22
7

The problem here is not the left join, that is working as expected. But when the query gets to a river that doesn't intersect with a lake it will feed the ST_Difference function with NULL as the second argument which seems to return null.

/Nicklas

Nicklas Avén
  • 13,241
  • 1
  • 39
  • 48
  • 2
    I think returning NULL is the right thing to do? As Paul mentioned -- it should behave like btrim etc. If you wanted to just return the first argument when no match you would use COALESCE. The join wouldn't return the lake if the difference would be empty. changing this behavior would break a lot of code -- as people relying on this behavior would then have to worry about order of arguments and so forth. e.g. COALESCE(ST_Difference..., river.geom, the_world); COALESCE allows you to apply an indefinite number of arguments and is the standard way database programmers deal with NULL. – Regina Obe Jun 30 '11 at 04:44
  • Yes, You are right, of course :-) Haven't thought about it before, why the join must return an undefined value instead of a empty or nothing when there is no match. – Nicklas Avén Jun 30 '11 at 05:32