Loading vector layers into PostGIS

PostGIS is the spatial add-on to the popular PostgreSQL database. It's a server-style database with authentication, permissions, schemas, and handling of simultaneous users. When you want to store large amounts of vector data and query them efficiently, especially in a multicomputer networked environment, consider PostGIS. This works fine for small data too, but many users find its configuration too much work when SpatiaLite may be better suited.

Getting ready

Pick a vector layer and load it in QGIS. You will also need to have a working copy of Postgres/PostGIS running, a PostGIS database created, and an account that allows table creation.

Tip

BostonGIS maintains a decent tutorial on installation for Windows, and getting a PostGIS set up for everyone. You can find this at http://www.bostongis.com/?content_name=postgis_tut01#316.

You should configure QGIS to be aware of your database and its connection parameters by creating a new database item in the PostGIS load dialog or by right-clicking on PostGIS in the Browser tab and selecting New Connection:

You can find more information about PostGIS at http://docs.qgis.org/2.8/en/docs/user_manual/working_with_vector/supported_data.html#postgis-layers.

How to do it…

Now that you can connect to a PostGIS database, you are ready to try importing data:

1. Open DB Manager from the Database menu.
2. Expand the PostGIS item to list your databases. Expand the database that you want to connect to, and you should be prompted to authenticate (if you haven't saved your password in the settings).
3. Expand the list and select the Public schema.

   Tip

   In general, unless you are performing advanced work and understand how Postgres schemas work, place your layers in the Public schema. This is the default that everyone expects.

4. Click on the following import layer icon:
   
5. A dialog will pop up, providing you with import options.

   Tip

   SQL databases are usually case insensitive, so you can use all lowercase. Also, never use spaces or special characters in table names; this can just lead to headaches later. An occasional underscore is okay.

6. Select the layer to import from the drop-down list.
7. Fill in a name for the new table.
8. Check whether schema is set to public.
9. In most cases, the only thing left to do is check the Create spatial index checkbox:
   

How it works…

QGIS converts your geometries to a format that is compatible with PostGIS, and inserts it, along with importing the attributes. Afterwards, it updates the metadata views in PostGIS to register the geometry column and build the spatial index on it. These two post-processes make the database table appear as a spatial layer to QGIS and speed up the loading of data from the table when panning and zooming.

There's more…

The options presented in the dialog are not all the options that are available. If you need more control or advanced options present, you'll likely be looking at the command-line tools: shp2pgsql (a graphical plugin for pgadmin3 is available on some platforms) and ogr2ogr. The shp2pgsql tool generally only handles shapefiles. If you have other formats, ogr2ogr can handle everything that QGIS is capable of loading. You can also use these tools to develop batch import scripts.

To import large or complicated CSV or text files, you sometimes will need to use the pgadmin3 or psql command-line interface to Postgres.

Need even more control? Then, consider scripting. OGR and Postgres both have very capable Python libraries.

Another option is using the OpenGeo Suite plugin, which has some additional options, such as allowing importing multiple layers into a single table or into one table per layer. To learn more about this, including how to install it, refer to http://qgis.boundlessgeo.com/static/docs/intro.html.

What happens if this fails? Databases can be really picky sometimes:

• It could be character encoding (accents, non-Latin languages), which requires specifying the encoding.
• It could be picky about mixing multilayers with regular layers. Multilayers is when you have several separate geometries that are part of one record. For example, Hawaii is actually many islands. So, if you only have one row representing Hawaii, you need to cram all the island polygons into one geometry field. However, if you mix this with North Dakota that is just a polygon, the import will fail. If you have this problem, you'll need to perform the import on the command-line using ogr2ogr and its new feature, -nlt PROMOTE_TO_MULTI, which converts all single items to multi-items, to fix this.
• Depending on your original source, you may have a mix of points, lines, and polygons. You'll either need to convert this to a Geometry Collection, or you need to split each type of geometry into a separate layer. Geometry Collections are currently poorly supported in many GIS viewers, so this is only recommended for advanced users.

See also

For more information on PostGIS installation and setup, refer to http://postgis.net/install.

For a more in-depth text on using PostGIS, there are many books available, including Packt Publishing's PostGIS Cookbook.