[postgis-users] Trigger to update lines using new points

[Obe, Regina]

Are you trying to do a line segment each comprising of 2 points or a
continuous line segment comprising of the whole batch of points you are
loading?
 
Couple of things that strike me odd are 
1) Seems like you would never have a record NEW.gid + 1 if you are doing
a FOR EACH ROW before insert since that row would not have been inserted
yet and both your start and end points are the same in your example
since you are reading from NEW.
2) I think for this case you should really be using an after insert
trigger so you are seeing the latest data.
3) There is no reason to do asEWKT since you want a geometry
4) If you want to draw a line comprising more than 2 points - e.g
containing the full batch of your inserts then you don't want to do a
row level trigger, but instead a per statement trigger.
 
Anyrate I would think your per row trigger function code should look
something like this
 
CREATE OR REPLACE FUNCTION insert_new_links() RETURNS "trigger"
 AS '
BEGIN
INSERT INTO links(the_geom) 
    SELECT MakeLine(prevnode.the_geom, NEW.the_geom)
        FROM nodes As prevnode WHERE prevnode.gid = NEW.gid - 1;
RETURN NEW;
 END' LANGUAGE plpgsql; 
 
CREATE OR  TRIGGER insert_new_links_insert    
AFTER INSERT ON nodes  
FOR EACH ROW    
EXECUTE PROCEDURE insert_new_links();
 
Hope that helps,
Regina
 

________________________________

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Broun Uganda
Sent: Tuesday, July 10, 2007 12:11 PM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] Trigger to update lines using new points


Dear all,A trying to write a trigger that would automatically create
lines joining points in the sequence in which i insert them. 
If I insert point AA then BB then CC, i want lines drawn as AA-BB &
BB-CC, Using the trigger below lines are drawn ( atleast i can see the
gid in table links) without the geometry. May anyone advise me on this.
Thank you
CREATE OR REPLACE FUNCTION insert_new_links() RETURNS "trigger"
 AS 'DECLAREstartpoint geometry;  
endpoint geometry;
BEGIN
startpoint = asEWKT(NEW.the_geom)  WHERE NEW.gid = NEW.gid;
endpoint = asEWKT(NEW.the_geom) WHERE NEW.gid = NEW.gid+1;
INSERT INTO links(the_geom)VALUES(MakeLine(startpoint, endpoint ));
RETURN NEW;
 END' LANGUAGE plpgsql; 
CREATE OR  TRIGGER insert_new_links_insert    
BEFORE INSERT ON nodes  
FOR EACH ROW    
EXECUTE PROCEDURE insert_new_links();

Broun Uganda




________________________________

Live Earth is coming.  Learn more about the hottest summer event - only
on MSN. Check it out!
<http://liveearth.msn.com?source=msntaglineliveearthwlm>  



-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070711/30729b1b/attachment.html>