Server Programming Interface - spi.exec() overheds issue
От | João Gonçalves |
---|---|
Тема | Server Programming Interface - spi.exec() overheds issue |
Дата | |
Msg-id | 4859475F.7080608@gmail.com обсуждение исходный текст |
Ответы |
Re: Server Programming Interface - spi.exec() overheds issue
|
Список | 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>);
В списке pgsql-novice по дате отправления: