Last time we talked on why PostGIS should be the database friend of a GISer , and we touched on most of the features that sets the GIS PostgreSQL extension far from any other spatial database. Of course, PostgreSQL is not the only spatial database that can get your work done, and as one of our readers had suggested, today we will be looking on why and when should choose between PostGIS and SpatiaLite.
First things first. SpatiaLite is a variant of the SQLite database that also provides GIS features. It uses the GEOS, PROJ.4, etc… libraries that are also used in PostGIS, and hence the resulting feature set is similar.
So what is the difference between the two ? Which one should you pick? Lets find out through a side by side comparison.
1.Scale of Project
SpatiaLite is probably the best choice when the project at hand needs a standalone, embedded database. Lets say for example you are building a mobile or desktop application that requires an embedded database.
PostGIS is preferred for large scale projects that have high loads and require quick responses,for one of the obvious reason being that its built on top of the most famous open source database management system, PostgreSQL.
2. Context/Access at a time
SQLite is single process and single threaded, this means if you’re doing something where only one thing at a time is accessing or changing the database, then it will work well . However if you need to be able to deal with dozens of concurrent connections and you are writing and reading different things from different clients; then PostgreSQL is the best choice here.
So if your webservice involves many users reading geometry from different areas and each also writing multiple items to the database , it is worth the time setting up PostGIS.
3. Advanced Functionality
Both databases provide basic spatial operations. They both follow OGC standards and built under similar libraries thus much of the functionalities are similar and very usable in SQL.
The support for using correct projections with supplying the SRID, and support for Geography types in PostGIS sets it apart from other spatial databases.
The lack of support for Geography projections (lat, long) in SpatiaLite is a clear minus, since in practice WGS84 projection is used for significant datasets
PostGIS is much mature in the functionalities it ships with, including raster support, point clouds and the famous ST_functions.
So if you need much mature and stable spatial functionalities that can serve a wide range of analyses at a time, PostGIS will server you right.
4. Levels of Organization
PostGIS is a more traditional database, in that it can maintain may levels of organization -Schemas, Tables etc within a single data directory and it communicates via a port or a socket.
On the other hand, SpatiaLite , like SQLite, is a file based database that does not have, for example, a user system.
So if you have a database model that will require different access levels and security implementations, you know the right database.
The above comparisons are based on what so far I have experienced in one project or another, and if you have experienced any difference that I have not mentioned, share them below in the comments.
To sum up, if SpatiaLite or PostGIS fulfill your spatial requirements, then you need to turn your focus on the differences between PostgreSQL and SQLite. SQLite is better for smaller embedded solutions, whereas PostgreSQL is a fully hosted database