QGIS, Postgis: Is it possible to create a new postgis layer?

Question

Situation:

I have a postgis vector lay that has lots of line segments (the features). I want to create a set of new layers each of which contains a subset of the features of the original layer and have these layers also stored back in postgis. Ideally with the actual features stored just once.

The only way I can see to create a new layer as opposed to load one from some source is via Layer->new which gives an option of shapefile or spatialite.

Choosing shape file I first tried to specify gid and fid attributes for the new layer but SPIT complains about duplicate field gid when I try to load it into postgis.

If I do not create any attributes for the new layer SPIT happily loads it, presumably duplicating all the features.

I am guessing this is a 'feature' rather than an oversight because if it were possible to share features between layers then then what would happen if a feature were split? If things were to work properly then all layers with referenced that feature would need updating with the new gid of the new portion.

Is this interpretation correct?

Russell

Answer 1

This is a somewhat older question and the correct answer has changed with recent versions of QGIS.

PostGIS Manager has been deprecated, and superseded by DB Manager which works for both PostGIS and SpatiaLite.

DB Manager is installed by default and accessible under the Database > DB Manager menu entry. That will open up the DB Manager window.

Select the database you want to add a layer too and then click Table > Create table. If you don't have a database selected when you click on the Table menu entry, the create table option will not be there.

That will open up the create table dialog which is very similar to the one accessed by Layer > New > New SpatiaLite Layer but with a different layout.

The options are mostly the same with the largest difference being that the New Spatialite Layer dialog allows access to the Specify CRS dialog which makes selecting your coordinate system easier. The Create table dialog requires that you know the SRID number beforehand to enter it.

Answer 2

Using PostGIS Manager plugin, you can create new tables in a PostGIS database.

You can also execute SQL statements such as

CREATE TABLE new_table AS SELECT * FROM old_table

to duplicate a table.