[postgis-users] postgis-users Digest, Vol 201, Issue 1

[Simon Norris]

You could also consider loading your csv with ogr2ogr, that should allow you to set the correct SRID on load. 
ogr should use the fast PG_USE_COPY option by default.
If that works well and your data allows it, you could try more optimization by splitting it up with ogr2ogr's -sql option - and run the load in parallel jobs, something like noted here: http://longwayaround.org.uk/notes/loading-postgis/ <http://longwayaround.org.uk/notes/loading-postgis/>.  Syntax for `parallel` on the single file might be tricky, I'd probably use python's multiprocessing module.


> 
> 
> Message: 7
> Date: Thu, 1 Nov 2018 15:57:41 +0800
> From: jerry73204 <jerry73204 at gmail.com>
> To: postgis-users at lists.osgeo.org
> Subject: [postgis-users] On UpdateGeometrySRID() performance
> Message-ID: <69efd569-bac0-d092-eb17-895dd02d20bc at gmail.com>
> Content-Type: text/plain; charset=utf-8; format=flowed
> 
> 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
> 
> 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20181101/766fdc38/attachment.html>