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

  • 1
    You can link SQLite DBs using sql - stackoverflow.com/questions/6824717/… – user2856 Sep 11 '13 at 4:37
  • This should be fine. Are you having trouble with something? – BradHards Sep 11 '13 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 Sep 12 '13 at 6:21
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.

  • 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 Sep 12 '13 at 6:27

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.