5

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

  • 1
    I'd build the intersection, then remove those points from the total. Alternatively, build a composite of all the circles, then do the operation. You can't do it one-by-one because while a point is in one circle, it is not intersecting most of the other circles. – BradHards Sep 10 '16 at 4:09
5

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.

  • Perfect, thanks very much. I hadn't though about the union of the buffers and hadn't experienced a case statement before. Thanks for the help and the lesson. – hselbie Sep 12 '16 at 16:03
  • Any thoughts on the use of ST_Disjoint as suggested (and discussed) here: gis.stackexchange.com/questions/210387/… – DPSSpatial Sep 16 '16 at 15:55

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.