I have some vector layers in Oracle and PostGIS, point, line and polygon, and i want to reproject those layer in a different projection but i don't want to export the projected layer, i want to do it on the original layer because i have related tables with the feature ID. I know about on the fly projection but i want to permanent transform the data.
1 Answer
In both databases you can't transform into the same column, if the column is registered. Both databases hold the projection srid inside the geometry.
If you insert a geometry with a different projection, maybe you break a constraint or maybe the spatial index rebuild failed.
Best way is write the transformation result into a new table or column. Check the result. Delete the spatial index, De-register the original column. Update the new geometries to the original column. Register the column and create a new spatial index.
answered Jul 5, 2017 at 14:06
-
Have you tried this before and in which database type? Commented Jul 5, 2017 at 15:56
-
I wrote years ago an import/export service for Oracle, who used a staging table with two geometry columns, for auto correction of the incoming geometries. That's equivalent to your use case. My experience with postgis, say it's the same her. Commented Jul 5, 2017 at 20:21
ST_TRANSFORMin an update statement?