[postgis-users] Problem creating a trigger using ST_Union?

Mon Jun 8 08:36:15 PDT 2015

Hey,
you must think  in term of geometry and not layer,
because the function you use works on geometry and not layer.
For instance
----
UPDATE public.a_polygon_dissolve SET geom = ST_Union(geom)
where ...
----
will take one row by one row, and for each row geometry (geomA), compute
st_union(geomA),
which is obviously not what you want.

On the opposite,
-------------------------
SELECT ST_Union(geom) AS geom FROM a_polygon;
-------------------------
will take all the row from a_polygon, and compute the union of all the row
geom.

You can use CTE within INSERT to emulate that
(e.g.
WITH to_insert AS (
  --do my computing
  SELECT ...
)
INSERT INTO ...
USING ...
)

Anyway, I can't understand what you are trying to do .

What you call 'not visible' in qgis is simply that you can't access it from
the regular menu because QGIS can't understand it.
This is because you didn't specified the type of geometry you are
producing, and don't have a primary key or unique row id.

creating the table like this
----
SELECT ST_Union(geom) AS geom  INTO a_polygon_dissolve
FROM a_polygon GROUP BY geom;
----
is a bad idea.

You should use
-------not so clean- ----
SELECT row_number() over() as example_of_unique_id,
ST_Union(geom)::geometry(polygon,4326) AS geom  INTO a_polygon_dissolve
FROM a_polygon GROUP BY geom;

-- OR (very clean)

CREATE TABLE public.a_polygon_dissolve (
 gid serial,
 geom geometry(polygon,4326)
);
INSERT INTO public.a_polygon_dissolve (geom)
SELECT ST_Union(geom) AS geom  INTO a_polygon_dissolveFROM a_polygon GROUP BY
geom
------

Cheers,
Rémi-C

2015-06-08 14:25 GMT+02:00 KhunSanAung <khunsanaung.gis at gmail.com>:

> Hi All,
>
> I'd like to create a trigger that will
> update the dissolve layer when there is any change on a polygon layer.
> I need a dissolve layer (big polygon) to get the boundary of all small
> polygons layer.
> But not able to get it successfully yet.
>
> My PGSQL trigger code is as below.
>
> ------------------------- CODE Start------------------
> -- Function: public.a_dissolve_trigger_func()
> -- DROP FUNCTION public.a_dissolve_trigger_func();
>
> CREATE OR REPLACE FUNCTION public.a_dissolve_trigger_func()
>   RETURNS trigger AS
> $BODY$
> BEGIN
> UPDATE public.a_polygon_dissolve
> SET
> geom = ST_Union(geom)
> where a_polygon_dissolve.gid=1;
> END;
> $BODY$
>   LANGUAGE plpgsql VOLATILE
>   COST 100;
> ALTER FUNCTION public.a_dissolve_trigger_func()
>   OWNER TO postgres;
>
>
> -- Trigger: a_dissolve_trigger on public.a_polygon
> -- DROP TRIGGER a_dissolve_trigger ON public.a_polygon;
>
> CREATE TRIGGER a_dissolve_trigger
>   AFTER INSERT OR DELETE OR UPDATE
>   ON public.a_polygon
>   FOR EACH ROW
>   EXECUTE PROCEDURE public.a_dissolve_trigger_func();
>
> ---------------------------------------- END OF CODE -------
>
>
> When I tried to modified the input polygon in QGIS,
> the error message says 'aggregate function - ST_Union() is not allowed in
> Update as below.
>
> --------------- error message start --------------------
>
> Could not commit changes to layer a_polygon
>
>
> Errors: ERROR: 1 feature(s) not added.
>
>  Provider errors:
>
> PostGIS error while adding features: ERROR: aggregate functions are not
> allowed in UPDATE
>
> LINE 3: geom = ST_Union(geom)
>
> ^
>
> QUERY: UPDATE public.a_polygon_dissolve
>
> SET
>
> geom = ST_Union(geom)
>
> where a_polygon_dissolve.gid=1
>
> CONTEXT: PL/pgSQL function a_dissolve_trigger_func() line 3 at SQL
> statement
>
> ----------------- error message end ----------------------
>
> *Question 1.*
> *If someone could suggest a better method dissolving layer in trigger, it
> would be very helpful for me.*
>
>
> I search and found some methods for creating a dissolve polygon.
> When I select the geom by Union, as follow
>
> -------------------------
> SELECT ST_Union(geom) AS geom FROM a_polygon;
> -------------------------
> Query returned successfully: one row affected,
>
> I can see the geometry values in output result.
> But When I tried to write the result into a new dissolve table as follow;
>
> -------------------------
> SELECT ST_Union(geom) AS geom  INTO a_polygon_dissolve
> FROM a_polygon GROUP BY geom;
> -------------------------
>
> The layer is not visible when I tried to connect using QGIS.
> I cannot open that file using QGIS.
> Sometimes (testing with other polygon layers) the geom column is blank and
> I cannot open the newly form 'a_polygon_dissolve' file in QIGS too.
> I'd tried to add a primary key, 'gid' column but it doesn't work.
> In this case, I need to drop the 'dissolve polygon' first in order to
> write a new file.
>
> *Question 2.*
> *If this is the option to use the ST_Union, how can I fix the above
> geometry problem ?*
>
>
> I'm using, postgres-9.3 and postgis-2.1 version in Window OS version.
> The input 'a_polygon' as text is below.
>
> ------------- input 'a_polygon' as text --------------
> "st_astext"
> "MULTIPOLYGON(((96.1484380679014 16.781203891314,96.1507922871521
> 16.7810815942101,96.1505171186682 16.777748998128,96.1505022713357
> 16.7777492102328,96.1483769193495 16.777779572404,96.1484380679014
> 16.781203891314)))"
> "MULTIPOLYGON(((96.1507922871521 16.7810815942101,96.1507922871521
> 16.7810815942101,96.152657317987 16.7810510199341,96.1523821495031
> 16.777779572404,96.1505171186682 16.777748998128,96.1507922871521
> 16.7810815942101)))"
> "MULTIPOLYGON(((96.152657317987 16.7810510199341,96.1540178732682
> 16.7810357327961,96.1538497147503 16.77788658237,96.1523821495031
> 16.777779572404,96.152657317987 16.7810510199341)))"
> "MULTIPOLYGON(((96.1540178732682 16.7810357327961,96.1555465870673
> 16.7810815942101,96.1554242899634 16.7781006023018,96.1538497147503
> 16.77788658237,96.1540178732682 16.7810357327961)))"
> ---------------- end of text ------------------
>
>
> Thank you very much for any idea.
>
>
> Have a nice day!
> --
>
> *Mr. Khun San Aung*
> * <http://geoportal.icimod.org/>*
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150608/c6f571ee/attachment.html>