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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-novice by date

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