Simplify spatialite database by eliminating unused spatialite tables

[Richard Males]

I am interested in using spatialite to create and populate geometry fields (Point, Linestring, Polygon) in various tables.  The database is then used in Python/C# applications. 

Creating a db in the latest spatialite gui generates a large number of new tables and views that I don't need and don't want to carry around (raster_coverages,  SE_fonts, many others).

So I want the excellent geometry creation and import functionality offered by the gui, the extended SQL capability to read the geometry fields, but once I have my fields defined and populated, I would like to eliminate unneeded tables to get to a 'skinny' spatialite db.

Is it best to create with all the tables and then eliminate the ones I don't need?  I have tried that, get some conflicts on table/view deletion, so I assume there is a proper order to doing this.

Thanks in advance.

Dick

[mj10777]

 The present spatialite gui will use InitSpatialMetaDataFull to create a complete spatialite database with a full support of everything possible in SpatiaLite 5.0.

To create a slimmed down version, with an minimal amount needed, use InitSpatialMetaData(1,'NONE') when creating a new database. This will create a Database as in version 4.3, with an empty spatial_ref_sys table.

Since SpatiaLite 5.0, AddGeometry will also add any needed srid to the spatial_ref_sys table.

In the past, using sql-scripts, I have created 'production' databases (i.e. slimmed down version to what is really needed) from a 'development' database.

This would then be a combination of:

- InitSpatialMetaData(1,'NONE')

and then ATTACH your development db and use CloneTable to import what you need.

All of this can be done from an sql-script.

See SpatiaLite Cookbook V5 at https://www.gaia-gis.it/gaia-sins/spatialite-cookbook-5/index.html for many samples of more complicated sql samples.

Mark

Thanks in advance.

Dick

[Richard Males]

Thank you, this is very helpful.  

To be clear - this needs to be done in the CLI to get started, correct?   I gather that if I create a new db in the gui it will always use InitSpatialMetaDataFull.

Dick

[mj10777]

On Saturday, 17 April 2021 at 20:47:26 UTC+2 rbm...@gmail.com wrote:

Thank you, this is very helpful.  

To be clear - this needs to be done in the CLI to get started, correct? 

Yes, since spatialite gui intends to support RasterLite2 etc.  

  I gather that if I create a new db in the gui it will always use InitSpatialMetaDataFull.

That is my understanding to insure that RasterLite2 is supported.  

[Richard Males]

thanks, in the meantime,  I have been trying it in the CLI

Win10 environment spatialite-tools-5.0.0-win-amd64 spatialite.exe

It appears that all tables are created in any case.  

>spatialite test1.db

spatialite> select InitSpatialMetaData(1,'NONE');
InitSpatiaMetaData() error:"table spatial_ref_sys already exists"
0

spatialite> .tables
ElementaryGeometries                raster_coverages_ref_sys
ISO_metadata                        raster_coverages_srid
ISO_metadata_reference              rl2map_configurations
ISO_metadata_view                   rl2map_configurations_view
KNN                                 spatial_ref_sys
SE_external_graphics                spatial_ref_sys_all
SE_external_graphics_view           spatial_ref_sys_aux
SE_fonts                            spatialite_history
SE_fonts_view                       sql_statements_log
SE_raster_styled_layers             stored_procedures
SE_raster_styled_layers_view        stored_variables
SE_raster_styles                    topologies
SE_raster_styles_view               vector_coverages
SE_vector_styled_layers             vector_coverages_keyword
SE_vector_styled_layers_view        vector_coverages_ref_sys
SE_vector_styles                    vector_coverages_srid
SE_vector_styles_view               vector_layers
SpatialIndex                        vector_layers_auth
data_licenses                       vector_layers_field_infos
geom_cols_ref_sys                   vector_layers_statistics
geometry_columns                    views_geometry_columns
geometry_columns_auth               views_geometry_columns_auth
geometry_columns_field_infos        views_geometry_columns_field_infos
geometry_columns_statistics         views_geometry_columns_statistics
geometry_columns_time               virts_geometry_columns
idx_ISO_metadata_geometry           virts_geometry_columns_auth
idx_ISO_metadata_geometry_node      virts_geometry_columns_field_infos
idx_ISO_metadata_geometry_parent    virts_geometry_columns_statistics
idx_ISO_metadata_geometry_rowid     wms_getcapabilities
networks                            wms_getmap
raster_coverages                    wms_ref_sys
raster_coverages_keyword            wms_settings

[mj10777]

On Saturday, 17 April 2021 at 21:08:35 UTC+2 rbm...@gmail.com wrote:

thanks, in the meantime,  I have been trying it in the CLI

Win10 environment spatialite-tools-5.0.0-win-amd64 spatialite.exe

It appears that all tables are created in any case.  

Yes, indeed it does. 

Documented at InitAdvancedMetaData, which I overlooked. 

http://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html 

We will have to wait until Sandro reacts to this, since there may be TRIGGER constraints that prevent the removal of these tables. 

[Alessandro Furieri]

Hi Richard,

the most recent SpatiaLite 5.0.x adopts a richer and most elaborate

set of metatables than any previous version.

this is strictly required because now libspatialite and librasterlite2

are fully integrated the one with the other.

here you can find some further information:

https://www.gaia-gis.it/fossil/libspatialite/wiki?name=Upgrading+existing+DB-files+to+5.0.0

note: Spatialite 5.0.x still preserves a limited capability to

correctly work even when the underlying DB is based on the

nowadays obsolete tables schema previously adopted by 4.0.x;

many new advanced SQL functions will fail in this case, but

the main core of Geometry-related SQL functions will still

continue to work unaffected.

both spatialite_gui and spatialite CLI (5.0.x) will always

install the latest 5.0.x DB schema as expected.

using sqlite3 + LOAD_EXTENSION('mod_spatialite') no automatic

DB schema initialitation will be performed and you'll be free

to choose between calling InitSpatialMetaDataFull() (the 

suggested best choice) or InitSpatialMetaData() if for any

reason you opt for installing the obsolete 4.0.x DB schema.

I read that you are working on Python, so you are free 

to choose between:

SELECT InitSpatialMetaData(...)

- this will install the obsolete 4.0.x schema

SELECT InitSpatialMetaDataFull(...)

- this will instell the more recent 5.0.x schema

caveat: manually removing system tables surely is a very

nice recipe for insured disasters. you are warned.

final conclusion: I'm unable to understand why less

than a dozen of further tables (presasumably destined

to remain empty because they'll be never referenced)

can disturb you so much. 

the increase in the DB size will be absolutely negligible

and there is asbolutely no performance impact; so, where 

is the problem ?

it seems only matter of subjective and merely aesthetic

evaluations.

bye Sandro

[Richard Males]

Thank you for the response and clarification, very helpful.   The current application makes very limited use of spatial data (read-only, for display) so I was just looking for the smallest/simplest footprint.   Other applications involve spatial analysis operations so the full set of capabilities is both needed and desired.

You are correct, it is not so much a matter of the actual burden of the additional tables/views, it is more that when describing/sharing the database structures with others the presence of this additional information (Styling, Metadata, Internal Data) becomes confusing.

As always, thank you for the great work that you have done for the community. 

Dick

[Richard Males]

After consultation today with other members of the project team, we have elected to take Sandro's suggestion and adopt a full spatialite implementation with no concerns at this time about cutting it down.

So this leads to the question of how best to convert an existing sqlite database to spatialite.  I tried Mark's suggestion of creating a new spatialite database, attaching the existing sqlite db and cloning, but when I attempt to clone a table from the attached sqlite db, I get: "Sqlite Sql Error: no such table a.geometry_columns".  

What is the preferred method of adding full spatialite capabilities to an existing sqlite db?

Thanks.

Dick

On Tuesday, April 20, 2021 at 9:00:15 AM UTC-4 Richard Males wrote:

[Pedro Camargo]

Initializing those tables is just a matter of running SELECT InitSpatialMetaDataFull(...). Isn't it?

---- On Tue, 27 Apr 2021 06:53:43 +1000 Richard Males <rbm...@gmail.com> wrote ----

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to spatialite-use...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/spatialite-users/70a31025-ed56-492d-bc52-e94d02cbaa51n%40googlegroups.com.

[a.fu...@lqt.it]

On Mon, 26 Apr 2021 13:53:43 -0700 (PDT), Richard Males wrote:
> What is the preferred method of adding full spatialite capabilities
> to
> an existing sqlite db?
>

Hi Dick,

you'll find some usefull informations in this Wiki page:

https://www.gaia-gis.it/fossil/libspatialite/wiki?name=Upgrading+existing+DB-files+to+5.0.0

assuming that your existing DB is already supporting the previous
4.0.x schema this will be all you need:

SELECT CreateMissingSystemTables();


upgrading some DB created by any other previous version (< 4.0.0)
is supported by the "spatialite_convert" CLI tool.

bye Sandro

[a.fu...@lqt.it]

On Tue, 27 Apr 2021 07:04:43 +1000, Pedro Camargo wrote:
> Initializing those tables is just a matter of running _SELECT
> InitSpatialMetaDataFull(...). _Isn't it?
>

Hi Pedro,

calling InitSpatialMetaDataFull() is intended to initialize
an empty DB (just created by SQLite and not yet containing
any SpatiaLite own stuff).

but if you intended to upgrade to 5.0.x an existing DB already
supporting the previous 4.0.x schema the optimal method is by
calling CreateMissingSystemTables()
this SQL function will check all existing metatables already
defined and will create only the missing ones.

bye Sandro

[mj10777]

On Monday, 26 April 2021 at 22:53:43 UTC+2 rbm...@gmail.com wrote:

After consultation today with other members of the project team, we have elected to take Sandro's suggestion and adopt a full spatialite implementation with no concerns at this time about cutting it down.

So this leads to the question of how best to convert an existing sqlite database to spatialite.  I tried Mark's suggestion of creating a new spatialite database, attaching the existing sqlite db and cloning

The CloneTable should only be used for your production tables. 

Do not clone system administration tables. 

 

, but when I attempt to clone a table from the attached sqlite db, I get: "Sqlite view-source ql Error: no such table a.geometry_columns".  

[Richard Males]

Thanks to all.  I have resolved the problem using the steps below.   I should have been more clear about the situation.   The databases I am working with are pure sqlite, migrated from MS Access tables that had no geometry of any kind.   I need to add geometry fields to a few tables.  spatialite_convert does not work, reports 'does not seem to contain valid spatial metadata', as these db's were never in any version of spatialite.

What does seem to work is the following, based on the suggestion of Pedro Camargo.

1) Open the pure sqlite database in spatialite gui 5.0

2) Select InitSpatialMetadataFull();

This allows me to proceed to create and populate the geometry columns I need with the usual sql techniques (InitSpatialMetaData(1), AddGeometryColumn, etc.)

This is sufficient for my purposes.  I appreciate the help in getting to this solution.

Dick