Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
SQL Server Management Studio Colors for the Spatial Results Tab
use tempdb
go
create table #polygons
(
polygonShape varchar(max),
color int check (color between 1 and 100),
intensity int check (intensity between 1 and 5)
);
insert #polygons (polygonShape, color, intensity)
values
('10 0, 20 0, 20 10, 10 10, 10 0', 10, 5),
('20 0, 30 0, 30 10, 20 10, 20 0', 20, 5),
('30 0, 40 0, 40 10, 30 10, 30 0', 30, 5),
('40 0, 50 0, 50 10, 40 10, 40 0', 40, 5),
('50 0, 60 0, 60 10, 50 10, 50 0', 50, 5),
('60 0, 70 0, 70 10, 60 10, 60 0', 60, 5),
('70 0, 80 0, 80 10, 70 10, 70 0', 70, 5),
('80 0, 90 0, 90 10, 80 10, 80 0', 80, 5),
('90 0, 100 0, 100 10, 90 10, 90 0', 90, 5),
('0 10, 10 10, 10 20, 0 20, 0 10', 1, 5),
('10 10, 20 10, 20 20, 10 20, 10 10', 11, 5),
('20 10, 30 10, 30 20, 20 20, 20 10', 21, 5),
('30 10, 40 10, 40 20, 30 20, 30 10', 31, 5),
('40 10, 50 10, 50 20, 40 20, 40 10', 41, 5),
('50 10, 60 10, 60 20, 50 20, 50 10', 51, 5),
('60 10, 70 10, 70 20, 60 20, 60 10', 61, 5),
('70 10, 80 10, 80 20, 70 20, 70 10', 71, 5),
('80 10, 90 10, 90 20, 80 20, 80 10', 81, 5),
('90 10, 100 10, 100 20, 90 20, 90 10', 91, 5),
('0 20, 10 20, 10 30, 0 30, 0 20', 2, 5),
('10 20, 20 20, 20 30, 10 30, 10 20', 12, 5),
('20 20, 30 20, 30 30, 20 30, 20 20', 22, 5),
('30 20, 40 20, 40 30, 30 30, 30 20', 32, 5),
('40 20, 50 20, 50 30, 40 30, 40 20', 42, 5),
('50 20, 60 20, 60 30, 50 30, 50 20', 52, 5),
('60 20, 70 20, 70 30, 60 30, 60 20', 62, 5),
('70 20, 80 20, 80 30, 70 30, 70 20', 72, 5),
('80 20, 90 20, 90 30, 80 30, 80 20', 82, 5),
('90 20, 100 20, 100 30, 90 30, 90 20', 92, 5),
('0 30, 10 30, 10 40, 0 40, 0 30', 3, 5),
('10 30, 20 30, 20 40, 10 40, 10 30', 13, 5),
('20 30, 30 30, 30 40, 20 40, 20 30', 23, 5),
('30 30, 40 30, 40 40, 30 40, 30 30', 33, 5),
('40 30, 50 30, 50 40, 40 40, 40 30', 43, 5),
('50 30, 60 30, 60 40, 50 40, 50 30', 53, 5),
('60 30, 70 30, 70 40, 60 40, 60 30', 63, 5),
('70 30, 80 30, 80 40, 70 40, 70 30', 73, 5),
('80 30, 90 30, 90 40, 80 40, 80 30', 83, 5),
('90 30, 100 30, 100 40, 90 40, 90 30', 93, 5),
('0 40, 10 40, 10 50, 0 50, 0 40', 4, 5),
('10 40, 20 40, 20 50, 10 50, 10 40', 14, 5),
('20 40, 30 40, 30 50, 20 50, 20 40', 24, 5),
('30 40, 40 40, 40 50, 30 50, 30 40', 34, 5),
('40 40, 50 40, 50 50, 40 50, 40 40', 44, 5),
('50 40, 60 40, 60 50, 50 50, 50 40', 54, 5),
('60 40, 70 40, 70 50, 60 50, 60 40', 64, 5),
('70 40, 80 40, 80 50, 70 50, 70 40', 74, 5),
('80 40, 90 40, 90 50, 80 50, 80 40', 84, 5),
('90 40, 100 40, 100 50, 90 50, 90 40', 94, 5),
('0 50, 10 50, 10 60, 0 60, 0 50', 5, 5),
('10 50, 20 50, 20 60, 10 60, 10 50', 15, 5),
('20 50, 30 50, 30 60, 20 60, 20 50', 25, 5),
('30 50, 40 50, 40 60, 30 60, 30 50', 35, 5),
('40 50, 50 50, 50 60, 40 60, 40 50', 45, 5),
('50 50, 60 50, 60 60, 50 60, 50 50', 55, 5),
('60 50, 70 50, 70 60, 60 60, 60 50', 65, 5),
('70 50, 80 50, 80 60, 70 60, 70 50', 75, 5),
('80 50, 90 50, 90 60, 80 60, 80 50', 85, 5),
('90 50, 100 50, 100 60, 90 60, 90 50', 95, 5),
('0 60, 10 60, 10 70, 0 70, 0 60', 6, 5),
('10 60, 20 60, 20 70, 10 70, 10 60', 16, 5),
('20 60, 30 60, 30 70, 20 70, 20 60', 26, 5),
('30 60, 40 60, 40 70, 30 70, 30 60', 36, 5),
('40 60, 50 60, 50 70, 40 70, 40 60', 46, 5),
('50 60, 60 60, 60 70, 50 70, 50 60', 56, 5),
('60 60, 70 60, 70 70, 60 70, 60 60', 66, 5),
('70 60, 80 60, 80 70, 70 70, 70 60', 76, 5),
('80 60, 90 60, 90 70, 80 70, 80 60', 86, 5),
('90 60, 100 60, 100 70, 90 70, 90 60', 96, 5),
('0 70, 10 70, 10 80, 0 80, 0 70', 7, 5),
('10 70, 20 70, 20 80, 10 80, 10 70', 17, 5),
('20 70, 30 70, 30 80, 20 80, 20 70', 27, 5),
('30 70, 40 70, 40 80, 30 80, 30 70', 37, 5),
('40 70, 50 70, 50 80, 40 80, 40 70', 47, 5),
('50 70, 60 70, 60 80, 50 80, 50 70', 57, 5),
('60 70, 70 70, 70 80, 60 80, 60 70', 67, 5),
('70 70, 80 70, 80 80, 70 80, 70 70', 77, 5),
('80 70, 90 70, 90 80, 80 80, 80 70', 87, 5),
('90 70, 100 70, 100 80, 90 80, 90 70', 97, 5),
('0 80, 10 80, 10 90, 0 90, 0 80', 8, 5),
('10 80, 20 80, 20 90, 10 90, 10 80', 18, 5),
('20 80, 30 80, 30 90, 20 90, 20 80', 28, 5),
('30 80, 40 80, 40 90, 30 90, 30 80', 38, 5),
('40 80, 50 80, 50 90, 40 90, 40 80', 48, 5),
('50 80, 60 80, 60 90, 50 90, 50 80', 58, 5),
('60 80, 70 80, 70 90, 60 90, 60 80', 68, 5),
('70 80, 80 80, 80 90, 70 90, 70 80', 78, 5),
('80 80, 90 80, 90 90, 80 90, 80 80', 88, 5),
('90 80, 100 80, 100 90, 90 90, 90 80', 98, 5),
('0 90, 10 90, 10 100, 0 100, 0 90', 9, 5),
('10 90, 20 90, 20 100, 10 100, 10 90', 19, 5),
('20 90, 30 90, 30 100, 20 100, 20 90', 29, 5),
('30 90, 40 90, 40 100, 30 100, 30 90', 39, 5),
('40 90, 50 90, 50 100, 40 100, 40 90', 49, 5),
('50 90, 60 90, 60 100, 50 100, 50 90', 59, 5),
('60 90, 70 90, 70 100, 60 100, 60 90', 69, 5),
('70 90, 80 90, 80 100, 70 100, 70 90', 79, 5),
('80 90, 90 90, 90 100, 80 100, 80 90', 89, 5),
('90 90, 100 90, 100 100, 90 100, 90 90', 99, 5)
DECLARE @sql nvarchar(max) = N'';
with nums as
(
select TOP 100 ROW_NUMBER() OVER (ORDER BY (SELECT 1)) n
from sys.messages
),
polygon_strings as
(
select replicate('POLYGON((' + polygonShape + ')),', intensity) as shape, color
from #polygons
union
select 'POLYGON((0 0, 0 1, 1 0, 0 0)),', n
from nums
),
joined_strings as
(
select n, (
select shape as [data()]
from polygon_strings
where color = n
for xml path('') ) as shape
from nums
)
select @sql = @sql + 'SELECT geometry::STGeomFromText(''GEOMETRYCOLLECTION('
+ left(shape, len(shape)-1)
+ ')'', 0) '
+ CASE WHEN n = 100 THEN '' ELSE 'UNION ALL ' END
from joined_strings
order by n;
exec sp_executesql @sql;
drop table #polygons;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment