<html>
<head>
<style>
.hmmessage P
{
margin:0px;
padding:0px
}
body.hmmessage
{
FONT-SIZE: 10pt;
FONT-FAMILY:Tahoma
}
</style>
</head>
<body class='hmmessage'><div style="text-align: left;">Dear all,<br>I have x,y coordinates in CSV file which i copy to postgres database using:<br><br><ol style="color: rgb(255, 0, 0);"><li>COPY links (id,from_node,to_node,....) FROM 'path/to/file' DELIMITER ',' CSV HEADER;</li><li>COPY nodes (id,point_x,point_y...) FROM 'path/to/file' DELIMITER ',' CSV HEADER;</li></ol></div><br>I have then written a trigger that would generate the_geom from number 2 (pointx,point_y);<br>I then use the id of Number 2 to generate line geometries for Number 1, where from_node and to_node are the beginning and end points of the line.<br>I use the following triggers:<br><span style="color: rgb(255, 0, 0);">BEGIN</span><br style="color: rgb(255, 0, 0);"><span style="color: rgb(255, 0, 0);">IF </span><span style="font-weight: bold; color: rgb(255, 0, 0);">NEW</span><span style="color: rgb(255, 0, 0);">.the_geom is null</span><br style="color: rgb(255, 0, 0);"><span style="color: rgb(255, 0, 0);">THEN</span><br style="color: rgb(255, 0, 0);"><span style="color: rgb(255, 0, 0);">INSERT INTO NODES (the_geom) </span><br style="color: rgb(255, 0, 0);"><span style="color: rgb(255, 0, 0);">(SELECT SetSRID(MakePoint(new.point_x, new.point_y), -1)FROM nodes) ;</span><br style="color: rgb(255, 0, 0);"><span style="color: rgb(255, 0, 0);">ELSE (for circumstances where i have the_geom and not the point_x and point_y)</span><br style="color: rgb(255, 0, 0);"><span style="color: rgb(255, 0, 0);">NEW.point_x = X(NEW.the_geom); </span><br style="color: rgb(255, 0, 0);"><span style="color: rgb(255, 0, 0);">NEW.point_y = Y(NEW.the_geom);</span><br style="color: rgb(255, 0, 0);"><span style="color: rgb(255, 0, 0);"> </span><br style="color: rgb(255, 0, 0);"><span style="color: rgb(255, 0, 0);">END IF;</span><br style="color: rgb(255, 0, 0);"><span style="color: rgb(255, 0, 0);">RETURN NEW;</span><br style="color: rgb(255, 0, 0);"><span style="color: rgb(255, 0, 0);">END</span><br style="color: rgb(255, 0, 0);"><br style="color: rgb(255, 0, 0);"><span style="color: rgb(255, 0, 0);">CREATE TRIGGER calc_point_insert</span><br style="color: rgb(255, 0, 0);"><span style="color: rgb(255, 0, 0);"> AFTER INSERT</span><br style="color: rgb(255, 0, 0);"><span style="color: rgb(255, 0, 0);"> ON nodes</span><br style="color: rgb(255, 0, 0);"><span style="color: rgb(255, 0, 0);"> FOR EACH ROW</span><br style="color: rgb(255, 0, 0);"><span style="color: rgb(255, 0, 0);"> EXECUTE PROCEDURE calc_point();</span><br style="color: rgb(255, 0, 0);"><span style="color: rgb(255, 0, 0);">CREATE TRIGGER calc_point_update</span><br style="color: rgb(255, 0, 0);"><span style="color: rgb(255, 0, 0);">
AFTERUPDATE</span><br style="color: rgb(255, 0, 0);"><span style="color: rgb(255, 0, 0);">
ON nodes</span><br style="color: rgb(255, 0, 0);"><span style="color: rgb(255, 0, 0);">
FOR EACH ROW</span><br style="color: rgb(255, 0, 0);"><span style="color: rgb(255, 0, 0);">
EXECUTE PROCEDURE calc_point();</span><br>This runs with no end and if i remove the<span style="font-style: italic;"> bold NEW</span> i get an error the_geom doesn't exist.<br>On the Number 1 Side I have this that is for joining points, which i thought should work but doesnt work.<br><br><span style="color: rgb(255, 0, 0);">CREATE OR REPLACE FUNCTION update_links()</span><br style="color: rgb(255, 0, 0);"><span style="color: rgb(255, 0, 0);"> RETURNS "trigger" AS</span><br style="color: rgb(255, 0, 0);"><span style="color: rgb(255, 0, 0);">$BODY$</span><br style="color: rgb(255, 0, 0);"><span style="color: rgb(255, 0, 0);">BEGIN</span><br style="color: rgb(255, 0, 0);"><br style="color: rgb(255, 0, 0);"><span style="color: rgb(255, 0, 0);">UPDATE links SET the_geom = MakeLine(NEW.the_geom,ends.the_geom) </span><br style="color: rgb(255, 0, 0);"><span style="color: rgb(255, 0, 0);"> FROM nodes As ends</span><br style="color: rgb(255, 0, 0);"><span style="color: rgb(255, 0, 0);"> WHERE NEW.id = links.from_node and ends.id = links.to_node;</span><br style="color: rgb(255, 0, 0);"><span style="color: rgb(255, 0, 0);"> </span><br style="color: rgb(255, 0, 0);"><span style="color: rgb(255, 0, 0);">UPDATE links SET the_geom = MakeLine(starts.the_geom,NEW.the_geom) </span><br style="color: rgb(255, 0, 0);"><span style="color: rgb(255, 0, 0);"> FROM nodes As starts</span><br style="color: rgb(255, 0, 0);"><span style="color: rgb(255, 0, 0);"> WHERE starts.id = links.from_node and NEW.id =links.to_node;</span><br style="color: rgb(255, 0, 0);"><br style="color: rgb(255, 0, 0);"><span style="color: rgb(255, 0, 0);"> </span><br style="color: rgb(255, 0, 0);"><span style="color: rgb(255, 0, 0);">RETURN NEW;</span><br style="color: rgb(255, 0, 0);"><span style="color: rgb(255, 0, 0);">END$BODY$</span><br style="color: rgb(255, 0, 0);"><span style="color: rgb(255, 0, 0);">CREATE TRIGGER update_links_insert</span><br style="color: rgb(255, 0, 0);"><span style="color: rgb(255, 0, 0);"> AFTER INSERT</span><br style="color: rgb(255, 0, 0);"><span style="color: rgb(255, 0, 0);"> ON nodes</span><br style="color: rgb(255, 0, 0);"><span style="color: rgb(255, 0, 0);"> FOR EACH ROW</span><br style="color: rgb(255, 0, 0);"><span style="color: rgb(255, 0, 0);"> EXECUTE PROCEDURE update_links();</span><br>Similarly for update.<br><br>Please help me with these triggers<br>Thank you<br><br>Broun Uganda<br><br><br /><hr />Express yourself instantly with MSN Messenger! <a href='http://clk.atdmt.com/AVE/go/onm00200471ave/direct/01/' target='_new'>MSN Messenger</a></body>
</html>