Database versus Files for Solo Developer

Question

I'm just getting started in GIS and am interested in using it to create maps of mountain biking trails in my area and nearby. I've been learning QGIS and have made enough progress to generate a decent map. So far I've been storing my layers in Shapefiles, which seems to be working okay.

This past weekend I started looking at PostGIS and have it up and running and managed to import my trail shapefiles and connect QGIS to PostGIS.

I'm now wondering what the pros and cons of using a database versus files would be. I have some thoughts already, but am curious what enlightening opinions others may have.

Pros of Database

One table for all my trails, regardless of park, allows me to have a consistent set of columns for the information.
Easier to query/export my data to multiple formats
Multiple clients/tools allow for easier viewing and editing of data
Easier editing of table structure (compared to QGIS table editing)

Cons of Database

Standing up/maintaining a database adds complexity to my setup.
I have a desktop and a laptop I do my GIS work on and using DropBox allows me to easily sync files between the two machines. This likely won't be possible (or as transparent) using PostGIS as using DropBox with files.
Easier to backup/restore files than a database.

giohappy · Accepted Answer

I suggest you to add Spatialite to your list. It has the benefits of being file based (as a shapefile, or an .mdb database) and supports most of the usual spatial operators and tpyes, that you can find in PostGIS too.
Spatialite is based on Sqlite, so you will manage a single file, that you can easily move and share.

Styp · Answer

I'd say the biggest Pro on a PostGIS database is, that you can work with the whole function set PostGIS gives you.

All the Cons in your list, are all very easy fixable. You can run Postgis on ONE server and use your laptop and desktop as a client. The backup question as well, you can generate dump files or what-so-ever to have a good and valueable backup! ;)

Vadivelan Palanichamy · Answer

There is a limitation in Shape file (max 2gb).. so if your map size is not going beyond this limit.. you can use the shape file without any db setup....

Spacedman · Answer

Another big plus for PostGIS is that with spatial databases you can keep one copy of the spatial data and create views that link to your non-spatial data via a unique id.

Then you can create subsets of your full data by creating views that select from it - so you can do single-state tables as views of a full country table. SOmething like:

create view txcounties as select * from counties where state = 'TX'

Also nobody seems to have mentioned the speed of spatial operations for PostGIS. Suppose you want to see what the nearest waterfall was to your bike trails, given a table of lines of trails and a table of points of interest including waterfalls. That's the kind of query that PostGIS can do really quickly since it keeps spatial indexes on the spatial tables. You can probably do it with the native tools in Qgis but it might be quite a bit slower.

Obviously for a fun little application like yours these things are probably not a good reason to get into the complexity of a spatial database, but it might explain why these 'enterprisey' features are essential for bigger projects.

Laurent Jégou · Answer

Another advantage of DB vs. flat files is versioning, crucial for multiuser data-entry GIS.

There's a beta plugin for PostGIS and GeoGit, cf. this question.

nr_aus · Answer

old thread I know.
I would also add that - as a solution mechanism, Geopackages are now a good halfway point between the two options, offering a good mix of the solution benefits of both file/db solutions.

Database versus Files for Solo Developer

Pros of Database

Cons of Database

6 Answers

Add your own answers!

Ask a Question