[gdal-dev] postgis to esri shape slow

[Even Rouault]

Le mercredi 30 avril 2014 16:44:54, Mario Jurcevic a écrit :
> On 04/30/2014 04:40 PM, Even Rouault wrote:
> > Le mercredi 30 avril 2014 15:54:45, Mario Jurcevic a écrit :
> >> On 04/30/2014 03:27 PM, Even Rouault wrote:
> >>> Hum, I managed to import the dump but I'm having troubles make the
> >>> export work. Could you paste the*exact*  ogr2ogr and pgsql2shp
> >>> commands you are using ?
> >> 
> >> 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'
> > 
> > Works, but recent versions of GDAL will show a warning :
> > ERROR 6: Unsupported WKB type 12
> > which shows that st_curvetoline() doesn't manage to convert all
> > geometries to regular linestring.
> 
> My postgis 2.0.4 is patched for curves conversion.
> You can use 2.1 version which has the improvements.
> 
> >> pgsqltoshape:
> >> 
> >> /usr/local/pgsql/bin/pgsql2shp -f out.shp -h localhost -u test_soft_gis
> >> -P passwd test_soft_gis \
> >> 'select
> >> "soft_gis_serial","Origine","Qualita","Genere",st_curvetoline("Geometria
> >> ") as Geometria from test_export."Copertura_del_suolo__SuperficieCS"'
> > 
> > This one does not work :
> > 
> > Initializing...
> > ERROR: Incompatible mixed geometry types in table
> > 
> > 
> > This might explain why conversion with pgsql2shp is so fast ;-)
> 
> For me works:
> 
> time /usr/local/pgsql/bin/pgsql2shp -f out.shp -h localhost -u
> test_soft_gis -P passwd test_soft_gis \
> 
>  > 'select
> 
> "soft_gis_serial","Origine","Qualita","Genere",st_curvetoline("Geometria")
> as Geometria from test_export."Copertura_del_suolo__SuperficieCS"'
> Initializing...
> Done (postgis major version: 2).
> Output shape: Polygon
> Dumping: XXXXXXXXXXXXXXXXXXXX [1962 rows].
> WARNING: Cannot determine spatial reference (empty table or unknown
> spatial ref). No prj file will be generated.
> 
> real	0m0.098s
> user	0m0.008s
> sys	0m0.012s

ok, after upgrading my postgis to the latest dev version :

$ 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" -overwrite
Warning 6: Normalized/laundered field name: 'soft_gis_serial' to 'soft_gis_s'

real	0m0.217s
user	0m0.100s
sys	0m0.030s

$ rm out2.*; time LD_LIBRARY_PATH=/opt/PostgreSQL/9.2/lib 
/opt/PostgreSQL/9.2/bin/pgsql2shp  -f out2.shp -p 5436 -u postgres -h 127.0.01 
autotest   'select 
"soft_gis_serial","Origine","Qualita","Genere",st_curvetoline("Geometria") as 
Geometria from test_export."Copertura_del_suolo__SuperficieCS"'
Initializing... 
Done (postgis major version: 2).
Output shape: Polygon
Dumping: XXXXXXXXXXXXXXXXXXXX [1962 rows].
WARNING: Cannot determine spatial reference (empty table or unknown spatial 
ref). No prj file will be generated.

real	0m0.188s
user	0m0.060s
sys	0m0.020s

Not that much a big difference... Perhaps you should try to isolate which layer 
conversion if particuarly slower with ogr2ogr

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