PostGIS: Automatically updating geometry field in a table after updating lat long values

Question

It's simple to create a view from a table that generates a valid geometry field:

create view mywgs84points as 
select row_number() over (order by id) as gid,
st_setsrid(st_point(lon, lat), 4326)
from mytable

But what's the method for dynamically updating a geometry field in the actual table, rather than a view based on it?

Answer 1

The solution to this is using Triggers. PostgreSQL triggers monitor certain actions on tables and views, allowing you to set rules for what happens when those actions take place.

If the action is an insert or update of a lat or lon field, and the desired action is an update of a geom field, then the code for the trigger function is:

CREATE OR REPLACE FUNCTION update_geometries()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
        NEW.geom = st_setsrid(st_point(NEW.lon, NEW.lat), 4326);
                                        
        RETURN NEW;
END;
$$;

You could also take it a step further and also implement Admin 0 layer detection for the point, doing something like:

CREATE OR REPLACE FUNCTION update_geometries()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
        NEW.geom = st_setsrid(st_point(NEW.lon, NEW.lat), 4326);
        
        SELECT b.name
        INTO NEW.admin_0
        FROM admin_0_table b 
        WHERE st_intersects(NEW.geom,b.geom);
                                                
        RETURN NEW;
END;
$$;

You then just have to apply the trigger to the table you want to monitor:

CREATE TRIGGER 
tr_mytable_updategeom
BEFORE INSERT OR UPDATE of lat,lon on 
mytable_updategeom
FOR EACH ROW EXECUTE PROCEDURE update_geometries();