[postgis-users] True intersection aggregate

[trevorw at fanweb.ca]

I have some trails data that I wish to create a localize surface which
will indicate how many trails exist within a certain distance from any
point on the map. For those who use GRASS the result will be similar to
using point data with v.neighbors.

So I wish to buffer each trail by a specified amount using a query like

create buffers as
select oldid, buffer(the_geom, 1000) as the_geom
from trails;


Next I wish to intersect all over lapping boundaries and count the number
of intersections using a query like

create intersectiontalble as
select oldid, count(*) as trailcount,
intersection_aggregate(the_geom) as the_geom
from buffers;

Now some time ago the following code was posted for intersecting geometry
collections, but I'm not sure how to modify this or to create an aggregate
function which would work with this code to achieve the result I want.

Thanks in advance.

---------------

CREATE FUNCTION Intersection(in_polys geometry) RETURNS geometry AS $$
DECLARE
intersection_result geometry := GeometryN(in_polys, 1);
BEGIN
    FOR i IN 2..NumGeometries(in_polys) LOOP
        intersection_result :=
Intersection(intersection_result,GeometryN(in_polys,i));
    END LOOP;
    RETURN intersection_result;
END;
$$ LANGUAGE plpgsql;