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

[KhunSanAung]

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/>*
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150608/c81df242/attachment.html>