[gdal-dev] Export from PG to spatialite

[aperi2007]

 > driver, but not of the SQLite driver. The SQLite driver has a 
hardcode name
 > for the geometry column, which is GEOMETRY

Hi, Roualt ,
this is really clear and precious information for me.

Expecially the info on the hardcoded name in splite.
This was not really clear in the docs for sqlite/splite.

Il 31/12/2011 14:10, Even Rouault ha scritto:
> 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
>