Gridding Geometries (or, “Creating patchwork animals in SQL Server”)

This is one of those things that I can’t imagine anybody would ever really want to do but, seeing as I haven’t posted anything for a while, I thought I’d write about it just in case it’s of use to someone…

First, suppose you had an arbitrary geometry. Here’s an example:

DECLARE @snake geometry = geometry::Parse('CIRCULARSTRING(0 0, 5 -5, 10 0, 15 5, 20 0, 25 -5, 29 -1)').STBuffer(2).STDifference(geometry::Parse('MULTIPOINT(28.5 -0.5, 29.75 -0.8)').STBuffer(0.6)).STUnion(geometry::Parse('MULTIPOINT(28.5 -0.5, 29.75 -0.8)').STBuffer(0.2)).STUnion(geometry::Parse('POLYGON((29.2 0.5, 29.5 2.7, 29.79 2.2, 30.22 2.75, 30 0.5, 29.2 0.5))'));
SELECT @snake;

In in the immortal words of Rolf Harris, “can you guess what it is yet?”.

Yes, that’s right – it’s a snake and it looks like this:

Now suppose that you wanted to divide that geometry up into a series of individual polygonal tiles on a regular-spaced grid. You could do so using the following T-SQL:

-- Determine the extent of the grid required to cover the geometry
DECLARE @grid geometry = @snake.STEnvelope();
DECLARE @gridwidth float = @grid.STPointN(3).STX - @grid.STPointN(1).STX;
DECLARE @gridheight float = @grid.STPointN(3).STY - @grid.STPointN(2).STY;

-- Work out the lower-left corner of the grid
DECLARE @xoffset float = @grid.STPointN(1).STX,
        @yoffset float = @grid.STPointN(1).STY;

-- Specify the number of columns and rows in the grid
DECLARE @numcolumns float = 80, @numrows float = 35;

-- Work out the height and width of each tile
DECLARE @cellwidth float = @gridwidth / @numcolumns;
DECLARE @cellheight float = @gridheight / @numrows;

-- Create a table variable to hold the tiles
DECLARE @gridcells table (id int, geom geometry)

-- Now, loop through and cut up the geometry into tiles
DECLARE @x int = 0, @y int = 0;
WHILE @y < @numrows
BEGIN
WHILE @x < @numcolumns
BEGIN
INSERT INTO @gridcells VALUES(
@y * @numcolumns + @x,
'POLYGON((' + cast(@xoffset + (@x * @cellwidth) AS varchar(32)) + ' ' + cast(@yoffset + (@y * @cellheight) AS varchar(32)) + ','
+ cast(@xoffset + ((@x + 1)  * @cellwidth) AS varchar(32)) + ' ' + cast(@yoffset + (@y * @cellheight) AS varchar(32)) + ','
+ cast(@xoffset + ((@x + 1)  * @cellwidth) AS varchar(32)) + ' ' + cast(@yoffset + ((@y + 1) * @cellheight) AS varchar(32)) + ','
+ cast(@xoffset + (@x * @cellwidth) AS varchar(32)) + ' ' + cast(@yoffset + ((@y + 1) * @cellheight) AS varchar(32)) + ','
+ cast(@xoffset + (@x * @cellwidth) AS varchar(32)) + ' ' + cast(@yoffset + (@y * @cellheight) AS varchar(32)) + '))'
)
SET @x = @x + 1;
END
SET @x = 0;
SET @y = @y + 1;
END

-- Finally, select the tiles from the table variable
SELECT geom.STIntersection(@snake) FROM @gridcells ORDER BY id;

My snake has now been chopped up into 800 little tiles, and here’s what the result looks like: