This post is an example of how to insert an array of polygons into the Postgres database using Python and psycopg2.
The most important and problematic part of inserting a polygon array is the string formatting for the polygon[].
The first thing I tried was to just make a string for the exact syntax the Postgresql expects. This was not easy in my case. Probibly mostly because I was reading all of my data from a file and python would wrap qoutes around many of the changes I made in the file.
Next I found that psycopg2 will automatically turn a Python list into the proper syntax for ARRAY[] insertion. This would work great but some of the type checking between Python and psycopg2 lead to an error that the column you are trying to insert into is of type polygon not type text[].
Last I stumbled upon what worked for me. I had to turn the array of polygons into a python list just I could encapsulate the string in double quotes and lastly in the whole thing in single quotes.
Example:
Data:
This is a list of axis aligned box polygons that I am inserting separated by ';'
((-7,-7),(-7,-5.6),(-5.6,-5.6),(-5.6,-7),(-7,-7));((-7,-2.8),(-7,-1.4),(-5.6,-1.4),(-5.6,-2.8),(-7,-2.8));((-7,-0),(-7,1.4),(-5.6,1.4),(-5.6,-0),(-7,-0));((-7,2.8),(-7,4.2),(-5.6,4.2),(-5.6,2.8),(-7,2.8));((-7,5.6),(-7,7),(-5.6,7),(-5.6,5.6),(-7,5.6));((-5.6,-5.6),(-5.6,-4.2),(-4.2,-4.2),(-4.2,-5.6),(-5.6,-5.6));((-5.6,-4.2),(-5.6,-2.8),(-4.2,-2.8),(-4.2,-4.2),(-5.6,-4.2));((-4.2,-5.6),(-4.2,-4.2),(-2.8,-4.2),(-2.8,-5.6),(-4.2,-5.6));((-4.2,-4.2),(-4.2,-2.8),(-2.8,-2.8),(-2.8,-4.2),(-4.2,-4.2));((-4.2,-2.8),(-4.2,-1.4),(-2.8,-1.4),(-2.8,-2.8),(-4.2,-2.8));((-4.2,4.2),(-4.2,5.6),(-2.8,5.6),(-2.8,4.2),(-4.2,4.2));((-4.2,5.6),(-4.2,7),(-2.8,7),(-2.8,5.6),(-4.2,5.6));((-2.8,5.6),(-2.8,7),(-1.4,7),(-1.4,5.6),(-2.8,5.6));((-1.4,-7),(-1.4,-5.6),(-0,-5.6),(-0,-7),(-1.4,-7));((-0,-7),(-0,-5.6),(1.4,-5.6),(1.4,-7),(-0,-7));((1.4,4.2),(1.4,5.6),(2.8,5.6),(2.8,4.2),(1.4,4.2));((2.8,-7),(2.8,-5.6),(4.2,-5.6),(4.2,-7),(2.8,-7));((4.2,5.6),(4.2,7),(5.6,7),(5.6,5.6),(4.2,5.6));((5.6,-5.6),(5.6,-4.2),(7,-4.2),(7,-5.6),(5.6,-5.6));((5.6,2.8),(5.6,4.2),(7,4.2),(7,2.8),(5.6,2.8))
After reading this from a file into a single string I used the following code to format it to psycopg2's liking.
obstacles=valuesDict['box_obstacles'].split(";")
obstacles_string = "{"
for obstacle in obstacles:
obstacles_string = obstacles_string + "\"" + str(obstacle) + "\","
obstacles_string = obstacles_string[:-1] + "}"
self._box_obstacles=obstacles_string
© 2013 Glen Berseth | www.fracturedplane.com