[postgis-users] On UpdateGeometrySRID() performance

[jerry73204]

Thanks for your reply.

I missed EWKT trick so much. `sed` before copy may save more time.

To Darafei's question: I simply google://"postgis change srid" to find 
out UpdateGeometrySRID.

Jerry Lin

On 11/2/18 12:51 AM, Darafei "Komяpa" Praliaskouski wrote:
> Hi,
>
> Please feed in your WKT polygons in EWKT format:
>
> SRID=4326;POLYGON(...)
>
> Since every update in Postgres is essentially Delete+Insert, time of 
> rewriting each and every row being equal to initial Insert time is 
> expected thing.
>
> You can also update SRID in two columns in one go:
>
> update tablename set geom1 = ST_SetSRID(geom1, 4326), geom2 = 
> ST_SetSRID(geom2, 4326);
>
> Out of curiosity, where did you learn about UpdateGeometrySRID before 
> learning about ST_SetSRID?
>
> ср, 31 окт. 2018 г. в 21:57, jerry73204 <jerry73204 at gmail.com 
> <mailto:jerry73204 at gmail.com>>:
>
>     Hi all,
>
>     I'm stuck in the low performance of UpdateGeometrySRID().
>
>     I get started with a 50GB polygon dataset in CSV in EPSG:4326
>     coordinates. Since I find no way to `\copy` the csv while
>     preserving the
>     SRID, the data is imported with null SRID and then `SELECT
>     UpdateGeometrySRID('table', 'column', 4326)`.
>
>     The `UpdateGeometrySRID()` takes as long time as that of `\copy`,
>     which
>     turns out to be approx two hours. The dataset has two geometry
>     columns
>     and thus I have to take triple time to finish this data.
>
>     I profiled the postgresql daemon. The avg disk writing speed is
>     30MB/s,
>     while occasionally peaks to 100MB/s. The SSD, F2FS formatted disk is
>     capable of up to 150MB/s. The daemon does not utilize the 4-core
>     i5-7600k CPU. It seems to be a single process task with avg CPU load
>     20%, while other workers are idle. I wonder if there's a room for
>     improving the performance. Also, I'm looking for if it's possible to
>     preserve SRID with `\copy`.
>
>     Jerry Lin
>
>     _______________________________________________
>     postgis-users mailing list
>     postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
>     https://lists.osgeo.org/mailman/listinfo/postgis-users
>
> -- 
> Darafei Praliaskouski
> Support me: http://patreon.com/komzpa
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20181102/b947e209/attachment.html>