3

I have two tables, A and B.

I want to Union(/Dissolve) B and then difference A,B. To get whatever is left of A when overlapping B is erased:

select a.someid, st_difference(a.wkb_geometry,st_union(b.wkb_geometry))
from a, b
where b.group_id=43 and b.type_id=12 and st_intersects(a.wkb_geometry, b.wkb_geometry)

I get an error on line 1:

ERROR: column "A.wkb_geometry" must appear in the GROUP BY clause or be used in an aggregate function

How to fix this? Without st_union I sometimes get duplicates a's (on id, not geometry) in the output, which I don't want. Maybe it is better to Union the output?

  • 1
    what happens when you add A "GROUP BY" clause? DId you try to use SELECT DISTINCT? – LaughU Oct 18 '18 at 11:17
  • I only tried to group by geometry, but when i also group by id it worked – BERA Oct 18 '18 at 11:21
  • actually I'd say union both inputs (with a grouping based on id?); as you do it, you recreate the union of b for each row in a. also, an index doesn't help much here anyways. – geozelot Oct 18 '18 at 11:49
  • @ThingumaBob How can I modify to only union b once? – BERA Oct 18 '18 at 11:59
  • move the union with filter into a CTE, at least for the 'blade' table and see if any index does help with smaller geometries in the other table. I guess that, if most or all geometries in the subset of that other table will be cut an index is rather useless, and so separately union both with necessary filters would be even simpler. attribute handling would be worse that way, though. – geozelot Oct 18 '18 at 21:12
4

I needed to group by both id and geometry, I had only tried to group by one of them.

select a.someid, st_difference(a.wkb_geometry,st_union(b.wkb_geometry))
from a, b
where b.group_id=43 and b.type_id=12 and st_intersects(a.wkb_geometry, b.wkb_geometry)
group by a.someid, a.wkb_geometry
| improve this answer | |
3

tl;dr: Try the query under However first. This will, if even, only make sense if the blade geometry doesn't need dissolving and ST_Union can be substituted with ST_Collect.


Following my comments:

What I was refering to is to 'outsource' the union into a CTE, that is, a WITH clause, like so:

WITH
  blade AS (
    SELECT ST_Collect(wkb_geometry) AS geom
    FROM b
    WHERE group_id = 43 and type_id = 12
  )

SELECT a.someid,
       ST_Difference(a.wkb_geometry, b.geom) AS geom
FROM a
JOIN blade AS b
  ON ST_Intersects(a.wkb_geometry, b.geom);

A CTE will be executed once for each transaction, and can be seen as a temp table dropped on commit; this helps avoiding creating sets of rows over and over, and it looks better.

I used ST_Collect instead of ST_Union; in most cases, this should return the same MULTI geometry while being orders of magnitude faster.

I also used a (INNER) JOIN instead of a WHERE filter; this is merely for readability, in most cases.

Note also that there are no (possibly costly) GROUP BY operations.


However:

The main motor for performance, and actually the guy doing your job, is the Query Planner; the Planner will highly optimize your query by flattening, replacing, moving and even not executing parts of it. And it is mostly right.

Its efficiency is mainly bound to up-to-date table stats; you can (and should, e.g. after creating indexes or UPDATE/INSERT/DELETE operations) update those manually by executing VACUUM ANALYZE/FULL on one or all tables.

Now, the key point; there are queries the planner cannot optimize, by design or logic, and CTEs are one of those (functions are another, so e.g. the union in your difference function cannot be optimized even if possible).

In your case, union within an actual subquery can thus be beneficial (and the better choice):

SELECT a.someid,
       ST_Difference(a.wkb_geometry, b.geom) AS geom
FROM a
JOIN (
   SELECT ST_Collect(wkb_geometry) AS geom
   FROM b
   WHERE group_id = 43 and type_id = 12
) AS b
ON ST_Intersects(a.wkb_geometry, b.geom);

Notes on index and performance:

In general, running geometric relation and/or manipulation queries on large and/or MULTI geometries perform worse than on many small single geometries; for each input point set, every vertex of the large geometry has to be traversed and checked.

Since a spatial index works with bbox trees, filtering (non-)relation is blazingly fast for small geometries by simply comparing only four vertices in a tree branch, and only do the geometric intersection test on a few chosen ones; having a very large bbox however will most likely include many or all compared others, even if the geometries themselves don't intersect geographically. PostGIS will then still have to do the costly intersection filter on too many geometries, defeating the purpose of an index.

That being said, ST_Difference is tricky, since it best works with the blade being a unified geometry (to avoid cross product slicing of the same geometry, resulting in duplicates). A union from indexed geometries will render the combined gemetry non-SARGable, that is, it cannot have or utilize an index.

But it is possible that, when working with a subset where each geometry is guaranteed to intersect with the blade, a union of those will perform equally or even faster, as there is no (in this case unnecessary) index lookup first, and having that second large MULTI geometry then in memory already.

Considering this, running:

SELECT ST_Difference(a.geom, b.geom) AS geom
FROM (
  SELECT ST_Collect(wkb_geometry) AS geom
  FROM a
  WHERE <column_filter_condition>
) AS a
CROSS JOIN (
  SELECT ST_Collect(wkb_geometry) AS geom
  FROM b
  WHERE group_id = 43 and type_id = 12
) AS b;

might even be faster, if you can filter a in advance (without checking for intersection) into an overlapping subset.


This quickly got out of hand in scope, not sure if this will even help. But since you asked...

Could you reply if any of the above did perform better? ...or failed to work?

| improve this answer | |
  • 1
    I wrote this form the top of my head...I'll try to add references for some key points ASAP. – geozelot Oct 19 '18 at 10:23
  • 1
    updated with some links – geozelot Oct 19 '18 at 11:24
  • Thank you! I cant get it to work though. Im getting topology error "TopologyException: side location conflict". Pretty sure it is the ST_Collect, i have checked topology for the others. I try changing it to ST_Union which makes it run but not complete (at least for about 5 min when i abort). – BERA Oct 20 '18 at 6:36
  • 1
    @BERA yes it's likely the ST_Collect since it will not dissolve overlaps and such...what I proposed above will be a waste of time if run with ST_Union, though, so better stay with your query. you can try if a.wkb_geometry && b.wkb_geometry gets you a little boost instead of a true intersection filter...but likely only if the resulting geometry count will not be doubled than with ST_Intersects, as it then would again increase union time. – geozelot Oct 20 '18 at 11:23

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.