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  ("Josh Tolley" <eggyknap@gmail.com>)
Список 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 по дате отправления:

Предыдущее
От: "Richard Broersma"
Дата:
Сообщение: Re: Step 1 with Visual Basic
Следующее
От: "Richard Broersma"
Дата:
Сообщение: Re: Step 1 with Visual Basic