<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body text="#000000" bgcolor="#FFFFFF">
<p>Thanks for your reply.<br>
<br>
I missed EWKT trick so much. `sed` before copy may save more time.<br>
<br>
To Darafei's question: I simply google://"postgis change srid" to
find out UpdateGeometrySRID.<br>
<br>
Jerry Lin<br>
<br>
</p>
<div class="moz-cite-prefix">On 11/2/18 12:51 AM, Darafei "Komяpa"
Praliaskouski wrote:<br>
</div>
<blockquote type="cite"
cite="mid:CAC8Q8tLqhob-7JKzuvS_7chNv2iQnCwYuj=U-fG5AxAZ0kqRCg@mail.gmail.com">
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<div dir="ltr">Hi,
<div><br>
</div>
<div>Please feed in your WKT polygons in EWKT format:</div>
<div><br>
</div>
<div>SRID=4326;POLYGON(...)</div>
<div><br>
</div>
<div>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.</div>
<div><br>
</div>
<div>You can also update SRID in two columns in one go:</div>
<div><br>
</div>
<div>update tablename set geom1 = ST_SetSRID(geom1, 4326), geom2
= ST_SetSRID(geom2, 4326);</div>
<div><br>
</div>
<div>Out of curiosity, where did you learn about
UpdateGeometrySRID before learning about ST_SetSRID?</div>
</div>
<br>
<div class="gmail_quote">
<div dir="ltr">ср, 31 окт. 2018 г. в 21:57, jerry73204 <<a
href="mailto:jerry73204@gmail.com" moz-do-not-send="true">jerry73204@gmail.com</a>>:<br>
</div>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">Hi all,<br>
<br>
I'm stuck in the low performance of UpdateGeometrySRID().<br>
<br>
I get started with a 50GB polygon dataset in CSV in EPSG:4326
<br>
coordinates. Since I find no way to `\copy` the csv while
preserving the <br>
SRID, the data is imported with null SRID and then `SELECT <br>
UpdateGeometrySRID('table', 'column', 4326)`.<br>
<br>
The `UpdateGeometrySRID()` takes as long time as that of
`\copy`, which <br>
turns out to be approx two hours. The dataset has two geometry
columns <br>
and thus I have to take triple time to finish this data.<br>
<br>
I profiled the postgresql daemon. The avg disk writing speed
is 30MB/s, <br>
while occasionally peaks to 100MB/s. The SSD, F2FS formatted
disk is <br>
capable of up to 150MB/s. The daemon does not utilize the
4-core <br>
i5-7600k CPU. It seems to be a single process task with avg
CPU load <br>
20%, while other workers are idle. I wonder if there's a room
for <br>
improving the performance. Also, I'm looking for if it's
possible to <br>
preserve SRID with `\copy`.<br>
<br>
Jerry Lin<br>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank"
moz-do-not-send="true">postgis-users@lists.osgeo.org</a><br>
<a
href="https://lists.osgeo.org/mailman/listinfo/postgis-users"
rel="noreferrer" target="_blank" moz-do-not-send="true">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote>
</div>
-- <br>
<div dir="ltr" class="gmail_signature"
data-smartmail="gmail_signature">
<div dir="ltr">Darafei Praliaskouski<br>
Support me: <a href="http://patreon.com/komzpa"
moz-do-not-send="true">http://patreon.com/komzpa</a></div>
</div>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<pre class="moz-quote-pre" wrap="">_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="https://lists.osgeo.org/mailman/listinfo/postgis-users">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></pre>
</blockquote>
</body>
</html>