Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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) 
share|improve this question
add comment

2 Answers 2

up vote 7 down vote accepted

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.

share|improve this answer
    
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 –  LR1234567 Jun 30 '11 at 2:22
add comment

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

share|improve this answer
1  
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. –  LR1234567 Jun 30 '11 at 4: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 5:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.