Trying to convert from Latitude/Longitude to Radar plot?

[David]

I guess my subject line says it all. I have tried cartisian co-ordinates to
convert from XY to Polar Graph, but I need to plot it on a radar chart. Can
anyone help me please?


Dave Whitehead

 

[Tushar Mehta]

You may want to explain in some more detail what it is you want to do
and how the suggestions to date have not been useful.

A radar chart is a chart with multiple independent axis along the lines
of http://www.tushar-mehta.com/excel/software/custom_radar/. It kinda
looks like a polar chart and, based on a search of google, many use the
terms interchangably.

To me a polar chart is one which plots two variables, r, the radius
from the center, and theta, the angular displacement from the some
preestablished line, usually the horizontal line going from the center
towards the right.

Plotting longitude/latitude on a polar chart makes sense -- as long as
one keeps in mind that latitude and longitude describe a position on a
3D globe whereas a polar chart is a 2D object.

But, what does it mean to you to plot longitude/latitude on a radar
chart?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[David]

Tushar;

First of all, thank you very much for replying.

I have taken a series of RF field strength measurements of an FM
transmitter, traveling around (360 degrees) the antenna (on the available
dirt or paved farm roads) with a radius from .8 to about 2.3 miles from the
FM antenna tower. I had a GPS reciever which stored the Latitude and
Longitude at each measurement point being sampled each second along with the
signal strength using a calibrated FM receive antenna, in a text file (I
also took GPS measurements of the tower location to use as the center point
for my graphs). I have a program which plots the location, on Microsoft
MapPoint, the field strength measured, with colored dots, each color
representing a small range of the minimum to maximum of the measured RF
Field strength.

In order to get a better visual representation, I would like to plot the
Lat/Long points in Polar form on an XY graph, so I would have a point at the
end of each ray, using the co-ordinates at the FM tower as the reference or
zero point.

I could then use a simple formula to calculate, from the known distance (r)
from the tower and the measured signal strength, what the strength would be
at, say, a 1 mile radius for all the rays, then convert THAT result to a
colored dot on a Polar or XY (with positive AND negative values) and get a
much better visualization of any anomaly or directivity of the transmitting
FM antenna. I have looked through the help files, tried Cartisian formulas
(which don't seem to care for negative values of X) as well as through this
and other news groups, but haven't been able to find anything close enough
to work. Unfortunately, I am not a math wizzard, and I haven't really done
much charting (most of which I have given up on). However I really need to
see this one through.

Thanks again for your time, and I hope this gives you a little better
understanding of what it is I am trying to accomplish.

Regards, Dave Whitehead

 

[Tushar Mehta]

The solution is sufficiently complex that it is probably best explained
with diagrams. I am willing to write it up but it would really useful
if I could use real data rather than make up latitude, longitude, and
RF strength data. Mind sharing the data you have with me?

Let me lay out the basic idea below. If anyone with cartography
experience knows I am on the wrong track this would be a good time to
speak up.

Three building blocks:

1) For simplicity, I will designate latitude and longitudes as (X,Y).
Northern latitudes and Western longitudes are positive, though one
could trivially change this convention as long as consistency is
maintained. Also for simplicity, the radius of the globe is normalized
to 1.

2) We can use any point, say (X0,Y0) as the (0,0) reference. It
doesn't have to be the intersection of the Prime Meridian with the
Equator. In this transformed reference, the old coordinates (Xi,Yi)
becomes ((Xi-X0) mod 90, (Yi-Y0) mod 180)

3a) When we project the 3D globe onto a 2D sphere and put the (90,-)
coordinate (i.e., the North pole so to say) at the center, the
longitudes are equidistant angles and the latitudes become concentric
circles at a distance from the center given by Cosine(latitude).

3b) If we project the same with (0,0) at the center, the latitudes are
horizontal lines at a distance from the center horizontal line
(Equator) given by Sine(latitude). The longitudes are curved lines
that 'radiate' from the poles and intersect the center horizontal line
at a distance of Sine(longitude). Hence, the x coordinate of a
location is given by Sine(longitude)*Cosine(latitude) and the y
coordinate is Sine(latitude).

OK, all the hard work is done. Now, it is just a question of
replicating the calculations in XL.

Suppose the original coordinates are in A (latitude) and B (longitude)
starting with row 2 and are given in degrees, i.e., minutes and seconds
have been converted into fractional degrees. Suppose C contains the RF
strength. Finally, suppose the cells containing the transmitter x and
y values are named Tx and Ty.

Now, if you want to go with 3b:

In D2 enter the formula =MOD(A2-Tx,90).
In E2 enter =MOD(B2-Ty,180).

D2 and E2 are the transformed latitude and longitude.

In F2 enter =SIN(RADIANS(E2))*COS(RADIANS(D2)).
In G2 enter =SIN(RADIANS(D2)).

F2 and G2 are the x and y values for plotting.

Copy D2:G2 as far down as you have data in A:C.

Plot F2:G2.

Use Bovey's XY Chartlabeler (www.appspro.com) or Walkenbach's
ChartTools (www.j-walk.com) to add column C as datalabels.

On the other hand, if you want to go with 3a:
In some cell enter the formula =-(Tx-90). In another enter =Ty. Name
these two cells X0 and Y0 respectively.

In D2 enter the formula =MOD(A2-X0,90).
In E2 enter =MOD(B2-Y0,180).

D2 and E2 are the transformed latitude and longitude.

In F2 enter =COS(RADIANS(D2)).
This is the radius of the concentric circle representing the latitude.

In G2 enter =F2*COS(E2). In H2, enter =F2*SIN(E2).

G2 and H2 are the x and y values for plotting.

Copy D2:H2 as far down as you have data in A:C. Plot G:H and use the
Bovey or Walkenbach add-in to add the RF strengths as data labels

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions