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

Aug 28, 2012

How to batch define projections with arcpy ArcGIS 10

ArcGIS 9.3 had a nice batch define tool in the toolbox that was removed in ArcGIS 10.  ArcGIS 10 has python 2.6.3 for the geoprocessing and well all kinds of fun stuff can be done with arcpy.

So how to batch define the projection for a folder full of shapefiles is the problem at hand. For those who can't wait I posted the code here first and the explanation comes after.

Python Code

# Name: 
# Description: Records the coordinate system information for the specified input dataset or feature class
# Author: ESRI

# import system modules
import arcpy
from arcpy import env
import os

# set workspace environment where the shapefiles are located
env.workspace = "C:\\BaseFolderName\\FolderOfShapefiles"
# creates a list of feature classes or shapefiles in the current workspace
fcs = arcpy.ListFeatureClasses('*') 

# set local variables
    for file in fcs:
        inData = file
        coordinateSystem = "PROJCS[\"MGI_Austria_GK_East\",GEOGCS[\"GCS_MGI\",DATUM[\"D_MGI\",SPHEROID[\"Bessel_1841\",6377397.155,299.1528128]],PRIMEM[\"Greenwich\",0.0],UNIT[\"Degree\",0.0174532925199433]],PROJECTION[\"Transverse_Mercator\"],PARAMETER[\"False_Easting\",0.0],PARAMETER[\"False_Northing\",-5000000.0],PARAMETER[\"Central_Meridian\",16.33333333333333],PARAMETER[\"Scale_Factor\",1.0],PARAMETER[\"Latitude_Of_Origin\",0.0],UNIT[\"Meter\",1.0],AUTHORITY[\"EPSG\",31256]]"
        arcpy.DefineProjection_management(inData, coordinateSystem)

except arcpy.ExecuteError:
    print arcpy.GetMessages(2)
except Exception as e:
    print e.args[0]

First you need to of course change the input folder to your folder where you have the shapefiles you wish to define the projection for.

Second you need to specify the coordinate system you want to set for all your shapefiles.  Here you need to escape out the "  otherwise the code will crash out.  To get this definition just go to your hard drive where ArcGIS is installed and find the folder of the projections and open your projection in a text editor such as notepad++ on Windows or vi on a Linux machine and select all in the text file and copy paste into your python script.

Thats that now just go ahead and run the script.

Have fun

Jul 30, 2012

Golf and Country Club Zürich with GOLFGIS Pin position module

Golf and Country Club Zürich hosted the Omnium Championship from 27.07 until 29.07.2012 using the pin position module to deliver day to day pin position maps for the players.

Feedback from the tournament organization was great.  Lahcen Jabrane (Head Greenkeeper) said "Working with Michael was a great pleasure and having the ability to update the pin positions live on my iPhone on the course saved us a huge amount of time."  The secretary could access Lahcen's updates live on his computer and print out the formated PDF files immediately after the new positions were placed on the course.

Timing is of great importance because the holes are re-located early in the morning depending on the weather and the team needed to be able to change and print the new pin position maps on the fly.

Using the pin position module for mobile and web provided by is a great way for the administration, secretary, tournament referee, team captain and greens keeping team to communicate in real time tournament changes to the players.

Jul 9, 2012

Pin Position Management

GOLFGIS  module  Pin Position Management

Using GIS for the last 15 years or so has lead me into many diverse uses of GIS.  Now recently our GOLFGIS product has taken on the task of managing pin positions.

I thought hey this can't be so difficult, just load a DEM (digital elevation model) map the contours and volla you have a surface model, flow model of how the ball would roll and immediate display of where you can place the pin.  But as the GIS veteran that I am I knew this would be harder.

Lets start with where do I get a DEM or map of the green?  it needs to be like millimeter accurate to be of any use to a golfer.  
  1. Laser scan data?  When available super, when not too costly to produce for a normal course budget
  2. Map it yourself.
We use option 2.  Map it yourself. Using my mapping skills by hand technique learned at University I was quickly up and running mapping, slope, break lines and contours.

Now we need to follow the golf rules for pin position placement.
    1. >= 4 paces from the green edge  (translation into GIS  80cm inner buffer)
    2. >=  4 paces from green edge if bunker is close to green (translation GIS distance from green polygon to bunker is less than xxxx apply rule
    3. 2 to 3 foot area around pin position must not be on a hill and should be relatively flat (GIS: find slope < xxx, within buffer of 2 to 3 feet)
So now we have some golf terms and GIS terms to create our Pin Position Module.

Why should a greenskeeper, super intendant, manager want to use such a tool?

  1. eliminate pin placement errors
  2. increase diversity of play, new course feel every day
  3. reduce maintenance costs by better management
  4. utilize more area for pin placement
  5. edit/print daily pin placements

It pays for itself  (ROI)

  • You: 4-6 hours thinking planning about where to place the pin
  • Cup cutter:  spends 15 - 20% extra time figuring the best position
  • Reduce
    • mateiral costs
    • green wear
    • time
Result = 3-5 Month  ROI

How can you start?  

  1. Contact us per email, phone (+43(0)676 520 3600)
  2. We map your greens (1-2 weeks)
  3. Login into (1-2 seconds)
    1. view greens
    2. manage pin positions
    3. print/edit daily pin placements

 Well there it is GIS has yet another super use to save the environment by reducing waste, increasing productivity, saving material costs and providing great services to its players.


May 7, 2012

Geonode to share golf data

We recently installed a Geonode instance on one of our servers to host golf data here at  The purpose is to share golf maps and golf specific data.

What is golf specific data?
  • Fairways
  • Greens
  • Tee Areas
  • Bunkers
  • Water Hazards
  • Trees
  • Cart Paths
  • Buildings
  • Playing Lines
  • Markers
    • out-of-bounds
    • water-hazard-front
    • water-hazard-side
    • 100m to green front
    • 150m to green front
    • 200m to green front
    • Biotop posts
  • Semi-rough
  • Rough
  • and more...
Now the geometry alone provides valuable information like square meters and perimeter values for each area that the Greenkeeper can use for calculating fertilizer amounts per m2.

Geonode is still a new project based on several open source software projects: Geoserver, Geonetwork, Postgresql, PostGIS, OpenLayers and GeoExt plus Django.

Lots of configuration goodness to get it all play nice together but it works.  Now we are busy making it work better for all the non GIS folk who are going to use this portal to access and use the golf data.

Please let us know if you want more information about  you can reach me at