[gdal-dev] postgis to esri shape slow

[Even Rouault]

Le mercredi 30 avril 2014 17:37:21, Mario Jurcevic a écrit :
> On 04/30/2014 05:32 PM, Even Rouault wrote:
> > Le mercredi 30 avril 2014 17:19:04, Mario Jurcevic a écrit :
> >> On 04/30/2014 05:11 PM, Even Rouault wrote:
> >>> Not that much a big difference... Perhaps you should try to isolate
> >>> which layer conversion if particuarly slower with ogr2ogr
> >> 
> >> I get big difference:
> >>    time ogr2ogr -sql 'select
> >> 
> >> "soft_gis_serial","Origine","Qualita","Genere",st_curvetoline("Geometria
> >> ") as Geometria from test_export."Copertura_del_suolo__SuperficieCS"'\
> >> 
> >>   >  -f "ESRI Shapefile" out.shp PG:'host=localhost dbname=test_soft_gis
> >> 
> >> user=test_soft_gis password=passwd'
> >> Warning 6: Normalized/laundered field name: 'soft_gis_serial' to
> >> 'soft_gis_s'
> >> 
> >> real	0m2.359s
> >> user	0m0.040s
> >> sys	0m0.004s
> >> 
> >> With debug on there there is many notice of ogr analyzing geom tables.
> >> Maybe that is, your dump miss has not the whole db.
> > 
> > I see ogrinfo reporting 167 layers.
> > 
> > The listing of spatial tables should be quite fast generally (in my case
> > it is). Perhaps you could try " tables=foo" (really 'foo' or another
> > dummy string) at the end of the OGR connexion string. This will skip
> > that listing.
> > 
> > time ogr2ogr -sql 'select
> > "soft_gis_serial","Origine","Qualita","Genere",st_curvetoline("Geometria"
> > ) as Geometria from test_export."Copertura_del_suolo__SuperficieCS"'
> > out.shp "pg:dbname=autotest host=127.0.0.1 port=5436 user=postgres
> > tables=foo" - overwrite --debug on
> > real	0m0.181s
> > user	0m0.110s
> > sys	0m0.020s
> > 
> > So this saves ~ 40 ms for me.
> 
> Yes!
> Without hidden listing is fast.

Hum, good. And it actually this rings a bell to me. In PostGIS 2.0 they have 
changed geometry_columns to be a view instead of a table. And in the case 
where you database is crowded (if you have more than the 167 above layers), 
the requests in the view might be really slow since the SQL to establish the 
view is rather complex...

Could you create a ticket in GDAL Trac about that issue ? I think it should be 
doable to implement a completely differed listing of layers the fist time 
GetLayerCount() is called. In the -sql case, it wouldn't be called, hence the 
listing will be skipped.

> 
> Thank you.

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