[postgis-users] How to insert the geometry field of a record with trigger and function

[Veronique Chaffard]

Thanks you for your response; That's what I want to do.

Otto Laurila a écrit :
> Hi,
>
> 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.
> It updates the geometry field when long and lat are changed. It also 
> updates long and lat if the geometry is modified.
> 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.
>
>
> CREATE FUNCTION update_geom() RETURNS trigger AS $update_geom$
>     DECLARE
>         geom varchar;
>         x_coord float;
>         y_coord float;
>     BEGIN
>         EXECUTE 'SELECT  GeomFromText(\'POINT(' || NEW.x || ' ' || 
> NEW.y || ')\')' INTO geom;
>
>         IF (NEW.x <> OLD.x OR NEW.y <> OLD.y)  THEN
>                 NEW.the_geom:= geom;
>         ELSEIF (NEW.the_geom <> geom) THEN
>             SELECT INTO x_coord X(NEW.the_geom);
>             SELECT INTO y_coord Y(NEW.the_geom);
>             NEW.x = x_coord;
>             NEW.y = y_coord;
>         END IF;
>         RETURN NEW;
>     END;
> $update_geom$ LANGUAGE plpgsql;
> CREATE TRIGGER update_geom BEFORE UPDATE ON hki_pois FOR EACH ROW 
> EXECUTE PROCEDURE update_geom();
>
> -- 
> Otto
>
>
>
> On 1/24/07, * Mark Cave-Ayland* <mark.cave-ayland at ilande.co.uk 
> <mailto:mark.cave-ayland at ilande.co.uk>> wrote:
>
>     On Wed, 2007-01-24 at 12:40 +0100, Veronique Chaffard wrote:
>     > I would like to insert the geometry field of a record by using a
>     trigger
>     > and a fonction, after the insertion of the other fields;The X and Y
>     > coordinates are saved into 2 fields: LAT and LONG. The geometry
>     field
>     > type is a point.
>     >
>     > My table is declared as:
>     CREATE TABLE TEST (
>     >     ID serial not null,
>     >     NOM VARCHAR(50) not null,
>     >     LONG FLOAT8 not null,
>     >     LAT  FLOAT8 not null,
>     > constraint PK_TEST primary key (ID)
>     > );
>     > SELECT AddGeometryColumn('','test','geom','-1','POINT',2);
>     >
>     > Could you help me ?
>     >
>     > Thanks in advance.
>
>
>     Hi Veronique,
>
>     You don't need to store latitude and longitude as separate fields as
>     they are automatically stored as part of the geometry. So you can
>     create
>     your table and insert fields like this:
>
>     CREATE TABLE test (
>         ID serial not null,
>         NOM VARCHAR(50) not null
>         constraint PK_TEST primary key (ID)
>     );
>     SELECT AddGeometryColumn('','test','geom','-1','POINT',2);
>
>     INSERT INTO test (nom, geom) VALUES ('test1', MakePoint(LONG1, LAT1));
>     INSERT INTO test (nom, geom) VALUES ('test2', MakePoint(LONG2, LAT2));
>     INSERT INTO test (nom, geom) VALUES ('test3', MakePoint(LONG3,
>     LAT3));
>
>
>     You can view the complete table like this:
>
>     SELECT AsText(geom) FROM test;
>
>     And you can extract the latitude and longitude as separate fields for
>     your application like this:
>
>     SELECT id, nom, X(geom) AS long, Y(geom) AS lat FROM test;
>
>     Finally, I would also recommend that if you are new to
>     PostgreSQL/PostGIS that you keep all table and column names in lower
>     case, since PostgreSQL quoting is slightly different than you may
>     expect.
>
>
>     Kind regards,
>
>     Mark.
>
>
>     _______________________________________________
>     postgis-users mailing list
>     postgis-users at postgis.refractions.net
>     <mailto:postgis-users at postgis.refractions.net>
>     http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>   


-- 
Véronique Chaffard

IRD / LTHE
BP 53
38 041 Grenoble Cedex 09

Tel : +33 (0)4 76 82 50 94
Email : veronique.chaffard at hmg.inpg.fr 
<mailto:veronique.chaffard at hmg.inpg.fr>