[gdal-dev] Export from PG to spatialite

[Even Rouault]

Le samedi 31 décembre 2011 13:18:54, vous avez écrit :
> Hi,
> 
> I'm try to export from Postgres 9.1.2 with Postgis 2.0 to a spatialite
> 3.0.0 version using ogr2ogr.exe.
> To do this I try this sintax:
> 
> ogr2ogr.exe --config PG_LIST_ALL_TABLES YES --config PG_SKIP_VIEWS YES
> -f "SQLite" db.sqlite -progress PG:"dbname='test' active_schema=work
> schemas=work,public host='localhost' port='5432' user='postgres'
> password='postgres' " -overwrite -lco GEOMETRY_NAME=geom -dsco
> SPATIALITE=yes -lco SPATIAL_INDEX=yes -gt 65536  -sql "select * from
> work.table1" -nln table1
> 
> Ogr2ogr regularly export it on a new created file named db.sqlite,
> and create a table in it named "table1",
> 
> but this table1 don't have a geometry column named "geom" as I try ask
> using -lco GEOMETRY:NAME=geom
> instead has a field named "Geometry" and with "G" uppercase.
> So I guess the
> GEOMETRY_NAME seem don't work .

Yes, it is expected. GEOMETRY_NAME is a layer creation option of the PG 
driver, but not of the SQLite driver. The SQLite driver has a hardcode name 
for the geometry column, which is GEOMETRY

> 
> Another think I notice is that the geometry field seem to have no a
> declared geometrytype.
> 
> Infact try-ing to export table1 which has a POINT geometrytype.
> After exported in the spatialite it don't has a cleared geometrytype,
> and qgis is not able to open it.
> 
> I guess surely miss something parameter.

This is due to the fact that you fetch the data from the PG database with a 
SQL clause, in which case the PG driver cannot guess the geometry type. You 
have 2 possibilities :
- Add "-nlt POINT" to your command line
- or replace "-sql "select * from work.table1"" by just work.table1 (or just 
table1 since your active_schema is work)

> 
> Thx,
> 
> Andrea
> _______________________________________________
> gdal-dev mailing list
> gdal-dev at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/gdal-dev