3

I have a polygon layer which is in UTM 39N as x and y coordinates. I need to convert it to EPSG:3857 and for this I used ST_Transform.

select st_transform(geom,3857) from block_1

But when I get back the geometry it shows like this and seems it's not changed. What is the reason?

select st_asewkt(geom) from block_1; 
"SRID=32639;POLYGON((743220 3414989.46519139,743182.75599886 3414997.93702281,743191.80859886 3415037.2547228,743220 3415030.96885788,743220 3414989.46519139))"
"SRID=32639;POLYGON((743220 3415038.0805396,743192.28249886 3415044.05802281,743198.19829886 3415071.27492281,743220 3415066.70501294,743220 3415038.0805396))"
"SRID=32639;POLYGON((743187.55809886 3415076.14972281,743181.28999886 3415049.4555228,743070 3415073.18330527,743070 3415099.39876062,743187.55809886 3415076.14972281))"
"SRID=32639;POLYGON((743180.82119886 3415039.76002281,743173.79019886 3415001.9225228,743086.54129886 3415021.46822281,743092.68279886 3415059.36532281,743180.82119886 3415039.76002281))"

Also when I get one of the features from transformation result, it returns right statement.

"SRID=3857;POLYGON((5960397.2315521 3612397.86500845,5960354.14066292 3612408.75798758,5960365.71012538 3612454.47133512,5960398.33073199 3612446.37415711,5960397.2315521 3612397.86500845))"
5

What you're doing with your first statement select st_transform(geom,3857) from block_1 is selecting all the rows, transformed to a new CRS.

If you do this, you'll note that it is in your expected CRS (as you note) select st_asewkt(st_transform(geom,3857)) from block_1;

But, you haven't changed the underlying data in either of these processes!

You have two options:

  1. Create a view create view v_block_1_3857 as select st_transform(geom,3857) from block_1; - note that this is permanent, but dynamic and may be slow. To get data out of this, you can select from the view select st_aswkt(geom) from v_block_1_3857
  2. Create a table from your select statement create table block_1_3857 as select st_transform(geom,3857) from block_1; - this is also permanent, but can have an index added to make it fast. Updating data can be done by either deleting the table, or truncating it first then inserting your data again.

TL;DR: your select statement doesn't change the data. To make it permanent, you need to change the stored data in the table.

EDIT: Option 3 is to do it semi-in place:

ALTER block_1 ADD COLUMN geom_too geometry(Geometry,3857);

UPDATE block_1 SET geom_too = st_transform(geom, 3857);

-- TEST HERE FIRST!!!
ALTER TABLE block_1 DELETE geom;

ALTER TABLE block_1 RENAME geomo_too TO geom;
  • 1
    It is possible to update my table to something like this: update block_1 set geom = st_transform(geom, 3857)? – nasser tahani Jul 10 '17 at 6:56
  • Yes that is possible, it's the third option... I was missing that one too. – pLumo Jul 10 '17 at 7:27
  • 1
    But it throws an error indicates that the SRID of geometry column is not 3857. – nasser tahani Jul 10 '17 at 7:47
  • 1
    Hi @nassertahani, doing it in place is a bit more tricky. You may want to do it in stages, creating a new column in the other SRID. Then delete the original when it's successful. I'll add to my list of options. – Alex Leith Jul 10 '17 at 23:29

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.