<= Drawing a simple picture in SSMS |
Drawing an Ellipse in SSMS => |
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.
