How to make projected geometry and update it to postgresql?

Question

I have an excel file containing some unprojected geographical coordinate values such as:

Time,Lat,Lon,Alt
2017:11:21:13:44:07:132,61.8984838,24.2221901,165

I wrote a java code to store the data to postgresql database. Now, my postgres database contains a table named 'info' which contains all the data from excel file. I also added a gid column to give unique serial number for all data and a geometry column to my database with null value. I want to put projected UTM value to that geometry column. My required UTM zone is WGS 84 UTM 35N and its EPSG code is 32635. Is st_transform able to do this projection? My sample query is here:

Update info set geom = sq.geom 
from (
    select ST_Transform(geom,32635) as geom
    from (
        SELECT ST_setsrid(geom_v,4326) as geom 
        from (
            select ST_MakePoint(lon,lat,alt) as geom_v 
            from (
                select latitude as lat, longitude as lon, altitude as alt from info order by gid asc
            ) as fpp
        )as ftt
    ) as tr
) as sq

I added st_setsrid as without this, postgres was returning "undefined 0 srid values" error. I set the srid to 4326 just for an example. It could be any valid srid. Anyway the query is filling the geom column with same value for each row. Such as:

geom
geometry
'01010000207B7F000026218A29179C154199C0063B12315A41'
'01010000207B7F000026218A29179C154199C0063B12315A41'
'01010000207B7F000026218A29179C154199C0063B12315A41'
'01010000207B7F000026218A29179C154199C0063B12315A41'
'01010000207B7F000026218A29179C154199C0063B12315A41'
'01010000207B7F000026218A29179C154199C0063B12315A41'
'01010000207B7F000026218A29179C154199C0063B12315A41'
'01010000207B7F000026218A29179C154199C0063B12315A41'
'01010000207B7F000026218A29179C154199C0063B12315A41'
'01010000207B7F000026218A29179C154199C0063B12315A41'

But if I try this:

select ST_Transform(geom,32635) as geom
from (
        SELECT ST_setsrid(geom_v,4326) as geom 
        from (
            select ST_MakePoint(lon,lat,alt) as geom_v 
            from (
                select latitude as lat, longitude as lon, altitude as alt from info order by gid asc limit 10
            ) as fpp
        )as ftt
) as tr

Then, it gives 10 different geometry values. Such as:

geom
geometry
'01010000207B7F000026218A29179C154199C0063B12315A41'
'01010000207B7F0000463A245D169C1541B86D933712315A41'
'01010000207B7F0000629183D5149C1541EBB6462A12315A41'
'01010000207B7F00007ECE9CA0139C15418EC6E61712315A41'
'01010000207B7F000064C89D65129C15415AD97AF911315A41'
'01010000207B7F0000B0693E76119C1541291311E011315A41'
'01010000207B7F0000F868ED7D109C154120819BC411315A41'
'01010000207B7F00003A478EDD0F9C1541E15E44AD11315A41'
'01010000207B7F00003A478EDD0F9C1541E15E44AD11315A41'
'01010000207B7F00003A478EDD0F9C1541E15E44AD11315A41'

Why this?

Answer 1

As user30184 outlined: It's a very common task, and there's plenty of documentation: ST_Transform.

To apply this, you need to figure out the EPSG codes of your projections.

UTM 35N probably is EPSG:32635, and your lat/lon coordinates could be anything. One of the more common ones is WGS84 as used in GPS with code EPSG:4326

The Postgis query then would be

SELECT ST_Transform(geom, 4326,32635)

If your postgis table already has the proper source SRID, you can do a simpler

SELECT ST_Transform(geom, 32635)

---

EDIT after you updated your question a lot:

The issue is not with ST_Transform, the issue is your query. You explicitly set all geometries to be the same. This is how UPDATE in combination with subqueries works. I really recommend you dig through the documentation a bit more to grasp this.

The solution to this issue can be found in the docs as well, by using a WHERE clause at the end. While not standard SQL, PostgreSQL allows UPDATE WHERE in order to apply subqueries to specific rows they match. In most cases, one has a unique identifier that you can use (just hand it down from the sub queries). In your case, depending on your data, you could use the timestamp or the lon/lat/alt itself to do so, as all these should match the same anyway.:

UPDATE info SET geom = sq.geom 
FROM (
    SELECT lon, lat,alt, ST_Transform(geom,32635) as geom
    FROM (
        SELECT lon,lat,alt, ST_setsrid(geom_v,4326) as geom 
        from (
            select lon,lat,alt, ST_MakePoint(lon,lat,alt) as geom_v 
            from (
                select latitude as lat, longitude as lon, altitude as alt from info order by gid asc
            ) as fpp
        )as ftt
    ) as tr
) as sq
WHERE info.lon = sq.lon AND info.lat = sq.lat AND info.alt = sq.alt

However, your entire query is unnecessarily convoluted (extremely so!). You could (and should) just use this:

UPDATE test SET geom = ST_Transform(ST_SetSRID(ST_MakePoint(lon,lat, alt),4326),32635)

to achieve the same result with a much faster and easier-to-read query.