Equatorial to Galactic and Trig calc PLS

[ARGHouse]

I'm working on 3D starmaps in Astrosynth and Pioneer, I usually have to go through around 5 to 10 minutes hopping between my calculator and various online calculators and converters so I can convert Equatorial in to Galactic, and then calculate the trigonomic values so I can get cartesian co-ordinates. Would be handy if this calculator also had an Equatorial to Galactic converter, along with a right angle trig calculator for getting cartesian co-ordinates.

[jchylik]

And by Equatorial coordinates you mean with respect to equator of Earth, or also equator of some other planets?

[ARGHouse]

Yep in respect to Earth, RA and Dec.

[lortordermur]

This is in respect to the equator of Earth. I already found some formulas: Conversion of coordinates . Also see Google search "equatorial to galactic coordinates formula".

Such a calculator should be doable in SFCalcsheet, although I am a bit weak with trigonometry.

[jchylik]

Such a calculator should be doable in SFCalcsheet

It is definitely doable, but the tricky thing is checking in the excel sheet whether the input angle is in degrees or radians.

[lortordermur]

I would standardize on degrees and include a degree <-> radian converter on the "Conversion" sheet.

[lortordermur]

I am in the process of finally implementing the calculator but my math skills are not that great in the trigonometry and equations areas. After some tinkering I got the formula for galactic latitude right and the result is correct^1, however I still don't manage to solve for galactic longitude.

I tried most of the formulas from those sites:^2^4 The main matter is that I don't know how to solve for l in the left side of the equation (l - l0).

Here is a work-in-progress copy of the spreadsheet with the interim results visible: sfcalcsheet.ods

The versions of the galactic longitude formula are in cells G47-G49 and H49-J49. Maybe someone with better math skills can have a look at it.

[jchylik]

The main matter is that I don't know how to solve for l in the left side of the equation (l - l0).

If you mean the equation 10 (section "3.4.2. Transformation"), then it is pretty straightforward. The functions arcsin and arccos are both available in majority of Spreadsheets.

For example in Libre Office it is: =ASIN( ) =ACOS( )

[lortordermur]

It is 3.2.1 but the principle is the same.

Yes, I already use ARCSIN/ARCCOS as the inverse operation before I convert to degrees. The issue is the l - l0 part on the left side of the equals sign in the second and third formula. So far I just add l0 at the end of the calculation but that doesn't seem to be the correct approach to just have l on the left side, i.e. eliminate the l0.

[jchylik]

The versions of the galactic longitude formula are in cells G47-G49 and H49-J49.

Please, on which sheet is it? I am having a difficulty to find it.

[lortordermur]

It is on "Conversion", the purple one. Fifth calculator from the top.

[jchylik]

It is on "Conversion", the purple one. Fifth calculator from the top.

I have freshly downloaded the latest version, but it does not appear there. The fifth calculator is still Degrees <-> Radians

[lortordermur]

I linked a work copy of the sheet in a previous issue comment.

https://github.com/lortordermur/sfcalcsheet/issues/5#issuecomment-2289237342

[jchylik]

I see.

There are a couple of typos, the first one I can see is:

G47 was missing "RADIANS"

=DEGREES(ACOS(COS(G46 - G45) * COS(H46) / COS(H47)) + I45)

=DEGREES(ACOS(COS(G46 - G45) * COS(H46) / COS(RADIANS(H47))) + I45)

I will have a deeper look at it and write more.

[lortordermur]

Great, if we get the math figured out and the calculator can be implemented I can give you credit in the README if you like.

[lortordermur]

I got it solved by correcting the formula in J49 (which I had generated using an online solver) to have the H47 value in radians and then converting everything to degrees.

Now working on the reverse calculator. From what I have read that should be doable by keeping the formulas and swapping a few variables around.

Thank you for taking the time to help out. Can I mention you in an acknowledgements section in the README?

[lortordermur]

@ARGHouse: The converter between equatorial and galactic coordinates is now complete and in the git repo. Any feedback? And how would the calculator for cartesian coordinates work? Do you mean something that makes a vector from RA, Dec and distance and calculates a resulting point in space?

[ARGHouse]

I'll try it out, thanks.

On Fri, 16 Aug 2024, 3:50 am lortordermur, @.***> wrote:

@ARGHouse https://github.com/ARGHouse: The converter between equatorial and galactic coordinates is now complete and in the git repo. Any feedback? And how would the calculator for cartesian coordinates work? Do you mean something that makes a vector from RA, Dec and distance and calculates a resulting point in space?

— Reply to this email directly, view it on GitHub https://github.com/lortordermur/sfcalcsheet/issues/5#issuecomment-2291849526, or unsubscribe https://github.com/notifications/unsubscribe-auth/AIH42YJ2SNCP5JJSPRVRBZ3ZRTS4XAVCNFSM6AAAAABMQV5F6OVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDEOJRHA2DSNJSGY . You are receiving this because you were mentioned.Message ID: @.***>

[jchylik]

The converter between equatorial and galactic coordinates is now complete and in the git repo. Any feedback?

I have had a look at it, it works majority of angles, but there is an issue with ((galactic -> equatorial)) for some values on the opposite site of the circle. For example:

b	l	alpha	delta
0.	212.	282.43	0.82
0.	213.	102.89	-0.06

I think it is either due to bounds of used functions, or some typo. I will have a closer look at it.

Can I mention you in an acknowledgements section in the README?

You can wait with mentioning me until I add the promised calculation of radiation effects on the atmosphere.

[lortordermur]

Okay. For easier debugging you can make the hidden calculations visible again by unprotecting the Conversion sheet (right-click the tab), selecting the cells and and changing the cell style from "InterimResult" to "StandardBackground".

[jchylik]

debugging

The issue seems to be in the cell G61: =DEGREES(ATAN((COS(G60 - J54)) / (COS(H54) * TAN(H60) - SIN(H54) * SIN(G60 - J54))) + G54)

Which does not agree with formulas on inverse transform. [^1]

[^1]: Wikipedia: Conversion between equatorial and galactic coordinates

[lortordermur]

That is the formula I obtained using an equation solver^1 from, I think, the third formula under "3.2.2 Transformation" in ^2. I solved for α.

[lortordermur]

By the way, for it to solve, I had to replace the constants α0, δ0 and l0 with their numeric values.

[lortordermur]

Correction: It was formula 6 on page 8 of this PDF file^1 that I fed into the solver.

Here is the direct link.

[jchylik]

That is the formula I obtained using an equation solver

I see. You do not have to combine them. Or rather the issue is that by combing the formulas in this way, you kind of remove some information. (ATAN is defined for all real numbers, but its range of values is only between -π/2 and π/2)

Just as the solver says: Warning: some solutions might be lost

[lortordermur]

Oops, then by being "lossy" that solver was the only one able to cope with that transformation formula. The Mathway one refused for example.

But there has to be a way to resolve the left side of the formula for α.

I am clueless for the moment.

[jchylik]

But there has to be a way to resolve the left side of the formula for α.

Yes, there is, but you are losing some of the solutions.

The equation 5 is clear to invert: the values of \delta are always between -90° and 90°, same as the range of ASIN().

However when you solve the equation 6 the way described in the calculator, you are limiting results only on range between 102° and 282°.

[jchylik]

... And that is why we need to calculate right hand side of all three equations in 5.2.2 (and btw. in 5.2.1 as well)

• If rhs3 is positive then the solution is ACOS( rhs2 )+ \alpha_0
• If rhs3 is negative, then 2/π - ACOS( rhs2)+ \alpha_0

[lortordermur]

Yes, I counter-checked using the NED coordinate calculator and I see issues with very small values of b < 0.48.

So we need an IFS branch with a separate formula for negative and positive values? Could we also limit input to 0-360/0-90 like the NED calculator?

What document/article are you referring to with the paragraphs? Can't find it in my pinned tabs.

[jchylik]

So we need an IFS branch with a separate formula for negative and positive values?

Yes. Either way you need to calculate right hand side of all three equations in 3.2.1 and 3.2.2 ^3. The linked exercise ^1 just combines them in one.

Could we also limit input to 0-360/0-90 like the NED calculator?

Could? Yes, but then the calculator would then cover only half of the sky.

[lortordermur]

Ok, then I will play around a bit with the formula systems (either later or tomorrow) and see what I get. I still need to run them through the solver of course to resolve for the coordinate variables.

[lortordermur]

Another work-in-progress of the spreadsheet: sfcalcsheet.ods

The solved second and third formula of 3.2.2. are in H63 and I63. The first one is already in I61. Do I need to combine those somehow?

If this ends up being too much effort, maybe we can forgo the reverse part of the calculator? It seems to be the less useful part for a worldbuilding spreadsheet and I could release 0.20 then.

[jchylik]

I can have a look in a few hours when I am back on the keyboard. I will also check whether the forward transformation works for most of the angles.

[jchylik]

I have tried to plot the values from the forward calculator and it has the same issue as the reverse calculator. It works for the angles that you usually test, but all values of lalways end up between the values 32° and 210°.

Which is not surprising given the used formula l= -asin ( sin(α - α_0) cos δ /cos b) +l_0 Arcsin always returns values between -90° and 90° In other words, two different points from the equatorial coordinates and projected to the same point in galactic coordinates.

I am going to write a fix for both of the calculators soon. I will write a quick fix soon.

[jchylik]

I am back on the topic, there were two issues:

• range of ASIN and ACOS
• the formulas on the link sites often included typos

(Un)surprisingly, the correct equations for transformation[^1] are listed on Wikipedia ^2

I have tried that the forward and inverse transformation are matching, see: sfcalcsheet_mod3exp_test.pdf

You can see it as a working example: sfcalcsheet_mod3exp.ods

The calculations to add to cells:

b [rad]| K56 : =ASIN(SIN(RADIANS(E56)) * SIN(H$54) +COS(RADIANS(E56)) * COS(H$54) * COS(RADIANS(C56) - G$54))

b [deg] | L56 : =DEGREES(K56)

sin(l) | M56 : =SIN(RADIANS(C56)-G$54)*COS(RADIANS(E56))/COS(K56)

cos(l) | N56 : =(SIN(RADIANS(E56))*COS($H$54)-COS(RADIANS(E56))*SIN($H$54)*COS(RADIANS(C56)-$G$54))/COS(K56)

l [rad]| O56 : =IF(N56>0,-ASIN(M56)+$I$54,-PI()+ASIN(M56)+$I$54)

l [deg]| P56 : =MOD(DEGREES(O56),360)

[^1]: Carroll, Bradley; Ostlie, Dale (2007). An Introduction to Modern Astrophysics (2nd ed.). Pearson Addison-Wesley. pp. 900–901. ISBN 978-0805304022.

[jchylik]

And the inverse transformation is then:

δ [rad] | Q61: =ASIN(SIN(RADIANS(L61))*SIN(H$54)+COS(RADIANS(L61))*COS(H$54)*COS($I$54-RADIANS(P61) ))

sin(α) | S61 : =COS(RADIANS(L61))*SIN($I$54-RADIANS(P61))/COS(Q61)

cos(α) | T61 : =(SIN(RADIANS(L61))*COS($H$54)-COS(RADIANS(L61))*SIN($H$54)*COS($I$54-RADIANS(P61)))/COS(Q61)

α [rad] | U61 : =IF(T61>0,ASIN(S61)+$G$54,PI()-ASIN(S61)+$G$54)

[lortordermur]

Thanks for all your effort! I got error 508 in O56, P56 and U61 because of typos and corrected that. Here is what I got: sfcalcsheet-temp.ods

Maybe a naive question, but how do I get the calculations into the result fields? When converted to degrees none give the final result.

[lortordermur]

I think I found that some empty cells are referenced. Looking into it.

[jchylik]

Maybe a naive question, but how do I get the calculations into the result fields? When converted to degrees none give the final result.

My bad, the addresses in the reverse transformation were referring to the test sheet, not to the conversion sheet:

[jchylik]

δ [rad] | Q61: =ASIN(SIN(RADIANS(C60))*SIN(H$54)+COS(RADIANS(C60))*COS(H$54)*COS($I$54-RADIANS(E60) ))

sin(α) | S61 : =COS(RADIANS(C60))*SIN($I$54-RADIANS())/COS(Q61)

cos(α) | T61 : =(SIN(RADIANS(C60))*COS($H$54)-COS(RADIANS(C60))*SIN($H$54)*COS($I$54-RADIANS(E60)))/COS(Q61)

α [rad] | U61 : =IF(T61>0,ASIN(S61)+$G$54,PI()-ASIN(S61)+$G$54)

[lortordermur]

By the way I made a little mistake in the temp sheet: mod 36 instead of 360 in P56.

[jchylik]

I got error 508 in O56, P56

That is strange. Forward (α,δ) -> Backward() -> worked on all angles with exception of those where δ = -90 or 90 (for obvious reasons).

[lortordermur]

I googled that and error 508 is simply a bracket or syntax error. Got that sorted out.

[lortordermur]

RADIANS() in S61 is missing a variable (error 511).

[jchylik]

61 is missing a variable (error 511).

Sorry, it all happened while copying.

sin(α) | S61 : =COS(RADIANS(C60))*SIN($I$54-RADIANS(E60))/COS(Q61)

[lortordermur]

Still doesn't quite figure out in terms of results but there are no more errors. Here is what I got in the temp sheet (including degree conversions): sfcalcsheet-temp.ods

Could you have a look at it?

[jchylik]

Could you have a look at it?

Sure, cells C61 and E61 still refer to the old values, G61 and H61 Instead they should refer to Degrees(Q61) and Mod(Degrees(U61),360)

[lortordermur]

Ah, got it. The input values for the galactic coordinates were swapped. Now testing the forward calculator.

[jchylik]

Now testing the forward calculator.

Oh, I see what the issue is: I am off by one row of table.

I refer to C56 and E56 instead of to C55 and E55

[jchylik]

I have modified directly your sheet and it seems to work now: sfcalcsheet-temp-j.ods

[lortordermur]

Cool, you were a minute faster.

I’ll do some testing, make the interim calculation block a bit more compact and then check it into Git.

After that maybe the cartesian calculator that the OP asked for. That one should be simpler.