Re: Server Programming Interface - spi.exec() overheds issue
От | Josh Tolley |
---|---|
Тема | Re: Server Programming Interface - spi.exec() overheds issue |
Дата | |
Msg-id | e7e0a2570806220857s24c7995l48fbd7e64f54d14c@mail.gmail.com обсуждение исходный текст |
Ответ на | Server Programming Interface - spi.exec() overheds issue (João Gonçalves <joaofgo@gmail.com>) |
Список | pgsql-novice |
On Wed, Jun 18, 2008 at 11:35 AM, João Gonçalves <joaofgo@gmail.com> wrote: > 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>); > > > > > > > > -- > Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-novice > Try using a cursor. See pg.spi.cursor_open in the pl/r documentation. It will allow you to issue the query once, but fetch and process results a little at a time (like your LIMIT idea, but easier). - Josh / eggyknap
В списке pgsql-novice по дате отправления: