0

I have a postgis table that is edited from different sources.

  • The points added from Source 1 are in SRID 23030.

  • The points added from Soruce 2 are in SRID 4230.

I want all the points in the table to be stored in SRID 23030.

I can create a trigger and st_transform(the_geom,23030) all NEW.the_geom but i was wondering is this is the easiest way to achieve this.

Maybe i can create a CONSTRAINT or something that reprojects (st_transforms) every point if SRID it's not in 23030 or something like that?

Any suggestion?

Regards,

  • 1
    You mean you are constantly inserting data in one of the other SRID -- your trigger idea suggests this?. A constraint will prevent you from inserting data that is not in correct SRID, it will not actually reproject. Can you not just use ST_Transform on the insert, rather than relying on triggers? – John Powell Feb 20 '15 at 12:03
  • What do you mean on the insert? If i want to manipulate the data inserted in the table before it is inserted i can only think of triggers, i don't know what you mean... – Egidi Feb 20 '15 at 12:45
  • 1
    Your question wasn't clear as to whether the data was already in the db and/or you were inserting more (as you mentioned constraints). Otherwise, yes, you comment about triggers would make sense. So, why not just wrap your inserts in ST_Translate on the way in? Triggers are a bit of a hidden way to do this. My personal view is that having mixed SRIDs in a table is a terrible idea, and you should put a constraint on enforcing 23030, no matter what you do with triggers/inserts. – John Powell Feb 20 '15 at 12:50
  • I agree with you in that, but i can't change the insert on the way in..i just can manipulate data once it is in the database (before insert or whatever) – Egidi Feb 20 '15 at 13:21
  • So how do you know what the SRID is? By the coordinate range. It does then sound like triggers are the way to go. – John Powell Feb 20 '15 at 13:53
2

I think that if the transformation can not be handled on the client side, a trigger is the way to go. It's a simple approach and it will work for your case.

CREATE OR REPLACE FUNCTION myschema.transform () RETURNS trigger AS $$
BEGIN
    NEW.geom = ST_Transform(NEW.geom, 23030);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER transform
       BEFORE INSERT OR UPDATE ON myschema.mytable
       FOR EACH ROW
       EXECUTE PROCEDURE myschema.transform();

Note, that check for the actual projections is no need, you can safely call ST_Transform on a geometry that is already in the target projection.

Maybe if you have a lot of inserts or updates, and few selects or depending on your exact user cases another approach will be useful. For example, allow having geometries with mixed srid in the table and having a SELECT RULE to make the transformation on the fly. But I don't like this as I think that usually is better have constraints a typemods to keep data integrity.

  • Is this even possible if you have an existing SRID constraint or typemods? – Stev_k Apr 3 '19 at 11:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.