[postgis-users] Creating a grid purely in SQL

[Pierre Racine]

I would use PostGIS raster:

first:

CREATE OR REPLACE FUNCTION ST_PixelAsPolygons(rast raster, band integer)
    RETURNS SETOF geomval AS
    $$
    DECLARE
        rast alias for $1;
        w integer;
        h integer;
        x integer;
        y integer;
        result geomval;
    BEGIN
        SELECT st_width(rast), st_height(rast)
        INTO w, h;
        FOR x IN 1..w LOOP
             FOR y IN 1..h LOOP
                 SELECT ST_PixelAsPolygon(rast, band, x, y), ST_Value(rast, band, x, y) INTO result;
            RETURN NEXT result;
         END LOOP;
        END LOOP;
        RETURN;
    END;
    $$
    LANGUAGE 'plpgsql';

CREATE FUNCTION ST_PixelAsPolygons(raster) RETURNS SETOF geomval AS
$$
    SELECT ST_PixelAsPolygons($1, 1);
$$
LANGUAGE SQL;

and then

CREATE TABLE cells AS
SELECT cell_id, ST_Centroid((gv).geom) centroid, (gv).geom the_geom
FROM (SELECT generate_series(1, 100) cell_id, ST_PixelAsPolygons(st_makeemptyraster(10, 10, 0, 0, 0.5, 0.5, 0, 0, 4326)) gv) foo

10 and 10 are X and Y

0 and 0 are ulx and uly

0.5 and 0.5 are width 'w' and height 'h'

next two 0 are skewx and skewy

4326 is SRID.

This generate two geometry columns. One for the centroid of the cell and one for the cell itself.

Pierre

>-----Original Message-----
>From: postgis-users-bounces at postgis.refractions.net [mailto:postgis-users-
>bounces at postgis.refractions.net] On Behalf Of Mr. Puneet Kishor
>Sent: 7 mars 2011 23:13
>To: PostGIS Users Discussion
>Subject: [postgis-users] Creating a grid purely in SQL
>
>Given a top-left starting point [ulx, uly], and a cell width 'w' and height 'h', is it possible to
>create a table entirely in SQL populated with rows increasing from left to right up to X and top to
>bottom up to Y. The table schema would be something like --
>
>CREATE TABLE cells (
>  cell_id INTEGER NOT NULL,
>  xmid DOUBLE PRECISION,
>  ymid DOUBLE PRECISION,
>  the_geom GEOMETRY,
>  CONSTRAINT cells_pkey PRIMARY KEY (cell_id)
>);
>
>where xmid = (xmin + xmax) / 2 and ymid = (ymin + ymax) / 2, [xmin, ymin, xmax, ymax] being the
>corners of each cell.
>
>A bonus question -- is it possible to store two geometry columns in one table? For example, if I
>wanted to store the geometry for both the center points [xmin, ymid] as well as the box [xmin, ymin,
>xmax, ymax], would that be possible? Would that even be recommended (for example, to speed up
>queries/drawing, etc.).
>
>Puneet.
>_______________________________________________
>postgis-users mailing list
>postgis-users at postgis.refractions.net
>http://postgis.refractions.net/mailman/listinfo/postgis-users