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.
- You would set the projection of that record imported using the lat/long fields
- then reproject it using ST_TRANSFORM
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?