Skip to content

Instantly share code, notes, and snippets.

@mjswart
Created Apr 18, 2017
Embed
What would you like to do?
Guess whose birthday it is!
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
('0 0, 250 0, 250 500, 0 500, 0 0', 95, 5),
('750 0, 1000 0, 1000 500, 750 500, 750 0', 95, 5),
('500 453.125,465.88688 389.49971,448.14519 387.08833,423.4482 399.87691,441.85525 302.1498,427.1776 292.01545,384.07655 340.26644,377.07917 315.76344,367.40169 309.97083,312.89992 321.42995,327.21516 269.3852,324.12141 249.95561,304.69522 240.82536,398.51649 164.61698,402.78417 151.85488,394.57281 124.90781,488.1422 137.684,495.75249 129.97342,491.47225 31.25,507.17864 31.25,504.70627 129.7617,511.87322 137.684,605.44264 124.90781,597.2313 151.85488,601.49897 164.61698,695.32022 240.82536,675.89405 249.95561,672.80028 269.3852,687.11555 321.42995,632.61375 309.97083,622.93625 315.76344,615.93888 340.26644,572.83785 292.01545,558.16017 302.1498,576.56719 399.87691,551.87025 387.08833,534.12855 389.49971,500 453.125', 95, 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