Getting ready

We will start by creating another table of random points with x, y, and z values, as shown in the following query:

DROP TABLE IF EXISTS chp02.xwhyzed1 CASCADE;
CREATE TABLE chp02.xwhyzed1
(
 x numeric,
 y numeric,
 z numeric
)
WITH (OIDS=FALSE);
ALTER TABLE chp02.xwhyzed1 OWNER TO postgres;
ALTER TABLE chp02.xwhyzed1 ADD COLUMN gid serial;
ALTER TABLE chp02.xwhyzed1 ADD PRIMARY KEY (gid);

INSERT INTO chp02.xwhyzed1 (x, y, z)
 VALUES (random()*5, random()*7, random()*106);
INSERT INTO chp02.xwhyzed1 (x, y, z)
 VALUES (random()*5, random()*7, random()*106);
INSERT INTO chp02.xwhyzed1 (x, y, z)
 VALUES (random()*5, random()*7, random()*106);
INSERT INTO chp02.xwhyzed1 (x, y, z)
 VALUES (random()*5, random()*7, random()*106);

How to do it...

Now we need a geometry column to populate. By default, the geometry column will be populated with null values. We populate a geometry column using the following query:

SELECT AddGeometryColumn ('chp02','xwhyzed1','geom',3734,'POINT',2);

We now have a column called the_geom with an SRID of 3734, that is, a point geometry type in two dimensions. Since we have x, y, z data, we could, in principle, populate a 3D point table using a similar approach.

Since all the geometry values are currently null, we will populate them using an UPDATE statement as follows:

UPDATE chp02.xwhyzed1
 SET geom = ST_SetSRID(ST_MakePoint(x,y), 3734);

The query here is simple when broken down. We update the table xwhyzed1 and set the the_geom column using ST_MakePoint, construct our point using the x and y columns, and wrap it in an ST_SetSRID function in order to apply the appropriate spatial reference information. So far we have just set the table up. Now we need to create a trigger in order to continue to populate this information once the table is in use. The first part of the trigger is a newly populated geometry function using the following query:

CREATE OR REPLACE FUNCTION chp02.xyz_pop_geom()
 RETURNS TRIGGER AS $popgeom$

BEGIN
 IF(TG_OP='INSERT') THEN

 UPDATE chp02.xwhyzed1
 SET geom = ST_SetSRID(ST_MakePoint(x,y), 3734)
 WHERE geom IS NULL
 ;

 END IF;
 RETURN NEW;
END;

$popgeom$ LANGUAGE plpgsql;

In essence, we have created a function that does exactly what we did manually: it updates the table's geometry column with the combination of ST_SetSRID and ST_MakePoint. The one exception here is that we've added a WHERE clause that allows us to apply this only to rows that have no geometry populated. This is the performant choice.

There's more...

While we have a function created, we have not yet applied it as a trigger to the table. Let us do that here as follows:

CREATE TRIGGER popgeom_insert
 AFTER INSERT ON chp02.xwhyzed1
 FOR EACH STATEMENT EXECUTE PROCEDURE chp02.xyz_pop_geom();

Now, any inserts into our table should be populated with new geometry records. Let us do a test insert using the following query:

INSERT INTO chp02.xwhyzed1 (x, y, z)
 VALUES (random()*5, random()*7, random()*106); 

CREATE OR REPLACE FUNCTION chp02.xyz_pop_geom()
 RETURNS TRIGGER AS $popgeom$

BEGIN
 IF(TG_OP='INSERT') THEN

 UPDATE chp02.xwhyzed1
 SET geom = ST_SetSRID(ST_MakePoint(x,y), 3734)
 WHERE geom IS NULL
 ;

 ELSIF(TG_OP='UPDATE') THEN
 UPDATE chp02.xwhyzed1
 SET geom = ST_SetSRID(ST_MakePoint(x,y), 3734)
 ; 

 END IF;
 RETURN NEW;
END;
$popgeom$ LANGUAGE plpgsql;

CREATE TRIGGER popgeom_insert
 AFTER INSERT ON chp02.xwhyzed1
 FOR EACH ROW
 EXECUTE PROCEDURE chp02.xyz_pop_geom();

CREATE TRIGGER popgeom_update
 AFTER UPDATE ON chp02.xwhyzed1
 FOR EACH ROW
 WHEN (OLD.X IS DISTINCT FROM NEW.X AND OLD.Y IS DISTINCT FROM NEW.Y)
 EXECUTE PROCEDURE chp02.xyz_pop_geom();

See also