5

I use the following SQL query to create a table that contains dissovled polygons of an existing table:

-- Schema: public
-- Table: t_union
-- Input table: input_table

CREATE TABLE public.t_union AS SELECT
    row_number() over() AS gid,
    sbqry.geom
FROM 
(SELECT
    (ST_Dump(ST_Union(geom))).geom::geometry(Polygon, /*SRID*/) AS geom
FROM public.input_table
) AS sbqry;

Input polygon features: enter image description here

Output polygon features: enter image description here

Is there a way to modify the polygons of the input table instead of creating a new table?

Edit 1:

Meanwhile, I've created a SQL query to modify the input table:

INSERT INTO public.input_table
SELECT
    row_number() over() + 100000 AS gid,
    sbqry.geom
FROM 
(SELECT
    (ST_Dump(ST_Union(geom))).geom::geometry(Polygon, /*SRID*/) AS geom
FROM public.input_table
) AS sbqry;

DELETE FROM public.input_table WHERE gid < 100000;
UPDATE public.input_table SET gid = gid - 100000;

row_number() over() + 100000 AS gid is a clumsy workaround to ignore the existing gid values and I don't know how to delete the input geometries in a simple way.

EDIT 2:

Perhaps this can be solved by using UPDATE and SET:

UPDATE public.input_table SET (geom) =
(SELECT COALESCE
    ((ST_Dump(ST_Union(geom))).geom::geometry(Polygon, /*SRID*/))
FROM public.input_table);

Unfortunately, an error occurs when executing the query:

set-valued function called in context that cannot accept a set
3
  • How are the output features and the source features related - by and ID or some sort? Jan 26, 2017 at 19:54
  • 1
    I've uploaded two sceenshots to illustrate the question. The numbering of the polyongs doesn't matter as long as the values are unique. Jan 27, 2017 at 8:20
  • How would you handle deleting some polygons from the original table? I would be doing a drop and create with the same schema in order to accomplish this... Jan 27, 2017 at 14:05

1 Answer 1

5
+100

You cannot remove lines in the table you are currently selecting! But you can use your solution in "Edit 1" with a temporary table:

BEGIN;

CREATE TEMP TABLE temp1 ON COMMIT DROP AS
SELECT
    row_number() over() AS gid,
    sbqry.geom AS geom
FROM 
(SELECT
    (ST_Dump(ST_Union(geom))).geom::geometry(Polygon, /*SRID*/) AS geom
FROM public.input_table
) AS sbqry;

TRUNCATE public.input_table;
INSERT INTO public.input_table (gid, geom) SELECT gid, geom FROM temp1;

COMMIT;

Note: I haven't checked your part of the query for the select/union stuff...

2
  • Your query works just fine. Just for info: UPDATE and SET can't be used in this case? Jan 29, 2017 at 12:25
  • In SQL, the UPDATE action is for updating existing rows one by one, and merging rows is not considered as an update. If you really want to use UPDATE, you can update the "first" row of the future union to be the new union and then delete the other ones, the SQL code will be really more complicated, but it's possible.
    – Elektordi
    Jan 29, 2017 at 23:03

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.