Convert from EPSG:4326 to UTM in PostGIS

Question

I would like to convert from EPSG:4326 to UTM (30N/EPSG:32630 or 29N/EPSG:32629) in PostGIS. I do the following query but I get wrong results:

SELECT ST_AsText(ST_Transform(ST_GeomFromText('POINT(36.5277099609375 -5.86424016952515)',4326),32630)) As check;

I get "POINT(5262418.33128724 -839958.963432011)" when it should be something approximate to 243625.00,4046330.00 in UTM 30N. If I do the conversion from 4326 to UTM I get the right result but not from UTM to 4326.

• What's wrong with the query?
• And are there anyway to get the UTM timezone from the coordinates in EPSG:4326 because I don't know if they belong to 30N or 29N?

Answer 1

1) Your query is correct but you coordinates are inverted. The correct coordinates order in the WKT format is POINT(x y), also POINT(longitude latitude)

This query give you the expected result:

SELECT ST_AsText(ST_Transform(ST_GeomFromText('POINT(-5.86424016952515 36.5277099609375)',4326),32630)) As check;

2) To get the UTM zone from a lat/long geometry you can use this formula:

ST_X(input_geometry)+180)/6)+1

with some adjustments.

For this purpose we use this function:

CREATE OR REPLACE FUNCTION get_utmzone(input_geom geometry)
  RETURNS integer AS
$BODY$
DECLARE
   zone int;
   pref int;
BEGIN
   IF GeometryType(input_geom) != 'POINT' THEN
     RAISE EXCEPTION 'Input geom must be a point. Currently is: %', GeometryType(input_geom);
   END IF;
   IF ST_Y(input_geom) >0 THEN
      pref:=32600;
   ELSE
      pref:=32700;
   END IF;
   zone = floor((ST_X(input_geom)+180)/6)+1;
   RETURN zone+pref;
END;
$BODY$
LANGUAGE plpgsql IMMUTABLE;

Use it with this query:

SELECT get_utmzone(ST_GeomFromText('POINT( -5.86424016952515 36.5277099609375)',4326));

The result should be 32630