Force coordinate precision in QGIS when saving feature to PostGIS

Is it possible to configure QGIS in a way, that one can specifically set the desired coordinate precision which is saved for each vertex/point when writing to a PostGIS layer? I want to achieve that WGS84 coordinates are saved as

POINT(0.06288 0.24537)

instead of

POINT(0.0628843267570899 0.24537407891492)

Changing the coordinate display setting of the project does not do the trick.

Answer

You can do this by adding a trigger to the table you're editing. First, you can define a generic snapping trigger:

CREATE OR REPLACE FUNCTION snap_coords ()
RETURNS TRIGGER AS $$
BEGIN
    NEW.geom := ST_SnapToGrid(NEW.geom, TG_ARGV[0]::float);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

You could have this function snap to a fixed precision (such as 1e-5), but this example allows a precision to be passed as an argument when the trigger is bound to a table.

Next, run a CREATE TRIGGER statement to bind the trigger function to the table:

CREATE TABLE test (id serial, geom geometry);

CREATE TRIGGER test_snap_coords
BEFORE INSERT OR UPDATE ON test
FOR EACH ROW EXECUTE PROCEDURE snap_coords (1e-5);

Now, we can verify that geometries stored to test are indeed snapped:

INSERT INTO test(geom) VALUES ('POINT(0.0628843267570899 0.24537407891492)'::geometry)

SELECT ST_AsText(geom) FROM test;
-- POINT(0.06288 0.24537)

A caveat

You might be puzzled by the result of the following query:

SELECT ST_Equals(geom, 'POINT(0.06288 0.24537)') FROM test;
-- false

The ST_SnapToGrid function does not always produce coordinates that are consistent with those produced by a text parser of coordinates. To try and clarify by way of example, consider the following point:

POINT (0 1.5234567)

If we parse this point and store it as a PostGIS geometry, we'll get the following binary geometry:

010100000000000000000000000EF4F5211460F83F

Snapping this point to 1e-7 should be a no-op, right? But ST_SnapToGrid('POINT (0 1.5234567)') returns a different binary value:

010100000000000000000000000DF4F5211460F83F

Note that the first E in the binary string has become an F. Both of these are "correct" in the sense that you can't store 1.5234567 exactly as a floating point number, and both of the stored coordinates are very close to (0, 1.5234567). Indeed, calling ST_AsText will return the same text string for both.

However, if your application/database consumes text coordinates from other sources, you might want the stored coordinates to be consistent with the text representation. In this case, the trigger should actually force the coordinates through a round-trip to text.

CREATE OR REPLACE FUNCTION snap_coords ()
RETURNS TRIGGER AS $$
BEGIN
    NEW.geom := ST_AsEWKT(ST_SnapToGrid(NEW.geom, TG_ARGV[0]::float));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Now, running the test query again, we can see that our stored coordinate is consistent with the text representation.

SELECT id, ST_Equals(geom, 'POINT(0.06288 0.24537)') FROM test;

 id | st_equals 
----+-----------
  3 | f
  4 | t