Tuesday, December 23, 2014

Happy Spatial Christmas!

Building Sky Map in SSMS =>

Christmas is pretty good time to exercise my newly acquired skills using Spatial Data.

For that event I decided to build a Christmas Tree in SSMS.



Here is the code to draw the Tree:



DECLARE @g TABLE (g GEOMETRY, ID INT IDENTITY(1,1));



-- Adjust Color

INSERT INTO @g(g) SELECT TOP 29 CAST('POLYGON((0 0, 0 0.0000001, 0.0000001 0.0000001, 0 0))' as geometry) FROM sys.messages;

-- Build Christmas Tree

INSERT INTO @g(g) VALUES (CAST('POLYGON((0 0,900 0,450 400, 0 0 ))' as geometry).STUnion(CAST('POLYGON((80 330,820 330,450 640,80 330 ))' as geometry)).STUnion(CAST('POLYGON((210 590,690 590,450 800, 210 590 ))' as geometry)));

-- Adjust Color

INSERT INTO @g(g) SELECT TOP 294 CAST('POLYGON((0 0, 0 0.0000001, 0.0000001 0.0000001, 0 0))' as geometry) FROM sys.messages;

-- Build a Star

INSERT INTO @g(g) VALUES (CAST('POLYGON ((450 910, 465.716 861.631, 516.574 861.631, 475.429 831.738, 491.145 783.369, 450 813.262, 408.855 783.369, 424.571 831.738, 383.426 861.631, 434.284 861.631, 450 910))' as geometry));

-- Build Colored Balls

INSERT INTO @g(g) SELECT TOP 2 CAST('POLYGON((0 0, 0 0.0000001, 0.0000001 0.0000001, 0 0))' as geometry) FROM sys.messages;

INSERT INTO @g(g) VALUES (CAST('CURVEPOLYGON (CIRCULARSTRING (80 290, 110 320, 140 290, 110 260, 80 290))' as geometry));

INSERT INTO @g(g) SELECT TOP 2 CAST('POLYGON((0 0, 0 0.0000001, 0.0000001 0.0000001, 0 0))' as geometry) FROM sys.messages;

INSERT INTO @g(g) VALUES (CAST('CURVEPOLYGON (CIRCULARSTRING (760 290, 790 320, 820 290, 790 260, 760 290))' as geometry));

INSERT INTO @g(g) SELECT TOP 3 CAST('POLYGON((0 0, 0 0.0000001, 0.0000001 0.0000001, 0 0))' as geometry) FROM sys.messages;

INSERT INTO @g(g) VALUES (CAST('CURVEPOLYGON (CIRCULARSTRING (210 550, 240 580, 270 550, 240 520, 210 550))' as geometry));

INSERT INTO @g(g) SELECT TOP 46 CAST('POLYGON((0 0, 0 0.0000001, 0.0000001 0.0000001, 0 0))' as geometry) FROM sys.messages;

INSERT INTO @g(g) VALUES (CAST('CURVEPOLYGON (CIRCULARSTRING (630 550, 660 580, 690 550, 660 520, 630 550))' as geometry));



SELECT g FROM @g ORDER BY ID;

GO

In this exercise I've used following Spatial Data  and a method:
POLYGON - 2-Dimensional surface area ("Tree", "Star", and "color adjustment" area)
CIRCULARSTRING - Collection of circular arc segments (Ball circles)
CURVEPOLYGON - 2-Dimensional surface area defined by a ring ("Colored Balls")
.STUnion - Method for unionizing two geometry instances.

Drawing has been done in following steps:
1. Adjusted a color for the Tree. I draw 29 dummy triangles to get the 30st color in SSMS palette, which is kind of Green.
2. Built three POLYGON - triangles and unionize them.
3. Did another color adjustment to get goldish color for the Star.
4. Built a POLYGON in a shape of star.
5. Built four colored circles with color adjustments before each of them.
6. Run the script.
7. Switch to "Spatial Result" tab in SSMS.

That was easy. Isn't it?



Tuesday, December 9, 2014

Learning Every Day: Case Sensitive Replace

During some T-SQL programming I needed to add spaces before some characters within a string.
The problem was that characters were in upper and lover case and I had to preserve their cases.

Idea for the solution was found in "stackoverflow.com".

Here is how it can be done:

SELECT REPLACE('1C2c3' COLLATE Latin1_General_CS_AS, 'C', ' C');
SELECT REPLACE('4C5c6' COLLATE Latin1_General_CS_AS, 'c', ' c');

And here is the result:

--------
1 C2c3
--------
4C5 c6

The trick is in using "Latin1_General_CS_AS" case sensitive collation to do the replace.

You can also use that method for a search. For example two queries below will return different results:

SELECT CHARINDEX('C', '1C2c3' COLLATE Latin1_General_CS_AS);
SELECT CHARINDEX('c', '1C2c3' COLLATE Latin1_General_CS_AS);

Sunday, December 7, 2014

Pictures from SQL Saturday #347 in DC

<= SQL Saturday #315 - Pittsburgh, PA


Here are picture from actual SQL Saturday #347 in DC.

SQL Saturday happened in Microsoft building in Chevy Chase, Maryland.


At first I was greeted by brave team of volunteers at the reception desk.
They gave me a wonderful "SQL Saturday #347"  coffee cup.

My first session was with Allen White about "Realtime ETL with Service Broker"  if you are interested, you can download it here.

While other rooms were packed, our room was half empty on another side of the hallway. 

Attendees are waiting for the next session.

Speakers' room


Sponsors





  My second session that day was with Reeves Smith. He was talking about "Evaluation Context with DAX, the Language of PowerPivot".







I did not make a lot of pictures on that event because I had my own session and had to be prepared well.
So, here are pictures from the closing ceremony.














Organizers claim that there were about 250 attendees. Biggest SQL Saturday in DC so far.
First-timers are rising their hands.

Raffling. Lucky winners.


























Last set of pictures is from after event party