GIS with SQLite: The difference betweeen Geopackage and SpatiaLite

2023-08-16

Managing GIS files is more or less done in one way, most of the time: in Shapefiles. Shapefile is the format that has been around forever for that purpose, which is why it’s still heavily used even today.

However, Shapefile has its share of problems (for example, it needs multiple files for a single data source), which is why voices are getting louder to switch away from it.

In my opinion, this is great, as lots of data can often be stored in a single file nowadays. A great file format for that is SQLite, and there already exists tooling for that.

With SQLite, there are two main ways to handle GIS data: Geopackage and SpatiaLite.

The difference between these two was confusing to me, and I wanted to share some things I found out.

The Difference between Geopackage and SpatiaLite

SpatiaLite is a separate library that needs to be enabled in SQLite. With SpatiaLite enabled, SQLite can run spatial functions on its data. Geopackage instead is simpler: It doesn't need a separate library and instead is really just a standardized way to store GIS datasets in SQLite. If we wanted to run geospatial functions on it, we’d need an outside library to do that (geos, for example). (Or add SpatiaLite on top of it.)

That’s the gist of it. I want to add that this is my current understanding, and I don’t have too much experience with both of these formats yet. So please take it with a grain of salt.

That said, I want to go into more detail on other things I found out.

SpatiaLite is a bit older than Geopackage (2008 vs 2014). Furthermore, SpatiaLite is, again, just a library. Geopackage is a standard by OGC, so it’s really pretty official!

Initially, when trying to figure out what format would be suited for what, I got the impression that they are incompatible. This isn’t true, however. Instead, SpatiaLite is even listed as a reference implementation for Geopackage.

This makes total sense, in my opinion. For one, SpatiaLite is much more than Geopackage, as it provides geospatial functions in SQL directly to work with. Also, SpatiaLite has a Geopackage mode, which, as I understood it, makes it work completely with Geopackage files.

Note that besides that mode, it also has an amphibious Geopackage mode. I could not find the difference between the two, so I finally found it in the documentation of some function in SpatiaLite. Basically, in geopackage mode, only geopackage geometries are allowed. In geopackage amphibious mode, both geopackage and SpatiaLite geometries are accepted. (I found this here. To find this, I actually looked through the source code, where I discovered that lots of code, sooner or later, calls this function. This took me some time—unfortunately, SpatiaLite isn’t that well documented, in my opinion. Which is a bit of a shame, as it seems really useful.)

The reason why these two modes need to exist in the first place is that Geopackage uses a slightly different geometry format than SpatiaLite does. Some reasoning on that is explained here, but I don’t want to go into too much detail about that.

That’s what I figured out so far about these two. I think if I needed a single file containing lots of GIS data nowadays, I would first go with Geopackage. If I need to process that data further, I would simply add the SpatiaLite extension to that SQLite file.