0

I have imported a 508GB DB with unknown SRID with ogr2ogr, hence my geometry SRID is 900914.

Now I'd like to run

SELECT UpdateGeometrySRID('contours','wkb_geometry',3857);

However, I'm running low on disk space (~300GB left) and it seems that UpdateGeometrySRID consume everything before failing.

Is there a way to run things more progressively ?

Edit my own question with best (easiest) answer:

Re-run ogr2ogr with -a_srs epsg:3857 option and remember it for the next time.

5
  • 1
    If you still have the original data it is probably fastest to drop the table and import again with ogr2ogr -a_srs epsg:3857.
    – user30184
    Commented Sep 12, 2015 at 9:54
  • It already took a week to import, so if there is something that could take one day or two ...
    – yvecai
    Commented Sep 12, 2015 at 9:58
  • If only you spent a minute or two for thinking before you started the import :). Well, create a new geometry column into your table "geom_3857" and run update table set geom_3857= ST_Transform(geom, 3857) limit 100000 offset 0 SQL may not be right, but got the idea: update in batches defined by limit and offset. After update you can delete the original geometries and free data before the next batch.
    – user30184
    Commented Sep 12, 2015 at 10:05
  • By reading postgis.net/docs/UpdateGeometrySRID.html, isn't the last example for you ALTER TABLE roads ALTER COLUMN geom TYPE geometry(MULTILINESTRING, 3857) USING ST_Transform(ST_SetSRID(geom,4326),3857) ; Have you tried?
    – user30184
    Commented Sep 12, 2015 at 10:09
  • You may run into fragmentation and high-water mark issues by trying to add a second geometry column. When a table is that large, you really don't want it fragmented, and if it took a week to load, it's possible your server is under-powered (colleagues of mine loaded 630m rows of points w/ ~500 bytes of attributes in 20 hours several years ago)
    – Vince
    Commented Sep 12, 2015 at 12:05

1 Answer 1

-1

Run ogr2ogr with -a_srs option a next time.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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