[postgis-users] Creating a grid purely in SQL

[Randall, Eric]

Hi Puneet,

This is typically how I've done it.  This example query builds a .25
degree grid around Pennsylvania.  There are probably better ways....


select row, col, geom
from
(
select yseries+1 as row,xseries+1 as
col,st_setsrid(st_translate(geom,0,yseries * .25),4326) as geom
from
(
select xseries, generate_series(0,15,1) as yseries,
st_translate(geom,xseries * .25,0) as geom
from
(
select generate_series(0,27,1) as xseries,
st_envelope(st_makeline(st_pointn(st_boundary(geom),1),
st_makepoint(st_x(st_pointn(st_boundary(geom),1)) + .25, 
st_y(st_pointn(st_boundary(geom),1)) + .25))) as geom		   
from
(
select
st_envelope(st_makebox2d(st_makepoint(-81,39),st_makepoint(-74,43))) as
geom
) as g1
) as g2
) as g3
) as g4


-Eric


...though the mischief arising from the study of words is
prodigious, we must not consider it as the only cause of darkening
the splendours of Truth, and obstructing the free diffusion of her
light.  Different manners and philosophies have equally contributed
to banish the goddess from our realms, and to render our eyes
offended with her celestial light. Hence we must not wonder that,
being indignant at the change, and perceiving the empire of
ignorance rising to unbounded dominion, she has retired from the
spreading darkness, and concealed herself in the tranquil and
divinely lucid regions of mind.   -Thomas Taylor

-----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: Monday, March 07, 2011 11:13 PM
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