<div dir="ltr"><div class="gmail_default" style="color:rgb(0,0,255)">Hi All,</div><div class="gmail_default" style="color:rgb(0,0,255)"><br></div><div class="gmail_default" style="color:rgb(0,0,255)">I'd like to create a trigger that will </div><div class="gmail_default" style="color:rgb(0,0,255)">update the dissolve layer when there is any change on a polygon layer.</div><div class="gmail_default" style="color:rgb(0,0,255)">I need a dissolve layer (big polygon) to get the boundary of all small polygons layer.</div><div class="gmail_default" style="color:rgb(0,0,255)">But not able to get it successfully yet.</div><div class="gmail_default" style="color:rgb(0,0,255)"><br></div><div class="gmail_default" style="color:rgb(0,0,255)">My PGSQL trigger code is as below.<br></div><div class="gmail_default" style="color:rgb(0,0,255)"><br></div><div class="gmail_default" style="color:rgb(0,0,255)">------------------------- CODE Start------------------</div><div class="gmail_default" style><div class="gmail_default" style><font face="monospace, monospace" color="#00ff00">-- Function: public.a_dissolve_trigger_func()</font></div><div class="gmail_default" style><font face="monospace, monospace" color="#00ff00">-- DROP FUNCTION public.a_dissolve_trigger_func();</font></div><div class="gmail_default" style><font color="#0000ff" face="monospace, monospace"><br></font></div><div class="gmail_default" style><font face="monospace, monospace" color="#000000">CREATE OR REPLACE FUNCTION public.a_dissolve_trigger_func()</font></div><div class="gmail_default" style><font face="monospace, monospace" color="#000000"> RETURNS trigger AS</font></div><div class="gmail_default" style><font face="monospace, monospace" color="#000000">$BODY$</font></div><div class="gmail_default" style><font face="monospace, monospace" color="#000000"><span class="" style="white-space:pre"> </span>BEGIN<span class="" style="white-space:pre"> </span></font></div><div class="gmail_default" style><font face="monospace, monospace" color="#000000"><span class="" style="white-space:pre"> </span>UPDATE public.a_polygon_dissolve </font></div><div class="gmail_default" style><font face="monospace, monospace" color="#000000"><span class="" style="white-space:pre"> </span>SET </font></div><div class="gmail_default" style><font face="monospace, monospace" color="#000000"><span class="" style="white-space:pre"> </span>geom = ST_Union(geom)</font></div><div class="gmail_default" style><font face="monospace, monospace" color="#000000"><span class="" style="white-space:pre"> </span>where a_polygon_dissolve.gid=1;</font></div><div class="gmail_default" style><font face="monospace, monospace" color="#000000"><span class="" style="white-space:pre"> </span>END;</font></div><div class="gmail_default" style><font face="monospace, monospace" color="#000000">$BODY$</font></div><div class="gmail_default" style><font face="monospace, monospace" color="#000000"> LANGUAGE plpgsql VOLATILE</font></div><div class="gmail_default" style><font face="monospace, monospace" color="#000000"> COST 100;</font></div><div class="gmail_default" style><font face="monospace, monospace" color="#000000">ALTER FUNCTION public.a_dissolve_trigger_func()</font></div><div class="gmail_default" style><font face="monospace, monospace" color="#000000"> OWNER TO postgres;</font></div><div class="gmail_default" style><font color="#0000ff" face="monospace, monospace"><br></font></div><div class="gmail_default" style><font color="#0000ff" face="monospace, monospace"><br></font></div><div class="gmail_default" style><font face="monospace, monospace" color="#00ff00">-- Trigger: a_dissolve_trigger on public.a_polygon</font></div><div class="gmail_default" style><font face="monospace, monospace" color="#00ff00">-- DROP TRIGGER a_dissolve_trigger ON public.a_polygon;</font></div><div class="gmail_default" style><font color="#0000ff" face="monospace, monospace"><br></font></div><div class="gmail_default" style><font face="monospace, monospace" color="#000000">CREATE TRIGGER a_dissolve_trigger</font></div><div class="gmail_default" style><font face="monospace, monospace" color="#000000"> AFTER INSERT OR DELETE OR UPDATE </font></div><div class="gmail_default" style><font face="monospace, monospace" color="#000000"> ON public.a_polygon</font></div><div class="gmail_default" style><font face="monospace, monospace" color="#000000"> FOR EACH ROW</font></div><div class="gmail_default" style><font face="monospace, monospace" color="#000000"> EXECUTE PROCEDURE public.a_dissolve_trigger_func();</font></div></div><div class="gmail_default" style="color:rgb(0,0,255)"><br></div><div class="gmail_default" style="color:rgb(0,0,255)">---------------------------------------- END OF CODE -------</div><div class="gmail_default"><div class="gmail_default" style="color:rgb(0,0,255)"><br></div><div class="gmail_default" style="color:rgb(0,0,255)"><br></div><div class="gmail_default" style="color:rgb(0,0,255)">When I tried to modified the input polygon in QGIS, </div></div><div class="gmail_default"><font color="#0000ff" face="arial, helvetica, sans-serif">the error message says 'aggregate function - ST_Union() is not allowed in Update as below.</font></div><div class="gmail_default"><font color="#0000ff" face="arial, helvetica, sans-serif"><br></font></div><div class="gmail_default"><font face="arial narrow, sans-serif" color="#000000">--------------- error message start --------------------</font></div><div class="gmail_default"><p style="margin:0px"><font face="arial narrow, sans-serif" color="#000000">Could not commit changes to layer a_polygon</font></p>
<p style="margin:0px"><font face="arial narrow, sans-serif" color="#000000"><br></font></p>
<p style="margin:0px"><font face="arial narrow, sans-serif" color="#000000">Errors: ERROR: 1 feature(s) not added.</font></p>
<p style="margin:0px"> </p>
<p style="margin:0px"><font face="arial narrow, sans-serif" color="#000000"> Provider errors:</font></p>
<p style="margin:0px"><font face="arial narrow, sans-serif" color="#000000"> PostGIS error while adding features: ERROR: aggregate functions are not allowed in UPDATE</font></p>
<p style="margin:0px"><font face="arial narrow, sans-serif" color="#000000"> LINE 3: geom = ST_Union(geom)</font></p>
<p style="margin:0px"><font face="arial narrow, sans-serif" color="#000000"> ^</font></p>
<p style="margin:0px"><font face="arial narrow, sans-serif" color="#000000"> QUERY: UPDATE public.a_polygon_dissolve </font></p>
<p style="margin:0px"><font face="arial narrow, sans-serif" color="#000000"> SET </font></p>
<p style="margin:0px"><font face="arial narrow, sans-serif" color="#000000"> geom = ST_Union(geom)</font></p>
<p style="margin:0px"><font face="arial narrow, sans-serif" color="#000000"> where a_polygon_dissolve.gid=1</font></p>
<p style="margin:0px"><font face="arial narrow, sans-serif" color="#000000"> CONTEXT: PL/pgSQL function a_dissolve_trigger_func() line 3 at SQL statement</font></p><p style="color:rgb(0,0,255);margin:0px">----------------- error message end ----------------------</p></div><div class="gmail_extra"><div><br></div><div><div class="gmail_default" style="color:rgb(0,0,255)"><b>Question 1.</b></div><div class="gmail_default" style="color:rgb(0,0,255)"><b>If someone could suggest a better method dissolving layer in trigger, it would be very helpful for me.</b></div><div class="gmail_default" style="color:rgb(0,0,255)"><br></div><br></div><div><div class="gmail_default" style="color:rgb(0,0,255)">I search and found some methods for creating a dissolve polygon.</div><div class="gmail_default" style="color:rgb(0,0,255)">When I select the geom by Union, as follow</div><div class="gmail_default"><br></div><div class="gmail_default"><span style="color:rgb(0,0,0);font-family:monospace,monospace">-------------------------</span></div><div class="gmail_default"><span style="color:rgb(0,0,0);font-family:monospace,monospace">SELECT ST_Union(geom) AS geom</span><font color="#000000" face="monospace, monospace"> FROM a_polygon;</font></div><div class="gmail_default"><div class="gmail_default"><font color="#000000" face="monospace, monospace">-------------------------</font></div><div class="gmail_default">Query returned successfully: one row affected, <br></div><div class="gmail_default"><br></div></div><div class="gmail_default" style="color:rgb(0,0,255)">I can see the geometry values in output result.</div><div class="gmail_default" style="color:rgb(0,0,255)">But When I tried to write the result into a new dissolve table as follow;<br></div><div class="gmail_default" style="color:rgb(0,0,255)"><div class="gmail_default" style="color:rgb(34,34,34)"><br></div><div class="gmail_default" style="color:rgb(34,34,34)"><span style="color:rgb(0,0,0);font-family:monospace,monospace">-------------------------</span></div><div class="gmail_default" style="color:rgb(34,34,34)"><span style="color:rgb(0,0,0);font-family:monospace,monospace">SELECT ST_Union(geom) AS geom INTO a_</span><span style="color:rgb(0,0,0);font-family:monospace,monospace">polygon_</span><span style="color:rgb(0,0,0);font-family:monospace,monospace">dissolve</span></div><div class="gmail_default" style="color:rgb(34,34,34)"><div class="gmail_default"><font color="#000000" face="monospace, monospace">FROM a_polygon GROUP</font><span style="color:rgb(0,0,0);font-family:monospace,monospace"> BY geom;</span></div><div class="gmail_default"><font color="#000000" face="monospace, monospace">-------------------------</font></div><div class="gmail_default"><br></div></div><div class="gmail_default">The layer is not visible when I tried to connect using QGIS.</div><div class="gmail_default">I cannot open that file using QGIS. <br></div><div class="gmail_default">Sometimes (testing with other polygon layers) the geom column is blank and I cannot open the newly form '<span style="color:rgb(0,0,0);font-family:monospace,monospace">a_</span><span style="color:rgb(0,0,0);font-family:monospace,monospace">polygon_</span><span style="color:rgb(0,0,0);font-family:monospace,monospace">dissolve'</span> file in QIGS too. </div><div class="gmail_default">I'd tried to add a primary key, 'gid' column but it doesn't work.</div><div class="gmail_default">In this case, I need to drop the 'dissolve polygon' first in order to write a new file.<br></div><div class="gmail_default"><br></div><div class="gmail_default"><b>Question 2.</b></div><div class="gmail_default"><b>If this is the option to use the ST_Union, how can I fix the above geometry problem ?</b></div></div></div><div><br></div><div><br></div><div><div class="gmail_default" style="color:rgb(0,0,255)">I'm using, postgres-9.3 and postgis-2.1 version in Window OS version.</div><div class="gmail_default" style="color:rgb(0,0,255)">The input 'a_polygon' as text is below.</div></div><div><div class="gmail_default" style="color:rgb(0,0,255)"><br></div><div class="gmail_default"><div class="gmail_default"><font face="monospace, monospace" color="#000000">------------- input 'a_polygon' as text --------------</font></div><div class="gmail_default"><font face="monospace, monospace" color="#000000">"st_astext"</font></div><div class="gmail_default"><font face="monospace, monospace" color="#000000">"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)))"</font></div><div class="gmail_default"><font face="monospace, monospace" color="#000000">"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)))"</font></div><div class="gmail_default"><font face="monospace, monospace" color="#000000">"MULTIPOLYGON(((96.152657317987 16.7810510199341,96.1540178732682 16.7810357327961,96.1538497147503 16.77788658237,96.1523821495031 16.777779572404,96.152657317987 16.7810510199341)))"</font></div><div class="gmail_default"><font face="monospace, monospace" color="#000000">"MULTIPOLYGON(((96.1540178732682 16.7810357327961,96.1555465870673 16.7810815942101,96.1554242899634 16.7781006023018,96.1538497147503 16.77788658237,96.1540178732682 16.7810357327961)))"</font></div><div class="gmail_default"><font face="monospace, monospace" color="#000000">---------------- end of text ------------------</font></div><div class="gmail_default"><br></div><div class="gmail_default"><br></div></div></div><div class="gmail_default" style="color:rgb(0,0,255)">Thank you very much for any idea.</div><div class="gmail_default" style="color:rgb(0,0,255)"><br></div><br><div class="gmail_signature"><div dir="ltr"><span style="color:rgb(0,0,153)">Have a nice day!</span><br>--<br><p style="line-height:115%"><b><span style="font-size:9pt;line-height:115%;color:rgb(127,127,127)" lang="EN-GB">Mr. Khun San Aung</span></b></p><u><span style="font-size:9pt;line-height:115%;color:blue" lang="EN-GB"><a href="http://geoportal.icimod.org/" target="_blank"></a></span></u><u><span style="font-size:9pt;line-height:115%;color:rgb(127,127,127)" lang="EN-GB"></span></u></div></div>
</div></div>