Skip site navigation (1) Skip section navigation (2)

Server Programming Interface - spi.exec() overheds issue

From: João Gonçalves <joaofgo(at)gmail(dot)com>
To: PgSQL Novice MailList <pgsql-novice(at)postgresql(dot)org>
Subject: Server Programming Interface - spi.exec() overheds issue
Date: 2008-06-18 17:35:27
Message-ID: 4859475F.7080608@gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
Hi!
I'm not quite sure if this is the right place to discuss this issue but
here goes. I've been testing PL/R language coupled with SPI and postgis 
to produce Voronoi tiles, the following function pushes a set of polygon 
vertexes into an R array and inserts the Voronoi tiles into the 
database. Since my current working dataset has something like 1.5M 
vertexes the overheads built up are huge and the proccess fails due to 
insuficient memory. Also, I can only see the results until all data is 
proccessed.

What is the best way to handle this? Can I flush/purge pg buffers to 
better handle memory issues?
Should I adopt a block processing strategy to narrow down the initial
dataset through a LIMIT statement or something along this line?
Is spi.execute() in read-only mode usable / effective in this context?
Are spi.freetuple or spi.freetuptable usable? How?
Any ideas?

CREATE OR REPLACE FUNCTION voronoi_tiles(TEXT, TEXT, INTEGER) RETURNS
void AS '
    library(deldir)

    gids<-pg.spi.exec(sprintf("SELECT DISTINCT a.poly_gid AS gid FROM
%1$s AS a ORDER BY gid;",arg1))

    for (i in 1:length(gids$gid)){

        # Retrieve points from the auxiliary geometry
        points <- pg.spi.exec(sprintf("SELECT st_x(a.the_geom) AS x,
st_y(a.the_geom) AS y FROM %1$s AS a WHERE a.poly_gid = %2$i;", arg1,
gids$gid[[i]]))

        # External envelope
        xmin<-min(points$x)-abs(min(points$x)-max(points$x))
        xmax<-max(points$x)+abs(min(points$x)-max(points$x))
        ymin<-min(points$y)-abs(min(points$y)-max(points$y))
        ymax<-max(points$y)+abs(min(points$y)-max(points$y))

        # Generate the voronoi object
        voro = deldir(points$x, points$y, digits=6, frac=1e-3,
list(ndx=2,ndy=2), rw=c(xmin,xmax,ymin,ymax))

        # Get the individual tiles/polygons for the Voronoi diagram
        tiles = tile.list(voro)

        for(j in 1:length(tiles)){

            tile<-tiles[[j]]
            geom = "GeomFromText(''LINESTRING("

            for(k in 1:length(tile$x)){
                geom = sprintf("%s %.6f %.6f,", geom, tile$x[[k]],
tile$y[[k]])
            }

            # Close the tile by appending the first vertex
            geom = sprintf("%s %.6f %.6f)'' , %i)", geom, tile$x[[1]],
tile$y[[1]], arg3)

            # Insert into the database
            pg.spi.exec(sprintf("INSERT INTO %1$s (gid, the_geom) VALUES
(%2$i, %3$s)", arg2, gids$gid[[i]], geom))
        }
    }
' LANGUAGE 'plr';

Example:
SELECT voronoi_tiles('test_set', 'output_test_table', <SRID>);







Responses

pgsql-novice by date

Next:From: Richard BroersmaDate: 2008-06-19 03:06:44
Subject: Re: Step 1 with Visual Basic
Previous:From: Richard BroersmaDate: 2008-06-18 01:14:31
Subject: Re: Step 1 with Visual Basic

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group