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

[Otto Laurila]

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> 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
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20070124/f8782c55/attachment.html>