[postgis-users] Convert x, y into geometry and update two geometries

Broun Uganda tekuganda at hotmail.com
Tue Feb 5 13:15:22 PST 2008

Dear all,
I have x,y coordinates in CSV file which i copy to postgres database using:

COPY links (id,from_node,to_node,....) FROM 'path/to/file' DELIMITER ',' CSV HEADER;COPY nodes (id,point_x,point_y...) FROM 'path/to/file' DELIMITER ',' CSV HEADER;
I have then written a trigger that would generate the_geom from number 2 (pointx,point_y);
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.
I use the following triggers:
BEGINIF NEW.the_geom is nullTHENINSERT INTO NODES  (the_geom) (SELECT SetSRID(MakePoint(new.point_x, new.point_y), -1)FROM nodes) ;ELSE (for circumstances where i have the_geom and not the point_x and point_y)NEW.point_x = X(NEW.the_geom); NEW.point_y = Y(NEW.the_geom);   END IF;RETURN NEW;ENDCREATE TRIGGER calc_point_insert  AFTER INSERT  ON nodes  FOR EACH ROW  EXECUTE PROCEDURE calc_point();CREATE TRIGGER calc_point_update
  ON nodes
  EXECUTE PROCEDURE calc_point();
This runs with no end and if i remove the bold NEW  i get an error the_geom doesn't exist.
On the Number 1 Side I have this that is for joining points, which i thought should work but doesnt work.

CREATE OR REPLACE FUNCTION update_links()  RETURNS "trigger" AS$BODY$BEGINUPDATE links SET the_geom = MakeLine(NEW.the_geom,ends.the_geom)         FROM nodes As ends        WHERE NEW.id = links.from_node and ends.id = links.to_node; UPDATE links SET the_geom = MakeLine(starts.the_geom,NEW.the_geom)         FROM nodes As starts        WHERE starts.id = links.from_node and NEW.id =links.to_node; RETURN NEW;END$BODY$CREATE TRIGGER update_links_insert  AFTER INSERT  ON nodes  FOR EACH ROW  EXECUTE PROCEDURE update_links();
Similarly for update.

Please help me with these triggers
Thank you

Broun Uganda

Express yourself instantly with MSN Messenger! Download today it's FREE!
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080206/3e1cd51e/attachment.html>

More information about the postgis-users mailing list