[GIS] merging layer stored in PostGIS

postgisqgis

I have a bunch of layers stored in PostGIS that I want to merge. I have spent quite some time investigating various approaches but I ket tripping over getting the GIDs to update properly.

In the end the only way that I found that worked was to:

load the layers into QGIS from PostGIS and then save them as shape files in a temp dir.
remove the layers from QGIS and reload them from the shape files.
delete the GID attribute from the layers and save the shape files again.
then use Vector->data managment->merge shape files to create a merged shape file
finally use SPIT to get the merged file back into PostGIS

The purpose of this 'question' is two fold, firstly to document something at works and secondly to ask if there is a better way of doing it.

Things that I discovered on this journey:

you can not paste features into a PostGIS layer because these don't get the appropriate magic in the GID field to allocate a new number from the sequence. (this is arguably a bug in QGIS ??)
Splitting feature in a PostGIS layer works as it should and the new feature picks up a GID when the layer is saved.

Best Answer

How about

CREATE TABLE tableName AS SELECT * FROM table1 UNION SELECT * FROM table2

Related Solutions

QGIS Merge – How to Merge Multiple Vector Layers to One Layer Using QGIS

You can use MMQGIS tools for merging...

mmqgis

The merge layers tool merges features from multiple layers into a single shapefile and adds the merged shapefile to the project. One or more layers are selected from the "Select Source Layers" dialog list box and an output shapefile name is specified in the "Output Shapefile" dialog field.

Merged layers must all be the same geometry type (point, polygon, etc.). If the source layers have different attribute fields (distinguished by name and type), the merged file will contain a set of all different fields from the source layers with NULL values inserted when a source layer does not have a specific output field.

i hope it helps you...

[GIS] How to avoid duplicate GIDs when copying features from one PostGIS layer to another

I'm not able to reproduce this in QGIS 2.2 Windows, c3a2817.

If other versions behave differently, or if you're continuing to have this problem, you can probably set up a PostgreSQL trigger on the table as a workaround:

Using this example table:

CREATE TABLE testing (gid serial PRIMARY KEY, geom geometry(Polygon, 4326));

Here's a trigger function that will assign a new gid where needed:

CREATE OR REPLACE FUNCTION testing_insert_trigger()
RETURNS trigger AS
$$
BEGIN
IF EXISTS (SELECT 1 FROM testing WHERE gid = NEW.gid) THEN
    NEW.gid := nextval('testing_gid_seq'::regclass);
END IF;
RETURN NEW;
END;
$$ language 'plpgsql';

Binding the function to the table...

CREATE TRIGGER testing_insert 
BEFORE INSERT ON testing
FOR EACH ROW EXECUTE PROCEDURE testing_insert_trigger();

This will automatically assign new IDs where the gid already exists. For example, the following query will now duplicate all data in the table instead of failing:

INSERT INTO testing (SELECT * FROM testing);

Of course, this approach can compromise the intent of your primary key, so use it carefully.

Related Question