share a spreadsheet

lfnfan · Post by **lfnfan** » Fri Sep 04, 2009 11:18 am

glad the formula looks about right - will pick up any rounding errors thru further checking...

It is possible to manually input a formula for the points' labels on the star chart (='Galaxy Data'!$N$xxx). So we can have planet names on the chart rather than XY co-ords. Appears that the labels remain correct whatever galaxy. Will just take a bit of time to implement.

PhantorGorth · Post by **PhantorGorth** » Fri Sep 04, 2009 12:18 pm

lfnfan wrote:
It is possible to manually input a formula for the points' labels on the star chart (='Galaxy Data'!$N$xxx). So we can have planet names on the chart rather than XY co-ords. Appears that the labels remain correct whatever galaxy. Will just take a bit of time to implement.

I have tried when I created my spreadsheet but I couldn't get it to work that's why I didn't bother porting the file to Excel in the end. So you might find it easier just to install OpenOffice and work with it there after all you will get the feature you want and OpenOffice is available for Windows, Linux, Solaris (not that there is a Solaris version of Oolite) and Macs for free where as Excel is available for Windows and Mac* only for quite a pretty penny. (*I believe the MS-Office for Macs is pretty ropey and lots of stuff won't work.)

Phantor Gorth

lfnfan · Post by **lfnfan** » Mon Sep 07, 2009 2:38 pm

I found a macro that automates what would otherwise have been a very tedious process (='Galaxy Data'!$N$5, ='Galaxy Data'!$N$6, ='Galaxy Data'!$N$7... ='Galaxy Data'!$N$260, ='Galaxy Data'!$N$261)

The Star Chart now shows planet names. Some areas of the chart are very cluttered, but it's probably an improvement overall.

So far I came across two differences between Oolite and spreadsheet - one a distance calc, and one a planet name. Could perhaps be an OXP i have installed?

(I have OpenOffice installed, but i rarely use it - probably lack of familiarity more than any other reason.)

JensAyton · Post by **JensAyton** » Mon Sep 07, 2009 5:14 pm

PhantorGorth wrote:
That formula looks right (I haven't counted brackets though, so I can be 100% sure). The only question I have about the distance calculation is that they maybe some rounding done before multiplying by 0.4. I have some vague recollection along those lines but I could be wrong.

The Elite-compatible distance calculation function is:
floor(sqrt(dx * dx + dy * dy)) * 0.4 (where dx and dy are x and y distances). according to a comment in the code, the maximum observed difference from the “accurate” calculation is 7.15/7.

lfnfan · Post by **lfnfan** » Tue Sep 08, 2009 1:14 pm

Thanks Ahruman

I have replaced INT( ) in the formula with FLOOR( ,1). The effect seems to be the same with my initial test planets.

Interesting comment in the code. Would explain my one finding so far, which was a difference of 1LY from the calculated value.

lfnfan · Post by **lfnfan** » Fri Sep 25, 2009 9:01 am

I have uploaded a new version of the 'trade route timekeeper' spreadsheet.

This version has the names of all the planets on the star chart, and gives a breadcrumb trail of the planets along your trade route. Makes it easier to see any profitable nearby diversions. Couple of other minor tweaks.

To be found here:
http://wiki.alioth.net/index.php/Oolite_FAQ in the 'How do I make decent profits?' question under 'Gameplay'
and here:
http://wiki.alioth.net/index.php/Oolite_planet_list, in the link on the second bullet point.

As a side-point, if anybody knows why my links in the Wiki appear in bold, please let me know!

Post by **Disembodied** » Fri Sep 25, 2009 11:31 am

lfnfan wrote:
As a side-point, if anybody knows why my links in the Wiki appear in bold, please let me know!

They both had three ' on either side, which formats as bold (e.g. '''this text would appear in bold on the wiki'''). That's the basic answer, but if you didn't put the 's there, then I don't know why they were added! I've de-bolded the links, anyway.

lfnfan · Post by **lfnfan** » Fri Sep 25, 2009 11:48 am

Thanks D

I copied the code from one of Thargoid's wiki pages. Guess they must have been bold there also, but looked less out of place

PhantorGorth · Post by **PhantorGorth** » Fri Sep 25, 2009 11:56 am

Due to my work on the Vector Maps the distance issue came up. I looked at the 1.72.2 code and saw that there is an initial rounding down of the dy distance.

Code: Select all

OOINLINE double distanceBetweenPlanetPositions ( int x1, int y1, int x2, int y2) 
{ 
   int dx = x1 - x2; 
   int dy = (y1 - y2)/2; 
   int dist = sqrtf(dx*dx + dy*dy); // here's where the rounding errors come in! 
   return 0.4 * dist; 
}

So the formula should be:

floor(sqrt(dx *dx + floor(dy) * floor(dy)) * 0.4

There is no divide by 2 as the y positions in the spreadsheet are stored as y/2 in the first place.

lfnfan · Post by **lfnfan** » Fri Sep 25, 2009 1:52 pm

Yeah, I noticed that discussion. Can you post me some distances that came up with differences, and I will see what my formula comes up with?

In my travels around G2 so far, the formula is consistently accurate to one decimal place. Apart from a single difference between Oolite and the spreadsheet of 1LY. Which seems to me to be a 'strange exception' rather than an indicator of a fundamental error in the formula. Getting my hands on more than one difference would be useful.

I'm dividing by 2 on the Y co-ord as the formula references the 2Y data in Oolite Galaxies.xls Column S.

out of curiosity, I added in the Floor() around the dy's and it made no difference!

PhantorGorth · Post by **PhantorGorth** » Sat Sep 26, 2009 4:10 pm

Lets take the world ANDIRI in Galaxy 2 (53, 157). It is linked to world ISRARA (67, 180).

The two equations are:

1) floor(sqrt(dx*dx + (dy/2) * (dy/2))) * 0.4

2) floor(sqrt(dx*dx + floor(dy/2) * floor(dy/2))) * 0.4

dx = x1 - x2 and dy = y1 - y2

where the world from which we measure the distance from is (x2, y2)

Therefore dx = 67-53 = 14 and dy = 180-157 = 23.

Equation 1 gives:

floor(sqrt(14 * 14 + 11.5 * 11.5)) * 0.4
= floor(sqrt(196 + 132.25)) * 0.4
= floor(18.117670932) * 0.4
= 18 * 0.4 = 7.2

Equation 2 gives:

floor(sqrt(14 * 14 + floor(11.5) * floor(11.5))) * 0.4
= floor(sqrt(14 * 14 + 11 * 11)) * 0.4
= floor(sqrt(196 + 121)) * 0.4
= floor(17.804493815) * 0.4
= 17 * 0.4 = 6.8

If you use the former equation then this link is excluded and by the hours taken = distance squared equation for witchspace jumps that would be an extra 5.6 hours anyway.

The code in Oolite uses "int" variables and it rounds towards zero, so negatives round up and positives round down.

The problem with int(value) in excel/OpenOffice Calc is that it rounds down only and 11.5 would become 11 but -11.5 becomes -12 which would be a problem. Using Floor gets round this issue.

I have looked on your version of the spreadsheet to see that you are using equation one and if you put those two worlds in you come up with 7.2 light years when it should be 6.8.

P.S. I have move the placing of you spreadsheet on the wiki page to the bottom of the section as it implied it was based on the first spreadsheet of that page which just a list of worlds where as your spreadsheet is based on mine which was lower down in that section.

lfnfan · Post by **lfnfan** » Sat Sep 26, 2009 11:02 pm

sweet. thanks for the example, and the step-by-step.

I have floor()'d the dy and andiri to israra now comes out at 6.8. The other exception I had was laraisso - lainor which was 3.6 per Oolite but 4 per the spreadsheet. Now also coming out consistent.

Had to floor(abs(dy)) because floor falls over if the signs for the number and the rounding factor are not the same.

will upload the new version shortly.

thanks again

PhantorGorth · Post by **PhantorGorth** » Sat Sep 26, 2009 11:24 pm

lfnfan wrote:
Had to floor(abs(dy)) because floor falls over if the signs for the number and the rounding factor are not the same.

Yeah, I wanted to use your version of the sheet for a long distance passenger run I am doing at the moment and had to modify it and hit the same problem. Ended up fixing it the same way too.

I need to double-check my XSLT code for the Vector Maps now

lfnfan · Post by **lfnfan** » Sun Sep 27, 2009 1:28 pm

with apologies to those who have just downloaded v05 of this spreadsheet,

v06 is now available, and it contains 'added accuracy' [/flashing lights]

download links in the wiki FAQ, and Planet List pages, as above.

sorry for any inconvenience