PostGIS - Create view (or materialised view) of existing polygons reprojected to Lat/Long (4326)

Question

I'm fairly new to SQL and have a set of existing PostGIS layers in our country coordinate system (EPSG: 2193).

I would like to create a view (or materialised view...but initially a simple view until I become better versed in SQL to generate a trigger) which simply re-projects this layer to EPSG 4326 (WGS84) for use in external software which only accepts geographic coordinate system data/4326.

I've not been able to find any examples to do this (most are related to re-projecting one geometry or geography. How can I do this?

CREATE OR REPLACE VIEW testview AS
FROM "Forests" 
SELECT st_transform("Forests".geom,4326) as geom
WHERE "Forests".forest_id = 'F_1'

Answer 1

From my experience it's better to create new table with reprojected geometry, than every time you open view call reprojection function. Add trigger to syncronize data in both tables and don't edit new table manualy.

Create table:

create table "Forest_wgs84"
(  forest_id varchar not null,
   geom geometry(Polygon, 4326)
)

Create trigger function:

CREATE OR REPLACE FUNCTION forest_to_wgs84()
  RETURNS trigger AS
$BODY$
begin
  if (TG_OP = 'UPDATE') then
    UPDATE "Forest_wgs84" 
    SET geom = ST_Transform(NEW.geom, 4326)
    WHERE forest_id = NEW.forest_id;
    RETURN NEW;
  elsif (TG_OP = 'DELETE') then
    DELETE from "Forest_wgs84" 
    WHERE forest_id = OLD.forest_id;
    RETURN OLD;
  elsif (TG_OP = 'INSERT') then
    insert into "Forest_wgs84" (forest_id, geom)
    values (forest_id, ST_Transform(NEW.geom, 4326));
    RETURN NEW;
  end if;
END;
$BODY$

Create trigger on main table:

CREATE TRIGGER "Sync_Forest"
  AFTER INSERT OR UPDATE OR DELETE
  ON "Forest"
  FOR EACH ROW
  EXECUTE PROCEDURE forest_to_wgs84();

Answer 2

Make sure the FROM comes after the SELECT.

Also, while not mandatory, you should cast the output geometry to its specific type/projection so the column is not of the "simple" type geometry but rather geometry(polygon, 4326) which can speed up many software consuming the view.

CREATE OR REPLACE VIEW testview AS
  SELECT uid,
         st_transform("Forests".geom,4326)::geometry(polygon, 4326) as geom
  FROM "Forests" 
  WHERE "Forests".forest_id = 'F_1';