[postgis-users] ST_Buffer + grid problem

[Pierre Racine]

> I am not sure if this is possible, but I have computed (using ST_Buffer) a sort of
> buffer around several LINESTRINGs. Now I would like to lay some sort of grid of
> say 1m^2 cell size on top of this collection of geometries and then compute the
> intersection... so in the end I would like to for example, know that grid cell ID = 1
> intersects with buffers 1 and 10, grid cell 2 intersects with buffers 7, 10 etc..
> 
> Is there a simple way of doing this in postgis? Maybe someone could point me to
> some documentation of how I can generate such a grid in postgis and maybe
> then I can use just ST_Intersect once I have these two geometries?

With the raster type you can now easily create a vector grid like this:

CREATE TABLE vectorgrid AS
SELECT (gvxy).geom, ((gvxy).x - 1) * rwidth + (gvxy).y gridid
FROM (SELECT ST_PixelAsPolygons(rast) gvxy, ST_Width(rast) rwidth
             FROM (SELECT ST_AsRaster(ST_Extent(geom)::geometry, 1.0, 1.0) rast 
                          FROM yourbuffertable
                         ) foo1
            ) foo2;

Make sure a spatial index exist on both tables:

CREATE INDEX yourbuffertable_geom_idx  ON yourbuffertable USING gist  (geom);
CREATE INDEX vectorgrid _geom_idx  ON vectorgrid USING gist  (geom);

You can then perform a normal intersect query:

CREATE TABLE interresult AS
SELECT b.bufferid, g.gridid, ST_Intersection(g.geom, b.geom) geom
FROM vectorgrid g, yourbuffertable b
WHERE ST_Intersects(g.geom, b.geom);

Pierre