Postgis query to convert UTM to Lat long

Question

I want Postgres/PostGIS queries to convert UTM coordinates from UTM 43N projection to Decimal Degrees 32643 projection.

select ST_X(the_geom), ST_X(the_geom) from table

Answer 1

You need to use ST_Transform. For example, taking a point somewhere in Southern India (which I'm guessing from the UTM zone and your profile is the area you are interesting in)

SELECT ST_X(geom), ST_Y(geom) 
FROM 
(SELECT  ST_Transform(ST_SetSrid(ST_MakePoint(627516, 1413990), 32643), 4326) as geom) g;

or in your case, if you have a table already with coordinates in:

SELECT col1, col2, ST_X(the_geom), ST_Y(the_geom)
FROM
(SELECT col1, col2, ST_Transform(the_geom, 4326) as the_geom from sometable) g;

If you don't have your geometry column's SRID set to 32643, you can either wrap this in ST_SetSRID, as in the first example, or run UpdateGeometrySRID first to put your table's geometry column in the correct projection -- good practice anyway, to have this explicitly set.