Interpolating in a spherical triangle

[lhargr2]

Hello gurus,

I'm not sure if this is the best place to start with these questions, because they're really more related to mathematics, but I've got a good feeling y'all will help. Right now I'm really looking more for some input on how I'm planning to approach the problem rather than the code itself. I'd like to give people the opportunity to suggest alternatives before I pour my heart into this.... I have searched far and wide for similar questions/solutions, but nothing seems to fit quite right.

Here's the scenario:

I have a set of about 200 Lat/Long points each with an associated value of some magnitude, B. These points represent "stations" where data is collected.

The desire is for an end user to enter in any Lat/Long point, Pw, and have a value B returned that is interpolated from the known values....below is how I plan to interpolate this value:

1. Calculate the distances from each data point to Pw using the haversine formula

2. Define data points P1 and P2 as the two closest points to Pw

3. Define data point Pn as the next closest point that satisfies the criteria: Pw is in triangle P1P2Pn (note: I plan to use haversine distances for proximity, but lat long values to test that Pw is enveloped, is there anything wrong with that logic?)

4. Calculate the distances P1P2, P2P3, P1P3, P1Pw and P2Pw using the haversine formula

5. "flatten" the triangle (generate cartesian coordinates) that honor the distances calculated in step 4 (I think this skews distance P3Pw, not sure how problematic that is)

6. Now that my triangle is flat, and all points are located, use barycentric coordinates and interpolate B for Pw


How convoluted is this? Is there any accuracy to this? What would be the magnitude of error if all my data points are in the same state and I just used Lat/Long coords and jumped to barycentric coordinates?

My search for answers keeps leading me to dissertations and abstracts as if this is some unsolved paradox, but it seems like the answer should be simple... (simpler than my logic)


ANY help is appreciated, and I will post code as I write it once I get some feedback.

Thanks guys!

[MarvinP]

Hi Ihargr2,

I've learned that when you have a hard problem, make one that is similar and easier. So to solve this problem I'd attack it like this. I assume we are talking about 3 space, perhaps air pressure at different locations on earth. I'd start with a flat cartesian plane and pick 3 points and millibar hights above those 3 points. How about (x,y,z) (z is the heights above the XY plane. Try (0,0,0), (3,5,2) and (0,2,6). Now pick your Pw point on the cartesian plane like point (1,2) and you need to find interpolated Z. If you can't do this problem then the harder one above, you can't do either. I'd guess the Z number by drawing a picture and GUESS an ANSWER. I'd guess Z is 3 knowing it wrong. Now how do I show it isn't 3? By showing it isn't 3 builds the formulas to find what it really is.

It seems to me staying in 3 space without using spherical geometry is a first step. The 3 given points above define a unique plane. Then where does (1,2) project onto this plane? Does the projection on the plane need to be orthoginal to the xy plane or orthoginal to the newly defined plane?? Do these with simple numbers and diagrams.

NOW - is the 3d (non spherical) answer correct? Do you really need to do spherical geometry? Will it add accuracy?

I hope the above suggestions helps solve the bigger problem.

[lhargr2]

MarvinP, I appreciate the strategy. Solving your example problem with pencil and paper helped me to understand the barycentric component of the problem, and for what it's worth, I arrive at 5/3 as the final answer. My biggest qualm with keeping it this simple are units. Using lat and long as if they are Cartesian values doesn't seem wise. Because I'm enjoying the learning experience, I'd like to keep this spherical, and have started to code it with the same basic approach I previously outlined. I made it pretty far before my first snag. Please see the attached spreadsheet.

Here's my new scenario:

1. A haversine UDF is used to calculate the distance from each data point to my target point in a helper column

2. Another UDF for testing if a point is in the triangle created by the three data points is defined

3. a Third UDF defines the station names and coordinates, using the 2 closest data points, while finding a third that satisfies the triangle test.

My problem is, in my code, if I define i as any value and delete the Do Until Loop then I get expected results. As soon as I add the Loop statement and try to call the PTINPOLY function I get #VALUES.

What's wrong with my Loop?

Please Login or Register  to view this content.

BDE Testing.xlsm

[lhargr2]

Can someone have a look, please? If I can troubleshoot the Loop I think I can finish this thing!

[shg]

I think you might Google kriging with irregularly spaced data. See, for example, http://amath.colorado.edu/faculty/kl...leiber2015.pdf

[lhargr2]

Thanks for the link, shg. Looks like I have some studying to do! In the meantime, could you please look at the Do Until Loop... Even if my approach is flawed, this is eating me up. Each UDF works independently, and I can manually change i until I arrive at a solution, but I can't get it to iterate without #VALUE!s

I think it's a simple syntax error, but I'm guessing at this point.

Please Login or Register  to view this content.

....full code in previous post

[shg]

I don't know what's wrong with your code, but it is inefficient.

Instead of calculating this

Please Login or Register  to view this content.

a zillion times, just sort the rows by distance from the top row, take rows 2 and 3 as the closest two points, then continue down the list until you find a point that makes a triangle that encloses the first.

You can step through the code to find what's causing the error.