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
  • How are the output features and the source features related - by and ID or some sort? – DPSSpatial Jan 26 '17 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. – eclipsed_by_the_moon Jan 27 '17 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... – DPSSpatial Jan 27 '17 at 14:05
3
+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...

| improve this answer | |
  • Your query works just fine. Just for info: UPDATE and SET can't be used in this case? – eclipsed_by_the_moon Jan 29 '17 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 '17 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.