3

I have two tables: table1 and table2, I would like to join polygons from table1 (purple) to table2 (yellow) if the polygons in table1 don't intersect or are coveredby table 2 polygons. I would like to keep the yellow polygons in table2 even if they don't intersect or are coveredby.

The code below is really slow, but table1 has 490k polygons and table2 has 913k polygons. I have GIST index on both tables.

The image shows both table1 and table2. The result would have no purple squiggles covering the yellow polygons, but would be present alone and the yellow polygons.

enter image description here

CREATE TABLE combined AS
SELECT
    COALESCE(table1.geom, table2.geom) AS combined_data
FROM
    table1
LEFT JOIN
    table2 ON NOT ST_Intersects(table1.geom, table2.geom) AND NOT ST_CoveredBy(table2.geom, table1.geom);

1 Answer 1

5

Not 100% sure this is what you want, but generally non-intersections translate to anti-joins in SQL; there are two common syntaxes to avoid a Cartesian product, of which I would always prefer the (NOT) EXISTS sub-query (the other being a LEFT JOIN with a NULL filter).

It seems very unnecessary to also check for ST_CoveredBy in conjunction with ST_Intersects.

You then probably want a UNION of both tables?

Try:

CREATE TABLE combined AS
  SELECT
    t.<id>,
    t.geom
  FROM
    <table1> AS t
  WHERE
    NOT EXISTS (
      SELECT
        1
      FROM
        <table2> AS _t
      WHERE
        ST_Intersects(t.geom, _t.geom)
    )
  UNION
  SELECT
    t.<id>,
    t.geom
  FROM
    <table2> AS t
;
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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