0

FOR QGIS: Is there a benefit to having a single spatialite database that contains many different geometry types (e.g., point, line, polygon) over separate spatialite databases for each geometry type?

  • Sorry, but I do not quite understand your question. One SpatiaLite database can contain however many tables (layers). Do you perhaps mean that is in OK to have mixed geometries in one table or would it be better to save points, lines, and polygons into separate tables? – user30184 Apr 10 '18 at 6:55
  • Hi user30184: Correct, my intention of the question was preferred to store mixed geometries in one table vs. storing them in different tables. – jdks18 Apr 10 '18 at 11:39
  • But still you accepted an answer that does not seem to answer that question. – user30184 Apr 10 '18 at 11:47
1

Spatialite databases don't work the way shapefiles do; there's no need to have separate files for each geometry type.

Two immediately apparent advantages of having all your relevant layers (of any geometry) in the one spatialite file are:

  1. tidy data organisation

  2. ability to quickly perform queries/analyses between layers within the same database

For example, if you wanted to count the points in one layer that are within the polygons in another layer, it's a lot easier and faster than if those two layers were in separate database files.

  • Thank you! Your answer helped me a lot. How you describe it is exactly what I'm looking for. – jdks18 Apr 10 '18 at 11:40
  • Having read your other responses I feel that I should clarify, I am simply referring to multiple layers/tables, within one spatialite database (but where each one has only one geom type). I'm not referring to mixed geometry types within a single layer/table. If you are relatively new to databases I would not recommend the latter as it is not well-supported (see Ralph's answer); I personally don't see any benefit in it given the way GIS clients work at the moment... – she_weeds Apr 10 '18 at 23:48
0

Firstly, in the context of GDAL/OGR, there are differences - structurally and functionally - between SQLite and Spatialite (which is SQLite + the Spatialite library). For use with QGIS, I usually go with SQLite because QGIS has many of the required geometry operators. For deployment, where PostGIS/PostgreSQL is overkill, I go with Spatialite (if possible).

A SHP file can store only one type of geometry. On the other hand, both SQLite and Spatialite can store multiple geometry types per table. (And, you can have more than one tables in a SQLite/Spatialite file.) However, for best-practises - I always constraint one geometry type per SQlite/Spatialite table. There are two main reasons for doing this. First - some QGIS/GRASS functions throws error when given a table with mixed geometries. For instance - a function that expects only lines/multilines was presented with a point - it crashes. Of course this is not QGIS' weakness but rather the tools that performed the operation. Second - each QGIS layers accepts/expects only one geometry type. If you have a table with multiple types - you need to add multiple times. This is not a big issue if you have only a few QGIS layers but it can quickly become confusing after a while, or for people who are not familiar with QGIS + SQLite/Spatialite. As an example - I once had great difficulty in explaining to an irritated customer why QGIS showed the layer count as having five records but visually it only showed three. The customer had thought there were geometry validity issues with the other two records when the truth was something else.

The screen shot below shows a trivial comparison between SQLite and Spatialite. Both files contain exactly the same set of geometries, ie two POINTs and two LINESTRING. Spatialite is much bigger because it carries all the geometry operators.

enter image description here

  • Ralph Tee: Thank you! It seems like some people are okay with putting mixed geometries in a single Spatialite, and there are some benefits in doing so (see she_weeds answer). But, depending on the need as you explained, it could be preferred to separate the different geometries (point, line, etc.) into separate Spatialite. Something for me to consider going forward... – jdks18 Apr 10 '18 at 11:44
  • @jdks18 Hehehe. MapInfo's TAB is a good example of a "traditional" file container that has multi-type table. When I first moved to SHP from TAB, I was "struggling". With TAB - everything was so "natural". However, as my experience increases, I discovered/found there are many benefits in keeping the tables mono-typed. An example of this benefit is when doing SQL PostGIS queries. – Ralph Tee Apr 10 '18 at 12:06
  • When it comes to the initial size of SpatiaLite database, most space is used for definitions of the supported spatial reference systems (4924 different at the moment). See the the answer that I just wrote about making a minimal 228 kB SpatiaLite database gis.stackexchange.com/questions/266489/… – user30184 Apr 10 '18 at 12:42

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.