[gdal-dev] ogr2ogr sqlite to spatialite (retain views as views)

[Even Rouault]

Luc,

> I was testing the ogr2ogr from sqlite to spatialite.
> 
> The sqlite source file is an FDO type (with geometry_columns &
> spatial_ref_sys tables).
> Also the encoding is FGF, but we know ogr can deal with this.
> 
> Our sqlite file has spatial tables and non spatial tables.
> We have the config options SQLITE_LIST_ALL_TABLES set in the windows
> variables.
> 
> The sqlitefile contains views (spatial and non spatial view).
> 
> The spatial views have an entry in geometry_columns metadata table.
> 
> Running the OGR2OGR -f sqlite targetfile sourcefile -dsco SPATIALITE=YES we
> noticed:
> 
> - the views become populated tables (and not views anymore)

Yes, that's expected. At the OGR abstraction level, there are only layers. That they come from 
database views or tables (or anything else) is lost.

> - the ex-spatial-view tables (source spatial view now table) are properly
> created as spatial tables
> - the ex spatial tables re converted as ordanary tables with "fdo_' prefix,
> no entry in the geometry columns, although the geomtry is correctly encoded.

As far as I can see, apart the FGF geometry deserialization, there's nothing FDO specific in 
the SQLite driver (at least, I cannot see the 'fdo_' string, so I presume this is the name of the 
table in source database).

https://trac.osgeo.org/fdo/wiki/FDORfc16 doesn't mention this fdo_ prefix either, but 
perhaps this doc is out of date.

But what you report is a bit strange: if the geometry is properly exported, then the table 
should normally be registered as a spatial one. Difficult to say what happens here without a 
sample

> 
> - a side effect it seems to have difficulties with FID columns in the views
> (used -unsetFID to bypass but are still wondered)

Yes, in general when reading views, you cannot associate a FID. Unless some explicit 
metadata is set somewhere to tell that this column can be used as a FID (which spatialite 
system tables allow)

> 
> My question short is how to use ogr2ogr to properly change an (fdo) sqlite
> file to spatial lite?
> How to do this when having spatial and non-spatial tables?
> How to do this when having spatial views and non-spatial views?
> 
> should this not be possible straightforward, it would be helpfull to know
> the limitations, constraints and possible workarounds or post process steps,
> would they be required.

For the view -> view conversion, you'd probably need a dedicated conversion script that 
would understand that the source layer is a view and would thus manually create a view in 
the output database.

Even

-- 
Spatialys - Geospatial professional services
http://www.spatialys.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20170630/8d53c2a5/attachment.html>