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 <[email protected]
<mailto:[email protected]>>:
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
[email protected] <mailto:[email protected]>
https://lists.osgeo.org/mailman/listinfo/postgis-users
--
Darafei Praliaskouski
Support me: http://patreon.com/komzpa
_______________________________________________
postgis-users mailing list
[email protected]
https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
https://lists.osgeo.org/mailman/listinfo/postgis-users
