Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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:

  1. load the layers into QGIS from PostGIS and then save them as shape files in a temp dir.
  2. remove the layers from QGIS and reload them from the shape files.
  3. delete the GID attribute from the layers and save the shape files again.
  4. then use Vector->data managment->merge shape files to create a merged shape file
  5. 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.
share|improve this question
add comment

2 Answers

up vote 7 down vote accepted

How about

CREATE TABLE tableName AS SELECT * FROM table1 UNION SELECT * FROM table2
share|improve this answer
    
THanks! that will do nicely :) I figured that it should be straight forward using SQL but as a long time mysql user I am still coming to grips with Postgres and what you can and can't do with sequences. –  Russell Fulton Apr 11 '13 at 7:59
add comment

As a GUI alternative to @zimmi 's answer. I was able to:

  1. Load two diffrent tables from PostGIS both with gid.
  2. Copy features from layer1 (table1) inside QGIS.
  3. Activated the second layer2 (table2) for editing
  4. Pasted the copied items from layer1 (table1) into layer2.
  5. Saved the edits.

I went on to open the edited layer and the GID field updated correctly with no duplicate values.

SYSTEM: Windows XP, QGIS 1.9.0-Master (OSGeo4W); PostgreSQL 9.2.1 ,PostGIS 2.0

share|improve this answer
    
When I tried this - assuming that by copy you mean via clipboard (on a mac with QGIS 1.8.0) I got errors on saving the combined layer back into PostGIS about duplicate GIDs. When I looked at the attribute table the GIDs were blank for the features that I had added. Maybe 1.9.0 has fixed this behaviour. Bravo if that is the case. –  Russell Fulton Apr 11 '13 at 8:04
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.