[GIS] Opposite of ST_intersection

postgispostgresql

I am trying to clip a series of lines selecting only the geometry portion outside of a polygon using PostGIS.

From my reading on what st_intersection does i.e. Returns a geometry that represents the shared portion of geomA and geomB. I'd like to do the opposite of this so return the geometry that isn't shared by geomA and geomB.

I've been trying to work with st_difference which should return a geometry that represents that part of geometry A that does not intersect with geometry B. But i'm having some trouble getting the result I want. This is what i've written so far…

SELECT
    t.id, ST_Difference(t.the_geom_webmercator, t1.dock) as intersection
FROM
    trips t, 
    (select
        d.id, d.name,ST_Buffer(ST_Centroid(the_geom_webmercator),1000) as dock
    from 
        docks d) as t1
WHERE 
    ST_Intersects(t.the_geom_webmercator,t1.dock)
    t.id IN (25558, 25555, 25553, 25533, 25517, 25523, 25501, 25506,25532,25439)
    and ST_Difference(t.the_geom_webmercator, t1.dock) is not null

This returns

  • the pink lines are the result of the above query, so some lines stop at the circle boundaries and others don't
  • the circles are the polygons I want to clip everything by,
  • the underlying point line is the result of basically the same query
    as above replacing st_intersection with st_difference which
    returns the inverse of the desired result i.e. the geometries within
    the circles.

Any suggestions on how to amend this query?
enter image description here

Best Answer

To get an "intuitive" difference is unfortunately a bit CPU intensive, but doable. The main insight is that you have to use only one differencer, otherwise you get a bunch of pairwise differences, which is not what you intuitively want.

WITH d AS (
    SELECT ST_Union(ST_Buffer(ST_Centroid(the_geom_webmercator),1000)) AS the_geom_webmercator
    FROM docks
)
SELECT
    CASE WHEN ST_Intersects(d.the_geom_webmercator, t.the_geom_webmercator)
    THEN ST_Difference(t.the_geom_webmercator, d.the_geom_webmercator)
    ELSE t.the_geom_webmercator
    END AS the_geom_webmercator
FROM d, trips t
WHERE t.id IN (25558, 25555, 25553, 25533, 25517, 25523, 25501, 25506,25532,25439)

(As a side note, a 1000 meter buffer of an object in web mercator is not a true 1km buffer anywhere but at the equator, so watch out that doesn't bit you.)

The above is a little brain-dead, and can be made more efficient (sometimes) for larger data sets by unioning the buffers into multiple differencers, one for each trip they are going to difference. The relative efficiency of the second approach depends on how many trips there are vs how many docks.

Related Question