2

I have a postgres/postgis database.

I would like to reproject those data coming to me in 4326 to 32736.

For those tables with lat long info in wgs84 (4326) I have set the geom to 4326. Then I have copied the table and used transform to reproject to 32736.

CREATE TABLE household.a_household_utm AS SELECT * FROM household.a_household;

ALTER TABLE household.a_household_utm 
    ALTER COLUMN geom TYPE geometry(Point,32736) USING ST_Transform(geom,32736);

This means that I will have to repeat this every time new data comes in if I want the dataset to be in 32736.

What is the best practice approach for re-projecting all data that is streaming in. Is a trigger function the best? I would re-project each new record on insert/update? I am not sure how I would do this.

  1. You would set the projection of that record imported using the lat/long fields
  2. then reproject it using ST_TRANSFORM

something like?

CREATE OR REPLACE FUNCTION hh_geom_lon_lat() RETURNS trigger AS  
$$
BEGIN  
  NEW.geom = ST_Transform(
    ST_GeomFromText('POINT(' || NEW.longitude || ' ' || NEW.latitude || ')', 4326),
  32736);
  RETURN NEW;
END;  
$$
LANGUAGE plpgsql VOLATILE;

how would you set the table projection initially?

  • 1
    How are you inserting the data. There is nothing wrong with triggers, but you could also do the transform directly as part of the insert. Note, you can use ST_SetSrid(ST_MakePoint(x,y), srid) instead of ST_GeomFromText with text concatenation. Nothing wrong with what you have done, but it avoids all the ||. – John Powell Nov 13 '14 at 16:40

Your Answer

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

Browse other questions tagged or ask your own question.