0

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?

  • 1
    Read about ST_Transform. – user30184 Jan 11 '18 at 5:42
  • I think st_transform changes my srid. If it is in srid 4326, then by transform I can convert it to some other srid. But can I convert lat lon to utm with this? – LSG Jan 11 '18 at 6:12
  • Yes, see the first example in postgis.net/docs/ST_Transform.html. But of course you must know what you have and what you want to have and the corresponding SRID codes or Proj4 strings. It is a common task and you will for sure find good examples. – user30184 Jan 11 '18 at 7:13
5

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.

  • So, st_transform projects the unprojected geographical coordinates? I am still confused. – LSG Jan 11 '18 at 21:48
  • So, this query will work for me? It is creating geometry but updating same value to all columns. Why? Update track_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) as geom_v from ( select latitude as lat, longitude as lon from track_info order by gid asc ) as fpp )as ftt ) as tr ) as sq – LSG Jan 12 '18 at 2:47
  • If you need more help, you need to give way more information than you're currently giving in your question. It's hard to gauge what you are trying to accomplish from these tidbits of information you give. You are doing way more than just doing a Transform in your query, and you haven't included that in your question. – Senshi Jan 12 '18 at 11:40
  • I completely changed the question. Now please check @Senshi – LSG Jan 12 '18 at 18:08
  • And I updated the answer to match it @LSG :) – Senshi Jan 16 '18 at 12:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.