Monday, January 5, 2015

Building Sky Map in SSMS using GeoSpatial methods.

I always try to link my hobbies. This time again I put together SQL and Astronomy.

In this post I will show how to build a Sky Map using GeoSpatial methods.

Will do it by steps:

1. At first we need to get database of stars. You can get it here: "www.astronexus.com/hyg". I've used the latest database "HYG 3.0", but assume there is no problem to use earlier and smaller versions.
If, by any chance, that resource is closed I loaded the data file into my Google drive and you can get it here.
After downloading extract comma separated data file "hygdata_v3.csv".

2. After you get a file upload it to a new SQL Server database on your test server using "SQL Server 2014 Import and Export Data". If you do not know where to find it search for "DTSWizard.exe". It is supposed to be in your SQL "DTS\Binn" folder.
a) Use "Flat File Source" to read the file and check check-box "Column names in the first data row".

b) For the destination create a new database on your test SQL Server:

c)Rename target table, which will be created in the new database:

d) End in the End just run the package to upload the data. Hopefully you won't have any surprises.

3. After you loaded data into SQL you can start working with it in SSMS.
Just go to your new database and run following script:


DECLARE @SpatialTable TABLE (

       Object varchar(50),

       Constellation varchar(50),

       Latitude Float,

       Longitude Float,

       Magnitude Float,

       g geography);



INSERT INTO @SpatialTable(Object, Constellation, Latitude, Longitude, Magnitude, g)

SELECT Proper, Con, CAST(ra as FLOAT)*(-15), [dec], CAST(mag as FLOAT),

       geography::STGeomFromText('POINT('

              + CONVERT(VARCHAR,CAST(ra as FLOAT)*-15)

              + ' ' + CONVERT(VARCHAR,[dec])

              + ')',104001).STBuffer((CAST(mag as FLOAT)*(-1)+5) * 0.002)

FROM dbo.tbl_Stellar_List

WHERE CAST(mag as FLOAT) <= 4.5 and Con != '';



INSERT INTO @SpatialTable(Object, g)

SELECT 'North Pole', geography::STGeomFromText('POINT(0 90)',104001)

UNION ALL

SELECT 'South Pole', geography::STGeomFromText('POINT(0 -90)',104001)

UNION ALL

SELECT 'Map Center', geography::STGeomFromText('POINT(0 0)',104001)

UNION ALL

SELECT 'Map Left', geography::STGeomFromText('POINT(-179.999 0)',104001)

UNION ALL

SELECT 'Map Right', geography::STGeomFromText('POINT(179.999 0)',104001)



SELECT * FROM @SpatialTable;

Then you can get beautiful pictures of Winter Sky constellations.
Here is "Orion":

And that is "Cassiopeia":

The problem with these pictures is that some stars look like ovals. That is because Geometrically they are projected on a Sphere and are curved a little.


Now let me explain the script I use piece by piece:
1. At first I create temporary table variable to include Star Name (Object),  Constellation name, Object's Latitude and Longitude, Magnitude of a Star (Star brightness), and Geography coordinates, understandable by SQL Server.

2. Insert list of stars into the Table Variable. I insert only stars, which are part of any constellation and have magnitude less than 4.5 (that will include only stars visible by naked eye). Astronomical magnitude value is little bit tricky: lover magnitude object is brighter than object with higher magnitude.

3. To build Stars' Geography coordinates I've used following statement:


geography::STGeomFromText('POINT('
    + CONVERT(VARCHAR,CAST(ra as FLOAT)*-15)
    + ' ' + CONVERT(VARCHAR,[dec])
    + ')',104001).STBuffer((CAST(mag as FLOAT)*(-1)+5) * 0.002)


a) At first, I created a "Point" on a sphere using "geography::STGeomFromText" function.
That function requires two parameters: Geometry coordinates and SRID (Spatial Reference Identifier)
Coordinate for a point is defined as 'POINT(1.2345 67.8901)' (Point is defined by two numbers separated by space).
For SRID I used number 104001, which is ID for Microsoft Spheroid (check system table SYS.SPATIAL_REFERENCE_SYSTEMS for the reference)

b) After defining Geography point I used a function "STBuffer()" to transform a dot into a 2D figure. The size of that figure is determined by Star's magnitude by formula: "(CAST(mag as FLOAT)*(-1)+5) * 0.002".

c) To generate a Geography point I had to transform object's Latitude from hours to degrees, to represent it in SQL. I also had to negate the Latitude value, because it has to be mirrored from it's Sky coordinates to Sphere. As a result I've got following formula: "CONVERT(VARCHAR,CAST(ra as FLOAT)*-15)".

4. After loading Stars' coordinates into Table Variable I also inserted additional map coordinates to represent Sky Map correctly. That is not necessary for the full skies, but very crucial when you try to build map of only one constellation. Points are: North, South, Center, Left and Right.

5. The last step is to select data from Table Variable, choose correct projection, Zoom and grid lines if necessary.


Now I want to show how to play with a new Sky Map.

With little modification in the WHERE clause I try to extract only Ursa Major (Great Bear) constellation, also known as Big Dipper:


DECLARE @SpatialTable TABLE (

       Object varchar(50),

       Constellation varchar(50),

       Latitude Float,

       Longitude Float,

       Magnitude Float,

       g geography);



INSERT INTO @SpatialTable(Object, Constellation, Latitude, Longitude, Magnitude, g)

SELECT Proper, Con, CAST(ra as FLOAT)*(-15), [dec], CAST(mag as FLOAT),

       geography::STGeomFromText('POINT('

              + CONVERT(VARCHAR,CAST(ra as FLOAT)*-15)

              + ' ' + CONVERT(VARCHAR,[dec])

              + ')',104001).STBuffer((CAST(mag as FLOAT)*(-1)+5) * 0.002)

FROM dbo.tbl_Stellar_List

WHERE CAST(mag as FLOAT) <= 4.5 and Con = 'Uma';



INSERT INTO @SpatialTable(Object, g)

SELECT 'North Pole', geography::STGeomFromText('POINT(0 90)',104001)

UNION ALL

SELECT 'South Pole', geography::STGeomFromText('POINT(0 -90)',104001)

UNION ALL

SELECT 'Map Center', geography::STGeomFromText('POINT(0 0)',104001)

UNION ALL

SELECT 'Map Left', geography::STGeomFromText('POINT(-179.999 0)',104001)

UNION ALL

SELECT 'Map Right', geography::STGeomFromText('POINT(179.999 0)',104001)



SELECT * FROM @SpatialTable;

As you can see, picture is not very good because constellation has been split on both sides of a map.
In order to fix this problem you have to shift the sphere 180 degrees.
Just add 180 degrees to Latitude value:


DECLARE @SpatialTable TABLE (

       Object varchar(50),

       Constellation varchar(50),

       Latitude Float,

       Longitude Float,

       Magnitude Float,

       g geography);



INSERT INTO @SpatialTable(Object, Constellation, Latitude, Longitude, Magnitude, g)

SELECT Proper, Con, CAST(ra as FLOAT)*(-15), [dec], CAST(mag as FLOAT),

       geography::STGeomFromText('POINT('

              + CONVERT(VARCHAR,CAST(ra as FLOAT)*-15+180)

              + ' ' + CONVERT(VARCHAR,[dec])

              + ')',104001).STBuffer((CAST(mag as FLOAT)*(-1)+5) * 0.002)

FROM dbo.tbl_Stellar_List

WHERE CAST(mag as FLOAT) <= 4.5 and Con = 'Uma';



INSERT INTO @SpatialTable(Object, g)

SELECT 'North Pole', geography::STGeomFromText('POINT(0 90)',104001)

UNION ALL

SELECT 'South Pole', geography::STGeomFromText('POINT(0 -90)',104001)

UNION ALL

SELECT 'Map Center', geography::STGeomFromText('POINT(0 0)',104001)

UNION ALL

SELECT 'Map Left', geography::STGeomFromText('POINT(-179.999 0)',104001)

UNION ALL

SELECT 'Map Right', geography::STGeomFromText('POINT(179.999 0)',104001)



SELECT * FROM @SpatialTable;

Then we have a very nice picture of Big Dipper where you can even notice a double star Mizar-Alkor (the second one on the handle):


Hopefully, I did a detailed enough explanation on how to build Sky Map in SSMS, but if there are any gaps or misunderstandings, please let me know.





Tuesday, December 23, 2014

Happy Spatial Christmas!

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?