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 geom with an SRID of 3734; that is, a point geometry type in two dimensions. Since we have x, y, and 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 the_geom = ST_SetSRID(ST_MakePoint(x,y), 3734); 

The query here is simple when broken down. We update the xwhyzed1 table 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 new populated geometry function using the following query:

CREATE OR REPLACE FUNCTION chp02.before_insertXYZ() 
  RETURNS trigger AS 
$$ 
BEGIN 
 
if NEW.geom is null then 
   NEW.geom = ST_SetSRID(ST_MakePoint(NEW.x,NEW.y), 3734); 
end if; 
RETURN NEW; 
END; 
 
$$ 
LANGUAGE 'plpgsql'; 

In essence, we have created a function that does exactly what we did manually: update the table's geometry column with the combination of ST_SetSRID and ST_MakePoint, but only to the new registers being inserted, and not to all the table.