Hi,<br><br>If the idea was to create the geometry field when the LONG and LAT fields are inserted here's a trigger I once did for this kind of task.<br>It updates the geometry field when long and lat are changed. It also updates long and lat if the geometry is modified.
<br>In the example the coordinates are x and y and the geometry field is the_geom. Replace these and the database name (hki_pois in the example). I found this from my old files and are not sure if it works but it should give you an idea how it's done.
<br><br><br>CREATE FUNCTION update_geom() RETURNS trigger AS $update_geom$ <br> DECLARE <br> geom varchar;<br> x_coord float;<br> y_coord float;<br> BEGIN<br> EXECUTE 'SELECT GeomFromText(\'POINT(' ||
NEW.x || ' ' || NEW.y || ')\')' INTO geom;<br><br> IF (NEW.x <> OLD.x OR NEW.y <> OLD.y) THEN <br> NEW.the_geom:= geom;<br> ELSEIF (NEW.the_geom <> geom) THEN
<br> SELECT INTO x_coord X(NEW.the_geom);<br> SELECT INTO y_coord Y(NEW.the_geom);<br> NEW.x = x_coord;<br> NEW.y = y_coord;<br> END IF;<br> RETURN NEW;<br> END;
<br>$update_geom$ LANGUAGE plpgsql;<br>CREATE TRIGGER update_geom BEFORE UPDATE ON hki_pois FOR EACH ROW EXECUTE PROCEDURE update_geom();<br><br>-- <br>Otto<br><br><br><br><div><span class="gmail_quote">On 1/24/07, <b class="gmail_sendername">
Mark Cave-Ayland</b> <<a href="mailto:mark.cave-ayland@ilande.co.uk">mark.cave-ayland@ilande.co.uk</a>> wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
On Wed, 2007-01-24 at 12:40 +0100, Veronique Chaffard wrote:<br>> I would like to insert the geometry field of a record by using a trigger<br>> and a fonction, after the insertion of the other fields;The X and Y<br>
> coordinates are saved into 2 fields: LAT and LONG. The geometry field<br>> type is a point.<br>><br>> My table is declared as:<br>CREATE TABLE TEST (<br>> ID serial not null,<br>> NOM VARCHAR(50) not null,
<br>> LONG FLOAT8 not null,<br>> LAT FLOAT8 not null,<br>> constraint PK_TEST primary key (ID)<br>> );<br>> SELECT AddGeometryColumn('','test','geom','-1','POINT',2);
<br>><br>> Could you help me ?<br>><br>> Thanks in advance.<br><br><br>Hi Veronique,<br><br>You don't need to store latitude and longitude as separate fields as<br>they are automatically stored as part of the geometry. So you can create
<br>your table and insert fields like this:<br><br>CREATE TABLE test (<br> ID serial not null,<br> NOM VARCHAR(50) not null<br> constraint PK_TEST primary key (ID)<br>);<br>SELECT AddGeometryColumn('','test','geom','-1','POINT',2);
<br><br>INSERT INTO test (nom, geom) VALUES ('test1', MakePoint(LONG1, LAT1));<br>INSERT INTO test (nom, geom) VALUES ('test2', MakePoint(LONG2, LAT2));<br>INSERT INTO test (nom, geom) VALUES ('test3', MakePoint(LONG3, LAT3));
<br><br><br>You can view the complete table like this:<br><br>SELECT AsText(geom) FROM test;<br><br>And you can extract the latitude and longitude as separate fields for<br>your application like this:<br><br>SELECT id, nom, X(geom) AS long, Y(geom) AS lat FROM test;
<br><br>Finally, I would also recommend that if you are new to<br>PostgreSQL/PostGIS that you keep all table and column names in lower<br>case, since PostgreSQL quoting is slightly different than you may<br>expect.<br><br>
<br>Kind regards,<br><br>Mark.<br><br><br>_______________________________________________<br>postgis-users mailing list<br><a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br></blockquote></div><br>