sql - PostgreSQL Transform Geometries

Question

I have a table full of Easting/Northing points that I want to transform into a column in SRID:27700. I'm using Postgres with postgis installed.

I'm trying this:

alter table ua
add column location geometry(point,27700);

UPDATE ua 
    SET "location" = 'SRID=27700;POINT(' || ua."Easting" || ' ' || ua."Northing" || ')';

Of course this returns the locations in easting/northing, but when I try to first transform the points using ST_Transform, it returns

SQL Error [42883]: ERROR: function st_transform(character varying, integer) does not exist

I don't know how to get beyond this!

question from:https://stackoverflow.com/questions/65851078/postgresql-transform-geometries

Answer 1

ST_Transform gets one of these set of params and none of them gets character as the first param type whereas you are passing character as first param and int as second param!

geometry ST_Transform(geometry g1, integer srid);
geometry ST_Transform(geometry geom, text to_proj);
geometry ST_Transform(geometry geom, text from_proj, text to_proj);
geometry ST_Transform(geometry geom, text from_proj, integer to_srid);

I think this should work :

update ua 
set "east/north" = ST_GeomFromText('POINT(' || ua."Easting" ||' '|| ua."Northing" || ')',27700)