[gdal-dev] OGR Spatialite to geopackage

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

[gdal-dev] OGR Spatialite to geopackage

lucvanlinden
Hi

We are using ogr 2.2.0 and were trying to turn a spatialite (non fdo) into a geopacke file.

The spatialite file has all the metadata tables and view in it.

Should we explicitly pass the layername list (tablenames) for a clean transform?

We are getting errors on records no being able to get transformed referring to tablenames that are those from the source metadata?



Gr
Luc Van Linden

_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: OGR Spatialite to geopackage

jratike80
lucvanlinden wrote
> Hi
> We are using ogr 2.2.0 and were trying to turn a spatialite (non fdo) into
> a geopacke file.
> The spatialite file has all the metadata tables and view in it.
> Should we explicitly pass the layername list (tablenames) for a clean
> transform?
> We are getting errors on records no being able to get transformed
> referring to tablenames that are those from the source metadata?

Hi,

Normally all you need to do for converting all data table from SpatiaLite
into corresponding geopackage is

ogr2ogr -f gpkg output.gpkg input.sqlite

What errors do you get? You may see more details by adding --debug on into
your command.


-Jukka Rahkonen-



--
Sent from: http://osgeo-org.1560.x6.nabble.com/GDAL-Dev-f3742093.html
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: OGR Spatialite to geopackage

lucvanlinden
Hi Jukka

Error 1: failed to execute insert : unique constraint failed :
geom_cols_ref_sys._rowid_

Error 1 : Unable to write feature 0 from layer geom_cols_ref_sys.

Terminating....

Seems to me that the metadata tables, in this case it is even a view is
treated as a normal layer.

Tx

Luc





--
Sent from: http://osgeo-org.1560.x6.nabble.com/GDAL-Dev-f3742093.html
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: OGR Spatialite to geopackage

bradh
Can you please show the exact command you are using, and tell us whether the geopackage you are using already exists (i.e. this is an append, not a create)?

Brad


_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: OGR Spatialite to geopackage

lucvanlinden
In reply to this post by jratike80
ogr2ogr -f gpkg output.gpkg input.sqlite



Gr
Luc Van Linden


-------- Original message --------
From: [hidden email]
Date: 12/06/2018 12:01 (GMT+01:00)
To: 'lucvanlinden' <[hidden email]>, [hidden email]
Subject: RE: [gdal-dev] OGR Spatialite to geopackage

Can you please show the exact command you are using, and tell us whether the geopackage you are using already exists (i.e. this is an append, not a create)?

Brad



_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: OGR Spatialite to geopackage

lucvanlinden
In reply to this post by jratike80
Sorry forgot to mention. This gpkg does not exist.



Gr
Luc Van Linden


-------- Original message --------
From: [hidden email]
Date: 12/06/2018 12:01 (GMT+01:00)
To: 'lucvanlinden' <[hidden email]>, [hidden email]
Subject: RE: [gdal-dev] OGR Spatialite to geopackage

Can you please show the exact command you are using, and tell us whether the geopackage you are using already exists (i.e. this is an append, not a create)?

Brad



_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: OGR Spatialite to geopackage

Even Rouault-2
In reply to this post by lucvanlinden
On mardi 12 juin 2018 02:57:56 CEST lucvanlinden wrote:

> Hi Jukka
>
> Error 1: failed to execute insert : unique constraint failed :
> geom_cols_ref_sys._rowid_
>
> Error 1 : Unable to write feature 0 from layer geom_cols_ref_sys.
>
> Terminating....
>
> Seems to me that the metadata tables, in this case it is even a view is
> treated as a normal layer.

There isn't a concept of view in the OGR model, so they are presented as
tables and converted as such. In that case this is a technical Spatialite
table that makes no sense in GPKG, so you should exclude it from the
conversion by explicitly listing the tables/views you want to convert.

Even

--
Spatialys - Geospatial professional services
http://www.spatialys.com
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: OGR Spatialite to geopackage

jratike80
Even Rouault-2 wrote
> There isn't a concept of view in the OGR model, so they are presented as
> tables and converted as such. In that case this is a technical Spatialite
> table that makes no sense in GPKG, so you should exclude it from the
> conversion by explicitly listing the tables/views you want to convert.

Hi,

Sorry, but this time your answer does not make sense. I can convert with
success a full featured SpatiaLite database (created by GDAL) with two
tables and a spatial view (that gets converted into a normal spatial table)
into GPKG with a simple command

ogr2ogr -f gpkg output.gpkg input.sqlite.

My source SpatiaLite db does include the "geom_cols_ref_sys" view, with this
DLL

CREATE VIEW geom_cols_ref_sys AS
SELECT f_table_name, f_geometry_column, geometry_type,
coord_dimension, spatial_ref_sys.srid AS srid,
auth_name, auth_srid, ref_sys_name, proj4text, srtext
FROM geometry_columns, spatial_ref_sys
WHERE geometry_columns.srid = spatial_ref_sys.srid

So my GDAL can handle the system view  "geom_cols_ref_sys" while the GDAL
that Luc has obviously can't. I suppose we have somehow different builds,
mine comes from the gisinternals developer builds.

-Jukka Rahkonen-



--
Sent from: http://osgeo-org.1560.x6.nabble.com/GDAL-Dev-f3742093.html
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: OGR Spatialite to geopackage

Even Rouault-2
On mercredi 13 juin 2018 01:36:18 CEST jratike80 wrote:

> Even Rouault-2 wrote
>
> > There isn't a concept of view in the OGR model, so they are presented as
> > tables and converted as such. In that case this is a technical Spatialite
> > table that makes no sense in GPKG, so you should exclude it from the
> > conversion by explicitly listing the tables/views you want to convert.
>
> Hi,
>
> Sorry, but this time your answer does not make sense. I can convert with
> success a full featured SpatiaLite database (created by GDAL) with two
> tables and a spatial view (that gets converted into a normal spatial table)
> into GPKG with a simple command

What I meant is that in OGR, on the read side, views and tables are presented
as OGR layers, and on the write side, they are written as tables. So the
concept of view is lost in the translation. And anyway in the GPKG case, the
concept of spatial view isn't really well supported by the standard currently.

>
> ogr2ogr -f gpkg output.gpkg input.sqlite.
>
> My source SpatiaLite db does include the "geom_cols_ref_sys" view, with this
> DLL
>
> CREATE VIEW geom_cols_ref_sys AS
> SELECT f_table_name, f_geometry_column, geometry_type,
> coord_dimension, spatial_ref_sys.srid AS srid,
> auth_name, auth_srid, ref_sys_name, proj4text, srtext
> FROM geometry_columns, spatial_ref_sys
> WHERE geometry_columns.srid = spatial_ref_sys.srid
>
> So my GDAL can handle the system view  "geom_cols_ref_sys" while the GDAL
> that Luc has obviously can't. I suppose we have somehow different builds,
> mine comes from the gisinternals developer builds.

I presumed that Luc had specified -oo LIST_ALL_TABLES=YES (which actually list
views as well), but apprently he didn't, so yes that's a bit mysterious


--
Spatialys - Geospatial professional services
http://www.spatialys.com
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Reply | Threaded
Open this post in threaded view
|

Re: OGR Spatialite to geopackage

lucvanlinden
Hi

Even, we had indead a similar question before on the all tables etc with
views.

This howevere is a straight spatialite to geopackge transform equally as
Jukka explained.

I must say we are on a 2.2.0 still.

The listing with tables works (layernames), the straight as in

 ogr2ogr -f gpkg output.gpkg input.sqlite

is causing the errors for the view "geom_cols_ref_sys".

(we have not made the transition yet to 2.3.X given the issues we read on
the new srs transform stuff we have to look into thecorrect EPSG 31370, but
this will be another topic once we had time to confiim or not )

Should however the current issues comming from the 2.2.0 version we can
obviously use that in the mean while for this exercise.

thanks once more.

Luc



--
Sent from: http://osgeo-org.1560.x6.nabble.com/GDAL-Dev-f3742093.html
_______________________________________________
gdal-dev mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/gdal-dev