Migrating to the use of databases over shapefiles in QGIS

Question

I mostly use ArcGIS at work and am digging into QGIS at home and for my business. At work we are phasing out the use of shapefiles and moving towards geodatabases. So I am thinking I should look into this at home and for the business. having very limited knowledge of this, this is what I assume I need to do.

1. Install Postgresql on my Ubuntu Server
2. Install PostGIS on my Ubuntu Server
3. Connect to PostGIS from my Windows Machine that currently has QGIS 2.0 installed.

Once this is done I have to make the connection to the server, then figure out how to turn some of my existing shapefile data into a database.

So here is my question:

Does the above process sound correct and are there any step by step resources for the beginner to set implement this process?

Be gentle I have no formal training in GIS, its all on the fly.

Answer 1

Any specific reason you don't want to test it all on your windows machine first? Get accustomed and then look into setting up a server?

Anyway, the workflow you posted is fine.

The detailed installation procedure varies a little from Ubuntu version to Ubuntu version. You'll find pointers for example in Install PostGIS on Ubuntu 12.04 or Unable to install the PostGIS extension to PostgreSQL on Ubuntu 12.04 LTS

You can load Shapefiles into PostGIS using QGIS DB Manager. It has an import tool. See for example Exporting shapefile to PostGIS database in QGIS

Answer 2

Consider using SpatiaLite

PostGIS/PostgreSQL is arguably the most powerful and flexible geospatial database solution, albeit a little cumbersome for the beginner to set up, configure and learn. Some of the advantages of having your geospatial data in PostgreSQL with PostGIS is all of the spatial operation functions it makes available to use in your SQL queries and the fast response of those queries.

The only aspects of a PostGIS/PostgreSQL setup that may be considered a disadvantage, depending upon your workflow, is its lack of easy portability and the general complexity of managing a database server. In many workflow instances, i.e. a single user, PostGIS/PostgreSQL is overkill.

There is another open source solution for geospatial databases, that offers many of the same features of PostGIS/PostgreSQL, plus portability and a file-based architecture (instead of server/client): SpatiaLite, which is built upon SQLite. With SpatiaLite 4.x users now have many of the same PostGIS-like geospatial functions available to them as well.

You can easily convert shapefiles to SpatiaLite database files via the command line, via the spatialite-gui application, or by right-clicking on a QGIS layer and choosing Save As... (QGIS SpatiaLite layer user docs).

Depending upon your workflow this may be a good final solution or a decent stepping stone towards an eventually more robust setup (e.g. PostGIS/PostgreSQL). There is also a new dynamic extension for SpatiaLite called VirtualPG that allows for direct connections between SpatiaLite and PostGIS/PostgreSQL to help with data exchange.