[postgis-devel] shp2pgsql multi-transaction INSERT mode

[Even Rouault]

Le vendredi 18 juillet 2014 13:32:34, Rémi Cura a écrit :
> Cool !
> That's probably why it is the fastest way to load shp (faster than gdal).
> B-)

FYI, in GDAL trunk, I've recently turned on by default COPY mode, which should 
make ogr2ogr much faster. For older version, you could also do it by 
explicitly specifying the PG_USE_COPY configuration option to YES ( 
http://gdal.org/drv_pg.html )

> 
> For big data I would bet that it is fastest to load with copy then
> reproject with transform in base.
> 
> Cheers,
> Rémi-C
> 
> 2014-07-18 12:56 GMT+02:00 Sandro Santilli <strk at keybit.net>:
> > On Fri, Jul 18, 2014 at 10:36:47AM +0100, Mark Cave-Ayland wrote:
> > > On 18/07/14 10:17, Sandro Santilli wrote:
> > > >It looks like shp2pgsql doesn't do INSERT in multiple
> > > >transactions anymore, but I couldn't figure out since when.
> > > >
> > > >It used to do 250 inserts per transaction around 2004, do
> > > >you know when was that changed and why ?
> > > 
> > > Not sure - check the history? However I would definitely have argued
> > > to remove this, given as I have personal experience that the time
> > > cost of manually sorting through a partial committed shapefile is
> > > exponentially larger than failing individual queued shapefiles so
> > > you easily know which files you need to retry.
> > 
> > I agree a single transaction is easier to deal with, but anywa
> > given this is a change in behavior I would have expected an entry
> > in the NEWS file.
> > 
> > Maybe it changed during the refactoring toward -core and -cli.
> > I guess a comparable speed to the "chunked insert" could be obtained
> > 
> > by using a single INSERT for multiple rows, like:
> >  INSERT INTO target VALUES
> >  
> >   (row1),
> >   (rowX),
> >   (row255)
> >   ;
> > 
> > Remi: shp2pgsql can do boty COPY or INSERT. You can't use COPY when
> > reprojecting, which is a reason to use INSERT.
> > 
> > --strk;
> > _______________________________________________
> > postgis-devel mailing list
> > postgis-devel at lists.osgeo.org
> > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-devel

-- 
Geospatial professional services
http://even.rouault.free.fr/services.html