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

[Mark Cave-Ayland]

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.