Aggregation in Maps

[Bill Binko]

Hi everyone,

So I was playing around with various projects and I came across a problem:
I had data that was only meaningful up close, but that I wanted summary
access to.  For example, parcel attributes (like sale_price) are naturally
associated directly with a parcel shape column: however, you often want to
aggregate them (average over an area or the like).

So I created a PL/PGSQL function that took a table, column, number of rows
and number of columns and generated a new table with just a grid of box
shapes that cover the same area.  I call it makeBlocks():

# select makeBlocks('parcels', 'parcel_shape', 'parcel_boxes',
'parcel_box', 10, 10 );

Then, I added columns for attributes I wanted to aggregate:

# alter table parcel_boxes add column avg_sale_price double precision;

And ran a NASTY SLOW query like this:

# update parcel_boxes set avg_sale_price = (select avg(sale_price) from
handyViewWithSalePrice where parcel_shape && parcel_box);

(Obviously, handyView is nastier and better named :)

It takes FOREVER, but when it's done, it works!

It took about 5 minutes for that 10x10 grid, and I'm about to start a
100x100 that might take all day :)

I just thought I'd share the idea (and the PL/PGSQL) with the group.
Please forgive the code: it's my first forray into PL/PGSQL.  If anyone
has any ideas on ways to make it faster (or better) please let me know.

Here's an example of the output (and yes, it uses the gradient support
patch).

http://www.binko.net/blocks.png

Bill


----Start PL/PGSQL code

CREATE OR REPLACE FUNCTION makeBlocks(text, text, text, text, integer, integer) returns text AS '
DECLARE
        source_table ALIAS FOR $1;
        source_col ALIAS for $2;
        dest_table ALIAS for $3;
        dest_col ALIAS for $4;
        num_cols ALIAS for $5;
        num_rows ALIAS for $6;

        thesrid integer;

        block_width double precision := 0;
        block_height double precision  := 0;
        border box3d;
        tmpString text;
        result text;
        therow record;
BEGIN

        thesrid :=      srid from geometry_columns where f_table_name ilike source_table and f_geometry_column ilike source_col;
        EXECUTE \'CREATE TABLE \' || dest_table || \' ( gid serial )\';

        --  RAISE NOTICE ''SRID = %'', thesrid;

        tmpString :=  \'SELECT AddGeometryColumn(''''\' || dest_table || \''''',''''\' || dest_col ||\''''',\' || thesrid::text || \', ''''POLYGON'''', 2 )\';


        EXECUTE tmpString;

        tmpString = \'SELECT box3d(extent(\'|| source_col || \')) as boundary from \' || source_table || \' LIMIT 1\';
        --  RAISE NOTICE ''borderq = %'', tmpString;

        for therow in EXECUTE tmpString LOOP
                border := therow.boundary;
        end loop;
        --  RAISE NOTICE ''borderReult = %'', border;

        DECLARE
        blx double precision;
        bly double precision;
        urx double precision;
        ury double precision;
        insertStmt text;
        BEGIN
                block_width := (xmax(border) - xmin(border)) / num_cols;
                block_height := (ymax(border) - ymin(border))/ num_rows;
                for r in 1 .. num_rows LOOP
                    --  RAISE NOTICE ''r = %'', r;
                    for c in 1 .. num_cols LOOP
                        --  RAISE NOTICE ''c = %'', c;
                        blx :=  xmin(border) + (c-1)*block_width;
                        bly :=  ymin(border) + (r-1)*block_height;
                        urx :=  xmin(border) + (c)*block_width;
                        ury :=  ymin(border) + (r)*block_height;
                        tmpString := \'SETSRID(BOX3D(''''BOX3D(\' || blx || \' \' || bly || \' 0, \' || urx || \' \' || ury || \' 0)''''), \' || thesrid || \')\';
                        --  RAISE NOTICE ''box = %'', tmpString;
                        insertStmt :=  ''insert into  '' || dest_table || ''( '' || dest_col || '') values ( ''||  tmpString || '' ) '';
                        --  RAISE NOTICE ''insert = %'', insertStmt;
                        EXECUTE insertStmt;
                    END LOOP;
                END LOOP;
        END;
        tmpString = \'create index \'|| dest_table || \'_gist on \' || dest_table || \' using GIST ( \' || dest_col || \')\';
        EXECUTE tmpString;

        return ''SUCCESS'';

end;
' LANGUAGE plpgsql;