ogr2ogr sqlite to spatialite (retain views as views)

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

ogr2ogr sqlite to spatialite (retain views as views)

lucvanlinden
Hi

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)
- 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.

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

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.

Thanks and regards

Luc




Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ogr2ogr sqlite to spatialite (retain views as views)

Even Rouault-2

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


_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Loading...