1

Given a spatialite database i want to export/dump all tables to shapefile (or dbf for those without geometry).

I want to do it programatically so acceptable solutions will be using a (bash) command line tool like spatialite, ogr2ogr, ... or Python.

Is there a command that export all database in one shot or should i iterate over each layer and dumping it individually?

2

GDAL is great and that's why it will automatically export layers one by one if you tell ogr2ogr to convert a multi-layer file to a single-layer format. The destination data source will automatically become a directory and inside you will get the single layers one by one.

To include all tables, you have to specify the config option SQLITE_LIST_ALL_TABLES set to yes.

$ ogrinfo --config SQLITE_LIST_ALL_TABLES yes test.spatialite 
INFO: Open of `test.spatialite.sqlite'
      using driver `SQLite' successful.
1: test (Point)
2: ElementaryGeometries (None)
3: SpatialIndex (None)
4: geom_cols_ref_sys (None)
5: geometry_columns (None)
6: geometry_columns_auth (None)
7: geometry_columns_field_infos (None)
8: geometry_columns_statistics (None)
9: geometry_columns_time (None)
10: idx_test_GEOMETRY (None)
11: idx_test_GEOMETRY_node (None)
12: idx_test_GEOMETRY_parent (None)
13: idx_test_GEOMETRY_rowid (None)
14: non_spatial (None)
15: spatial_ref_sys (None)
16: spatial_ref_sys_all (None)
17: spatial_ref_sys_aux (None)
18: spatialite_history (None)
19: sql_statements_log (None)
20: sqlite_sequence (None)
21: vector_layers (None)
22: vector_layers_auth (None)
23: vector_layers_field_infos (None)
24: vector_layers_statistics (None)
25: views_geometry_columns (None)
26: views_geometry_columns_auth (None)
27: views_geometry_columns_field_infos (None)
28: views_geometry_columns_statistics (None)
29: virts_geometry_columns (None)
30: virts_geometry_columns_auth (None)
31: virts_geometry_columns_field_infos (None)
32: virts_geometry_columns_statistics (None)

$ ogr2ogr -f "ESRI Shapefile" out file.gpkg

$ ls -1 out/
ElementaryGeometries.dbf
geom_cols_ref_sys.dbf
geometry_columns_auth.dbf
geometry_columns.dbf
geometry_columns_field_infos.dbf
geometry_columns_statistics.dbf
geometry_columns_time.dbf
idx_test_GEOMETRY.dbf
idx_test_GEOMETRY_node.dbf
idx_test_GEOMETRY_parent.dbf
idx_test_GEOMETRY_rowid.dbf
non_spatial.dbf
SpatialIndex.dbf
spatialite_history.dbf
spatial_ref_sys_all.dbf
spatial_ref_sys_aux.dbf
spatial_ref_sys.dbf
sqlite_sequence.dbf
sql_statements_log.dbf
test.dbf
test.prj
test.shp
test.shx
vector_layers_auth.dbf
vector_layers.dbf
vector_layers_field_infos.dbf
vector_layers_statistics.dbf
views_geometry_columns_auth.dbf
views_geometry_columns.dbf
views_geometry_columns_field_infos.dbf
views_geometry_columns_statistics.dbf
virts_geometry_columns_auth.dbf
virts_geometry_columns.dbf
virts_geometry_columns_field_infos.dbf
virts_geometry_columns_statistics.dbf
4
  • Thanks for the tip. That only exports spatial tables and not alphanumeric only tables. I try with gis.stackexchange.com/a/115638/4177 but that also export indexes, idx_xxx, internal views like virts_geometry_columns.dbf, ... Mar 9 '20 at 9:06
  • I added that config option now, sorry for missing it. Hm, could you add that requirement to your question? Right now my answer fully satisfies it.
    – inc42
    Mar 9 '20 at 15:57
  • It was already in the question (or dbf for those without geometry):). Thanks for update your answer. I accept it. Mar 9 '20 at 17:01
  • That part was and I had missed it. I meant the other tables you want to not have. We could just add a call to rm for those files ;)
    – inc42
    Mar 9 '20 at 18:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.