<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<STYLE>P {
PADDING-RIGHT: 0px; PADDING-LEFT: 0px; PADDING-BOTTOM: 0px; MARGIN: 0px; PADDING-TOP: 0px
}
BODY {
FONT-SIZE: 10pt; FONT-FAMILY: Tahoma
}
</STYLE>
<META content="MSHTML 6.00.6000.16481" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=182544620-10072007>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?</SPAN></DIV>
<DIV><SPAN class=182544620-10072007></SPAN> </DIV>
<DIV><SPAN class=182544620-10072007>Couple of things that strike me odd are
</SPAN></DIV>
<DIV><SPAN class=182544620-10072007>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.</SPAN></DIV>
<DIV><SPAN class=182544620-10072007>2) I think for this case you should really
be using an after insert trigger so you are seeing the latest data.</SPAN></DIV>
<DIV><SPAN class=182544620-10072007>3) There is no reason to do asEWKT since you
want a geometry</SPAN></DIV>
<DIV><SPAN class=182544620-10072007>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.</SPAN></DIV>
<DIV><SPAN class=182544620-10072007></SPAN></DIV>
<DIV><SPAN class=182544620-10072007></SPAN> </DIV>
<DIV><SPAN class=182544620-10072007>Anyrate I would think your per row trigger
function code should look something like this</SPAN></DIV>
<DIV><SPAN class=182544620-10072007></SPAN> </DIV>
<DIV><SPAN class=182544620-10072007></SPAN><SPAN class=182544620-10072007>CREATE
OR REPLACE FUNCTION insert_new_links() RETURNS "trigger"<BR> AS
'<BR>BEGIN<BR>INSERT INTO links(the_geom) </SPAN></DIV>
<DIV><SPAN class=182544620-10072007> SELECT
MakeLine(prevnode.the_geom, NEW.the_geom)</SPAN></DIV>
<DIV><SPAN class=182544620-10072007>
FROM nodes As prevnode WHERE prevnode.gid = NEW.gid - 1;<BR>RETURN
NEW;<BR> END' LANGUAGE plpgsql; </SPAN></DIV>
<DIV><SPAN class=182544620-10072007></SPAN> </DIV>
<DIV><SPAN class=182544620-10072007>CREATE OR TRIGGER
insert_new_links_insert <BR>AFTER INSERT ON nodes
<BR>FOR EACH ROW <BR>EXECUTE PROCEDURE
insert_new_links();</SPAN></DIV>
<DIV><SPAN class=182544620-10072007></SPAN> </DIV>
<DIV><SPAN class=182544620-10072007>Hope that helps,</SPAN></DIV>
<DIV><SPAN class=182544620-10072007>Regina</SPAN></DIV>
<DIV><SPAN class=182544620-10072007></SPAN> </DIV>
<DIV dir=ltr align=left><BR></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<B>From:</B> postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of </B>Broun
Uganda<BR><B>Sent:</B> Tuesday, July 10, 2007 12:11 PM<BR><B>To:</B>
postgis-users@postgis.refractions.net<BR><B>Subject:</B> [postgis-users] Trigger
to update lines using new points<BR><BR></DIV>
<DIV></DIV>Dear all,A trying to write a trigger that would automatically create
lines joining points in the sequence in which i insert them. <BR>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.<BR>Thank you<BR>CREATE OR
REPLACE FUNCTION insert_new_links() RETURNS "trigger"<BR> AS
'DECLAREstartpoint geometry; <BR>endpoint geometry;<BR>BEGIN<BR>startpoint
= asEWKT(NEW.the_geom) WHERE NEW.gid = NEW.gid;<BR>endpoint =
asEWKT(NEW.the_geom) WHERE NEW.gid = NEW.gid+1;<BR>INSERT INTO
links(the_geom)VALUES(MakeLine(startpoint, endpoint ));<BR>RETURN
NEW;<BR> END' LANGUAGE plpgsql; <BR>CREATE OR TRIGGER
insert_new_links_insert <BR>BEFORE INSERT ON nodes
<BR>FOR EACH ROW <BR>EXECUTE PROCEDURE
insert_new_links();<BR><BR>Broun Uganda<BR><BR><BR><BR>
<HR>
Live Earth is coming. Learn more about the hottest summer event - only on
MSN. <A href="http://liveearth.msn.com?source=msntaglineliveearthwlm"
target=_new>Check it out!</A> </BODY></HTML>
<HTML><BODY><P><hr size=1></P>
<P><STRONG>
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.
</STRONG></P></BODY></HTML>