1

I am very new to PostGIS2.4 and Postgresql 10 and having hard times to make holes inside a polygon using another polygon from a single table liveuamap by using ST_SymDifference.

the polygon in the middle should be cleared

The polygon in the middle should be cleared.

I searched a lot for an answer but nothing could serve in my situation for CARTO. I used the following query :

SELECT a.cartodb_id, b.cartodb_id,
    CAST (ST_AsText (ST_SymDifference (a.the_geom, b.the_geom) )
    AS VARCHAR(350) ) SYM_DIFF
  FROM liveuamap a, liveuamap b
  WHERE a.cartodb_id = 105 AND b.cartodb_id = 116
  • Welcome to GIS SE. As a new user, please take the Tour. Please Edit the question to provide the exact versions of PostgreSQL and PostGIS in use, and provide more information about the exact query you used. – Vince Jul 1 '18 at 12:12
  • I'm not sure what ST_DymDifference will do in this case - it's supposed to find that portion of both polygons, that do not intersect, which, in theory, could also work here - but you should definetely try `ST_Difference(<large_popy>, <small_poly>)' instead. – ThingumaBob Jul 2 '18 at 6:56
2

I have never used ST_SymDifference but (correct me if I am wrong) you can achieve the same result with ST_Difference. But before applying any of these functions, you should bear in mind that any SQL query inside BUILDER should select (a unique) cartodb_id, the_geom and the_geom_webmercator.

Check this resource to see how you can clip or create donnuts in CARTO. Again, notice that you will need to add the_geom to your query. So your query should be something like this:

WITH clip AS (
    SELECT
      1 as cartodb_id,
      a.cartodb_id as id_1,
      b.cartodb_id as id_2,
      ST_Difference(
        a.the_geom,
        b.the_geom
      ) as the_geom
    FROM
      table a,
      table b
    WHERE
      b.cartodb_id = {cartodb_id}
    AND
      a.cartodb_id = {cartodb_id})

SELECT 
    *, 
    ST_Transform(the_geom, 3857) as the_geom_webmercator
FROM
    clip

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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