[Mapbender-users] Create points with a geoserver WFS-T

[Daniel Grum]

Sebastian Krohn-Grimberghe schrieb:
> Hello Daniel,
>
>   
>> If I unstand you correctly, I have to write a function in my PostgreSQL
>> database. I never programmed a function in PostgreSQL by my
>> self...should this be sth. like a Trigger: so the Trigger startet after
>> the user created the object?! and wrote the message? and stop the
>> action
>> saving the new point in my database!
>>     
>
> You are right... You can create a trigger for your geometry table, which is
> called when GeoServer inserts a new geometry into the table. There you can
> check with PostGIS functions if the new (buffered) point intersects other
> geometries. If the point intersects another geometry you can raise an
> exception. By raising the exception the new point will not be stored in the
> database and GeoServer should receive and pass a failure message. 
>
> The trigger function could look like this:
>
> CREATE OR REPLACE FUNCTION my_trigger_function() RETURNS trigger AS
>  $$
>    DECLARE
> 	-- declare variables
>       current_row RECORD;
>    BEGIN
> 	-- get all existing geometries which intersect the new geometry
> (NEW.the_geom) 
> 	-- around the new geometry is a buffer of 50 meters (or whatever
> your unit is)
>       FOR current_row IN SELECT * from my_geom_table t WHERE
> ST_Intersects(ST_Buffer(NEW.the_geom, 50),t.the_geom) LOOP
> 		-- intersection found
> 		RAISE EXCEPTION 'my error message';
>       END LOOP;
>
> 	-- no intersection, new geometry will be inserted
>       RETURN NEW;
>    END;
>  $$ LANGUAGE 'plpgsql';
>
> CREATE TRIGGER my_trigger BEFORE INSERT OR UPDATE ON my_geom_table FOR EACH
> ROW EXECUTE PROCEDURE my_trigger_function();
>
>
> I use a similar trigger with an OpenLayers client. When an exception is
> raised, GeoServer returns XML with a failure information. But the message is
> always the same, so it is not affected by the error message in the trigger
> function. I do not know how the error messages of WFS transactions are
> handled by Mapbender. So I cannot help you with this yet, but I hope this
> helps you with the database part.
>
>
> Best regards,
> Sebastian
>
> --
> Sebastian Krohn-Grimberghe
> Entwicklung
>
> IP SYSCON GmbH
>
> Tiestestraße 16-18
> D-30171 Hannover
> Tel.: +49 (511)  85 03 03 - 0
> Fax: +49 (511)  85 03 03 - 30
> E-Mail: sebastian.krohn-grimberghe at ipsyscon.de
> http://www.ipsyscon.de/
>
> _______________________________________________
> Mapbender_users mailing list
> Mapbender_users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapbender_users
>
>   
I tried to solve the problem, but after I made this TRIGGER function I 
can not create new points(buildings) anymore!!! I allways get the 
message that the new point can not by saved in my database from the 
WFS-T :-(

My TRIGGER function is:

-- Function: my_trigger_function()

-- DROP FUNCTION my_trigger_function();

CREATE OR REPLACE FUNCTION my_trigger_function()
  RETURNS trigger AS
$BODY$
   DECLARE
    -- declare variables
      current_row RECORD;
   BEGIN
    -- get all existing geometries which intersect the new geometry 
NEW.the_geom
    -- around the new geometry is a buffer of 50 meters (or whatever 
your unit is)
      FOR current_row IN
      SELECT *
      FROM public.holzfaeller t, public.erzmine e, public.wohnhaus w, 
public.steinbruch s, public.kaserne k, public.saegerwerk r
      WHERE     ST_Intersects(ST_Buffer(NEW.the_geom, 5),t.the_geom) AND
        ST_Intersects(ST_Buffer(NEW.the_geom, 5),e.the_geom) AND
        ST_Intersects(ST_Buffer(NEW.the_geom, 5),w.the_geom) AND
        ST_Intersects(ST_Buffer(NEW.the_geom, 5),s.the_geom) AND
        ST_Intersects(ST_Buffer(NEW.the_geom, 5),k.the_geom) AND
        ST_Intersects(ST_Buffer(NEW.the_geom, 5),r.the_geom)

        LOOP
           -- intersection found
    RAISE EXCEPTION 'Gebaeude kann hier nicht errichtet werden!!!';
    END LOOP;

    -- no intersection, new geometry will be inserted
      RETURN NEW;
   END;
 $BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION my_trigger_function() OWNER TO postgres;

I tried a low buffer but the but the error message was the same :-(
Where can be the mistake???
--daniel