psycopg2 instering an array of polygons

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

Postgresql: Calculate the number of polygon islands or overlapping groups

This post is about some work I did on how to calculate the number of number of groups of intersecting polygons in an array of polygons in Postgresql


First lets be a little more strict about what the definition of an island is. For the purposes of this algorithm an island consists of a set polygons where at least one polygon in the set overlaps another in the set.


I created a function to calculate the number of islands attached is the plpgsql code for the function.

CREATE OR REPLACE FUNCTION getNumIslands(polys polygon[]) RETURNS INT AS
$BODY$
DECLARE
islandList polygon[]; -- holds the set of obstacles that make up the current island.
islandCount INT; -- holds the count of the number of islands.
poly polygon; -- holds the polygon to start an island search
polys_copy polygon[]; -- A copy of the input list
inddex INT; -- keeps track of the current index in the polys_copy search

BEGIN
    
islandCount := 0;

islandList := polys;
polys_copy := polys;
inddex := 1;

WHILE array_length(polys_copy,1) > 0
LOOP
    -- RAISE NOTICE 'Poly_list length is: %', array_length(polys_copy, 1);
    -- grab the first poly
    poly := polys_copy[1];
    islandList := array_append(ARRAY[]::polygon[], poly);
    -- RAISE NOTICE 'Current island is %', islandList;
    -- remove the first poly because we dont care about it overlaping itself.
    polys_copy := polys_copy[2:array_length(polys_copy, 1)];
    
    -- Make a list of all the polys left that overlap with poly
    WHILE inddex <= array_length(polys_copy, 1)
    LOOP
        -- If the poly_copy overlaps the poly then add its index to the list
        -- of polys to be removed
        FOREACH poly IN ARRAY islandList
        LOOP
            IF polys_copy[inddex] && poly
            THEN
                islandList := array_append(islandList, polys_copy[inddex]);
                --
                IF array_length(polys_copy,1) = 1
                THEN
                    polys_copy := null;
                ELSE
                    polys_copy := polys_copy[1:inddex-1] || polys_copy[inddex+1:array_length(polys_copy, 1)];
                END IF;
                -- RAISE NOTICE 'Intersects with poly index: %', inddex;
                -- When a new intersecting poly is found it needs to be compared
                -- to every poly not just the polys left in the list
                inddex := 0;
                EXIT;
            END IF;
        END LOOP;
        -- RAISE NOTICE 'Current island is %', islandList;
        inddex := inddex + 1;
    END LOOP;
    
    islandCount := islandCount + 1;
    inddex := 1;
END LOOP;

RETURN islandCount;
                
END
                
$BODY$ LANGUAGE plpgsql;

© 2013 Glen Berseth | www.fracturedplane.com