Performance improvement suggestions for load_spatialite

[caspervdw]

When creating a new spatialite file, you should call SELECT InitSpatialMetaData();. This is done by load_spatialite. However this call takes very long (minutes!).

There are several ways for optimizing this:

1. Limit the size of the spatial ref table: (done in Add GeoPackage support #456)
2. Use the optional argument transaction and set it to TRUE. Then the whole operation will be handled as a single Transaction (faster): the default setting is transaction=FALSE (slower, but safer). (copy-paste from http://www.gaia-gis.it/gaia-sins/spatialite-sql-latest.html)
3. Set PRAGMA journal_mode = MEMORY. This is less safe, but again a lot faster.

We use both 'unsafe' methods and additionally wrap the whole initialization thing in a "file transaction": https://github.com/nens/threedi-schema/blob/master/threedi_schema/application/threedi_database.py#L136 to make it safe.

I think it is good to remove the initalization stuff from the load_spatialite and instead, make explicit methods spatialite_is_initialized() -> bool, init_spatialite which can be called by the user.

[adrien-berchet]

Interesting. We could add more arguments to load_spatialite, like use_transaction and maybe journal_mode. So people could use it the same way as the init_mode argument.

About splitting the function into load_spatialite and init_spatialite I'm afraid it will be harder to use as listeners. I never tried but what happens if we register 2 listeners for the same event? Like:

listen(engine, "connect", load_spatialite)
listen(engine, "connect", init_spatialite)

Is the order guaranteed?
I guess we could provide another function that just calls these 2, so only one has to be registered as listener.

[adrien-berchet]

Solved by #459