1

Coming from a ms-access background, it is common to split up larger applications into several database files. An ms-access database can then access data that is in another database via a linked table. This allows for better information management in many cases.

I am wondering if I can do something similar with SpatiaLite/SQLite, for example use Spatialite for the GIS part of projects and a separate SQLite database for the non-spatial components. One of the reasons for doing this would be to hide the Spatialite system tables from the user. The other might be that it simply makes sense to separate datasets into different databases (to maintain a single point of truth, etc). For example, my spatialite database may have a list of plant locations recorded during a field survey but my SQLite database may have a data dictionary with all the plant names for my country (~40 000 species). If linking tables is not something that SQLite does, what is the alternative?

Andrew M

Improve this question
5
  • 1
    You can link SQLite DBs using sql - stackoverflow.com/questions/6824717/…
    – user2856
    Commented Sep 11, 2013 at 4:37
  • This should be fine. Are you having trouble with something?
    – BradHards
    Commented Sep 11, 2013 at 5:50
  • I also found a commercial solution SQLite Expert Professional which has a GUI for attaching SQLite databases to the current database. It uses the Attach command as @Luke points out.
    – AndrewM
    Commented Sep 12, 2013 at 6:21
  • Seven years later, I am really interested in your final solution, @AndrewM. I basically want users of QGIS projects stored in Geopackages) to use a taxonomic database and a vegetation database. Both are stored in SQLite databases, and data collection from QGIS/QFIELD would be my aim. I'd appreciate suggestions where to start looking for projects which do this to learn from them.
    – aae
    Commented Oct 30, 2020 at 18:55
  • 1
    I use dbeaver as a database manager. This will do the job for you. I also still use ms-access quite a lot and there are a couple of modules and dlls that you can use to import sqlite data into ms-access. I use Geopaparazzi or Smash over Qfield and these application also use sqlite or spatialite for data storage. I am a botanist so the use case would be very similar. The modules is called sqlite4access. It works well in 32bit access, not sure if it works in 64 bit access. Ms-access is good for forms, Dbeaver is good for interactive use.
    – AndrewM
    Commented Nov 4, 2020 at 8:04

1 Answer 1

Reset to default
2

Within a QGIS project, you can add layers from as many SQLite and spatialite databases as you want.

Keeping the folder paths relative, it should be easy to exchange database and project files with other people working with it.

It should be no problem to replace one database with an updated one (of the same name and data structure), and work on with the combination with the other datasets.

Improve this answer
1
  • Whilst linking multiple SQLite/Spatialite files would enable you to show many layers, does it let you write SQL statements that join tables from different databases to create a view or update query. My instinct is that the Spatialite plugin and probably the DB Manager plugin would expect all tables to be in the same database. The only way to get around this without copying and pasting tables into a single database would be to use the attach statement. Cutting and pasting is really bad as it violates the single point of truth principle (Spelling things out for newbies who read this)
    – AndrewM
    Commented Sep 12, 2013 at 6:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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