[postgis-users] Problem creating a trigger using ST_Union?
KhunSanAung
khunsanaung.gis at gmail.com
Tue Jun 9 06:51:08 PDT 2015
Thank you very much ,
inserting the aggregate values into the dissolve table by trigger is what I
want.
The sql command below works well as I wanted to.
----------------------------------------------------
-- 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
DELETE FROM a_polygon_dissolve
WHERE gid>0;
INSERT INTO public.a_polygon_dissolve (geom)
SELECT ST_Union(geom) AS geom FROM a_polygon ; --GROUP BY geom ;
RETURN NEW;
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();
----------------------------------------------------
I've used INSERT instead of UPDATE.
I create a dissolve layer for the first time and it get updated by trigger
all the time.
Thank you very much again for your explanation and help
Best regards
-
On Mon, Jun 8, 2015 at 10:06 PM, Rémi Cura <remi.cura at gmail.com> wrote:
> 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
>>
>
>
--
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/20150609/31372038/attachment.html>
More information about the postgis-users
mailing list