Wednesday, December 19, 2012

Quantum GIS copy paste geom and attributes into Postgis

Problem: I have a shapefile with 5000 points and I have a Postgis table that I want to add data to from the shapefile.  I want to select 1500 points from the shapefile and use copy paste into the Postgis table.


  1. open qgis add the shapefile layer add the postgis layer
  2. start editing the postgis layer (this is where the new data is going)
  3. select the 1500 points using the table editor based on criteria
  4. select copy
  5. select the postgis layer from the layers menu on the left
  6. select paste
  7. data is pasted
  8. save
  9. ERROR
Änderungen am Layer poi konnten nicht gespeichert werden
Fehler: FEHLER: 14 Objekte nicht hinzugefügt.
PostGIS-Fehler beim Attributhinzufügen: ERROR: duplicate key value violates unique constraint "poi_pkey"
DETAIL: Key (id)=(0) already exists.

What to do?

  1. open attribute table of postgis layer and you should see the data selected and make sure you only see the selected data
  2. click field calculator
  3. select edit existing field
  4. select the ID field i want to update with values
  5. type    $rownum  in the bottom field  
  6. click ok
  7. selected fields will now have a number 1, 2, 3 (this is still a problem because the values 1 for example are already given and cause the Primary Key constraint to yell, so we need to have a sort of auto increment value.
  8. now select the field calculator again with the same data still selected
  9. select only selected checkbox
  10. select update existing field checkbox
  11. now type  $id+94500
    $id is the field  id
    94500 is the highest number in the id field currently so we are just adding 1 for the first row, then adding 2 for the second row...etc for only our selected values.  Now we have added our data and updated the ID field to meet the primary key and NOT NULL constraints.
  12. click ok
  13. done
what happe

No comments:

Post a Comment