SQL Server: Welcome to SQL Server Day – Spatial style

Hi Friends,

On 23 feb 2013, we had one of the largest SQL Server Day in Bangalore with more than 150 SQL enthusiasts turning out for the event. I am yet to blog about that. But what I didn’t want to delay is publishing the spatial code which people wanted and have been sending me requests.

So what does the spatial code display?

Well, see it for yourself. Here it is. Execute the code. Go to Spatial results tab.

DECLARE @Geom1 GEOMETRY = 'LINESTRING (83 496, 83 495, 83 494, 83 492, 83 489, 83 486, 83 484, 83 480, 83 479, 83 476, 83 471, 83 467, 82 462, 82 458, 82 456, 82 453, 82 450, 82 449, 82 446, 82 445, 82 443, 84 440, 85 438, 85 437, 86 435, 87 433, 88 433, 90 433, 93 432, 97 431, 99 431, 100 431, 102 431, 106 431, 110 432, 113 433, 115 436, 117 439, 118 442, 119 445, 120 449, 120 451, 121 452, 121 453, 121 454, 121 455, 121 454, 122 450, 123 445, 125 443, 126 441, 127 440, 129 438, 133 437, 134 436, 140 436, 145 435, 154 436, 159 437, 162 440, 164 448, 165 459, 166 468, 165 479, 165 483, 165 490, 165 492, 165 493)'
SET @Geom1 = @Geom1.MakeValid()
DECLARE @Geom2 GEOMETRY = 'LINESTRING (180 458, 180 457, 181 456, 182 454, 184 453, 186 452, 190 452, 195 452, 197 452, 198 453, 200 457, 202 460, 203 463, 204 465, 204 466, 204 467, 204 468, 202 468, 199 469, 197 469, 195 469, 194 469, 193 469, 191 469, 189 468, 188 468, 187 467, 186 466, 184 465, 183 464, 182 463, 181 462, 180 460, 180 459, 179 459, 179 456, 179 454, 179 452, 179 450, 179 447, 180 444, 181 443, 182 442, 183 440, 186 440, 188 438, 192 435, 196 434, 200 433, 201 433, 207 433, 210 434, 212 434, 216 436, 218 438, 220 440, 221 441, 221 442)'
SET @Geom2 = @Geom2.MakeValid()
DECLARE @Geom3 GEOMETRY = 'LINESTRING (241 497, 241 496, 241 491, 241 483, 241 479, 241 472, 241 467, 241 463, 240 459, 240 455, 240 451, 240 449, 240 447, 240 443, 240 441, 240 439, 241 439, 241 437, 241 436, 241 435, 241 434, 241 433, 242 433, 242 434, 242 436)'
SET @Geom3 = @Geom3.MakeValid()
DECLARE @Geom4 GEOMETRY = 'LINESTRING (290 474, 290 473, 289 473, 288 473, 285 473, 279 473, 275 472, 273 471, 267 470, 264 467, 262 465, 260 464, 259 462, 258 460, 257 457, 256 454, 255 451, 255 448, 255 446, 255 444, 255 443, 255 442, 255 440, 257 438, 259 438, 261 437, 263 435, 266 435, 269 434, 273 433, 274 433, 279 433, 282 432, 287 432, 291 432, 293 432, 295 432, 296 433, 297 433, 297 434, 297 435, 297 437)'
SET @Geom4 = @Geom4.MakeValid()
DECLARE @Geom5 GEOMETRY = 'LINESTRING (332 467, 331 467, 329 467, 325 466, 322 465, 318 464, 315 462, 312 460, 310 457, 310 452, 310 448, 310 445, 310 441, 310 438, 312 435, 313 433, 314 432, 319 431, 323 430, 325 430, 330 430, 336 430, 340 431, 344 435, 346 438, 348 444, 349 448, 349 454, 348 461, 346 465, 345 467, 344 469, 343 469, 342 469, 339 469, 338 469, 336 469, 335 469, 334 469, 333 469, 331 469, 330 469, 329 469)'
SET @Geom5 = @Geom5.MakeValid()
DECLARE @Geom6 GEOMETRY = 'LINESTRING (355 462, 355 463, 358 464, 361 465, 362 465, 364 465, 366 465, 367 463, 371 458, 372 452, 373 447, 374 443, 374 439, 374 435, 373 433, 373 432, 373 435, 373 446, 373 454, 373 459, 374 465, 375 466, 376 467, 378 467, 382 468, 383 468, 384 468, 384 466, 386 464, 388 459, 389 454, 390 449, 390 444, 391 439, 391 433, 391 431, 391 430, 391 431, 391 440, 391 445, 391 447, 391 448, 390 449, 390 451, 390 452, 391 455, 396 458, 396 459, 397 459, 398 461, 401 462, 404 463, 406 463, 408 464, 412 463, 413 463, 414 459, 414 454, 414 451, 414 447, 414 445, 414 442, 414 439, 415 436, 415 434, 415 437)'
SET @Geom6 = @Geom6.MakeValid()
DECLARE @Geom7 GEOMETRY = 'LINESTRING (428 456, 429 456, 436 456, 443 456, 447 458, 455 463, 458 465, 458 466, 459 468, 459 471, 459 472, 459 473, 457 474, 451 475, 446 475, 442 475, 438 474, 433 471, 430 469, 427 467, 425 462, 425 457, 424 452, 424 447, 425 441, 427 435, 430 431, 435 427, 443 426, 450 425, 458 425, 464 425, 468 425, 471 427, 474 428, 475 429)'
SET @Geom7 = @Geom7.MakeValid()
DECLARE @Geom8 GEOMETRY = 'LINESTRING (228 394, 228 393, 229 384, 229 378, 229 371, 230 368, 231 363, 231 357, 232 352, 232 348, 232 345, 232 344, 232 345)'
SET @Geom8 = @Geom8.MakeValid()
DECLARE @Geom9 GEOMETRY = 'LINESTRING (222 367, 223 367, 232 368, 243 367, 244 367, 245 367)'
SET @Geom9 = @Geom9.MakeValid()
DECLARE @Geom10 GEOMETRY = 'LINESTRING (263 370, 263 371, 262 371, 259 371, 257 371, 255 368, 255 364, 254 360, 254 356, 254 353, 254 350, 257 349, 261 348, 264 348, 267 349, 268 351, 270 357, 268 369, 266 375, 265 376, 264 377, 263 377, 261 377, 260 377)'
SET @Geom10 = @Geom10.MakeValid()
DECLARE @Geom11 GEOMETRY = 'LINESTRING (98 295, 98 296, 98 299, 95 304, 92 308, 88 312, 83 314, 78 314, 69 312, 62 305, 58 299, 57 293, 58 285, 59 281, 63 276, 66 272, 69 270, 72 269, 77 270, 80 271, 83 273, 86 273, 89 274, 94 275, 98 275, 102 275, 104 272, 106 265, 107 259, 107 254, 105 247, 103 243, 95 240, 87 238, 80 235, 75 234, 69 234, 67 236, 65 239, 63 243, 63 247, 64 251, 64 252, 64 253, 65 253)'
SET @Geom11 = @Geom11.MakeValid()
DECLARE @Geom12 GEOMETRY = 'LINESTRING (142 304, 142 305, 141 305, 139 305, 132 305, 126 300, 121 293, 119 281, 118 263, 118 252, 118 247, 123 242, 128 239, 137 239, 147 242, 153 247, 157 256, 157 271, 155 283, 154 293, 153 297, 151 300, 151 303, 149 304, 145 304, 141 304, 138 304, 137 304)'
SET @Geom12 = @Geom12.MakeValid()
DECLARE @Geom13 GEOMETRY = 'LINESTRING (142 265, 143 265, 149 263, 151 261, 154 252, 156 247, 158 242, 160 237, 160 235, 160 236)'
SET @Geom13 = @Geom13.MakeValid()
DECLARE @Geom14 GEOMETRY = 'LINESTRING (177 308, 177 306, 177 296, 176 286, 176 275, 176 268, 177 262, 177 257, 177 253, 177 250, 178 248, 178 246, 178 242, 178 241, 178 240, 180 240, 195 241, 200 241, 209 242, 214 243, 217 243, 219 243)'
SET @Geom14 = @Geom14.MakeValid()
DECLARE @Geom15 GEOMETRY = 'LINESTRING (284 304, 284 305, 281 306, 276 309, 270 313, 260 314, 251 313, 245 312, 239 308, 236 297, 235 285, 236 273, 238 267, 240 265, 243 263, 252 263, 260 263, 268 263, 269 263, 271 258, 272 255, 272 252, 271 247, 264 243, 259 241, 252 240, 245 240, 242 239, 241 239, 242 240, 245 240)'
SET @Geom15 = @Geom15.MakeValid()
DECLARE @Geom16 GEOMETRY = 'LINESTRING (283 268, 285 268, 303 268, 310 268, 312 271, 315 280, 315 283, 315 284, 312 285, 308 286, 300 285, 297 283, 293 283, 288 280, 287 276, 286 273, 286 269, 286 265, 286 264, 286 259, 287 256, 289 253, 293 250, 300 247, 305 247, 308 247, 315 247, 318 248, 323 249, 326 250, 326 251)'
SET @Geom16 = @Geom16.MakeValid()
DECLARE @Geom17 GEOMETRY = 'LINESTRING (334 249, 334 248, 339 252, 347 273, 350 289, 349 295, 344 301, 342 302, 337 302, 334 300, 332 292, 332 287, 335 283, 344 281, 350 281, 354 283, 361 285, 363 285, 363 284, 363 280, 362 276, 361 269, 361 264, 361 260, 362 254, 364 251, 366 249, 370 248, 376 248, 376 249, 376 250, 377 253)'
SET @Geom17 = @Geom17.MakeValid()
DECLARE @Geom18 GEOMETRY = 'LINESTRING (377 294, 377 293, 382 281, 386 272, 390 261, 392 256, 393 254, 394 254, 395 261, 398 282, 398 290, 399 294, 399 296, 400 297, 401 298)'
SET @Geom18 = @Geom18.MakeValid()
DECLARE @Geom19 GEOMETRY = 'LINESTRING (406 278, 408 278, 420 277, 423 277, 426 279, 428 281, 428 285, 427 288, 426 289, 423 289, 420 290, 419 290, 417 290, 415 289, 413 288, 410 286, 408 282, 406 279, 405 274, 404 268, 403 261, 403 255, 404 250, 407 246, 412 245, 422 244, 429 246, 434 247, 436 249, 439 250, 440 250, 442 252)'
SET @Geom19 = @Geom19.MakeValid()
DECLARE @Geom20 GEOMETRY = 'LINESTRING (454 246, 454 247, 457 250, 459 258, 459 263, 461 268, 461 271, 461 272, 461 273, 462 273, 462 274, 462 276, 461 279, 460 281, 460 283, 460 284, 458 287, 457 288, 456 289, 454 289, 453 289, 451 287, 451 284, 451 281, 452 278, 453 277, 456 277, 460 277, 466 277, 472 279, 476 280, 481 280, 484 281, 484 280, 483 278, 481 274, 479 268, 477 263, 477 261, 477 257, 477 253, 477 251, 479 247, 480 246, 484 244, 488 244, 493 244, 495 245, 496 245, 496 246, 496 247)'
SET @Geom20 = @Geom20.MakeValid()
DECLARE @Geom21 GEOMETRY = 'LINESTRING (543 334, 543 333, 543 313, 543 294, 544 283, 544 272, 545 259, 546 247, 546 244, 547 244, 547 250)'
SET @Geom21 = @Geom21.MakeValid()
DECLARE @Geom22 GEOMETRY = 'LINESTRING (535 325, 535 326, 540 330, 549 332, 557 332, 563 331, 572 329, 578 324, 581 315, 583 304, 583 291, 583 277, 583 268, 580 257, 577 250, 573 247, 567 245, 565 243, 560 242, 555 242, 549 241, 543 241, 541 241, 539 241, 538 241)'
SET @Geom22 = @Geom22.MakeValid()
DECLARE @Geom23 GEOMETRY = 'LINESTRING (616 281, 615 281, 614 281, 611 281, 606 280, 603 275, 602 268, 602 263, 604 260, 608 258, 614 257, 617 259, 619 262, 620 272, 620 280, 618 283, 617 284, 617 283, 617 274, 617 267, 617 265, 617 262, 619 258, 620 257, 621 257)'
SET @Geom23 = @Geom23.MakeValid()
DECLARE @Geom24 GEOMETRY = 'LINESTRING (632 284, 632 283, 632 275, 632 266, 634 261, 638 258, 643 257, 658 257, 664 260, 666 275, 666 287, 666 288, 665 288, 663 274, 662 263, 662 250, 662 240, 660 233, 655 225, 644 217, 628 210, 618 206, 609 204, 608 204)'
SET @Geom24 = @Geom24.MakeValid()
DECLARE @Geom25 GEOMETRY = 'LINESTRING (315 185, 315 184, 316 184, 316 183, 316 181, 317 181, 317 180, 317 183, 316 184, 316 183, 316 182, 316 181, 316 180, 316 179, 317 179, 318 179, 318 181, 318 183, 318 184, 317 184, 316 184, 315 184, 315 183, 315 182, 315 181, 316 180, 317 180, 318 180, 319 180, 319 183, 319 186, 318 187, 317 187, 316 187, 316 186, 316 185, 316 183, 31
6 181, 316 180, 316 178, 317 177, 319 177)'
SET @Geom25 = @Geom25.MakeValid()
DECLARE @Geom26 GEOMETRY = 'LINESTRING (317 159, 317 160, 316 160, 316 158, 316 156, 316 153, 316 152, 317 151, 318 151, 321 155, 319 157, 318 160, 317 160, 317 159, 317 157, 317 156, 317 155, 318 155, 319 157, 319 156, 319 155, 319 154, 319 153, 320 153, 320 152, 320 153)'
SET @Geom26 = @Geom26.MakeValid()
DECLARE @Geom27 GEOMETRY = 'LINESTRING (329 197, 330 197, 334 194, 338 189, 340 186, 344 181, 346 176, 346 171, 346 163, 346 157, 344 151, 343 146, 342 144, 342 143, 340 141, 339 139, 338 138, 337 137, 336 137, 335 136, 335 135, 334 135)'
SET @Geom27 = @Geom27.MakeValid()
	 
select @Geom1.STBuffer(5)
union all
select @Geom2.STBuffer(5)
union all
select @Geom3.STBuffer(5)
union all
select @Geom4.STBuffer(5)
union all
select @Geom5.STBuffer(5)
union all
select @Geom6.STBuffer(5)
union all
select @Geom7.STBuffer(5)
union all
select @Geom8.STBuffer(5)
union all
select @Geom9.STBuffer(5)
union all
select @Geom10.STBuffer(5)
union all
select @Geom11.STBuffer(5)
union all
select @Geom12.STBuffer(5)
union all
select @Geom13.STBuffer(5)
union all
select @Geom14.STBuffer(5)
union all
select @Geom15.STBuffer(5)
union all
select @Geom16.STBuffer(5)
union all
select @Geom17.STBuffer(5)
union all
select @Geom18.STBuffer(5)
union all
select @Geom19.STBuffer(5)
union all
select @Geom20.STBuffer(5)
union all
select @Geom21.STBuffer(5)
union all
select @Geom22.STBuffer(5)
union all
select @Geom23.STBuffer(5)
union all
select @Geom24.STBuffer(5)
union all
select @Geom25.STBuffer(5)
union all
select @Geom26.STBuffer(5)
union all
select @Geom27.STBuffer(5)

I used Spatial Viewer tool for this developed by Simon Sabin.