Dec 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

Dec 12, 2012

Geoserver batch create layers from Postgis


Goal is to automatically slurp up all tables in the Postgresql Postgis database in the schema "geodata" and create the geoserver layers.  Then we want to assign a SLD to the layer. Finally create a layer group.


  1. Postgresql PostGIS database exists
  2. Geoserver Workspace exists (demo_wrkspc)
  3. Geoserver Datastore exists (temp_datastore)
  4. Text file list of all table names (tablelist.txt)
  5. curl is installed
  6. SLD style is created and located in a workspace
  7. OS: ran on CentOS 6.3


my bash script saved as
while read p; do
  curl -u username:password -v -XPOST -H 'Content-Type:text/xml' -d '<featureType><name>'$p'</name></featureType>' http://localhost:8080/geoserver/rest/workspaces/demo_wrkspc/datastores/temp_datastore/featuretypes;
done < tablelist.txt

tablelist.txt is a list of the table names in my database here are 3 tables i have in my list.  (originally i had 75 tables)


 Next asign an SLD to a layer

  • nameOfWorkspace  = the name of your workspace
  • nameOfSLD = the name of the SLD you have created
  • defaultStyle true  will check the check box style is enabled
  • advertised false will unselect the check box advertised
  • nameOfWorkspace:NameOfLayer   this is layer you want to assign the sld to  
curl -u username:password -v -XPUT -H 'Content-Type:text/xml' -d '<layer><defaultStyle><workspace>nameOfworkspace</workspace><name>nameOfSLD</name></defaultStyle><enabled>true</enabled><advertised>false</advertised></layer>' http://localhost:8080/geoserver/rest/layers/nameOfWorkspace:NameOfLayer

Bonus create a layer group
  • @ug01.xml is an xml file looks like this

curl -u username:password -XPOST -d @ug01.xml -H 'Content-type: text/xml' http://localhost:8080/geoserver/rest/layergroups


  1. Blog OpenGeo 
  2. Geoserver Rest Doc