Skip to content

Instantly share code, notes, and snippets.

@AlexArcPy
Created March 23, 2018 10:38
Show Gist options
  • Save AlexArcPy/c8b32d6e24c026eee31727888a2a0ade to your computer and use it in GitHub Desktop.
Save AlexArcPy/c8b32d6e24c026eee31727888a2a0ade to your computer and use it in GitHub Desktop.
Multiple Ring Buffer with SQL Server
--simple list of values to generate buffers around
DECLARE @buffer_distances TABLE (dist int);
INSERT @buffer_distances(dist) VALUES (1), (2), (3);
SELECT
p.STATE_NAME, p.CITY_NAME, b.dist, p.Shape.STBuffer(b.dist) AS Poly
FROM
CITIES p
CROSS JOIN
@buffer_distances b
WHERE
p.STATE_NAME = 'Alaska'
ORDER BY
p.CITY_NAME, b.dist;
--using CTE to generate a range table with the start and the end values and the step
WITH buffer_distances AS
(SELECT 5 AS dist
UNION ALL
SELECT dist + 5 AS dist
FROM buffer_distances
WHERE buffer_distances.dist < 20)
SELECT
p.STATE_NAME, p.CITY_NAME, b.dist, p.Shape.STBuffer(b.dist) AS Poly
FROM
CITIES p
CROSS JOIN
buffer_distances b
WHERE
p.STATE_NAME = 'Alaska'
ORDER BY
p.CITY_NAME, b.dist;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment