[GIS] PostGis: Geometry from UTM text

postgispostgresqlsridutm

I have plain data in a postgresql table which looks like this: 

╔════╦═══════╦═════════════╦══════════════╦══════════╦═════════════════╗
║ id ║ index ║   easting   ║   northing   ║ utm_zone ║ utm_zone_letter ║
╠════╬═══════╬═════════════╬══════════════╬══════════╬═════════════════╣
║  1 ║   1   ║ 233410.0000 ║ 1024701.0000 ║       38 ║ N               ║
║  1 ║   2   ║ 213310.0000 ║ 1167201.0000 ║       36 ║ N               ║
║  2 ║   1   ║ 213310.0000 ║ 1167201.0000 ║       31 ║ B               ║
║  2 ║   2   ║ 213310.0000 ║ 1167201.0000 ║       31 ║ B               ║
╚════╩═══════╩═════════════╩══════════════╩══════════╩═════════════════╝

Note: Coordinates are not real, but typed by hand

As you can see these are UTM coordinates but stored in plain db type fields.
I want to convert those last 4 columns to one of Geometry type for PostGis.
To something like this: 

╔════╦══════════════════════╗
║ id ║       Geometry       ║
╠════╬══════════════════════╣
║  1 ║ 02347237427342342347 ║
║  2 ║ 27584872345646325863 ║
╚════╩══════════════════════╝

I used query similar to this:


--creates polygon with wrong SRID :(
SELECT z.id, ST_Polygon(z.geom, 3395) geom FROM ( 
    --adds first point of line at the end to close it (requeird by ST_Polygon)
    SELECT l.id id, ST_AddPoint(g.geom, ST_StartPoint(g.geom)) geom FROM (
        -- groups points by ID and creates open line geometry from each group
        SELECT t.id id, ST_MakeLine(t.strPoints) geom FROM (
            --creates POINT(x,y) text from coordinates
            SELECT 
                g.id id, 
                g.index idx, 
                'POINT(' || g.easting || ' ' || g.northing || ')' strPoints
            FROM data g
            ORDER BY g.index
        ) t
        GROUP BY t.id
        HAVING count(t.strPoints) > 2
    ) g

) z
where ST_IsValid(z.geom)

But this does not use utm_zone and utm_zone_letter and points and lines are created without SRID value. Creating polygon from such values and specifing some SRID gives bad results. At same time because points and lines do not have SRID set I cannot use utmzone(geometry) DB function because it requires geometry to have SRID.

I looked at ST_Transform, ST_FromText and other functions but could not find one where I can specify UTM zone number and letter to create Geometry type value.
Creating point without specifying SRID gives me bad results – points fall on penguins in Antarctica which is wrong.

Can you tell me how to convert that data to correct Geometry values with correct SRID specified?

Best Answer

I think you should be able to use this function (from here):

 -- Function: utmzone(geometry)
 -- DROP FUNCTION utmzone(geometry);
 -- Usage: SELECT ST_Transform(the_geom, utmzone(ST_Centroid(the_geom)) )
    FROM sometable;

 CREATE OR REPLACE FUNCTION utmzone(geometry)
   RETURNS integer AS
 $BODY$
 DECLARE
     geomgeog geometry;
     zone int;
     pref int;

 BEGIN
     geomgeog:= ST_Transform($1,4326);

     IF (ST_Y(geomgeog))>0 THEN
        pref:=32600;
     ELSE
        pref:=32700;
     END IF;

     zone:=floor((ST_X(geomgeog)+180)/6)+1;

     RETURN zone+pref;
 END;
 $BODY$ LANGUAGE 'plpgsql' IMMUTABLE
   COST 100;
Related Question