PostGIS: Trigger for Location

You have a table of Point Locations (not MultiPoint) in PostgreSQL/PostGIS.

  • The table is named meters
  • The table is stored in a schema named water.
  • The data is stored in a standard state plane projection
  • You have two fields: Latitude and Longitude
  • Your Geometry Column is geom

–Function–cut and paste below

CREATE OR REPLACE FUNCTION update_location_func()
RETURNS TRIGGER AS ‘
BEGIN
NEW.longitude := st_x(st_transform(NEW.geom, 4326));
NEW.latitude := st_y(st_transform(NEW.geom, 4326));
RETURN NEW;
END;
‘ language ‘plpgsql’;

–end cut and paste above

–Trigger–cut ans paste below

CREATE TRIGGER update_watermeter_location BEFORE insert or update
ON water.metersĀ FOR EACH ROW EXECUTE PROCEDURE
update_location_func();

–end cut and paste above

What happens? If you notice the function is doing two things. One – it’s recording the location. Two – it’s “reprojecting” the location to 4326 (WGS84). The trigger is being issued anytime the the point is inserted or updated. If you’re concerned with location and you want to save the data in Decimal Degrees that’s exactly how you would handle it.

What if your data is in WGS 84 OR you actually want the location saved as the Local Coordinate System?

–Function–cut and paste below

CREATE OR REPLACE FUNCTION update_location_func()
RETURNS TRIGGER AS ‘
BEGIN
NEW.longitude := st_x(NEW.geom);
NEW.latitude := st_y(NEW.geom);
RETURN NEW;
END;
‘ language ‘plpgsql’;

–end cut and paste above

Use the above and just re-use the trigger. You’re now Automatically recording locations in your database.