0

I can't get my head around plotting a circle on a big sphere (Planet Earth). I have a column that has 180 rows - 1 for each degree. The next row is each degree converted to radians. Easy so far.

A1 is the centre point of the radius latitude

B1 is the centre point of the radius longitude

C1 is the radius required in miles

Column D is each degree so there are 180 rows.

Column E is each degree converted to radians

Column F has (first three rows):

A1+((C1/69)*COS(E1))    latitude for degree 1.
A1+((C1/69)*COS(E2))    latitude for degree 2.
A1+((C1/69)*COS(E3))    latitude for degree 3.

Column G is (first three rows):

B1+(C1/(COS(RADIANS(A1))*69)*SIN(E1))   longitude for degree 1.
B1+(C1/(COS(RADIANS(A1))*69)*SIN(E2))   longitude for degree 2.
B1+(C1/(COS(RADIANS(A1))*69)*SIN(E3))   longitude for degree 3.

After the first 180 degrees, the formula changes as you multiply by -SIN & -COS.

A1+((C1/69)*(-COS(E1)))   latitude for degree 181.
A1+((C1/69)*(-COS(E2)))   latitude for degree 182.
A1+((C1/69)*(-COS(E3)))   latitude for degree 183.

and

B1+(C1/(COS(RADIANS(A1))*69)*(-SIN(E1)))   longitude for degree 181.
B1+(C1/(COS(RADIANS(A1))*69)*(-SIN(E2)))   longitude for degree 182.
B1+(C1/(COS(RADIANS(A1))*69)*(-SIN(E3)))   longitude for degree 183.

I get a wonderful circle in the graph which overlays a map, can be moved and resized by changing A1, B1 & C1 (A1 & B1 use a filterxml-webservice, and it's actually just a postcode I change), and looks very good.

My problem is, have I actually got the math right?

Is it correct to divide the radius required in C1 (e.g. 10 miles) by the distance per degree at the equator (app 69 miles)?

I personally live around 53deg latitude, so for me 1 deg longitude is about 41.5 miles but the spreadsheet is used worldwide.

I've removed the $ signage for clarity (used in spreadsheets to drag formula down rows & across columns).

I've been asked to give a screenshot. Don't know what good it'll do, as I'm just asking if my sums are right... map and two radii

Two circles are shown with the cluster of members in each (the names are fake - just in case you were wondering) I believe the circles (especially the lower one) are too big The radius is set to 35 miles, but it's looking more like 45. If the circles were correct, there would be 15 more members on the sheet

I came to this site as it was here that I found some calculations to help me at: Calculating longitude length in miles?

The very simplified sheet is here

Improve this question
7
  • can you post a screenshot of spreadsheet?
    – neogeomat
    Apr 29, 2018 at 14:12
  • I meant screenshot of spreadsheet, altough map also helps.
    – neogeomat
    Apr 29, 2018 at 15:07
  • It's 20MB big, but here goes.... [link] dropbox.com/s/0178rjv6pn33tx9/FakeMembers.ods?dl=1 Using the Radius and County search in the control shows the "missing members" from the circle, but these members are more than 35 miles away.
    – user256787
    Apr 29, 2018 at 15:13
  • didn't find those columns in the spreadsheet.
    – neogeomat
    Apr 29, 2018 at 15:33
  • Workings sheet, columns BQ to BZ. BQ is the degrees, BR is the radians. BS & BT are the lat & long for the first postcode, BU & BV for the second postcode, BW & BX for the 3rd, and BY & BZ fo the 4th. I only show 2 postcodes, but you can add two more in the Control sheet. In my explanation, I was just trying to keep things easier to explain with a simpler spreadsheet.
    – user256787
    Apr 29, 2018 at 15:47

1 Answer 1

Reset to default
1

The answer is, yes it is correct.

I traced the problem to the Eastings & Northings used to calculate and display the position of each member. They were in kilometres.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.